Connection Open Error

  • Thread starter Thread starter Pattnayaks
  • Start date Start date
P

Pattnayaks

Hi,

Below is the piece of code i am using for my connection object &
database transaction.


private SqlConnection _mConnection;
private SqlCommand _mCommand;
private SqlTransaction _mTransaction;


public SqlDataAdapter CreateDataAdaptor(string comText,
CommandType ComType, string Attribute)
{
try
{
_mCommand = new SqlCommand();
_mConnection = new
SqlConnection(ConnectionString(Attribute));
_mCommand.Connection = _mConnection;
_mCommand.CommandType = ComType;
_mCommand.CommandText = comText;
if (_mConnection.State != ConnectionState.Open)
{
_mConnection.Open();
}
SqlDataAdapter da = new SqlDataAdapter(_mCommand);
return da;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_mConnection.State == ConnectionState.Open)
{
_mConnection.Close();
_mConnection.Dispose();
_mCommand.Dispose();
}
}
}
public void Insert(string comText, Parameters Params,
CommandType ComType, string Attribute)
{
try
{
_mCommand = new SqlCommand();
_mConnection = new
SqlConnection(ConnectionString(Attribute));
_mCommand.Connection = _mConnection;
_mCommand.CommandType = ComType;
_mCommand.CommandText = comText;
//_mCommand.Transaction = _mTransaction;
if (_mConnection.State != ConnectionState.Open)
{
_mConnection.Open();
}
if (ComType == CommandType.StoredProcedure)
{
if (Params != null)
{
foreach (Parameter oParam in Params)
{

_mCommand.Parameters.AddWithValue(oParam.Name, oParam.Value);
}
_mCommand.ExecuteNonQuery();
}
}
else
{
_mCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_mConnection.State == ConnectionState.Open)
{
_mConnection.Close();
_mConnection.Dispose();
_mCommand.Dispose();
}
}
}
 
the if condition
if(_mConnection.State == ConnectionState.Open)
needs to be changed

try changing this to
if(_mConnection.State != ConnectionState.Closed)

since the connection could be busy executing some query or fetching
records... its still open but the status code is different

on a side note if all you need to do is call dispose on your objects then
use the using construct which quarantees that dispose will be called on the
objects
 
the if condition
  if(_mConnection.State == ConnectionState.Open)
needs to be changed

try changing this to
  if(_mConnection.State != ConnectionState.Closed)

since the connection could be busy executing some query or fetching
records... its still open but the status code is different

on a side note if all you need to do is call dispose on your objects then
use the using construct which quarantees that dispose will be called on the
objects

--
Misbah Arefinhttps://mcp.support.microsoft.com/profile/MISBAH.AREFINhttp://www.linkedin.com/in/misbaharefin









- Show quoted text -
Click to expand...

Thanks..it worked and i removed _mConnection.Dispose() from finally
block.

Romi
 
Back
Top