学步园

第一章 ADO.NET非连接类概述

1.1DataTable对象  

Dim auto As New DataTable("Auto")

 

1.1.1添加DataColumn对象以创建一个模式  

        Dim vin As New DataColumn("Vin")
        vin.DataType = GetType(String)
        vin.MaxLength = 23
        vin.Unique = True
        vin.AllowDBNull = False
        vin.Caption = "VIN"
        auto.Columns.Add(vin)
        Dim make As New DataColumn("Make")
        make.MaxLength = 35
        make.AllowDBNull = False
        auto.Columns.Add(make)
        Dim year As New DataColumn("Year", GetType(Integer))
        year.AllowDBNull = False
        auto.Columns.Add(year)
        Dim yearMake As New DataColumn("Year and Make")
        yearMake.MaxLength = 70
        yearMake.Expression = "year + ' ' + make"
        auto.Columns.Add(yearMake)

 

 

 1.1.2 创建主键列

 

auto.PrimaryKey = New DataColumn() {vin}

 

 

1.1.3创建用于存储数据的DataRow对象

1.1.3.1向DataTable添加数据

 

Dim newAuto As DataRow = auto.NewRow
newAuto("Vin") = "123456789abcd"
newAuto("make") = "ford"
newAuto("Year") = 2002
auto.Rows.Add(newAuto)
auto.Rows.Add("98765", "buick", 2001)
auto.LoadDataRow(New Object() {"987654321xyz", "Jeep", 2002}, LoadOption.OverwriteChanges)

 

 

1.1.3.2使用DataRowState查看DataRow对象的状态

Private Function GetDataRowInfo(ByVal row As DataRow, ByVal columnName As String) As String
　　Dim retVal As String = String.Format("RowState: {0} ", row.RowState) & vbCrLf
　　Dim versionString As String
　　For Each versionString In System.Enum.GetNames(GetType(DataRowVersion))
　　　　Dim version As DataRowVersion = CType(System.Enum.Parse(GetType(DataRowVersion), versionString), DataRowVersion)
　　　　If row.HasVersion(version) Then
　　　　　　retVal &= String.Format("Version: {0} value: {1}", version, row(columnName, version)) & vbCrLf
　　　　Else
　　　　　　retVal &= String.Format("Version: {0} does not exist.", version) & vbCrLf
　　　　End If
　　Next
　　Return retVal
End Function

 

1.1.4枚举DataTable对象

        Dim buffer As New System.Text.StringBuilder
        For Each dc As DataColumn In auto.Columns
            buffer.Append(String.Format("{0,15} ", dc.ColumnName))
        Next
        buffer.Append(vbCrLf)
        For Each dr As DataRow In auto.Rows
            For Each dc As DataColumn In auto.Columns
                buffer.Append(String.Format("{0,15} ", dr(dc)))
            Next
            buffer.Append(vbCrLf)
        Next

 1.1.5复制和克隆DataTable对象

 

        Dim copy As DataTable = auto.Copy()
        Dim clone As DataTable = auto.Clone()
        clone.ImportRow(auto.Rows(0))

 1.1.6使用DataTable对象处理XML数据

 

        auto.TableName = "Car"
        auto.Columns("Vin").ColumnMapping = MappingType.Attribute
        auto.Columns("Make").ColumnMapping = MappingType.Attribute
        auto.Columns("Year").ColumnMapping = MappingType.Attribute
        auto.Columns("Year and Make").ColumnMapping = MappingType.Hidden

 

 

下面的代码将XML文件读入一个新的DataTable对象中

 

        Dim xmlTable As New DataTable()
        xmlTable.ReadXml("C:\auto.xml")

 

 

 1.1.7将DataView对象作为窗口添加到DataTable对象中

1.1.7.1使用Sort属性对数据进行排序

 

        Dim view As New DataView(auto)
        view.Sort = "Make ASC, Year DESC"

 

 

1.1.7.2使用RowFilter和RowStateFilter属性实现精确查找

 

        Dim view As New DataView(auto)
        view.RowFilter = "Make like 'B%' and year > 2003"
        view.RowStateFilter = DataViewRowState.Deleted

 

1.1.7.3枚举DataView对象

 

        Dim view As New DataView(auto)
        view.RowFilter = "Make like 'B%' and year > 2003"
        view.RowStateFilter = DataViewRowState.Deleted
        Dim buffer As New System.Text.StringBuilder
        For Each dc As DataColumn In auto.Columns
            buffer.Append(String.Format("{0,15} ", dc.ColumnName))
        Next
        buffer.Append(vbCrLf)
        For Each dr As DataRowView In view
            For Each dc As DataColumn In auto.Columns
                buffer.Append(String.Format("{0,15} ", dr.Row(dc)))
            Next
        Next
        TextBox1.Text = buffer.ToString()

 

 1.1.7.4将一个DataView对象导出到一个新的DataTable对象中

 

Dim export As DataTable = view.ToTable("MyAutoTable", True, "Vin", "Make", "Year")

 

 

1.1.8使用一个DataSet对象处理大量数据

