SharePoint List => DataTable with Title names

Question

I'm accessing a SharePoint list like this:

#region Accessing share point
        Microsoft.SharePoint.Client.ClientContext cC = new Microsoft.SharePoint.Client.ClientContext(Link);
        cC.Credentials = System.Net.CredentialCache.DefaultCredentials;
        Microsoft.SharePoint.Client.List SPList = cC.Web.Lists.GetByTitle(ListName);
        cC.Load(SPList.Fields);
        Microsoft.SharePoint.Client.FieldCollection Fields = SPList.Fields;
        #endregion

Looping the fields, I have all the name information for the different fields. I can do that like this:

            int i = 0;
        foreach(Microsoft.SharePoint.Client.Field FL in Fields)
        {
            WriteLine("Index: " + i++ + "; Internal name: " + FL.InternalName + "; Static name: " + FL.StaticName + "; Title: " + FL.Title);
        }

The export looks like this:

Index 5; Internal name: Lieferant_x0020__x0028_Nummer_x0; Static name: Lieferant_x0020__x0028_Nummer_x0; Title: Lieferant (Nummer)

As you can see, sharepoint stores the name internal by using unicode for additional characters. Only in "FL.Title" the name is stored as the user can see it on the sharepoint list.
Now, I want to put this data in a DataTable and filter it. I can do it like this:

        Microsoft.SharePoint.Client.CamlQuery cQuery = new Microsoft.SharePoint.Client.CamlQuery();
        cQuery = Microsoft.SharePoint.Client.CamlQuery.CreateAllItemsQuery();
        Microsoft.SharePoint.Client.ListItemCollection LIC = SPList.GetItems(cQuery);
        cC.Load(LIC);
        cC.ExecuteQuery();
        dt = new DataTable();

        #region Create columns
        foreach (var Field in LIC[0].FieldValues)
        {
            dt.Columns.Add(Field.Key);
        }
        #endregion

        #region Reading data
        foreach (Microsoft.SharePoint.Client.ListItem It in LIC)
        {
            System.Data.DataRow row = dt.NewRow();

            foreach(var Field in It.FieldValues)
            {
                row[Field.Key] = Field.Value;
            }
            dt.Rows.Add(row);
        }
        #endregion

the problem is, that in the ListItemCollection, only the internalname with additional characters as Unicode are available. I also cannot Encode it, because SharePoint 2010 has only 32 characters for internal names leading to cut names which are not complete anymore.

Is there any posibility to get the Title from the Listcolumns in the KeyValuePair of the ListItem.FieldValues? Actually, the KeyValuePairs are built like this:

<br>
It.FieldValues.Key = //Column as Internal Name
It.FieldValues.Value = //Value

I need:

It.FieldValues.Key = //Column as Title
It.FieldValues.Value = //Value

Thank you in advance, Jan

CJens CJens · Accepted Answer · 2017-05-16T15:53:17

Ok, I found a solution to load the sharepoint list to a DataTable having the field.Title used as the columns Title.

Please find the code:

        private System.Data.DataTable GetDataFromSharePointWithFilter(System.Uri Link, string ListName)
    {
        #region GetDataFromSharePoint
        string connectionString = GenerateConnectionString(Link, ListName);
        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);
        conn.Open();

        string strSQL = "SELECT * FROM LIST";
        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(strSQL, conn);
        DataSet ds = new DataSet();

        ds.Locale = System.Globalization.CultureInfo.InstalledUICulture;
        System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cmd);
        da.Fill(ds);
        #endregion

        #region Transfer data to DataTable
        return ds.Tables[0];
        #endregion
        }

If I loop all the columns, their title is correct. But the content of the list is not stored correctly. In on Listcolumn, values like 000, 001, 008 or 0028 are stored - in textformat. And the code below is reading for example 008 as 45. In another column, a name is typed for example Schneider, Markus and my programm returns 24.

Does anybody has an idea, why?

EDIT: Found the error, you couldn't see. In the connection string, I had the option RetrieveIds=Yes;. That was the reason for my code to repeat the content wrong.

SharePoint List => DataTable with Title names

3 Answers