DataGeneral: Lightweight ADO.NET wrapper replacing Enterprise Library Data Access Application Block

 

The DataGeneral class provides access to the most often used features of ADO.NET in a simple-to-use class boosting developer productivity.

DataGeneral is lightweight for maximum performance. It employs Microsoft best practices. DataGeneral provides the best features of Enterprise Libraries Data Application block. In my experience, I like the Enterprise libraries Application Blocks to learn what features of the .NET framework are available , and best practice coding techniques (hence the name patterns and practices). But I don't like the weight of the enterprise libraries, and the random errors I get when versions are out of sync across environments. That is why I wrote DataGeneral which is just a single wrapper class. With DataGeneral you will have less lines of code to debug. DataGeneral is specific to Microsoft Sql Server.

For example, one of the features of the Data Access Block is stated as: "By changing the settings in the configuration file, developers can use their applications with different database configurations without recompiling their code." This makes me laugh: What application can have its most major component ripped out and replaced without recompiling the code? I would state that this feature is useless as are many of the other features in the Data Access block. However Enterprise library has many good features like saving you lines of code when building a SqlCommands with SqlParameters. That's why I wrote DataGeneral: All the good parts without all the useless parts.

DataGeneral has been used in several enterprise applications currently in production. On each project it has been further refined and perfected.

 

Example 1) DataGeneral Source Code:

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

namespace DataAccess
{
    public class DataGeneral
    {

        private static SqlConnection createConnection()
        {
            string sqlCn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            SqlConnection cn = new SqlConnection(sqlCn);
            cn.Open();
            return cn;
        }

