Simple SQL Connection Class to use for CRUD ops and transactions

Question

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...

Tried:

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

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.

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?

Regards.

Edit:

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

Just one caveat: SqlCommand is disposable, so you'll need to add a using statement. — Rob Lyndon
– Rob Lyndon, Commented Jul 4, 2013 at 8:53

Rob Lyndon · Accepted Answer · 2013-07-04 10:21:52Z

1

I would make the db_Update_Delete_Query() method return your own DbCommandToken, which wraps an SqlTransaction object, and can be used to cancel the transaction or report the completion of the command.

This would look like this.

public class DbConnection
{
    public static DbCommandToken db_Update_Delete_Query(string strQuery)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(strConnectionString))
            {
                connection.Open();
                var transaction = connection.BeginTransaction();
                SqlCommand command = new SqlCommand(strQuery, connection);
                return new DbCommandToken(transaction, command);
            }
        }
        catch (Exception)
        {
            //some error handling.
        }
    }
}

Note that both your command and your transaction are now owned by your DbCommandToken object, which should implement IDisposable.

If your command is a long-running command, which you may want to run asynchronously, and maybe cancel with a later request, you can add success and failure callbacks to your DbCommandToken: something along these lines.

public class DbCommandToken : IDisposable
{
    private readonly SqlTransaction _transaction;
    private readonly SqlCommand _command;

    public DbCommandToken(SqlTransaction transaction, SqlCommand command)
    {
        _transaction = transaction;
        _command = command;
    }

    public Action Success { get; set; }

    public Action Failure { get; set; }

    public Task<int> Execute()
    {
        return Task.Factory.StartNew(() => _command.ExecuteNonQuery())
            .ContinueWith(t =>
                {
                    var rowsAffected = t.Result;
                    if (rowsAffected >= 0)
                    {
                        _transaction.Commit();
                        Success();
                    }

                    ...Handle other scenarios here...

                    return t.Result;
                });
    }

    public void Cancel()
    {
        _transaction.Rollback();
    }

    public void Dispose()
    {
        _command.Dispose();
        _transaction.Dispose();
    }
}

edited Jul 4, 2013 at 10:21

answered Jul 4, 2013 at 8:56

Rob Lyndon

12.7k5 gold badges56 silver badges79 bronze badges

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

5 Comments

Sin5k4 Over a year ago

But if i dont open a transaction,how can it return a value? The point it on what spot should i open the transaction?

Sin5k4 Over a year ago

Cheers.Then the usage would be : String insert2 = @"INSERT INTO BARZO.dbo.Table_1 ( A, B ) VALUES ( N'CCC', -- A - nchar(10) N'DDD' -- B - nchar(10) )"; _dbconnection.db_Update_Delete_Query(insert2).Execute(); right? How can i just call the class and run the execute commands a couple of times,then do the commit?

Rob Lyndon Over a year ago

OK, my bad. I misunderstood what you were asking for. The first question that occurs to me is, do you need the wrapper class at all? Can you just do the whole thing with a SqlConnection that creates transactions using BeginTransaction, and SqlCommand objects that you run on the fly? Is the wrapper class just a way of making that process less verbose?

Rob Lyndon Over a year ago

Nice. The only change I'd suggest is to make command a local variable rather than a member variable, and wrap it in a using statement. Otherwise, the second time you call db_Update_Delete_Query, you'll overwrite your first instance, and that instance won't get disposed.

Rob Lyndon Over a year ago

Oh, and I'd dispose of the transaction before closing the connection in your Dispose() method. Then it looks like you're good to go.

Collectives™ on Stack Overflow

Simple SQL Connection Class to use for CRUD ops and transactions

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related