Monday, October 27, 2008

C# and SharePoint 2007 Tutorial - Getting the Total Responses for All the Surveys on a SharePoint Site

Click the link to download the source code for this post

Licensing and Warranty

You may use the code as you wish - it may be used in commercial or other applications, and it may be redistributed and modified. The code is provided "as-is". No claim of suitability, guarantee, or any warranty whatsoever is provided. By downloading the code, you agree to defend, indemnify, and hold harmless the Author and the Publisher from and against any claims, suits, losses, damages, liabilities, costs, and expenses (including reasonable legal or attorneys' fees) resulting from or relating to any use of the code by you.

A project that I'm currently working on for my CIO is to create more robust reports from surveys created in SharePoint 2007. I'm using a SQL Report Server Project to build these reports from the data entered on a SharePoint survey. In order to be able to create a report, the SharePoint survey needs to have responses. I really didn't want to spend time manually checking each survey to see whether it has responses. I wrote a program using C# to automatically check the surveys on the SharePoint site and return the item counts (responses) to me via e-mail.


The program uses the Microsoft.SharePoint library to access the SharePoint site, and it uses the System.Net.Mail library to send the e-mail. To keep the program flexible, I keep the "to e-mail", "from e-mail", SharePoint site URL, and SMTP server name in the app.config file. That way, if there's a change to any addresses, I can just change the config file instead of having to change and to recompile any code.

In a nutshell, the program does the following:

  • It gets the values from the app.config file

  • It opens the SharePoint site

  • It loops through the Lists collection of the site and returns the item count (responses)

  • Sends the results to the e-mail address referenced in the "to e-mail" element of app.config


Traversing through the Lists collection on the SharePoint site
Starting from line 25 in the source (Program.cs), it uses the SPSite class and the SPWeb class (Microsoft.SharePoint) to open the site (as referenced in app.config). Using a foreach loop, it looks at each list in the collection and returns the value in the Items property of the list, which is the total number of responses for the survey. Here is the code snippet:

using (SPSite site = new SPSite(strURL))
  using (SPWeb web = site.OpenWeb())
    foreach (SPList list in web.Lists)
      msgSB.Append(" - ");
      SPListItemCollection items = list.Items;
      msgSB.Append("Total items: " + items.Count);

Note that msgSB is an instance of the StringBuilder class, and I am recording all the results in this instance.

If your SMTP server requires a specific ID and password for authentication, you can use the System.Net.NetworkCredential class to specify the ID and password. Here is a code snippet on how to set up your SmtpClient instance to use the specific credentials:

System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(ID, password, domain);
smtpClient.Credentials = credentials;

If you are going to use this method, please be aware of some security precautions, such as not storing the ID, password, and domain in clear view of the naked eye. For example, if you want to store this information in the app.config file: encrypt the values, put the encrypted values in the app.config file, and use the code to decrypt the values.

Sending an E-Mail
The MailMessage class sends the e-mail. However, I wouldn't be able to send an e-mail without referencing an SMTP server. The SmtpClient class is used to "set up" the SMTP server. Below is the code snippet, found on line 42 in the source.

String message = msgSB.ToString();
MailMessage mail = new MailMessage(fromEmail, toEmail, subject, message);
String mailServer = Properties.Settings.Default.mailServer;
SmtpClient emailClient = new SmtpClient(mailServer);
emailClient.UseDefaultCredentials = true;

Note that I'm converting the StringBuilder instance (which contains the results) into a string. Also note that the UseDefaultCredentials property is set to true, which means that the program will use the current logged in user's credentials to authenticate against the SMTP server. One of the ways that network administrators prevent hackers from hijacking their SMTP server to use for sending spoof e-mails is they configure the SMTP server to require authentication.

The app.config file

Since it's possible that addresses can change due to machine upgrades and domain name changes, I stored the following four values in the app.config file:

  • <toEmailAddress> - The e-mail address of the person who should receive the report

  • <fromEmailAddress&; - The e-mail address of the person of the "sender". This is typically a "webmaster" address or a generic "SharePoint administrator's" e-mail address

  • <surveyURL> - The ROOT URL where the surveys are located. For example, if your surveys are on site http://bogussite/surveys, enter http://bogussite/surveys.

  • <mailServer> - The name of the SMTP server

In the source code, I wrote an explanation of what values should be in each element. You will need to replace that information with the actual values.
Here is an example configuration snippet of the app.config file:

  <setting name="toEmailAddr" serializeAs="String">
  <setting name="fromEmailAddr" serializeAs="String">
  <setting name="surveyURL" serializeAs="String">
  <setting name="mailServer" serializeAs="String">

Before Using the Code for Your Own Solution:

  • The compiled program (binary) must reside on the same machine where SharePoint 2007 is installed, or you will get errors.

  • Ideally, you want to have your surveys in a separate subsite of your site collection. If you have other lists in your site collection or subsites, the program will pick up the item counts of those lists as well.

  • If you want the program to run at a certain time (ex: every Wednesday evening), you will need to use Windows Scheduler to schedule the binary to run automatically.

No comments: