5.1 理解并发问题
UPDATE [DBO].[TblBookList]
SET [ISBN]=@ISBN,
[BookName]=@BookName,
WHERE [ISBN]=@Original_ISBN
AND [BookName]=@Original_BookName
AND [Quantity]=@Original_Quantity
SET [ISBN]=@ISBN,
[BookName]=@BookName,
WHERE [ISBN]=@Original_ISBN
AND [BookName]=@Original_BookName
AND [Quantity]=@Original_Quantity
解决并发冲突
5.2 设计非连接数据
SCOPE_IDENTITY
INSERT INTO [TblAuthor]([SSN],[LastName],[FirstName])
VALUES(@SSN,@LastName,@FirstName);
SELECT SSN,LastName,FirstName FROM TblAuthor
WHERE (Id=SCOPE_IDENTITY());
VALUES(@SSN,@LastName,@FirstName);
SELECT SSN,LastName,FirstName FROM TblAuthor
WHERE (Id=SCOPE_IDENTITY());
如何初始化GUID
代码
Dim WithEvents salesSurrogateGuidKeyDs As DataSet
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
For Each dt As DataTable In salesSurrogateGuidKeyDs.Tables
If Not dt.Columns("Id") Is Nothing Then
AddHandler dt.TableNewRow, AddressOf InitializeGuid
End If
Next
End Sub
Sub InitializeGuid(ByVal sender As System.Object, ByVal e As DataTableNewRowEventArgs)
If TypeOf e.Row("Id") Is DBNull Then
e.Row("Id") = Guid.NewGuid()
End If
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
For Each dt As DataTable In salesSurrogateGuidKeyDs.Tables
If Not dt.Columns("Id") Is Nothing Then
AddHandler dt.TableNewRow, AddressOf InitializeGuid
End If
Next
End Sub
Sub InitializeGuid(ByVal sender As System.Object, ByVal e As DataTableNewRowEventArgs)
If TypeOf e.Row("Id") Is DBNull Then
e.Row("Id") = Guid.NewGuid()
End If
End Sub
5.2.1 应加载什么数据
5.2.2 选择主键
5.3 GUID的用法
5.3.1 复制粘贴数据
5.3.2 在非连接表中使用同名的主键列
5.3.3 查找数据库中的GUID
代码
CREATE PROCEDURE dbo.uspGetDataForId
(
@id uniqueidentifier
)
AS
SET NOCOUNT ON
--NOTE: this proc assume that all user tables have 'Tbl' prefix
--USAGE:in query analyser, type the following without the '--'
--EXEC uspGetDataForId 'C4831BB6-D71E-4930-BB10-88380F598479'
DECLARE @Tbl VARCHAR(2000)
DECLARE @SQL VARCHAR(2000)
IF OBJECT_ID('tempdb..#idTable')IS NOT NULL DROP TABLE #idTable
CREATE TABLE #idTable(
Id uniqueidentifier,
Count INT,
TableName VARCHAR(2000)
)
DECLARE table_cursor CURSOR
FOR SELECT TABLE_NAME FROM information_schema.TABLES
WHERE substring(TABLE_NAME,1,3)='Tbl'
OPEN TABLES_CURSOR
FETCH NEXT FROM TABLES_CURSOR INTO @Tbl
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS
WHERE TABLE_NAME=@tbl and column_name='Id')
BEGIN
SET @SQL='INSERT INTO #idTable SELECT id as ''Id'','
+ 'COUNT(*) as ''COUNT'','''+ @tbl +'''as ''TableName'' FROM '
+ @tbl +' WHERE id=''' + CONVERT(VARCHAR(2000),@id)
+ ''' goup by id'
EXEC(@SQL)
END
FETCH NEXT FROM TABLES_CURSOR INTO @tbl
END
CLOSE TABLES_CURSOR
DEALLOCATE TABLES_CURSOR
SELECT Id, TableName FROM #idTable WHERE count > 0
(
@id uniqueidentifier
)
AS
SET NOCOUNT ON
--NOTE: this proc assume that all user tables have 'Tbl' prefix
--USAGE:in query analyser, type the following without the '--'
--EXEC uspGetDataForId 'C4831BB6-D71E-4930-BB10-88380F598479'
DECLARE @Tbl VARCHAR(2000)
DECLARE @SQL VARCHAR(2000)
IF OBJECT_ID('tempdb..#idTable')IS NOT NULL DROP TABLE #idTable
CREATE TABLE #idTable(
Id uniqueidentifier,
Count INT,
TableName VARCHAR(2000)
)
DECLARE table_cursor CURSOR
FOR SELECT TABLE_NAME FROM information_schema.TABLES
WHERE substring(TABLE_NAME,1,3)='Tbl'
OPEN TABLES_CURSOR
FETCH NEXT FROM TABLES_CURSOR INTO @Tbl
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT * FROM information_schema.COLUMNS
WHERE TABLE_NAME=@tbl and column_name='Id')
BEGIN
SET @SQL='INSERT INTO #idTable SELECT id as ''Id'','
+ 'COUNT(*) as ''COUNT'','''+ @tbl +'''as ''TableName'' FROM '
+ @tbl +' WHERE id=''' + CONVERT(VARCHAR(2000),@id)
+ ''' goup by id'
EXEC(@SQL)
END
FETCH NEXT FROM TABLES_CURSOR INTO @tbl
END
CLOSE TABLES_CURSOR
DEALLOCATE TABLES_CURSOR
SELECT Id, TableName FROM #idTable WHERE count > 0
5.3.4 查找数据库中所有使用GUID的位置
代码
CREATE PROCEDURE dbo.uspGetUsagesForId
(
@id uniqueidentifier
)
AS
--NOTE: this proc assume that all user Tables have 'Tbl' prefix
--USAGE:in query analyser, type the following without the '--'
--EXEC uspGetDataForId 'C4831BB6-D71E-4930-BB10-88380F598479'
SET NOCOUNT ON
DECLARE @Tbl VARCHAR(2000)
DECLARE @sql VARCHAR(2000)
DECLARE @counter integer
IF OBJECT_ID('tempdb..#guidTable')IS NOT NULL DROP TABLE #guidTable
CREATE TABLE #guidTable(
Id uniqueidentifier,
Count INT,
TableName VARCHAR(2000)
)
DECLARE Tables_cursor CURSOR
FOR SELECT TABLE_NAME FROM information_schema.Tables
WHERE substring(TABLE_NAME,1,3)='Tbl'
OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor INTO @Tbl
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='INSERT INTO #guidTable SELECT id as ''Id'','
+ 'COUNT(*) as ''count'','''+ @Tbl +'''as ''TableName'' FROM '
+ @tbl + ' WHERE '+dbo.fnGetGuidWhereClause(@Tbl, @id)
+ ' GOUP BY ID '
EXEC(@sql)
SELECT @counter = COUNT(*) FROM #guidTable
IF @counter > 0
BEGIN
SET @sql ='SELECT ''' + @Tbl + ''' AS TABLE_NAME, * FROM ' + @Tbl
+ ' WHERE ' + dbo.fnGetGuidWhereClause(@Tbl, @id)
EXEC(@sql)
END
DELETE FROM #guidTable
FETCH NEXT FROM Tables_cursor INTO @tbl
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
(
@id uniqueidentifier
)
AS
--NOTE: this proc assume that all user Tables have 'Tbl' prefix
--USAGE:in query analyser, type the following without the '--'
--EXEC uspGetDataForId 'C4831BB6-D71E-4930-BB10-88380F598479'
SET NOCOUNT ON
DECLARE @Tbl VARCHAR(2000)
DECLARE @sql VARCHAR(2000)
DECLARE @counter integer
IF OBJECT_ID('tempdb..#guidTable')IS NOT NULL DROP TABLE #guidTable
CREATE TABLE #guidTable(
Id uniqueidentifier,
Count INT,
TableName VARCHAR(2000)
)
DECLARE Tables_cursor CURSOR
FOR SELECT TABLE_NAME FROM information_schema.Tables
WHERE substring(TABLE_NAME,1,3)='Tbl'
OPEN Tables_cursor
FETCH NEXT FROM Tables_cursor INTO @Tbl
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='INSERT INTO #guidTable SELECT id as ''Id'','
+ 'COUNT(*) as ''count'','''+ @Tbl +'''as ''TableName'' FROM '
+ @tbl + ' WHERE '+dbo.fnGetGuidWhereClause(@Tbl, @id)
+ ' GOUP BY ID '
EXEC(@sql)
SELECT @counter = COUNT(*) FROM #guidTable
IF @counter > 0
BEGIN
SET @sql ='SELECT ''' + @Tbl + ''' AS TABLE_NAME, * FROM ' + @Tbl
+ ' WHERE ' + dbo.fnGetGuidWhereClause(@Tbl, @id)
EXEC(@sql)
END
DELETE FROM #guidTable
FETCH NEXT FROM Tables_cursor INTO @tbl
END
CLOSE Tables_cursor
DEALLOCATE Tables_cursor
5.4 建立冲突解决界面
5.4.1 创建项目
5.4.2 扩展类型化DataSet类CustomerDataSet
代码
Imports System.Data
Partial Class CustomerDataSet
Private createDefaultGuidForNewRows As Boolean = False
Public Sub CreateDefaultGuids()
If createDefaultGuidForNewRows Then Exit Sub
createDefaultGuidForNewRows = True
For Each dt As DataTable In Me.Tables
If Not dt.Columns("Id") Is Nothing Then
AddHandler dt.TableNewRow, AddressOf TableNewRow
End If
Next
End Sub
Private Sub TableNewRow(ByVal sender As Object, ByVal e As DataTableNewRowEventArgs)
If TypeOf e.Row("Id") Is DBNull Then
e.Row("Id") = Guid.NewGuid()
End If
End Sub
End Class
Partial Class CustomerDataSet
Private createDefaultGuidForNewRows As Boolean = False
Public Sub CreateDefaultGuids()
If createDefaultGuidForNewRows Then Exit Sub
createDefaultGuidForNewRows = True
For Each dt As DataTable In Me.Tables
If Not dt.Columns("Id") Is Nothing Then
AddHandler dt.TableNewRow, AddressOf TableNewRow
End If
Next
End Sub
Private Sub TableNewRow(ByVal sender As Object, ByVal e As DataTableNewRowEventArgs)
If TypeOf e.Row("Id") Is DBNull Then
e.Row("Id") = Guid.NewGuid()
End If
End Sub
End Class
窗体对象
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
CustomerDataSet.CreateDefaultGuids()
End Sub
CustomerDataSet.CreateDefaultGuids()
End Sub
5.4.3 扩展TableAdapter类TblCustomerTableAdapter以导出ContinueUpdateOnError属性
代码
Namespace CustomerDataSetTableAdapters
Partial Public Class TblCustomerTableAdapter
Public Property ContinueUpdateOnError() As Boolean
Get
Return Adapter.ContinueUpdateOnError
End Get
Set(ByVal value As Boolean)
Adapter.ContinueUpdateOnError = value
End Set
End Property
End Class
End Namespace
Partial Public Class TblCustomerTableAdapter
Public Property ContinueUpdateOnError() As Boolean
Get
Return Adapter.ContinueUpdateOnError
End Get
Set(ByVal value As Boolean)
Adapter.ContinueUpdateOnError = value
End Set
End Property
End Class
End Namespace
窗体对象
代码
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
CustomerDataSet.CreateDefaultGuids()
TblCustomerTableAdapter.ContinueUpdateOnError = True
End Sub
CustomerDataSet.CreateDefaultGuids()
TblCustomerTableAdapter.ContinueUpdateOnError = True
End Sub
5.4.4 使DataSet非连接对象与数据库服务器同步
代码
Private Sub SyncWithDatabaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SyncWithDatabaseToolStripMenuItem.Click
TblCustomerTableAdapter.Update(CustomerDataSet.TblCustomer)
If CustomerDataSet.HasErrors Then
StatusStrip1.Text = "Partial synchronization with concurrency errors."
Else
'get current database data
TblCustomerTableAdapter.Fill(CustomerDataSet.TblCustomer)
StatusStrip1.Text = "Database synchronized."
End If
TblCustomerDataGridView.Refresh()
End Sub
TblCustomerTableAdapter.Update(CustomerDataSet.TblCustomer)
If CustomerDataSet.HasErrors Then
StatusStrip1.Text = "Partial synchronization with concurrency errors."
Else
'get current database data
TblCustomerTableAdapter.Fill(CustomerDataSet.TblCustomer)
StatusStrip1.Text = "Database synchronized."
End If
TblCustomerDataGridView.Refresh()
End Sub
5.4.5 创建冲突解决界面
代码
Public Class frmConflict
Public Sub New(ByVal currentDataRow As DataRow, ByVal currentDatabaseDataRow As DataRow)
MyBase.New()
' 此调用是 Windows 窗体设计器所必需的。
InitializeComponent()
' 在 InitializeComponent() 调用之后添加任何初始化。
Me.CurrentDataRow = currentDataRow
Me.FinalDatabaseDataRow = currentDatabaseDataRow
End Sub
Public Property FinalDatabaseDataRow() As DataRow
Get
Return _FinalDatabaseDataRow
End Get
Set(ByVal value As DataRow)
_FinalDatabaseDataRow = value
End Set
End Property
Dim _FinalDatabaseDataRow As DataRow
Public Property CurrentDataRow() As DataRow
Get
Return _CurrentDataRow
End Get
Set(ByVal value As DataRow)
_CurrentDataRow = value
End Set
End Property
Dim _CurrentDataRow As DataRow
Sub PopulateTab(ByVal tab As TabPage, ByVal dataRow As DataRow, ByVal dataRowVersion As DataRowVersion, ByVal m_ReadOnly As Boolean)
Const verticalSpacing As Integer = 30
Const labelWidth As Integer = 50
Const horizontalSpacing As Integer = 10
Const buttonWidth As Integer = 100
Const buttonHeight As Integer = 20
For col As Integer = 0 To dataRow.ItemArray.Length - 1
Dim val As Object = dataRow(col, dataRowVersion)
Dim label As New Label
tab.Controls.Add(label)
label.Text = dataRow(col, dataRowVersion)
label.Top = (col + 1) * verticalSpacing
label.Width = labelWidth
label.Visible = True
Dim textbox As New TextBox
tab.Controls.Add(textbox)
With textbox
.Text = val.ToString
.Top = (col + 1) * verticalSpacing
.Left = horizontalSpacing * 2 + labelWidth
.Width = tab.Width - textbox.Left - buttonWidth - horizontalSpacing * 2
.Name = tab.Name + label.Text
.ReadOnly = m_ReadOnly
.Visible = True
.Anchor = AnchorStyles.Left Or AnchorStyles.Top Or AnchorStyles.Right
End With
If tab.Name = "tabFinal" Then Continue For
Dim btn As New Button
tab.Controls.Add(btn)
With btn
.Text = "Copy to Final"
.Left = textbox.Left + textbox.Width + horizontalSpacing
.Top = (col + 1) * verticalSpacing
.Height = buttonHeight
.Visible = True
.Anchor = AnchorStyles.Top Or AnchorStyles.Right
AddHandler .Click, AddressOf CopyToFinal
Dim propertyBag As New ArrayList
propertyBag.Add(dataRow.Table.Columns(col))
propertyBag.Add(textbox)
.Tag = propertyBag
End With
Next
End Sub
Private Sub CopyToFinal(ByVal sender As Object, ByVal e As EventArgs)
Dim btn As Button = sender
Dim propertyBag As ArrayList = CType(btn.Tag, ArrayList)
Dim dc As DataColumn = CType(propertyBag(0), DataColumn)
Dim textBox As TextBox = CType(propertyBag(1), TextBox)
TabFinal.Controls(TabFinal.Name + dc.ColumnName).Text = textBox.Text
End Sub
Private Sub frmConflict_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
PopulateTab(TabCurrent, CurrentDataRow, DataRowVersion.Current, True)
PopulateTab(TabOriginal, CurrentDataRow, DataRowVersion.Original, True)
PopulateTab(TabCurrentDb, FinalDatabaseDataRow, DataRowVersion.Original, True)
PopulateTab(TabFinal, FinalDatabaseDataRow, DataRowVersion.Current, False)
End Sub
Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click
For Each dc As DataColumn In FinalDatabaseDataRow.Table.Columns
FinalDatabaseDataRow(dc) = TabFinal.Controls(TabFinal.Name + dc.ColumnName).Text
Next
End Sub
End Class
Public Sub New(ByVal currentDataRow As DataRow, ByVal currentDatabaseDataRow As DataRow)
MyBase.New()
' 此调用是 Windows 窗体设计器所必需的。
InitializeComponent()
' 在 InitializeComponent() 调用之后添加任何初始化。
Me.CurrentDataRow = currentDataRow
Me.FinalDatabaseDataRow = currentDatabaseDataRow
End Sub
Public Property FinalDatabaseDataRow() As DataRow
Get
Return _FinalDatabaseDataRow
End Get
Set(ByVal value As DataRow)
_FinalDatabaseDataRow = value
End Set
End Property
Dim _FinalDatabaseDataRow As DataRow
Public Property CurrentDataRow() As DataRow
Get
Return _CurrentDataRow
End Get
Set(ByVal value As DataRow)
_CurrentDataRow = value
End Set
End Property
Dim _CurrentDataRow As DataRow
Sub PopulateTab(ByVal tab As TabPage, ByVal dataRow As DataRow, ByVal dataRowVersion As DataRowVersion, ByVal m_ReadOnly As Boolean)
Const verticalSpacing As Integer = 30
Const labelWidth As Integer = 50
Const horizontalSpacing As Integer = 10
Const buttonWidth As Integer = 100
Const buttonHeight As Integer = 20
For col As Integer = 0 To dataRow.ItemArray.Length - 1
Dim val As Object = dataRow(col, dataRowVersion)
Dim label As New Label
tab.Controls.Add(label)
label.Text = dataRow(col, dataRowVersion)
label.Top = (col + 1) * verticalSpacing
label.Width = labelWidth
label.Visible = True
Dim textbox As New TextBox
tab.Controls.Add(textbox)
With textbox
.Text = val.ToString
.Top = (col + 1) * verticalSpacing
.Left = horizontalSpacing * 2 + labelWidth
.Width = tab.Width - textbox.Left - buttonWidth - horizontalSpacing * 2
.Name = tab.Name + label.Text
.ReadOnly = m_ReadOnly
.Visible = True
.Anchor = AnchorStyles.Left Or AnchorStyles.Top Or AnchorStyles.Right
End With
If tab.Name = "tabFinal" Then Continue For
Dim btn As New Button
tab.Controls.Add(btn)
With btn
.Text = "Copy to Final"
.Left = textbox.Left + textbox.Width + horizontalSpacing
.Top = (col + 1) * verticalSpacing
.Height = buttonHeight
.Visible = True
.Anchor = AnchorStyles.Top Or AnchorStyles.Right
AddHandler .Click, AddressOf CopyToFinal
Dim propertyBag As New ArrayList
propertyBag.Add(dataRow.Table.Columns(col))
propertyBag.Add(textbox)
.Tag = propertyBag
End With
Next
End Sub
Private Sub CopyToFinal(ByVal sender As Object, ByVal e As EventArgs)
Dim btn As Button = sender
Dim propertyBag As ArrayList = CType(btn.Tag, ArrayList)
Dim dc As DataColumn = CType(propertyBag(0), DataColumn)
Dim textBox As TextBox = CType(propertyBag(1), TextBox)
TabFinal.Controls(TabFinal.Name + dc.ColumnName).Text = textBox.Text
End Sub
Private Sub frmConflict_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
PopulateTab(TabCurrent, CurrentDataRow, DataRowVersion.Current, True)
PopulateTab(TabOriginal, CurrentDataRow, DataRowVersion.Original, True)
PopulateTab(TabCurrentDb, FinalDatabaseDataRow, DataRowVersion.Original, True)
PopulateTab(TabFinal, FinalDatabaseDataRow, DataRowVersion.Current, False)
End Sub
Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click
For Each dc As DataColumn In FinalDatabaseDataRow.Table.Columns
FinalDatabaseDataRow(dc) = TabFinal.Controls(TabFinal.Name + dc.ColumnName).Text
Next
End Sub
End Class
5.4.6 调用冲突解决界面
代码
Private Sub ResolveConcurrencyErrorsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles ResolveConcurrencyErrorsToolStripMenuItem.Click
If CustomerDataSet.TblCustomer.HasErrors Then
Dim refreshCustomer As New CustomerDataSet
TblCustomerTableAdapter.Fill(refreshCustomer.TblCustomer)
For Each dr As DataRow In CustomerDataSet.TblCustomer.GetErrors
Dim currentDb As DataRow = refreshCustomer.TblCustomer.Rows.Find(dr("Id"))
Using conflict As New frmConflict(dr, currentDb)
If conflict.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
dr.ClearErrors()
TblCustomerTableAdapter.Update(conflict.FinalDatabaseDataRow)
CustomerDataSet.TblCustomer.LoadDataRow( _
conflict.FinalDatabaseDataRow.ItemArray, LoadOption.OverwriteChanges)
StatusStrip1.Text = "Single row updated. "
Else
StatusStrip1.Text = "Single row update cancelled."
End If
End Using
Next
TblCustomerDataGridView.Refresh()
End If
End Sub
Handles ResolveConcurrencyErrorsToolStripMenuItem.Click
If CustomerDataSet.TblCustomer.HasErrors Then
Dim refreshCustomer As New CustomerDataSet
TblCustomerTableAdapter.Fill(refreshCustomer.TblCustomer)
For Each dr As DataRow In CustomerDataSet.TblCustomer.GetErrors
Dim currentDb As DataRow = refreshCustomer.TblCustomer.Rows.Find(dr("Id"))
Using conflict As New frmConflict(dr, currentDb)
If conflict.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
dr.ClearErrors()
TblCustomerTableAdapter.Update(conflict.FinalDatabaseDataRow)
CustomerDataSet.TblCustomer.LoadDataRow( _
conflict.FinalDatabaseDataRow.ItemArray, LoadOption.OverwriteChanges)
StatusStrip1.Text = "Single row updated. "
Else
StatusStrip1.Text = "Single row update cancelled."
End If
End Using
Next
TblCustomerDataGridView.Refresh()
End If
End Sub
5.4.7 使用冲突解决界面改正并发错误
5.4.8 建立一个更好的冲突解决界面
