dbfactory.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

Option Explicit On
Option Strict On

Imports System.Data
Imports System.Data.Common

Public Class frmDbFactory
    'SqlClient provider settings - Customers table
    Private strSqlProvider As String = "System.Data.SqlClient"
    Private strSqlConn As String = "Data Source=localhost;" + _
     "Initial Catalog=Northwind;Integrated Security=SSPI"
    Private strSqlTable As String = "Customers"

    'OleDb provider settings - Products table
    Private strOleDbProvider As String = "System.Data.OleDb"
    Private strOleDbConn As String = "Provider=SQLOLEDB;Data Source=localhost;" + _
     "Initial Catalog=Northwind;Integrated Security=SSPI"
    Private strOleDbTable As String = "Products"

    'Odbc provider settings - Suppliers table
    Private strOdbcProvider As String = "System.Data.Odbc"
    Private strOdbcConn As String = "DRIVER={SQL Server};SERVER=localhost;" + _
      "Trusted_connection=Yes;DATABASE=Northwind;"
    Private strOdbcTable As String = "Suppliers"

    '*****************************
    'Fill initial list (SqlClient)
    '*****************************

    Private Sub frmDbFactory_Load(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Load
        PopulateList(strSqlProvider, strSqlConn, strSqlTable)
    End Sub

    Private Sub PopulateList(ByVal strProvider As String, _
     ByVal strConn As String, ByVal strTable As String)
        'Create a DbProviderFactory, IDbConnection, IDbCommand, and IDataReader
        'for the specified data provider
        Dim cnFactory As IDbConnection = Nothing
        Dim drData As IDataReader = Nothing
        Try
            'Specify the DataProvider
            Dim dpFactory As DbProviderFactory = _
             DbProviderFactories.GetFactory(strProvider)
            'Create a connection
            cnFactory = dpFactory.CreateConnection()
            cnFactory.ConnectionString = strConn
            'Create a command and open the connection
            Dim cmFactory As IDbCommand = cnFactory.CreateCommand
            cmFactory.CommandType = CommandType.Text
            cmFactory.CommandText = "SELECT * FROM " + strTable
            cnFactory.Open()
            'Create and traverse a DataReader
            drData = cmFactory.ExecuteReader(CommandBehavior.KeyInfo)
            lstData.Items.Clear()
            Dim dtSchema As DataTable
            With drData
                While drData.Read
                    'Must use Object because datatypes change
                    lstData.Items.Add(.GetValue(0).ToString + _
                     " - " + .GetValue(1).ToString)
                End While
                dtSchema = drData.GetSchemaTable()
                With dgvSchema
                    If dtSchema.Columns.Count > 1 Then
                        .RowHeadersVisible = False
                        .DataSource = dtSchema
                        .AutoGenerateColumns = True
                        Application.DoEvents()
                        If .Columns.Count > 0 Then
                            .Columns(0).Frozen = True
                            .Columns("BaseSchemaName").Width = 110
                            If .Columns.Count = 24 Then
                                'SqlClient only
                                .Columns(23).Width = 200
                            End If
                        End If
                    End If
                End With
            End With

            If dgvSchema.Columns.Count > 0 Then
                '*****************************
                'Documentation for column data
                Dim intCtr As Integer
                Dim strDataCols As String = ""
                For intCtr = 0 To dgvSchema.Rows(0).Cells.Count - 1
                    strDataCols += dgvSchema.Columns(intCtr).Name + vbTab + _
                     dgvSchema.Rows(0).Cells(intCtr).Value.ToString + vbCrLf
                Next intCtr
                intCtr = 0
                '*****************************
            End If
        Catch exc As Exception
            MsgBox(exc.Message + exc.StackTrace)
        Finally
            If Not drData Is Nothing Then
                drData.Close()
            End If
            If Not cnFactory Is Nothing Then
                cnFactory.Close()
            End If
        End Try
    End Sub

    '*********************
    'Change data providers
    '*********************

    Private Sub optSqlClient_CheckedChanged(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles optSqlClient.CheckedChanged
        If optSqlClient.Checked = True Then
            PopulateList(strSqlProvider, strSqlConn, strSqlTable)
            Me.Text = "DbFactory Test Form - SqlClient"
        End If
    End Sub

    Private Sub optOleDb_CheckedChanged(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles optOleDb.CheckedChanged
        If optOleDb.Checked = True Then
            PopulateList(strOleDbProvider, strOleDbConn, strOleDbTable)
            Me.Text = "DbFactory Test Form - OleDb"
        End If
    End Sub

    Private Sub optOdbc_CheckedChanged(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles optOdbc.CheckedChanged
        If optOdbc.Checked = True Then
            PopulateList(strOdbcProvider, strOdbcConn, strOdbcTable)
            Me.Text = "DbFactory Test Form - Odbc"
        End If
    End Sub
End Class