Simplify DatabaseAccess

Download source - 5 KB

Introduction

As a developer, I often write the same code over and over again. SqlConnection and SqlCommand are frequently used objects and it's boring to repeat almost the same code. The only difference is the commandText and the parameters. And of course what the datareader does. See the code and you will get the point.

Using the Code

The main thing here is that you inherit a DataAccessBase which does most of the work. The DataAccessBase class creates the three objects you need to do most database operations:

SqlConnection
SqlCommand
SqlDataReader

Putting all dataaccess handling in the same class like this will make it easy to modify it for performance or even change databaseprovider.

In a class which inherits from the class DataAccessBase, you can easily execute commandtext, get datatables from your database, etc. The following code gives you an idea of how this works:

// A class with two simple methods for some dataaccess the normal way...
public class NormalDataAccess
{
    // A simple database example. Add a user
    // Uses a stored procedure which returns UserId
    public int AddUser(string username, string password)
    {
        SqlConnection conn = new SqlConnection("connectionString");
        SqlCommand cmd = new SqlCommand("spMyProcedure", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@UserId", DBNull.Value));
        cmd.Parameters["@UserId"].SqlDbType = SqlDbType.Int;
        cmd.Parameters["@UserId"].Size = 4;
        cmd.Parameters["@UserId"].Direction = ParameterDirection.Output;
        cmd.Parameters.Add(new SqlParameter("@Username", username));
        cmd.Parameters.Add(new SqlParameter("@Password", password));

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        int userId = Convert.ToInt32(cmd.Parameters["@UserId"].Value);

        cmd.Dispose();
        conn.Dispose();

        return userId;
    }

    // Just execute a simple sqlcommand
    public void ExecuteSqlCommand()
    {
        SqlConnection conn = new SqlConnection("connectionString");
        SqlCommand cmd = new SqlCommand("spMyProcedure", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        cmd.Dispose();
        conn.Dispose();
    }
}

// The same two methods but with DataAccessBase inherited
public class SimplifiedDataAccess : DataAccessBase
{
    public int AddUser(string username, string password)
    {
        base.AddParameter("@UserId", DBNull.Value, SqlDbType.Int, 4, ParameterDirection.Output);
        base.AddParameter("@Username", username, SqlDbType.VarChar, 255);
        base.AddParameter("@Password", password, SqlDbType.VarChar, 255);
        base.ExecuteNonQuery("spUsers_AddUser");
        int UserId = Convert.ToInt32(base.GetParameter("@UserId").Value);
        base.Dispose();
        return UserId;
    }

    public void ExecuteSqlCommand()
    {
        base.ExecuteNonQuery("spMyProcedure");
        base.Dispose();
    }
}

And the DataAccessBase looks like this:
(I have removed comments. Download the example to see more.)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SimplifyDataAccess
{

    public abstract class DataAccessBase
    {
        public SqlConnection Connection;
        public SqlCommand Command;
        public SqlDataReader DataReader;

        public DataAccessBase()
        {
            Connection = new SqlConnection("### Your connectionstring ###");
            Command = new SqlCommand("", Connection);
            Command.CommandType = CommandType.StoredProcedure;
        }

        public void Dispose()
        {
            if ((DataReader != null) && (!DataReader.IsClosed))
                DataReader.Close();

            if (Connection.State == ConnectionState.Open)
                Connection.Close();

            DataReader = null;
            Command.Dispose();
            Command = null;
            Connection.Dispose();
            Connection = null;
        }

        public void ExecuteDataReader(string commandText)
        {
            if (Connection.State == ConnectionState.Closed)
                Connection.Open();

            this.Command.CommandText = commandText;
            this.DataReader = this.Command.ExecuteReader();
        }

        public void ExecuteNonQuery(string commandText)
        {
            Command.CommandText = commandText;

            if (Connection.State == ConnectionState.Open)
            {
                Command.ExecuteNonQuery();
            }
            else
            {
                Connection.Open();
                Command.ExecuteNonQuery();
                Connection.Close();
            }
        }

        public object ExecuteScalar(string commandText)
        {
            Command.CommandText = commandText;
            object value = null;

            if (Connection.State == ConnectionState.Open)
            {
                value = Command.ExecuteScalar();
            }
            else
            {
                Connection.Open();
                value = Command.ExecuteScalar();
                Connection.Close();
            }

            return value;
        }

        public DataTable ExecuteDataTable(string commandText, string tableName)
        {
            Command.CommandText = commandText;
            DataTable dt = null;

            if (Connection.State == ConnectionState.Open)
            {
                SqlDataAdapter da = new SqlDataAdapter(Command);
                dt = new DataTable(tableName);
                da.Fill(dt);
            }
            else
            {
                Connection.Open();
                SqlDataAdapter da = new SqlDataAdapter(Command);
                dt = new DataTable(tableName);
                da.Fill(dt);
                Connection.Close();
            }

            return dt;
        }

        public void AddParameter(string parameterName, object value, 
                SqlDbType sqlDbType, int size)
        {
            AddParameter(parameterName, value, sqlDbType, size, 
                ParameterDirection.Input);
        }

        public void AddParameter(string parameterName, object value, 
                SqlDbType sqlDbType, int size, ParameterDirection direction)
        {
            SqlParameter p = new SqlParameter(parameterName, value);
            p.SqlDbType = sqlDbType;
            p.Size = size;
            p.Direction = direction;
            Command.Parameters.Add(p);
        }

        public SqlParameter GetParameter(string parameterName)
        {
            return this.Command.Parameters[parameterName];
        }
    }
}