A Database Access Class with Overloaded SELECT Statements

A useful database access class with overloaded SELECT and other 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;
}