6.1 导航关系
代码
'使用DataRelation对象在DataRow父子对象之间实现双向导航
Dim ds1 As New DataSet
'create tables
Dim dt1 As DataTable = ds1.Tables.Add("dt1")
Dim dt2 As DataTable = ds1.Tables.Add("dt2")
'create columns
Dim parentPk As DataColumn = dt1.Columns.Add("pk", GetType(Integer))
dt2.Columns.Add("pk", GetType(Integer))
Dim childFk As DataColumn = dt2.Columns.Add("Fk", GetType(Integer))
'create relation
Dim rel As DataRelation = New DataRelation("dt1dt2", parentPk, childFk)
ds1.Relations.Add(rel)
'add some data
dt1.Rows.Add(1)
dt1.Rows.Add(2)
dt1.Rows.Add(3)
dt1.Rows.Add(4)
dt1.Rows.Add(5)
dt2.Rows.Add(1, 1)
dt2.Rows.Add(2, 1)
dt2.Rows.Add(3, 2)
dt2.Rows.Add(4, 2)
dt2.Rows.Add(5, 2)
'get a parent row
Dim parentRow As DataRow = dt1.Rows(1)
'get children
Dim children As DataRow() = parentRow.GetChildRows(rel)
'get parent again
Dim myParent As DataRow = children(0).GetParentRow(rel)
Dim ds1 As New DataSet
'create tables
Dim dt1 As DataTable = ds1.Tables.Add("dt1")
Dim dt2 As DataTable = ds1.Tables.Add("dt2")
'create columns
Dim parentPk As DataColumn = dt1.Columns.Add("pk", GetType(Integer))
dt2.Columns.Add("pk", GetType(Integer))
Dim childFk As DataColumn = dt2.Columns.Add("Fk", GetType(Integer))
'create relation
Dim rel As DataRelation = New DataRelation("dt1dt2", parentPk, childFk)
ds1.Relations.Add(rel)
'add some data
dt1.Rows.Add(1)
dt1.Rows.Add(2)
dt1.Rows.Add(3)
dt1.Rows.Add(4)
dt1.Rows.Add(5)
dt2.Rows.Add(1, 1)
dt2.Rows.Add(2, 1)
dt2.Rows.Add(3, 2)
dt2.Rows.Add(4, 2)
dt2.Rows.Add(5, 2)
'get a parent row
Dim parentRow As DataRow = dt1.Rows(1)
'get children
Dim children As DataRow() = parentRow.GetChildRows(rel)
'get parent again
Dim myParent As DataRow = children(0).GetParentRow(rel)
6.2 创建约束
6.3 更新数据：引入访问层
6.3.1 获取关系
代码
IF EXISTS(
SELECT * FROM dbo.sysobjects
WHERE id=OBJECT_ID('dbo.uspGetSqlRelationships')
AND OBJECTPROPERTY(id,'IsProcedure')=1)
DROP PROCEDURE dbo.uspGetSqlRelationships
CREATE PROCEDURE dbo.uspGetSqlRelationships
AS
IF OBJECT_ID('tempdb..#temp')IS NOT NULL DROP TABLE #temp
SELECT r_obj.name as relationshipName,
parent_obj.name as Parent,
child_obj.name as child,
CONVERT(nvarchar(1000),'') as pk,
CONVERT(nvarchar(1000),'') as fk,
r.fkeyid,
r.rkeyid,
r.constid
INTO #temp
FROM dbo.sysobjects r_obj
INNER JOIN dbo.sysreferences r ON r_obj.id=r.constid
INNER JOIN dbo.sysobjects parent_obj ON r.rkeyid=parent_obj.Id
INNER JOIN dbo.sysobjects child_obj ON r_obj.parent_obj=child_obj.Id
INNER JOIN dbo.sysforeignkeys fkeys ON fkeys.constid=r.constid
INNER JOIN dbo.syscolumns cols ON cols.id=fkeys.fkeyid and cols.colid=fkeys.fkey
WHERE r_obj.xtype='F' AND (r_obj.status & 0x100)=0
DECLARE @PK VARCHAR(1000), @FK VARCHAR(1000)
DECLARE @fkeyid int,@rkeyid int, @cnstid INT
DECLARE @keys NVARCHAR(2126)
DECLARE @cnstname SYSNAME
DECLARE @cnstdes nvarchar(4000)
DECLARE @cnsttype CHARACTER(2)
DECLARE @relName VARCHAR(1000)
DECLARE tempTable CURSOR LOCAL FOR
SELECT relationshipName,pk,fk,fkeyid,rkeyid,constid FROM #temp
open tempTable
FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@constid
WHILE @@FETCH_STATUS>=0
BEGIN
DECLARE ms_crs_fkey cursor local FOR
SELECT fkey,rkey from sysforeignkeys WHERE constid=@cnstid
OPEN ms_crs_fkey
FETCH ms_crs_fkey into @fkey,@rkey
SELECT @keys=col_name(@fkeyid,@fkeycol)
SELECT @cnstdes=col_name(@rkeyid,@rkeycol)
FETCH ms_crs_fkey into @fkey,@rkey
WHILE @@FETCH_STATUS>=0
BEGIN
SELECT @keys=@keys+','+col_name(@fkeyid,@fkeycol)
SELECT @cnstdes=@cnstdes+','+col_name(@rkeyid,@rkeycol)
FETCH ms_crs_fkey into @fkey,@rkey
END
DEALLOCATE ms_crs_fkey
UPDATE #temp
SET pk=@cnstdes,fk=@keys
WHERE relationshipName=@relName
FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@constid
END
DEALLOCATE tempTable
SELECT DISTINCT relationshipName, Parent, child,pk,fk from #temp
SELECT * FROM dbo.sysobjects
WHERE id=OBJECT_ID('dbo.uspGetSqlRelationships')
AND OBJECTPROPERTY(id,'IsProcedure')=1)
DROP PROCEDURE dbo.uspGetSqlRelationships
CREATE PROCEDURE dbo.uspGetSqlRelationships
AS
IF OBJECT_ID('tempdb..#temp')IS NOT NULL DROP TABLE #temp
SELECT r_obj.name as relationshipName,
parent_obj.name as Parent,
child_obj.name as child,
CONVERT(nvarchar(1000),'') as pk,
CONVERT(nvarchar(1000),'') as fk,
r.fkeyid,
r.rkeyid,
r.constid
INTO #temp
FROM dbo.sysobjects r_obj
INNER JOIN dbo.sysreferences r ON r_obj.id=r.constid
INNER JOIN dbo.sysobjects parent_obj ON r.rkeyid=parent_obj.Id
INNER JOIN dbo.sysobjects child_obj ON r_obj.parent_obj=child_obj.Id
INNER JOIN dbo.sysforeignkeys fkeys ON fkeys.constid=r.constid
INNER JOIN dbo.syscolumns cols ON cols.id=fkeys.fkeyid and cols.colid=fkeys.fkey
WHERE r_obj.xtype='F' AND (r_obj.status & 0x100)=0
DECLARE @PK VARCHAR(1000), @FK VARCHAR(1000)
DECLARE @fkeyid int,@rkeyid int, @cnstid INT
DECLARE @keys NVARCHAR(2126)
DECLARE @cnstname SYSNAME
DECLARE @cnstdes nvarchar(4000)
DECLARE @cnsttype CHARACTER(2)
DECLARE @relName VARCHAR(1000)
DECLARE tempTable CURSOR LOCAL FOR
SELECT relationshipName,pk,fk,fkeyid,rkeyid,constid FROM #temp
open tempTable
FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@constid
WHILE @@FETCH_STATUS>=0
BEGIN
DECLARE ms_crs_fkey cursor local FOR
SELECT fkey,rkey from sysforeignkeys WHERE constid=@cnstid
OPEN ms_crs_fkey
FETCH ms_crs_fkey into @fkey,@rkey
SELECT @keys=col_name(@fkeyid,@fkeycol)
SELECT @cnstdes=col_name(@rkeyid,@rkeycol)
FETCH ms_crs_fkey into @fkey,@rkey
WHILE @@FETCH_STATUS>=0
BEGIN
SELECT @keys=@keys+','+col_name(@fkeyid,@fkeycol)
SELECT @cnstdes=@cnstdes+','+col_name(@rkeyid,@rkeycol)
FETCH ms_crs_fkey into @fkey,@rkey
END
DEALLOCATE ms_crs_fkey
UPDATE #temp
SET pk=@cnstdes,fk=@keys
WHERE relationshipName=@relName
FETCH tempTable into @relName,@pk,@fk,@fkeyid,@rkeyid,@constid
END
DEALLOCATE tempTable
SELECT DISTINCT relationshipName, Parent, child,pk,fk from #temp
6.3.2 获取表列
代码
IF EXISTS(
SELECT * FROM dbo.sysobjects
WHERE id=OBJECT_ID('dbo.uspGetSqlTables')
AND OBJECTPROPERTY(id,'IsProcedure')=1)
DROP PROCEDURE dbo.uspGetSqlTables
CREATE PROCEDURE dbo.uspGetSqlTables
AS
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE table_type='BASE TABLE'
and TABLE_NAME<>'dtproperties'
and TABLE_NAME<>'sysdiagrams'
SELECT * FROM dbo.sysobjects
WHERE id=OBJECT_ID('dbo.uspGetSqlTables')
AND OBJECTPROPERTY(id,'IsProcedure')=1)
DROP PROCEDURE dbo.uspGetSqlTables
CREATE PROCEDURE dbo.uspGetSqlTables
AS
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE table_type='BASE TABLE'
and TABLE_NAME<>'dtproperties'
and TABLE_NAME<>'sysdiagrams'
6.3.3 排序表列
代码
Imports System.Configuration
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Text
Public Class DataAccess
Public Shared Property Instance() As DataAccess
Get
Return _instance
End Get
Set(ByVal value As DataAccess)
_instance = value
End Set
End Property
Private Shared _instance As DataAccess
Public Property OrderedTableList() As DataTable
Get
Return _orderedTableList
End Get
Set(ByVal value As DataTable)
_orderedTableList = value
End Set
End Property
Private _orderedTableList As DataTable
Private Sub BuildOrderedTableList()
Dim sourceTableList As DataTable = GetTable("dbo.uspGetSqlTables")
Dim sourceRelationList As DataTable = GetTable("dbo.uspGetSqlRelationships")
OrderedTableList = sourceTableList.Clone
OrderedTableList.TableName = "OrderedTableList"
Dim iCounter As Integer = 0
While iCounter < sourceRelationList.Rows.Count
If sourceRelationList.Rows(iCounter)("child").ToString = _
sourceRelationList.Rows(iCounter)("parent").ToString Then
sourceRelationList.Rows.Remove(sourceRelationList.Rows(iCounter))
Else
iCounter += 1
End If
End While
Dim position As Integer = 0
Dim lastCount As Integer = sourceTableList.Rows.Count
While sourceTableList.Rows.Count <> 0
Dim findChild As String = String.Format("[child]='{0}'", sourceTableList.Rows(position)("TABLE_NAME"))
If sourceRelationList.Select(findChild).Length = 0 Then
Dim findParent As String = String.Format("[parent]='{0}'", sourceTableList.Rows(position)("TABLE_NAME"))
For Each parent As DataRow In sourceRelationList.Select(findParent)
sourceRelationList.Rows.Remove(parent)
Next
OrderedTableList.Rows.Add(sourceTableList.Rows(position).ItemArray)
sourceTableList.Rows.RemoveAt(position)
position = 0
Else
position += 1
If position = sourceTableList.Rows.Count Then
FindCircularErrors(sourceRelationList)
End If
lastCount = sourceTableList.Rows.Count
position = 0
End If
End While
End Sub
Private Sub FindCircularErrors(ByVal sourceRelationList As DataTable)
For Each currentRelation As DataRow In sourceRelationList.Rows
Dim beenThere As New ArrayList
If IsCircular(currentRelation, beenThere, sourceRelationList) Then
Dim pathBuilder As New StringBuilder
For Each relation As DataRow In beenThere
pathBuilder.AppendFormat("parent: {0,-35} child:{1,-35} Relationship:{2}" & vbCrLf, _
relation("parent"), relation("child"), relation("child"), relation("relationshipName"))
Next
Throw New InvalidConstraintException("Circular relationships exist in the database." & vbCrLf & pathBuilder.ToString)
End If
Next
End Sub
Private Function IsCircular(ByVal currentRelation As DataRow, ByVal beenThere As ArrayList, ByVal relations As DataTable) As Boolean
beenThere.Add(currentRelation)
For Each childRelation As DataRow In relations.Rows
If childRelation("Parent").ToString = currentRelation("child").ToString Then
If beenThere.Contains(childRelation) Then Return True
Dim currentPath As New ArrayList(beenThere)
If IsCircular(childRelation, currentPath, relations) Then
beenThere = currentPath
Return True
End If
End If
Next
Return False
End Function
Public Shared Function GetTable(ByVal storedProcedure As String) As DataTable
Dim cnString As ConnectionStringSettings
cnString = ConfigurationManager.ConnectionStrings("CustomerData")
Dim dt As New DataTable
Using connection As DbConnection = New SqlConnection
connection.ConnectionString = cnString.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProcedure
connection.Open()
dt.Load(cmd.ExecuteReader)
End Using
Return dt
End Function
End Class
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Text
Public Class DataAccess
Public Shared Property Instance() As DataAccess
Get
Return _instance
End Get
Set(ByVal value As DataAccess)
_instance = value
End Set
End Property
Private Shared _instance As DataAccess
Public Property OrderedTableList() As DataTable
Get
Return _orderedTableList
End Get
Set(ByVal value As DataTable)
_orderedTableList = value
End Set
End Property
Private _orderedTableList As DataTable
Private Sub BuildOrderedTableList()
Dim sourceTableList As DataTable = GetTable("dbo.uspGetSqlTables")
Dim sourceRelationList As DataTable = GetTable("dbo.uspGetSqlRelationships")
OrderedTableList = sourceTableList.Clone
OrderedTableList.TableName = "OrderedTableList"
Dim iCounter As Integer = 0
While iCounter < sourceRelationList.Rows.Count
If sourceRelationList.Rows(iCounter)("child").ToString = _
sourceRelationList.Rows(iCounter)("parent").ToString Then
sourceRelationList.Rows.Remove(sourceRelationList.Rows(iCounter))
Else
iCounter += 1
End If
End While
Dim position As Integer = 0
Dim lastCount As Integer = sourceTableList.Rows.Count
While sourceTableList.Rows.Count <> 0
Dim findChild As String = String.Format("[child]='{0}'", sourceTableList.Rows(position)("TABLE_NAME"))
If sourceRelationList.Select(findChild).Length = 0 Then
Dim findParent As String = String.Format("[parent]='{0}'", sourceTableList.Rows(position)("TABLE_NAME"))
For Each parent As DataRow In sourceRelationList.Select(findParent)
sourceRelationList.Rows.Remove(parent)
Next
OrderedTableList.Rows.Add(sourceTableList.Rows(position).ItemArray)
sourceTableList.Rows.RemoveAt(position)
position = 0
Else
position += 1
If position = sourceTableList.Rows.Count Then
FindCircularErrors(sourceRelationList)
End If
lastCount = sourceTableList.Rows.Count
position = 0
End If
End While
End Sub
Private Sub FindCircularErrors(ByVal sourceRelationList As DataTable)
For Each currentRelation As DataRow In sourceRelationList.Rows
Dim beenThere As New ArrayList
If IsCircular(currentRelation, beenThere, sourceRelationList) Then
Dim pathBuilder As New StringBuilder
For Each relation As DataRow In beenThere
pathBuilder.AppendFormat("parent: {0,-35} child:{1,-35} Relationship:{2}" & vbCrLf, _
relation("parent"), relation("child"), relation("child"), relation("relationshipName"))
Next
Throw New InvalidConstraintException("Circular relationships exist in the database." & vbCrLf & pathBuilder.ToString)
End If
Next
End Sub
Private Function IsCircular(ByVal currentRelation As DataRow, ByVal beenThere As ArrayList, ByVal relations As DataTable) As Boolean
beenThere.Add(currentRelation)
For Each childRelation As DataRow In relations.Rows
If childRelation("Parent").ToString = currentRelation("child").ToString Then
If beenThere.Contains(childRelation) Then Return True
Dim currentPath As New ArrayList(beenThere)
If IsCircular(childRelation, currentPath, relations) Then
beenThere = currentPath
Return True
End If
End If
Next
Return False
End Function
Public Shared Function GetTable(ByVal storedProcedure As String) As DataTable
Dim cnString As ConnectionStringSettings
cnString = ConfigurationManager.ConnectionStrings("CustomerData")
Dim dt As New DataTable
Using connection As DbConnection = New SqlConnection
connection.ConnectionString = cnString.ConnectionString
Dim cmd As DbCommand = connection.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = storedProcedure
connection.Open()
dt.Load(cmd.ExecuteReader)
End Using
Return dt
End Function
End Class
调用方法
代码
Private Sub btnGetOrderedTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetOrderedTables.Click
DataGridView1.DataSource = DataAccess.Instance.OrderedTableList
End Sub
DataGridView1.DataSource = DataAccess.Instance.OrderedTableList
End Sub
6.3.4 使用OrderedTableList对象执行更新操作
在DataAccess中添加BuildList
代码
Public Function BuildList(ByVal state As DataRowState, ByVal ds As DataSet) As ArrayList
Dim list As New ArrayList
For Each drTable As DataRow In OrderedTableList.Rows
Dim s As String = CType(drTable("TABLE_NAME"), String)
Dim dt As DataTable = ds.Tables(s)
If Not dt Is Nothing Then
list.Add(dt)
End If
Next
If state = DataRowState.Deleted Then
list.Reverse()
End If
Return list
End Function
Dim list As New ArrayList
For Each drTable As DataRow In OrderedTableList.Rows
Dim s As String = CType(drTable("TABLE_NAME"), String)
Dim dt As DataTable = ds.Tables(s)
If Not dt Is Nothing Then
list.Add(dt)
End If
Next
If state = DataRowState.Deleted Then
list.Reverse()
End If
Return list
End Function
创建DataAccess类的Update
代码
Public ReadOnly Property TableAdapterMappings() As Dictionary(Of String, TableAdapterBase)
Get
Return _tableAdapterMappings
End Get
End Property
Private _tableAdapterMappings As New Dictionary(Of String, TableAdapterBase)
Public Sub Update(ByVal ds As DataSet)
UpdateOperation(ds, DataRowState.Added)
UpdateOperation(ds, DataRowState.Modified)
UpdateOperation(ds, DataRowState.Deleted)
End Sub
Public Sub UpdateOperation(ByVal ds As DataSet, ByVal state As DataRowState)
Dim _buildList As ArrayList = BuildList(state, ds)
For Each table As DataTable In _buildList
Using tempTable As DataTable = table.GetChanges(state)
If Not tempTable Is Nothing Then
Dim ta As TableAdapterBase = TableAdapterMappings(table.TableName)
ta.UpdateTable(tempTable)
End If
End Using
Next
End Sub
Get
Return _tableAdapterMappings
End Get
End Property
Private _tableAdapterMappings As New Dictionary(Of String, TableAdapterBase)
Public Sub Update(ByVal ds As DataSet)
UpdateOperation(ds, DataRowState.Added)
UpdateOperation(ds, DataRowState.Modified)
UpdateOperation(ds, DataRowState.Deleted)
End Sub
Public Sub UpdateOperation(ByVal ds As DataSet, ByVal state As DataRowState)
Dim _buildList As ArrayList = BuildList(state, ds)
For Each table As DataTable In _buildList
Using tempTable As DataTable = table.GetChanges(state)
If Not tempTable Is Nothing Then
Dim ta As TableAdapterBase = TableAdapterMappings(table.TableName)
ta.UpdateTable(tempTable)
End If
End Using
Next
End Sub
TableAdapterBase的实现代码
代码
Imports System.Data.Common
Imports System.Reflection
Partial Public MustInherit Class TableAdapterBase
Inherits System.ComponentModel.Component
Public Sub New()
ContinueUpdateOnError = True
For Each tableMapping As DataTableMapping In ChildAdapter.TableMappings
DataAccess.Instance.TableAdapterMappings(tableMapping.DataSetTable) = Me
Next
End Sub
ReadOnly Property ChildAdapter() As DbDataAdapter
Get
Dim myAdapter As PropertyInfo = Me.GetType.GetProperty("Adapter", _
Reflection.BindingFlags.GetProperty Or BindingFlags.NonPublic Or BindingFlags.Instance)
Return CType(myAdapter.GetValue(Me, Nothing), DbDataAdapter)
End Get
End Property
Property ContinueUpdateOnError() As Boolean
Get
Return ChildAdapter.ContinueUpdateOnError
End Get
Set(ByVal value As Boolean)
ChildAdapter.ContinueUpdateOnError = value
End Set
End Property
Public Function UpdateTable(ByVal table As DataTable) As Integer
Return ChildAdapter.Update(table)
End Function
End Class
Imports System.Reflection
Partial Public MustInherit Class TableAdapterBase
Inherits System.ComponentModel.Component
Public Sub New()
ContinueUpdateOnError = True
For Each tableMapping As DataTableMapping In ChildAdapter.TableMappings
DataAccess.Instance.TableAdapterMappings(tableMapping.DataSetTable) = Me
Next
End Sub
ReadOnly Property ChildAdapter() As DbDataAdapter
Get
Dim myAdapter As PropertyInfo = Me.GetType.GetProperty("Adapter", _
Reflection.BindingFlags.GetProperty Or BindingFlags.NonPublic Or BindingFlags.Instance)
Return CType(myAdapter.GetValue(Me, Nothing), DbDataAdapter)
End Get
End Property
Property ContinueUpdateOnError() As Boolean
Get
Return ChildAdapter.ContinueUpdateOnError
End Get
Set(ByVal value As Boolean)
ChildAdapter.ContinueUpdateOnError = value
End Set
End Property
Public Function UpdateTable(ByVal table As DataTable) As Integer
Return ChildAdapter.Update(table)
End Function
End Class
6.3.5 测试关联更新
代码
Private custDataSet As New CustomerDataSet
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim Adapter As TableAdapterBase
Adapter = New CustomerDataSetTableAdapters.TblProductTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblSalesPersonTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblCustomerTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblLocationTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblOrderTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblOrderItemTableAdapter
Dim p1Id As Guid = Guid.NewGuid
Dim p2Id As Guid = Guid.NewGuid
Dim spId As Guid = Guid.NewGuid
Dim cId As Guid = Guid.NewGuid
Dim l1Id As Guid = Guid.NewGuid
Dim l2Id As Guid = Guid.NewGuid
Dim oId As Guid = Guid.NewGuid
Dim oi1Id As Guid = Guid.NewGuid
Dim oi2Id As Guid = Guid.NewGuid
With custDataSet
With .TblProduct
.LoadDataRow(New Object() {p1Id, "Widget 1", 123.45}, False)
.LoadDataRow(New Object() {p2Id, "Widget 2", 234.56}, False)
End With
.TblSalesPerson.LoadDataRow(New Object() {spId, "Joe"}, False)
.TblCustomer.LoadDataRow(New Object() {cId, spId, "Joe's Customer"}, False)
With .TblLocation
.LoadDataRow(New Object() { _
l1Id, cId, "HQ", "add1", "add2", "city", "State", "12345", "US"}, False)
.LoadDataRow(New Object() { _
l2Id, cId, "WAREHOUSE", "add1", "add2", "city", "State", "12345", "US"}, False)
End With
.TblOrder.LoadDataRow(New Object() {oId, cId, "1", l2Id, l1Id}, False)
With .TblOrderItem
.LoadDataRow(New Object() {oi1Id, oId, p1Id, 1, "Widget1", 1, 123.45}, False)
.LoadDataRow(New Object() {oi2Id, oId, p2Id, 2, "Widget2", 2, 234.56}, False)
End With
End With
DataAccess.Instance.Update(custDataSet)
MessageBox.Show("Customer Database Updated")
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim Adapter As TableAdapterBase
Adapter = New CustomerDataSetTableAdapters.TblProductTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblSalesPersonTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblCustomerTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblLocationTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblOrderTableAdapter
Adapter = New CustomerDataSetTableAdapters.TblOrderItemTableAdapter
Dim p1Id As Guid = Guid.NewGuid
Dim p2Id As Guid = Guid.NewGuid
Dim spId As Guid = Guid.NewGuid
Dim cId As Guid = Guid.NewGuid
Dim l1Id As Guid = Guid.NewGuid
Dim l2Id As Guid = Guid.NewGuid
Dim oId As Guid = Guid.NewGuid
Dim oi1Id As Guid = Guid.NewGuid
Dim oi2Id As Guid = Guid.NewGuid
With custDataSet
With .TblProduct
.LoadDataRow(New Object() {p1Id, "Widget 1", 123.45}, False)
.LoadDataRow(New Object() {p2Id, "Widget 2", 234.56}, False)
End With
.TblSalesPerson.LoadDataRow(New Object() {spId, "Joe"}, False)
.TblCustomer.LoadDataRow(New Object() {cId, spId, "Joe's Customer"}, False)
With .TblLocation
.LoadDataRow(New Object() { _
l1Id, cId, "HQ", "add1", "add2", "city", "State", "12345", "US"}, False)
.LoadDataRow(New Object() { _
l2Id, cId, "WAREHOUSE", "add1", "add2", "city", "State", "12345", "US"}, False)
End With
.TblOrder.LoadDataRow(New Object() {oId, cId, "1", l2Id, l1Id}, False)
With .TblOrderItem
.LoadDataRow(New Object() {oi1Id, oId, p1Id, 1, "Widget1", 1, 123.45}, False)
.LoadDataRow(New Object() {oi2Id, oId, p2Id, 2, "Widget2", 2, 234.56}, False)
End With
End With
DataAccess.Instance.Update(custDataSet)
MessageBox.Show("Customer Database Updated")
End Sub
