As i mentioned in my previous posting, i am trying to seperate the modules in my projects so i can test every single code. I am new to TDD so i meet a lot of challenges during the adoptation. One of them is the database access, how will i unit test a class or a function where these is a database dependency. i decided to create a generic database access class, which implements an interface that could be mock out.
In the database side, i always use stored procedures to do actions, and my stored procedures either returns an integer value (if it doesnt return anything like delete from..., consider it returns 1), or a DataTable such as select * from table. So i can easily write an interface such as:
public interface IExecuteProcedure
{
DataTable GetDataTable(string StoredProcedureName, List<SqlParameter> Parameters);
int ExecuteStoreProcedure(string StoredProcedureName, List<SqlParameter> Parameters);
}
So GetDataTable runs a stored procedure with a list of parameters given , and returns a DataTable, ExecuteStoreProcedure returns int. A database access class could follow a pattern like below to implement this interface:
public class DatabaseAccess : IExecuteProcedure
{
private readonly string ConnectionString_;
public DatabaseAccess(string ConnectionString)
{
this.ConnectionString_ = ConnectionString;
}
public DataTable GetDataTable(string StoredProcedureName, List<SqlParameter> Parameters)
{
SqlConnection conn = new SqlConnection(ConnectionString_);
SqlCommand cmd = new SqlCommand(StoredProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in Parameters)
{
cmd.Parameters.Add(param);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
}
catch (Exception)
{
dt.Clear();
}
return dt;
}
#region IExecuteProcedure Members
public int ExecuteStoreProcedure(string StoredProcedureName, List<SqlParameter> Parameters)
{
SqlConnection conn = new SqlConnection(ConnectionString_);
SqlCommand cmd = new SqlCommand(StoredProcedureName, conn);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in Parameters)
{
cmd.Parameters.Add(param);
}
int iReturn = 0;
try
{
conn.Open();
cmd.ExecuteScalar();
for (int i = 0; i < Parameters.Count; i++)
{
if (Parameters[i].Direction == ParameterDirection.Output)
{
Parameters[i].Value = cmd.Parameters[Parameters[i].ParameterName].Value;
iReturn = Int32.Parse(Parameters[i].Value.ToString());
}
}
}
catch(Exception ex)
{
iReturn = Int32.MinValue;
}
finally
{
conn.Dispose();
}
return iReturn;
}
#endregion
}
So the database access class implements the interface, and returns the appropriate values. Now in my next posting we will see how we can use this class to access the database, but also we will see how we can inject some code, and change the behaviour without changing the structure.