Like many ASP .NET developers, I’ve bound many data sources to a dropdown control over the years, using a database as a source, but I recently needed to do the same thing using a SharePoint list instead. Below is the C# code used to do so, with each line of code simply coming after the previous one.
First we need to connect to our SharePoint site using the SPSite object:
SPSite site = new SPSite(“http://ComputerName/SiteName”);
SPWeb web = site.OpenWeb();
Then we use the SPSiteDataQuery object to query the list using a CAML query. In this case, the list we want is called “Customers”, so we store this in the query’s list property.
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = string.Format(“<Lists><List ID=\”{0}\” /></Lists>”, web.Lists["Customers"].ID);
Now we’re going to indicate the fields we want to return.
query.ViewFields =“<FieldRef Name=\”Title\” /><FieldRef Name=\”ID\” />”;
Next we declare a DataTable and execute the query:
DataTable dt = web.GetSiteData(query);
Now all that’s left to do is bind the result to a droplist:
ddlCustomer.DataTextField =“Title”;
ddlCustomer.DataValueField = “ID”;
ddlCustomer.DataSource = dt;
ddlCustomer.DataBind();
If we wanted to restrict the rowset further with criteria, we could also use the query’s query property to add an XML formatted criteria. For example:
query.Query =“<Where><Eq><FieldRef Name=’ID’ /><Value Type=’Number’>” + sID + “</Value></Eq></Where>”;
In this case, we’re passing the ID of a list item record in as a parameter, resulting in only one row being returned. This query line simply needs to be added to the query object prior to execution. For example, to alter the code from earlier, we just drop it in between the other lines we’ve already written:
query.Lists = string.Format(“<Lists><List ID=\”{0}\” /></Lists>”, web.Lists["Customers"].ID);
query.Query = “<Where><Eq><FieldRef Name=’ID’ /><Value Type=’Number’>” + sID + “</Value></Eq></Where>”;
query.ViewFields = “<FieldRef Name=\”Title\” /><FieldRef Name=\”ID\” />”;
Happy coding!
