CODEWITHSUNDEEP
c#sql-serverdatabase-designconnectiondatabase-connection
Marcus
Marcus

Reputation: 6717

The proper way to connect to a database using C#

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.

Upvotes: 0

Views: 2215

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186833

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

Upvotes: 3

bdn02
bdn02

Reputation: 1500

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!

Upvotes: 0

Related Questions