|
Data Access With ADO.NET
By Steven Smith
Writing a data access layer (DAL) for .NET using C# can be a tedious process. Following the best
practices laid out in the IBuySpy.com sample site means creating a lot
of method calls to wrap stored procedure calls. This is not all that different from how one would accomplish
the same task using Visual Basic 6 and COM, but the tediousness is somewhat increased by the apparent
increase in the number of lines of code required to perform the task. Scott Guthrie has written a wizard
tool that helps a bit with this process (not sure if it has been updated from Beta 1 yet), which you can
try here. However, even then you end up with a LOT of code
for this relatively simple task. For example, for this call to return a collection of articles which only
uses one stored procedure parameter, it requires 3 lines of code for this one parameter. That's 1/3 of the
whole function, and this only gets worse for functions that require multiple parameters (as most do).
listArticlesByPopularity method:
public SqlDataReader listArticlesByPopularity(int howmanyrows)
{
// Create Instance of Connection and Command Object
SqlConnection conn = new SqlConnection(DAL.ConnectionString);
SqlCommand cmd = new SqlCommand("sp_ListArticles", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pHowManyRows = new SqlParameter("@howmany", SqlDbType.Int,4);
pHowManyRows.Value = howmanyrows;
cmd.Parameters.Add(pHowManyRows);
// Execute the command
conn.Open();
SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Return the datareader result
return result;
}
|
With ADO, creating parameters was a one line process which, while still tedious, was much easier to maintain
and read. Spending three lines per parameter may not seem like a big deal, but it gets old really fast,
let me assure you (especially when you have to convert over a hundred sp's into DAL methods). Unfortunately,
ADO.NET does not provide an overloaded paramter constructor that will create a full-fledged sqlparameter for
you. So I created one and stuck it in my DAL base class, from which all of my DAL classes inherit. The
function looks like this, and can easily be overloaded to eliminate the need to send it all of the parameters
listed here.
pMaker method:
protected static SqlParameter pMaker(String parameterName, SqlDbType DbType, Int32 Size,
ParameterDirection Direction, Object Value)
{
SqlParameter param;
if(Size > 0)
{
param = new SqlParameter(parameterName, DbType, Size);
}
else
{
param = new SqlParameter(parameterName, DbType);
}
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
|
To perform a task similar to the first one listed, and calling a stored procedure that takes a single
parameter, our code would now look something like this:
listColumnists method:
public SqlDataReader listColumnists(int howmanyrows)
{
// Create Instance of Connection and Command Object
SqlConnection conn = new SqlConnection(DAL.ConnectionString);
SqlCommand cmd = new SqlCommand("sp_ListColumnists", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(pMaker("@how_many", SqlDbType.Int, 4, ParameterDirection.Input, howmanyrows));
// Execute the command
conn.Open();
SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Return the datareader result
return result;
}
|
Now, this may not seem like it's much help, but consider the difference it makes when you call a function
like this one:
insertColumnist method:
public int insertColumnist(String url, String featured_link, String title, String description, DateTime join_date,
bool isIndexed, bool isTaxIdOnFile, String notes, String bio, String username,
int user_id)
{
SqlConnection conn = new SqlConnection(DAL.ConnectionString);
SqlCommand cmd = new SqlCommand("sp_InsertColumnist",conn);
cmd.CommandType = CommandType.StoredProcedure;
// Set bit variables to numeric equivalent
int index_column_flag = 0;
int tax_id_on_file = 0;
if (isIndexed) index_column_flag = 1;
if (isTaxIdOnFile) tax_id_on_file = 1;
// Set up Parameters
cmd.Parameters.Add(pMaker("@user_id", SqlDbType.Int, 4, ParameterDirection.Input, user_id));
cmd.Parameters.Add(pMaker("@url", SqlDbType.NVarChar, 255, ParameterDirection.Input, url));
cmd.Parameters.Add(pMaker("@featured_link", SqlDbType.NVarChar, 50, ParameterDirection.Input, featured_link));
cmd.Parameters.Add(pMaker("@title", SqlDbType.NVarChar, 100, ParameterDirection.Input, title));
cmd.Parameters.Add(pMaker("@description", SqlDbType.NVarChar, 8000, ParameterDirection.Input, description));
cmd.Parameters.Add(pMaker("@join_date", SqlDbType.DateTime, 0, ParameterDirection.Input, join_date));
cmd.Parameters.Add(pMaker("@index_column_flag", SqlDbType.Bit, 0, ParameterDirection.Input, index_column_flag));
cmd.Parameters.Add(pMaker("@tax_id_on_file", SqlDbType.Bit, 0, ParameterDirection.Input, tax_id_on_file));
cmd.Parameters.Add(pMaker("@notes", SqlDbType.NVarChar, 8000, ParameterDirection.Input, notes));
cmd.Parameters.Add(pMaker("@bio", SqlDbType.NVarChar, 8000, ParameterDirection.Input, bio));
cmd.Parameters.Add(pMaker("@username", SqlDbType.NVarChar, 50, ParameterDirection.Input, username));
cmd.Parameters.Add(pMaker("@columnist_id", SqlDbType.Int, 4, ParameterDirection.Input, null));
try{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception e){
throw e;
}
finally{
if (conn.State == ConnectionState.Open)
conn.Close();
}
return Int32.Parse(cmd.Parameters["@columnist_id"].Value.ToString());
}
|
Apart from this small helper function, the only other thing I put in my DAL layer today is the connection string,
which I make a static variable so that I can reference it without instantiating my DAL class. In the future,
I may make additions to this class that I will want to use across all of my different DAL classes, so I have
all of my DAL classes inherit from this base class. My complete DAL class is listed below:
DAL.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;
using System.Runtime.CompilerServices;
// Assembly Info
[assembly: AssemblyTitle("ASPAlliance.DAL")]
[assembly: AssemblyDescription("Data Access Components for ASP Alliance.")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("ASPAlliance")]
[assembly: AssemblyProduct("ASPAlliance Framework")]
[assembly: AssemblyCopyright("2001")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("0.1.2.1")]
[assembly: AssemblyInformationalVersion("0.1.2.1")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("")]
namespace ASPAlliance.DAL {
public class DAL {
static String m_ConnectionString;
public static String ConnectionString{
get{
if (m_ConnectionString == null){
try{
m_ConnectionString = (String) ConfigurationSettings.AppSettings["DSN"];
}
catch{
throw new Exception("ASPAlliance DSN value not set in web.config.");
}
if (m_ConnectionString == null){
throw new Exception("ASPAlliance DSN value not set in web.config.");
}
}
return m_ConnectionString;
}
}
protected static SqlParameter pMaker(String parameterName, SqlDbType DbType, Int32 Size,
ParameterDirection Direction, Object Value)
{
SqlParameter param;
if(Size > 0)
{
param = new SqlParameter(parameterName, DbType, Size);
}
else
{
param = new SqlParameter(parameterName, DbType);
}
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
}
}
|
If you want to learn more about how to build a Data Access Layer in .NET, I strongly recommend you check out
IBuySpy.com before anything else. As you will note, the classes and methods
presented here adhere closely to the IBS recommended design.
Update: 8 June 2002
One of my readers, Eric Newton, wrote me today to let me know that the Add method of
the command object returns a
reference to the newly added procedure, something I was not aware of. What does this mean? Well, it
means that you can add parameters and specify their values in a single line without resorting to writing
your own helper function (like pMaker) to do so. Here's an example he sent me:
oCmd.Parameters.Add("@site_id", SqlDbType.Int).Value = siteId;
So, although I haven't actually had a chance to test this method out, and while it seems to me that it
doesn't cover the case of output parameters, it is definitely something worth remembering. Thanks, Eric!
|
|
