.Net DataReader Wrapper

.Net DataReader Wrapper

.Net DataReader Wrapper

UPDATE 2.0!!!

Wow, just realized it’s been awhile since i posted anything… well kiddies, time for some new code.

Although I have grown up loveing, carressing, and mutilating Visual Basic, I have decided to take a stab at some C# since most of my projects lately have comes across in the form of PHP.  While I do love VB still, I am starting to fall hard for some C# sexyness (<- spelling).

I have a VB version of what I am about to post as well, and though the language structure is different, there really aren’t very many differences between the 2.  I thought I may get some sort of performance boost out of this “conversion” but alas, I was mistaken.  Both languages performed admirably clocking in at 1.19secs each to pull in 87,000 records from a pretty complicated query.

I have added in some .Net 4.5 niceties to the C# version, that I will port over to VB, but for now let’s let that sleeping beast lie in wait and get to the goodies!

Here is the code kiddies… have fun and do what you will with it.  Just do me a favor… if you make it better, let me know what you did, and where i went wrong please?!?  I’ll leave it up to you to figure out how to use it properly, but I’ll also post my test code first 😉

Test: program.cs

using o7th.Class.Library.Data;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using System.Linq;

namespace Testing
{
    class Program
    {
        static void Main(string[] args)
        {
            long startTime = DateTime.Now.Ticks;

            IList<Typing> _T = default(IList<Typing>);

            _T = Wrapper.GetResults<Typing>("List.ZipSearch", System.Data.CommandType.StoredProcedure, 
                new string[] {"@ZipCode", "@RadiusMile"}, 
                new object[]{"01020", 100}, 
                new System.Data.SqlDbType[]{System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Float});

            long endTime = DateTime.Now.Ticks;
            TimeSpan timeTaken = new TimeSpan(endTime - startTime);
            Console.WriteLine("Task Took: " + timeTaken + " for: " + _T.Count + " records.");

            Thread.Sleep(2000);

            long startTime2 = DateTime.Now.Ticks;

            IList<Typing> _T2 = default(IList<Typing>);

            _T2 = WrapperAsync.GetResults<Typing>("List.ZipSearch", System.Data.CommandType.StoredProcedure,
                new string[] { "@ZipCode", "@RadiusMile" },
                new object[] { "01020", 100 },
                new System.Data.SqlDbType[] { System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Float });

            long endTime2 = DateTime.Now.Ticks;
            TimeSpan timeTaken2 = new TimeSpan(endTime2 - startTime2);
            Console.WriteLine("Task Took: " + timeTaken2 + " for: " + _T2.Count() + " records.");

            Console.WriteLine("");
            Console.WriteLine("Press any key to continue...");

            Console.ReadKey();

        }

        partial class Typing {
            public long ZipID { get; set; }
        }

    }

}

