locked
How to convert a DataTable to SPList RRS feed

  • Question

  • Hello,

    Is it possible to convert a datatable to SPList?

    Thanks

    Sunday, April 10, 2011 3:32 PM

Answers

  • Hi!

    This might be a little tricky if you have different datatypes in your datatable.

    But an example, using only text fields could be something like this.

    /* Create datatable */
          DataTable dt = new DataTable();
          dt.Columns.Add("Test1");
          dt.Columns.Add("Tets2");
    
          using (SPSite site = new SPSite("http://myurl"))
          {
            using (SPWeb web = site.OpenWeb())
            {
              SPList newList = null;
    
              try
              {
                Guid newListGuid = web.Lists.Add("TempList", "This is my temp list, it will be removed when used", SPListTemplateType.GenericList);
                newList = web.Lists[newListGuid];
    
                if (newList != null)
                {
                  /* Loop through the datatable and add columns to the list */
                  foreach (DataColumn dc in dt.Columns)
                  {
                    newList.Fields.Add(dc.ColumnName, SPFieldType.Text, false);
                  }
    
                  /* Update the list to get the new fields */
                  newList.Update();
    
                  /* Populate the list from the datatable */
                  SPListItem newItem = null;
                  foreach (DataRow row in dt.Rows)
                  {
                    /* Create a new item in the list */
                    newItem = newList.AddItem();
    
                    foreach (DataColumn dc in dt.Columns)
                    {
                      newItem[dc.ColumnName] = row[dc].ToString();
                    }
    
                    /* Save the item to the list */
                    newItem.Update();
                  }
                }
              }
              catch
              {
                /* Do some logging */
              }
              finally
              {
                if (newList != null)
                {
                  newList.Delete();
                }
              }
            }
          }
    

    I haven't had time to test this properly myself, but I think that it should be pretty close to what you want.

    After the Populate list with /* Populate the list from the datatable */ you have to do want you want with the list items.

    The finally statement will remove the list for you.

    Hope this helps.

    Good luck


    Sebastian
    • Marked as answer by Jakobian Monday, April 11, 2011 9:15 AM
    Monday, April 11, 2011 7:26 AM

All replies

  • No, I doubted that to convert Datatable to SPList.

    Instead, We have to loop through the datatable and add the rows to SharePoint List.

    We can get the Datatable from SPList,

    DataTable dt = SPList.Items.getDataTable()


    Regards,
    Shantha Kumar .T | MCPD - SharePoint Developer 2010 | MCITP - SharePoint Administrator 2010 |
    (B) Shantha Kumar's Blog | (T)Follow me | (C) IOTAP
    Sunday, April 10, 2011 3:47 PM
  • Then my next question will be: can I create a temporary SPList and put my items in it, and detroy it right after?

    Do you have an example how to fill this temporary SPList from DataTable?

    Thanks!

    Sunday, April 10, 2011 4:17 PM
  • Hi!

    This might be a little tricky if you have different datatypes in your datatable.

    But an example, using only text fields could be something like this.

    /* Create datatable */
          DataTable dt = new DataTable();
          dt.Columns.Add("Test1");
          dt.Columns.Add("Tets2");
    
          using (SPSite site = new SPSite("http://myurl"))
          {
            using (SPWeb web = site.OpenWeb())
            {
              SPList newList = null;
    
              try
              {
                Guid newListGuid = web.Lists.Add("TempList", "This is my temp list, it will be removed when used", SPListTemplateType.GenericList);
                newList = web.Lists[newListGuid];
    
                if (newList != null)
                {
                  /* Loop through the datatable and add columns to the list */
                  foreach (DataColumn dc in dt.Columns)
                  {
                    newList.Fields.Add(dc.ColumnName, SPFieldType.Text, false);
                  }
    
                  /* Update the list to get the new fields */
                  newList.Update();
    
                  /* Populate the list from the datatable */
                  SPListItem newItem = null;
                  foreach (DataRow row in dt.Rows)
                  {
                    /* Create a new item in the list */
                    newItem = newList.AddItem();
    
                    foreach (DataColumn dc in dt.Columns)
                    {
                      newItem[dc.ColumnName] = row[dc].ToString();
                    }
    
                    /* Save the item to the list */
                    newItem.Update();
                  }
                }
              }
              catch
              {
                /* Do some logging */
              }
              finally
              {
                if (newList != null)
                {
                  newList.Delete();
                }
              }
            }
          }
    

    I haven't had time to test this properly myself, but I think that it should be pretty close to what you want.

    After the Populate list with /* Populate the list from the datatable */ you have to do want you want with the list items.

    The finally statement will remove the list for you.

    Hope this helps.

    Good luck


    Sebastian
    • Marked as answer by Jakobian Monday, April 11, 2011 9:15 AM
    Monday, April 11, 2011 7:26 AM
  • Hi,

    I guess it is better to use pure object model to retrieve the name of the fields and the we can use simple .net to create the datatable at run time. I doubt if there is any existing method is provided by microsoft..

    Thanks
    Bijay
    http://www.fewlines4biju.com  (A Sharepoint and Personal blogging and Tutorial Web Site)
    MCTS MOSS 2007

    Monday, April 11, 2011 7:43 AM
  • There is a method if you have a SharePoint list to get the datatable, SPList.GetDataTable, http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getdatatable.aspx

     


    Sebastian
    Monday, April 11, 2011 8:49 AM
  • Hello Sebastian,

    Many thanks for your help. That's what I'm trying to do now. However, I'm using the GetListTemplate to get the source list fields dynamically.

    I'm still wondering: there must be a way to do such operatinns with some more straight forward methods. For example with DataTables, you can easily get two datatables, create a third one, and merge them together, and pass it to your grid view . It's pretty simple.

    It's not possible to do the same with SPList...

    Anyway, I'll try to implement this and I will let you know.

    Thanks again!

     

     

    Monday, April 11, 2011 9:15 AM

  • Hey, I was trying to implement the above code but it fails here

    foreach (DataRow row in dt.Rows) { /* Create a new item in the list */ newItem = newList.AddItem(); foreach (DataColumn dc in dt.Columns) { newItem[dc.ColumnName] = row[dc].ToString();

    /* Save the item to the list */ newItem.Update(); } }

    this outer loop runs only once. What to do?

    Friday, September 11, 2015 10:02 AM