In the 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 the 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 the code below.
No. of columns and data type should be exactly the same as the 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.
Visit my blog for – SharePoint Tutorial , SharePoint Interview Questions and Answers, and SharePoint 2013 Interview Questions
You may like this – PowerShell Script to export SharePoint List in Excel File
You may read some popular blogs on SharePointCafe.Net
Keep following SharePointCafe.Net for upcoming blogs.