Access.cs

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace o7th.Class.Library.Data
{
    internal class Access : IDisposable
    {

#region "Properties"

        // Set the type of query we are running
        private CommandType _QT;
        internal CommandType QueryType { set { _QT = value; } }

        // Set the actual query text to run
        private string _Qry;
        internal string Query { set { _Qry = value; } }

        // Set the parameter names if there are any
        private string[] _PNs;
        internal string[] ParameterNames { set { _PNs = value; } }

        // Set the parameter values if there are any
        private object[] _PVs;
        internal object[] ParameterValues { set { _PVs = value; } }

        // Set the actual Sql Data Types if there are any
        private System.Data.SqlDbType[] _DTs;
        internal System.Data.SqlDbType[] ParameterDataTypes { set { _DTs = value; } }

        // Check to see if there are any parameters passed
        private bool AreParams() {
            // Check to see if the values and names are null first
            if (_PVs != null && _PNs != null) {
                try {
                    Type _t_pv = _PVs.GetType();
                    Type _t_pn = _PNs.GetType();
                    if (_t_pv.IsArray && _t_pn.IsArray) {
                        return (_PVs.Length > 0 && _PNs.Length > 0) ? true : false;
                    } else {
                        return false;
                    }
                } catch {
                    // yes I meant to do this, we really don't need to get the exception here
                    return false;
                }
            } else {
                return false;
            }
        }

        // Get a return message if any
        private string _Msg;
        internal string Message { get { return _Msg; } }

        // Get the connection string from our class assemblies settings
        internal string _ConnString { get { return Properties.Settings.Default.ConnectionString; } }

        // Set the official Sql Reader object
        private SqlDataReader _Rdr;
        // Set the official Sql Connection object
        private SqlConnection _Conn;
        // Set the official Sql Command object
        private SqlCommand _Cmd;
        // Hack for seeing if we're disposed already
        private bool disposedValue;

#endregion

        // Constructor
        internal Access() {
            Invoke();
        }

        // Official Constructor.  We can thread these 2 becuase they are not being used yet, and it makes it slightly more efficient
        internal void Invoke() {
            try { 
                Parallel.Invoke(() => {
                    _Conn = new SqlConnection(_ConnString);
                }, () =>
                {
                    _Cmd = new SqlCommand();
                });
            }catch (Exception ex) {
                _Msg = "Access.Invoke Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Invoke", _Msg);
            }
        }

        /// <summary>
        /// Return a SqlDataReader based on the properties passed to this class
        /// </summary>
        /// <returns></returns>
        internal SqlDataReader GetResults() {
            try {
                // check for parameters
                if (AreParams()) {
                    PrepareParams(_Cmd);
                }
                // set our connection
                _Cmd.Connection = _Conn;
                // set the type of query to run
                _Cmd.CommandType = _QT;
                // set the actual query to run
                _Cmd.CommandText = _Qry;
                // open the connection
                _Cmd.Connection.Open();
                // prepare the command with any parameters that may have gotten added
                _Cmd.Prepare();
                // Execute the SqlDataReader, and set the connection to close once returned
                _Rdr = _Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                // clear out any parameters
                _Cmd.Parameters.Clear();
                // return our reader object
                return (!_Rdr.HasRows) ? null: _Rdr;
            }
            catch (SqlException SqlEx) {
                _Msg += "Acccess.GetResults SqlException: " + SqlEx.Message;
                ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.Access.GetResults", _Msg);
                return null;
            }
            catch (Exception ex) {
                _Msg += "Acccess.GetResults Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.GetResults", _Msg);
                return null;
            }
        }

        /// <summary>
        /// Execute a non-return query, and return the success
        /// </summary>
        /// <returns></returns>
        internal bool Execute() {
            try {
                // check for parameters
                if (AreParams()) {
                    PrepareParams(_Cmd);
                }
                // set our connection
                _Cmd.Connection = _Conn;
                // set the type of query to run
                _Cmd.CommandType = _QT;
                // set the actual query to run
                _Cmd.CommandText = _Qry;
                // open the connection
                _Cmd.Connection.Open();
                // prepare the command with any parameters that may have gotten added
                _Cmd.Prepare();
                // execute the non-returnable query against the database
                _Cmd.ExecuteNonQuery();
                // clear out any parameters
                _Cmd.Parameters.Clear();
                // executed successfully (otherwise would have thrown an exception)
                return true;
            } catch (SqlException SqlEx) {
                _Msg += "Access.Execute SqlException: " + SqlEx.Message;
                ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.Access.Execute", _Msg);
                return false;
            }
            catch (Exception ex) {
                _Msg += "Access.Execute Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Execute", _Msg);
                return false;
            }
        }

        /// <summary>
        /// Execute a query with a return value.  Used in Selecting the ID of the last inserted record.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="_DefVal"></param>
        /// <returns></returns>
        internal T ExecuteWithReturn<T>(T _DefVal) {
            try {
                T _Ret;
                // check for parameters
                if (AreParams()) {
                    PrepareParams(_Cmd);
                }
                // set our connection
                _Cmd.Connection = _Conn;
                // set the type of query to run
                _Cmd.CommandType = _QT;
                // set the actual query to run
                _Cmd.CommandText = _Qry;
                // open the connection
                _Cmd.Connection.Open();
                // prepare the command with any parameters that may have gotten added
                _Cmd.Prepare();
                T _T = (T)_Cmd.ExecuteScalar();
                _Ret = (_T is DBNull) ? default(T) : _T;
                // clear out _T
                _T = default(T);
                // clear out any parameters
                _Cmd.Parameters.Clear();
                // return the single return value from the query run
                return _Ret;
            } catch (SqlException SqlEx) {
                _Msg += "Access.ExecuteWithReturn SqlException: " + SqlEx.Message;
                ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.Access.ExecuteWithReturn", _Msg);
                return default(T);
            } catch (Exception ex) {
                _Msg += "Access.ExecuteWithReturn Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.ExecuteWithReturn", _Msg);
                return default(T);
            }
        }

        /// <summary>
        /// Prepare our parameters, adding them and forcing a valid data length
        /// </summary>
        /// <param name="objCmd"></param>
        protected void PrepareParams(SqlCommand objCmd) {
            try  {
                // set our initial Data Size
                int _DataSize = 0;
                // get the number of Parameter Values passed in
                int _PCt = _PVs.GetUpperBound(0);
                // begin array check
                Type _t_dt = _DTs.GetType();
                // start looping over our parameters
                for (int i = 0; i <= _PCt; ++i) {
                    // make sure that the data types are actually an array
                    if (_t_dt.IsArray) {
                        // select which datatype, and force the official size
                        switch ((int)_DTs[i]) {
                            case 0:
                            case 33:
                            case 6:
                            case 9:
                            case 13:
                            case 19:
                                _DataSize = 8;
                                break;
                            case 1:
                            case 3:
                            case 7:
                            case 10:
                            case 12:
                            case 21:
                            case 22:
                            case 23:
                            case 25:
                                _DataSize = _PVs[i].ToString().Length;
                                break;
                            case 2:
                            case 20:
                                _DataSize = 1;
                                break;
                            case 5:
                                _DataSize = 17;
                                break;
                            case 8:
                            case 17:
                            case 15:
                                _DataSize = 4;
                                break;
                            case 14:
                                _DataSize = 16;
                                break;
                            case 31:
                                _DataSize = 3;
                                break;
                            case 32:
                                _DataSize = 5;
                                break;
                            case 16:
                                _DataSize = 2;
                                break;
                        }
                        // add our parameter to the command object
                        objCmd.Parameters.Add(_PNs[i], _DTs[i], _DataSize).Value = _PVs[i]; 
                    } else {
                        // if the datatypes were not set, try to add them generically
                        objCmd.Parameters.AddWithValue(_PNs[i], _PVs[i]);
                    }
                }
                // clean up
                _PNs = null;_PVs = null;_DTs = null;
            } catch (Exception ex) {
                _Msg += "Access.PrepareParams Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.PrepareParams", _Msg);
            }
        }

