SharePoint 2010 has some interesting things you can do with it. In this blog I will show you how to export a SharePoint list or lists to a SQL database.

Problem:
We had to do some enhancements on a big SharePoint 2010 solution designed by someone else. The design of the site was done with info path forms and a lot of custom coding. The problem is that for each client that gets added to the solution it creates a sub site for the specific client.

On these clients you can then add a request which in return adds a sub site to the client sub site. The problem with this is that it is very difficult to do reporting on the SharePoint 2010 solution.

We had to do a full report as to see when a request status changed, who’s the client and a lot more which is quite difficult with all these subsides.

Solution:
Exporting Microsoft SharePoint 2010 list to SQL server database is fact not that difficult to do. To be able to do very nice reports on the system we had to export the Microsoft SharePoint 2010 lists to a SQL Server Database. We ended up moving all the lists contained in the solution to a database. In part 2 I will show you the end product with all the nice graphs, lists and views.

This is what I came up with. First of all you need to decide what you are going to use to actually get the data from SharePoint 2010 to a SQL database. We used web part but you can use a console window, timer job, or any other method that is available.

We started off by creating a new visual web part in Visual Studio 2010. I’m not going into too much detail on how to do everything; I would like more just to show you the actual code exporting the data.

I create a class with the following in. You will see there is one big part that is commented out; you can re-enable that if you need only certain lists to be extracted.

I have also added a few comments in the code to explain what I’m doing.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
 
namespace CustomReportList.Library
{
    public class ListRetieval
    {
        private readonly DataSet _getAllTables = new DataSet();
 
        /// 
        /// This method is the initial method that should be called.  
        /// It will check through the lists on the main sites and then move on to the sub sites, and then the sub sub site.
        /// 
        ///
        public void ExportData(string spSiteUrl)
        {
            try
            {
                //connection to the database.  GetSharePointSqlServerName will return the current sharepoint sql database name
                string connectionString = "Data Source=" + GetSharePointSqlServerName() +
                                          ";Initial Catalog=testDB;  Integrated Security=SSPI;";
 
                if (!string.IsNullOrEmpty(connectionString))
                {
                    //Export all SharePoint 2010 lists on the main site to the SQL Server Database
                    using (var connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        //call to the actual export function, m is the prefix used on the tables that will be created
                        ExportMainSites(spSiteUrl, connection, true, "m");
                        connection.Close();
                    }
 
                    //Export all SharePoint 2010 sub-site lists to the SQL Server Database
                    using (var connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        //Do a select statement on the mainsite to retrieve additional sub-sites URL's and then loop through them 
                        DataTable dataTableSites =
                            DbQuery(
                                "SELECT  ClientSite FROM m_Clients WHERE (ClientSite &lt;&gt; '') AND (ClientSite IS NOT NULL)",
                                connection);
 
                        foreach (DataRow row in dataTableSites.Rows)
                        {
                            //This will get the site url, it is divided by SharePoint 2010 into "http://sitename, site Description"
                            string clientSite = row[0].ToString().Split(',')[0];
                            string clientName = row[0].ToString().Split(',')[1];
 
                            //s is the prefix for the table
                            ExportSites(clientSite, "s", clientName);
                        }
 
                        for (int i = 0; i &lt; _getAllTables.Tables.Count; i++)
                        {
                            //Get the column names and datatypes to create the tables in sql
                            string[] columnNames = (from DataColumn x in _getAllTables.Tables[i].Columns
                                                    select x.ColumnName).ToArray();
 
                            string[] columnTypes = (from DataColumn x in _getAllTables.Tables[i].Columns
                                                    select x.DataType.ToString()).ToArray();
 
                            //Drop the table and then recreate it.  Do a bulk insert into the database
                            DropTable(connection, _getAllTables.Tables[i].TableName);
                            CreateTable(connection, _getAllTables.Tables[i].TableName, columnNames, columnTypes);
 
                            BulkCopy(connection, _getAllTables.Tables[i].TableName, _getAllTables.Tables[i]);
                        }
 
                        connection.Close();
                    }
 
                    _getAllTables.Clear();
                    _getAllTables.Reset();
 
                    //Export all SharePoint 2010 sub-sub-sites lists to the SQL Server Database
                    using (var connection = new SqlConnection(connectionString))
                    {
                        connection.Open();
                        //Do a select statement on the mainsite to retrieve additional sub-sites URL's and then loop through them 
                        DataTable dataTableCases =
                            DbQuery("SELECT  CaseSite FROM m_Cases WHERE CaseSite IS NOT NULL AND CaseSite &lt;&gt; ''",
                                    connection);
 
                        foreach (DataRow row in dataTableCases.Rows)
                        {
                            //This will get the site url, it is divided by SharePoint 2010 into "http://sitename, site Description"
                            string clientSite = row[0].ToString().Split(',')[0];
                            string clientName = row[0].ToString().Split(',')[1];
 
                            //s is the prefix for the table
                            ExportSites(clientSite, "c", clientName);
                        }
 
                        for (int i = 0; i &lt; _getAllTables.Tables.Count; i++)
                        {
                            //Get the column names and datatypes to create the tables in sql
                            string[] columnNames = (from DataColumn x in _getAllTables.Tables[i].Columns
                                                    select x.ColumnName).ToArray();
 
                            string[] columnTypes = (from DataColumn x in _getAllTables.Tables[i].Columns
                                                    select x.DataType.ToString()).ToArray();
                            //Drop the table and then recreate it.  Do a bulk insert into the database
                            DropTable(connection, _getAllTables.Tables[i].TableName);
                            CreateTable(connection, _getAllTables.Tables[i].TableName, columnNames, columnTypes);
 
                            BulkCopy(connection, _getAllTables.Tables[i].TableName, _getAllTables.Tables[i]);
                        }
                        connection.Close();
                    }
                }
            }
            catch (Exception e)
            {
                //Write to ULS logs
                WriteToLog.Write(e);
            }
        }
 
