Western Mass Hosting's c# Articles

.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