#region "Dispose Support"

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue && disposing) {
                try
                {
                    _Qry = string.Empty;
                    _Rdr.Close();
                    _Rdr.Dispose();
                    _Cmd.Connection.Close();
                    _Conn.Close();
                    _Cmd.Dispose();
                    _Conn.Dispose();
                    _Msg = null;
                }
                catch(Exception ex) {
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Dispose", "");
                }
            }
            disposedValue = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

#endregion

    }
}

AccessAsync.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace o7th.Class.Library.Data
{
    internal class AccessAsync : IDisposable
    {

        #region "Properties"

        // Set the type of query we are running
        private CommandType _QT;
        internal CommandType QueryType { set { _QT = value; } }

        // Set the actual query text to run
        private string _Qry;
        internal string Query { set { _Qry = value; } }

        // Set the parameter names if there are any
        private string[] _PNs;
        internal string[] ParameterNames { set { _PNs = value; } }

        // Set the parameter values if there are any
        private object[] _PVs;
        internal object[] ParameterValues { set { _PVs = value; } }

        // Set the actual Sql Data Types if there are any
        private System.Data.SqlDbType[] _DTs;
        internal System.Data.SqlDbType[] ParameterDataTypes { set { _DTs = value; } }

        // Check to see if there are any parameters passed
        private bool AreParams()
        {
            // Check to see if the values and names are null first
            if (_PVs != null && _PNs != null)
            {
                try
                {
                    Type _t_pv = _PVs.GetType();
                    Type _t_pn = _PNs.GetType();
                    if (_t_pv.IsArray && _t_pn.IsArray)
                    {
                        return (_PVs.Length > 0 && _PNs.Length > 0) ? true : false;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch
                {
                    // yes I meant to do this, we really don't need to get the exception here
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        // Get a return message if any
        private string _Msg;
        internal string Message { get { return _Msg; } }

        // Get the connection string from our class assemblies settings
        internal string _ConnString { get { return Properties.Settings.Default.ConnectionString; } }

        // Set the official Sql Reader object
        private SqlDataReader _Rdr;
        // Set the official Sql Connection object
        private SqlConnection _Conn;
        // Set the official Sql Command object
        private SqlCommand _Cmd;
        // Hack for seeing if we're disposed already
        private bool disposedValue;

        #endregion

        // Constructor
        internal AccessAsync()
        {
            Invoke();
        }

        // Official Constructor.  We can thread these 2 becuase they are not being used yet, and it makes it slightly more efficient
        internal void Invoke()
        {
            try
            {
                Parallel.Invoke(() =>
                {
                    _Conn = new SqlConnection(_ConnString);
                }, () =>
                {
                    _Cmd = new SqlCommand();
                });
            }
            catch (Exception ex)
            {
                _Msg = "Access.Invoke Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Invoke", _Msg);
            }
        }

        /// <summary>
        /// Return a SqlDataReader based on the properties passed to this class
        /// </summary>
        /// <returns></returns>
        internal async Task<SqlDataReader> GetResults()
        {
            try
            {
                // check for parameters
                if (AreParams())
                {
                    PrepareParams(_Cmd);
                }
                // set our connection
                _Cmd.Connection = _Conn;
                // set the type of query to run
                _Cmd.CommandType = _QT;
                // set the actual query to run
                _Cmd.CommandText = _Qry;
                // open the connection
                await _Cmd.Connection.OpenAsync();
                // prepare the command with any parameters that may have gotten added
                _Cmd.Prepare();
                // Execute the SqlDataReader, and set the connection to close once returned
                _Rdr = await _Cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
                // clear out any parameters
                _Cmd.Parameters.Clear();
                // return our reader object
                return (!_Rdr.HasRows) ? null : _Rdr;
            }
            catch (SqlException SqlEx)
            {
                _Msg += "Acccess.GetResults SqlException: " + SqlEx.Message;
                ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.Access.GetResults", _Msg);
                return null;
            }
            catch (Exception ex)
            {
                _Msg += "Acccess.GetResults Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.GetResults", _Msg);
                return null;
            }
        }

        /// <summary>
        /// Execute a non-return query, and return the success
        /// </summary>
        /// <returns></returns>
        internal bool Execute()
        {
            try
            {
                // check for parameters
                if (AreParams())
                {
                    PrepareParams(_Cmd);
                }
                // set our connection
                _Cmd.Connection = _Conn;
                // set the type of query to run
                _Cmd.CommandType = _QT;
                // set the actual query to run
                _Cmd.CommandText = _Qry;
                // open the connection
                _Cmd.Connection.OpenAsync();
                // prepare the command with any parameters that may have gotten added
                _Cmd.Prepare();
                // execute the non-returnable query against the database
                _Cmd.ExecuteNonQueryAsync();
                // clear out any parameters
                _Cmd.Parameters.Clear();
                // executed successfully (otherwise would have thrown an exception)
                return true;
            }
            catch (SqlException SqlEx)
            {
                _Msg += "Access.Execute SqlException: " + SqlEx.Message;
                ErrorReporting.WriteEm.WriteItem(SqlEx, "o7th.Class.Library.Data.Access.Execute", _Msg);
                return false;
            }
            catch (Exception ex)
            {
                _Msg += "Access.Execute Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Execute", _Msg);
                return false;
            }
        }

        /// <summary>
        /// Prepare our parameters, adding them and forcing a valid data length
        /// </summary>
        /// <param name="objCmd"></param>
        protected void PrepareParams(SqlCommand objCmd)
        {
            try
            {
                // set our initial Data Size
                int _DataSize = 0;
                // get the number of Parameter Values passed in
                int _PCt = _PVs.GetUpperBound(0);
                // begin array check
                Type _t_dt = _DTs.GetType();
                // start looping over our parameters
                for (int i = 0; i <= _PCt; ++i)
                {
                    // make sure that the data types are actually an array
                    if (_t_dt.IsArray)
                    {
                        // select which datatype, and force the official size
                        switch ((int)_DTs[i])
                        {
                            case 0:
                            case 33:
                            case 6:
                            case 9:
                            case 13:
                            case 19:
                                _DataSize = 8;
                                break;
                            case 1:
                            case 3:
                            case 7:
                            case 10:
                            case 12:
                            case 21:
                            case 22:
                            case 23:
                            case 25:
                                _DataSize = _PVs[i].ToString().Length;
                                break;
                            case 2:
                            case 20:
                                _DataSize = 1;
                                break;
                            case 5:
                                _DataSize = 17;
                                break;
                            case 8:
                            case 17:
                            case 15:
                                _DataSize = 4;
                                break;
                            case 14:
                                _DataSize = 16;
                                break;
                            case 31:
                                _DataSize = 3;
                                break;
                            case 32:
                                _DataSize = 5;
                                break;
                            case 16:
                                _DataSize = 2;
                                break;
                        }
                        // add our parameter to the command object
                        objCmd.Parameters.Add(_PNs[i], _DTs[i], _DataSize).Value = _PVs[i];
                    }
                    else
                    {
                        // if the datatypes were not set, try to add them generically
                        objCmd.Parameters.AddWithValue(_PNs[i], _PVs[i]);
                    }
                }
                // clean up
                _PNs = null; _PVs = null; _DTs = null;
            }
            catch (Exception ex)
            {
                _Msg += "Access.PrepareParams Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.PrepareParams", _Msg);
            }
        }

        #region "Dispose Support"

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue && disposing)
            {
                try
                {
                    _Qry = string.Empty;
                    _Rdr.Close();
                    _Rdr.Dispose();
                    _Cmd.Connection.Close();
                    _Conn.Close();
                    _Cmd.Dispose();
                    _Conn.Dispose();
                    _Msg = null;
                }
                catch (Exception ex)
                {
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Access.Dispose", "");
                }
            }
            disposedValue = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion

    }
}

