数据库开发032数据库通用Connection和Command模块

Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc
'这个方法已经老了
Namespace DBConfig
    'Connection类-------------------------------------------------------
    Public Class DBConnection
        Private _strConn As String
        Public Property strConn As String
            Get
                Return _strConn
            End Get
            Set(ByVal value As String)
                _strConn = value
            End Set
        End Property
#If DB_OLEDB Then
        protected conn as oleDbConnection
#ElseIf DB_ODBC Then
        protected conn as OdbcConnecton
#Else
        Protected conn As SqlConnection
#End If
        Protected Sub open()
            If strConn Is Nothing Or strConn = "" Then
                MsgBox("请指定连接字符串")
                Return
            End If
#If DB_OLEDB Then
            conn=New oleDbConnection(strConn)
#ElseIf DB_ODBC Then
            conn=New OdbcConnection(strConn)
#Else
            conn = New SqlConnection(strConn)
#End If
            conn.Open()
        End Sub
        Protected Sub Close()
            conn.Close()
        End Sub
    End Class


    'Command类-----------------------------------------------------------------
    Public Class DBCommand
        Inherits DBConnection
        Public Sub New(ByVal str As String)
            strConn = str
        End Sub
        Public Function Insert(ByVal strSql As String) As Integer
            open()
#If DB_OLEDB Then
            dim cmd as OledbCommand=new OleDbCommand(strSql,conn)
#ElseIf DB_ODBC Then
             dim cmd as OdbcCommand=new odbcCommand(strSql,conn)
#Else
            Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
            Dim count As Integer
            count = cmd.ExecuteNonQuery
            Close()
            Return count
        End Function

        Public Function Delete(ByVal table As String, ByVal row As String, ByVal value As String) As Integer
            open()
            Dim strSql As String = "Delete from " & table + "Where " & row & "=" & value
#If DB_OLEDB Then
             dim cmd as OledbCommand=new OleDbCommand(strSql,conn)
#ElseIf DB_ODBC Then
             dim cmd as ObdcCommand=new ObdcCommand(strSql,conn)
#Else
            Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
            Dim count As Integer = 0
            count = cmd.ExecuteNonQuery
            Close()
            Return count
        End Function

        Public Function Update(ByVal table As String, ByVal strContent As String, ByVal row As String, ByVal value As Integer) As Integer
            open()
            Dim strSql As String = "Update " & table & " Set " & strContent & " Where " & row & "=" & value & ""
#If DB_OLEDB Then
            dim cmd as OledbCommand=new OledbCommand(strSql,conn)
#ElseIf DB_ODBC Then
            dim cmd as OdbcCommand=new OdbcCommand(strSql,conn)
#Else
            Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
            Dim count As Integer = 0
            count = cmd.ExecuteNonQuery
            Close()
            Return count
        End Function
    End Class

End Namespace

