The proper way to connect to a database using C#

Question

I'm currently working with an application connected to a local SQL Server database on my machine. I have a number of SQL queries currently executing fine using different methods. My question is regarding the Opening/closing of the database connection between the different methods.

I have 2 methods looking something like this:

Class MyClass
{
    string connectionString = "myConnectionString";

    public void Method1()
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        string sqlStr = "my SQL query";
        SqlCommand com = new SqlCommand(sqlStr, con);
        com.ExecuteNonQuery();
        con.Close();
    }

    public void Method2()
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        string sqlStr = "my SQL query";
        SqlCommand com = new SqlCommand(sqlStr, con);
        com.ExecuteNonQuery();
        con.Close();
    }
}

If I call these methods they work fine, no exceptions. But is this the proper way of handling database connections? Could I for example use a static connection that is initialized as soon as MyClass gets initialized? Like this

Class MyClass
{
    string connectionString = "myConnectionString";
    SqlConnection con = new SqlConnection(connectionString);
    con.Open();

    public void Method1()
    {
        ...
    }
    etc.

or is there a "better" way to handle database connections?

I'm thankful for any input.

Dmitrii Bychenko · Accepted Answer · 2014-01-30 18:04:04Z

3

You'd rather put using whenever you work with IDisposable instances:

public void Method3() {
  string sqlStr = "my SQL query";

  // Do not forget to configure connection pull so that
  // establishing a connection will not be expensive 
  using (SqlConnection con = new SqlConnection(connectionString)) {
    con.Open();

    using (SqlCommand com = new SqlCommand(sqlStr, con)) {
      com.ExecuteNonQuery();
    }
  }
}

You can combine queries if you like:

    public void Method4() {
      string sqlStr1 = "my SQL query 1";
      string sqlStr1 = "my SQL query 2";

      // Do not forget to configure connection pull so that
      // establishing a connection will not be expensive 
      using (SqlConnection con = new SqlConnection(connectionString)) {
        con.Open();

        // Think on having both queries executed in one transaction
        using (SqlCommand com1 = new SqlCommand(sqlStr1, con)) {
          com1.ExecuteNonQuery();
        }

        using (SqlCommand com2 = new SqlCommand(sqlStr2, con)) {
          com2.ExecuteNonQuery();
        } 
      }
    }

Static connections could be very hard to maintain, esp. if you are implementing multithreading software, that's why you should avoid using them

edited Jan 30, 2014 at 18:04

answered Jan 30, 2014 at 11:22

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

2 Comments

Marcus Over a year ago

@Dmitry Bychenko am I supposed to close the connection after using (....) { .... } ?

Dmitrii Bychenko Over a year ago

@Marcus: No; using(...) {...} will do it for you.

bdn02 · Accepted Answer · 2014-01-30 12:08:36Z

0

Don't use a static connection, check the database connectionstring to enable the connection pooling. Is the connection pooling that controls the connection, leave it open until timeout (improve the performance) and close it when you don't need it. Use the 'using' clause for every disposable object!

answered Jan 30, 2014 at 12:08

bdn02

1,5109 silver badges15 bronze badges

Collectives™ on Stack Overflow

The proper way to connect to a database using C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related