        /// <summary>
        /// Wraps the SqlCommand.ExecuteReader() method. 
        /// </summary>
        /// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
        /// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
        /// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement.</param>
        /// <returns>Returns a SqlDataReader which MUST be wrapped in a using statement so that its SqlConnecion is closed as soon as the SqlDataReader is disposed.</returns>
        public static SqlDataReader ExecuteReader(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
        {
            //IMPORTANT: make sure you wrap the returned SqlDataReader in a using statement so that it is closed. (You do not need to close the SqlConnection object.)
            SqlConnection cn = createConnection();

            SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);

            if (isStoredProcedure)
                cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// Wraps the SqlCommand.ExecuteScalar() method. 
        /// </summary>
        /// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
        /// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
        /// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement.</param>
        /// <returns>Returns the first value of the first row of the Sql Statement.</returns>
        public static object ExecuteScalar(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
        {
            object scalarValue;
            using (SqlConnection cn = createConnection())
            {
                SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
                if (isStoredProcedure)
                    cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                scalarValue = cmd.ExecuteScalar();
            }
            return scalarValue;
        }

        /// <summary>
        /// Wraps the SqlCommand.ExecuteNonQuery() method. 
        /// </summary>
        /// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
        /// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
        /// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement</param>
        /// <returns>Returns the number of rows affected by ExecuteNonQuery().</returns>
        public static int ExecuteNonQuery(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure)
        {
            int rowsAffected;
            using (SqlConnection cn = createConnection())
            {
                SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                if (isStoredProcedure)
                    cmd.CommandType = CommandType.StoredProcedure;

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return rowsAffected;
        }

        public class ParamBuilder
        {
            private readonly List<SqlParameter> _parameters = new List<SqlParameter>();
            public List<SqlParameter> Parameters
            {
                get
                {
                    return _parameters;
                }
            }

            public void AddParam(SqlDbType sqlDbType, string paramName, object paramVal)
            {
                SqlParameter p = new SqlParameter(paramName, sqlDbType);
                p.Value = paramVal ?? DBNull.Value;
                _parameters.Add(p);
            }

            public SqlParameter AddOutputParam(SqlDbType sqlDbType, string paramName)
            {
                SqlParameter p = new SqlParameter(paramName, sqlDbType);
                p.Direction = ParameterDirection.Output;
                _parameters.Add(p);
                return p;
            }
        }


    }
}

Example 2) Sample Usage of ExecuteReader()

Note you should wrap the ExecuteReader() in a using statement.

public static Address GetAddressByID(int addressID)
{
    Address address = null;

    DataGeneral.ParamBuilder paramBuilder = new DataGeneral.ParamBuilder();
    paramBuilder.AddParam(SqlDbType.Int, "@AddressID", addressID);

    using (SqlDataReader sqlDataReader = DataGeneral.ExecuteReader("Location_Address_GetByID", paramBuilder.Parameters, true))
    {
        if (sqlDataReader.Read())
        {
            address = PopulateAddressFromReader(sqlDataReader);
        }
    }

    return address;
}

private static Address PopulateAddressFromReader(SqlDataReader sqlDataReader)
{
    Address address = new Address();

    address.AddressID = (int)sqlDataReader["AddressID"];
    address.AddressLine1 = sqlDataReader["AddressLine1"] as string;
    address.AddressLine2 = sqlDataReader["AddressLine2"] as string;
    address.AddressLine3 = sqlDataReader["AddressLine3"] as string;
    address.City = sqlDataReader["City"] as string;
    address.Zipcode = sqlDataReader["ZipCode"] as string;
    address.CountryRefID = (int)sqlDataReader["CountryRefID"];
    address.CityLatitude = sqlDataReader["CityLatitude"] as float?;
    address.CityLongitude = sqlDataReader["CityLongitude"] as float?;
    address.ZipcodeLatitude = sqlDataReader["ZipcodeLatitude"] as float?;
    address.ZipcodeLongitude = sqlDataReader["ZipcodeLongitude"] as float?;

    return address;
}

Example 3) Sample usage of ExecuteNonQuery()

Note, that both inline sql and stored procedures are supported. This example also demonstrates output parameters. For example, if you are inserting in to a table with an identity column and want to return the newly created identity value.

This example also demonstrates wrapping a call with TransactionScope() which is another great feature of the .NET framework.

public static void AddAddress(Address address)
{
    DataGeneral.ParamBuilder paramBuilder = new DataGeneral.ParamBuilder();
    SqlParameter paramNewAddressId = paramBuilder.AddOutputParam(SqlDbType.Int, "@NewAddressID");
    paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine1", address.AddressLine1);
    paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine2", address.AddressLine2);
    paramBuilder.AddParam(SqlDbType.VarChar, "@AddressLine3", address.AddressLine3);
    paramBuilder.AddParam(SqlDbType.VarChar, "@City", address.City);
    paramBuilder.AddParam(SqlDbType.VarChar, "@StateProvinceRefID", address.StateProvinceRefID);
    paramBuilder.AddParam(SqlDbType.VarChar, "@ZipCode", address.Zipcode);
    paramBuilder.AddParam(SqlDbType.VarChar, "@CountryRefID", address.CountryRefID);
    paramBuilder.AddParam(SqlDbType.Float, "@CityLatitude", address.CityLatitude);
    paramBuilder.AddParam(SqlDbType.Float, "@CityLongitude", address.CityLongitude);
    paramBuilder.AddParam(SqlDbType.Float, "@ZipcodeLatitude", address.ZipcodeLatitude);
    paramBuilder.AddParam(SqlDbType.Float, "@ZipcodeLongitude", address.ZipcodeLongitude);

    const string sql =
@"    INSERT Address 
    ( AddressLine1, AddressLine2, AddressLine3, City, StateProvinceRefID, ZipCode, CountryRefID,
    CityLatitude,  CityLongitude, ZipcodeLatitude, ZipcodeLongitude, AddressPhoneID,AddressFaxPhoneID,
    CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) 

    VALUES                          
    (@AddressLine1,@AddressLine2,@AddressLine3,@City,@StateProvinceRefID,@ZipCode,@CountryRefID,
    @CityLatitude,@CityLongitude,@ZipcodeLatitude,@ZipcodeLongitude)
    
    SET @NewAddressID=SCOPE_IDENTITY();";

    using (TransactionScope transactionScope = new TransactionScope())
    {
        DataGeneral.ExecuteNonQuery(sql, paramBuilder.Parameters, false);
        transactionScope.Complete();
    }
    address.AddressID = (int) paramNewAddressId.Value;
}