Opening/closing sql connection - redundant code

Question

I was wondering what is the most basic way to avoid the following.

    con.ConnectionString = connection_String
    con.Open()
    cmd.Connection = con

    'database interaction here

    cmd.Close()

I keep making those lines all over in my project, but I figure there has to be a better way to save on typing this over and over. It makes the code look even more sloppy than it already is!

Ended up with this, works well for me. Thanks for the help :)

Public Sub connectionState()
    If con.State = 0 Then
        con.ConnectionString = connection_String
        con.Open()
        cmd.Connection = con
    Else
        con.Close()
    End If
End Sub

Answer 1

This is where a lot of programmers are tempted to create a "database layer" with a variations on method signatures that look like this:

Public DataSet ExecuteSQL(ByVal sql As String) As DataSet

That allows you to isolate all that boilerplate connection code in one place. An sql command string goes in, and data comes out. Easy.

Don't do it!

This is headed in the right direction, but has one very big flaw: it forces you to use string manipulation to substitute parameter values into your sql queries. That leads to horrible sql injection security vulnerabilities.

Instead, make sure you include some mechanism in your methods to prompt for the sql parameters separately. This usually comes in the form of an additional argument to the function, and could be as simple as an array of KeyValuePairs. If you're comfortable with lambdas, my preferred pattern looks like this:

Public Iterator Function GetData(Of T)(ByVal sql As String, ByVal addParameters As Action(Of SqlParameterCollection), ByVal translate As Func(Of IDatarecord, T)) As IEnumerable(Of T)
    Using cn As New SqlConnection("connection string"), _
          cmd As New SqlCommand(sql, cn)

        addParameters(cmd.Parameters)

        cn.Open()
        Using rdr As SqlDataReader = cmd.ExecuteReader()
            While rdr.Read()
                Yield(translate(rdr))
            End While
        End Using
    End Using
End Function

To call that function, you would do something like this:

Dim bigCustomers = GetData("SELECT * FROM Customers WHERE SalesTotal > @MinSalesTotal", _
                   Sub(p) p.Add("@MinSalesTotal", SqlDbType.Decimal, 1000000), _
                   MyCustomerClass.FromIDataRecord)

Answer 2

You can try creating a class ( a singleton class ), and write the database connection syntax code and exceptions in that class, then call one object to the main class to create the database connection, that's the best way in performance and keep writing the same code on and on...