Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class SQLDataBase
    Inherits MarshalByRefObject
    Enum SQLVariant
        SqlSelect = 1
        SqlUpLoad = 2
        SqlDelete = 3
    End Enum
    Dim strSelect As String
    Dim strConnetction As String = "Server=.\SQLExpress;database=test;Trusted_Connection=yes;"
    Dim MyConnection As New SqlConnection(strConnetction)
    Dim MySqlDataAdapter As New SqlDataAdapter()
    Dim MySqlCommandBuilder As New SqlCommandBuilder()
    Dim WorkDataSet As Data.DataSet
    Public Function SqlMethods(ByVal [SqlVariant] As SQLVariant, ByRef [DataSet] As Data.DataSet, ByVal SelectString As String, Optional ByVal strSelectNewID As String = "") As Integer
        Dim result As Integer = 0
        strSelect = SelectString
        WorkDataSet = [DataSet]
        MySqlDataAdapter = New SqlDataAdapter(strSelect, MyConnection)
        MySqlCommandBuilder.DataAdapter = MySqlDataAdapter
        MySqlCommandBuilder.ConflictOption = Data.ConflictOption.OverwriteChanges
        If [SqlVariant] = SQLDataBase.SQLVariant.SqlSelect Then
            SqlMethodSelect()
            [DataSet] = WorkDataSet
            Return result
        End If
        MySqlDataAdapter.UpdateCommand = MySqlCommandBuilder.GetUpdateCommand(True)
        MySqlDataAdapter.InsertCommand = MySqlCommandBuilder.GetInsertCommand(True)
        MySqlDataAdapter.DeleteCommand = MySqlCommandBuilder.GetDeleteCommand(True)
        Select Case [SqlVariant]
            Case SQLDataBase.SQLVariant.SqlDelete
                Dim i As Integer
                SqlMethodSelect()
                If WorkDataSet Is Nothing Then
                    result = -1
                    [DataSet] = WorkDataSet
                    Return result
                End If
                For i = 0 To WorkDataSet.Tables(0).Rows.Count - 1
                    WorkDataSet.Tables(0).Rows(i).Delete()
                Next
                SqlMethodUpLoad()
            Case SQLDataBase.SQLVariant.SqlUpLoad
                SqlMethodUpLoad()
                [DataSet] = New Data.DataSet
                SqlMethods(SQLDataBase.SQLVariant.SqlSelect, [DataSet], strSelectNewID)
                result = WorkDataSet.Tables(0).Rows(0)(0)
        End Select
        [DataSet] = WorkDataSet
        Return result
    End Function
    Private Sub SqlMethodSelect()
        Try
            MyConnection.Open()
        Catch ex As Exception
        End Try
        Try
            MySqlDataAdapter.Fill(WorkDataSet)
            MyConnection.Close()
        Catch ex As Exception
            WorkDataSet = Nothing
            MyConnection.Close()
        End Try
    End Sub
    Private Sub SqlMethodUpLoad()
        Try
            MyConnection.Open()
        Catch ex As Exception
        End Try
        Try
            MySqlDataAdapter.Update(WorkDataSet)
            MyConnection.Close()
        Catch ex As Exception
            WorkDataSet = Nothing
            MyConnection.Close()
        End Try
    End Sub
End Class

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class TestTable
Public Function TestMethods(ByVal [SqlVariant] As SQLDataBase.SQLVariant, Optional ByRef [DataSet] As Data.DataSet = Nothing, Optional ByVal strKey As String = "NULL") As Data.DataSet
Dim TableName As String
Dim RecordIDFieldName As String
Dim ID As Integer
TableName = "Test1"
RecordIDFieldName = "lngTestID"
ID = doSQl([SqlVariant], [DataSet], TableName, RecordIDFieldName, strKey)
Return [DataSet]
End Function
Public Sub getTestSum(Optional ByRef [DataSet] As Data.DataSet = Nothing, Optional ByVal strKey As String = "NULL")
Dim strSelect As String
strSelect = "select count(*) from test1"
If strKey <> "NULL" Then
strSelect = strSelect + " WHERE lngTestID=" & strKey
End If
doSQlStatment([DataSet], strSelect)
End Sub
Private Function doSQl(ByVal [SqlVariant] As SQLDataBase.SQLVariant, ByRef [DataSet] As Data.DataSet, ByVal TableName As String, ByVal RecoredIDFieldName As String, Optional ByVal strKey As String = "NULL") As Integer
Dim a As New SQLDataBase
Dim strSelect As String
Dim selectNewId As String
strSelect = "select * from " & TableName
selectNewId = "SELECT MAX(" & RecoredIDFieldName & ") FROM " & TableName
If strKey <> "NULL" Then
strSelect = strSelect + " WHERE " & RecoredIDFieldName & "=" & strKey
End If
If [DataSet] Is Nothing Then
[DataSet] = New Data.DataSet
End If
Return a.SqlMethods([SqlVariant], [DataSet], strSelect, selectNewId)
End Function
Private Function doSQlStatment(ByRef [DataSet] As Data.DataSet, ByVal strSelect As String) As Integer
Dim a As New SQLDataBase
If [DataSet] Is Nothing Then
[DataSet] = New Data.DataSet
End If
Return a.SqlMethods(SQLDataBase.SQLVariant.SqlSelect, [DataSet], strSelect)
End Function
End Class