How to use a C# Timer Job to grab SharePoint data and insert it into a SQL database

Want to look like a SharePoint guru?  You need to know how to work around SharePoint's numerous limitations.  Creating timer jobs that utilize SharePoint data outside of SharePoint workflows is a necessity for any medium to large sized business with lots of data.  Most consulting companies will charge you $5k - $10k to create a simple timer job.  Here's exactly how to create one fast and easy!

This is a step by step guide to creating a C# timer job that connects to a SharePoint list and then inserts this data into a SQL database table.  To start you will open Microsoft Visual Studio and create a console application in C#.  Next make sure it is set to x64 by default, if not you will get errors when connecting to the SharePoint list:

If x86 is selected (this is the default setting) you will not be able to retrieve SharePoint list data – it will error out and not be able to find the lists.  To change this setting, select the small arrow to the right of the configuration settings box.  Then select x64 for the active solution platform and select close.

Next make sure you have the necessary references.  Click on the Solution Explorer, right click on References and select Add Reference. 

A new window will appear.  Select .NET and then find the references you will need.  Because you are connecting to a SharePoint list you will need several SharePoint related references (Utilities, Client, Security, Business data, Shared Services and Workflow).  As this timer job will connect with SQL we will also need the SQL data client.  Below are all of the references we will be using for this timer job:

Next you will create a try/catch statement to catch errors like shown below – this will catch any errors and display them to the console.

Now, as for the actual code, first we want to access the SharePoint list. This is done by using SPSite,SPWeb, SPList, SPQuery, a CAML query and SP ListItemCollection.  These are defined as per below:

To access all the items in the list and then filter based upon the CAML query we will need the above code and then a CAML query that correctly filters the data to pull back exactly what you want.  In this case we are pulling back all items that are not null.  You could also use greater than or equal to <geq></geq>, etc…  There are numerous free resources on the internet for building your CAML query so I will not go into depth on that here.

Next I am going to access the database table HOURSOFF and truncate it so that all old data is removed but the structure will stay (quickest way to delete the data in a table).  This is similar to the above code for accessing the SharePoint list with a few differences.  You have to pass the permissions and a SQL string (in this case truncate table HOURSOFF_temp.  You also have to execute each SQL statement you want run against the database.

Next you will want to do a loop to read through all the items in the list and write them into variables (in this case we will do a foreach loop).  As there will be many items and each item will have several fields we will use arrays.  Before we start with reading from each item in the list, we want to count the total number of items in the list so we can properly set the size of our arrays.  This can be done with a simple foreach loop.  As seen in the below code, the resulting totalnumber variable will hold the total number of items in the list.  We can then use this to set our array variables:

Next we will declare the array variables so we can read each item in the SharePoint list in to them.  To make the code read easier I am renaming the totalnumber variable to p. This way, the array size is not hardcoded and will not need to be changed and republished as the list grows in size.

Now comes the foreach loop that reads in all the items we want in the list and the fields for each of these items.  You will see right before the loop a variable n is set to zero.  This will be the counter variable so that each field from each item is correctly recorded in the arrays.  This way they will all go over to the SQL database table correctly and in the proper order.

In the above code you will the n=n+1 line which adds 1 to the counter variable (n) so that this foreach loop goes systematically through each item in the list that meets the filter requirements set in the earlier CAML query.  mysourceListItem is the command to access the list items.  Each field will need its own instance as seen above (fields ID, StartDateTimeSQl, etc…).  Also, there will be fields that will return data that will be objects (this is common in SharePoint).  So, you will have to convert them to string or similar (ex. .ToString()).  In the case of Days[n] I needed to remove the first 6 characters as they contain the fieldtype and ;#. 

Next, we have to account for nulls.  Nulls can cause the timer job to error out.  The below code shows you exactly how to convert nulls into blank (“ “).  This has to be repeated for each field that will or could have nulls and placed inside the above foreach loop.

Next we will do a do-while loop and place a second try/catch all inside of the foreach loop above.  In this case, we want the timer job to update the SQL database at least one time hence the do-while.  Below is the whole snippet of code.

The above code includes the SQL command to insert 4 values per line into the database table HOURSOFF_temp.  This is done with 4 parameters @ADUSERID, @DATEOFF, @REASONOFF, and @HOURSOFF).  This uses the previously established myDBConnection and then ends with the execution command (cmd.ExecuteNonQuery).  Without the execute command nothing will be written into the SQL table.  The console writeline statements in the catch section are worded to write any errors to the console by line number so they can quickly be identified and corrected.  This is followed by an i++ which auto increments based on the variable I which is declared at the start of the do while loop (i is used to determine how many times this loop will run (<convert.todouble(Days[n]).  If you look at the original array variable declarations at the start of this document, you will see that Days[n] is a string array.  To compare it to the variable i it needs to be converted to a number and a double works best here hence the convert.todouble.

Net, you have to run it and test it.  Click the play button to run the code:

After that, make your changes to the urls to reflect your production domains and database names, then build the .exe file by selecting build from the Visual Studio toobar.  Then select Build Solution. 

Once the solution is built, the location of the solution and whether or not the compile was successful will display in the Output window at the bottom.

That is basically about it.  Now you just need to schedule the .exe file on the production server.  This will need to be tweaked for different list urls, SQL databases and table names.  You can also expand to include more or fewer fields. This is just one example of what can be done with a timer job to get around SharePoint's numerous limitations.  Once you have done this you can already probably envision what else can be done (updating another SharePoint list, writing data daily to an Excel spreadsheet, automated email workflows that can include both internal and external emails, complex automated approval processes, data manipulation (ETL (Extract, Transform and Load) processes), etc... - the sky is the limit, especially when you take the data outside of SharePoint).

Thank you for taking the time to read this.  If you found this how to article helpful, please like and share so that others may benefit as you have.

I would also like to take a moment to thank God as he is the one that makes everything possible!

"If you had faith even as small as a mustard seed, you could say to this mulberry tree, "May you be uprooted and thrown into the sea," and it would obey you!  Luke 17:6

David Maillie is a technology aficionado and sales superstar available for motivational speaking, training and education to groups, businesses and charities. 

Other great posts and content from David Maillie that you will definitely want to see:

How to fix SAP Crystal Reports not running - common issue

How to access SharePoint data in SAP Crystal Reports!

How to copy SharePoint data quickly without any code!

How to create, find and fix alerts in SharePoint

What is the difference between a SharePoint list and library?

Why most sales training does not work!

For convenience the full code for the above C# timer job is included below: