学步园

11.1 开始了解元数据

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <clear/>
    <add name="NorthwindSqlClient" connectionString=
         "Data Source=.\sqlexpress;
         AttachDbFilename=|DataDirectory|\northwnd.mdf;
         Integrated Security=True;User Instance=True"
         providerName="system.data.SqlClient"/>
    <add name="NorthwindSqlOleDb" connectionString=
         "Provider=SQLOLEDB.1;
         Integrated Security=SSPI;
         Persist Security Info=False;
         Initial Catalog=Northwind;
         Data Source=.\sqlexpress;
         User Instance=True"
         providerName="system.data.OleDb"/>
    <add name="NorthwindSqlOdbc" connectionString=
         "DRIVER=SQL Server;
         SERVER=.\SQLEXPRESS;
         database=Northwind;Trusted_connection=Yes"
         providerName="system.data.Odbc"/>
    <add name="NorthwindAccess" connectionString=
         "Provider=Microsoft.Jet.OLEDB.4.0;
         Data Source=|DataDirectory|\northwnd.mdb;
         Persist Security Info=True"
         providerName="system.data.OleDb"/>    
  </connectionStrings>
</configuration>

 

安装Northwind数据库

 

Imports System.Configuration
Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        For Each cnSetting As ConnectionStringSettings In ConfigurationManager.ConnectionStrings
            ComboBox1.Items.Add(cnSetting.Name)
        Next
        'mount northwind if it isn't mounted
        Using cn As New SqlConnection
            Using cmd As SqlCommand = cn.CreateCommand
                cmd.CommandText = "SELECT COUNT(*) FROM master.sys.databases" &
                    "Where name='Northwind'"
                cn.ConnectionString = "server=.\sqlexpress;" &
                    "database=master;" &
                    "Integrated security=true"
                cn.Open()
                Dim count As Integer = CType(cmd.ExecuteScalar, Integer)
                If count = 1 Then
                    Return
                End If
                cn.Close()
            End Using
            cn.ConnectionString = "server=.\sqlexpress;" &
                "AttachDbFilename=|DataDirectory|\MountedNorthwnd.mdf;" &
                "database=Northwind;Integrated security=true;" &
                "User Instance=false"
            cn.Open()
            cn.Close()
            MessageBox.Show("Northwind Mounted")
        End Using
    End Sub
End Class

  

11.2 获取元数据集合 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ComboBox1.Text.Trim.Length = 0 Then
            MessageBox.Show("select a connection")
            Return
        End If
        DataGridView1.DataSource = GetSchemaDataTable(Nothing, Nothing)
    End Sub
    Function GetSchemaDataTable(ByVal collectionName As String, ByVal restrictions As String()) As DataTable
        Dim cnSettings As ConnectionStringSettings =
            ConfigurationManager.ConnectionStrings(ComboBox1.Text)
        Dim provider As DbProviderFactory =
            DbProviderFactories.GetFactory(cnSettings.ProviderName)
        Using cn As DbConnection = provider.CreateConnection
            cn.ConnectionString = cnSettings.ConnectionString
            cn.Open()
            If collectionName Is Nothing Then
                Return cn.GetSchema
            End If
            Return cn.GetSchema(collectionName, restrictions)
        End Using
    End Function

 

 

11.2.1 模式导航

11.2.2 导航元数据集合 

    Private Sub DataGridView1_CellDoubleClick(
                                             ByVal sender As Object,
                                             ByVal e As System.Windows.Forms.DataGridViewCellEventArgs
                                             ) Handles DataGridView1.CellDoubleClick
        If DataGridView1.Columns("CollectionName") Is Nothing Then
            MessageBox.Show("No CollectionName column")
            Return
        End If
        Dim collectionName As String = CStr(DataGridView1.Rows(
                e.RowIndex).Cells("CollectionName").Value)
        DataGridView1.DataSource = GetSchemaDataTable(collectionName, Nothing)
    End Sub

  

