The example code below will describe how to create an abstract database call using MS SQL Server. This technique will demonstrate how to keep the data layer far far away from the business code. Enjoy =)
First thing we need to do is add a connection string to the database we want access to using the web.config file. In your web.config file create a "connectionstring" element like this:
<configuration>
<configSections>
........
<connectionStrings>
<add name="SiteConnectionString" connectionString="Server=..;Database=..;Uid=..;Password=..;"/>
</connectionStrings>
.......
Now that we have a connection string ready to go we can build the abstract class to take advantage of it like this:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public abstract class DatabaseCall : IDisposable
{
//Create a connection
private SqlConnection sqlConnection = new SqlConnection();
//Create a command
private SqlCommand sqlCommand = new SqlCommand();
//Create a transaction
private SqlTransaction sqlTransaction;
.......
We need this class to disposed of itself correctly so the IDisposable interface has been added to handle that. So let's add some clean up code to the class:
public void Dispose()
{
if (sqlConnection != null)
{
sqlConnection.Close();
sqlConnection.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
if (sqlTransaction != null)
{
sqlTransaction.Dispose();
}
}
Now that we can clean up our mess after we instantiate the class, let's create our constructor for the class:
public DatabaseCall(bool hasTransaction, string connectionString)
{
this.SetConnection = connectionString;
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
sqlCommand.CommandTimeout = 30;
sqlCommand.CommandType = CommandType.StoredProcedure;
if (hasTransaction)
{
sqlTransaction = sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
}
}
The constructor is simple enough. You tell it if the database calls from the derived class will be using transactions, and which connection string to use (from the web.config).
Now that we have all that taken care of we can create a simple database call method like so:
public int ExecuteNonQuery(string procedureName, Hashtable hashtable)
{
try
{
//Clear parameters and set stored procedure to execute
sqlCommand.Parameters.Clear();
sqlCommand.CommandText = procedureName;
AssignParameters(ref sqlCommand, hashtable);
return sqlCommand.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
The ExecuteNonQuery method takes a stored procedure name and a hashtable of parameter values and sends that information to the database after a bit of manipulation from the AssignParameters method does it's magic. Of course if there are any exceptions they are thrown up the stack to be captured by a process of your choosing.
Now that we can call a stored procedure we need to know how the parameters are pulled out of the hashtable. This brings us to the AssignParameters method:
private void AssignParameters(ref SqlCommand sqlCommand, Hashtable hashtable)
{
SqlCommandBuilder.DeriveParameters(sqlCommand);
//Check for a parameter match and set the value
foreach (SqlParameter parameter in sqlCommand.Parameters)
{
if (hashtable.ContainsKey(parameter.ParameterName))
{
parameter.Value = hashtable[parameter.ParameterName];
}
}
}
The AssignParameters method uses the SqlCommandBuilder.DeriveParameters() method to pull out parameter information from the database for the stored procedure stored in the SqlCommand object. Once that information is returned from the database we can go through the hashtable and find the stored procedures by name and create a parameter collection. After all that information is gathered you can then call the ExecuteNonQuery() method in ADO.NET which executes the stored procedure and returns the number of rows affected by the call.
So now let's derive this puppy and see how it works in a real application. We will need to build a derived class for each connection string in our web.config file (in this case just one). So let's create the derived class now:
public class RunDabaseCall : DatabaseCall
{
public RunDabaseCall ()
: base(false, ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString)
{ }
}
Whew! What a monster class! Now to run a database call from your application simply do this:
int Main(args[])
{
Hashtable hash = new Hashtable();
RunDabaseCall runDatabaseCall = new RunDabaseCall();
hash["@parameter1"] = "some data";
runDatabaseCall.ExecuteNonQuery("stored procedure name", hash);
runDatabaseCall.Dispose();
}
You want to run this in a transaction!? Yer crazy, but I like you... Here's all you have to do. Add this bit to the DatabaseCall class:
public void CommittTransaction()
{
try
{
if (sqlTransaction != null)
{
sqlTransaction.Commit();
}
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
AND:
public void RollBackTransaction()
{
try
{
sqlTransaction.Rollback();
}
catch (SqlException sqlex)
{
throw sqlex;
}
catch (Exception ex)
{
throw ex;
}
}
Now change the derived class RunDatabaseCall like this:
public class RunDabaseCall : DatabaseCall
{
public RunDabaseCall ()
: base(true, ConfigurationManager.ConnectionStrings["SiteConnectionString"].ConnectionString)
{ }
}Now when you run your database calls in your application you will be running them in a database transaction:
int Main(args[])
{
Hashtable hash = new Hashtable();
RunDabaseCall runDatabaseCall = new RunDabaseCall();
hash["@parameter1"] = "some data"; <------------------ CASE SenSItiVE
runDatabaseCall.ExecuteNonQuery("", hash);
runDatabaseCall.CommitTransaction();
runDatabaseCall.Dispose();
}
If there are any errors or any reasons to roll back your transaction like it never happened just call the RollBackTransaction() method.
Thats it! Happy database calling.
-Matt
No comments:
Post a Comment