1

I have an ASP.NET web application which is connected to SQL Server.

I have used three connections for each SQL operation. It works very well, however I think this is not an efficient way to do things - can this be written better than it is?

public partial class Home : System.Web.UI.Page
{
    SqlConnection co = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString);
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString);
    SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString);

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (co)
        {
            co.Open();
            SqlCommand cm = co.CreateCommand();
            cm.CommandText = "select...";
            cm.ExecuteNonQuery();
        }
        co.Close();

        using (con)
        {
            con.Open();
            SqlCommand cmv = con.CreateCommand();
            cmv.CommandText = "insert...";

            cmv.ExecuteNonQuery();
        }
        con.Close();

        using (con2)
        {
            con2.Open();
            SqlCommand cmf = con2.CreateCommand();
            cmf.CommandText = "delete from...";
            cmf.ExecuteNonQuery();
        }
        con2.Close();
    }
}
3
  • you can change the command text and execute again as many times as you want within one connection Commented May 19, 2016 at 10:43
  • What you are trying to acheive ? Commented May 19, 2016 at 10:49
  • No harm doing so but also no need to. Use one connection. Commented May 19, 2016 at 11:02

5 Answers 5

2
     SqlConnection co = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString);

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (co)
            {
                co.Open();
                SqlCommand cm = co.CreateCommand();
                cm.CommandText = "select...";
                cm.CommandText += " insert..."; 
                cm.CommandText += " delete from...";
                cm.ExecuteNonQuery();
            }
            co.Close();              
        }

you can use like this.

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

Comments

2

You're using the same connection string for each connection? Why do you need three connections? Why not just open and close the same one?

As long as the connection string is the same, you only need one connection.

Comments

2

In general you should prefer to create and open a connection object as close to where you make use of it as possible, and dispose of it as soon as possible afterwards (preferably by making use of a using statement). Connection pooling will take care of ensuring you only actually create a limited number of real connections to the server, despite the large number of SqlConnection objects your code may seem to create.

Within a single method, however, it is reasonable to use a single connection object:

public partial class Home : System.Web.UI.Page
{
    string connString = ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString;

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection co = new SqlConnection(connString))
        {
            co.Open();
            using(SqlCommand cm = co.CreateCommand())
            {
              cm.CommandText = "select...";
              cm.ExecuteNonQuery();
            }

            using(SqlCommand cmv = co.CreateCommand())
            {
              cmv.CommandText = "insert...";
              cmv.ExecuteNonQuery();
            }

            using(SqlCommand cmf = co.CreateCommand())
            {
              cmf.CommandText = "delete from...";
              cmf.ExecuteNonQuery();
            }
        }
    }
}

(You don't need to explicitly close the connection object, the Dispose (within the using is equivalent)

Comments

2
  1. No use declaring/creating multiple connections when you would be using only one at a time. You can do with just one.
  2. Declare variable as close as possible to its first use, and with minimum scope manageable.
  3. Make things modular and reusable as far as possible.
  4. No need to explicitly close the connection, since the IDisposable interface implementation (and using block) does it anyways. But there is no harm in explicitly closing it.

protected void Button1_Click(object sender, EventArgs e)
{
    ExecuteNonQuery("select...", null);         // why??
    ExecuteNonQuery("insert...", null);
    ExecuteNonQuery("delete from...", null);
}

protected void ExecuteNonQuery(string query, SqlParameter[] parameters)
{
    using (SqlConnection co = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString))
    {
        co.Open();
        SqlCommand cm = co.CreateCommand();
        cm.CommandText = query;
        if (parameters != null) cm.Parameters.AddRange(parameters);
        cm.ExecuteNonQuery();
    }
}

Comments

1

You can also try this.

        SqlConnection co = new SqlConnection(ConfigurationManager.ConnectionStrings["TextConnectionString"].ConnectionString);

        protected void Button1_Click(object sender, EventArgs e)
        {
           StringBuilder sb = new StringBuilder();
           sb.AppendLine(" Select statement.. ");
           sb.AppendLine(" Insert statement ");
           sb.AppendLine(" delete statement ");
            using (co)
            {
                co.Open();
                SqlCommand cm = co.CreateCommand();
                cm.CommandText = sb.Tostring();
                cm.ExecuteNonQuery();
            }
            co.Close();              
        }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.