11.2.3 处理约束

 

    Dim restrictionDataTable As DataTable
    Dim unselected As String = "<unselected>"
    Public Function GetRestrictions(ByVal collectionName As String) As String()
        'display form with filter selections
        Dim frm As New Form
        Dim filters As New ArrayList
        Dim completecollection As DataTable = GetSchemaDataTable(collectionName, Nothing)
        restrictionDataTable.DefaultView.RowFilter =
            "CollectionName='" & collectionName & "'"
        restrictionDataTable.DefaultView.Sort = "RestrictionNumber ASC"
        Dim labelX As Integer = 10
        Dim comboX As Integer = 150
        Dim labelWidth As Integer = comboX - labelX
        Dim comboWidth As Integer = 150
        Dim formWidth As Integer = comboWidth + comboX + 10
        Dim y As Integer = 20
        frm.Text = "Select Filter Values"
        frm.Width = formWidth
        Dim containsFilters As Boolean = False
        For Each dv As DataRowView In restrictionDataTable.DefaultView
            Dim lbl As New Label
            lbl.Location = New Point(comboX, y)
            lbl.Width = labelWidth
            lbl.Text = CStr(dv("RestrictionName"))
            Dim cmb As New ComboBox
            cmb.DropDownStyle = ComboBoxStyle.DropDownList
            cmb.Location = New Point(comboX, y)
            cmb.Width = comboWidth
            cmb.Anchor = AnchorStyles.Left Or AnchorStyles.Right Or AnchorStyles.Top
            cmb.Items.Add(unselected)
            cmb.Items.AddRange(GetComboValues(dv, completecollection))
            frm.Controls.Add(lbl)
            frm.Controls.Add(cmb)
            filters.Add(cmb)
            cmb.Text = unselected
            cmb.Visible = True
            lbl.Visible = True
            y += 30
            containsFilters = True
        Next
        If containsFilters Then
            Dim ok As New Button
            ok.Text = "&Ok"
            ok.DialogResult = Windows.Forms.DialogResult.OK
            ok.Location = New Point(labelX, y)
            frm.Controls.Add(ok)
            ok.Visible = True
            frm.AcceptButton = ok
            Dim cancel As New Button
            cancel.Text = "&Cancel"
            cancel.DialogResult = Windows.Forms.DialogResult.Cancel
            cancel.Location = New Point(comboX, y)
            frm.Controls.Add(cancel)
            cancel.Visible = True
            frm.AcceptButton = cancel
        Else
            Return Nothing
        End If
        frm.Height = y + 60
        If frm.ShowDialog <> Windows.Forms.DialogResult.OK Then
            Return Nothing
        End If
        Dim results(filters.Count - 1) As String
        For i As Integer = 0 To filters.Count - 1
            Dim cmb As ComboBox = CType(filters(i), ComboBox)
            If cmb.Text = unselected Or cmb.Text Is Nothing Or cmb.Text.Length = 0 Then
                results(i) = Nothing
            Else
                results(i) = cmb.Text
            End If
        Next
        Return results
    End Function
    Private Function GetComboValues(ByVal dv As DataRowView, ByVal list As DataTable) As Object()
        Dim ret As New Hashtable
        Dim columnName As String
        columnName = CStr(dv("RestrictionName"))
        For Each dr As DataRow In list.Rows
            Dim currentValue As String = CStr(dr(columnName))
            ret(currentValue) = currentValue
        Next
        Dim comboValues As New ArrayList(ret.Keys)
        comboValues.Sort()
        Return comboValues.ToArray
    End Function

 

对之前的代码进行改动

 

    Private Sub Button1_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button1.Click
        restrictionDataTable = GetSchemaDataTable("Restrictions", Nothing)
        DataGridView1.DataSource = GetSchemaDataTable(Nothing, Nothing)
    End Sub
    Private Sub DataGridView1_CellDoubleClick(
                                             ByVal sender As Object,
                                             ByVal e As System.Windows.Forms.DataGridViewCellEventArgs
                                             ) Handles DataGridView1.CellDoubleClick
        If DataGridView1.Columns("CollectionName") Is Nothing Then
            MessageBox.Show("No CollectionName column")
            Return
        End If
        Dim collectionName As String = CStr(DataGridView1.Rows(
                e.RowIndex).Cells("CollectionName").Value)
        DataGridView1.DataSource = GetSchemaDataTable(collectionName,
                                                      GetRestrictions(collectionName))
    End Sub

 

 

11.2.4 修改和扩展元数据

11.2.5 理解唯一标识符部分