1

I would need to import 2 columns of data from a SQL database into a SharePoint list? Is there a way to easily import data from a sql database to a SharePoint list?

2

BCS: Business connectivity Service allows us to connect external data and work with it from SharePoint.Just we need to create an external content type.

Getting started with Business Connectivity Services (BCS) in SharePoint 2010

PS: Steps are also same for SharePoint 2013

0
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;  
        }  
    }  
} 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.