2009-02-03

Sql Data Services CRUD

Alright everyone--let's talk Sql Data Services. This is a wonderful concept, especially for publicly available, not-necessarily-relational data. Microsoft is stepping into the Cloud with this technology, and at this point, it definitely shows some promise.

If you're not already familiar with Sql Data Services, head over to Microsoft's Azure advertising to learn more.

http://www.microsoft.com/azure/default.mspx

I've been working on a basic CRUD wrapper for Sql Data Services, and I thought I'd share. A  lot of this code is derived from MSDN's sample code for SDS. As of this writing, SDS is not publically available, and this code may break if Microsoft changes SDS's interfaces. Take a look at the documentation here:


Please familiarize yourself with the new data model, first. Microsoft has opted to use an ACE [Authority, Container, Entity] model for SDS, and this is a different direction than the model you may be familiar with that's currently used by MS Sql Server.


Basically, an Entity is a single record of data. An Authority is like Sql Server Instance. And the confusing part... A Container maybe used like a table--or like a database. One Container can store multiple types of data--which may or may not have the same structure. Since Entities are uploaded in an XML format--all SDS cares is that an Entity is properly formatted Xml [that abides by the SDS schema]. So, one Container can have many Entities--and each of those entities maybe of very different types.

Well, here's the code:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Xml;

using System.IO;

using System.Net;


namespace ubercode

{

    public class DataServiceBase

    {

        protected virtual string EntityName

        {

           get;

        }

 

        // POST=create; PUT=update; DELETE=delete; GET=retrieve

        protected const string HttpGetMethod = "GET";

        protected const string HttpPutMethod = "PUT";

        protected const string HttpPostMethod = "POST";

        protected const string HttpDeleteMethod = "DELETE";

 

        protected string ContainerUri

        {

            get

            {

                return String.Format(ContainerUriTemplate, Authority, EntityName);

            }

        }

 

        protected const string ssdsContentType = "application/x-ssds+xml";

 

        protected String PerformQuery(string Query)

        {

            string queryUri = String.Format(ContainerUri + "?q='" + Query + "'");

            try

            {

                // Query container

                string xmlString = GetResult(queryUri);

 

                // process the return Xml Data

                return xmlString;

            }

            catch (WebException ex)

            {

                using (HttpWebResponse response = ex.Response as HttpWebResponse)

                {

                    if (response != null)

                    {

                        throw new Exception("Unexpected status code returned: " + response.StatusCode, new Exception(ReadResponse(response)));

                    }

                    else

                    {

                        // Process DNS name resolution error

                        throw new Exception(ex.Message);

                    }

                }

            }

        }

 

        public static string ReadResponse(HttpWebResponse response)

        {

            // Begin by validating our inbound parameters.

            if (response == null)

            {

                throw new ArgumentNullException("response", "Value cannot be null");

            }

 

            // Then, open up a reader to the response and read the contents to a string

            // and return that to the caller.

            string responseBody = "";

            using (Stream rspStm = response.GetResponseStream())

            {

                using (StreamReader reader = new StreamReader(rspStm))

                {

                    responseBody = reader.ReadToEnd();

                }

            }

 

            return responseBody;

        }

        // TODO: Make this configurable

        protected static string UserName = "";

        // TODO: Make this configurable

        protected static string Password = "";

        // TODO: Make this configurable

        protected static string Authority = "";

        // TODO: Make this configurable

        protected static string ContainerUriTemplate = "https://{0}.data.database.windows.net/v1/{1}";

 

        protected static string GetResult(string entityUri)

        {

            string data = null;

 

            if (string.IsNullOrEmpty(entityUri))

            {

                throw new ArgumentOutOfRangeException("entityUri");

            }

            // retrieve

            WebRequest request = HttpWebRequest.Create(entityUri);

            request.Method = HttpGetMethod;

            //request.ContentLength = 0;

            request.ContentType = ssdsContentType;

            request.Credentials = new NetworkCredential(UserName, Password);

 

            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())

            {

                data = ReadResponse(response);

                if (response.StatusCode != HttpStatusCode.OK)

                {

                    throw new Exception("Unexpected status code returned: " + response.StatusCode, new Exception("Error: " + data));

                }

            }

 

            return data;

        }

 

        public void DeleteEntity(string EntityUri)