Wrapper.cs

using System;
using System.Collections.Generic;
using System.Data.Common;

namespace o7th.Class.Library.Data {

    /// </p>
<summary>
    /// Wrapper class for our data access
    /// </summary>
<p>
    public class Wrapper {

        /// </p>
<summary>
        /// Setup our return message if any
        /// </summary>
<p>
        public static string Message { set { _Msg = value; } get { return _Msg; } }
        private static string _Msg;

        // Instantiate our caching methods
        internal static Common.CustomCache _Cache = new Common.CustomCache();

        // Map our datareader object to a strongly typed list
        private static IList<T> Map<T>(DbDataReader dr) where T : new()
        {
            try
            {
                // initialize our returnable list
                List<T> list = new List<T>();
                // fire up the lamda mapping
                var converter = new Converter<T>(dr);
                while (dr.Read())
                {
                    // read in each row, and properly map it to our T object
                    var obj = converter.CreateItemFromRow();
                    // add it to our list
                    list.Add(obj);
                }
                // reutrn it
                return list;
            }
            catch (Exception ex)
            {
                // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                _Msg += "Wrapper.Map Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Map", _Msg);
                // make sure this method returns a default List
                return default(List<T>);
            }
        }

        /// </p>
<summary>
        /// Get the results of a stronly-typed IList Object
        /// </summary>
<p>
        /// <typeparam name="T">Strongly-Typed class of objects that should be returned</typeparam>
        /// <param name="_Qry">The query to run</param>
        /// <param name="_QryType">The Query Type to run</param>
        /// <param name="_ParamNames">The Parameters' names to pass to the query, if any</param>
        /// <param name="_ParamVals">The Parameters' values to pass to the query, if any</param>
        /// <param name="_ParamDTs">The Parameters' data types to pass to the query, if any</param>
        /// <param name="_ShouldCache">Should we cache the response</param>
        /// <param name="_CacheID">Cache item name</param>
        /// <returns>Strongly Typed ilist of objects</returns>
        public static IList<T> GetResults<T>(string _Qry, System.Data.CommandType _QryType,
                                            string[] _ParamNames = null,
                                            object[] _ParamVals = null,
                                            System.Data.SqlDbType[] _ParamDTs = null,
                                            bool _ShouldCache = false,
                                            string _CacheID = "") where T : new()
        {
            // Create a reference to a potential already cached IList
            IList<T> _CachedItem = _Cache.Get<IList<T>>(_CacheID);
            // If we're already cached, there's no need to fire up the data access objects, so return the cached item instead
            if (_CachedItem != null && _ShouldCache)
            {
                return _CachedItem;
            }
            else
            {
                // Fire up our data access object
                using (Access db = new Access())
                {
                    try
                    {
                        // create a new ilist reference of our strongly typed class
                        IList<T> _Query = default(IList<T>);
                        // set the query type
                        db.QueryType = _QryType;
                        // set the query text
                        db.Query = _Qry;
                        // make sure we've got some parameters, if we do the set them to our db access object
                        if (_ParamNames != null)
                        {
                            // set the parameter names
                            db.ParameterNames = _ParamNames;
                            // set the parameter values
                            db.ParameterValues = _ParamVals;
                            // set the parameter data types
                            db.ParameterDataTypes = _ParamDTs;
                        }
                        // start using our db access 🙂  Fire off the GetResults method and return back a SqlDataReader to work on
                        using (DbDataReader r = db.GetResults())
                        {
                            // make sure the data reader actually exists and contains some results
                            if (r != null)
                            {
                                // map the data reader to our strongly type(s)
                                _Query = Map<T>(r);
                            }
                        }
                        // check if we should cache the results
                        if (_ShouldCache)
                        {
                            // if so, set the query object to the cache
                            _Cache.Set<IList<T>>(_Query, _CacheID);
                        }
                        // return our strongly typed list
                        return _Query;
                    }
                    catch (Exception ex)
                    {
                        // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                        _Msg += "Wrapper.GetResults Exception: " + ex.Message + db.Message;
                        ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.GetResults", _Msg);
                        // make sure this method returns a default List
                        return default(IList<T>);
                    }
                }
            }
        }


