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
– SLaks, Commented Jun 19, 2013 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
– Darren, Commented Jun 19, 2013 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
– Alex, Commented Jun 19, 2013 at 13:33

Tim Schmelter · Accepted Answer · 2013-06-19 13:26:00Z

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, 2013 at 13:26

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2495593 Over a year ago

thank u..but my question is i repetedly use this usinng this connection open and close.How can i avoid this

Sergey Kalinichenko Over a year ago

@user2495593 Normally, you don't need to avoid this, because it's optimized very well.

Tim Schmelter Over a year ago

@user2495593: You don't need to avoid it since you are using Connection-Pooling 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.

Igor Over a year ago

I suspect the poster wants to reduce code repetition rather than has concern about connection Open and Close overhead.

Igor Over a year ago

@user2495593 - see my answer - start implementing it and if you have difficulties, ask more.

|

Panagiotis Kanavos · Accepted Answer · 2013-06-19 13:37:38Z

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.

Collectives™ on Stack Overflow

How to create Databasecommand in c#

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related