下面代码演示了如何创建一个包含两个DataTable对象的简单DataSet对象。

 

        Dim vendorData As New DataSet("VendorData")
        Dim vendor As DataTable = vendorData.Tables.Add("vendor")
        With vendor.Columns
            .Add("id", GetType(Guid))
            .Add("Name", GetType(String))
            .Add("Address1", GetType(String))
            .Add("Address2", GetType(String))
            .Add("city", GetType(String))
            .Add("state", GetType(String))
            .Add("zipCode", GetType(String))
            .Add("country", GetType(String))
        End With
        vendor.PrimaryKey = New DataColumn() {vendor.Columns("Id")}
        Dim part As DataTable = vendorData.Tables.Add("part")
        With part.Columns
            .Add("id", GetType(Guid))
            .Add("VendorId", GetType(Guid))
            .Add("partCode", GetType(String))
            .Add("PartDescription", GetType(String))
            .Add("Cost", GetType(Decimal))
            .Add("RetailPrice", GetType(Decimal))
        End With
        part.PrimaryKey = New DataColumn() {part.Columns("Id")}
        vendorData.Relations.Add("vendor_part", vendor.Columns("Id"), part.Columns("VendorId"))

 

1.1.9更特殊的类型化DataSet对象

1.1.10使用DataRelation对象导航家族树

 

        Dim vendorRow As DataRow = vendor.NewRow
        vendorRow("Id") = Guid.NewGuid()
        vendorRow("Name") = "Tailspin Toys"
        vendor.Rows.Add(vendorRow)
        Dim partRow As DataRow
        partRow = part.NewRow
        partRow("Id") = Guid.NewGuid
        partRow("VendorId") = vendorRow("Id")
        partRow("PartCode") = "WGT1"
        partRow("PartDescription") = "Widget 1 Description"
        partRow("Cost") = 10.0
        partRow("RetailPrice") = 12.32
        part.Rows.Add(partRow)
        partRow = part.NewRow
        partRow("Id") = Guid.NewGuid
        partRow("VendorId") = vendorRow("Id")
        partRow("PartCode") = "WGT2"
        partRow("PartDescription") = "Widget 2 Description"
        partRow("Cost") = 9.0
        partRow("RetailPrice") = 11.32
        part.Rows.Add(partRow)
        '从父表记录导航到子表记录
        Dim parts() As DataRow = vendorRow.GetChildRows("vendor_part")
        For Each dr As DataRow In parts
            TextBox1.AppendText("Part: " & dr("PartCode") & vbCrLf)
        Next
        '从子表记录导航到父表记录
        Dim parentRow As DataRow = part.Rows(1).GetParentRow("vendor_part")
        TextBox1.AppendText("Vendor: " & parentRow("Name") & vbCrLf)

 

 

1.1.10.1创建外键约束

 

vendorData.Relations.Add("vendor_part", vendor.Columns("id"), part.Columns("vendorId"), True)

 

 

1.1.10.2 级联删除和级联更新

 

Dim fk As ForeignKeyConstraint = part.Constraints("vendor_part")
fk.DeleteRule = Rule.None

 

 

1.1.11 序列化和解序列化DataSet对象

1.1.11.1 将DataSet对象序列化为XML文件

 

vendorData.WriteXml("C:\vendors.xml", XmlWriteMode.IgnoreSchema)

 

 

下面代码说明了如何修改xml格式

 

        vendorData.Relations("vendor_part").Nested = True
        For Each dt As DataTable In vendorData.Tables
            For Each dc As DataColumn In dt.Columns
                If Not dc.DataType.Equals(GetType(Guid)) Then
                    dc.ColumnMapping = MappingType.Attribute
                End If
            Next
        Next

 

 

将数据类型信息保存在XML文件中

 

vendorData.WriteXml("C:\vendors.xml", XmlWriteMode.WriteSchema)

 

 

 

将数据类型信息保存在XSD文件中

 

vendorData.WriteXmlSchema("C:\vendorSchema.xsd")

 

 

1.1.11.2 将一个已经修改的DataSet对象序列化为一个DiffGram

DiffGram包含DataSet对象中所有数据，包括DataRow对象的Original信息

 

vendorData.WriteXml("C:\vendors.xml", XmlWriteMode.DiffGram)

 

 

1.1.11.3从xml文档中解序列化DataSet对象

 

vendorData.ReadXmlSchema("C:\vendorSchema.xsd")
vendorData.ReadXml("C:\vendors.xml", XmlReadMode.IgnoreSchema)

 

1.11.1.4 将DataSet对象序列化为二进制数据

 

Imports System.Runtime.Serialization.Formatters.Binary
Imports System.IO
Dim fs As New FileStream("C:\vendors.bin", FileMode.Create)
Dim fmt As New BinaryFormatter()
fmt.Serialize(fs, vendorData)
fs.Close()

 

在上一个代码的开始部分添加下面一行代码，将使该文件保存为真正的二进制数据（不包含嵌套的XML文件）

 

vendorData.RemotingFormat = SerializationFormat.Binary

 

 

1.1.11.5 从二进制数据中解序一个DataSet对象

 

Dim fs As New FileStream("C:\vendors.bin", FileMode.Open)
Dim fmt As New BinaryFormatter()
vendorData = CType(fmt.Deserialize(fs), DataSet)
fs.Close()

 1.1.12 使用Merge方法合并DataSet数据

 

        '创建一个初始化数据集
        Dim masterData As New DataSet("Sales")
        Dim person As DataTable = masterData.Tables.Add("Person")
        With person.Columns
            .Add("Id", GetType(Guid))
            .Add("Name", GetType(String))
        End With
        person.PrimaryKey = New DataColumn() {person.Columns("Id")}
        '创建一个临时数据集，并做一些修改
        Dim tempData As DataSet = masterData.Copy
        Dim tempPerson As DataTable = tempData.Tables("Person")
        Dim









														

返回