.Net DataReader Wrapper

Posted: December 20, 2013

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: .Net C# Development MS SQL MySQL Security Software Source Code

Tags: .net c# development hosting mssql mysql web development

Moving a ModX Revolution Site to Another Server

Simple Navigator Snippet for ModX Revolution

Kevin Pirnie

20+ Years of PC and server maintenance & over 15+ years of web development/design experience; you can rest assured that I take every measure possible to ensure your computers are running to their peak potentials. I treat them as if they were mine, and I am quite a stickler about keeping my machines up to date and optimized to run as well as they can.

Revised: June 8, 2021

Thank you for choosing to be part of my website at https://kevinpirnie.com (“Company”, “I”, “me”, “mine”). I am committed to protecting your personal information and your right to privacy. If you have any questions or concerns about this privacy notice, or my practices with regards to your personal information, please contact me at .

When you visit my website https://kevinpirnie.com (the “Website”), and more generally, use any of my services (the “Services”, which include the Website), I appreciate that you are trusting me with your personal information. I take your privacy very seriously. In this privacy notice, I seek to explain to you in the clearest way possible what information we collect, how I use it and what rights you have in relation to it. I hope you take some time to read through it carefully, as it is important. If there are any terms in this privacy notice that you do not agree with, please discontinue use of my Services immediately.

This privacy notice applies to all information collected through my Services (which, as described above, includes our Website), as well as, any related services, sales, marketing or events.

Please read this privacy notice carefully as it will help you understand what I do with the information that I collect.

1. WHAT INFORMATION DO I COLLECT?

Information automatically collected

In Short: Some information – such as your Internet Protocol (IP) address and/or browser and device characteristics – is collected automatically when you visit my Website.

I automatically collect certain information when you visit, use or navigate the Website. This information does not reveal your specific identity (like your name or contact information) but may include device and usage information, such as your IP address, browser and device characteristics, operating system, language preferences, referring URLs, device name, country, location, information about how and when you use our Website and other technical information. This information is primarily needed to maintain the security and operation of our Website, and for our internal analytics and reporting purposes.

Like many businesses, I also collect information through cookies and similar technologies.

The information I collect includes:
Log and Usage Data. Log and usage data is service-related, diagnostic, usage and performance information our servers automatically collect when you access or use our Website and which we record in log files. Depending on how you interact with us, this log data may include your IP address, device information, browser type and settings and information about your activity in the Website (such as the date/time stamps associated with your usage, pages and files viewed, searches and other actions you take such as which features you use), device event information (such as system activity, error reports (sometimes called ‘crash dumps’) and hardware settings).

Device Data. I collect device data such as information about your computer, phone, tablet or other device you use to access the Website. Depending on the device used, this device data may include information such as your IP address (or proxy server), device and application identification numbers, location, browser type, hardware model Internet service provider and/or mobile carrier, operating system and system configuration information.

Location Data. I collect location data such as information about your device’s location, which can be either precise or imprecise. How much information I collect depends on the type and settings of the device you use to access the Website. For example, I may use GPS and other technologies to collect geolocation data that tells me your current location (based on your IP address). You can opt out of allowing me to collect this information either by refusing access to the information or by disabling your Location setting on your device. Note however, if you choose to opt out, you may not be able to use certain aspects of the Services.

2. HOW DO I USE YOUR INFORMATION?

In Short: I process your information for purposes based on legitimate business interests, the fulfillment of my contract with you, compliance with my legal obligations, and/or your consent.

I use personal information collected via my Website for a variety of business purposes described below. I process your personal information for these purposes in reliance on my legitimate business interests, in order to enter into or perform a contract with you, with your consent, and/or for compliance with my legal obligations. I indicate the specific processing grounds I rely on next to each purpose listed below.

For other business purposes. I may use your information for other business purposes, such as data analysis, identifying usage trends, determining the effectiveness of our promotional campaigns and to evaluate and improve my Website, products, marketing and your experience. I may use and store this information in aggregated and anonymized form so that it is not associated with individual end users and does not include personal information. I will not use identifiable personal information without your consent.

3. WILL YOUR INFORMATION BE SHARED WITH ANYONE?

In Short: I only share information with your consent, to comply with laws, to provide you with services, to protect your rights, or to fulfill business obligations.

4. DO WE USE COOKIES AND OTHER TRACKING TECHNOLOGIES?

In Short: I may use cookies and other tracking technologies to collect and store your information.

I may use cookies and similar tracking technologies (like web beacons and pixels) to access or store information. Specific information about how I use such technologies and how you can refuse certain cookies is set out in our Cookie Notice.

5. IS YOUR INFORMATION TRANSFERRED INTERNATIONALLY?

In Short: We may transfer, store, and process your information in countries other than your own.

My servers are located in the United States of America, unless otherwise requested by my clients. If you are accessing my Website from outside, please be aware that your information may be transferred to, stored, and processed by me in my facilities and by those third parties with whom I may share your personal information (see “WILL YOUR INFORMATION BE SHARED WITH ANYONE?” above), in and other countries.

If you are a resident in the European Economic Area, then these countries may not necessarily have data protection laws or other similar laws as comprehensive as those in your country. I will however take all necessary measures to protect your personal information in accordance with this privacy notice and applicable law.

