0

I have a DataTable having Some rows. Is there any way to execute CAML Query on it.

Or Alternatively, How can I convert this DataTable into a SPList so tha CAML can be executed on SPList.

private DataTable Process(DataTable d, string s)
{
    DataTable dd = d;
    SPQuery query = new SPQuery();
    query.Query = s;
    query.ViewAttributes = "Scope = 'RecursiveAll'";
    DataTable dtSearch = new DataTable();
    SPList Dest = new SPList();


    dtSearch = d.GetItems(query).GetDataTable();
    return dtSearch;
} 

In above Code d.GetItems can not be done. ???

Solution tried:

I tried this from technet.

private SPList dtConvertSPList(DataTable dt)
{
    SPList newList = null;
    using (SPSite site = new SPSite("http://sharepointvm"))
    {
        using (SPWeb web = site.OpenWeb())
        {



                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.Items.Add();
                        foreach (DataColumn dc in dt.Columns)
                        {
                            newItem[dc.ColumnName] = row[dc].ToString();
                            newItem.Update();

                        }


                    }
                }
           }
    }
    return newList;

}

But the problem being faced by me is: the outer for loop i.e.

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

                        }

                    }

this outer loop is executing only once out of surprise.

*Debug Findings: The DataTable passed to this function had 3358 records. newList is created Fine with all columns but only one row that too with incomplete information in its columns.

Improvement Needed:

The loop should work fine and where should i put the code to delete this newList after returnig it?

0

I guess newItem.update() should be outside of inner for loop.

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

Also why you want to run caml query on datatable? You can use datatable.select() to query on datatable.

  • Even that did not work . – ankit Sep 26 '15 at 10:45
  • Is you are getting any error message? Also why not you are directly do query on Datatable? – P S Sep 28 '15 at 4:07
  • How do i query dataTable with a single keyword on each row + all the columns. – ankit Oct 6 '15 at 11:36
  • I have not tried this, but i guess this is as per your requirement: datatables.net/reference/api/search() – P S Oct 6 '15 at 11:52

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.