A Database Access Class with Overloaded SELECT Statements


This is a SQL Server Data Access example with many overloaded Select statements  and several different ways to execute stored procedures.

Here is some code snippet:

public AuthorData(string connection)
{
this
.connection = connection;

........

public SQLDataReader Select(string commandName)
{
SQLDataReader dr =
null
;
try

{
SQLConnection cnn =
new SQLConnection(this
.connection);
cnn.Open();
SQLCommand cmd =
new
SQLCommand(commandName,cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Execute(
out
dr);
cmd.ActiveConnection =
null
;
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, commandName);
}
return
(dr);
}
.............

public void Select(out SQLDataReader dr, string commandName)
{
dr =
null
;
try

{
SQLConnection cnn =
new SQLConnection(this
.connection);
cnn.Open();
SQLCommand cmd =
new
SQLCommand(commandName,cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Execute(
out
dr);
cmd.ActiveConnection =
null
;
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, commandName);
}

.............

public void Insert(string commandName, params object[] args)
{
try

{
SQLConnection cnn =
new SQLConnection(this
.connection);
SQLParameter parm =
new
SQLParameter();
cnn.Open();
SQLCommand cmd =
new
SQLCommand(commandName,cnn);
cmd.CommandType = CommandType.StoredProcedure;
parm = cmd.Parameters.Add(
new
SQLParameter("@au_id", SQLDataType.VarChar, 11));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_id"].Value = args[0];
parm = cmd.Parameters.Add(
new
SQLParameter("@au_lname", SQLDataType.VarChar, 40));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_lname"].Value = args[1];
parm = cmd.Parameters.Add(
new
SQLParameter("@au_fname", SQLDataType.VarChar, 20));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_fname"].Value = args[2];
parm = cmd.Parameters.Add(
new
SQLParameter("@Phone", SQLDataType.Char, 12));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@Phone"].Value = args[3];
parm = cmd.Parameters.Add(
new
SQLParameter("@Address", SQLDataType.VarChar, 40));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@Address"].Value = args[4];
parm = cmd.Parameters.Add(
new
SQLParameter("@city", SQLDataType.VarChar, 20));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@city"].Value = args[5];
parm = cmd.Parameters.Add(
new
SQLParameter("@state", SQLDataType.Char, 2));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@state"].Value = args[6];
parm = cmd.Parameters.Add(
new
SQLParameter("@zip", SQLDataType.VarChar, 5));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@zip"].Value = args[7];
parm = cmd.Parameters.Add(
new
SQLParameter("@contract", SQLDataType.Bit ));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@contract"].Value = args[8];
cmd.ExecuteNonQuery();
cmd.ActiveConnection =
null
;
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, commandName);
}

.....................

public void Update(string commandName, params object[] args)
{
try

{
SQLConnection cnn =
new SQLConnection(this
.connection);
SQLParameter parm =
new
SQLParameter();
cnn.Open();
SQLCommand cmd =
new
SQLCommand(commandName,cnn);
cmd.CommandType = CommandType.StoredProcedure;
parm = cmd.Parameters.Add(
new
SQLParameter("@au_id", SQLDataType.VarChar, 11));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_id"].Value = args[0];
parm = cmd.Parameters.Add(
new
SQLParameter("@au_lname", SQLDataType.VarChar, 40));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_lname"].Value = args[1];
parm = cmd.Parameters.Add(
new
SQLParameter("@au_fname", SQLDataType.VarChar, 20));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_fname"].Value = args[2];
parm = cmd.Parameters.Add(
new
SQLParameter("@Phone", SQLDataType.Char, 12));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@Phone"].Value = args[3];
parm = cmd.Parameters.Add(
new
SQLParameter("@Address", SQLDataType.VarChar, 40));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@Address"].Value = args[4];
parm = cmd.Parameters.Add(
new
SQLParameter("@city", SQLDataType.VarChar, 20));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@city"].Value = args[5];
parm = cmd.Parameters.Add(
new
SQLParameter("@state", SQLDataType.Char, 2));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@state"].Value = args[6];
parm = cmd.Parameters.Add(
new
SQLParameter("@zip", SQLDataType.VarChar, 5));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@zip"].Value = args[7];
parm = cmd.Parameters.Add(
new
SQLParameter("@contract", SQLDataType.Bit ));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@contract"].Value = args[8];
cmd.ExecuteNonQuery();
cmd.ActiveConnection =
null
;
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, commandName);
}
}
......................... 

public void Delete(string commandName, string recordID)
{
try

{
SQLConnection cnn =
new SQLConnection(this
.connection);
SQLParameter parm =
new
SQLParameter();
cnn.Open();
SQLCommand cmd =
new
SQLCommand(commandName,cnn);
cmd.CommandType = CommandType.StoredProcedure;
parm = cmd.Parameters.Add(
new
SQLParameter("@au_id", SQLDataType.VarChar, 11));
parm.Direction = ParameterDirection.Input;
cmd.Parameters["@au_id"].Value = recordID;
cmd.ExecuteNonQuery();
cmd.ActiveConnection =
null
;
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, commandName);
}
}
..................................... 

public void ExecuteProc(string commandName, params object[] args)
{
try

{
ADOConnection cnn =
new ADOConnection(this
.connection);
cnn.Open();
ADOCommand cmd =
new
ADOCommand();
cmd.ActiveConnection = cnn;
cmd.CommandText = commandName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ResetParameters();
int
i = 0;
foreach( ADOParameter prm in
cmd.Parameters)
{
cmd.Parameters[i].Value = args[i];
i++;
}
cmd.ExecuteNonQuery();
}
catch
(Exception e)
{
ErrorLog errLog =
new
ErrorLog();
errLog.LogError(e.Message, "Method: ExecuteProc, Stored Proc: " + commandName);
}
}
~AuthorData() {}
//Destructor not really needed
string
connection;
}