Super lightweight Data Access Layer returning models from ADO

Return strongly typed objects directly from stored procedure calls

Entity framework is an awesome tool for working with database in .net, but sometime you have to get back to old fashion ADO for the performances. The bad thing is that as soon as you get back to ADO, you have to deal with SqlDataReader, DataTable and DataSet classes.

This is not so convenient for using in upper levels of applications, especially in MVC environment where using strongly typed objects is an essential thing.

There are a bunch of libraries available on the internet, but each and every has some additional things that you actually do not need. For this purpose I wrote a lightweight DataAccess which only transforms DataReader and DataTable instances to POCO classes. It is light as possible and allows transformation of these two basic ADO classes to any POCO class instance.

It can be used as a base for any ADO based DataAccess layer. The basic is pretty much similar to any pattern used for DataAccess layer library.

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace SimpleData.DataAccessLayer{public abstract class DataAccessBase : IDisposable{#region Fieldsprivate SqlConnection connection = null;#endregion#region Propertiespublic SqlConnection Connection{get{return this.connection;}set{this.connection = value;}}#endregion#region Constructorspublic DataAccessBase(){var connection = ConfigurationManager.ConnectionStrings["SimpleDataAccessConnection"];if (connection != null){this.connection = new SqlConnection(connection.ConnectionString);}}public DataAccessBase(string connectionString){this.connection = new SqlConnection(connectionString);}public DataAccessBase(SqlConnection connection)
        {
            this.connection = connection;
        }
        #endregion

        #region ADO operations

        /// <summary>
        /// Returns SqlDataReader for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string procedureName, IDictionary<string, IConvertible> parameters = null)
        {
            return this.GetCommand(procedureName, parameters).ExecuteReader();
        }

        /// <summary>
        /// Returns DataTable for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string procedureName, IDictionary<string, IConvertible> parameters = null)
        {
            DataTable dataTable = new DataTable();
            this.GetAdapter(procedureName, parameters).Fill(dataTable);
            return dataTable;
        }

        /// <summary>
        /// Returns DataSet for stored procedure with optional paramets list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string procedureName, IDictionary<string, IConvertible> parameters = null)
        {
            DataSet dataSet = new DataSet();
            this.GetAdapter(procedureName, parameters).Fill(dataSet);
            return dataSet;
        }

        #region Command preparing methods

        /// <summary>
        /// Creates SqlAdapter instance for the stored procedure with optional paramets
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private SqlDataAdapter GetAdapter(string procedureName, IEnumerable<KeyValuePair<string, IConvertible>> parameters = null)
        {
            return new SqlDataAdapter(this.GetCommand(procedureName, parameters));
        }

        /// <summary>
        /// Creates SqlAdapter instance for the command
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private SqlDataAdapter GetAdapter(SqlCommand command)
        {
            return new SqlDataAdapter(command);
        }

        /// <summary>
        /// Prepares SqlCommand for stored procedure with oprional parameters list
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private SqlCommand GetCommand(string procedureName, IEnumerable<KeyValuePair<string, IConvertible>> parameters = null)
        {
            SqlCommand command = new SqlCommand(procedureName);
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = this.connection;
            if (parameters != null && parameters.Any())
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(new SqlParameter(param.Key, param.Value));
                }
            }
            return command;
        }

        #endregion

        #endregion

        #region IDisposable implementation

        /// <summary>
        /// Dispose DataAccessLayer instance and closes database connection
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this.connection != null)
                {
                    this.connection.Close();
                    this.connection.Dispose();
                }
            }
        }

        #endregion
    }
}
    

If you take a closer look you do not notice anything special. It is a simple DataAcces layer class that wraps ADO in a manner of easier use with several method overloads and nothing really special.

The binding however is left to be implemented and I decided to use reflection to make things generic and enable binding ADO objects to any POCO class instance.

The simples way is to match POCO or Model class properties to names of the columns returned in ADO SqlDataReader or DataTable. This is not bad approach bu very often column names in tables have not so code-styled names, and your model class might have to look pretty much the same as data returned from database which is not so nice usually.

