I have been using a methodology of using DB connections within 'Using' statements. Recently I ran into the old chestnut of sharing a connection between BOs for transactional purposes, and hating the old approach of passing everthing around within criteria objects, I decided to look at ApplicationContext.LocalContext.
I need a few pointers as to best practice here - should I open one connection at startup, pass it into ApplicationContext.LocalContext["cn"] and then use throughout?? I used to do this kind of thing in the days of VB 6.0 and was told it was bad :-(.
Advice greatly appreciated before I start rewriting everyting ;-).
Thanks
The Bone
The general rule is that a connection should be kept open for as short a period of time as possible. In CSLA this typically means no longer than the duration of a DataPortal_XYZ call.
To simplify things, and make your code more reusable, you can use something like this (typed off the top of my head, so it may not be totally right):
Imports System.Configuration
Public Class ConnectionManager
Implements IDisposable
Private _connection As SqlConnection
Private _name As String
private _refCount As Integer
Private Sub New(name As String)
Dim connectionString As String
connectionString = ConfigurationManager.ConnectionStrings(name).ConnectionString
_connection = New SqlConnection(connectionString)
_connection.Open()
_name = name
Csla.ApplicationContext(name) = Me
End Sub
Public ReadOnly Property Connection() As SqlConnection
Get
Return _connection
End Get
End Property
Public ReadOnly Property Name() As String
Get
Return _name
End Get
End Property
Private Sub AddRef()
_refCount += 1
End Sub
Private Sub DeRef()
_refCount -= 1
If _refCount = 0 Then
_connection.Dispose()
Csla.ApplicationContext.Remove(_name)
GC.SuppressFinalize(Me)
End If
End Sub
Public Shared Function GetManager(name As String) As ConnectionManager
Dim mgr As ConnectionManager = CType(Csla.ApplicationContext(name), ConnectionManager)
If mgr Is Nothing Then
mgr = New ConnectionManager(name)
End If
mgr.AddRef()
Return mgr
End Function
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
End Sub
Private Sub Dispose(disposing As Boolean)
If disposing Then
DeRef()
End If
End Sub
Protected Overrides Sub Finalize()
Dispose(False)
End Sub
End Class
Then your DP_XYZ code can universally look something like this:
Private Overrides Sub DataPortal_Fetch(criteria As Criteria)
Using mgr As ConnectionManager = ConnectionManager.GetManager("mydb")
Dim cn As SqlConnection = mgr.Connection
' use connection
End Using
End Sub
Without something like this you have to write code to see if ApplicationContext has an open connection or not in every DP_XYZ method, and that's somewhat repetative.
Many thanks for this - this is really useful.
Bonio
Rocky,
One problem I have run into with this method is when I have a set of nested datareaders (in a fetch of a parent and child data from another object i.e. not loaded in the same stored proc). The connection keeps complaining that there is another datareader associated with the connection - is there an obvioud way around this without returning to individual creation/disposal of connections per DataPortal_Fetch??
Thanks
The Bone
If you are using nested datareaders or similar techniques, then
you need multiple open connections – no way around it that I’m
aware of.
That means that if you are using TransactionScope transactions,
you’ll use the DTC. No way around that either.
Rocky
I use the DbProviderFactory to create a connection manager that is database independent.
I also use connection manager to handle command creation using a DbCommand wrapper and ADO.Net transactions when TransactionScope is not an option. The end result is that all my data access code universally looks like this
private void DataPortal_Fetch(SingleCriteria<ContactMethod, Guid> criteria)
{
using (ConnectionManager mgr = ConnectionManager.GetManager("MyDatabase"))
{
using (CommandWrapper cm = mgr.CreateCommand(CommandType.StoredProcedure, "[dbo].[GetContactMethod]"))
{
cm.Parameters.Add(mgr.CreateParameter("@PkId", DbType.Guid, criteria.Value));
using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
{
dr.Read();
_pkId = dr.GetGuid("PkId");
_name = dr.GetString("Name");
ValidationRules.CheckRules(); ;
}
}//using Command
}//using Manager
}
private override void DataPortal_Insert()
{
using (ConnectionManager mgr = ConnectionManager.GetManager("MyDatabase"))
{
using (CommandWrapper cm = mgr.CreateCommand(CommandType.StoredProcedure, "[dbo].[InsertContactMethod]"))
{
cm.Parameters.Add(mgr.CreateParameter("@PkId", DbType.Guid, _pkId));
cm.Parameters.Add(mgr.CreateParameter("@Name", DbType.String, _name));
cm.ExecuteNonQuery();
}//using Command
}//using ConnectionManager
}
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Csla;
namespace MyNamespace
{
internal class ConnectionManager : IDisposable
{
private static object _lock = new object();
private DbProviderFactory _factory = null;
private DbTransaction _tr = null;
private DbConnection _cn = null;
private string _name;
public static ConnectionManager GetManager(string name)
{
lock (_lock)
{
ConnectionManager mgr = (ConnectionManager)Csla.ApplicationContext.LocalContext["db:" + name];
if (mgr == null)
{
mgr = new ConnectionManager(name);
Csla.ApplicationContext.LocalContext["db:" + name] = mgr;
}
mgr.AddRef();
return mgr;
}
}
private ConnectionManager(string name)
{
_name = name;
_factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[name].ProviderName);
_cn = _factory.CreateConnection();
_cn.ConnectionString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
_cn.Open();
}
public DbConnection Connection
{
get { return _cn; }
}
public string Name
{
get { return _name; }
}
#region Reference Counting
private int _refCount;
private void AddRef()
{
_refCount += 1;
}
private void DeRef()
{
lock (_lock)
{
_refCount -= 1;
if (_refCount == 0)
{
if (_tr != null)
{
_tr.Dispose();
_tr = null;
}
_cn.Dispose();
Csla.ApplicationContext.LocalContext.Remove("db:" + _name);
//GC.SuppressFinalize(this);
}
}
}
#endregion
#region Transaction
public DbTransaction BeginTransaction()
{
if (_tr == null)
_tr = _cn.BeginTransaction();
return _tr;
}
public void Commit()
{
if (_tr != null)
{
try
{
_tr.Commit();
}
finally
{
_tr.Dispose();
_tr = null;
}
}
}
public void Rollback()
{
if (_tr != null)
{
try
{
_tr.Rollback();
}
finally
{
_tr.Dispose();
_tr = null;
}
}
}
#endregion
#region CreateCommand
public CommandWrapper CreateCommand()
{
DbCommand cm = _cn.CreateCommand();
if (_tr != null) cm.Transaction = _tr;
return new CommandWrapper(cm);
}
public CommandWrapper CreateCommand(CommandType commandType, string commandText)
{
DbCommand cm = _cn.CreateCommand();
cm.CommandType = commandType;
cm.CommandText = commandText;
if (_tr != null) cm.Transaction = _tr;
return new CommandWrapper(cm);
}
public CommandWrapper CreateCommand(CommandType commandType)
{
DbCommand cm = _cn.CreateCommand();
cm.CommandType = commandType;
if (_tr != null) cm.Transaction = _tr;
return new CommandWrapper(cm);
}
#endregion
#region CreateParameter
public DbParameter CreateParameter(string name, DbType dbType, ParameterDirection direction, int size, object value)
{
DbParameter p = _factory.CreateParameter();
p.ParameterName = name;
p.DbType = dbType;
p.Size = size;
p.Value = (value == null) ? DBNull.Value : value;
p.Direction = direction;
return p;
}
public DbParameter CreateParameter(string name, DbType dbType, object value)
{
DbParameter p = _factory.CreateParameter();
p.ParameterName = name;
p.DbType = dbType;
p.Value = (value == null) ? DBNull.Value : value;
return p;
}
public DbParameter CreateParameter(string name, object value)
{
DbParameter p = _factory.CreateParameter();
p.ParameterName = name;
p.Value = (value == null) ? DBNull.Value : value;
return p;
}
public DbParameter CreateOutParameter(string name, DbType dbType, int size)
{
DbParameter p = _factory.CreateParameter();
p.ParameterName = name;
p.Size = size;
p.DbType = dbType;
p.Direction = ParameterDirection.Output;
return p;
}
#endregion
#region Dataset/DataTable
public DataSet CreateDataSet(CommandWrapper command)
{
using (DbDataAdapter da = _factory.CreateDataAdapter())
{
return command.ExecuteDataSet(da);
}
}
public DataTable CreateDataTable(CommandWrapper command)
{
using (DbDataAdapter da = _factory.CreateDataAdapter())
{
return command.ExecuteDataTable(da);
}
}
#endregion
#region IDisposable
public void Dispose()
{
Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
DeRef();
}
}
~ConnectionManager()
{
Dispose(false);
}
#endregion
}
public class CommandWrapper : IDisposable
{
DbCommand _command;
public CommandWrapper(DbCommand command)
{
_command = command;
}
public string CommandText
{
get { return _command.CommandText; }
set { _command.CommandText = value; }
}
public int CommandTimeout
{
get { return _command.CommandTimeout; }
set { _command.CommandTimeout = value; }
}
public CommandType CommandType
{
get { return _command.CommandType; }
set { _command.CommandType = value; }
}
public UpdateRowSource UpdatedRowSource
{
get { return _command.UpdatedRowSource; }
set { _command.UpdatedRowSource = value; }
}
public DbParameterCollection Parameters
{
get { return _command.Parameters; }
}
public void Cancel()
{
_command.Cancel();
}
public int ExecuteNonQuery()
{
return _command.ExecuteNonQuery();
}
public DbDataReader ExecuteReader()
{
return _command.ExecuteReader();
}
public DbDataReader ExecuteReader(CommandBehavior behavior)
{
return _command.ExecuteReader(behavior);
}
public object ExecuteScalar()
{
return _command.ExecuteScalar();
}
public void Prepare()
{
_command.Prepare();
}
internal DataSet ExecuteDataSet(DbDataAdapter adapter)
{
adapter.SelectCommand = _command;
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet;
}
internal DataTable ExecuteDataTable(DbDataAdapter adapter)
{
adapter.SelectCommand = _command;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return dataTable;
}
#region IDisposable
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
if (disposing)
{
_command.Dispose();
}
}
~CommandWrapper()
{
Dispose(false);
}
#endregion
}
}
Protected Overrides Sub DataPortal_Update()
Using transaction As New TransactionScope()
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sql As String
' TODO: set sql
Using cmd As DbCommand = db.GetSqlStringCommand(sql)
' TODO: add parameters to DbCommand object
' db.AddInParameter(cmd, "EmployeeID", DbType.Int32, criteria.Id)
' AddInsertUpdateParameters(db, cmd)
db.ExecuteNonQuery(cmd)
End Using
' TODO: update child collections
' mChildren.Update(Me)
transaction.Complete()
End Using
End Sub
Copyright (c) Marimer LLC