        /// </p>
<summary>
        /// Execute a query against the database.  Usually used for IUD Operations
        /// </summary>
<p>
        /// <param name="_Qry">The query to execute</param>
        /// <param name="_QryType">The Query Type to run</param>
        /// <param name="_ParamNames">The Parameters' names to pass to the query, if any</param>
        /// <param name="_ParamVals">The Parameters' values to pass to the query, if any</param>
        /// <param name="_ParamDTs">The Parameters' data types to pass to the query, if any</param>
        /// <returns>Boolean of success</returns>
        public static bool Execute(string _Qry, System.Data.CommandType _QryType,
                                    string[] _ParamNames = null,
                                    object[] _ParamVals = null,
                                    System.Data.SqlDbType[] _ParamDTs = null) {
            // setup a reference for our success return
            bool _T;
            // Fire up our data access object
            using (Access db = new Access()) {
                try {
                    // set the query type
                    db.QueryType = _QryType;
                    // set the query text
                    db.Query = _Qry;
                    // make sure we've got some parameters, if we do the set them to our db access object
                    if (_ParamNames != null)
                    {
                        // set the parameter names
                        db.ParameterNames = _ParamNames;
                        // set the parameter values
                        db.ParameterValues = _ParamVals;
                        // set the parameter data types
                        db.ParameterDataTypes = _ParamDTs;
                    }
                    // execute the query and return if it was successful or not
                    _T = db.Execute();
                    // return it
                    return _T;
                }
                catch (Exception ex)
                {
                    // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                    _Msg += "Wrapper.Execute Exception: " + ex.Message + db.Message;
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Execute", _Msg);
                    // make sure this method returns a default value of false
                    return false;
                }
            }
        }

