ADO.net 程式碼大全 For VB

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        'Datareader
        Dim cn As SqlConnection = New SqlConnection(ado.conn)
        Dim cmd As SqlCommand = New SqlCommand("select * from book;select * from test", cn) '讀雙資料表大絕!!
        cn.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        'dr.Read() '特性為一次讀一欄
        Do While dr.Read()
            'TextBox1.Text = dr.Item("ct")
            Response.Write(dr.Item("ct") + "<br />")

        Loop
        dr.NextResult()
        Do While dr.Read()
            'TextBox1.Text = dr.Item("ct")
            Response.Write(dr.Item("ct2") + "<br />")

        Loop
        'TextBox1.Text = dr.GetValue(1)
        dr.Close()
        With cn
            .Close()
            .Dispose()
        End With
        cn = Nothing

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        'dataset
        Dim cn As SqlConnection = New SqlConnection(ado.conn)
        Dim ap As SqlDataAdapter = New SqlDataAdapter("select * from book", cn)
        'Dim ap As SqlDataAdapter = New SqlDataAdapter("update set ct = @ct WHERE id = 1", cn)
        Dim dt As DataSet = New DataSet()
        ap.Fill(dt, "444")
        TextBox1.Text = dt.Tables(0).Rows(0)(1)
        Dim ap2 As SqlDataAdapter = New SqlDataAdapter("select * from test", cn) '讀雙資料表大絕!!
        ap2.Fill(dt, "555")
        Response.Write(dt.Tables("555").Rows(0)(1))
        'GridView1.DataSource = dt.Tables(0)
        'GridView1.DataBind()

    End Sub

    Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        'datareader update
        Using cn As New SqlConnection(ado.conn)
            cn.Open()
            Dim cmd As New SqlCommand("update book set ct = @ct where id = 2", cn)
            cmd.Parameters.AddWithValue("@ct", TextBox1.Text)
            cmd.ExecuteNonQuery()
        End Using
        'cn.Close() 使用USING可以不需要去關閉!
    End Sub

    Protected Sub Button4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button4.Click
        'dataset update
        Dim cn As SqlConnection = New SqlConnection(ado.conn)
        'Dim ap As SqlDataAdapter = New SqlDataAdapter
        Dim ap As SqlDataAdapter = New SqlDataAdapter("select * from book where id = 2", cn)
        'Dim dt As DataSet = New DataSet()
        Dim dt As DataTable = New DataTable()
        ap.Fill(dt)
        ap.InsertCommand = New SqlCommand("insert into book ct values @ct ", cn)
        ap.UpdateCommand = New SqlCommand("update book set ct = '5214' where id = 2", cn)
        'ap.UpdateCommand.CommandText = "update book set ct = @ct where id = 2"
        'ap.UpdateCommand.Parameters.AddWithValue("@ct", TextBox1.Text)
        dt.Rows(0)(1) = "555"
        ap.Update(dt) '由於update()方法會自動偵測變更選用相關command 所以他的更新方式很機車= =
    End Sub
End Class