Best practices for writing data access in ASP.NET

Question

Ideally I would like to put all the code to access and fetch the data from the database in a separate utility files and all those methods at data binding times from the .net code behind file. The problem comes is when should the connection object be closed. For simple stuff, the helper method can close the connection but for things like DataGrid or GridView you can't close connection until you have bound the data to the GridView...so how can I do that in the best design way..so if I have class call DatabaseHelper and it has a method like public static DataSet fetchEmployeesData.... this method cant close the connection

1. My other question is how do use the transactions and do atomic operations..so for example, if i have 3 or 4 methods in my db helper class say InsertX, InsertY, UpdateZ etc and i have to call all these operations in a transaction, how do i write such a code with a good design.. (in this example, I would be calling these methods from the aspx code behind file on some event say form submit)

Answer 1

If all you need to do is display the data, you can indeed close the connection before databinding. The GridView only requires you to provide an object that can be enumerated (that is, an object that implements IEnumerable), so you are welcome to close the connection after calling DataAdapter.Fill - the DataSet does not need to be connected when to binding.

So, once you've called Fill - probably inside fetchEmployeesData - you can close the connection. If the fetchEmployeesData method requires callers to provide a connection, you can close it after the call to that method:

DataSet employeesDataSet = null;
using (IDbConnection connection = DatabaseHelper.CreateConnection()) {
  employeesDataSet = DatabaseHelper.fetchEmployeesData(connection);
}

// The connection is closed now (since 'using' guarantees Dispose() is called)
EmployeeDataGridView.DataSource = employeesDataSet;
EmployeeDataGridView.DataBind();

Answer 2

Advice: Don't reinvent the wheel.

{Pulls Fully Baked Wheel from the oven}

Here you go: Data Access Application Block Streamlines Your Data-Access Layer

Answer 3

My general advice is to put all data access into a separate class (often called a repository). If you want to make life easier on yourself, go grab an ORM to do the mapping for you. Linq To SQL is good (and not dead) if you are accessing SQL Server. Entity Framework, SubSonic, NHibernate, LLBLGen all work well. (Note: NHibernate is my favorite, but if you don't have someone close by that you can ask questions to, go with another ORM).

The ORM will then handle the connections for you, and will make CRUD access much easier for you.

Once done, you can then map to your data access classes. You can bind directly to a List in the DataGrid.

Answer 4

Hi,

My approach is similar to Chris'. What I might add is that inside of the DAL, you should open and close your connection as soon as you're done with your data operation. This increases scalability and still performs well with connection pooling.

When combining multiple operations into a single transaction, I'll use TransactionScope, which is in the System.Transactions namespace. You'll need to add a reference to the System.Transactions.dll too. Here's some pseudo code on how this might work:

using (var txScope = new TransactionScope(TransactionScopeOption.Required))
{
    InsertX...
    InsertY...
    InsertZ...

    txScope.Complete();
}

Remember to call Complete (often as the last statement) on your transaction scope. Otherwise, the transaction will automatically roll back (a common gottcha when first using TransactionScope).

Joe

Answer 5

You are supposed to open the connection as late as possible and close it as soon as possible. So I usually have a method that returns a DataTable and looks like:

  public DataTable GetUsersRightsForProject(int ProjectId)
        {
            SqlCommand cmd = GetNewCmd("dbo.GetUsersRightsForProject");
            cmd.Parameters.Add("@ProjectId", SqlDbType.Int).Value = ProjectId;
            return GetTable(cmd);
        }

The GetTable is a method in my base class that I use for all of my projects.

protected DataTable GetTable(SqlCommand objCmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                da.SelectCommand = objCmd;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                LogException(ex);
                throw ex;
            }
            finally
            {
                Close(objCmd);
                da.Dispose();
                da = null;

            }

            return dt;

        }

Notice that I am openning the connection as late as possible - the da.Fill(dt) actually opens and closes it. I then double check that it is closed in the "finally" section. My base class takes care of getting the connection string, logging any exceptions and I have wrappers similar to this one for GetDataSet, ExecuteScalar, etc.

So I could bind to a GridView like this:

GridView1.DataSource = cApp.DB.GetUsersRightsForProject(ProjectId);
GridView1.DataBind();

(Usually you have a business object in the middle of that.)

My pattern is usually the following:

1. create a new table.
2. create the stored procs to get data and save data.
3. create the data access layer methods that call the stored procs (like the first method above).

Some of this coding can be tedious especially if your table has a lot of columns. So I wrote 2 stored procs to help in this. One generates a "save" stored proc code based on a table. The second one generates the data access method code based on a stored proc.

They can be found here