Handling Database Connection in C#

Here are some points to think about when using a connection.

1) Dispose the connection object as soon as you no longer need it by using the using statement:

using (var conn = new SqlConnection(connectionstring))  
{  
    // your sql magic goes here
} 

2) If you're not disposing the object immediately, you can make sure the connection is closed using a try-finally statement:

var conn = new SqlConnection(connectionstring);
try
{
// do sql shizzle
}
finally
{
    conn.Close();
}           

3) To prevent SQL injection, use parameterized queries, never concatenated strings

using (var conn = new SqlConnection(connectionstring))  
{  
    conn.Open();
    using(var comm = new SqlCommand("select * from FooBar where foo = @foo", conn))
    {
        comm.Parameters.Add(new SqlParameter("@foo", "bar"));
        // also possible:
        // comm.Parameters.AddWithValue("@foo", "bar");
        using(var reader = comm.ExecuteReader())
        {
            // Do stuff with the reader;
        }
    }
} 

4) If you're performing multiple update, insert or delete statements, and they all need to be succesful at once, use a transaction:

using (var conn = new SqlConnection(connectionstring))  
{  
    conn.Open();
    using(var trans = conn.BeginTransaction())
    {
        try 
        {
            using(var comm = new SqlCommand("delete from FooBar where fooId = @foo", conn, trans))
            {
                comm.Parameters.Add(new SqlParameter { ParameterName = "@foo", DbType = System.Data.DbType.Int32 });
                for(int i = 0; i < 10 ; i++)
                {
                    comm.Parameters["@foo"].Value = i;
                    comm.ExecuteNonQuery();
                }
            }
            trans.Commit();
        }
        catch (Exception exe)
        {
            trans.Rollback();
            // do some logging
        }
    }
} 

5) Stored procedures are used similarly:

using (var conn = new SqlConnection(connectionstring))
{
    conn.Open();
    using (var comm = new SqlCommand("FooBarProcedure", conn) { CommandType = CommandType.StoredProcedure }) 
    {
         comm.Parameters.Add(new SqlParameter("@FooBar", "shizzle"));
         comm.ExecuteNonQuery();
    }
}

(Source stored procedures: this Answer)

Multi threading: The safest way to use multi threading and SQL connections is to always close and dispose your connection object. It's the behavior the SqlConnection was designed for. (Source: Answer John Skeet)

Best practice is make a common DBHelper class and create CRUD methods into that class. I am adding code snippet.This may help you.

web.config

<connectionStrings>
    <add name="mssqltips"
         connectionString="data source=localhost;initial catalog=mssqltips;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

DBHelper.cs

//Opening Connection
public SqlConnection GetConnection(string connectionName)
{
  string cnstr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
  SqlConnection cn = new SqlConnection(cnstr);
  cn.Open();
  return cn;
}
//for select 
public DataSet ExecuteQuery(
  string connectionName,
  string storedProcName,
  Dictionary<string, sqlparameter=""> procParameters
)
{
  DataSet ds = new DataSet();
  using(SqlConnection cn = GetConnection(connectionName))
  {
      using(SqlCommand cmd = cn.CreateCommand())
      {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = storedProcName;
          // assign parameters passed in to the command
          foreach (var procParameter in procParameters)
          {
            cmd.Parameters.Add(procParameter.Value);
          }
          using (SqlDataAdapter da = new SqlDataAdapter(cmd))
          {
            da.Fill(ds);
          }
      }
 }
 return ds;
}

//for insert,update,delete

public int ExecuteCommand(
  string connectionName,
  string storedProcName,
  Dictionary<string, SqlParameter> procParameters
)
{
  int rc;
  using (SqlConnection cn = GetConnection(connectionName))
  {
    // create a SQL command to execute the stored procedure
    using (SqlCommand cmd = cn.CreateCommand())
    {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = storedProcName;
      // assign parameters passed in to the command
      foreach (var procParameter in procParameters)
      {
        cmd.Parameters.Add(procParameter.Value);
      }
      rc = cmd.ExecuteNonQuery();
    }
  }
  return rc;
}

If you do not want to dispose context every time you can create repository class and inject SqlConnection inside.

using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=MYDB"))  
{  
    repository.SetConnection(connection);
    var values = repository.GetSomething();
} 

And Create Class:

public Class Repository
{
   private SqlConnection _connection {get; set;}

   public void SetConnection(SetConnection connection)
   {
        _connection = connection;
   }

   public string GetSomething()
   {
       _connection.Open();
       //do stuff with _connection
       _connection.Close();
   }
}

Anyway I recommend you to read about ORM's (Entity Framework or Dapper) and SQL injection attack.