The Simplest CRUD Operations for Lookup Tables in ASP.NET - The SQLHelper

(Page 2 of 7 )

This is a simple encapsulated class I added for my convenience. It mainly handles database related communications. Actually, you can extend it further according to your requirements.

The first method inside the SQLHelper class is as follows:

    Public Overloads Sub SQLExecute(ByVal strSQL As String)
        Dim ConnAs SqlConnection
        Dim cmd As SqlCommand
        Try
            Conn= New SqlConnection(_ConnectionString)
            cmd = New SqlCommand(strSQL, Conn)
            With cmd
                .Connection.Open()
                .ExecuteNonQuery()
                .Connection.Close()
                .Dispose()
            End With

        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open
Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& strSQL)
        End Try
    End Sub

The above method will simply try to execute any SQL statement (except SELECT).  This can be generally used for any DML or DDL operations.  Proceeding further, we have the following:

  PublicFunction getDataTable(ByVal sqlSELECT As String) As
System.Data.DataTable
        Dim ConnAs SqlConnection
        Dim da As SqlDataAdapter
        Try
            Conn= New SqlConnection(_ConnectionString)
            Dim dt As New DataTable

            da = New SqlDataAdapter(sqlSELECT, Conn)
            da.Fill(dt)
            da.Dispose()
            Return dt

        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message & ". SQL Statement: "
& sqlSELECT)
        End Try
    End Function

The above is mainly used to retrieve a set of rows from the database.

Next: The SQLHelper: continued >>
 

More ASP.NET Articles
More By Jagadish Chaterjee

Comments On: The Simplest CRUD Operations for Lookup Tables in ASP.NET

· why wouldn't you use a tableadapter?or the MS Enterprise Library?    · ofcourse, I would have used. But, I didn't mean to use enterprise library in this...   >>> Post your comment now!