VB.NET DBHELP

DbHelper.vb


Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DbHelper   
    '' Connection to perform database activity
#Region "Open Sqlconnection"
    Public Function openConnection() As SqlConnection
        Dim con As SqlConnection
        con = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
        If (con.State = ConnectionState.Open) Then
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
        End If
        con.Open()
        Return con
    End Function
#End Region
 
#Region "Close Connection"
    Public Function closeConnection(ByVal con As SqlConnection)
        If (con.State = ConnectionState.Open) Then
            con.Close()
        End If
    End Function
#End Region
 
#Region "getDataset"
    Public Function getDataSet(ByVal strSql As String) As DataSet
 
        Dim con As SqlConnection
        Dim sda As SqlDataAdapter
        Dim scb As SqlCommandBuilder
 
        Try
            Dim dtst = New DataSet
            con = New SqlConnection
            con = openConnection()
            Dim cmd = New SqlCommand(strSql, con)
            sda = New SqlDataAdapter(cmd)
            scb = New SqlCommandBuilder(sda)
            sda.Fill(dtst)
            Return dtst
 
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            sda.Dispose()
            scb.Dispose()
            closeConnection(con)
        End Try
 
    End Function
#End Region
 
#Region "getDataTable"
    Public Function getDataTable(ByVal strSql As String) As DataTable
        Dim con As SqlConnection
        Dim sda As SqlDataAdapter
        Dim scb As SqlCommandBuilder
 
        Try
            Dim dtst As New DataSet
            Dim dt As New DataTable
            con = New SqlConnection
            con = openConnection()
            Dim cmd As New SqlCommand(strSql, con)
            sda = New SqlDataAdapter(cmd)
            scb = New SqlCommandBuilder(sda)
            sda.Fill(dtst)
            dt = dtst.Tables(0)
            Return dt
 
        Catch ex As Exception
            Throw New Exception(ex.Message())
        Finally
            sda.Dispose()
            scb.Dispose()
            closeConnection(con)
        End Try
    End Function
#End Region
 
End Class 

 

 

 

 

 

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim objServ As New DbService.Db_Webserv
      Dim retVal As Integer
      retVal = objServ.connectToDB()
      If retVal = 1 Then
          txtMessage.Text = "Connected to database successfully."
          Dim dtst As New DataSet
          dtst = objServ.getProducts()
          dtgrdProducts.DataSource = dtst.Tables(0)
      Else
          txtMessage.Text = "Unable to connect to the database, check the connection information"
      End If
End Sub