main.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 141 行

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports Microsoft.Data.Sqlxml

Public Class frmMain
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Call OpenOleDbDataReader()
        Call OpenSqlXmlReader()
        Call OpenOdbcDataReader()
    End Sub


    Private Sub OpenOleDbDataReader()
        'Define and open the OleDbConnection object
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
         "Data Source=C:\Program Files\Microsoft Office\OFFICE11" + _
         "\SAMPLES\Northwind.mdb;Persist Security Info=False"
        'Substitute the following if you don't have Northwind.mdb available
        'Dim strConn As String = "Provider=SQLOLEDB;" + _
        ' "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"

        Dim cnnNwind As OleDbConnection = New OleDbConnection(strConn)
        cnnNwind.Open()

        'Define the OleDbCommand
        Dim strSQL As String = "SELECT * FROM Shippers"
        'strSQL += ";SELECT EmployeeID, FirstName, LastName FROM Employees"
        Dim cmdReader As OleDbCommand = New OleDbCommand(strSQL, cnnNwind)
        cmdReader.CommandType = CommandType.Text

        'Define, create, and traverse the OleDbDataReader
        'Don't close the connection when closing the OleDbDataReader
        Dim odbReader As OleDbDataReader = _
         cmdReader.ExecuteReader(CommandBehavior.Default)
        lstShippers.Items.Clear()
        With odbReader
            If .HasRows Then
                While .Read
                    'Process the rows
                    lstShippers.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString)
                End While
                .Close()
            End If
        End With
        lstEmployees.Items.Clear()
        cmdReader.CommandText = "SELECT EmployeeID, FirstName, LastName FROM Employees"
        odbReader = cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
        'Process additional rowsets
        With odbReader
            If .HasRows Then
                While .Read
                    'Process additional rows
                    lstEmployees.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString + " " + .Item(2).ToString)
                End While
            End If
            'Close the OleDbDataReader and the OleDbConnection
            .Close()
        End With
    End Sub

    '********************
    'XmlReader Example
    '********************

    Private Sub OpenSqlXmlReader()
        'Define OleDb connection string
        Dim strConn As String = "Provider=SQLOLEDB;Data Source=localhost;" + _
         "Initial Catalog=Northwind;Integrated Security=SSPI"

        'Define the SqlXmlCommand
        Dim strSQL As String = "SELECT * FROM Shippers FOR XML AUTO, Elements"
        Dim cmdXml As SqlXmlCommand = New SqlXmlCommand(strConn)
        cmdXml.CommandText = strSQL
        Dim xrShippers As System.Xml.XmlReader = cmdXml.ExecuteXmlReader
        With xrShippers
            .Read()
            Do While .ReadState <> Xml.ReadState.EndOfFile
                txtXML.Text += .ReadOuterXml
            Loop
            'Format the result
            txtXML.Text = Replace(txtXML.Text, "><", ">" + vbCrLf + "<")
            .Close()
        End With
    End Sub

    '**********************
    'OdbcDataReader Example
    '**********************

    Private Sub OpenOdbcDataReader()
        'Define and open the OdbcConnection object
        Dim strConn As String = "DRIVER={SQL Server};SERVER=localhost;" + _
         "Trusted_connection=yes;DATABASE=Northwind;"

        Dim cnnNwind As OdbcConnection = New OdbcConnection(strConn)
        cnnNwind.Open()

        'Define the OdbcCommand
        Dim strSQL As String = "SELECT * FROM Shippers"
        Dim cmdReader As OdbcCommand = New OdbcCommand(strSQL, cnnNwind)
        cmdReader.CommandType = CommandType.Text

        'Define, create, and traverse the OdbcDataReader
        'Close the connection when closing the OdbcDataReader
        Dim sdrReader As OdbcDataReader = _
         cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
        If chkUseOdbc.Checked Then
            lstShippers.Items.Clear()
        End If
        With sdrReader
            If .HasRows Then
                While .Read
                    'Process the rows
                    Dim intShipperID As Integer = .GetInt32(0)
                    Dim strCompany As String = .GetString(1)
                    Dim strPhone As String = .GetString(2)
                    If chkUseOdbc.Checked Then
                        lstShippers.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString)
                    End If
                End While
            End If
            'Close the OdbcDataReader and the OdbcConnection
            .Close()
        End With
    End Sub

    Private Sub chkUseOdbc_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkUseOdbc.CheckedChanged
        'Rerun the appropriate procedure
        With chkUseOdbc
            If .Checked Then
                Call OpenOdbcDataReader()
            Else
                Call OpenOleDbDataReader()
            End If
        End With
    End Sub
End Class