CODEWITHSUNDEEP
c#sql-serversqlconnection
MustafaP
MustafaP

Reputation: 6633

Do we need open db whendb is defined in using?

when I use using for connections, I know there is no need to use close or dispose. I wonder, do we need to use open?

using (var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"]))
{             
       dbSqlConnection.Open();  // is this required?
}

Upvotes: 0

Views: 91

Answers (4)

dknaack
dknaack

Reputation: 60496

It depends on what you are doing. The SqlAdapter for example opens and closes the connection by itself.

If you use the SqlCommand then yes, you need to open the connection manually to use it because the constructor of SqlConnection does not open the connection automatically.

As you said, the Dispose method (automatically called when leaving the using block) closes the connection if they is still open.

Using

using (var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"]))
{             
    dbSqlConnection.Open();  // is this required?
}

Try finally

var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"];
try
{
   SqlCommand command = new SqlCommand("SELECT ...", dbSqlConnection);
   dbSqlConnection.Open();
   // execute the SqlCommand e.g. ExecuteReader, ExecuteNonQuery, ExecuteScalar 
}
catch (Exception ex)
{
   // handle the exception
}
finally
{   
    // this gets called even if a exception has occured   
    if (dbSqlConnection != null)
       dbSqlConnection.Dispose();
}

Upvotes: 3

Leo
Leo

Reputation: 14830

It depends on what you're doing...if you're manually executing a command using the SqlCommand object you will definitely need to open the connection before you execute any methods on the command. However, if you're using something like DataAdapter...you don't have to because it will manage the connection for you.

Using the SqlCommand object...

using (var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"]))
{             
       var cmd = new SqlCommand("Your_Sql_Query", con);
       dbSqlConnection.Open();  // is this required?
       cmd.ExecuteNonQuery();
}

using a SqlDataAdapter...

using (var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"]))
    { 
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(
            queryString, dbSqlConnection );
        adapter.Fill(ds);
    }

Notice that the SqlDataAdapter will manage the connection for you, it will open and dispose it

Upvotes: 4

LittleDragon
LittleDragon

Reputation: 2437

no you do not need to do it always its depend on how you going to implement im not use open() command

here is the way to do it automatically with DataAdapter

 SqlConnection con = dBClass.SqlConnection(); // return the connection string 

    SqlDataAdapter da = new SqlDataAdapter("[PR_R_TRN_test]", con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;

    da.SelectCommand.Parameters.Add("@plong_ID", SqlDbType.BigInt).Value = ID;


                    DataSet result = new DataSet();
                    da.Fill(result);

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98760

Yes, you need to open it.

The using statement calls the Dispose method in a finally block. It doesn't open any connection. If your code inside in using statement doesn't open your connection on behind (like SqlDataAdapter), you need to open it manually.

using (var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"]))
{             

}

is equavalent to

var dbSqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString1"];
try
{

}
finally
{      
    if (dbSqlConnection != null)
        ((IDisposable)dbSqlConnection).Dispose();
}

As you can see, using statement doesn't do anything about opening a connection.

Upvotes: 5

Related Questions