main.vb

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

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Public Class frmMain
    'Connection string for all examples; modify it to suit your installation
    Private strConn As String = "Server=localhost;Database=Northwind;Integrated Security=SSPI"
    'As an example, if you're using SQL Express and have installed the 
    'Northwind sample database in accordance with the instructions in the Introduction,
    'use the following connection string
    'Private strConn As String = "Server=.\SQLEXPRESS;Database=Northwind;Integrated Security=SSPI"

    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Procedures are in the order of presentation in Chapter 1
        OpenDataReader()
        OpenXmlReader()
        LoadDataGridView()
        'The following procedures don't display data
        OpenExecuteRow()
        OpenExecuteScalar()
        OpenDataReaderSqlTypes()
    End Sub

    Private Sub OpenDataReader()
        'Define and open the SqlConnection object
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        Try
            cnnNwind.Open()

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

            'Define, create, and traverse the SqlDataReader
            'Close the connection when closing the SqlDataReader
            Dim sdrReader As SqlDataReader = _
             cmdReader.ExecuteReader(CommandBehavior.CloseConnection)
            With sdrReader
                If .HasRows Then
                    While .Read
                        'Process initial rows
                        lstShippers.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString)
                    End While
                    While .NextResult
                        'Process additional rowsets
                        While .Read
                            'Process additional rows
                            lstEmployees.Items.Add(.Item(0).ToString + " - " + .Item(1).ToString + " " + .Item(2).ToString)
                        End While
                    End While
                End If
                'Close the SqlDataReader and the SqlConnection
                .Close()
            End With
        Catch exc As Exception
            MsgBox(exc.Message)
        End Try
    End Sub

    '*****************************
    'XmlReader Example with SqlXml
    '*****************************

    Private Sub OpenXmlReader()
        'Define and open the SqlConnection object
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        Try
            cnnNwind.Open()

            'Define the SqlCommand
            Dim strSQL As String = "SELECT * FROM Shippers FOR XML AUTO, Elements"
            Dim cmdXml As SqlCommand = New SqlCommand(strSQL, cnnNwind)
            cmdXml.CommandType = CommandType.Text
            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
        Catch exc As Exception
            MsgBox(exc.Message)
        Finally
            cnnNwind.Close()
        End Try
    End Sub

    '***************************************
    'DataGridView Example with SqlDataReader
    '***************************************

    Private Sub LoadDataGridView()
        'Populate a read-only DataGridView control with an SqlDataReader
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        Try
            Dim strSql As String = "SELECT * FROM Customers"
            Dim cmdGrid As New SqlCommand(strSql, cnnNwind)
            cmdGrid.CommandType = CommandType.Text
            cnnNwind.Open()
            Dim sdrGrid As SqlDataReader = cmdGrid.ExecuteReader
            Dim intCol As Integer
            With sdrGrid
                If .HasRows Then
                    dgvCusts.Rows.Clear()
                    'Add column definition: FieldName, and ColumnName
                    For intCol = 0 To .FieldCount - 1
                        dgvCusts.Columns.Add(.GetName(intCol), .GetName(intCol))
                    Next
                    'Base column width on header text width
                    dgvCusts.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                    While .Read
                        'Get row data as an Object array
                        Dim objCells(intCol) As Object
                        .GetValues(objCells)
                        'Add an entire row at a time
                        dgvCusts.Rows.Add(objCells)
                    End While
                    .Close()
                End If
            End With
        Catch exc As Exception
            MsgBox(exc.Message)
        Finally
            cnnNwind.Close()
        End Try
    End Sub

    '*********************************************************************
    'OpenExecuteRow, OpenExecuteScalar, and OpenExecuteNonQuery Procedures
    '*********************************************************************

    Private Sub OpenExecuteRow()
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        Try
            cnnNwind.Open()
            'Define the SqlCommand
            Dim strSQL As String = "SELECT * FROM Customers"
            'Following is optional for the first record
            'strSQL += " WHERE CustomerID = 'ALFKI'"
            Dim cmdRow As SqlCommand = New SqlCommand(strSQL, cnnNwind)
            cmdRow.CommandType = CommandType.Text
            'In early betas, there was an SqlRecord type and an ExecuteRow method, 
            'both of which were removed from the RTM version
            'Dim srRecord As SqlRecord = cmdRecord.ExecuteRow
            Dim sdrRow As SqlDataReader = cmdRow.ExecuteReader(CommandBehavior.SingleRow Or _
                CommandBehavior.CloseConnection)
            With sdrRow
                If .HasRows Then
                    'Following added for SqlDataReader
                    .Read()
                    Dim intFields As Integer = .FieldCount
                    Dim strCustID As String = .GetString(0)
                    Dim strCompany As String = .GetString(1)
                End If
                'Closes the DataReader and Connection
                .Close()
            End With
        Catch exc As Exception
            MsgBox(exc.Message + exc.StackTrace)
        Finally
            'Close the SqlConnection, if still open
            cnnNwind.Close()
        End Try
    End Sub

    Private Sub OpenExecuteScalar()
        'Return a single SQL aggregate value
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        cnnNwind.Open()

        'Define the SqlCommand
        Dim strSQL As String = "SELECT COUNT(*) FROM Customers"
        Dim cmdScalar As SqlCommand = New SqlCommand(strSQL, cnnNwind)
        cmdScalar.CommandType = CommandType.Text
        Dim intCount As Integer = CInt(cmdScalar.ExecuteScalar)
        'Close the SqlConnection
        cnnNwind.Close()
    End Sub

    Private Sub btnRunNonQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRunNonQuery.Click
        'Modifies data, so uses a button
        RunExecuteNonQuery()