Data Access Helper Class Implementation
Below is Data Access helper class implementation for .NET. Unlike Data Access blocks and classes provided by Microsoft - this class does not have numerous sql types bugs and most important - it is a bit over-optimized for performance. Therefore it:
- It works only with stored procedures.
- SqlCommand parameter access is provided by index only.
However it is a very performant, scalable and well-written example that demonstrates how to work with stored procedures in ADO.NET, open/close connections to database correctly, work with SQLCommand, command input/output parameters, etc.
public class DataAccessHelper : IDisposable
{
// connection to data source
private SqlConnection con;
private string strConnection =
ConfigurationSettings.AppSettings["ConnectionString"];
public int RunProcedure(string procedureName)
{
SqlCommand cmd = CreateCommand(procedureName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
public int RunProcedure(string procedureName,
SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procedureName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
public void RunProcedure(string procedureName,
out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procedureName, null);
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public void RunProcedure(string procedureName,
SqlParameter[] prams,
out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procedureName, prams);
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
private SqlCommand CreateCommand(string procedureName,
SqlParameter[] prams)
{
// make sure connection is open
this.Open();
SqlCommand cmd = new SqlCommand(procedureName, con);
cmd.CommandType = CommandType.StoredProcedure;
// add proc parameters
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
cmd.Parameters.Add(parameter);
}
}
// return param
cmd.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int,
4,
ParameterDirection.ReturnValue,
false,
0,
0,
String.Empty,
DataRowVersion.Default,
null));
return cmd;
}
public SqlParameter MakeInParameter(string parameterName,
SqlDbType dbType,
int size,
object hisValue)
{
return MakeParameter(parameterName,
dbType,
size,
ParameterDirection.Input,
hisValue);
}
public SqlParameter MakeOutParameter(string parameterName,
SqlDbType dbType,
int size)
{
return MakeParameter(parameterName,
dbType,
size,
ParameterDirection.Output,
null);
}
public SqlParameter MakeParameter(string parameterName,
SqlDbType dbType,
int size,
ParameterDirection direction,
object hisValue)
{
SqlParameter param = (size > 0) ?
new SqlParameter(parameterName, dbType, size) :
new SqlParameter(parameterName, dbType);
param.Direction = direction;
if (!((direction == ParameterDirection.Output) &&
(hisValue == null)))
{
param.Value = hisValue;
}
return param;
}
private void Open()
{
// open connection
if (con == null)
{
con = new SqlConnection(strConnection);
con.Open();
}
}
public void Close()
{
if (con != null)
{
con.Close();
}
}
public void Dispose()
{
// make sure connection is closed
if (con != null)
{
con.Dispose();
con = null;
}
}
}
Please, pay attention to how IDisposable interface is implemented in this class. All data access classes, that are supposed to work with database are supposed to call the functions of this class.
Wednesday, August 4, 2004 6:03 AM
