数据库开发019 DataReader对象

    DataReader对象提供单向只读数据，只能依次读取数据，DataSet中的数据可以任意读取和修改。DataReader对象有一个很重要的方法Read，它是个布尔值，作用是读取下一条数据，当布尔值为真时执行，为假时退出。
    DataReader从数据库当中检索只读的数据流，存储在客户端的网络缓冲区当中。在内存当中只存储一行，具有开销小、速度快的特点。由于数据不在内存中缓存，所以在检索大量数据时，DataReader是一种较好的选择。DataReader只能通过Command对象ExecuteReader()方法来创建，不能实例化。
    DataReader具有独占性，如果在已经打开DataReader的情况下，将不能对Connection进行任何操作，必须在用完时调用Close()方法关闭。通过Command对象返回多个结果集，并且通过DataReader对象NextResult()方法来使用。
    DataReader对象通过Command对象的ExecuteReader()方法创建，读取DataReader对象的数据有两种方法：通过和ataGridView等数据控件绑定，直接输出；利用循环将数据取出.

Imports System.Data.SqlClient
Public Class Form1
    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Private Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.SalesConnectionString)
    End Function

    Private Sub DisplayData(ByVal str As String)
        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 DataReaderData()
        conn = GetConnection()
        conn.Open()

        Dim comm As New SqlCommand(TextBox1.Text, 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 InsertRecord()
        '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 Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayData("select * from grade")
        TextBox1.Text = "select * from grade"
    End Sub

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