        /// 
        /// Method to connect to SQL Database and then returns a datatable
        /// 
        ///
        ///
        /// 
        public DataTable DbQuery(string sqlQuery, SqlConnection connection)
        {
            var da = new SqlDataAdapter();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sqlQuery;
            da.SelectCommand = cmd;
            var ds = new DataSet();
            da.Fill(ds);
 
            DataTable customerTable = ds.Tables[0];
            return customerTable;
        }
 
        /// 
        /// Method that will extract sub-sites from SharePoint 2010
        /// Table data is stored in _getAllTables to then bulk insert when it is ready
        /// 
        ///
        ///
        ///
        private void ExportSites(string spSiteUrl, string prefix, string clientName)
        {
            //bool tableExists = false;
            using (var objSite = new SPSite(spSiteUrl))
            {
                using (SPWeb currentWeb = objSite.OpenWeb())
                {
                    //This is used to add a specific column to the tables created.
                    string tableToCreate = prefix == "c" ? "CaseName" : "ClientName";
 
                    //Re-anable this part to specify certain lists to export
 
                    ////string[] listToCall = prefix == "c"
                    ////                          ? new string[5]
                    ////                                {
                    ////                                    "Engagement", "Case History", "Shared Documents", "Tasks",
                    ////                                    "Form Templates"
                    ////                                }
                    ////                          : new string[4] { "Background Checks", "Shared Documents", "Form Templates", "Tasks" };
 
                    ////foreach (string t in listToCall)
                    ////{
                    ////    bool listExists;
                    ////    try
                    ////    {
                    ////        SPList oList = currentWeb.Lists[t];
                    ////        listExists = true;
                    ////    }
                    ////    catch
                    ////    {
                    ////        listExists = false;
                    ////    }
 
                    ////    if (listExists)
                    ////    {
                    ////        SPList oList = currentWeb.Lists[t];
                    ////        SPListItemCollection dfList = oList.Items;
 
                    //first of all get the data of all lists in the system
                    foreach (SPList dfList in currentWeb.Lists)
                    {
                        try
                        {
                            //if (!oList.Hidden)
                            //{
                            ////DataTable sourceTable = dfList.GetDataTable();
                            DataTable sourceTable = dfList.Items.GetDataTable();
                            if (sourceTable != null)
                            {
                                //This will skip tables if you are not using the above specific lists part.
                                //This function being called have all the tables in that needs to be skipped
                                if (TablesToSkip(CleanupSqlObjectName(dfList.Title)))
                                {
                                    //clean table name if required
                                    string tableName = CleanupSqlObjectName(prefix + "_" + dfList.Title);
                                    sourceTable.TableName = tableName;
 
                                    //Add new column and loop throught the datatable and update the value toi current client
                                    sourceTable.Columns.Add(tableToCreate);
                                    if (_getAllTables.Tables[tableName] == null)
                                    {
                                        for (int i = 0; i &lt; sourceTable.Rows.Count; i++)
                                        {
                                            sourceTable.Rows[i][tableToCreate] = clientName;
                                        }
                                        _getAllTables.Tables.Add(sourceTable);
                                    }
                                    else
                                    {
                                        for (int i = 0; i &lt; sourceTable.Rows.Count; i++)
                                        {
                                            sourceTable.Rows[i][tableToCreate] = clientName;
                                        }
                                        _getAllTables.Tables[tableName].Merge(sourceTable);
                                    }
                                }
                            }
                            //}
                        }
                        catch (Exception e)
                        {
                            WriteToLog.Write(e);
                        }
                        //}
                    }
                    // BulkCopy(connection, tableName, sourceTable);
                }
            }
        }
 
