Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Windows Azure, TDS, WCF, Silverlight and a few problems on the way Part II – Database access layer

, 14 May 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Part II of a multiple part article where I build a project to show News Headlines from a number of RSS feeds as a vertical scrolling region

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Paul S. Chapman
Software Developer (Senior) Simplicita Online
United Kingdom United Kingdom
UK based IT Consultant. Started in 1985 selling home computers such as the Sinclair ZX Spectrum, BBC Model B and Commodore 64, and in 1987 moved into development, starting first with Torch Computers, developing software for the XXX UNIX Workstation.
 
Currently developing a new Azure/Silverlight based website/desktop applications for a new startup, hoping to launch in late 2009/early 2010

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 14 May 2009
Article Copyright 2009 by Paul S. Chapman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid