Strong Coders Generic Data Access Library
Until recently, I was using a modified version of the below code that was SQL Server-based for light weight data access. I've since introduced .NET generics to make it database independent. The below code is perfect for smaller projects where a component such as Enterprise Library or IBatis might be overkill, and it allows for one line data access. The below code can also be downloaded as a CS file here.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
namespace StrongCoders.Data
{
public class Library
{
public static void AddDataParametersToDbCommand(IDbCommand command, IDataParameter[] parameters)
{
foreach (IDataParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
public static TParameter CreateInputParameter<TParameterValue, TParameter>(string parameterName, TParameterValue parameterValue)
where TParameter : IDbDataParameter, new()
{
TParameter parameter = new TParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
parameter.Direction = ParameterDirection.Input;
return parameter;
}
public static TParameter CreateInputParameterWithCheck<TParameterValue, TParameter>(string parameterName, TParameterValue valueToCheckFor, TParameterValue parameterValue)
where TParameterValue : IComparable<TParameterValue>
where TParameter : IDbDataParameter, new()
{
TParameter parameter = new TParameter();
parameter.ParameterName = parameterName;
parameter.Direction = ParameterDirection.Input;
if (parameterValue == null || parameterValue.CompareTo(valueToCheckFor) == 0)
{
parameter.Value = DBNull.Value;
}
else
{
parameter.Value = parameterValue;
}
return parameter;
}
public static TParameter CreateOutputParameter<TParameter>(string parameterName, DbType dbType)
where TParameter : IDbDataParameter, new()
{
TParameter parameter = new TParameter();
parameter.ParameterName = parameterName;
parameter.DbType = dbType;
parameter.Direction = ParameterDirection.Output;
return parameter;
}
public static void ExecuteNonQuery<TConnection, TCommand, TParameter>(string commandText, bool isProcedure, string connectionString)
where TConnection : IDbConnection, new()
where TCommand : IDbCommand, new()
where TParameter : DbParameter, new()
{
ExecuteNonQuery<TConnection, TCommand, TParameter>(commandText, isProcedure, connectionString, null);
}
public static void ExecuteNonQuery<TConnection, TCommand, TParameter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
where TConnection : IDbConnection, new()
where TCommand : IDbCommand, new()
where TParameter : DbParameter, new()
{
using (TConnection connection = new TConnection())
{
connection.ConnectionString = connectionString;
using (TCommand command = new TCommand())
{
command.CommandText = commandText;
command.Connection = connection;
if (isProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (!IsArrayNullOrEmpty(parameters))
{
AddDataParametersToDbCommand(command, parameters);
}
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
}
}
public static TReturnValueType ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(string commandText, bool isProcedure, string connectionString)
where TConnection : IDbConnection, new()
where TCommand : IDbCommand, new()
where TParameter : DbParameter, new()
{
return ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(commandText, isProcedure, connectionString, null);
}
public static TReturnValueType ExecuteScalar<TConnection, TCommand, TParameter, TReturnValueType>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
where TConnection : IDbConnection, new()
where TCommand : IDbCommand, new()
where TParameter : DbParameter, new()
{
using (TConnection connection = new TConnection())
{
connection.ConnectionString = connectionString;
using (TCommand command = new TCommand())
{
command.CommandText = commandText;
command.Connection = connection;
TReturnValueType returnValue = default(TReturnValueType);
if (isProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (!IsArrayNullOrEmpty(parameters))
{
AddDataParametersToDbCommand(command, parameters);
}
command.Connection.Open();
returnValue = (TReturnValueType)command.ExecuteScalar();
command.Connection.Close();
return returnValue;
}
}
}
public static void FillDataAdapter<TCommand, TDataAdapter>(DataTable dataSource, TCommand selectCommand)
where TCommand : DbCommand
where TDataAdapter : DbDataAdapter, new()
{
TDataAdapter dataAdapter = new TDataAdapter();
dataAdapter.SelectCommand = selectCommand;
dataAdapter.Fill(dataSource);
}
public static void FillDataAdapter<TCommand, TDataAdapter>(DataSet dataSource, TCommand selectCommand)
where TCommand : DbCommand
where TDataAdapter : DbDataAdapter, new()
{
TDataAdapter dataAdapter = new TDataAdapter();
dataAdapter.SelectCommand = selectCommand;
dataAdapter.Fill(dataSource);
}
public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, string connectionString, bool isProcedure)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, null);
}
public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, parameters);
}
public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string tableName, string connectionString)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, tableName, connectionString, null);
}
public static DataTable FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string tableName, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
using (TConnection connection = new TConnection())
{
connection.ConnectionString = connectionString;
using (TCommand command = new TCommand())
{
command.CommandText = commandText;
command.Connection = connection;
DataTable dataTable;
if (!string.IsNullOrEmpty(tableName))
{
dataTable = new DataTable(tableName);
}
else
{
dataTable = new DataTable();
}
if (isProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (!IsArrayNullOrEmpty(parameters))
{
AddDataParametersToDbCommand(command, parameters);
}
FillDataAdapter<TCommand, TDataAdapter>(dataTable, command);
return dataTable;
}
}
}
public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, DataSet dataSet)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, dataSet, connectionString, null);
}
public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, DataSet dataSet, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, dataSet, connectionString, parameters);
}
public static void FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string sourceTable, DataSet dataSet, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
using (TConnection connection = new TConnection())
{
connection.ConnectionString = connectionString;
using (TCommand command = new TCommand())
{
command.CommandText = commandText;
command.Connection = connection;
if (isProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (!IsArrayNullOrEmpty(parameters))
{
AddDataParametersToDbCommand(command, parameters);
}
TDataAdapter adapter = new TDataAdapter();
adapter.SelectCommand = command;
if (!string.IsNullOrEmpty(sourceTable))
{
adapter.Fill(dataSet, sourceTable);
}
else
{
adapter.Fill(dataSet);
}
}
}
}
public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, null as TParameter);
}
public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, string.Empty, connectionString, parameters);
}
public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string dataSetName, string connectionString)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isProcedure, dataSetName, connectionString, null as TParameter);
}
public static DataSet FillDataSet<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isProcedure, string dataSetName, string connectionString, params TParameter[] parameters)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
using (TConnection connection = new TConnection())
{
connection.ConnectionString = connectionString;
using (TCommand command = new TCommand())
{
command.CommandText = commandText;
command.Connection = connection;
DataSet dataSet;
if (!string.IsNullOrEmpty(dataSetName))
{
dataSet = new DataSet(dataSetName);
}
else
{
dataSet = new DataSet();
}
if (isProcedure)
{
command.CommandType = CommandType.StoredProcedure;
}
if (!IsArrayNullOrEmpty(parameters))
{
AddDataParametersToDbCommand(command, parameters);
}
FillDataAdapter<TCommand, TDataAdapter>(dataSet, command);
return dataSet;
}
}
}
public static Dictionary<string, string> GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isStoredProcedure, string connectionString, params string[] databaseFields)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
return GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(commandText, isStoredProcedure, null, connectionString, databaseFields);
}
public static Dictionary<string, string> GetDatabaseFields<TConnection, TCommand, TParameter, TDataAdapter>(string commandText, bool isStoredProcedure, TParameter[] parameters, string connectionString, params string[] databaseFields)
where TConnection : DbConnection, new()
where TCommand : DbCommand, new()
where TParameter : DbParameter, new()
where TDataAdapter : DbDataAdapter, new()
{
if (!IsArrayNullOrEmpty(databaseFields))
{
DataRow dataRow;
if (isStoredProcedure)
{
if (!IsArrayNullOrEmpty(parameters))
{
dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, true, connectionString, parameters).Rows[0];
}
else
{
dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, connectionString, true).Rows[0];
}
}
else
{
if (!IsArrayNullOrEmpty(parameters))
{
dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, false, connectionString, parameters).Rows[0];
}
else
{
dataRow = FillDataTable<TConnection, TCommand, TParameter, TDataAdapter>(commandText, false, connectionString).Rows[0];
}
}
Dictionary<string, string> fieldsAndValues = new Dictionary<string, string>();
foreach (string databaseField in databaseFields)
{
fieldsAndValues.Add(databaseField, Convert.ToString(dataRow[databaseField]));
}
}
return null;
}
public static DataSet PopulateDataSet(params DataTable[] dataTables)
{
DataSet dataSet = new DataSet();
if (!IsArrayNullOrEmpty(dataTables))
{
foreach (DataTable dataTable in dataTables)
{
dataSet.Tables.Add(dataTable);
}
}
return dataSet;
}
private static bool IsArrayNullOrEmpty(Array array)
{
return array == null || array.Length == 0;
}
}
}
Similar Posts
- Generic ExecuteScalar method
- Nullable Types
- C# GridView Sorting/Paging w/o a DataSourceControl DataSource
