I am going through one of my class library projects, and while nothing is wrong with it, I am finding myself being a bit anal, and wanting to organize things a bit differently.
The project in particular that I am looking at reorganizing, is a class library to hook into a multitude of databases, and do work on them. Now because of the differences in datatypes and methods for doing said work, each database has its own sub-folder in the main project, labelled after the database it will do the work for.
Would it be best to keep it like it is, or combine the similar methods into 1 class and simply through a switch statement in, giving the option to "select" the database type the library should be working with?
Right now it's setup as you simply use or import My.ClassLibrary.MSSQL and the like, however, all the methods in each type are so flippin similar, I am looking at it and saying to myself... why?
Each Database type has a folder in this project containing the database specific representation of the following code:
Access Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace kp.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 DataTypes[] _DTs;
internal DataTypes[] 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; } }
// 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(string _connStr)
{
Invoke(_connStr);
}
// Official Constructor. We can thread these 2 becuase they are not being used yet, and it makes it slightly more efficient
internal void Invoke(string _connStr)
{
try {
Parallel.Invoke(() => {
_Conn = new SqlConnection(_connStr);
}, () =>
{
_Cmd = new SqlCommand();
});
}
catch (SqlException dEx)
{
// Catch an exception if any, an write it out to our logging mechanism, in addition to adding it our returnable message property
_Msg = "Access.Invoke Exception: " + dEx.Message;
ErrorReporting.WriteEm.WriteItem(dEx, "kp.Class.Library.Data.Access.Invoke", _Msg);
}
catch (Exception ex)
{
_Msg = "Access.Invoke Exception: " + ex.Message;
ErrorReporting.WriteEm.WriteItem(ex, "kp.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, "kp.Class.Library.Data.Access.GetResults", _Msg);
return null;
}
catch (Exception ex) {
_Msg += "Acccess.GetResults Exception: " + ex.Message;
ErrorReporting.WriteEm.WriteItem(ex, "kp.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, "kp.Class.Library.Data.Access.Execute", _Msg);
return false;
}
catch (Exception ex) {
_Msg += "Access.Execute Exception: " + ex.Message;
ErrorReporting.WriteEm.WriteItem(ex, "kp.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, "kp.Class.Library.Data.Access.ExecuteWithReturn", _Msg);
return default(T);
} catch (Exception ex) {
_Msg += "Access.ExecuteWithReturn Exception: " + ex.Message;
ErrorReporting.WriteEm.WriteItem(ex, "kp.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], (SqlDbType)_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, "kp.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();
_Cmd.Dispose();
if (_Conn.State == ConnectionState.Open)
{
SqlConnection.ClearAllPools();
_Conn.Close();
_Conn.Dispose();
}
_Msg = null;
}
catch(Exception ex) {
ErrorReporting.WriteEm.WriteItem(ex, "kp.Class.Library.Data.Access.Dispose", "");
}
}
disposedValue = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
}
}
Wrapper Class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
namespace kp.Class.Library.Data
{
/// <summary>
/// Wrapper class for our data access
/// </summary>
public class Wrapper {
/// <summary>
/// Setup our return message if any
/// </summary>
public static string Message { 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, "kp.Class.Library.Data.Wrapper.Map", _Msg);
// make sure this method returns a default List
return default(List<T>);
}
}
/// <summary>
/// Get the results of a stronly-typed IList Object
/// </summary>
/// <typeparam name="T">Strongly-Typed class of objects that should be returned</typeparam>
/// <param name="_connStr">The connection string to the database</param>
/// <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 _connStr, string _Qry, CommandType _QryType,
string[] _ParamNames = null,
object[] _ParamVals = null,
DataTypes[] _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(_connStr))
{
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);
}
r.Close();
}
// 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 (DbException dEx)
{
// 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: " + dEx.Message + db.Message;
ErrorReporting.WriteEm.WriteItem(dEx, "kp.Class.Library.Data.Wrapper.GetResults", _Msg);
// make sure this method returns a default List
return default(IList<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.GetResults Exception: " + ex.Message + db.Message;
ErrorReporting.WriteEm.WriteItem(ex, "kp.Class.Library.Data.Wrapper.GetResults", _Msg);
// make sure this method returns a default List
return default(IList<T>);
}
}
}
}
/// <summary>
/// Execute a query against the database. Usually used for IUD Operations
/// </summary>
/// <param name="_connStr">The connection string to the database</param>
/// <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>
/// <returns>Boolean of success</returns>
public static bool Execute(string _connStr, string _Qry, CommandType _QryType,
string[] _ParamNames = null,
object[] _ParamVals = null,
DataTypes[] _ParamDTs = null)
{
// setup a reference for our success return
bool _T;
// Fire up our data access object
using (Access db = new Access(_connStr))
{
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 (DbException dEx)
{
// 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: " + dEx.Message + db.Message;
ErrorReporting.WriteEm.WriteItem(dEx, "kp.Class.Library.Data.Wrapper.Execute", _Msg);
// make sure this method returns a default List
return false;
}
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, "kp.Class.Library.Data.Wrapper.Execute", _Msg);
// make sure this method returns a default value of false
return false;
}
}
}
/// <summary>
/// Executes a query against the database, and returns a value
/// </summary>
/// <typeparam name="T">Strongly Typed Object for return</typeparam>
/// <param name="_connStr">The connection string to the database</param>
/// <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="_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 _connStr, string _Qry, CommandType _QryType,
string[] _ParamNames = null,
object[] _ParamVals = null,
DataTypes[] _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(_connStr))
{
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 (DbException dEx)
{
// 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: " + dEx.Message + db.Message;
ErrorReporting.WriteEm.WriteItem(dEx, "kp.Class.Library.Data.Wrapper.ExecuteWithReturn", _Msg);
// make sure this method returns a default List
return default(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, "kp.Class.Library.Data.Wrapper.ExecuteWithReturn", _Msg);
// return the default value for the strong typed object
return default(T);
}
}
}
}
}
Data Typing Class
namespace kp.Class.Library.Data.SqlServer
{
/// <summary>
/// Sql Data Type Enumeration
/// </summary>
public enum DataTypes : int
{
/// <summary>
/// BigInt
/// </summary>
BigInt = 0,
/// <summary>
/// Binary
/// </summary>
Binary = 1,
/// <summary>
/// Bit
/// </summary>
Bit = 2,
/// <summary>
/// Char
/// </summary>
Char = 3,
/// <summary>
/// Date
/// </summary>
Date = 31,
/// <summary>
/// DateTime
/// </summary>
DateTime = 4,
/// <summary>
/// DateTime2
/// </summary>
DateTime2 = 33,
/// <summary>
/// DateTimeOffset
/// </summary>
DateTimeOffset = 34,
/// <summary>
/// Decimal
/// </summary>
Decimal = 5,
/// <summary>
/// Float
/// </summary>
Float = 6,
/// <summary>
/// Image
/// </summary>
Image = 7,
/// <summary>
/// Int
/// </summary>
Int = 8,
/// <summary>
/// Money
/// </summary>
Money = 9,
/// <summary>
/// NChar
/// </summary>
NChar = 10,
/// <summary>
/// NText
/// </summary>
NText = 11,
/// <summary>
/// NVarChar
/// </summary>
NVarChar = 12,
/// <summary>
/// Real
/// </summary>
Real = 13,
/// <summary>
/// SmallDateTime
/// </summary>
SmallDateTime = 15,
/// <summary>
/// SmallInt
/// </summary>
SmallInt = 16,
/// <summary>
/// SmallMoney
/// </summary>
SmallMoney = 17,
/// <summary>
/// Structured
/// </summary>
Structured = 30,
/// <summary>
/// Text
/// </summary>
Text = 18,
/// <summary>
/// Time
/// </summary>
Time = 32,
/// <summary>
/// Timestamp
/// </summary>
Timestamp = 19,
/// <summary>
/// TinyInt
/// </summary>
TinyInt = 20,
/// <summary>
/// Udt
/// </summary>
Udt = 29,
/// <summary>
/// UniqueIdentifier
/// </summary>
UniqueIdentifier = 14,
/// <summary>
/// VarBinary
/// </summary>
VarBinary = 21,
/// <summary>
/// VarChar
/// </summary>
VarChar = 22,
/// <summary>
/// Variant
/// </summary>
Variant = 23,
/// <summary>
/// Xml
/// </summary>
Xml = 25
}
}
