I need to migrate items from SQLdatabase table to SharePoint list. I know this can be achieved using a C# console, but I have no idea how to achieve this. (Need to implement this using C# code, I know BCS can be used, but i don't want to use that)
Any help on this will be appreciated. Thanks
Add these 2 namespaces :
using System.Data.SqlClient;using Microsoft.SharePoint;Add this code and call GetData method
private void GetData()
{
try
{
string query = "SELECT * FROM [DATABASENAME].[dbo].[TABLENAME] WHERE [ID] IS NOT NULL";
DataSet ds = getDataFromSQL(query, CommandType.Text);
if (ds != null && ds.Tables.Count > 0)
{
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count > 0)
{
using(SPSite site = new SPSite("siteUrl"))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists.TryGetList("");
if (list != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
SPListItem item = list.AddItem();
item["colName1"] = Convert.ToString(dr["colName"]);
item["colName2"] = Convert.ToString(dr["colName"]);
//... And so on
item.Update();
}
}
}
}
}
}
}
catch { }
}
private DataSet getDataFromSQL(string dbQuery, CommandType dbType)
{
DataSet dsToReturn = null;
try
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "your connection string here";
using (SqlConnection dsConnection = connection)
{
try { dsConnection.Open(); }
catch { }
using (SqlCommand dsCommand = new SqlCommand())
{
dsCommand.CommandType = dbType;
dsCommand.CommandText = dbQuery;
dsCommand.Connection = dsConnection;
SqlDataAdapter daToFillDataSet = new SqlDataAdapter(dsCommand);
dsToReturn = new DataSet();
daToFillDataSet.Fill(dsToReturn);
//dsConnection.Close();
//dsCommand.Dispose();
}
}
}
catch(Exception){
return dsToReturn;
}
return dsToReturn;
}
You will need to update connection string, database name, table name, column name
It's a simple example to retrieve items from SQLdatabase table to SharePoint list
Create a function to get your data from SQL server and return it as a data table.
// your method to pull data from database to datatable
public DataTable GetDatafromSQL()
{
DataTable dataTable = new DataTable();
string connString = @"your connection string here";
string query = "select * from table";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
conn.Close();
da.Dispose();
return dataTable;
}
Create a function that adds items to list and loop for every item at the retrieved SQL data table.
void Addnewitem()
{
DataTable dt = new DataTable();
dt= GetDatafromSQL();
using (SPSite oSite=new SPSite("http://mysharepoint"))
{
using (SPWeb oWeb=oSite.RootWeb)
{
SPList oList = oWeb.Lists["Test"];
foreach (DataRow row in dt.Rows) // Loop over the rows.
{
SPListItem oSPListItem = oList.Items.Add();
oSPListItem["Title"] = dr["Title"].ToString();
oSPListItem.Update();
}
}
}
Note: I considered that the data structure that will retrieve from SQL is already matched with List Structure.
you can follow this link
or you can syncronize the db table data to sharePoint list using BCS also Below link help you with that How to sync a SQL database with a SharePoint List?