For that purpose I decided to use custom attributes for mapping column with object instance property name. With this you can easily have column in database table named first_name and map it to FirstName property in model class.

 Inherited = true)]
    public class DataColumn : Attribute
    {
        #region Fields
        private string columnName;
        #endregion

        #region Properties
        public string ColumnName
        {
            get
            {
                return this.columnName;
            }
            set
            {
                this.columnName = value;
            }
        }
        #endregion

        #region Constructors
        public DataColumn(string columnName)
        {
            this.columnName = columnName;
        }
        #endregion

    }
    

Nowm all we have to do is to do the mapping. As I mentioned before I used reflection to make things more generic. However reflection is certainly not the fastest way to do it, but applying it to not such large number of objects you do not loose much on performances in total.

        #region Model binding methods
        /// <summary>
        /// Creates single instance of POCO class of type T
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public T ExecuteProcedureSingleModel<T>(string procedureName, IDictionary<string, IConvertible> parameters = null) where T : class, new()
        {
            T item = null;
            IDataRecord record = this.GetCommand(procedureName, parameters).ExecuteScalar() as IDataRecord;
            if (record != null)
            {
                var objectType = typeof(T);
                item = new T();
                for (int columnIndex = 0; columnIndex < record.FieldCount; columnIndex++)
                {
                    var objectProperty = objectType.GetProperties()
                            .Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
                                .Where(a => ((DataColumn)a).ColumnName == record.GetName(columnIndex))
                                .Any()
                                ).FirstOrDefault();
                    if (objectProperty != null)
                    {
                        var dataValue = record.GetValue(columnIndex);
                        objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
                    }
                }
            }
            return item;
        }

        /// <summary>
        /// Return IEnumerable of POCO class instances of type T for the mutiple results from database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IEnumerable<T> ExecuteProcedureMultipleModel<T>(string procedureName, IDictionary<string, IConvertible> parameters = null) where T : class, new()
        {
            var reader = this.ExecuteReader(procedureName, parameters);
            if (reader.HasRows)
            {
                var objectType = typeof(T);
                while (reader.Read())
                {
                    T item = new T();
                    for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
                    {
                        var objectProperty = objectType.GetProperties()
                            .Where(p => p.GetCustomAttributes(typeof(DataColumn), true)
                                .Where(a => ((DataColumn)a).ColumnName == reader.GetName(columnIndex))
                                .Any()
                                ).FirstOrDefault();
                        if (objectProperty != null)
                        {
                            var dataValue = reader.GetValue(columnIndex);
                            objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
                        }
                    }
                    yield return item;
                }
            }
            reader.Close();
        }

        #endregion
    

You can find whole class as the attachment for this article.

It is pretty simple, lightweight and straight forward solution that can be easily implemented and used for applications that require fast data reading from database.

The following sample code shows how flexible is this lightweight solution, that you can bind pretty much any model to a result of SqlDataReader or DataTable. It is even better to use SqlDataReader as it is much faster and in the end you get the same collection of models or a single model whether you are retrieving DataTable or SqlDataReader.

using System;
using System.Collections.Generic;
using SimpleData.DataAccessLayer;
namespace DataAccessTest
{
    class MyDataAccess : SimpleData.DataAccessLayer.DataAccessBase
    {
        public MyDataAccess() :
            base()
        {

        }
    }

    public class EmployeeModel
    {
        [DataColumn("id")]
        public int Id { get; set; }

        [DataColumn("first_name")]
        public string FirstName { get; set; }

        [DataColumn("last_name")]
        public string LastName { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var dal = new MyDataAccess();
            var employees = dal.ExecuteProcedureMultipleModel<EmployeeModel>("GetEmployees",
                new Dictionary<string, IConvertible>(){
                    {"SectorId", 23},
                    {"IncludeInactive", true}
                });
        }
    }

}
    

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article