Connection Open Error

[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();
}
}
}

 

[Misbah Arefin]

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

 

[Pattnayaks]

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 -

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

Romi