数据库开发022 DataAdpapter对象的Command属性

    DataAdapter对象还可以通过4个属性传递数据，给4个属性赋值的对象必须是Command对象。

• 如果Command对象执行的是SELECT语句，必须将对象传递给SelectCornmand属性；
• 如果是UPDATE语句，则传递给UpdateCommand属性；
• 如果是INSERT语句，则传递给InsertCommand属性；
• 如果是DELETE语句，则传递给DeleteCommand对象。

    默认情况下，当Connection对象执行Open方法的时候，DataAdapter对象将自动调用SelectCommand属性。除了SelectCommand属性，其他3个属性都需要使用“ExecuteNonQuery0”方法调用。

Imports System.Data.SqlClient

Public Class Form1
    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet           '必须加“NEW”关键字
    Dim dt As DataTable
    Private Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.SalesConnectionString)
    End Function

    Private Sub DisplayData1(ByVal str As String) '用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) '用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) '用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 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 Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox1.Text = "Select * from grade"
        DisplayData3(TextBox1.Text)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        InserRecord2()
    End Sub
End Class

调试程序的时候注意修改这里的“学号”和“姓名”值

        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'"

总有一些莫名其秒的错误，程序调试越来越耗时。