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 ( 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);
}
}
///
/// Return a SqlDataReader based on the properties passed to this class
///
///
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;
}
}
///
/// Execute a non-return query, and return the success
///
///
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;
}
}
///
/// Execute a query with a return value. Used in Selecting the ID of the last inserted record.
///
///
///
///
internal T ExecuteWithReturn(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);
}
}
///
/// Prepare our parameters, adding them and forcing a valid data length
///
///
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 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);
}
}
///
/// Return a SqlDataReader based on the properties passed to this class
///
///
internal async Task 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;
}
}
///
/// Execute a non-return query, and return the success
///
///
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;
}
}
///
/// Prepare our parameters, adding them and forcing a valid data length
///
///
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 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(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);
}
}
// 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
Read the full article
