Introduction
The way I previously created messages to send to the GovTalk service I used the XMLDocument to create the request. While this worked it left a number of problems; not least that for every message a special function would need to created. This is OK for the short term but the biggest cost in any software project is maintenance and this would be a headache to maintain.
So the following is a somewhat better way of achieving the same thing. For the purposes of this article I am going to be using the CompanyNumberSearch request of the GovTalk service – although this technique would work for any service that accepted XML. The C# functions which send and receive the messages remain the same. The magic sauce in this is the XSLT which defines the structure of the request, and the use of objects in conjunction with reflection to provide the content. It is a bit like Sweet Chilli Sauce added to Chicken on a bed of rice. So on to the Sweet Chilli Sauce
The Sweet Chilli Sauce
The request to search for a company based on it’s number is as follows;
<GovTalkMessage xsi:schemaLocation="http://www.govtalk.gov.uk/CM/envelope http://xmlgw.companieshouse.gov.uk/v1-0/schema/Egov_ch-v2-0.xsd" xmlns="http://www.govtalk.gov.uk/CM/envelope" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#" xmlns:gt="http://www.govtalk.gov.uk/schemas/govtalk/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<EnvelopeVersion>1.0</EnvelopeVersion>
<Header>
<MessageDetails>
<Class>NumberSearch</Class>
<Qualifier>request</Qualifier>
<TransactionID>1</TransactionID>
</MessageDetails>
<SenderDetails>
<IDAuthentication>
<SenderID>????????????????????????????????</SenderID>
<Authentication>
<Method>CHMD5</Method>
<Value>????????????????????????????????</Value>
</Authentication>
</IDAuthentication>
</SenderDetails>
</Header>
<GovTalkDetails>
<Keys/>
</GovTalkDetails>
<Body>
<NumberSearchRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xmlgw.companieshouse.gov.uk/v1-0/schema/NumberSearch.xsd">
<PartialCompanyNumber>99999999</PartialCompanyNumber>
<DataSet>LIVE</DataSet>
<SearchRows>1</SearchRows>
</NumberSearchRequest>
</Body>
</GovTalkMessage>
This is the XML that we send to the GovTalk Service and we get back a list of companies that match the criteria passed
A message is structured in two parts; The envelope which identifies the person sending the request, with the name of the request, and the body which gives the detail of the company we are looking for.
The Chilli
What makes it possible is the use of XSLT to define the message – and serialization to convert each request object into XML. To start we need to create an object which will represent the contents of the message we are sending. However there is a common properties in all the messages that we send to Companies House. These properties are as follows
- SenderId – the id of the person sending the message
- SenderPassword – the password associated with Id
- TransactionId – Unique identifier for the message
- AuthenticationValue – authenticates the request
Because these properties are unique to the Companies House message, and because they are shared with all messages they are perfect candidates for a base class.
The class is as follows;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.WindowsAzure.ServiceRuntime;
namespace CompanyHub.Services
{
public class GovTalkRequest
{
public GovTalkRequest()
{
try
{
SenderID = RoleEnvironment.GetConfigurationSettingValue("SenderId");
SenderPassword = RoleEnvironment.GetConfigurationSettingValue("SenderPassword");
TransactionId = DateTime.Now.Ticks.ToString();
AuthenticationValue = EncodePassword(String.Format("{0}{1}{2}", SenderID, SenderPassword, TransactionId));
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// returns the Sender ID to be used when communicating with the GovTalk Service
/// </summary>
public String SenderID
{
get;
set;
}
/// <summary>
/// return the password to be used when communicating with the GovTalk Service
/// </summary>
public String SenderPassword
{
get;
set;
} // end SenderPassword
/// <summary>
/// Transaction Id - uses the Time and Date converted to Ticks
/// </summary>
public String TransactionId
{
get;
set;
} // end TransactionId
/// <summary>
/// calculate the authentication value that will be used when
/// communicating with
/// </summary>
public String AuthenticationValue
{
get;
set;
} // end AuthenticationValue property
/// <summary>
/// encodes password(s) using MD5
/// </summary>
/// <param name="clearPassword"></param>
/// <returns></returns>
public static String EncodePassword(String clearPassword)
{
MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
byte[] hashedBytes;
UTF32Encoding encoder = new UTF32Encoding();
hashedBytes = md5Hasher.ComputeHash(ASCIIEncoding.Default.GetBytes(clearPassword));
String result = Regex.Replace(BitConverter.ToString(hashedBytes), "-", "").ToLower();
return result;
}
}
}
There is nothing particularly clever here, except for the EncodePassword method which hashes the value made up of the SenderId, Password and Transaction id.
Each message inherits from this object. So for the Company Number Search in addition to the properties above we need a partial number, which dataset to search – for the purposes of the project we only need to search the LIVE set so this can be set in the constructor and the SearchRows. Again all are set as properties. With the SearchRows and DataSet initialized in the constructor.
public class CompanyNumberSearchRequest : GovTalkRequest, IDisposable
{
/// <summary>
///
/// </summary>
public CompanyNumberSearchRequest() : base()
{
DataSet = "LIVE";
SearchRows = 1;
}
/// <summary>
/// Company Number to search against
/// </summary>
public String PartialCompanyNumber { get; set; }
/// <summary>
/// What DataSet should be searched for the company
/// </summary>
public String DataSet { get; set; }
/// <summary>
/// How many rows should be returned
/// </summary>
public int SearchRows { get; set; }
public void Dispose()
{
DataSet = String.Empty;
PartialCompanyNumber = String.Empty;
DataSet = "LIVE";
SearchRows = 1;
}
}
As well as inheriting from our base class, I have also inherited from IDisposable – not just because it is just plain good practice to dispose of objects when coding, but it gives also gives us more versatility when using the object.
There are four stages in making a request and this is reflected in the four methods we execute in making a call to the Companies House service;
- Create a request
- Send a request
- Check the status
- If OK then get the results of the request
I’ve implemented each of these stages within a static class called Toolbox – which also means I don’t need to create an instance of the class to use it.
When making a request there are three stages;
- Get the template for the message
- Serialize the object representing the message
- Transform the serialized object using a predefined XSLT file.
Each of my templates I have defined as an embedded resource. When retrieving a resource of this kind we have to include the full namespace to the resource. In making the code re-usable as much as possible I defined the full ‘path’ within the GetRequest method.
requestFile = String.Format("CompanyHub.Services.Schemas.{0}", RequestFile);
So we now have the full path of the file within the assembly. Now all we need do is retrieve the assembly and get the resource.
asm = Assembly.GetExecutingAssembly();
sr = asm.GetManifestResourceStream(requestFile);
Once retrieved
So this can be returned to the calling function and we now have a stream of XSLT to define the message. Time now to serialize the request to create the other side of this message.
// Serialize object containing Request, Load into XML Document
t = Obj.GetType();
ms = new MemoryStream();
serializer = new XmlSerializer(t);
xmlTextWriter = new XmlTextWriter(ms, Encoding.ASCII);
serializer.Serialize(xmlTextWriter, Obj);
ms = (MemoryStream)xmlTextWriter.BaseStream;
GovTalkRequest = Toolbox.ConvertByteArrayToString(ms.ToArray());
First off we need the type of the object so we make a call to the GetType method of the object containing the Message properties.
Next we need a MemoryStream, XmlSerializer and an XMLTextWriter so these can be initialized.
The object is serialized by making the call to the Serialize method of the serializer object. The result of that is then converted into a MemoryStream. That MemoryStream is then converted into a string.
ConvertByteArrayToString
This is a fairly simple function which uses an ASCIIEncoding object found within the System.Text namespace to convert an array of bytes into a string.
public static String ConvertByteArrayToString(byte[] bytes)
{
System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
return enc.GetString(bytes);
}
I only put it into a function because I will be using this in various places.
The Sauce
When adding support for other messages outside of creating a new object to store the properties of the message, the C# components do not need to change. It is in the XSLT file that the versatility of the technique lies. The XSLT file determines the format of the message. For the CompanyNumberSearch the XSLT file is as follows;
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<GovTalkMessage xsi:schemaLocation="http://www.govtalk.gov.uk/CM/envelope http://xmlgw.companieshouse.gov.uk/v1-0/schema/Egov_ch-v2-0.xsd" xmlns="http://www.govtalk.gov.uk/CM/envelope" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#" xmlns:gt="http://www.govtalk.gov.uk/schemas/govtalk/core" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<EnvelopeVersion>1.0</EnvelopeVersion>
<Header>
<MessageDetails>
<Class>NumberSearch</Class>
<Qualifier>request</Qualifier>
<TransactionID>
<xsl:value-of select="CompanyNumberSearchRequest/TransactionId"/>
</TransactionID>
</MessageDetails>
<SenderDetails>
<IDAuthentication>
<SenderID><xsl:value-of select="CompanyNumberSearchRequest/SenderID"/></SenderID>
<Authentication>
<Method>CHMD5</Method>
<Value>
<xsl:value-of select="CompanyNumberSearchRequest/AuthenticationValue"/>
</Value>
</Authentication>
</IDAuthentication>
</SenderDetails>
</Header>
<GovTalkDetails>
<Keys/>
</GovTalkDetails>
<Body>
<NumberSearchRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xmlgw.companieshouse.gov.uk/v1-0/schema/NumberSearch.xsd">
<PartialCompanyNumber>
<xsl:value-of select="CompanyNumberSearchRequest/PartialCompanyNumber"/>
</PartialCompanyNumber>
<DataSet>
<xsl:value-of select="CompanyNumberSearchRequest/DataSet"/>
</DataSet>
<SearchRows>
<xsl:value-of select="CompanyNumberSearchRequest/SearchRows"/>
</SearchRows>
</NumberSearchRequest>
</Body>
</GovTalkMessage>
</xsl:template>
</xsl:stylesheet>
The outer two tags define that this is a XSLT stylesheet and the root tag from which the nodes are searched for.
The GovTalkMessage is the format of the message that will be sent to Companies House.
We first set up the XslCompiledTransform object which will transform the XSLT template and the serialized object into the request to Companies House.
xslt = new XslCompiledTransform();
resultStream = new MemoryStream();
writer = new XmlTextWriter(resultStream, Encoding.ASCII);
doc = new XmlDocument();
The Serialize method require XmlTextWriter to write the XML (writer) and a stream to place the transferred object into (writer). The XML will be loaded into an XMLDocument object (doc) prior to the transformation.
// create XSLT Template
xslTemplate = Toolbox.GetRequest(Template);
xslTemplate.Seek(0, SeekOrigin.Begin);
templateReader = XmlReader.Create(xslTemplate);
xslt.Load(templateReader);
I have stored all the templates as a series of Embedded Resources and the GetRequestCall takes the name of the template and extracts the relevent XSLT file.
/// <summary>
/// Gets the framwork XML which makes the request
/// </summary>
/// <param name="RequestFile"></param>
/// <returns></returns>
public static Stream GetRequest(String RequestFile)
{
String requestFile = String.Empty;
Stream sr = null;
Assembly asm = null;
try
{
requestFile = String.Format("CompanyHub.Services.Schemas.{0}", RequestFile);
asm = Assembly.GetExecutingAssembly();
sr = asm.GetManifestResourceStream(requestFile);
}
catch (Exception)
{
throw;
}
finally
{
asm = null;
}
return sr;
} // end private static stream GetRequest
We first take the template name and expand it to include the full namespace to the Embedded Resource I like to keep all my schemas in the same directory and so the namespace reflects this. The rest is the default namespace for the project.
Then we get the currently executing assembly (which will contain the resources with the call to GetExecutingAssembly() )
Finally we get a stream which contains the XSLT file. We use this stream and then load an XmlReader with the contents of the template, and that is in turn loaded into the XslCompiledTransform object.
We convert the object containing the message properties into Xml by serializing it; calling the Serialize() method of the XmlSerializer object. To set up the object we do the following;
t = Obj.GetType();
ms = new MemoryStream();
serializer = new XmlSerializer(t);
xmlTextWriter = new XmlTextWriter(ms, Encoding.ASCII);
We first determine the type of the object being transferred by calling GetType() We create an XmlSerializer object by passing the type of the object being serialized. The serializer writes to a memory stream and that is linked to an XmlTextWriter.
Next job is to serialize the object and load it into an XmlDocument.
serializer.Serialize(xmlTextWriter, Obj);
ms = (MemoryStream)xmlTextWriter.BaseStream;
xmlRequest = new XmlTextReader(ms);
GovTalkRequest = Toolbox.ConvertByteArrayToString(ms.ToArray());
doc.LoadXml(GovTalkRequest);
Time to transform the XML to construct the full request.
xslt.Transform(doc, writer);
resultStream.Seek(0, SeekOrigin.Begin);
request = Toolbox.ConvertByteArrayToString(resultStream.ToArray());
So that creates the full request to be sent to Companies House.
Sending the request
So far we have a string with a request for the Companies House service. Now we need to send the request to the Companies House Service.
Configuration within an Azure project
There are entire blog entries written about configuration within an Azure project – most of this is out of scope for this article but the following is a summary.
Configuration is defined in two files within the parent project *.csdef which contains the definition of configuration setting.
<?xml version="1.0" encoding="utf-8"?>
<ServiceDefinition name="OnlineCompanyHub" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition">
<WebRole name="CompanyHub.Host">
<InputEndpoints>
<InputEndpoint name="HttpIn" protocol="http" port="80" />
</InputEndpoints>
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" />
<Setting name="DataConnectionString" />
</ConfigurationSettings>
</WebRole>
<WebRole name="CompanyHub.Services">
<InputEndpoints>
<InputEndpoint name="HttpIn" protocol="http" port="8080" />
</InputEndpoints>
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" />
<Setting name="SenderId"/>
<Setting name="SenderPassword" />
<Setting name="GovTalkUrl"/>
</ConfigurationSettings>
</WebRole>
<WorkerRole name="CompanyHub.Worker">
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" />
</ConfigurationSettings>
</WorkerRole>
</ServiceDefinition>
Above is the configuration definition from the project. What we are interested in however is the ConfigurationSettings tag of the CompanyHub.Services WebRole.
There are four configuration settings here, but at the moment we are interested in the second to forth settings; SenderId, SenderPassword and GovTalkUrl
The value of these settings are defined in the ServiceDefinition.cscfg file;
<?xml version="1.0"?>
<ServiceConfiguration serviceName="OnlineCompanyHub" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration">
<Role name="CompanyHub.Host">
<Instances count="2" />
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" value="UseDevelopmentStorage=true" />
<Setting name="DataConnectionString" value="UseDevelopmentStorage=true" />
</ConfigurationSettings>
</Role>
<Role name="CompanyHub.Services">
<Instances count="2" />
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" value="UseDevelopmentStorage=true" />
<Setting name="SenderId" value="UserID"/>
<Setting name="SenderPassword" value="Password"/>
<Setting name="GovTalkUrl" value="http://xmlgw.companieshouse.gov.uk/v1-0/xmlgw/Gateway"/>
</ConfigurationSettings>
</Role>
<Role name="CompanyHub.Worker">
<Instances count="2" />
<ConfigurationSettings>
<Setting name="DiagnosticsConnectionString" value="UseDevelopmentStorage=true" />
</ConfigurationSettings>
</Role>
</ServiceConfiguration>
Look for the Role tag that contains our project name (CompanyHub.Services).
Having configured the parameters we can now transmit the request. This is done by ‘POST’ing a stream of XML to the Companies House servers.
govTalkUrl = RoleEnvironment.GetConfigurationSettingValue("GovTalkUrl");
request = WebRequest.Create(govTalkUrl);
request.Method = "POST";
request.ContentType = "text/xml";
writer = new StreamWriter(request.GetRequestStream());
writer.WriteLine(RequestMessage);
writer.Close();
We use the WebRequest object to send the object. Set the method of sending to ‘POST’ and the type of data as text/xml. Once set up all we do is write the request to the writer – this sends the request to Companies House.
Did the Request Work Part I – Getting the response
Having sent a request – we now need the result of that request.
response = request.GetResponse();
reader = response.GetResponseStream();
result = Toolbox.ConvertByteArrayToString(Toolbox.ReadFully(reader));
The WebRequest object has a GetResponse() method which allows us to get the response sent back. Like many of these calls the results come in the form of a stream which we convert into a string.
Did the Request Work Part II – Translating the Response
Much like XSLT and XML were used to create the original request, so it can be used to extract the response and by deserializing the result we create an object that contains the response.
Did it work?
It would be really great if everything worked all the time. Of course if it did then I don’t suppose people would pay me and others the big bucks so that our programmes do not
a) Collapse in a heap (this is an area of memory)
b) Blow every fuse in the place in a shower of sparks (this will probably not happen this being real life and not a Hollywood movie, but it was possible to blow the sound system of a BBC Model B with a poorly coded setting)
c) Go nuts and trap everyone outside the airlock (this was from a movie, and unless NASA get a manned moon/mars mission set up unlikely to happen)
d) Go nuts and take over the world (this was also from a movie, but please note life has a habit of being of exceeding the wildest imaginations of Hollywood writers (note writers – Hollywood executives have no imagination and judging by recent output of that town have turned plagiarism into an art form).
e) Freeze in total confusion because the cleaner pulled the plug to the internet router (this has happened)
So anyway – we need to check to see if our request actually worked. Within the GovTalk response there is a section that details the status of the message and a description of what went wrong (if anything did). I have defined an XSLT template which will extract these into an XML document.
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ev="http://www.govtalk.gov.uk/CM/envelope"
xmlns:gt="http://www.govtalk.gov.uk/schemas/govtalk/core"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsl:template match="/">
<GovTalkStatus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Status>
<xsl:value-of select="ev:GovTalkMessage/ev:Header/ev:MessageDetails/ev:Qualifier"/>
</Status>
<Text>
<xsl:value-of select="ev:GovTalkMessage/ev:GovTalkDetails/ev:GovTalkErrors/ev:Error/ev:Text"/>
</Text>
<Location>
<xsl:value-of select="ev:GovTalkMessage/ev:GovTalkDetails/ev:GovTalkErrors/ev:Error/ev:Location"/>
</Location>
<Number>
<xsl:value-of select="ev:GovTalkMessage/ev:GovTalkDetails/ev:GovTalkErrors/ev:Error/ev:Number"/>
</Number>
<Type>
<xsl:value-of select="ev:GovTalkMessage/ev:GovTalkDetails/ev:GovTalkErrors/ev:Error/ev:Type"/>
</Type>
</GovTalkStatus>
</xsl:template>
</xsl:stylesheet>
Only thing different about previous XSL files is the references to two namespaces ev & gt. These are defined in the GovTalk response at the top of the response;
xsi:schemaLocation="http://www.govtalk.gov.uk/CM/envelope http://xmlgw.companieshouse.gov.uk/v1-0/schema/Egov_ch-v2-0.xsd"
xmlns="http://www.govtalk.gov.uk/CM/envelope"
xmlns:dsig="http://www.w3.org/2000/09/xmldsig#"
xmlns:gt="http://www.govtalk.gov.uk/schemas/govtalk/core"
If we do not put these references into the XSLT template then the XslCompiledTransform object will not be able to find the relevant tags.
Deserialization is a fairly simple activity.
encoder = new ASCIIEncoding();
ms = new MemoryStream(encoder.GetBytes(statusXML));
serializer = new XmlSerializer(typeof(GovTalkStatus));
xmlTextWriter = new XmlTextWriter(ms, Encoding.ASCII);
messageStatus = (GovTalkStatus)serializer.Deserialize(ms);
We set up a serialization object using the object type containing the error state and pass to it the results of a transformation between the XSLT above and the GovTalk response.
Now we have an object containing any error state, and the error message. All we need to do is check the status. If there is an error then we can flag an error. If not then we extract the results and pass that as an object back to the calling function. We go this by guess what – defining an XSLT template for the result and using that to create an Xml Stream which can be deserialized into a .Net object. In this instance the XSLT to create the result of a Company Number Search is;
<?xml version="1.0" encoding="us-ascii"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ev="http://www.govtalk.gov.uk/CM/envelope"
xmlns:sch="http://xmlgw.companieshouse.gov.uk/v1-0/schema"
exclude-result-prefixes="ev">
<xsl:template match="/">
<CompanySearchResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<CompanyNumber>
<xsl:value-of select="ev:GovTalkMessage/ev:Body/sch:NumberSearch/sch:CoSearchItem/sch:CompanyNumber"/>
</CompanyNumber>
<CompanyName>
<xsl:value-of select="ev:GovTalkMessage/ev:Body/sch:NumberSearch/sch:CoSearchItem/sch:CompanyName"/>
</CompanyName>
</CompanySearchResult>
</xsl:template>
</xsl:stylesheet>
and the object definition is;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace CompanyHub.Services
{
public class CompanySearchResult
{
public CompanySearchResult()
{
CompanyNumber = String.Empty;
CompanyName = String.Empty;
}
public String CompanyNumber { get; set; }
public String CompanyName { get; set; }
}
}
Our entire code to make calls to send a request, and interpret the results are;
String request = String.Empty;
String response = String.Empty;
GovTalkStatus status = null;
fault = null;
try
{
using (CompanyNumberSearchRequest requestObj = new CompanyNumberSearchRequest())
{
requestObj.PartialCompanyNumber = CompanyNumber;
request = Toolbox.CreateRequest(requestObj, "CompanyNumberSearch.xsl");
response = Toolbox.SendGovTalkRequest(request);
status = Toolbox.GetMessageStatus(response);
if (status.Status.ToLower() == "error")
{
fault = new HubFault()
{
Message = status.Text
};
}
else
{
Object obj = Toolbox.GetGovTalkResponse(response, "CompanyNumberSearchResult.xsl", typeof(CompanySearchResult));
}
}
}
catch (FaultException<ArgumentException> ex)
{
fault = new HubFault()
{
FaultType = ex.Detail.GetType().FullName,
Message = ex.Detail.Message
};
}
catch (System.Exception ex)
{
fault = new HubFault()
{
FaultType = ex.GetType().FullName,
Message = ex.Message
};
}
finally
{
}
Wrap up
So there we have it – a reusable set of functions to send and interpret XML results from an internet based service. The code is reusable with a little change with any service which uses XML as a transport mechanism – and as for the Companies House GovTalk service all I need to do is create various objects for the result and message sent and the relevent XSLT files. I might need minor changes for other services but something like 70-90% will be exactly the same.
Project Sourcecode: NewsMashup
In Part I of this series of articles I discussed the database that lies behind the application. Moving from the backend to the client side, it is now time to discuss the code which accesses the database and returns the data to application.
I have a standard class I use when accessing databases. The version I am using here is developed to connect to SQL Server but with some minor changes it would not be difficult to adapt to other databases, and I already have one for ODBC.
public Database(string Connection)
{
connection = new SqlConnection(Connection);
InitialiseCommand();
}
public Database(SqlConnection Connection)
{
connection = Connection;
InitialiseCommand();
}
public Database()
{
connection = new SqlConnection();
InitialiseCommand();
}
The above code shows the constructor for the data access layer. Essentially the constructor expects either a connection, connection string or nothing to be passed to the constructor. Each calls another function; InitialiseCommand() which sets up the command object and is below.
private void InitialiseCommand()
{
try
{
if (dbCommand == null)
{
dbCommand = new SqlCommand();
dbCommand.CommandType = CommandType.StoredProcedure; // default to Stored Prc
if (connection != null)
{
dbCommand.Connection = connection;
}
}
}
catch (System.Exception ex)
{
throw ex;
}
}
This initialises the Command object and sets the type to Stored Procedure, if the Connection object has been initialised the command objects connection is set to the connection object.
To execute stored procedures we need two. One to return a datatable, and one which does not
public void Execute()
{
try
{
if (dbCommand.Connection.State != ConnectionState.Open) dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
}
catch (System.Exception ex)
{
throw ex;
}
}
public DataTable Execute(string TableName)
{
SqlDataAdapter da;
DataSet ds;
DataTable dt = new DataTable();
try
{
da = new SqlDataAdapter(dbCommand);
ds = new DataSet();
da.Fill(ds,TableName);
if (ds.Tables.Count > 0)
dt = ds.Tables[0];
}
catch (System.Exception ex)
{
throw ex;
}
return dt;
}
}
The first of these two methods is fairly simple. Essentially what it does is confirm the connection is open, if not the connection is opened. Then it executes the stored procedure. The whole lot is then wrapped in a try catch, which is thrown back to the calling application.
The second is more interesting. It uses a Data Adapter and Dataset to fill a table returned back to the calling programme.
These two methods cover all the various types of database action I might want to undertake.
The real magic here however is in the objects which inherit from this class.
public class NewsMashupDB : Database,IDisposable
The class inherits from the class defined in the code above. The constructor for the object is as follows;
public NewsMashupDB()
: base()
{
}
public NewsMashupDB(string ConnectionString)
: base(ConnectionString)
{
}
Thats it. There is two here – one initialises the connection string, the other does not. All the calling application need do is retrieve the connection string. Executing a stored procedure is equally simple. Here we have the stored procedure which executes the CreateStory stored procedure;
public void CreateStory(string Headline, string Description, string Url, string Supplier, long PubDateValue)
{
SqlParameter paramHeadline = new SqlParameter("Headline", SqlDbType.VarChar);
SqlParameter paramDescription = new SqlParameter("Description",SqlDbType.VarChar);
SqlParameter paramUrl = new SqlParameter("Url",SqlDbType.VarChar);
SqlParameter paramSupplier = new SqlParameter("Supplier", SqlDbType.VarChar);
SqlParameter paramPubDate = new SqlParameter("PubDateValue", SqlDbType.BigInt);
CommandText = "NewsMashup.CreateStory";
try
{
paramHeadline.Value = Headline;
paramDescription.Value = Description;
paramUrl.Value = Url;
paramSupplier.Value = Supplier;
paramPubDate.Value = PubDateValue;
dbCommand.Parameters.Clear();
dbCommand.Parameters.Add(paramHeadline);
dbCommand.Parameters.Add(paramDescription);
dbCommand.Parameters.Add(paramUrl);
dbCommand.Parameters.Add(paramSupplier);
dbCommand.Parameters.Add(paramPubDate);
Execute();
}
catch (System.Exception ex)
{
throw ex;
}
}
Except for the code setting up the parameters required the execution of the stored procedure takes two lines; one to initialise the command text, the other to execute the stored procedure.
A stored procedure which returns data is equally simple.
public DataTable RetrieveNewsFeeds()
{
DataTable dt = new DataTable();
try
{
CommandText = "NewsMashup.RetrieveNewsFeeds";
dt = Execute("NewsFeeds");
}
catch (System.Exception ex)
{
throw ex;
}
return dt;
}
It is clearer here than in the other method, again only two lines of code; one to initialise the CommandText the other to execute the stored procedure. This version of the Execute returns the datatable containing the results of the stored procedure’s execution.
This shows examples of the code. The rest should be in the attached zip file
If you have had a chance to look at the downloadable videos from MIX 09 (http://videos.visitmix.com/MIX09/All) then you will have heard that it will soon be possible to develop Azure hosted applications and have good old Transact SQL as a backend! It was in Nigel Ellis’ session on ‘What's New in Microsoft SQL Data Services’ which gives all the details.
However as of the time of writing this functionality, while due in the next couple of months, is not yet available. All is not lost. I am going to use the local Azure development environment to host an application and point it at my local SQL Server instance until such time that I can host it online.
While writing this application there were a number of gotcha’s – some because Azure is beta, some not. The follow is how I wrote a small application to show news items, that constantly updates. The backend is a SQL database undated on a regular basis by an Azure Worker Role, then read by a Web Service called from a Silverlight application, itself hosted on an Azure WebRole.
In developing this application, which I will be adapting for a much larger online one I am working on I will work from the Backend to the Silverlight application which makes up the client end.
Creating our database
According to the SQL Data Services session access to the database; either locally or from SQL Data Services will be via SQL Management Studio. Once the a database has been created it is time to create the tables that will store the details of our new stories;
This project is actually part of a much larger one I am creating. For this reason I want to logically separate parts of the database using schemas. This for example will allow one database to have two tables called ledger – one in a schema called ‘Sales’ and one called ‘Purchases’. You will notice that all of the database objects created in the following examples of T/SQL are preceded by the word ‘NewsMashup’. To create this schema execute the following code against the SQL database;
A Tables to store the sites from which feeds will be taken – this allows us to store a hyperlink to the parent site.
One to store the details of feeds from which stories will be taken;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [NewsMashup].[Feeds](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Url] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
[Supplier] [bigint] NULL,
CONSTRAINT [PK_NewsFeeds] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [NewsMashup].[Feeds] WITH CHECK ADD CONSTRAINT [FK_NewsFeeds_Supplier] FOREIGN KEY([Supplier])
REFERENCES [NewsMashup].[Supplier] ([Id])
GO
ALTER TABLE [NewsMashup].[Feeds] CHECK CONSTRAINT [FK_NewsFeeds_Supplier]
One table to store Stories as they are picked up
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [NewsMashup].[Stories](
[Id] [bigint] NOT NULL,
[Headline] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
[Description] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
[Url] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
[Supplier] [bigint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and finally one table to store the sites from which the feeds will be taken
ALTER Procedure [NewsMashup].[RetrieveNewsFeeds]
As
Begin
Select NewsMashup.Supplier.Id As Supplier,
NewsMashup.Supplier.Name,
NewsMashup.Supplier.Url As HomePage,
NewsMashup.Feeds.Url
From NewsMashup.Feeds
INNER JOIN NewsMashup.Supplier ON
NewsMashup.Feeds.Supplier = NewsMashup.Supplier.Id
Order By NewsMashup.Supplier.Name
End
Creating Stored Procedures to access and write data
In placing all database queries in a stored procedure you gain a number of advantages. Stored Procedures can be faster than inline code, partly this is because the database is able to partly compile and optimise your query. With inline code this happens at execution time. For the purposes of an application to display news headlines there are four stored procedures; the first to Create our Stories;
ALTER Procedure [NewsMashup].[CreateStory]
@Headline VarChar(Max),
@Description VarChar(Max),
@Url VarChar(Max),
@Supplier BigInt,
@PubDateValue BigInt
As
Begin
Declare @Counter BigInt
Set @Counter = (Select Count(*) As PreviousCounter From NewsMashup.Stories
Where Url = @Url)
If @Counter = 0
Begin
INSERT INTO NewsMashup.Stories
(Headline, Description, Url, Supplier, Id)
VALUES (@Headline,@Description,@Url,@Supplier,@PubDateValue)
End
End
It’s fairly simple this function. We first check to see if the link has already been added to the database. If not then the story is added. The next two procedures are both involved in retrieving headlines; we need two – one is used to retrieve the latest 10 stories from the database, the other retrieves any subsequent stories that may have been added to the database. This allows new stories to be added to the display as and when they are picked up.
CREATE SCHEMA [NewsMashup] AUTHORIZATION [dbo]
Retrieving the most recent 10 headlines
ALTER PROCEDURE [NewsMashup].[RetrieveHeadlines]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (10) NewsMashup.Stories.Id, NewsMashup.Stories.Headline, NewsMashup.Stories.Description, NewsMashup.Stories.Url, NewsMashup.Supplier.HasDescription,
NewsMashup.Supplier.Name AS Supplier
FROM NewsMashup.Stories INNER JOIN
NewsMashup.Supplier ON NewsMashup.Stories.Supplier = NewsMashup.Supplier.Id
ORDER BY NewsMashup.Stories.Id DESC
END
Retrieving the latest new headlines
ALTER PROCEDURE [NewsMashup].[RetrieveHeadlines2]
@LatestHeadline BigInt
AS
BEGIN
SET NOCOUNT ON;
SELECT Id, Headline, Description, Url, Supplier
FROM NewsMashup.Stories
WHERE (Id > @LatestHeadline)
ORDER BY Id
END
The final stored procedure retrieves the list of feeds from which headlines will be extracted.
ALTER Procedure [NewsMashup].[RetrieveNewsFeeds]
As
Begin
Select NewsMashup.Supplier.Id As Supplier,
NewsMashup.Supplier.Name,
NewsMashup.Supplier.Url As HomePage,
NewsMashup.Feeds.Url
From NewsMashup.Feeds
INNER JOIN NewsMashup.Supplier ON
NewsMashup.Feeds.Supplier = NewsMashup.Supplier.Id
Order By NewsMashup.Supplier.Name
End
That just about creates the database structures required to stored the News Headlines picked up by the application. Part II I will discuss the .Net libraries used to access these procedures.