I mentioned yesterday in my blog entry Are database pros relevant on the RESTful web? that I’d be doing more blogging related to cloud computing and here is the first one. it regards SQL Server Data Services (SSDS).
I’ve been using SSDS for a few months and during that time I’ve managed to create a lot of junk up in that there cloud….I’ve actually got 27 authorities hanging around, most of them empty, all of them gibberish. I figured it was time to start getting rid of some of them so I’ve spent part of today writing some C# code (not my strongpoint) that would do just that. Unfortunately I later found out that apparently deleting authorities is not currently possible so my efforts were pretty much in vain but I have produced some code that will enumerate a list of authorities so I thought it might be useful to share.
I’ve used LINQ To XML to achieve which is completely new ground for me – thanks to Simon for putting me on the right track. Without further ado here’s that code:
I’ve written it as a method that takes a username, password and service URI as parameters (at the time of writing the URI is https://data.beta.mssds.com/v1/). I also declare a variable in order to hold the list of authorities:
private static List<string> GetAuthorityList(string userName, string password, string serviceUri)
{
List<string> auths; //will be populated with a list of all my authorities
Its important to make sure I’m requesting a list of my authorities to I append “?q=” onto the end of the service URI which tells SSDS to return that list of authorities:
string authQuery = serviceUri + @"?q=";
Next we build an HTTP request using the service URI/username/password, specify that we want to GET the authorities, and fire the request off to SSDS:
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(authQuery);
request.Credentials = new NetworkCredential(userName, password);
request.Method = "GET";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
Next we get the response into a format that can be understood by LINQ To XML; an XML document (if you know of a more concise way of doing this then I’m all ears):
using (Stream rspStm = response.GetResponseStream())
{
using (StreamReader reader = new StreamReader(rspStm))
{
XmlReader xmlReader = XmlReader.Create(reader);
XDocument doc = XDocument.Load(xmlReader);
Now the clever LINQ To XML bit. We have to define the XML namespace (in variable “ns”) that SSDS uses and you can see where that gets used in our LINQ query before loading it into the List<string> that we created earlier:
XNamespace ns = @"http://schemas.microsoft.com/sitka/2008/03/";
IEnumerable<string> ids = from e in doc.Descendants(ns + "Authority").Descendants(ns + "Id")
select e.Value;
auths = ids.ToList<string>();
Lastly we return the enumerated list and close off the method:
Easy when you know how! Just to prove that it works I’ll let Intellisense do its stuff:
[I told you they were gibberish]
That is all. For completeness and easy of copy-and-paste here is the method in its entirety:
private static List<string> GetAuthorityList(string userName, string password, string serviceUri)
{
List<string> auths; //will be populated with a list of all my authorities
string authQuery = serviceUri + @"?q=";
HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(authQuery);
request.Credentials = new NetworkCredential(userName, password);
request.Method = "GET";
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (Stream rspStm = response.GetResponseStream())
{
using (StreamReader reader = new StreamReader(rspStm))
{
XmlReader xmlReader = XmlReader.Create(reader);
XDocument doc = XDocument.Load(xmlReader);
XNamespace ns = @"http://schemas.microsoft.com/sitka/2008/03/";
IEnumerable<string> ids = from e in doc.Descendants(ns + "Authority").Descendants(ns + "Id")
select e.Value;
auths = ids.ToList<string>();
}
}
return auths;
}
Hope that proves useful to someone!
-Jamie