Sunday, October 04, 2009

DataAccess – Part II

In Part I of this topic, I tried to explain the basic functionality you should strive for in a DataAccess layer in your applications:

  • Keep the DataAccess totally separate from your UI layer.
  • Pass data objects between the layers.

In Part II, I will attempt to show you how to abstract your DataAccess layer one more step. The key is to program to the IDb interfaces, as I will show below.

My DataAccess layer has two base classes: BBConnection and BBDataAccess. All the methods are coded to the various DataAccess Interfaces (the IDb interface objects). DataSets are used simply for transporting data back and forth. I know that there are plenty of camps who advocate Biz objects for this, but I'm a believer of Typed DataSets for transporting my data between front-end and back-end.

The BBConnection class basically has a GetConnection() method that finds the Connection String from the app's config info and returns an instantiated Connection. It returns it as an interface, the IDbConnection.

/// <summary>
/// For another type of database, replace the SqlConnection in the constructor
/// </summary>
public class BBConnection
{
#region Declaration
// Use a variable to store the connection string, if you try and get it from the SqlConnection object, it strips the password
public string ConnectionString = "";

#endregion

#region Methods
public IDbConnection GetConnection()
{
return this.GetConnection("");
}
public IDbConnection GetConnection(string tConnectionString)
{
string cConnection = tConnectionString;

if (tConnectionString == "")
cConnection = ConfigurationSettings.AppSettings["ConnectionString"];

SqlConnection conn = new SqlConnection(cConnection);
this.ConnectionString = cConnection;
return conn;
}

#endregion
}

Probably a slightly better design than what I show below, would be to use a Factory Pattern ... but my code was initially refactored from more tightly-coupled-to-SQL code, and at the time was easier done the way I did it. I'll leave that as an exercise for the reader! ;-)

The BBDataAccess class, simplified, is something like this:

public class BBDataAccess
{
#region Declarations

private IDbConnection oConnection;
private IDbCommand oCommand;
private IDbDataAdapter oAdapter;
private IDataParameterCollection oParms;
public string ConnectionString = "";
protected string ErrorMessage = "";

#endregion

#region Constructor/Destructor

public BBDataAccess()
{
BBConnection o = new BBConnection();
this.oConnection = o.GetConnection();
this.ConnectionString = o.ConnectionString;

this.oCommand = this.GetIDbCommand();
this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
this.oCommand.Connection = this.oConnection;
this.oCommand.CommandType = CommandType.StoredProcedure;
}

#endregion

#region Get/Use IDb Interface Objects

protected IDbCommand GetIDbCommand()
{
return new SqlCommand();
}
protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
{
return new SqlDataAdapter((SqlCommand)command);
}
protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
{
return new SqlParameter(ParmName, ParmValue);
}
protected void DeriveParameters(IDbCommand command)
{
SqlCommandBuilder.DeriveParameters((SqlCommand)command);
}
#endregion

// Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.
// It's these various methods that get used in the classes that are sub-classed from this "base" class.
// Here's just a few of them:

protected void ClearParameters()
{
this.oCommand.Parameters.Clear();
this.ErrorMessage = "";
}

protected void AddParms(string ParmName, object ParmValue)
{
try
{
if (ParmName.StartsWith(this.AtSign) == false)
ParmName = this.AtSign + ParmName;

if (ParmValue != DBNull.Value)
this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
}
catch (Exception ex)
{
this.ErrorMessage += ex.Message;
}
}
protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
{
this.AddParms(ParmName, ParmValue);
this.SetParmDirection(ParmName, direction);
}
protected bool FillData(DataSet ds, string StoredProcName)
{
try
{
this.oCommand.CommandText = StoredProcName;
this.oAdapter.Fill(ds);
}
catch (Exception ex)
{
this.ErrorMessage += ex.Message;
this.CloseConnection();
return false;
}
return true;
}
}

In this way, your DataAccess sub-classes are totally "disassociated" from knowing what your back-end data is (could be SqlServer, as the above class is, but if you also have an Oracle customer, you have another base class exactly the same as the above class, but it uses Oracle-specific stuff instead and you use the appropriate DLL depending on your which database your customer has ... however, you're still programming to the interface (the IDb Interface objects), so your sub-classes, which are in different projects (and thus different DLLs) don't care and don't need to be changed.

So, an example of a sub-class might be this:

public class CustomerAccess : BBDataAccess
{
#region Public Get Methods

public DataSet GetCustomerInfo(long CustomerKey)
{
CustomerDataSet ds = new CustomerDataSet();

this.ClearParameters();
this.AddParms("CustomerKey", CustomerKey);
this.FillData(ds, "csp_Customer_Get");

return ds;
}

// more methods

#endregion
}

UPDATE: Since posting my 2-part DataAccess "article", I've realized that it should have been 3-part. There's another, better methodology for DataAccess that I've used and I'd like to show you all. So, see Part III of my series.

2 comments:

  1. Hi Bonnie

    I have been trying to implement Part II into my application. I have a customers table and as far as I can see I have created a CustomerDataSet using Add New Item > Dataset and then dragged on my Customers table to the design area and saved it, naming it CustomerDataSet.

    When I run the code in the CustomerAccess class as you have it, the dataset returns empty.

    If I change the code to DataSet ds = new DataSet() // instead of CustomerDataSet ds = new CustomerDataSet.

    I get the dataset complete with the expected data.

    I tried with plain sql in the commandText and the stored procedure but got the same results each way. BOth work with DataSet, both Dont work with CustomerDataSet. Perhaps, I have created my dataset incorrectly. Can you advise?

    Thanks,

    J

    ReplyDelete
    Replies
    1. Although that's the way it's described in many places to create a Typed DataSet, that's not the way you should do it. You don't want to be dragging any data stuff onto your Form, for several reasons. Please read my blog post about this (and be sure to read the comments too):

      http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html

      Delete