        /// </p>
<summary>
        /// Executes a query against the database, and returns a value
        /// </summary>
<p>
        /// <typeparam name="T">Strongly Typed Object for return</typeparam>
        /// <param name="_Qry">The query to execute</param>
        /// <param name="_QryType">The Query Type to run</param>
        /// <param name="_ParamNames">The Parameters' names to pass to the query, if any</param>
        /// <param name="_ParamVals">The Parameters' values to pass to the query, if any</param>
        /// <param name="_ParamDTs">The Parameters' data types to pass to the query, if any</param>
        /// <param name="_DefVal">Default value that should get returned if none are</param>
        /// <returns>Strongly Typed object from the query executed</returns>
        public static T ExecuteWithReturn<T>(string _Qry, System.Data.CommandType _QryType,
                                    string[] _ParamNames = null,
                                    object[] _ParamVals = null,
                                    System.Data.SqlDbType[] _ParamDTs = null,
                                    object _DefVal = null) where T : new() {
            // setup a new reference to T
            T _T;
            // Fire up our data access object
            using (Access db = new Access()) {
                try{
                    // set the query type
                    db.QueryType = _QryType;
                    // set the query text
                    db.Query = _Qry;
                    // make sure we've got some parameters, if we do the set them to our db access object
                    if (_ParamNames != null)
                    {
                        // set the parameter names
                        db.ParameterNames = _ParamNames;
                        // set the parameter values
                        db.ParameterValues = _ParamVals;
                        // set the parameter data types
                        db.ParameterDataTypes = _ParamDTs;
                    }
                    // execute the query and return the results back to _T
                    _T = db.ExecuteWithReturn<T>((T)_DefVal);
                    // return it
                    return (_T is DBNull) ? default(T) : _T;
                }
                catch (Exception ex)
                {
                    // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                    _Msg += "Wrapper.ExecuteWithReturn Exception: " + ex.Message + db.Message;
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.ExecuteWithReturn", _Msg);
                    // return the default value for the strong typed object
                    return default(T);
                }
            }
        }
    }

}

WrapperAsync.cs

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Threading.Tasks;

namespace o7th.Class.Library.Data
{

    /// </p>
<summary>
    /// Wrapper class for our data access, only allows a resultset and an execution, does not contain ExecuteWithReturn
    /// </summary>
<p>
    public class WrapperAsync
    {

        /// </p>
<summary>
        /// Setup our return message if any
        /// </summary>
<p>
        public static string Message { set { _Msg = value; } get { return _Msg; } }
        private static string _Msg;

        // Instantiate our caching methods
        internal static Common.CustomCache _Cache = new Common.CustomCache();

        // Map our datareader object to a strongly typed list
        private static async Task<IList<T>> Map<T>(DbDataReader dr) where T : new()
        {
            try
            {
                // initialize our returnable list
                List<T> list = new List<T>();
                // fire up the lamda mapping
                var converter = new Converter<T>(dr);
                while (await dr.ReadAsync())
                {
                    // read in each row, and properly map it to our T object
                    var obj = converter.CreateItemFromRow();
                    // add it to our list
                    list.Add(obj);
                }
                // reutrn it
                return list;
            }
            catch (Exception ex)
            {
                // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                _Msg += "Wrapper.Map Exception: " + ex.Message;
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Map", _Msg);
                // make sure this method returns a default List
                return default(IList<T>);
            }
        }