        /// 
        /// This part will export the current SharePoint 2010 site lists to SQL server Database
        /// 
        ///
        ///
        ///
        ///
        private void ExportMainSites(string spSiteUrl, SqlConnection connection, bool dropTable, string prefix)
        {
            using (var objSite = new SPSite(spSiteUrl))
            {
                using (SPWeb currentWeb = objSite.OpenWeb())
                {
                    foreach (SPList dfList in currentWeb.Lists)
                    {
                        try
                        {
                            if (!dfList.Hidden)
                            {
                                DataTable sourceTable = dfList.Items.GetDataTable();
                                if (sourceTable != null)
                                {
                                    //clean table name if required
                                    string tableName = CleanupSqlObjectName(prefix + "_" + dfList.Title);
 
                                    //Get the column names and datatypes to create the tables in sql
                                    string[] columnNames = (from DataColumn x in sourceTable.Columns
                                                            select x.ColumnName).ToArray();
 
                                    string[] columnDataTypes = (from DataColumn x in sourceTable.Columns
                                                                select x.DataType.ToString()).ToArray();
 
                                    if (dropTable)
                                    {
                                        DropTable(connection, tableName);
                                    }
 
                                    //Create table and bulk insert into SQL Server Database
                                    CreateTable(connection, tableName, columnNames, columnDataTypes);
                                    BulkCopy(connection, tableName, sourceTable);
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            WriteToLog.Write(e);
                        }
                    }
                    // }
                }
            }
        }
 
        /// 
        /// Get the current database the SharePoint 2010 are using
        /// 
        /// 
        private static string GetSharePointSqlServerName()
        {
            string sServerName = "notFound";
            foreach (SPServer item in
                from item in SPFarm.Local.Servers
                from svc in item.ServiceInstances
                where svc is SPDatabaseServiceInstance
                let s = svc as SPDatabaseServiceInstance
                select item)
                sServerName = item.DisplayName;
 
            return sServerName;
        }
 
        /// 
        /// Cleans up the SharePoint 2010 names received from the lists.
        /// 
        ///
        /// 
        private static string CleanupSqlObjectName(String sqlObjectName)
        {
            var tempSb = new StringBuilder(sqlObjectName);
            // tempSb.Insert(0, "[");
            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
            }
            // tempSb.Append("]");
 
            return tempSb.ToString();
        }
 
        /// 
        /// This creates the table inside SQL Server
        /// 
        ///
        ///
        ///
        ///
        private void CreateTable(SqlConnection connection, string tableName, IList columns,
                                 IList dataTypes)
        {
            try
            {
                //first drop this table
                //now we need to create table
                var strBuilder = new StringBuilder();
                strBuilder.Append("CREATE TABLE " + tableName + " (");
 
                for (int i = 0; i &lt; columns.Count; i++)
                {
                    if (i == columns.Count)
                        strBuilder.Append("[" + columns[i] + "] " + dataTypes[i]);
                    else
                        strBuilder.Append("[" + columns[i] + "] " + dataTypes[i] + ", ");
                }
 
                strBuilder.Append(")");
 
                string finalSql = strBuilder.ToString();
                finalSql = renameAllDataTypes(finalSql);
 
                var cmd = new SqlCommand
                              {
                                  Connection = connection,
                                  CommandType = CommandType.Text,
                                  CommandText = finalSql
                              };
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                WriteToLog.Write(e);
            }
        }
 
        /// 
        /// Drops the table in SQL server
        /// 
        ///
        ///
        private void DropTable(SqlConnection connection, string tableName)
        {
            try
            {
                string finalSql = "Drop Table " + tableName;
 
                var cmd = new SqlCommand
                              {
                                  Connection = connection,
                                  CommandType = CommandType.Text,
                                  CommandText = finalSql
                              };
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                WriteToLog.Write(e);
            }
        }
 
        /// 
        /// Renames the datatypes to be used in a SQL query
        /// You might need to add your own lines here aswell
        /// 
        ///
        /// 
        private string renameAllDataTypes(string finalSql)
        {
            finalSql = finalSql.Replace("System.Int32", "INT");
            finalSql = finalSql.Replace("System.String", "NVARCHAR(MAX)");
            finalSql = finalSql.Replace("System.Double", "FLOAT");
            finalSql = finalSql.Replace("System.DateTime", "DATETIME");
            //finalSql = finalSql.Replace("", "");
 
            return finalSql;
        }
 
        /// 
        /// These are the tables that will be skipped
        ///  We had to use this on all sub-site to prevent doubling of data
        /// 
        ///
        /// 
        private bool TablesToSkip(string tableName)
        {
            switch (tableName)
            {
                case "Backup_CA":
                    {
                        return false;
                    }
                case "BackupCAMRecords":
                    {
                        return false;
                    }
                case "Cases":
                    {
                        return false;
                    }
                case "Clients":
                    {
                        return false;
                    }
                case "Content_and_Structure_Reports":
                    {
                        return false;
                    }
                case "Data_Connections":
                    {
                        return false;
                    }
                case "Documents":
                    {
                        return false;
                    }
                case "FormTemplates":
                    {
                        return false;
                    }
                case "Pages":
                    {
                        return false;
                    }
                case "RA__Approval_and_Risk_Classification_of_Services":
                    {
                        return false;
                    }
                case "Requests":
                    {
                        return false;
                    }
                case "Reusable_Content":
                    {
                        return false;
                    }
                case "Site_Assets":
                    {
                        return false;
                    }
                case "Site_Collection_Documents":
                    {
                        return false;
                    }
                case "Site_Collection_Images":
                    {
                        return false;
                    }
                case "Site_Pages":
                    {
                        return false;
                    }
                case "Status":
                    {
                        return false;
                    }
                case "TAX__Approval_and_Risk_Classification_of_Services":
                    {
                        return false;
                    }
                case "Style_Library":
                    {
                        return false;
                    }
                case "TestLibrary":
                    {
                        return false;
                    }
                case "WIP_View":
                    {
                        return false;
                    }
                case "WorkflowActions":
                    {
                        return false;
                    }
                case "WorkflowActivities":
                    {
                        return false;
                    }
                case "Workflows":
                    {
                        return false;
                    }
 
                default:
                    return true;
            }
        }
 
        /// 
        /// Bulk insert data into SQL server
        /// 
        ///
        ///
        ///
        private static void BulkCopy(SqlConnection connection, string tableName, DataTable sourceTable)
        {
            try
            {
                //now copy the data
                using (var bulkcopy = new SqlBulkCopy(connection))
                {
                    //Set destination table name
                    //to table previously created.
                    bulkcopy.DestinationTableName = tableName;
                    bulkcopy.WriteToServer(sourceTable);
                }
            }
            catch (Exception e)
            {
                WriteToLog.Write(e);
            }
        }
    }
}

using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using Microsoft.SharePoint; using Microsoft.SharePoint.Administration; namespace CustomReportList.Library { public class ListRetieval { private readonly DataSet _getAllTables = new DataSet(); /// /// This method is the initial method that should be called. /// It will check through the lists on the main sites and then move on to the sub sites, and then the sub sub site. /// /// public void ExportData(string spSiteUrl) { try { //connection to the database. GetSharePointSqlServerName will return the current sharepoint sql database name string connectionString = "Data Source=" + GetSharePointSqlServerName() + ";Initial Catalog=testDB; Integrated Security=SSPI;"; if (!string.IsNullOrEmpty(connectionString)) { //Export all SharePoint 2010 lists on the main site to the SQL Server Database using (var connection = new SqlConnection(connectionString)) { connection.Open(); //call to the actual export function, m is the prefix used on the tables that will be created ExportMainSites(spSiteUrl, connection, true, "m"); connection.Close(); } //Export all SharePoint 2010 sub-site lists to the SQL Server Database using (var connection = new SqlConnection(connectionString)) { connection.Open(); //Do a select statement on the mainsite to retrieve additional sub-sites URL's and then loop through them DataTable dataTableSites = DbQuery( "SELECT ClientSite FROM m_Clients WHERE (ClientSite <> '') AND (ClientSite IS NOT NULL)", connection); foreach (DataRow row in dataTableSites.Rows) { //This will get the site url, it is divided by SharePoint 2010 into "http://sitename, site Description" string clientSite = row[0].ToString().Split(',')[0]; string clientName = row[0].ToString().Split(',')[1]; //s is the prefix for the table ExportSites(clientSite, "s", clientName); } for (int i = 0; i < _getAllTables.Tables.Count; i++) { //Get the column names and datatypes to create the tables in sql string[] columnNames = (from DataColumn x in _getAllTables.Tables[i].Columns select x.ColumnName).ToArray(); string[] columnTypes = (from DataColumn x in _getAllTables.Tables[i].Columns select x.DataType.ToString()).ToArray(); //Drop the table and then recreate it. Do a bulk insert into the database DropTable(connection, _getAllTables.Tables[i].TableName); CreateTable(connection, _getAllTables.Tables[i].TableName, columnNames, columnTypes); BulkCopy(connection, _getAllTables.Tables[i].TableName, _getAllTables.Tables[i]); } connection.Close(); } _getAllTables.Clear(); _getAllTables.Reset(); //Export all SharePoint 2010 sub-sub-sites lists to the SQL Server Database using (var connection = new SqlConnection(connectionString)) { connection.Open(); //Do a select statement on the mainsite to retrieve additional sub-sites URL's and then loop through them DataTable dataTableCases = DbQuery("SELECT CaseSite FROM m_Cases WHERE CaseSite IS NOT NULL AND CaseSite <> ''", connection); foreach (DataRow row in dataTableCases.Rows) { //This will get the site url, it is divided by SharePoint 2010 into "http://sitename, site Description" string clientSite = row[0].ToString().Split(',')[0]; string clientName = row[0].ToString().Split(',')[1]; //s is the prefix for the table ExportSites(clientSite, "c", clientName); } for (int i = 0; i < _getAllTables.Tables.Count; i++) { //Get the column names and datatypes to create the tables in sql string[] columnNames = (from DataColumn x in _getAllTables.Tables[i].Columns select x.ColumnName).ToArray(); string[] columnTypes = (from DataColumn x in _getAllTables.Tables[i].Columns select x.DataType.ToString()).ToArray(); //Drop the table and then recreate it. Do a bulk insert into the database DropTable(connection, _getAllTables.Tables[i].TableName); CreateTable(connection, _getAllTables.Tables[i].TableName, columnNames, columnTypes); BulkCopy(connection, _getAllTables.Tables[i].TableName, _getAllTables.Tables[i]); } connection.Close(); } } } catch (Exception e) { //Write to ULS logs WriteToLog.Write(e); } } /// /// Method to connect to SQL Database and then returns a datatable /// /// /// /// public DataTable DbQuery(string sqlQuery, SqlConnection connection) { var da = new SqlDataAdapter(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = sqlQuery; da.SelectCommand = cmd; var ds = new DataSet(); da.Fill(ds); DataTable customerTable = ds.Tables[0]; return customerTable; } /// /// Method that will extract sub-sites from SharePoint 2010 /// Table data is stored in _getAllTables to then bulk insert when it is ready /// /// /// /// private void ExportSites(string spSiteUrl, string prefix, string clientName) { //bool tableExists = false; using (var objSite = new SPSite(spSiteUrl)) { using (SPWeb currentWeb = objSite.OpenWeb()) { //This is used to add a specific column to the tables created. string tableToCreate = prefix == "c" ? "CaseName" : "ClientName"; //Re-anable this part to specify certain lists to export ////string[] listToCall = prefix == "c" //// ? new string[5] //// { //// "Engagement", "Case History", "Shared Documents", "Tasks", //// "Form Templates" //// } //// : new string[4] { "Background Checks", "Shared Documents", "Form Templates", "Tasks" }; ////foreach (string t in listToCall) ////{ //// bool listExists; //// try //// { //// SPList oList = currentWeb.Lists[t]; //// listExists = true; //// } //// catch //// { //// listExists = false; //// } //// if (listExists) //// { //// SPList oList = currentWeb.Lists[t]; //// SPListItemCollection dfList = oList.Items; //first of all get the data of all lists in the system foreach (SPList dfList in currentWeb.Lists) { try { //if (!oList.Hidden) //{ ////DataTable sourceTable = dfList.GetDataTable(); DataTable sourceTable = dfList.Items.GetDataTable(); if (sourceTable != null) { //This will skip tables if you are not using the above specific lists part. //This function being called have all the tables in that needs to be skipped if (TablesToSkip(CleanupSqlObjectName(dfList.Title))) { //clean table name if required string tableName = CleanupSqlObjectName(prefix + "_" + dfList.Title); sourceTable.TableName = tableName; //Add new column and loop throught the datatable and update the value toi current client sourceTable.Columns.Add(tableToCreate); if (_getAllTables.Tables[tableName] == null) { for (int i = 0; i < sourceTable.Rows.Count; i++) { sourceTable.Rows[i][tableToCreate] = clientName; } _getAllTables.Tables.Add(sourceTable); } else { for (int i = 0; i < sourceTable.Rows.Count; i++) { sourceTable.Rows[i][tableToCreate] = clientName; } _getAllTables.Tables[tableName].Merge(sourceTable); } } } //} } catch (Exception e) { WriteToLog.Write(e); } //} } // BulkCopy(connection, tableName, sourceTable); } } } /// /// This part will export the current SharePoint 2010 site lists to SQL server Database /// /// /// /// /// private void ExportMainSites(string spSiteUrl, SqlConnection connection, bool dropTable, string prefix) { using (var objSite = new SPSite(spSiteUrl)) { using (SPWeb currentWeb = objSite.OpenWeb()) { foreach (SPList dfList in currentWeb.Lists) { try { if (!dfList.Hidden) { DataTable sourceTable = dfList.Items.GetDataTable(); if (sourceTable != null) { //clean table name if required string tableName = CleanupSqlObjectName(prefix + "_" + dfList.Title); //Get the column names and datatypes to create the tables in sql string[] columnNames = (from DataColumn x in sourceTable.Columns select x.ColumnName).ToArray(); string[] columnDataTypes = (from DataColumn x in sourceTable.Columns select x.DataType.ToString()).ToArray(); if (dropTable) { DropTable(connection, tableName); } //Create table and bulk insert into SQL Server Database CreateTable(connection, tableName, columnNames, columnDataTypes); BulkCopy(connection, tableName, sourceTable); } } } catch (Exception e) { WriteToLog.Write(e); } } // } } } } /// /// Get the current database the SharePoint 2010 are using /// /// private static string GetSharePointSqlServerName() { string sServerName = "notFound"; foreach (SPServer item in from item in SPFarm.Local.Servers from svc in item.ServiceInstances where svc is SPDatabaseServiceInstance let s = svc as SPDatabaseServiceInstance select item) sServerName = item.DisplayName; return sServerName; } /// /// Cleans up the SharePoint 2010 names received from the lists. /// /// /// private static string CleanupSqlObjectName(String sqlObjectName) { var tempSb = new StringBuilder(sqlObjectName); // tempSb.Insert(0, "["); 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 } // tempSb.Append("]"); return tempSb.ToString(); } /// /// This creates the table inside SQL Server /// /// /// /// /// private void CreateTable(SqlConnection connection, string tableName, IList columns, IList dataTypes) { try { //first drop this table //now we need to create table var strBuilder = new StringBuilder(); strBuilder.Append("CREATE TABLE " + tableName + " ("); for (int i = 0; i < columns.Count; i++) { if (i == columns.Count) strBuilder.Append("[" + columns[i] + "] " + dataTypes[i]); else strBuilder.Append("[" + columns[i] + "] " + dataTypes[i] + ", "); } strBuilder.Append(")"); string finalSql = strBuilder.ToString(); finalSql = renameAllDataTypes(finalSql); var cmd = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = finalSql }; cmd.ExecuteNonQuery(); } catch (Exception e) { WriteToLog.Write(e); } } /// /// Drops the table in SQL server /// /// /// private void DropTable(SqlConnection connection, string tableName) { try { string finalSql = "Drop Table " + tableName; var cmd = new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = finalSql }; cmd.ExecuteNonQuery(); } catch (Exception e) { WriteToLog.Write(e); } } /// /// Renames the datatypes to be used in a SQL query /// You might need to add your own lines here aswell /// /// /// private string renameAllDataTypes(string finalSql) { finalSql = finalSql.Replace("System.Int32", "INT"); finalSql = finalSql.Replace("System.String", "NVARCHAR(MAX)"); finalSql = finalSql.Replace("System.Double", "FLOAT"); finalSql = finalSql.Replace("System.DateTime", "DATETIME"); //finalSql = finalSql.Replace("", ""); return finalSql; } /// /// These are the tables that will be skipped /// We had to use this on all sub-site to prevent doubling of data /// /// /// private bool TablesToSkip(string tableName) { switch (tableName) { case "Backup_CA": { return false; } case "BackupCAMRecords": { return false; } case "Cases": { return false; } case "Clients": { return false; } case "Content_and_Structure_Reports": { return false; } case "Data_Connections": { return false; } case "Documents": { return false; } case "FormTemplates": { return false; } case "Pages": { return false; } case "RA__Approval_and_Risk_Classification_of_Services": { return false; } case "Requests": { return false; } case "Reusable_Content": { return false; } case "Site_Assets": { return false; } case "Site_Collection_Documents": { return false; } case "Site_Collection_Images": { return false; } case "Site_Pages": { return false; } case "Status": { return false; } case "TAX__Approval_and_Risk_Classification_of_Services": { return false; } case "Style_Library": { return false; } case "TestLibrary": { return false; } case "WIP_View": { return false; } case "WorkflowActions": { return false; } case "WorkflowActivities": { return false; } case "Workflows": { return false; } default: return true; } } /// /// Bulk insert data into SQL server /// /// /// /// private static void BulkCopy(SqlConnection connection, string tableName, DataTable sourceTable) { try { //now copy the data using (var bulkcopy = new SqlBulkCopy(connection)) { //Set destination table name //to table previously created. bulkcopy.DestinationTableName = tableName; bulkcopy.WriteToServer(sourceTable); } } catch (Exception e) { WriteToLog.Write(e); } } } }

This is the result:

Hope you enjoyed this and have a look next week at the reporting that was done. Please feel free to contact me on Skype at corvitech

Download

5 Comments

SP Dev on January 23, 2014 at 5:22 pm

Geez!!

So much hard coding!

No checking or management of resources and memory

Bulky and messy!!
- Johan Meyer on February 5, 2014 at 8:04 am
  
  Hi SP Dev,
  
  That is correct. This was a test to see if will work for the reporting, if you would like the actual application that went into production we might be able to give you some of the code.
  
  Kind Regards
- Maverick on April 14, 2016 at 3:04 am
  
  Hi Johan good job. I sure appreciate it.
naresh on October 7, 2014 at 6:29 am

hi Johan,

shall you send me the code for actual application what you specified in your comment. And I am new to sharepoint.
I need to do one task in BCS. shall you give me the step by step guidance for doing task.

mt task is:

the application is already running by querying data from share point list. now i need change code to query data from sql data table.

for that i am planing that :

1. first transfer data from list to database
2. using bcs create dataconnection in visual studio. and creating a new list for end users data entry.
3. in code where ever caml query is there, i will replace with sql query.

pls sufggest is there any better way. and how to do ,in step by step.

Thanks in advance
Kavitha on May 13, 2016 at 11:39 am

Hi Johan,

We have a requirement Creating TimerJob based on that SQL database Table.

For example in sql i have four database(db1,db2,db3,db4) and sharepoint i have 4 custom list(list1,list2,list3,list4).

db1 having 4 columns(EMPID, name,address,Phone NUmber)

Same data are in sharepoint list1(Customlist Name : Employeedatabase) also but except PhoneNumber column.

So i need to add this Phone Number column to Employeedatabase custom list through External content type or timerjob.

when i add this External Content Type to Employeedatabase custom list PhoneNumber column should be added and Based on that EMPID in that same row PhoneNumber should be update.

How i can achieve this through code without using sharepoint designer?

This is just example like that we are having more database with more columns. Please anyone share sample code snippet or any link for this.

Thanks in advance.