Introduction
This post explains how to connect to an SQL table, fetching the records based on SQL query, then inserting them as items into SharePoint list by using Client Side Object Model in SharePoint online
Sometimes, it is required to load data from a SQL table to a SharePoint List. There are many ways to load data from SQL server to SharePoint List such as:
- Using Business Connectivity Services(BCS)
- Using Powershell Script
- Using CSOM
Here is how to insert items into SharePoint List, by using the Client Side Object Model(CSOM) in SharePoint Online. Please follow the below steps.
- Connect to the SharePoint Online site and get the reference of the SharePoint list.
- Connect to the SQL database using a connection string and execute the SQL query.
- Iterate through all the rows in the data table and create items in SharePoint List.
Pre-requisites( Environment Details)
- Microsoft SQL Server Management Studio 18
- Visual Studio 2017
- SharePoint online site has to be available, a list has to be created with the following example details
Products
| Column Name |
Column Type |
| ProductID |
Number |
| Name |
Single line of text |
| ProductNumber |
Single line of text |
Form the SQL query and execute inside the SQL server to make sure the desired results are retrieved.
SQL query
SELECT p.ProductID, p.Name, p.ProductNumber from Production.Product p where p.ProductID<500;
Output
![]()
Source Code
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SharePoint.Client;
namespace SqlDataConnection
{
class Program
{
static void Main(string[] args)
{
string userName = "<provide your username to connect to the SharePoint site>";
string password = "<provide your password>";
SecureString securePassword = new SecureString();
foreach (char c in password)
{
securePassword.AppendChar(c);
}
using (var clientContext = new ClientContext("https://test.sharepoint.com/sites/Practice/"))
{
// SharePoint Online Credentials
clientContext.Credentials = new SharePointOnlineCredentials(userName, securePassword);
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
List productList = web.Lists.GetByTitle("Products");
DataTable dt = new DataTable();
dt = GetDatafromSQL();
foreach (DataRow dr in dt.Rows) // Loop over the rows.
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem newItem = productList.AddItem(itemCreateInfo);
newItem["ProductID"] = dr["ProductID"];
newItem["Name"] = dr["Name"];
newItem["ProductNumber"] = dr["ProductNumber"];
newItem.Update();
clientContext.Load(newItem);
clientContext.ExecuteQuery();
}
clientContext.Load(productList);
clientContext.ExecuteQuery();
}
}
private static DataTable GetDatafromSQL()
{
DataTable dataTable = new DataTable();
string connString = @"Server=<provide server url>;Database=<Database Name>;Integrated Security=True";
string query = "SELECT p.ProductID, p.Name, p.ProductNumber from Production.Product p where p.ProductID<500;";
SqlConnection connection = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
connection.Close();
da.Dispose();
return dataTable;
}
}
}
1. Connect to SharePoint Online site and get the reference of SharePoint list
Create a console application using Visual Studio and reference the following SharePoint Assemblies in the solution.
- Microsoft.SharePoint.Client.dll
- Microsoft.SharePoint.Client.Runtime.dll
Source Code:
string userName = "<provide your username to connect to the SharePoint site>";
string password = "<provide your password>";
SecureString securePassword = new SecureString();
foreach (char c in password)
{
securePassword.AppendChar(c);
}
using (var clientContext = new ClientContext("https://test.sharepoint.com/sites/Practice/"))
{
// SharePoint Online Credentials
clientContext.Credentials = new SharePointOnlineCredentials(userName, securePassword);
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
List productList = web.Lists.GetByTitle("Products");
}
}
2. Connect to SQL database using connection string and execute the SQL query
The connection string has to be formed as shown below, In this case, I am using windows authentication, hence the connection string includes only Server, Database name, Integrated Security. If SQL Server authentication is used, then credentials also has to be provided along with other details to establish connection with the SQL Server. Once connection is established, load the SQL query and load the results into a data table.
Source Code
private static DataTable GetDatafromSQL()
{
DataTable dataTable = new DataTable();
string connString = @"Server=<provide Server URL>;Database=<provide database name>;Integrated Security=True";
string query = "SELECT p.ProductID, p.Name, p.ProductNumber from Production.Product p where p.ProductID<500;";
SqlConnection connection = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
connection.Close();
da.Dispose();
return dataTable;
}
3. Iterate through all the rows in the data table and create items in SharePoint List.
Source Code
DataTable dt = new DataTable();
dt = GetDatafromSQL();
foreach (DataRow dr in dt.Rows) // Loop over the rows.
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem newItem = productList.AddItem(itemCreateInfo);
newItem["ProductID"] = dr["ProductID"];
newItem["Name"] = dr["Name"];
newItem["ProductNumber"] = dr["ProductNumber"];
newItem.Update();
clientContext.Load(newItem);
clientContext.ExecuteQuery();
}
clientContext.Load(productList);
clientContext.ExecuteQuery();
Build and execute the application. Once it is executed successfully, you can see the data in the SharePoint List.