Friday, May 14, 2010

Connecting to Sharepoint Lists with C# and ASP.Net

I was recently tasked with a workflow process that would automatically notify SharePoint users that their timesheets were due. This was to happen every Monday at 8am and again at 11am based off of a form library in SharePoint.

There was already a 3-step approval process workflow in place for this library so my first thought was to create a workflow in SharePoint designer to fill this need. I created a Boolean column in the Employees list to flip a submission status and tapped into the approval workflow to toggle this flag when the form was submitted and flip it back when the final approval step happened. So now I had something to check against to generate my "list of shame" for users to notify.

I created a 2nd workflow to get the list of users who had this flag set to false and send out a reminder email, but this had 2 inherent issues. The first issue was that this did not allow a way to trigger the workflow on a set schedule. Second issue was SharePoint workflows do not seem to allow you to iterate through a list and perform an action against all matching criteria (for each loop). They only seem to allow the top returned result to be acted against.

The first issue I solved with a small console .exe file that taps into the SharePoint list and starts the workflow. I scheduled this with windows task scheduler to run at the needed times and everything worked perfect. However this did not solve the inability to iterate through the list of users.

Since this was just an email task and didn’t rely on modifying the document in any way, I realized that the small app I had already developed could handle all of this for me and eliminate the 2nd workflow all together. So I took what I already had and added a method to get the list of employees, check it against the submitted flag and send out emails accordingly. The code below accomplishes this task.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Workflow;
using System.Net.Mail;

namespace TriggerEmailNag1
{
class Program
{
static void Main(string[] args)
{
SendNagEmails();
}


///
/// This will send a nag email to users whos timesheet submitted status in the employees list is set to No.
///

static void SendNagEmails()
{
using (SPSite site = new SPSite("http://YourSharePointSite/"))
{
using (SPWeb web = site.OpenWeb())
{
// Provide the name of the SharePoint list to connect to
SPList list = web.Lists["Employees"];
SPListItemCollection listItems = list.Items;

// Loop through the list and do work
foreach (SPListItem item in listItems)
{
if (!(bool)item["Timesheet Submitted"])
{
string UserName = item["User Name"].ToString();

// Check to see if the username has the domain prepended to match. If not then add it
int check = UserName.IndexOf("\\");
if (check <= 0)
{
UserName = "domain\\" + UserName;
}

// Get the users profile from SharePoint by the username in the list
SPUser user = web.AllUsers[UserName];

// Create the email message to send to the user
StringBuilder body = new StringBuilder();
MailMessage message = new MailMessage();
message.From = new MailAddress("some@emailaddress.com");
message.To.Add(new MailAddress(user.Email.ToString()));
message.Subject = "Timesheet Reminder";
body.Append("This is a reminder that your timesheet for this week has not been submitted yet.").AppendLine().AppendLine();
body.Append("Please submit your timesheets by 8am on Monday morning to avoid any delay in billing and payroll disbursment.").AppendLine().AppendLine();
body.Append("Thank you.");
message.Body = body.ToString();

// Send the email
SmtpClient client = new SmtpClient();
client.Send(message);
}
}

}
}
}
}
}

1 comments:

Carl Fisher said...
This comment has been removed by the author.

Post a Comment

 
src='http://sites.google.com/site/carljfisher/shBrushCss.js'/> src='http://sites.google.com/site/carljfisher/shBrushJScript.js'/> src='http://sites.google.com/site/carljfisher/shBrushSql.js'/> src='http://sites.google.com/site/carljfisher/shBrushXml.js'/>