Using Statement in C#

Question

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?

Surya Kant · Answer

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.

Tapan Patel · Answer

string constr=ConfigurationManager.ConnectionStrings[ "SampleAppConnectionString" ].ConnectionString; using (SqlConnectioncon= new SqlConnection(constr)) { con.Open(); using (SqlCommandcmd= new SqlCommand()) { cmd.CommandText= "deleteFROM[DetailTable]whereID='" +prodID+ "'" ; cmd.Connection=con; cmd.ExecuteNonQuery(); } con.Close(); }

Kashif Asif · Answer

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.

Bikesh Srivastava · Answer

Hi, You need to open and close connection . but using use for memory managment .

Vinay Singh · Answer

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 bhaskar · Answer

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

Rajeesh Menoth · Answer

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 Pandit · Answer

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.