utildata.vb

Programming the .NET Compact Framework with vb 源代码

' -----------------------------------------------------------------------------
' Code from _Programming the .NET Compact Framework with VB_
' and _Programming the .NET Compact Framework with C#_
' (c) Copyright 2002-2004 Paul Yao and David Durant. 
' All rights reserved.
' -----------------------------------------------------------------------------

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlServerCe

Public Class UtilSqlCE
   Private Shared strGetTableNames As String = _
      "SELECT TABLE_NAME" & _
      "  FROM Information_Schema.TABLES" & _
      " WHERE TABLE_TYPE = 'TABLE'"

   Private Shared strGetTableRows As String = _
      "SELECT * FROM "

   Private Shared strGetRelationships As String = _
      "SELECT C.CONSTRAINT_NAME" & _
      "     , C.REFERENCED_TABLE_NAME" & _
      "     , C.REFERENCED_COLUMN_NAME" & _
      "     , C.TABLE_NAME" & _
      "     , C.CONSTRAINT_COLUMN_NAME" & _
      "  FROM MSysConstraints C" & _
      "  WHERE C.CONSTRAINT_TYPE = 1"

   Public Shared Function BuildDataSet(ByVal strDB As String _
                                      ) _
                                      As DataSet
      Dim dsetWork As DataSet

      If Not File.Exists(strDB) Then
         Exit Function
      End If

      Try
         Dim strConn As String = "Data Source=" & strDB
         Dim connDB As _
            New SqlServerCe.SqlCeConnection(strConn)
         connDB.Open()

         dsetWork = New DataSet("ourProduceCo")

         With GetTableNames(connDB)
            While .Read()
               LoadTable(dsetWork, connDB, .GetString(0))
            End While
            .Close()
         End With

         LoadRelationships(dsetWork, connDB)

         connDB.Close()

         Return dsetWork
      Catch exSQL As SqlCeException
         With exSQL
            MsgBox(.Message)
         End With
      Finally
      End Try
   End Function


   ' ********************************************************'
   '
   '     GENERIC ROUTINES
   '

   Public Shared Function GetTableNames _
                          (ByVal connDB As SqlCeConnection _
                          ) _
                                        As SqlCeDataReader

      Dim cmndDB As New SqlCeCommand(strGetTableNames, connDB)
      Return cmndDB.ExecuteReader()
   End Function

   Public Shared Sub LoadTable _
                             (ByVal dsetDB As DataSet, _
                              ByVal connDB As SqlCeConnection, _
                              ByVal strTable As String)

      Dim cmndDB As New SqlCeCommand _
                        (strGetTableRows & strTable, connDB)
      Dim daptProducts As New SqlCeDataAdapter(cmndDB)
      daptProducts.Fill(dsetDB, strTable)
   End Sub

   Public Shared Function GetRelationships _
                              (ByVal connDB As SqlCeConnection _
                              ) _
                                          As SqlCeDataReader

      Dim cmndDB As New SqlCeCommand(strGetRelationships, connDB)
      Return cmndDB.ExecuteReader()
   End Function

   Public Shared Sub LoadRelationships _
                                (ByVal dsetDB As DataSet, _
                                 ByVal connDB As SqlCeConnection)
      '  Retrieve foreign key information from the
      '     database.  For each foreign key, create
      '     a relationship in the DataSet.

      '  Create GetRelationships command object
      Dim cmndDB As New _
         SqlCeCommand(strGetRelationships, connDB)

      '  Execute GetRelationships
      With cmndDB.ExecuteReader()
         Dim strRelation As String
         Dim dcolParent, dcolChild As DataColumn

         While .Read()
            '  For each foreign key in the database 

            '  Extract and convert name, parent, child info.
            strRelation = .GetString(0)
            dcolParent = _
               dsetDB.Tables(.GetString(1)).Columns(.GetString(2))
            dcolChild = _
               dsetDB.Tables(.GetString(3)).Columns(.GetString(4))

            '  Add relation to DataSet.
            dsetDB.Relations.Add(strRelation, _
                                 dcolParent, dcolChild)
         End While
         .Close()
      End With

      '  Make each relationship a nested relationship
      Dim drelForXML As DataRelation
      For Each drelForXML In dsetDB.Relations
         drelForXML.Nested = True
      Next
   End Sub

   Public Shared Function ConvertArrayToTable _
                                 (ByVal arrRows() As DataRow _
                                 ) _
                                 As DataTable

      '  Create and empty table to hold these rows by cloning
      '     the structure of the table that these rows came from.
      Dim dtabWork As DataTable = arrRows(0).Table.Clone

      '  Add the rows to the table.
      Dim drowWork As DataRow
      For Each drowWork In arrRows
         dtabWork.ImportRow(drowWork)
      Next

      '  Return the table.
      Return dtabWork
   End Function

   Public Shared Function RetrieveDataReader _
                              (ByVal connDB As SqlCeConnection, _
                               ByVal strSelect As String _
                              ) _
                              As SqlCeDataReader
      Dim cmndDb As New SqlCeCommand(strSelect, connDB)
      Dim drdrWork As SqlCeDataReader
      drdrWork = cmndDb.ExecuteReader()
      Return drdrWork
   End Function

   Public Shared Function RetrieveDataTable _
                              (ByVal connDB As SqlCeConnection, _
                               ByVal strSelect As String _
                              ) _
                              As DataTable
      Dim daptProducts As New SqlCeDataAdapter(strSelect, connDB)
      Dim dtabWork As New DataTable
      daptProducts.Fill(dtabWork)
      Return dtabWork
   End Function

   ' ********************************************************'

   Private Sub CreateTable()
      '  Create empty table.
      Dim dtabCustomers As New DataTable("Customers")

      '  Create three columns.
      Dim dcolID As New DataColumn("ID")
      dcolID.DataType = GetType(Integer)
      dcolID.AutoIncrement = True

      Dim dcolName As New DataColumn("Name")
      dcolName.DataType = GetType(String)

      Dim dcolAddress As New DataColumn("Address")
      dcolAddress.DataType = GetType(String)

      '  Add columns to table.
      dtabCustomers.Columns.Add(dcolID)
      dtabCustomers.Columns.Add(dcolName)
      dtabCustomers.Columns.Add(dcolAddress)

      '  Add a primary key constraint.
      dtabCustomers.Constraints.Add("PKCust", dcolID, True)

      '  Add two rows to the table
      Dim drowCust As DataRow
      drowCust = dtabCustomers.NewRow()
      drowCust("ID") = 1
      drowCust("Name") = "Amalgamated United"
      drowCust("Address") = "PO Box 123, 98765"
      dtabCustomers.Rows.Add(drowCust)
      drowCust = dtabCustomers.NewRow()
      drowCust("ID") = 2
      drowCust("Name") = "United Amalgamated"
      drowCust("Address") = "PO Box 987, 12345"
      dtabCustomers.Rows.Add(drowCust)
   End Sub

   Private Sub dummmy()
      Dim dsetDB As New DataSet
      dsetDB.Tables _
         ("Categories").Select("CategoryName = 'Widgets'")
      Dim drowX As DataRow = dsetDB.Tables _
         ("Categories").Select("CategoryName = 'Widgets'")(0)


      With dsetDB
         '  Define the relationship 
         '     between Products and Categories.

         .Relations.Add( _
            "FKProdCat", _
            .Tables("Categories").Columns("CategoryID"), _
            .Tables("Products").Columns("CategoryID"), _
            True)

         '  Select the Widget row in the Categories data table.
         '    Use it to retrieve all Widget rows from the
         '    Products data table.

         dsetDB.Tables("Categories"). _
            Select("CategoryName = 'Widgets'")(0). _
               GetChildRows("FKProdCat")
      End With

      '  Make each relationship a nested relationship
      Dim drelForXML As DataRelation
      For Each drelForXML In dsetDB.Relations
         drelForXML.Nested = True
      Next

      '  Make the FKProdCat relationship nested.
      dsetDB.Relations("FKProdCat").Nested = True

   End Sub

End Class