3

I am using sql to pull data from a specific table, is it possible to autopopulate certain columns in a sharepoint list with this data?

Currently I'm pulling the data and copy pasting into the list (in datasheet view) and would love to automate this process.

Ideally the data would only be populating columns C through H and columns A and B would still be manually editable but if that's not possible I can do without.

Thanks!

  • MS-Access can write to a linked SharePoint List .. and MS-Access does SQL – Danny '365CSI' Engelman Apr 13 '17 at 15:22
  • Are you tied to using something like SQL Server Management Studio to do this? If not, you can use ADO.NET and any of the SharePoint object models to get the data in... – Jeff Freeman Apr 13 '17 at 15:30
2

I'm assuming quite a bit here:

  • You are using SharePoint 2013.
  • You have access to the SharePoint CSOM.
  • You can leverage the CSOM in Visual Studios (2013).

If you meet those criteria, here's some code that should start you on your way to automating this task..

using Microsoft.SharePoint.Client;
using System.Data;
using System.Data.SqlClient;

namespace SharePointUtil
{
    public class QuickAndDirtyAutopopulateUtil
    {
        const string SHAREPOINT_C = "C";
        const string SQL_C = "C";
        const string SHAREPOINT_D = "D";
        const string SQL_D = "D";
        const string SHAREPOINT_E = "E";
        const string SQL_E = "E";
        const string SHAREPOINT_F = "F";
        const string SQL_F = "F";
        const string SHAREPOINT_G = "G";
        const string SQL_G = "G";
        const string SHAREPOINT_H = "H";
        const string SQL_H = "H";

        private readonly string _url;
        private readonly string _listTitle;
        private readonly string _connString;
        private readonly string _sqlQuery;

        public QuickAndDirtyAutopopulateUtil(string spWebUrl, string spListTitle, string connString, string sql)
        {
            _url = spWebUrl;
            _listTitle = spListTitle;
            _connString = connString;
            _sqlQuery = sql;
        }

        public void InsertIntoSharePoint2013UsingCSOM()
        {
            var context = new ClientContext(_url);
            var list = context.Web.Lists.GetByTitle(_listTitle);                
            var data = QuerySQL(_connString, _sqlQuery);

            foreach (DataRow row in data.Rows)
            {
                var itemCreateInfo = new ListItemCreationInformation();
                var newItem = list.AddItem(itemCreateInfo);

                newItem[SHAREPOINT_C] = row[SQL_C].ToString();
                newItem[SHAREPOINT_D] = row[SQL_D].ToString();
                newItem[SHAREPOINT_E] = row[SQL_E].ToString();
                newItem[SHAREPOINT_F] = row[SQL_F].ToString();
                newItem[SHAREPOINT_G] = row[SQL_G].ToString();
                newItem[SHAREPOINT_H] = row[SQL_H].ToString();
                newItem.Update();
            }                

            context.ExecuteQuery();
        }

        private static DataTable QuerySQL(string connString, string sql)
        {
            DataTable dt = null;

            using (SqlConnection conn = new SqlConnection(connString))
            {
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    dt = new DataTable();
                    dt.Load(reader);
                    reader.Close();
                }
            }

            return dt;
        }
    }
}

You'll need to change the SHAREPOINT_X to your SharePoint internal field names, the SQL_X to your SQL column names. I hope the rest is self-explanatory. Once that's done, you can use this POCO in a console app like...

namespace SharePointUtil
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string spWebUrl = "https://spurl";
            string spListTitle = "Documents";
            string connString = "server=(local);initial catalog=AdventureWorks;Integrated Security=SSPI";
            string sql = "SELECT * FROM table";

            var o = new QuickAndDirtyAutopopulateUtil(spWebUrl, spListTitle, connString, sql);
            o.InsertIntoSharePoint2013UsingCSOM();
        }
    }
}

I haven't thoroughly tested this, so please use at your own risk. Good luck!

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.