5 使用非连接数据-CSDN博客

5.1 理解并发问题

UPDATE [ DBO ] . [ TblBookList ]
     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 ());

如何初始化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

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

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

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

窗体对象

Private Sub Form1_Load( ByVal sender As Object , ByVal e As System.EventArgs) Handles Me .Load
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

窗体对象

代码

     Private Sub Form1_Load( ByVal sender As Object , ByVal e As System.EventArgs) Handles Me .Load
        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

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

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

5.4.7 使用冲突解决界面改正并发错误

5.4.8 建立一个更好的冲突解决界面

转载于:https://www.cnblogs.com/csl-office-vb-sql-net/archive/2010/02/06/1665152.html