dbconn

Imports System.Data.Odbc
Imports System.Data
Imports System.IO

Public Class dbconn
    Public gs_ConnDB As OdbcConnection
    Public objCnf As ConfigurationManager
    Dim objOdbcDataReader As OdbcDataReader
    Dim lsobjDR As OdbcDataReader
    Public trans As OdbcTransaction

    Public Sub OpenConn()
        gs_ConnDB = New OdbcConnection(ConfigurationManager.AppSettings("ConnectionString").ToString)
        If gs_ConnDB.State = ConnectionState.Closed Then
            gs_ConnDB.Open()
        End If
    End Sub
    Public Sub OpenConnRCS()
        gs_ConnDB = New OdbcConnection(ConfigurationManager.AppSettings("ConnectionStringRCS").ToString)
        If gs_ConnDB.State = ConnectionState.Closed Then
            gs_ConnDB.Open()
        End If
    End Sub
    Public Function BeginTransaction()
        trans = gs_ConnDB.BeginTransaction
        Return trans
    End Function
    Public Sub CommitTransaction()
        trans.Commit()
    End Sub
    Public Sub RollBackTransaction()
        trans.Rollback()
    End Sub
    Public Function GetDataReader(ByVal SQL As String) As OdbcDataReader
        Dim cmdQuery As New OdbcCommand
        'Dim lsobjDR As OdbcDataReader
        cmdQuery.Connection = gs_ConnDB
        cmdQuery.CommandText = SQL
        cmdQuery.CommandType = CommandType.Text
        lsobjDR = cmdQuery.ExecuteReader
        Return lsobjDR
    End Function
    Public Function GetDataSet(ByVal SQL As String, ByVal tblName As String) As DataSet
        'This function will Retrieve Data and Return as Dataset together with table name
        Dim lobjDataAdapter As New OdbcDataAdapter(SQL, gs_ConnDB)
        Dim lobjDataSet As New DataSet
        lobjDataAdapter.Fill(lobjDataSet, tblName)
        Return lobjDataSet
    End Function

    Public Function ExecuteNonQuerySQL(ByVal SQL As String) As Integer
        Dim cmdQuery As New OdbcCommand
        Dim mnResult As Integer
        cmdQuery.Connection = gs_ConnDB
        cmdQuery.CommandText = SQL
        cmdQuery.CommandType = CommandType.Text
        Try
            mnResult = cmdQuery.ExecuteNonQuery
            mnResult = 1
        Catch ex As OdbcException
            mnResult = 0
        End Try
        cmdQuery.Dispose()
        Return mnResult
    End Function

    Public Function ExecuteNonQuerySP(ByVal pSPName As String, ByVal pVal As String) As Integer
        Dim cmdQuery As New OdbcCommand
        Dim parmReturnValue As OdbcParameter
        Dim arrValue As String()
        Dim lstValue As String
        Dim arrNameValue As String()
        Dim mnResult As Integer

        arrValue = Split(pVal, ",")

        cmdQuery = New OdbcCommand(pSPName, gs_ConnDB)
        cmdQuery.CommandType = CommandType.StoredProcedure

        For Each lstValue In arrValue
            arrNameValue = Split(lstValue, "|")
            cmdQuery.Parameters.AddWithValue("@" & arrNameValue(0), arrNameValue(1))
        Next

        parmReturnValue = cmdQuery.Parameters.AddWithValue("ReturnValue", SqlDbType.Int)
        parmReturnValue.Direction = ParameterDirection.ReturnValue

        cmdQuery.ExecuteNonQuery()
        mnResult = cmdQuery.Parameters("ReturnValue").Value

        cmdQuery.Dispose()
        Return mnResult
    End Function
    Public Sub CloseConn()
        gs_ConnDB.Close()
    End Sub
    Public Sub popdata(ByVal sql As String, ByVal TableName As String, ByVal Grid As DataGrid, ByVal lblErrMsg As Label)
        Dim ds_tuser As New DataSet
        Dim objcmnfunctions As New cmnfunctions
        ds_tuser = GetDataSet(sql, TableName)
        If ds_tuser.Tables(TableName).Rows.Count > 0 Then
            Grid.Visible = True
            Grid.DataSource = ds_tuser
            Grid.DataBind()
        Else
            Grid.Visible = False
            lblErrMsg.Text = objcmnfunctions.GetErrMsg("ET_010")
        End If
        ds_tuser.Dispose()
    End Sub
    Public Function GetExecuteScalar(ByVal SQL As String) As String
        GetExecuteScalar = ""
        Dim lobjCommand As New OdbcCommand
        Try
            lobjCommand.Connection = gs_ConnDB
            lobjCommand.CommandText = SQL
            lobjCommand.CommandType = CommandType.Text
            GetExecuteScalar = lobjCommand.ExecuteScalar
            Return GetExecuteScalar
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        lobjCommand.Dispose()
        lobjCommand = Nothing
    End Function
End Class