Handling Database Connection in C#

Question

I am Creating WinForm application using C# and SqlServer. I have to handle many database CRUD Queries on it. And also there are so many forms and so many controllers.

Now I want to know is, If i create common class for handle database connectivity with many methods for open connection, close connection, execute Sql command or do any other data retrievals. This method is good or bad?

or below method for run every query is good or bad?

using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=MYDB"))  
{  
    connection.Open();        
    // Pool A is created.  
}  

which method is better for performance and security?

Answer 1

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)

Answer 2

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.

Answer 3

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;
}