Imports MyNameSpace.DBConfig
Imports System.Data.SqlClient
Public Class Form1
    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet           '必须加“NEW”关键字
    Dim dt As DataTable

    Public Function GetConncetion1() As String
        Dim myDbConfig As New DBConnection
        myDbConfig.strConn = My.Settings.SalesConnectionString
        Return myDbConfig.strConn
    End Function

    Private Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.SalesConnectionString)
    End Function

    Private Sub DisplayData1(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataReader对象

        datagridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader = comm.ExecuteReader
        Dim dt As New DataTable
        dt.Load(dr)
        conn.Close()

        DataGridView1.DataSource = dt
    End Sub

    Private Sub DisplayData2(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter(str, conn)
        da.Fill(ds, "dt")

        conn.Close()

        DataGridView1.DataSource = ds.Tables("dt")
    End Sub

    Private Sub DisplayData3(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        Dim comm As New SqlCommand(str, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        conn.Open()

        ds = New DataSet
        da.Fill(ds, "grade")
        conn.Close()

        DataGridView1.DataSource = ds.Tables("grade")

    End Sub

    Private Sub DisplayData4(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        dgv.ColumnHeadersVisible = True
        dgv.RowHeadersVisible = True

        conn.Close()
    End Sub

    Private Sub DataReaderData(ByVal str As String)
        conn = GetConnection()
        conn.Open()

        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader
        dr = comm.ExecuteReader
        Dim strOutput As String = ""
        Try
            While dr.Read
                strOutput += dr("学号").ToString.PadLeft(10) + Space(5)
                strOutput += dr("姓名").ToString.PadLeft(10) + Space(5)
                strOutput += dr("语文").ToString.PadLeft(10) + Space(5)
                strOutput += dr("数学").ToString.PadLeft(10) + Space(5)
                strOutput += dr("英语").ToString.PadLeft(10) + Space(5)
                strOutput += vbCrLf
            End While
        Catch ex As Exception
            MsgBox("出现异常")
        Finally
            dr.Close()
            conn.Close()
        End Try
        'Label2.Text = strOutput
    End Sub

    Private Sub UpdateData()
        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand
        Dim trans As SqlTransaction
        trans = conn.BeginTransaction
        comm.Connection = conn
        comm.Transaction = trans
        Try
            comm.CommandText = "Update grade set 数学=95 Where 姓名 like '%周%'"
            comm.ExecuteNonQuery()
            comm.CommandText = "Update grade set 数学=65 Where 姓名 like '%张%'"
            comm.ExecuteNonQuery()

            trans.Commit()
            'Label1.Text = "事务运行成功"
        Catch ex As Exception
            trans.Rollback()
            'Label1.Text = "有错"
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub InsertRecord1()
        'conn = GetConnection()
        'conn.Open()
        'Dim strSql As String = "Insert into grade(学号,姓名,语文,数学,英语) " & _
        '"Values('" & txtId.Text & "','" & txtName.Text & "','" & _
        'txtChinese.Text & "','" & txtMaths.Text & "','" & txtEnglish.Text & "')"
        'Dim comm As New SqlCommand(strSql, conn)
        'comm.ExecuteNonQuery()
        'conn.Close()

    End Sub

    Private Sub InserRecord2()
        conn = GetConnection()

        Dim strInsertSql As String = "Insert into grade(学号,姓名,数学) Values(27,'于谦',59)"
        Dim strUpdateSql As String = "Update grade Set 数学=60 where 学号=27"
        Dim strSelectSql As String = "Select * From grade where 学号='27'"

        Dim InsertComm As New SqlCommand(strInsertSql, conn)
        Dim UpdateComm As New SqlCommand(strUpdateSql, conn)
        Dim SelectComm As New SqlCommand(strSelectSql, conn)

        da = New SqlDataAdapter
        conn.Open()

        da.InsertCommand = InsertComm
        da.UpdateCommand = UpdateComm
        da.SelectCommand = SelectComm

        ds = New DataSet

        da.InsertCommand.ExecuteNonQuery()
        da.Fill(ds, "grade1")
        da.UpdateCommand.ExecuteNonQuery()
        da.Fill(ds, "grade2")
        da.Fill(ds, "grade3")

        '显示插入结果
        'DataGridView2.DataSource = ds.Tables("grade1")
        '显示更新结果
        'DataGridView3.DataSource = ds.Tables("grade2")
        '显示查询结果
        'DataGridView4.DataSource = ds.Tables("grade3")

        conn.Close()

    End Sub

    Private Sub RunningCreateDataSet(ByVal dgv As DataGridView)
        Dim myds As New DataSet
        Dim mydt As New DataTable("Squares")
        Dim mydr As DataRow
        Dim i As Integer
        mydt.Columns.Add(New DataColumn("数字", GetType(Integer)))
        mydt.Columns.Add(New DataColumn("平方", GetType(Integer)))
        For i = 0 To 10
            mydr = mydt.NewRow
            mydr(0) = i
            mydr(1) = i * i
            mydt.Rows.Add(mydr)
        Next
        myds.Tables.Add(mydt)
        dgv.DataSource = myds.Tables("Squares")

        myds.WriteXml("Squares.xml")
    End Sub

    Private Sub SortDataView(ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter
        ds = New DataSet
        Dim strSql As String = "Select * from grade"
        Dim comm As New SqlCommand(strSql, conn)
        da.SelectCommand = comm
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"))
        dv.RowFilter = "语文>60"
        dv.Sort = "学号 asc,语文 desc"

        Label1.Text = "满足条件的记录有：" + dv.Count.ToString + "条"

        dgv.DataSource = dv

        conn.Close()
    End Sub

    Private Sub SearchfromDataView()
        conn = GetConnection()
        conn.Open()
        Dim strSql As String = "Select * from Grade"
        Dim comm As New SqlCommand(strSql, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        ds = New DataSet
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"), "", "姓名", DataViewRowState.CurrentRows)
        'Dim rowIndex As Integer = dv.Find(TextBox1.Text)
        'If rowIndex = -1 Then
        'Label2.Text = "没找到"
        'Else
        'Label2.Text = "姓名=" & dv(rowIndex)("姓名").ToString & Space(5) & _
        '"语文=" & dv(rowIndex)("语文").ToString & Space(5) & _
        ' "数学=" & dv(rowIndex)("数学").ToString & Space(5) & _
        '"英语=" & dv(rowIndex)("英语").ToString

        'End If

        conn.Close()
    End Sub

    Private Sub myFilter(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade order by 数学 asc", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        conn.Close()

    End Sub
    

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayData1("select * from grade", DataGridView1)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As New DBCommand(GetConncetion1)

        cmd.Update("grade", "数学=90", "学号", 14)

        DisplayData1("select * from grade", DataGridView2)
    End Sub

End Class