Windows Azure, TDS, WCF, Silverlight and a Few Problems on the Way - Part II – Database Access Layer

• Download project source code - 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 program.

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)
{

}

That's it. There are two here – one initialises the connection string, the other does not. All the calling application needs 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.