Get data from SharePoint list and store in SQL Table - C# Code

In previous blog, I wrote about "How to Export SharePoint list items in Excel using PowerShell script".

In this blog, we will see how to get SharePoint list items and store them in SQL table.
To do this you must have a SharePoint site with a list having few rows in it.
Now create a table similar to the list.
Note:
In SQL, table column name could be anything, because column mapping will be done in code below.
No. of columns and data type should be exactly the same as SharePoint list.
In my case, I kept most of the column type in SQL as nvarchar.

Create a new console application in Visual Studio and paste below code in Program.cs file.

class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SPSite site = new SPSite("<SharePoint Site URL>"))
                {
                    using (SPWeb myWeb = site.OpenWeb())
                    {
                        SPList target = myWeb.Lists["MyList"];
                        SPListItemCollection items = target.GetItems();
                        DataTable dt = items.GetDataTable();

                        if (dt.Rows.Count > 0)
                        {
                            string consString = "SQL Connection String";
                            using (SqlConnection con = new SqlConnection(consString))
                            {
                                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                                {
                                    sqlBulkCopy.DestinationTableName = "Table1";
                                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                                    sqlBulkCopy.ColumnMappings.Add("LinkTitle", "Title");
                                    sqlBulkCopy.ColumnMappings.Add("EmpName", "EmployeeName");
                                    sqlBulkCopy.ColumnMappings.Add("Dept", "Department");
                                    con.Open();
                                    sqlBulkCopy.WriteToServer(dt);
                                    con.Close();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
            }
        }
    }


Make sure you change the platform target to x64 in project properties.

Comments

Popular

SharePoint Interview Questions and Answers

Calling ASP.Net WebMethod using jQuery AJAX

What is Cloud Computing - A guide for beginners

How to Crop and Upload image using JQuery