Closing connection using C# problem

[misogsk]

Hi All,

i'm creating database using C#. Everything works fine, creating database, creating stored procedures from file. then i use connection_name .Close() to close connection to database, but connection still stays open till i close whole application.
I need to close all connections immediatelly.
How can i do this from C# code?
Please help.
Thanks a lot

[Plater]

How are you using your connection?
Are you closing all your datareaders and dataadapters and sqlcommands?

They maintain a copy of the connection I believe if they are not closed

[misogsk]

How are you using your connection?
Are you closing all your datareaders and dataadapters and sqlcommands?

They maintain a copy of the connection I believe if they are not closed

I'm using only sqlcommands. After each executed command i close connection.

[nateraaaa]

I'm using only sqlcommands. After each executed command i close connection.

are you closing the command also? You should also call the Dispose() method on the cmd and the connection to force garbage collection.

Nathan

[misogsk]

are you closing the command also? You should also call the Dispose() method on the cmd and the connection to force garbage collection.

Nathan

I try it with no result:(

[nateraaaa]

I try it with no result:(

Please post the code giving you the problem. Make sure you include where you open and close and dispose the database connection and your slqcommand.

[misogsk]

Please post the code giving you the problem. Make sure you include where you open and close and dispose the database connection and your slqcommand.

public void CreateConnectio n(string connectionType)
{
string sqlConnectionSt ring = null;
if (connectionType == "db")
{
sqlConnectionSt ring = Common.GetConne ctionStringToDa tabase(_serverN ame, _instance, _databaseName, _windowsAuth, _username, _password);
}
else
{
sqlConnectionSt ring = Common.GetConne ctionStringToSe rver(_serverNam e, _instance, _windowsAuth, _username, _password);
}
_dbConnection = new SqlConnection(s qlConnectionStr ing);

try
{

_dbConnection.O pen();
}
catch (Exception exp)
{

MessageBox.Show (exp.Message);

}



}
public Boolean CloseConnection ()
{
try
{
Server srv = new Server(_serverN ame + @"\" + _instance);
_dbConnection.D ispose();
_dbConnection = null;
srv.KillAllProc esses(_database Name);

}
catch (Exception exp)
{

MessageBox.Show (exp.Message);
return false;

}
return false;
}
i call Method CreateConnectio n and to close connection i use CloseConnection .
one method with SqlCommand(i have another one to create porcedures from files).

public Boolean CreateDatabase( )
{
SqlCommand sqlCmd = new SqlCommand();
try
{
sqlCmd.CommandT ype = CommandType.Tex t;
sqlCmd.CommandT ext = "IF EXISTS(SELECT name FROM sysdatabases WHERE name = '" + _databaseName + "') DROP DATABASE " + _databaseName;
sqlCmd.Connecti on = _dbConnection;
sqlCmd.ExecuteN onQuery();
sqlCmd.Dispose( );
sqlCmd.CommandT ext = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_ CI_AS";
sqlCmd.CommandT ext = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_ CI_AS";
sqlCmd.Dispose( );
sqlCmd.ExecuteN onQuery();
}
catch (Exception exp)
{

MessageBox.Show (exp.Message);
return false;
}

return true;

}

[nateraaaa]

public void CreateConnectio n(string connectionType)
{
string sqlConnectionSt ring = null;
if (connectionType == "db")
{
sqlConnectionSt ring = Common.GetConne ctionStringToDa tabase(_serverN ame, _instance, _databaseName, _windowsAuth, _username, _password);
}
else
{
sqlConnectionSt ring = Common.GetConne ctionStringToSe rver(_serverNam e, _instance, _windowsAuth, _username, _password);
}
_dbConnection = new SqlConnection(s qlConnectionStr ing);

try
{

_dbConnection.O pen();
}
catch (Exception exp)
{

MessageBox.Show (exp.Message);

}



}
public Boolean CloseConnection ()
{
try
{
Server srv = new Server(_serverN ame + @"\" + _instance);
_dbConnection.D ispose();
_dbConnection = null;
srv.KillAllProc esses(_database Name);

}
catch (Exception exp)
{

MessageBox.Show (exp.Message);
return false;

}
return false;
}
i call Method CreateConnectio n and to close connection i use CloseConnection .
one method with SqlCommand(i have another one to create porcedures from files).

public Boolean CreateDatabase( )
{
SqlCommand sqlCmd = new SqlCommand();
try
{
sqlCmd.CommandT ype = CommandType.Tex t;
sqlCmd.CommandT ext = "IF EXISTS(SELECT name FROM sysdatabases WHERE name = '" + _databaseName + "') DROP DATABASE " + _databaseName;
sqlCmd.Connecti on = _dbConnection;
sqlCmd.ExecuteN onQuery();
sqlCmd.Dispose( );
sqlCmd.CommandT ext = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_ CI_AS";
sqlCmd.CommandT ext = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_ CI_AS";
sqlCmd.Dispose( );
sqlCmd.ExecuteN onQuery();
}
catch (Exception exp)
{

MessageBox.Show (exp.Message);
return false;
}

return true;

}

Have you ever used the finally block associated with a try catch block? The finally block is a gerat place to close and dispose sqlcommands and database connections. In your CreateDatabase method I would recommend only closing your sqlCmd and then adding a finally block where you will dispose it. Please look for the words MODIFIED or ADDED to see the changes that I made to your CreateDatabase method.

public Boolean CreateDatabase()
        {
            SqlCommand sqlCmd = new SqlCommand();
            try
            {
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandText = "IF EXISTS(SELECT name FROM sysdatabases WHERE name = '" + _databaseName + "') DROP DATABASE " + _databaseName;
                sqlCmd.Connection = _dbConnection;
                sqlCmd.ExecuteNonQuery();
                sqlCmd.Close;  //MODIFIED
                sqlCmd.CommandText = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_CI_AS";
                sqlCmd.CommandText = "CREATE DATABASE " + _databaseName + " COLLATE Latin1_General_CI_AS";
                sqlCmd.ExecuteNonQuery();
                sqlCmd.Close; //MODIFIED
                return true; //MODIFIED

            }
            catch (Exception exp)
            {

                MessageBox.Show(exp.Message);
                return false;
            }
           finally //ADDED
          {
              sqlCmd.Dispose();
              _dbConnection.Close();
              _dbConnection.Dispose();             
          }

Nathan

[aalmakto]

try
[code= c#]using (YOURSQLCONNECTI ON)
{
//Code that requires the connection
codecodecodecod ecode

} //<----At this point the connection automatically closes

[/code]