Dump SharePoint Lists Data into SQL Server Tables using SqlBulkCopy

In one of our projects it is required to clean and normalize SharePoint Data and dump that data into SQL server so that any third party can consume that data for reporting purposes. For that we created a timer job that runs every night and does the job....  Here is the approach that we took for implementation


Create a SharePoint Timer job  and add these two references

using System.Data;
using System.Data.SqlClient;



class DatabaseDump : SPJobDefinition
    {
        // No Argument Constructor (required for the system internal operation)
        public DatabaseDump()
        {
        }

        public DatabaseDump(string JobName, SPWebApplication objWebApplication)
            : base(JobName, objWebApplication, null, SPJobLockType.Job)
        {

        }


        public override void Execute(Guid targetInstanceId)
        {

            SPWebApplication currentWebApplication = this.WebApplication;
            try
            {
                using (SPSite currentSite = currentWebApplication.Sites[0])
                {
                    //todo:call export program
                    ExportData(currentSite.Url);
                 }
            }
            catch (Exception ex)
            {
                //exception handling here
            }
        }
    }


Step 2 : Create a function for ExportData

public void ExportData(string SpSiteURL)
        {
            DataTable SourceTable;
            DataTable UserMappingTable;
            DataRow dr;
            DataRow drMapping;
            string tableName;
            string connectionString;
            SPQuery query;
            try
            {
                connectionString = "SQL SERV ER CONNECTION STRING";
                if (!string.IsNullOrEmpty(connectionString))
                {
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        if (connection != null)
                        {
                            using (SPSite objSite = new SPSite(SpSiteURL))
                            {
                                using (SPWeb currentWeb = objSite.OpenWeb())
                               {
                                    //first of all get the data of all lists in the system
                                    foreach (SPList DFList in currentWeb.Lists)
                                    {
                                        try
                                        {
                                            if (!DFList.Hidden)
                                            {
                                                foreach (SPView view in DFList.Views)
                                                {
//we created a view for the data to be exported but default view can also be used here

                                                    if (view.Title =="Reports View")
                                                    {
                                                        SourceTable = DFList.GetItems(view).GetDataTable();
                                                        if (SourceTable != null)
                                                        {
                                                            //clean table name if required
                                                            tableName = CleanupSQLOBjectName(DFList.Title);
                                                        //drop and create new table first or if you don't want to do this then truncate it first                  
                                       CreateTable(connection, tableName, SourceTable);
                                                            BulkCopy(connection, tableName, SourceTable);
                                                        }
                                                        else
                                                        {
                                                            //no data in this list
                                                            //we need to truncate the existing data for this list now
                                                            tableName = CleanupSQLOBjectName(DFList.Title);
                                                            TruncateTable(connection, tableName);

                                                        }
                                                        break;
                                                    }
                                                }
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                          //handle exception here
                                        }
                                    }
        }




   private string CleanupSQLOBjectName(String SQLObjectName)
        {
            StringBuilder tempSb = new StringBuilder(SQLObjectName);
            tempSb = tempSb.Replace("/", "_");
            //these are two types of dashes
            tempSb = tempSb.Replace("–", "_");
            tempSb = tempSb.Replace("-", "_");
            tempSb = tempSb.Replace("(", "_");
            tempSb = tempSb.Replace(")", "_");
            tempSb = tempSb.Replace(" ", "_");
            tempSb = tempSb.Replace("_x0020_", "_");
            tempSb = tempSb.Replace("_x002f_", "_");
            tempSb = tempSb.Replace("__", "_");
            if (tempSb.Equals("group"))
            {
                tempSb.Replace("group", "group_1");//"Group" is a reserved word
            }
            return tempSb.ToString();
        }




private void CreateTable(SqlConnection connection, string tableName, string[] columns, string[] dataTypes)
        {
            StringBuilder strBuilder;
        
            SqlCommand cmd;

            //first drop this table
            DropTable(connection, tableName);


            //now we need to create table
            strBuilder = new StringBuilder();
            strBuilder.Append("CREATE TABLE " + tableName + " (");

            for (int i = 0; i < columns.Length; i++)
            {
                strBuilder.Append("[" + columns[i] + "] " + dataTypes[i] + ", ");
            }

            strBuilder.Append(")");


            cmd = new SqlCommand();

            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strBuilder.ToString();
            cmd.ExecuteNonQuery();
        }

   private void BulkCopy(SqlConnection connection, string tableName, DataTable SourceTable)
        {
            //now copy the data
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
            {
                //Set destination table name
                //to table previously created.
                bulkcopy.DestinationTableName = tableName;
                bulkcopy.WriteToServer(SourceTable);
            }
        }


This will update all the data of sharepoint lists in a SQL server database. You can add code to normalize lookup columns, multivalue columns etc and can also extend it to create child tables for lookup data..

Let us know if somebody require any assistance in extending this code.