Loading Data Into a SharePoint List from SQL in SharePoint Online using CSOM

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.