Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Module MoDatabase
    Public ConnectionString As String = _
     "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & ConfigurationSettings.AppSettings.Item("uid") _
                    & ";Password=" & ConfigurationSettings.AppSettings.Item("pwd") _
                    & ";Initial Catalog=" & ConfigurationSettings.AppSettings.Item("db") _
                    & ";Data Source='" & ConfigurationSettings.AppSettings.Item("server") & "'"

    Public Function getDataset(ByVal cmd As String) As DataSet

        Dim connection As OleDbConnection = Nothing

        Dim command As OleDbCommand = Nothing

        Dim sqlda As OleDbDataAdapter = Nothing

        Dim res As New DataSet
        Try

            connection = New OleDbConnection(ConnectionString)

            command = New OleDbCommand(cmd, connection)

            command.CommandType = CommandType.Text

            sqlda = New OleDbDataAdapter(command)

            sqlda.Fill(res)

        Catch ex As Exception

            MessageBox.Show(ex.Message, "Error ", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Return res
    End Function


    Public Function ExecuteNonQuery(ByVal cmd As String) As Integer

        Dim connection As OleDbConnection = Nothing

        Dim transaction As OleDbTransaction = Nothing

        Dim command As OleDbCommand = Nothing
        Dim res As Integer = -1
        Try

            connection = New OleDbConnection(ConnectionString)

            command = New OleDbCommand(cmd, connection)

            command.CommandType = CommandType.Text

            connection.Open()

            transaction = connection.BeginTransaction()
            command.Transaction = transaction

            res = command.ExecuteNonQuery()

            transaction.Commit()

        Catch ex As Exception

            If Not (transaction Is Nothing) Then
                transaction.Rollback()
            End If
            MessageBox.Show(ex.Message, "Error ", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Return res
    End Function
End Module