KMC KMC - 2 months ago 15
C# Question

Connection not close between SqlCommand

I programmatically create a database then subsequently create a table in the database. The database is created, but not the table.

// Create Database
try
{
using (SqlCommand cmd = new SqlCommand(connstr, sqlConn))
{
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButton.OK, MessageBoxImage.Information);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}

// Create table
try
{
using (SqlCommand cmd = new SqlCommand(
"CREATE TABLE dbo.MyTable ("
+ "ID int IDENTITY(1,1) PRIMARY KEY,"
+ "MyProduct nvarchar(100) NOT NULL,"
+ "MyDateTime datetime NOT NULL);"
+ "", sqlConn))
{
sqlConn.Open();
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
catch (Exception ex)
{

MessageBox.Show(ex.ToString());
}


But I receive the error message saying my connection is not closed.

enter image description here

I tried repeatedly adding sqlConn.Close() to end of every {block} but still I get the same error. How can I properly close the connection after creating the database then reopen connection again to create table?

[EDIT]

Following the answers input, I have restructured my code that I'm sure wrapping property without duplicating Open().

// Create Database
try
{
using (SqlConnection sqlConn = new SqlConnection(sqlConnectionStr))
{
using (SqlCommand cmd = new SqlCommand(connstr, sqlConn))
{
try
{
sqlConn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButton.OK, MessageBoxImage.Information);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
}
}
}
catch(Exception ex)
{
MessageBox.Show("(1)\n" + ex.ToString());
}

// Create Table
try
{
using (SqlConnection sqlConn = new SqlConnection(sqlConnectionStr))
{

using (SqlCommand cmd = new SqlCommand("CREATE TABLE dbo.MyTable ("
+ "ID int IDENTITY(1,1) PRIMARY KEY,"
+ "MyProduct nvarchar(100) NOT NULL,"
+ "MyDateTime datetime NOT NULL);"
+ "", sqlConn))
{
sqlConn.Open();
cmd.ExecuteNonQuery();
sqlConn.Close();
}
}

}
catch (Exception ex)
{
MessageBox.Show("(2)\n" + ex.ToString());
}


Then I receive the error There is already an object named 'MyTable' in the database. But when I look into SSMS, the table isn't there. I am very confused now.

enter image description here

Win Win
Answer

I do not see that you close the connection for second query. You can wrap with using statement for SqlConnection.

FYI: If you use using statement, you do not need to explicitly close the connection.

string cmdText = "SELECT * FROM SomeTable";
using (var conn = new SqlConnection(connectionString))
using (var cmd = new SqlCommand(cmdText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
    MessageBox.Show(...);
}