        {

            try

            {

                // Create the request to send.

                WebRequest request = HttpWebRequest.Create(EntityUri);

                request.Credentials = new NetworkCredential(UserName, Password);

                request.Method = HttpDeleteMethod;

 

                // Get the response and read it in to a string.

                using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())

                {

                    if (response.StatusCode != HttpStatusCode.OK)

                    {

                        throw new Exception("Failed to delete the entity resource.");

                    }

                }

            }

            catch (WebException ex)

            {

                if (ex.Response != null)

                {

                    using (HttpWebResponse response = ex.Response as HttpWebResponse)

                    {

                        throw new Exception("Unexpected status code returned: " + response.StatusCode, new Exception(ex.Message));

                    }

                }

                else

                {

                    throw ex;

                }

            }

        }

 

        protected static string CreateEntity(string ContainerUri, string EntityPayload, string UniqueIdentifier)

        {

            //const string ssdsContentType = "application/x-ssds+xml";

            string entityUri = null;

 

            if (String.IsNullOrEmpty(ContainerUri))

            {

                throw new ArgumentOutOfRangeException(ContainerUri);

            }

 

            if (String.IsNullOrEmpty(EntityPayload))

            {

                throw new ArgumentOutOfRangeException(EntityPayload);

            }

 

            try

            {

                // Create the request to send.

                WebRequest request = HttpWebRequest.Create(ContainerUri);

                request.Credentials = new NetworkCredential(UserName, Password);

                // POST=create; PUT=update; DELETE=delete; GET=retrieve

                request.Method = HttpPostMethod;

                UTF8Encoding encoding = new UTF8Encoding();

                request.ContentLength = encoding.GetByteCount(EntityPayload);

                request.ContentType = ssdsContentType;


                // Write the request data over the wire.

                using (Stream reqStm = request.GetRequestStream())

                {

                    reqStm.Write(encoding.GetBytes(EntityPayload), 0,

                                 encoding.GetByteCount(EntityPayload));

                }


                // Get the response and read it in to a string.

                using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())

                {

                    if (response.StatusCode == HttpStatusCode.Created)

                    {

                        Console.WriteLine("Entity created. System assigned version: {0}", response.Headers["ETag"]);

                        // Since current implementation returns response.Headers[HttpResponseHeader.Location]

                        // value null, construct entity URI

                        entityUri = string.Format(ContainerUri + "/" + UniqueIdentifier);

                    }

                    else

                    {

                        throw new Exception("Unexpected status code returned: {0}" + response.StatusCode);

                    }

                }

            }

            catch (WebException ex)

            {

                using (HttpWebResponse response = ex.Response as HttpWebResponse)

                {

                    if (response != null)

                    {

                        throw new Exception(ReadResponse(response));

                    }

                }

            }

            return entityUri;

        }


        protected string UpdateEntity(string ContainerUri, string EntityPayload, string UniqueIdentifier)

        {

            string entityUri = EntityUri(ContainerUri, UniqueIdentifier);


            // Send updated entity back

            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(entityUri);

            request.Method = HttpPutMethod;

            UTF8Encoding encoding = new UTF8Encoding();

            request.ContentLength = encoding.GetByteCount(EntityPayload);

            request.ContentType = ssdsContentType;

            request.Credentials = new NetworkCredential(UserName, Password);

 

            // Write the request data over the wire.

            using (Stream reqStm = request.GetRequestStream())

            {

                reqStm.Write(encoding.GetBytes(EntityPayload), 0,

                             encoding.GetByteCount(EntityPayload));

            }


            string returnValue = "";


            // Get the response and read it in to a string.


            using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())

            {

                if (response.StatusCode == HttpStatusCode.OK)

                {

                    returnValue = response.Headers["ETag"];

                }

                else

                {

                    throw new Exception("Unexpected status code returned: " + response.StatusCode);

                }

            }

 

            return returnValue;

        }

 

        protected string EntityUri(string ContainerUri, string UniqueIdentifier)

        {

            return ContainerUri + "/" + UniqueIdentifier;

        }

    }

}

As always, code provided is without Warranty. This wrapper only handles the Post, Puts, Deletes, and Gets--how you create your payload [formatted Xml], how you handle your returned Xml, How you manage your data and business objects--that's still up to you. This should make it a little easier, though. You'll probably want to either inherit from this class--but you can adapt this code for your purposes.

Again, a lot of this is right from Microsoft's examples. If I get around to it, I might post an example of a business layer on top of this.

Enjoy!

No comments: