Connection Open Error -- dotnet ado.net

Pattnayaks@gmail.com
3/25/2008 8:10:20 PM

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
3/26/2008 8:23:07 AM

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 Arefin
https://mcp.support.microsoft.com/profile/MISBAH.AREFIN
http://www.linkedin.com/in/misbaharefin

[quoted text, click to view]

Pattnayaks@gmail.com
3/27/2008 10:53:00 PM

On Mar 27, 2:23=A0am, Misbah Arefin
[quoted text, click to view]

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : Connection Open Error