6. HOW LONG DO WE KEEP YOUR INFORMATION?

In Short: I keep your information for as long as necessary to fulfill the purposes outlined in this privacy notice unless otherwise required by law.

I will only keep your personal information for as long as it is necessary for the purposes set out in this privacy notice, unless a longer retention period is required or permitted by law (such as tax, accounting or other legal requirements). No purpose in this notice will require me keeping your personal information for longer than 6 months.

When I have no ongoing legitimate business need to process your personal information, I will either delete or anonymize such information, or, if this is not possible (for example, because your personal information has been stored in backup archives), then I will securely store your personal information and isolate it from any further processing until deletion is possible.

7. HOW DO WE KEEP YOUR INFORMATION SAFE?

In Short: I aim to protect your personal information through a system of organizational and technical security measures.

I have implemented appropriate technical and organizational security measures designed to protect the security of any personal information I process. However, despite our safeguards and efforts to secure your information, no electronic transmission over the Internet or information storage technology can be guaranteed to be 100% secure, so I cannot promise or guarantee that hackers, cybercriminals, or other unauthorized third parties will not be able to defeat my security, and improperly collect, access, steal, or modify your information. Although I will do my best to protect your personal information, transmission of personal information to and from my Website is at your own risk. You should only access the Website within a secure environment.

8. DO WE COLLECT INFORMATION FROM MINORS?

In Short: I do not knowingly collect data from or market to children under 18 years of age.

I do not knowingly solicit data from or market to children under 18 years of age. By using the Website, you represent that you are at least 18 or that you are the parent or guardian of such a minor and consent to such minor dependent’s use of the Website. If I learn that personal information from users less than 18 years of age has been collected, I will deactivate the account and take reasonable measures to promptly delete such data from my records. If you become aware of any data I may have collected from children under age 18, please contact me at .

9. WHAT ARE YOUR PRIVACY RIGHTS?

In Short: You may review, change, or terminate your account at any time.

If you are a resident in the European Economic Area and you believe I am unlawfully processing your personal information, you also have the right to complain to your local data protection supervisory authority. You can find their contact details here: http://ec.europa.eu/justice/data-protection/bodies/authorities/index_en.htm.

If you are a resident in Switzerland, the contact details for the data protection authorities are available here: http://ec.europa.eu/justice/data-protection/bodies/authorities/index_en.htm.

Cookies and similar technologies: Most Web browsers are set to accept cookies by default. If you prefer, you can usually choose to set your browser to remove cookies and to reject cookies. If you choose to remove cookies or reject cookies, this could affect certain features or services of my Website.

10. CONTROLS FOR DO-NOT-TRACK FEATURES

Most web browsers and some mobile operating systems and mobile applications include a Do-Not-Track (“DNT”) feature or setting you can activate to signal your privacy preference not to have data about your online browsing activities monitored and collected. At this stage no uniform technology standard for recognizing and implementing DNT signals has been finalized. As such, I do not currently respond to DNT browser signals or any other mechanism that automatically communicates your choice not to be tracked online. If a standard for online tracking is adopted that I must follow in the future, I will inform you about that practice in a revised version of this privacy notice.

11. DO CALIFORNIA RESIDENTS HAVE SPECIFIC PRIVACY RIGHTS?

In Short: Yes, if you are a resident of California, you are granted specific rights regarding access to your personal information.

California Civil Code Section 1798.83, also known as the “Shine The Light” law, permits my users who are California residents to request and obtain from me, once a year and free of charge, information about categories of personal information (if any) I disclosed to third parties for direct marketing purposes and the names and addresses of all third parties with which I shared personal information in the immediately preceding calendar year. If you are a California resident and would like to make such a request, please submit your request in writing to me using the contact information provided below.

If you are under 18 years of age, reside in California, and have a registered account with the Website, you have the right to request removal of unwanted data that you publicly post on the Website. To request removal of such data, please contact us using the contact information provided below, and include the email address associated with your account and a statement that you reside in California. I will make sure the data is not publicly displayed on the Website, but please be aware that the data may not be completely or comprehensively removed from all my systems (e.g. backups, etc.).

12. DO I MAKE UPDATES TO THIS NOTICE?

In Short: Yes, I will update this notice as necessary to stay compliant with relevant laws.

I may update this privacy notice from time to time. The updated version will be indicated by an updated “Revised” date and the updated version will be effective as soon as it is accessible. If I make material changes to this privacy notice, I may notify you either by prominently posting a notice of such changes or by directly sending you a notification. We encourage you to review this privacy notice frequently to be informed of how I am protecting your information.

13. HOW CAN YOU CONTACT ME ABOUT THIS NOTICE?

If you have questions or comments about this notice, you may email me at or by post to:

Kevin C. Pirnie

22 Orlando St.
Feeding Hills, MA 01030
United States of America

14. HOW CAN YOU REVIEW, UPDATE, OR DELETE THE DATA I COLLECT FROM YOU?

Based on the applicable laws of your country, you may have the right to request access to the personal information I collect from you, change that information, or delete it in some circumstances. To request to review, update, or delete your personal information, you may email me at or by post to:

Kevin C. Pirnie

22 Orlando St.
Feeding Hills, MA 01030
United States of America

.Net DataReader Wrapper