So, you’ve been reading my blog for a while now, or maybe this is your first time here. Regardless, I think it’s time I showed you guys some code. At my core I like to think of myself as a competent developer. I’m probably fooling myself, but I enjoy it and even find it relaxing at times. However, lately my main job function has been that of a SharePoint Architect which does not leave me much time to write any code at all.
Recently, one of my twitter friends (@rlilly) wanted to know how to get some functionality out of SharePoint that was not part of its standard functionality and I thought “Hey, I could write something fairly quickly to do that”. So, I spent a few hours playing around and was able to come up with an application that met her needs. It was a fairly simple application, but I thought it would be a great introductory application to help new SharePoint Developers learn a thing or two about the SharePoint Object Model and writing some code for SharePoint.
Now, I could just give you some unrelated code snippets and explain them to you but I find I get more out of code if there is an overall objective. So, we are going to take some real world requirements for an application and build it over five blog posts. So, let’s get started!
*CAVEAT* Please do not consider this production worthy code! This is a teaching tool for you as well as myself! If you stick it in production, I am not responsible for where I forgot to tell you to dispose of objects, and your memory filling up, your servers crashing, and your eventual termination. :)
Application Requirements
The application we are going to write needs to, on a scheduled basis, read all the entries from a particular view in a SharePoint List, convert those entries into an Excel spreadsheet, and automatically upload those files to a document library. So, if we break the functionality down we have:
- Application needs to get all entries for a particular view of a list
- Application needs to convert entries into an excel spreadsheet
- Application needs to upload excel spreadsheet to a document library
- Document needs to be executed on a scheduled basis
First thing we need to do is determine what kind of application we need to write. We could get most of this functionality out of a custom workflow attached to the SharePoint list except for executing on a scheduled basis. We could also use a custom timer job or just create a console application and run it as a scheduled task.
What we will do is create a console application to accept three command line parameters:
- Name of the SharePoint List to create excel spreadsheet from
- GUID of the View for the corresponding List
- Name of the Document Library to store excel spreadsheet to
I chose to write a console application because of the ease of using command line parameters and scheduling it as a Scheduled Task on the server OS. As an aside, I’m writing this application to run on your SharePoint farm, however I could have also written this entire application using SharePoint’s web services and it could be executed from any computer. I might write that application as well if there is enough interest.
Set Up Dev Environment
So, we have our basic requirements. Now we need to set up our dev environment. Remember, in order to develop using the SharePoint Object Model you have to develop on a SharePoint Server. If you still don’t have a VM, build one or download Microsoft’s free one. You can find out more information about both of these options on my old blog post: SharePoint VM – Just Do It
Create Project
Next we need to create our console application project in Visual Studio. If you’ve read a couple of my other blogs you know I can go into way too much detail on some of this stuff. However, I know that there are some people out there who have just never done this before and I want to help them as much as possible. So, if you already know this stuff, just skip on down to what you don’t know.
To create the console application project:
- Start Visual Studio and from the File menu click on “File->New->Project”
- Select “Windows” Project type under “Visual C#”. Then select “Console Application”. Also, give your project the name “SPListExportToExcel” (or whatever you like) and click on “OK”.
There you go, your project is created. By the way, in case you’ve spent your whole development life writing web applications or Windows applications, a Console Application is a command line application that does not require or have a GUI. They tend to be parameter driven and run in a command prompt type window. These are great for applications that need to be scheduled to be executed. They can start, do their job, and terminate while not consuming as many resources as other applications. Go ahead and press “F5” in visual studio. It will compile and run your program. You will see a black console window appear on the screen and then disappear when the program terminates. Pretty awesome.. eh?
Enough already! I want to see some code!
Okay… Okay… Time for some meat you can chew on. The first part of the application we are going to write is code to retrieve a specific SharePoint list from your farm. I wanted to make this application as flexible as possible, so instead of passing in the path to a specific list, I’m just going to accept the name of the list. This way, it does not matter where in the farm your list is. The application will search the entire farm until the list is found. However, the code would need to be modified if you wanted it to handle multiple instances of a list on a farm. Another good reason to search the entire farm for the list is to see how it’s done! Who needs to go to the movies when you can step through code that goes through your entire farm looking for a list?? Sorry ladies.. I am married.
First thing you need to do is add a reference to the SharePoint Object Model to your project. Chances are if you needed the steps to creating a project, you’ll need these steps as well. No worries, we all had to do something for the first time, right? To add the reference to the SharePoint Object Model:
- Right click on the name of the project in the Solution Explorer window on the right side of the screen and select “Add Reference”
- Scroll down to “Windows® SharePoint® Services”. Click on it, and click “OK”
- Now you need to add two “using” statements to your code to use the SharePoint Object Model.
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
namespace SPListExportToExcel
{
class Program
{
static void Main(string[] args)
{
}
}
}
Seriously, Where’s the Code?
Be patient! Let’s put a couple of lines of code in here that will help us debug our code as we go. Whenever you are writing console applications you can take advantage of writing messages directly out to the console window so you can see what’s going on with your program. Just to make absolutely sure our console application is working, let’s write out a message to the console window and pause the program when it finishes so that the console window does not disappear before we can see what happened. So, modify your code as follows:
static void Main(string[] args)
{
//write a message to the console window
Console.WriteLine("Okay, the program is working! Press enter to exit");
//wait for an enter key press
Console.ReadLine();
}
Now when you press “F5” to run the application you will get a message and the console window will stay on the screen until you press enter. Be sure to remove that “Console.ReadLine()” before you go into production or the application won’t terminate!
How About Now? NOW Can I See the Code?
Yes.. yes… we are finally ready to show you the big chunk-o-code for part 1 of this blog post. A while back I needed to find a SharePoint List on my farm and I was not sure where it might reside. I reached out for help on Twitter (where else) and Mr. Philip Wheat (@pwheat) was kind enough to point me to some code he had written for SharePoint 2003. I was able to take this code and with very few modifications make it work in 2007. Thanks for the help Philip! The following code will traverse the entire farm on which it as executed until it finds the list specified. VERY IMPORTANT NOTE: The application runs with the credentials of the user who is signed in. If the user does not have access to a list, the application won’t find the list! When you run this as a scheduled task, part of setting up a scheduled task is assigning the credentials the application will use when it is executed.
So, modify your code as follows. Notice the many “Console.Write()” calls in the code that are used to help you watch the progress in the console window when the application is running. What’s also nice about writing an application that runs on the SharePoint farm using the SharePoint object model is that you don’t have to write any code to “connect” to the farm. You are automatically connected to the farm using the credentials of the user logged in.
static void Main(string[] args)
{
//specify the name of the list your want to find
String listName = "AuthorizedRequestors";
//go through each SPWebApplication
foreach (SPWebApplication oWebApp in SPWebService.ContentService.WebApplications)
{
Console.WriteLine("Searching WebApp: " + oWebApp.DisplayName.ToString());
//go through each SPSite
foreach (SPSite oSite in oWebApp.Sites)
{
Console.WriteLine("Searching Site: " + oSite.Url.ToString());
try
{
//go throuth each SPWeb in the SPSite
foreach (SPWeb oWeb in oSite.AllWebs)
{
Console.WriteLine("Searching web: " + oWeb.Url.ToString());
try
{
//get the list based upon name.
//if list is not in spweb this will cause an exception
//to be thrown. This is Ok. so, just catch it and continue
//I know.. I know.. this is not good practice. But there is not
//a oWeb.ListExists() method or something like that (that I know of).
SPList oList = oWeb.Lists[listName];
//list was found!
Console.WriteLine("LIST FOUND! Press enter to exit");
//wait for an enter key press
Console.ReadLine();
return;
}
catch
{
//catch and continue
Console.WriteLine("List not found in web: " + oWeb.Url.ToString());
}
finally
{
//make sure to dispose of web
oWeb.Dispose();
}
}
}
catch (Exception e)
{
Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
}
finally
{
oSite.Dispose();
}
}
}
//write a message to the console window
Console.WriteLine("List was not found. Press enter to exit");
//wait for an enter key press
Console.ReadLine();
}
Now when you run the application, this code will go through all the sites on your farm looking for whatever list you have specified for the variable “listName”. Once the list is found, a message is displayed saying it found the list and the program terminates after an enter key press.
Let’s Clean this Up a Bit
Before I conclude this blog post, let’s clean this up a bit. Let’s turn the code for getting a SharePoint list into a Method that returns an SPList and have our main class call that method. So, the above code becomes:
static void Main(string[] args)
{
//specify the name of the list your want to find
String listName = "AuthorizedRequestors";
SPList oList = GetSPList(listName);
//write a message to the console window
if (oList == null)
{
Console.WriteLine("List '{0}' was NOT found. Press enter to exit",listName);
}
else
{
Console.WriteLine("List '{0}' WAS found! Press enter to exit",listName);
}
//wait for an enter key press
Console.ReadLine();
}
private static SPList GetSPList(String listName)
{
//go through each SPWebApplication
foreach (SPWebApplication oWebApp in SPWebService.ContentService.WebApplications)
{
Console.WriteLine("Searching WebApp: " + oWebApp.DisplayName.ToString());
//go through each SPSite
foreach (SPSite oSite in oWebApp.Sites)
{
Console.WriteLine("Searching Site: " + oSite.Url.ToString());
try
{
//go throuth each SPWeb in the SPSite
foreach (SPWeb oWeb in oSite.AllWebs)
{
Console.WriteLine("Searching web: " + oWeb.Url.ToString());
try
{
//get the list based upon name.
//if list is not in spweb this will cause an exception
//to be thrown. This is Ok. so, just catch it and continue
//I know.. I know.. this is not good practice. But there is not
//a oWeb.ListExists() method or something like that.
SPList oList = oWeb.Lists[listName];
//list was found!
Console.WriteLine("LIST FOUND!");
return oList;
}
catch
{
//catch and continue
Console.WriteLine("List not found in web: " + oWeb.Url.ToString());
}
finally
{
//make sure to dispose of web
oWeb.Dispose();
}
}
}
catch (Exception e)
{
Console.WriteLine("Exception occured: {0}\r\n{1}", e.Message, e.StackTrace);
}
finally
{
oSite.Dispose();
}
}
}
return null;
}
Stay tuned for the next blog post where we take this list and iterate through the entries in the list for a specific view. In the third blog post we will store the entries in an excel spreadsheet. In the fourth blog post we will upload the excel spreadsheet to a document library, and in the fifth blog post we will put it all together to make it something useful.
Hope you found something helpful here. If you know of a better way to do something I’ve shown here PLEASE feel free to leave a comment and let us all know!
<UPDATE>
Part 2 is now up and can be found here: Getting Your Feet Wet Writing Code For SharePoint – Part 2 of 5
</UPDATE>
posted @ Friday, August 07, 2009 5:58 PM