I’ve been doing a lot of CRUD-type work recently against a reporting database. The application does not utilize an ORM because of the complexity of selection SQL, so I’ve been designing a custom ORM framework.
One of my top priorities is to route all database access through a single class, so I developed this generic database reader. The code is shown below.
The generic reader class is DataReaderHelper. It has a GetData generic method that accepts a generic delegate and returns read data using a referenced generic parameter. This combination of generics and delegates provides an exceptionally flexible class that I can use for any database reads, ensuring that my connections are centrally managed.
The code sample below also provides sample usage in the Mapper class.
// generic class for reading database data
public class DataReaderHelper
{
// generic delegate passed into the GetData method to manipulate the IDataReader object
public delegate void ReadDatabaseValue<T>(IDataReader reader, ref T businessObject);
// delegate is passed into the GetData method to create a new connection
public delegate IDbConnection CreateConnection();
public static void GetData<T>(CreateConnection createConnection, string sql,
ReadDatabaseValue<T> readDelegate, ref T businessObject)
{
IDbConnection dbConnection = null;
IDbCommand command = null;
IDataReader reader = null;
try
{
dbConnection = createConnection();
if (dbConnection.State != ConnectionState.Open) { dbConnection.Open(); }
command = dbConnection.CreateCommand();
command.CommandText = sql;
reader = command.ExecuteReader();
while (reader.Read())
{
readDelegate(reader, ref businessObject);
}
}
catch { throw; }
finally
{
if (dbConnection != null) dbConnection.Dispose();
if (command != null) command.Dispose();
if (reader != null) reader.Dispose();
}
}
}
// provides the database connection delegate
internal class ConnectionFactory
{
internal static SqlConnection GetConnection()
{
try
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
}
catch { throw; }
}
}
// provides delegates used to read data
internal class DataReaderFactory
{
internal static void ReadFirstColumnIntoString(IDataReader reader, ref string value)
{
value = reader.GetString(0);
}
internal static void ReadFirstColumnIntoStringCollection(IDataReader reader,
ref StringCollection value)
{
value.Add(reader.GetString(0));
}
}
// example DataReaderHelper usage
internal class Mapper
{
internal static string DemoGetString()
{
string demo = null;
DataReaderHelper.GetData<string>(ConnectionFactory.GetConnection, "my sql",
DataReaderFactory.ReadFirstColumnAsValue, ref demo);
return demo;
}
internal static StringCollection DemoGetStringCollection()
{
StringCollection demo = null;
DataReaderHelper.GetData<StringCollection>(ConnectionFactory.GetConnection, "my sql",
DataReaderFactory.ReadFirstColumnAsInt32IdList, ref demo);
return demo;
}
}
