Creating a wrapper for SqlClient

I'm trying to find out what is the best way to create a class so I can use it to connect to the SQL Server and be able to rollback everything if a single error exists.

Right now, I'm using a single connection for a procedure. This, sometimes, is causing my code to throw There is already an open DataReader associated with this Command which must be closed first.

I thought of setting up a new connection for every transaction but I don't know if I can rollback everything if I do it that way.

My current "way", if a single error exists, I call sBDCloseConnect(True) and it does a rollback and closes the connection. This is useful because I usually do a lot iteration with multiple sql queries.

Below is the relevant code I use to do this and I would like to know if there is a better way to do this and avoid the error thrown I mentioned previously.

One last note, I don't want a solution like MARS.

Test case

'' Initialize connection to database
sBDInitConnect()
Dim test1 As Integer = Val(sBDReturnQuery("SELECT 1"))
If test1  1 Then
    Using dt As New DataTable
        Dim test2 As Integer = sBDSelectSQL("SELECT name FROM users WHERE idUser=1", dt)
        If test2  0 Then
            If Not sBDNonQuery("UPDATE users SET name='only a test' WHERE idUser=1") Then
                sBDCloseConnect(True)
            Else
                If Not sBDNonQuery("UPDATE users SET name2='this field does not exist' WHERE idUser=1") Then
                    sBDCloseConnect(True)
                End If
            End If
        End If
    End Using
End If
'' No error
sBDCloseConnect(False)

Class

```
Public Partial Class componenteDEFAULT
Inherits System.Web.UI.UserControl

Private bdConection As Data.SqlClient.sqlConnection
Private bdTransaction As Data.SqlClient.sqlTransaction
Public bdCommand As Data.SqlClient.SqlCommand

Sub sBDInitConnect()
Try : bdConection.Close() : Catch : End Try
Try
bdConection = Nothing

I read the Text portion of your question and it really sounds like you are trying to do the database's work for it, which is always inefficient, if possible DON'T. Especially when it comes down to rolling back transactions.

in the case where you need to do a bunch of different queries and rollback if something goes wrong, create a stored procedure that calls all these queries.

Use the SQL Database for what it was meant to be used for.

SQL Server is good at querying data and performing operations on data, and it has ways of catching errors and rolling back the entire thing. Go do that, it will be much easier in the long run.

StackExchange Code Review Q#90181, answer score: 2