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