Sharing: A Function To Globally Run SQL Queries

Hey,

I've been noticing alot of people here asking questions about using sql and so on so I just wanted to share a function I built to run my sql queries.

You'll notice that i'm connecting to gstrConnectionString within my connection string. You would either within your Web.config file declare the gstrConnectionString or within a module.

The below function I've placed within a module so that i dont have to access an object to call it each time.

Code:

---


'How To call this function
Dim objRs as SqlDataReader
objRs = RunSQL("Select * FROM tblUsers")

'Notice that the 'intExecType is optional and set to 1 where if it's a 1
'we are returning the reader back


Public Function RunSQL(ByVal strSQL As String, Optional ByVal intExecType As Int16 = 1) As SqlDataReader
        Dim oCnn As New SqlConnection
        Dim oCommand As New SqlCommand
        Dim oRS As SqlDataReader

        With oCnn
            .ConnectionString = gstrConnectionString
            .Open()
        End With

        If intExecType = 1 Then  'Run and return ors object
            With oCommand
                .CommandType = CommandType.Text
                .Connection = oCnn
                .CommandText = strSQL
                Return .ExecuteReader
            End With
        Else
            With oCommand
                .Connection = oCnn
                .CommandText = strSQL
                .CommandType = CommandType.Text
                .ExecuteNonQuery()
            End With
        End If

        oCnn.Close()
        oRS.Close()

        oCnn = Nothing
        oCommand = Nothing
        oRS = Nothing

    End Function

---