第一章 ADO.NET非连接类概述
1.1DataTable对象
1.1.1添加DataColumn对象以创建一个模式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 创建主键列
1.1.3创建用于存储数据的DataRow对象
1.1.3.1向DataTable添加数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象的状态
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 clone As DataTable = auto.Clone()
clone.ImportRow(auto.Rows(0))
1.1.6使用DataTable对象处理XML数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象中
xmlTable.ReadXml("C:\auto.xml")
1.1.7将DataView对象作为窗口添加到DataTable对象中
1.1.7.1使用Sort属性对数据进行排序
view.Sort = "Make ASC, Year DESC"
1.1.7.2使用RowFilter和RowStateFilter属性实现精确查找
view.RowFilter = "Make like 'B%' and year > 2003"
view.RowStateFilter = DataViewRowState.Deleted
1.1.7.3枚举DataView对象
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象中
1.1.8使用一个DataSet对象处理大量数据
下面代码演示了如何创建一个包含两个DataTable对象的简单DataSet对象。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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对象导航家族树
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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创建外键约束
1.1.10.2 级联删除和级联更新
fk.DeleteRule = Rule.None
1.1.11 序列化和解序列化DataSet对象
1.1.11.1 将DataSet对象序列化为XML文件
下面代码说明了如何修改xml格式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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文件中
将数据类型信息保存在XSD文件中
1.1.11.2 将一个已经修改的DataSet对象序列化为一个DiffGram
DiffGram包含DataSet对象中所有数据,包括DataRow对象的Original信息
1.1.11.3从xml文档中解序列化DataSet对象
vendorData.ReadXml("C:\vendors.xml", XmlReadMode.IgnoreSchema)
1.11.1.4 将DataSet对象序列化为二进制数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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文件)
1.1.11.5 从二进制数据中解序一个DataSet对象
Dim fmt As New BinaryFormatter()
vendorData = CType(fmt.Deserialize(fs), DataSet)
fs.Close()
1.1.12 使用Merge方法合并DataSet数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 Joe As DataRow = tempPerson.Select("Name='Joe'")(0)
Dim joeid As Guid = CType(Joe("id"), Guid)
Joe("Name") = "joe in sales"
Dim order As DataTable = tempData.Tables.Add("Order")
With order.Columns
.Add("Id", GetType(Guid))
.Add("PersonId", GetType(Guid))
.Add("Amount", GetType(Decimal))
End With
order.PrimaryKey = New DataColumn() {order.Columns("Id")}
order.Rows.Add(Guid.NewGuid, joeid, 100)
masterData.Merge(tempData, False, MissingSchemaAction.AddWithKey)
1.2使用DataTableReader遍历数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 part As DataTable = masterData.Tables.Add("Part")
With part.Columns
.Add("id", GetType(Guid))
.Add("partName", GetType(String))
End With
part.PrimaryKey = New DataColumn() {part.Columns("Id")}
For i = 0 To 100
person.Rows.Add(Guid.NewGuid, "joe " & i.ToString)
part.Rows.Add(Guid.NewGuid, "part " & i.ToString)
Next
Dim rd As DataTableReader = masterData.CreateDataReader
While rd.Read
TextBox1.AppendText(rd("Name").ToString & vbCrLf)
End While
rd.NextResult()
While rd.Read
TextBox1.AppendText(rd("PartName").ToString & vbCrLf)
End While