        /// </p>
<summary>
        /// Get the results of a stronly-typed IList Object Asyncronously
        /// </summary>
<p>
        /// <typeparam name="T">Strongly-Typed class of objects that should be returned</typeparam>
        /// <param name="_Qry">The query to run</param>
        /// <param name="_QryType">The Query Type to run</param>
        /// <param name="_ParamNames">The Parameters' names to pass to the query, if any</param>
        /// <param name="_ParamVals">The Parameters' values to pass to the query, if any</param>
        /// <param name="_ParamDTs">The Parameters' data types to pass to the query, if any</param>
        /// <param name="_ShouldCache">Should we cache the response</param>
        /// <param name="_CacheID">Cache item name</param>
        /// <returns>Strongly Typed ilist of objects</returns>
        public static IList<T> GetResults<T>(string _Qry, System.Data.CommandType _QryType,
                                            string[] _ParamNames = null,
                                            object[] _ParamVals = null,
                                            System.Data.SqlDbType[] _ParamDTs = null,
                                            bool _ShouldCache = false,
                                            string _CacheID = "") where T : new()
        {
            // Create a reference to a potential already cached IList
            IList<T> _CachedItem = _Cache.Get<IList<T>>(_CacheID);
            // If we're already cached, there's no need to fire up the data access objects, so return the cached item instead
            if (_CachedItem != null && _ShouldCache)
            {
                return _CachedItem;
            }
            else
            {
                // Fire up our data access object
                using (AccessAsync db = new AccessAsync())
                {
                    try
                    {
                        // create a new ilist reference of our strongly typed class
                        IList<T> _Query = null;
                        // set the query type
                        db.QueryType = _QryType;
                        // set the query text
                        db.Query = _Qry;
                        // make sure we've got some parameters, if we do the set them to our db access object
                        if (_ParamNames != null)
                        {
                            // set the parameter names
                            db.ParameterNames = _ParamNames;
                            // set the parameter values
                            db.ParameterValues = _ParamVals;
                            // set the parameter data types
                            db.ParameterDataTypes = _ParamDTs;
                        }
                        // start using our db access 🙂  Fire off the GetResults method and return back a SqlDataReader to work on
                        using (DbDataReader r = db.GetResults().Result)
                        {
                            // make sure the data reader actually exists and contains some results
                            if (r != null)
                            {
                                // map the data reader to our strongly type(s)
                                _Query = Map<T>(r).Result;
                            }
                        }
                        // check if we should cache the results
                        if (_ShouldCache)
                        {
                            // if so, set the query object to the cache
                            _Cache.Set<IList<T>>(_Query, _CacheID);
                        }
                        // return our strongly typed list
                        return _Query;
                    }
                    catch (Exception ex)
                    {
                        // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                        _Msg += "Wrapper.GetResults Exception: " + ex.Message + db.Message;
                        ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.GetResults", _Msg);
                        // make sure this method returns a default List
                        return default(IList<T>);
                    }
                }
            }
        }


        /// </p>
<summary>
        /// Execute a query against the database.  Usually used for IUD Operations
        /// </summary>
<p>
        /// <param name="_Qry">The query to execute</param>
        /// <param name="_QryType">The Query Type to run</param>
        /// <param name="_ParamNames">The Parameters' names to pass to the query, if any</param>
        /// <param name="_ParamVals">The Parameters' values to pass to the query, if any</param>
        /// <param name="_ParamDTs">The Parameters' data types to pass to the query, if any</param>
        /// <returns>Boolean of success</returns>
        public static bool Execute(string _Qry, System.Data.CommandType _QryType,
                                    string[] _ParamNames = null,
                                    object[] _ParamVals = null,
                                    System.Data.SqlDbType[] _ParamDTs = null)
        {
            // setup a reference for our success return
            bool _T;
            // Fire up our data access object
            using (AccessAsync db = new AccessAsync())
            {
                try
                {
                    // set the query type
                    db.QueryType = _QryType;
                    // set the query text
                    db.Query = _Qry;
                    // make sure we've got some parameters, if we do the set them to our db access object
                    if (_ParamNames != null)
                    {
                        // set the parameter names
                        db.ParameterNames = _ParamNames;
                        // set the parameter values
                        db.ParameterValues = _ParamVals;
                        // set the parameter data types
                        db.ParameterDataTypes = _ParamDTs;
                    }
                    // execute the query and return if it was successful or not
                    _T = db.Execute();
                    // return it
                    return _T;
                }
                catch (Exception ex)
                {
                    // Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
                    _Msg += "Wrapper.Execute Exception: " + ex.Message + db.Message;
                    ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Wrapper.Execute", _Msg);
                    // make sure this method returns a default value of false
                    return false;
                }
            }
        }

    }

}

