title: Simple SQL Connection Class to use for CRUD ops and transactions
body: <p>I have been trying to write an sql connection class for a small project,which will use simple sql commands for insert/update/select/delete ops and sometimes with transactions,is there any guidelines i could use? The class could be instantiated at any point with or without transactions...</p>
  
  <p>Tried:</p>
  
  <pre><code>public class DbConnection
  
  {
      public static string srConnectionString = "blablab";
  
  public DbConnection()
  {
  
  }
  
  public static DataSet db_Select_Query(string strQuery)
  {
      DataSet dataSet = new DataSet();
  
      try
      {
          using (SqlConnection connection = new SqlConnection(srConnectionString))
          {
              connection.Open();
              SqlDataAdapter _SqlDataAdapter = new SqlDataAdapter(strQuery, connection);
              DA.Fill(dataSet);
          }
          return dataSet;
      }
  
      catch (Exception)
      {
           //some error handling.
      }
  }
  
  public static void db_Update_Delete_Query(string strQuery)
  {
      try
      {
          using (SqlConnection connection = new SqlConnection(srConnectionString))
          {
              connection.Open();
              SqlCommand command = new SqlCommand(strQuery, connection);
              command.ExecuteNonQuery();
          }
      }
      catch (Exception)
      {
           //some error handling.
      }
  }
  </code></pre>
  
  <p>For example how can i add a parameter that opens or closes transaction as long as this class is used?For example i could be able to call db.commit or db.rollback from outside the class.</p>
  
  <p>PS:tried some micro orms(petapoco for ex.) ,is there a way to run them with pure sql and get return type as datasets or datatables only?</p>
  
  <p>Regards.</p>
  
  <p>Edit:</p>
  
  <pre><code>  public class dbconnection : IDisposable
  {
      public static string strConnectionString = @"Data Source=PC137\PC137_SQL2008;Initial Catalog=BARZO;Integrated Security=True";
  
      #region IDisposable Members
  
      public void Dispose()
      {
          GC.SuppressFinalize(this);
      }
  
      #endregion
  
      private SqlTransaction transaction;
      private SqlConnection connection;
      private SqlCommand command = new SqlCommand();
  
  
      public void db_OpenConnection(bool WithTransaction)
      {
          connection = new SqlConnection(strConnectionString);
          connection.Open();
  
          if (WithTransaction)
          {
              transaction = connection.BeginTransaction();
          }
      }
  
  
      public void db_CloseConnection()
      {
          connection.Close();
          connection.Dispose();
          transaction.Dispose();
          command.Dispose();
      }
  
  
      public void db_Commit()
      {
          transaction.Commit();
      }
  
  
      public void db_RollBack()
      {
          transaction.Rollback();
      }
  
  
      public DataSet db_Select_Query(string strQuery)
      {
          var dataSet = new DataSet();
  
          try
          {
              SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(strQuery, connection);
              SqlDataAdapter.Fill(dataSet);
              return dataSet;
          }
  
          catch (SqlException sqlError)
          {
              MessageBox.Show(sqlError,MessageBoxButtons.OK, MessageBoxIcon.Stop);
          }
      }
  
      public bool db_Update_Delete_Query(string strQuery)
      {
          try
          {
              command = new SqlCommand(strQuery, connection);
              command.Transaction = transaction;
              command.ExecuteNonQuery();
          }
          catch (SqlException sqlError)
          {
              MessageBox.Show(sqlError,MessageBoxButtons.OK, MessageBoxIcon.Stop);
              return false;
          }
          return true;
      }
  }
  </code></pre>