datatables.vb

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

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

Public Class frmDataTables
    Private dtCusts As DataTable
    Private dtSchema As DataTable
    Private strPath As String
    Private strIeFile As String = "\Program Files\Internet Explorer\Iexplore.exe"
    Private blnIsLoading As Boolean
    Private intRows As Integer

    '****************************************
    'DataTable Loading and Display Procedures
    '****************************************

    Private Sub frmDataTables_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Load and display the default DataTable
        strPath = Application.StartupPath + "\"
        LoadFromDatabase(False)
        SaveXmlFiles(False)
        'Test for IE in expected location
        If File.Exists(strIeFile) Then
            btnShowData.Visible = True
            btnShowDataSet.Visible = True
            btnShowDiffGram.Visible = True
        End If
    End Sub

    Private Sub btnReloadNS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReloadNS.Click
        'Reload DataTable with optional namespace
        LoadFromDatabase(chkAddNS.Checked)
        SaveXmlFiles(True)
    End Sub

    Private Sub LoadFromDatabase(ByVal blnWithNamespace As Boolean)
        'Load and display the DataTable with or without a table namespace
        Dim strConn As String = "Server=localhost;Integrated Security=True;Database=Northwind"
        Dim cnnNwind As SqlConnection = New SqlConnection(strConn)
        Try
            Dim cmdCusts As SqlCommand = cnnNwind.CreateCommand
            With cmdCusts
                .CommandType = CommandType.Text
                .CommandText = "SELECT * FROM Customers"
            End With
            cnnNwind.Open()
            Dim drCusts As SqlDataReader = cmdCusts.ExecuteReader(CommandBehavior.KeyInfo)
            dtCusts = New DataTable
            dtSchema = drCusts.GetSchemaTable
            With dtCusts
                .TableName = "Customers"
                If blnWithNamespace Then
                    'Uncomment the following line to view effect on data and schemas
                    '.Prefix = "custs"
                    .Namespace = "http://www.oakleaf.ws/schemas/northwind/customers"
                End If

                'Load the data and accept changes
                .Load(drCusts)
                .AcceptChanges()

                If .PrimaryKey.Length = 0 Then
                    'Set the primary key constraint if missing
                    Dim acolKeys(1) As DataColumn
                    acolKeys(0) = .Columns(0)
                    .PrimaryKey = acolKeys
                End If

                'Test the DataSet property
                If Not .DataSet Is Nothing Then
                    Dim strName As String = .DataSet.DataSetName
                    MsgBox(strName)
                End If
            End With
            drCusts.Close()

            'Test the DataTableReader
            Dim dtrCusts As New DataTableReader(dtCusts)
            intRows = 0
            While dtrCusts.Read
                intRows += 1
            End While
            dtrCusts.Close()

            LoadDataGridViews()
        Catch excDT As Exception
            MsgBox(excDT.Message + excDT.StackTrace, , "DataTable Load Exception")
        Finally
            cnnNwind.Close()
        End Try
    End Sub

    Private Sub LoadDataGridViews()
        'Load and format the data grid
        blnIsLoading = True
        With dgvCusts
            .DataSource = dtCusts
            .AutoGenerateColumns = True
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            .Columns(0).Width = 70
            .Columns(7).Width = 70
            .RowHeadersWidth = 25
            .BorderStyle = BorderStyle.None
        End With
        'Load and format the schema grid
        With dgvSchema
            .DataSource = dtSchema
            .AutoGenerateColumns = True
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            .RowHeadersVisible = False
            .BorderStyle = BorderStyle.None
        End With
        blnIsLoading = False
    End Sub

    '**********************************
    'Save and Load XML Files Procedures
    '**********************************

    Private Sub SaveXmlFiles(ByVal blnShowMessage As Boolean)
        'Delete and resave data, schema, and (if changes) diffgram files
        DeleteXmlFiles()
        With dtCusts
            .WriteXml(strPath + "Data.xml", System.Data.XmlWriteMode.IgnoreSchema)
            .WriteXml(strPath + "DataSet.xml", System.Data.XmlWriteMode.WriteSchema)
            .WriteXmlSchema(strPath + "Schema.xsd")
        End With
        btnShowData.Enabled = True
        btnShowDataSet.Enabled = True
        btnShowSchema.Enabled = True
        'HasChanges property is missing
        Dim dtChanges As New DataTable
        dtChanges = dtCusts.GetChanges
        Dim strMsg As String
        If dtChanges Is Nothing Then
            strMsg = "Data and schema for " + intRows.ToString + " rows written to '" _
             + strPath + "' folder."
            btnShowDiffGram.Enabled = False
        Else
            dtChanges.WriteXml(strPath + "Diffgram.xml", System.Data.XmlWriteMode.DiffGram)
            strMsg = "Data for " + intRows.ToString + " rows, schema, and changes diffgram written to '" + _
             strPath + "' folder and changes accepted."
            dtCusts.AcceptChanges()
            btnShowDiffGram.Enabled = True
        End If
        If blnShowMessage Then
            MsgBox(strMsg, , "XML Files Saved")
        End If
        btnReadXML.Enabled = True
    End Sub

    Private Sub DeleteXmlFiles()
        'Delete all saved files
        File.Delete(strPath + "Data.xml")
        File.Delete(strPath + "DataSet.xml")
        File.Delete(strPath + "Diffgram.xml")
        File.Delete(strPath + "Schema.xsd")
        File.Delete(strPath + "Schema_app1.xsd")
        btnReadXML.Enabled = False
    End Sub

    Private Sub btnReadXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadXML.Click
        'Load or attempt to load DataTable from saved DataSet
        btnShowDiffGram.Enabled = False
        Try
            dtCusts = New DataTable
            With dtCusts
                .ReadXml(strPath + "DataSet.xml")
                If File.Exists(strPath + "Diffgram.xml") Then
                    'Apply the changes
                    .ReadXml(strPath + "Diffgram.xml")
                End If
                .AcceptChanges()
            End With

            'Test the DataTableReader
            Dim dtrCusts As New DataTableReader(dtCusts)
            dtSchema = dtrCusts.GetSchemaTable
            intRows = 0
            While dtrCusts.Read
                intRows += 1
            End While
            dtrCusts.Close()

            LoadDataGridViews()
        Catch excXML As Exception
            MsgBox(excXML.Message + excXML.StackTrace, , "DataTable ReadXml Exception")
        End Try
    End Sub

    '*****************************
    'Grid View Editing Procedures
    '*****************************

    Private Sub dgvCusts_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgvCusts.CurrentCellChanged
        If Not blnIsLoading Then
            'HasChanges property is missing
            Dim dtChanges As New DataTable
            dtChanges = dtCusts.GetChanges
            If dtChanges Is Nothing Then
                btnShowDiffGram.Enabled = False
            Else
                dtChanges.WriteXml(strPath + "Diffgram.xml", System.Data.XmlWriteMode.DiffGram)
                'dtCusts.AcceptChanges()
                btnShowDiffGram.Enabled = True
            End If
        End If
    End Sub

    Private Sub dgvCusts_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgvCusts.DataError
        'Handle grid editing errors
        MsgBox(e.Exception.Message + e.Exception.StackTrace, , "DataTable Editing Error")
    End Sub

    '*****************************
    'Display Button Event Handlers
    '*****************************

    Private Sub btnShowData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowData.Click
        ShowInIE(" " + strPath + "Data.xml")
    End Sub

    Private Sub btnShowDataSet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowDataSet.Click
        ShowInIE(" " + strPath + "DataSet.xml")
    End Sub

    Private Sub btnShowSchema_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSchema.Click
        ShowInIE(" " + strPath + "Schema.xsd")
    End Sub

    Private Sub btnShowDiffGram_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowDiffGram.Click
        ShowInIE(" " + strPath + "DiffGram.xml")
    End Sub

    Private Sub ShowInIE(ByVal strFile As String)
        Shell("" + strIeFile + "" + strFile, AppWinStyle.NormalFocus)
    End Sub
End Class