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(
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(
string
parameterName, TParameterValue valueToCheckFor, TParameterValue parameterValue)
where
TParameterValue : IComparable
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(
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(
string
commandText,
bool
isProcedure,
string
connectionString)
where
TConnection : IDbConnection,
new
()
where
TCommand : IDbCommand,
new
()
where
TParameter : DbParameter,
new
()
{
ExecuteNonQuery(commandText, isProcedure, connectionString,
null
);
}
public
static
void
ExecuteNonQuery(
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(
string
commandText,
bool
isProcedure,
string
connectionString)
where
TConnection : IDbConnection,
new
()
where
TCommand : IDbCommand,
new
()
where
TParameter : DbParameter,
new
()
{
return
ExecuteScalar(commandText, isProcedure, connectionString,
null
);
}
public
static
TReturnValueType ExecuteScalar(
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(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(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(
string
commandText,
string
connectionString,
bool
isProcedure)
where
TConnection : DbConnection,
new
()
where
TCommand : DbCommand,
new
()
where
TParameter : DbParameter,
new
()
where
TDataAdapter : DbDataAdapter,
new
()
{
return
FillDataTable(commandText, isProcedure,
string
.Empty, connectionString,
null
);
}
public
static
DataTable FillDataTable(
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(commandText, isProcedure,
string
.Empty, connectionString, parameters);
}
public
static
DataTable FillDataTable(
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(commandText, isProcedure, tableName, connectionString,
null
);
}
public
static
DataTable FillDataTable(
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(dataTable, command);
return
dataTable;
}
}
}
public
static
void
FillDataSet(
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(commandText, isProcedure,
string
.Empty, dataSet, connectionString,
null
);
}
public
static
void
FillDataSet(
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(commandText, isProcedure,
string
.Empty, dataSet, connectionString, parameters);
}
public
static
void
FillDataSet(
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(
string
commandText,
bool
isProcedure,
string
connectionString)
where
TConnection : DbConnection,
new
()
where
TCommand : DbCommand,
new
()
where
TParameter : DbParameter,
new
()
where
TDataAdapter : DbDataAdapter,
new
()
{
return
FillDataSet(commandText, isProcedure,
string
.Empty, connectionString,
null
as
TParameter);
}
public
static
DataSet FillDataSet(
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(commandText, isProcedure,
string
.Empty, connectionString, parameters);
}
public
static
DataSet FillDataSet(
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(commandText, isProcedure, dataSetName, connectionString,
null
as
TParameter);
}
public
static
DataSet FillDataSet(
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(dataSet, command);
return
dataSet;
}
}
}
public
static
Dictionary
<
string
,
string
> GetDatabaseFields(
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(commandText, isStoredProcedure,
null
, connectionString, databaseFields);
}
public
static
Dictionary
<
string
,
string
> GetDatabaseFields(
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(commandText,
true
, connectionString, parameters).Rows[0];
}
else
{
dataRow = FillDataTable(commandText, connectionString,
true
).Rows[0];
}
}
else
{
if
(!IsArrayNullOrEmpty(parameters))
{
dataRow = FillDataTable(commandText,
false
, connectionString, parameters).Rows[0];
}
else
{
dataRow = FillDataTable(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;
}
}
}