Reading SharePoint Lists into an ADO.Net DataTable, Updated

I've been integrating processes with SharePoint lists for some time now. I made a post back in September '08 about how to pull a SharePoint list into an ADO.NET DataTable.

A very kind reader contributed a much appreciated simplified solution. It actually worked for general cases, but didn't solve a fundamental problem (one of the reasons I posted my version of the code to begin with): What if you have a column with odd characters in the name, like "EntityA.Property1"?

Unfortunately, ADO.NET imports columns with certain character encodings as DBNull. I suspect this might be a bug, but the example below shows a very simple WPF form that uses the basic technique with a slight variation to work around the issue:

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using data = System.Data;
using xml = System.Xml;
namespace WindowsApplication1
{
///

/// Interaction logic for Window1.xaml
///

public partial class Window1 : System.Windows.Window
{

public Window1()
{
InitializeComponent();
}

public void OnLoaded(object sender, RoutedEventArgs args)
{
SharePointLists.Lists listsService = new WindowsApplication1.SharePointLists.Lists();
listsService.Url = "http://spsite/_vti_bin/lists.asmx";
listsService.Proxy = null;
listsService.UseDefaultCredentials = true;
xml.XmlNode listData =
listsService.GetListItems(
"Test List",
default(string),
null,
null,
default(string),
null,
default(string));

//Takes care of columns with names like "EntityA.Property1", gets rid of "ows_" prefix:
listData.InnerXml = listData.InnerXml.Replace("_x002e_", "_").Replace("ows_", "");

data.DataSet listDataSet = new data.DataSet();
xml.XmlNodeReader listDataReader = new xml.XmlNodeReader(listData);
listDataSet.ReadXml(listDataReader);
CollectionViewSource dataObj = (CollectionViewSource)Resources["Data"];
dataObj.Source = new data.DataView(listDataSet.Tables[1]);

}
}
}

Here's the XAML component:

<Window x:Class="WindowsApplication1.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="WindowsApplication1" Height="300" Width="300"
Loaded="OnLoaded"
>

<Window.Resources>
<CollectionViewSource x:Key="Data" />
</Window.Resources>

<Grid DataContext="{StaticResource Data}">
<ListBox ItemsSource="{Binding}" DisplayMemberPath="EntityA_Property1"/>
</Grid>

</Window>

WORKAROUND: Misconfigured Windows-Integrated Authentication for Web Services

In trying to drive a process from a SharePoint list, I ran across a problem...

I couldn't create a web reference in my C# project due to some really weird problem... In the "Add web reference" wizard, I entered my URL, and was surprised by a pop-up titled "Discovery Credential", asking me for credentials for the site.

Since I was on the local domain and had "owner" permissions to the site, I thought I would just waltz in and get the WSDL.

Ok, so it wants creds... I gave it my own.

Negative...!?!?

After a few attempts and access denied errors, I hit Cancel, and was rewarded by, of all things, the WSDL display... but I still couldn't add the reference.

After quite a bit of wrestling, it turns out there was an authentication provider configuration problem. The site was configured to use Kerberos authentication, but the active directory configuration was not set up correctly. (I believe it needed someone to use SetSPN to update the Service Principal Name (SPN) for the service.)

One way to resolve the problem was to set the authentication provider to NTLM, but in my case, I didn't have, (and wasn't likely to get) that configuration changed in the site (a SharePoint Web Application) I really needed access to.

In order to make it work, I had to initially create my reference to a similar, accessible site.

(e.g. http://host/sites/myaccessiblesite/_vti_bin/lists.asmx )

Then, I had to initialize the service as such, in code:

private void InitWebService()
{
  System.Net.AuthenticationManager.Unregister("Basic");

  System.Net.AuthenticationManager.Unregister("Kerberos");

  //System.Net.AuthenticationManager.Unregister("Ntlm");

  System.Net.AuthenticationManager.Unregister("Negotiate");

  System.Net.AuthenticationManager.Unregister("Digest");

  SmokeTestSite.Lists workingLists = new SmokeTest.SmokeTestSite.Lists();

  workingLists.Url = "http://host/sites/mybrokensite/_vti_bin/lists.asmx";

  workingLists.UseDefaultCredentials = true;

  workingLists.Proxy = null;

  lists = workingLists;
}

What this accomplishes is it unregisters all authentication managers in your application domain. (This can only be done once in the same app domain. Attempts to unregister the same manager more than once while the program's running will throw an exception.)

So by having all the other authentication managers disabled in the client, the server would negotiate and agree on Ntlm authentication, which succeeds.