ADO.NET DAL - Data Access Layer - High-Flying C# Coding Tips

Sponsored Links

73058_New Scooba® 230 Floor Washing Robot + Free Shipping!


ADO.NET DAL - Data Access Layer

Here I've provided what has become my personal usage ADO.NET Data Access Layer (or DAL).

ADO.NET had been available since .NET 1 and so has been used for many years. More recently we've had releases such as LINQ to SQL and the Entity Framework, which may well replace ADO.NET in many modern applications.

However, ADO.NET has proven to be trust worthy and a long standing tool in connecting with the database. What I've provided here is the current stage in my evolution of using ADO.NET in many projects over the years, and I wanted to achieve simple to use, minimal, re-usable methods, making it easy to add new calls to the database.

Data access is a big area, so needless to say that my implementation here uses SQL stored procedures with ADO.NET, in fact parameterised stored procedures as they improve security against SQL injection attacks. This reason alone should be enough to keep you away from doing inline SQL statements produced in the C# code, not to mention easier to maintain, and stored procedures can be maintained by the DBA (DataBase Administrator - could be you).

ADO.NET DAL - Data Access Layer - Core methods

The following code is what I use for reading data from the database, the results coming back into a DataSet.

// DataBase Read
// Takes a SqlCommand object and returns the results in a DataSet
public DataSet FromDataSet(SqlCommand command)
    DataSet ds = new DataSet();
    command.CommandType = CommandType.StoredProcedure;

    using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings.Get("DBConnectionString".ToString())))
        command.Connection = sqlCon;
        SqlDataAdapter adap = new SqlDataAdapter(command);

    return ds;

Now this method is passed a SqlCommand object (which we'll see later below), and then this method basically executes the SqlCommand using a SqlDataAdapter to fill a DataSet. This is wrapped up with the 'using' statement to ensure that the SqlConnection object is always closed, even on error. This leads us to handling errors, this is something to do in the parent method call, which may well send an email with the error message, and/or handle appropriately in the presentation layer.

If you didn't need a whole DataSet to return the result of your database call you could swap the SqlDataAdapter for a call to ExecuteNonQuery:

    using (SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings.Get("DBConnectionString".ToString())))
        command.Connection = sqlCon;

ADO.NET DAL - Data Access Layer - Individual database calls

Using the 'FromDataSet' method shown above, I can now make methods for each database call, and make them very quickly. I just need to create a SqlCommand object which references the Stored Procedure name, and add SqlParameters, if there are any.

    public DataSet GetAllProducts()
        SqlCommand command = new SqlCommand("Get_All_Products");
        return FromDataSet(command);

So here is the most simple form, calling 'FromDataSet', passing a SqlCommand specifying the stored procedure name, and no parameters, our core method handles the rest and executes the stored procedure, and returns the results from SQL Server back into a DataSet, ensuring the SqlConnection is closed regardless of error. Now it becomes very quick and easy to add additional methods for each stored procedure we add to the database.

ADO.NET DAL - Data Access Layer - Passing Parameters

Here we have an example using parameters.

    public DataSet GetProductInfo(Int32 userID)
        SqlCommand command = new SqlCommand("Get_Product_Info");
        command.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int)).Value = userID;
        return FromDataSet(command);

So here we add one parameter by adding a SqlParameter to the SqlCommand object, which contains the parameter name, datatype and then set it's value.

ADO.NET DAL - Data Access Layer - Return values

Sometimes we like to get return values from SQL Server, such as a row count to confirm that our Update or Insert SQL commands completed successfully. To do this we just specify that our parameter is a 'ReturnValue', as shown below:

    public Int32 InsertPremiumAdvert(Int32 productID, DateTime startDate, DateTime endDate)
        SqlCommand command = new SqlCommand("Insert_Premium_Advert");
        command.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.Int)).Value = productID;
        command.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime)).Value = startDate;
        command.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)).Value = endDate;

        // Specify return value
        command.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue;

        // Here is our return value from SQL
        return Convert.ToInt32(command.Parameters["@RowCount"].Value);

So here we have multiple paramters and we're not returning a DataSet full of results but an Integer containing the row count following our SQL operation. We make the '@RowCount' parameter a 'ReturnValue', and we need to perform a conversion to get the value into an Int32. Our stored procedure would need to specifically return this value.