Using Statement in C#

Pankil Bhatt

string constr = ConfigurationManager.ConnectionStrings["SampleAppConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "delete FROM [DetailTable] where ID = '" + prodID +"'";
cmd.Connection = con;
cmd.ExecuteNonQuery();
}
}

ERROR :

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

while I am Using statement needs to write connection open () and close()??

which is the better why to use the using statement?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kashif Asif10y ago
Dear,
First you need to open and close the connection.
and
Using() block you use for memory management. When you have done your work it will automatically close the connection.
0
Tapan Patel10y ago
1. stringconstr=ConfigurationManager.ConnectionStrings["SampleAppConnectionString"].ConnectionString;
2. using(SqlConnectioncon=newSqlConnection(constr))
3. {
4. con.Open();
5. using(SqlCommandcmd=newSqlCommand())
6. {
7. cmd.CommandText="deleteFROM[DetailTable]whereID='"+prodID+"'";
8. cmd.Connection=con;
9. cmd.ExecuteNonQuery();
10. }
11. con.Close();
12. }
0

Bikesh Srivastava10y ago

Hi,

You need to open and close connection .

but using use for memory managment .

Vinay Singh10y ago

check the code

string constr = ConfigurationManager.ConnectionStrings["SampleAppConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constr)
con.Open();
SqlCommand cmd = new SqlCommand()

cmd.CommandText = "delete FROM [DetailTable] where ID = '" + prodID +"'";
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
}

modify your code as above

gnana bhaskar10y ago

Hi,

The using block helps manage resources. Conceptually it protects the whole system's resources by specifying the scope of the usage of the resource. The using statement is combined with a type that implements IDisposable.

If any class implements the IDisposable interface and the required Dispose method then we can create the instance of the class with "using".

At the end of the using block, it will call the dispose method the the class. so dispose method has to handle necessary jobs likes close the connection or close the file or handle any unmanaged.

regards

Bhaskar

Surya Kant10y ago

If you writing statement with using keyword then it mean that the object will destroyed automatically and connection should be closed but it's does not means that "Using" will open the sql connection automatically; so you required to manually open the connection for this and in-case if you forget to close the connection then the using will close the connection for you and destroy all the instance in a time interval.

So that's why it's an good practice to write the statement with Using.

Rajeesh Menoth10y ago

Hi,

The reason for the "using" statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn't require explicit code to ensure that this happens.

Reference :

http://stackoverflow.com/questions/75401/uses-of-using-in-c-sharp?lq=1

http://www.c-sharpcorner.com/UploadFile/ff0d0f/using-keyword-in-C-Sharp/

Deeksha Pandit10y ago

Using() block you use for memory management. here you can say for closing the connection after you have got your work done.

This comes under C# best practices. when ever you are creating a new instance of database connection or a windows form, put it in using block. The instance will be destroyed after its use or you can say memory will be released.