SqlDataReader 用法

 

這只是一個單純的 VB.NET ConsoleApplication 結構如下

 

Module1.vb 內容為

Imports System.Data.SqlClient

Module Module1

    Dim sCn As String = "server=DESKTOP-HE3V963\SQLEXPRESS; database=NORTHWIND; uid=sa; pwd=xxxxxx;"

    Sub Main()
        Try
            Using cn As New SqlConnection(sCn)
                cn.Open()
                Dim cmd As New SqlCommand()
                cmd.Connection = cn
                cmd.CommandText = "select top 1 ProductName from Products"
                Dim dr As SqlDataReader = cmd.ExecuteReader()

                Do While dr.Read = True
                    MsgBox(dr.Item(0).ToString)
                Loop

            End Using
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Module

 

執行畫面為

 

另一 VB.NET WinForm 範例

Imports System.Data.SqlClient

'tip:如何提升DataReader物件的讀取效能 getInt16 GetString
Public Class Form1
    Dim sCn As String = "data source =.\SQLexpress; attachDBfilename = |dataDirectory|MyDB.mdf; integrated security = true; user instance = true;"
    Dim cn As SqlConnection
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            Dim dataReader As SqlDataReader
            Dim sqlCommand As SqlCommand
            Dim cn = New SqlConnection(sCn)
            cn.Open()
            sqlCommand = New SqlCommand("select * from 員工", cn)
            dataReader = sqlCommand.ExecuteReader
            RichTextBox1.Text = ""
            For i = 0 To dataReader.FieldCount - 1 'FieldCount 取得columnCount
                RichTextBox1.Text += "show 員工的 column name: " & dataReader.GetName(i).ToString & vbCrLf 'show columnName
            Next
            dataReader.Close()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            Dim dataReader As SqlDataReader
            Dim sqlCommand As SqlCommand
            Dim cn = New SqlConnection(sCn)
            cn.Open()
            sqlCommand = New SqlCommand("select * from 員工", cn)
            dataReader = sqlCommand.ExecuteReader
            RichTextBox1.Text = ""
            Do While (dataReader.Read) 'when dataReader can 'read' then do 
                For i = 0 To dataReader.FieldCount - 1
                    RichTextBox1.Text += dataReader.GetName(i) & ":" & dataReader.Item(i).ToString 'there Item(columnIndex) will show rows value 
                Next
                RichTextBox1.Text += vbCrLf
            Loop
            dataReader.Close()
            cn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class

範例下載