Converter.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace o7th.Class.Library.Data
{

    /// </p>
<summary>
    /// Converter class to convert returned Sql Records to strongly typed classes
    /// </summary>
<p>
    /// <typeparam name="T">Type of the object we'll convert too</typeparam>
    internal class Converter<T> where T : new()
    {
        // Declare our _converter delegate
        readonly Func<IDataReader, T> _converter;
        // Declare our internal dataReader
        readonly IDataReader dataReader;

        // Build our mapping based on the properties in the class/type we've passed in to the class
        private Func<IDataReader, T> GetMapFunc()
        {
            try
            {
                // declare our field count
                int _fc = dataReader.FieldCount;
                // declare our expression list
                List<Expression> exps = new List<Expression>();
                // build our parameters for the expression tree
                ParameterExpression paramExp = Expression.Parameter(typeof(IDataRecord));
                ParameterExpression targetExp = Expression.Variable(typeof(T));
                // Add our expression tree assignment to the exp list
                exps.Add(Expression.Assign(targetExp, Expression.New(targetExp.Type)));
                //does int based lookup
                PropertyInfo indexerInfo = typeof(IDataRecord).GetProperty("Item", new[] { typeof(int) });
                // grab a collection of column names from our data reader
                var columnNames = Enumerable.Range(0, _fc).Select(i => new { i, name = dataReader.GetName(i)}).AsParallel();
                // loop through all our columns and map them properly
                foreach (var column in columnNames)
                {
                    var property = targetExp.Type.GetProperty(column.name, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                    if (property == null)
                        continue;
                    // build our expression tree to map the column to the T
                    ConstantExpression columnIndexExp = Expression.Constant(column.i);
                    IndexExpression cellExp = Expression.MakeIndex(paramExp, indexerInfo, new[] { columnIndexExp });
                    // Column value expression
                    ParameterExpression cellValueExp = Expression.Variable(typeof(object));
                    // Check for nulls, and set a default property value
                    ConditionalExpression convertExp = Expression.Condition(Expression.Equal(cellValueExp, Expression.Constant(DBNull.Value)), Expression.Default(property.PropertyType), Expression.Convert(cellValueExp, property.PropertyType));
                    // set the value/column/type exression
                    BlockExpression cellValueReadExp = Expression.Block(new[] { cellValueExp }, Expression.Assign(cellValueExp, cellExp), convertExp);
                    // Assign the property/value to our expression
                    BinaryExpression bindExp = Expression.Assign(Expression.Property(targetExp, property), cellValueReadExp);
                    // add it to our expression list
                    exps.Add(bindExp);
                }
                // add the originating map to our expression list
                exps.Add(targetExp);
                // return a compiled cached map
                return Expression.Lambda<Func<IDataReader, T>>(Expression.Block(new[] { targetExp }, exps), paramExp).Compile();
            }
            catch (Exception ex)
            {
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Converter.GetMapFunc", ex.Message);
                return default(Func<IDataReader, T>);
            }
        }

        // initialize
        internal Converter(IDataReader dataReader)
        {
            // initialize the internal datareader
            this.dataReader = dataReader;
            // build our map
            _converter = GetMapFunc();
        }

        // create and map each column to it's respective object
        internal T CreateItemFromRow()
        {
            try
            {
                // convert the datareader record to our map
                return _converter(dataReader);
            }
            catch (DataException dex)
            {
                ErrorReporting.WriteEm.WriteItem(dex, "o7th.Class.Library.Data.Converter.CreateItemFromRow-DB", dex.Message);
                return default(T);
            }
            catch (Exception ex)
            {
                ErrorReporting.WriteEm.WriteItem(ex, "o7th.Class.Library.Data.Converter.CreateItemFromRow", ex.Message);
                return default(T);
            }
        }

    }
}

Used properly these classes will allow you to map your strongly typed classes to the SqlDataReader object, to which you could even convert that into just a DataReader… but ehh.

SIDE NOTE:  You’ll need to come up with your own error reporting 😉

Happy Coding!
~Kevin

Categories


Let Us Help

Get You Online

Contact Us Today

Important Cookie Information
Our website uses cookies. By continuing to browse the site you are agreeing to our use of cookies. For more details about cookies and their use, please see our Cookie Policy.