4

Is there any code sample to read the external SQL table data and create a list in SharePoint 2013, thanks in advance

5

It's a simple example via c# SSOM to retrieve Records from SQLdatabase table to existing SharePoint list

1. Create a function to get your data from SQL server and return it to 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;
    }

2. Create a function that loop for every item at the retrieved SQL data table and adds it to list.

     void binditems()
     {
         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"] = row["Title"].ToString();
            oSPListItem.Update();
            }
        }
     }
     }

Read the Function binditems(), to can bind the data from SQL to List

Note : don't forget to include this namespaces using System.Data.SqlClient; using Microsoft.SharePoint;

  • Hi Qassas, Connection string throws null reference. How to add sql connection string to SharePoint webconfig file – sam daniel Nov 10 '16 at 9:59
  • Hi @samdaniel should you share wha't is your provided connection string line ? for add sql connection string to SP web.config check this blogs.technet.microsoft.com/msjimblog/2012/07/31/… – Mohamed El-Qassas MVP Nov 10 '16 at 10:18
  • @Qassas The name 'dr["Title"]' does not exit in current context. How to fix this? thanks in advance – sam daniel Nov 10 '16 at 11:02
  • @samdaniel replace it with row to be row["Title"].ToString(); please don't hesitate to tell me if you need any clarification or face another issue – Mohamed El-Qassas MVP Nov 10 '16 at 11:05

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.