How to create Databasecommand in c#

Question

I write my database command in C# as follows

 using (SqlConnection con = new SqlConnection(Config.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO ProjectDetails(pro_name,pro_location,pro_briefdesc,pro_desc,pro_res,pro_contact,pro_add,pro_email,pro_phone) VALUES (@name,@loc,@brief,@desc,@res,@cont,@add,@email,@ph)", con))
            {
                cmd.Parameters.AddWithValue("@name",pro_name);
                cmd.Parameters.AddWithValue("@loc",pro_location );
                cmd.Parameters.AddWithValue("@brief",pro_briefdesc);
                cmd.Parameters.AddWithValue("@desc", pro_desc);
                cmd.Parameters.AddWithValue("@res",pro_res);
                cmd.Parameters.AddWithValue("@cont",pro_contact);
                cmd.Parameters.AddWithValue("@add",pro_add);
                cmd.Parameters.AddWithValue("@email",pro_email);
                cmd.Parameters.AddWithValue("@ph",pro_phone );
                con.Open();
                int modified = cmd.ExecuteNonQuery();
                if (con.State == System.Data.ConnectionState.Open) con.Close();
                return modified;

            }

        }

How can i write my connection open and close commonly - i.e. with minimal code repetition (duplication).

You don't need to call `con.Close();`; the `using` statement does that for you. — SLaks, Jun 19 '13 at 13:24
You already have the command and you've used a `using` which is good so `con` will automatically be closed and disposed of by the CLR. — Darren, Jun 19 '13 at 13:25
Just make sure you always wrap both the Connection and Command in using statements. Doing just either of them doesn't close the other. — Alex, Jun 19 '13 at 13:33

score 5 · Answer 1 · answered Jun 19 '13 at 13:26

5

With the using-statement you don't need to close the connection because it is closed from Dispose.

using (SqlConnection con = new SqlConnection(Config.ConnectionString))
{
    using (SqlCommand cmd = new SqlCommand("INSERT INTO ProjectDetails(pro_name,pro_location,pro_briefdesc,pro_desc,pro_res,pro_contact,pro_add,pro_email,pro_phone) VALUES (@name,@loc,@brief,@desc,@res,@cont,@add,@email,@ph)", con))
    {
        cmd.Parameters.AddWithValue("@name",pro_name);
        // ...
        con.Open();
        int modified = cmd.ExecuteNonQuery();
        //con.Close(); <-- not necessary
        return modified;
    }
}

answered Jun 19 '13 at 13:26

Tim Schmelter

450,073
74
686
939

thank u..but my question is i repetedly use this usinng this connection open and close.How can i avoid this – user2495593 Jun 19 '13 at 13:28
@user2495593 Normally, you don't need to avoid this, because it's optimized very well. – Sergey Kalinichenko Jun 19 '13 at 13:30
1

@user2495593: You don't need to avoid it since you are using [**Connection-Pooling**](http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx) by default which manages all physical connections. So if you call `con.Open` you'll get an available connection and if you call `con.Close`(or use `using`) you'll make this connection again usable for others. So no need to avoid opening or closing connections. Actually it's much more efficient and avoids exceptions if you close connections as soon as possible. If you keep it open the pool always needs to open a new physical connection. – Tim Schmelter Jun 19 '13 at 13:30
I suspect the poster wants to reduce code repetition rather than has concern about connection `Open` and `Close` overhead. – Igor Jun 19 '13 at 13:32
@user2495593 - see my answer - start implementing it and if you have difficulties, ask more. – Igor Jun 19 '13 at 13:41
@user2495593: I don't know what you want to improve. You should use the `using` statement as mentioned above, if you'd create a class which manages the connection and the command you can get into stormy water soon. http://stackoverflow.com/a/9707060/284240 – Tim Schmelter Jun 19 '13 at 14:06

score 1 · Answer 2 · answered Jun 19 '13 at 13:37

Your code is OK and you don't have to concern yourself with how often the connection is opened or closed.

ADO.NET uses connection pooling to reuse free connections and only creates a new one if all previous connections are in use. This increases performance by orders of magnitude for the following reasons:

You don't pay the cost of opening/closing an actual server connection
Any leftover server locks are released when you dispose the connection, even though the actual connection still exists. This means fewer deadlocks and waits.

In fact, it is A Very Good Thing to open a connection just before you need it and dispose of it immediatelly after you finish with it.

This isn't a new feature. It goes all the way back to ADO and OLE DB.

Finally, you can control the minimum and maximum number of connections kept in the pool with connection string parameters for most ADO.NET and OLE DB providers. This is extremely usefull in server applications.

By setting a minimum number you ensure your application has enough connections to handle its typical load without wasting time opening new connections as new requests come in. Setting the maximum number ensures you don't acquire so many server locks that your clients end up waiting for each other most of the time.

How to create Databasecommand in c#

2 Answers2