Wednesday, 6 May 2015

Database Class in vb.net (db class) sql connecton class in vb.net with Mysql and sql database

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports MySql.Data.MySqlClient
Public Class db

    Public EmailExtraction As String = ConfigurationSettings.AppSettings("Eamilconnection")
    Public localconnection As String = ConfigurationSettings.AppSettings("connection")
    Public localconnection1 As String = ConfigurationSettings.AppSettings("connection1")
    Public amanconnection As String = ConfigurationSettings.AppSettings("amanconnection")
    Public anticonnection As String = ConfigurationSettings.AppSettings("anticonnection")
    Public Function execute(ByVal myqry As String, ByVal executeconnection As String) As Boolean  'insert into database
        On Error GoTo err
        If executeconnection.Contains("database = ugc") = True Then
            Dim appcon As SqlConnection = New SqlConnection(executeconnection)
            Dim qrycmd As SqlCommand = New SqlCommand
            appcon.Open()
            qrycmd.Connection = appcon
            qrycmd.CommandText = myqry
            qrycmd.CommandTimeout = 0
            qrycmd.ExecuteNonQuery()
            appcon.Close()
            Return True
        Else
            Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
            Dim MySqlCommand As MySqlCommand = New MySqlCommand
            MySQLConnection.Open()
            MySqlCommand.Connection = MySQLConnection
            MySqlCommand.CommandText = myqry
            MySqlCommand.CommandTimeout = 0
            MySqlCommand.ExecuteNonQuery()
            MySQLConnection.Close()
            Return True
        End If
err:
        Resume
    End Function
    Public Function getdata(ByVal myqry As String, ByVal executeconnection As String) As DataTable

        On Error GoTo err
        If executeconnection.Contains("database = ugc") = True Then

            Dim dt As DataTable = New DataTable
            Dim dataset As DataSet = New DataSet
            Dim appcon As SqlConnection = New SqlConnection(executeconnection)
            appcon.Open()
            Dim myadapter As SqlDataAdapter = New SqlDataAdapter
            myadapter.SelectCommand = New SqlCommand(myqry, appcon)
            myadapter.SelectCommand.CommandTimeout = 0
            myadapter.Fill(dataset)
            dt = dataset.Tables(0)
            appcon.Close()
            Return dt
            Exit Function

        Else

            Dim dtdatabase As DataTable = New DataTable()
            Dim dataset As DataSet = New DataSet()
            Dim MySQLConnection As MySqlConnection = New MySqlConnection(executeconnection)
            MySQLConnection.Open()
            Dim MyAdp As MySqlDataAdapter = New MySqlDataAdapter()
            MyAdp.SelectCommand = New MySqlCommand(myqry, MySQLConnection)
            MyAdp.SelectCommand.CommandTimeout = 0
            MyAdp.Fill(dataset)
            dtdatabase = dataset.Tables(0)
            MySQLConnection.Close()
            Return dtdatabase
        End If

err:
        Resume
    End Function
    Public Function executeodbc(ByVal myqry As String, ByVal executeconnection As String) As Boolean  'insert into database
        Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
        Dim qrycmd As OdbcCommand = New OdbcCommand
        appcon.Open()
        qrycmd.Connection = appcon
        qrycmd.CommandText = myqry
        qrycmd.CommandTimeout = 0
        qrycmd.ExecuteNonQuery()
        appcon.Close()
        Return True
    End Function
    Public Function getdataodbc(ByVal myqry As String, ByVal executeconnection As String) As DataTable
        Dim dt As DataTable = New DataTable
        Dim dataset As DataSet = New DataSet
        Dim appcon As OdbcConnection = New OdbcConnection(executeconnection)
        appcon.Open()
        Dim myadapter As OdbcDataAdapter = New OdbcDataAdapter
        myadapter.SelectCommand = New OdbcCommand(myqry, appcon)
        myadapter.SelectCommand.CommandTimeout = 0
        myadapter.Fill(dataset)
        dt = dataset.Tables(0)
        appcon.Close()
        Return dt
    End Function
End Class