使用的TableAdapter来更新数据库表与一个DataTable

问题描述：

我可以正确地更新与datatable一个SQL数据库表如下：使用的TableAdapter来更新数据库表与一个DataTable

Dim resultsDataTable As New DataTable() 
Dim dtpHExportDataTable As New DataTable() 
Dim cnString As String = <<<ConnectionString>>> 
Using cnSQL1 As New SqlConnection 
    cnSQL1.ConnectionString = cnString 
     Using adapter1 = New SqlDataAdapter("SELECT SampleNo, Results, Complete_Date, Dex_Row_Id " & "FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1) 
     Dim builder1 As New SqlCommandBuilder(adapter1) 
     adapter1.UpdateCommand = builder1.GetUpdateCommand() 
     Using New SqlCommandBuilder(adapter1) 
      adapter1.Fill(resultsDataTable) 
      resultsDataTable.PrimaryKey = New DataColumn() {resultsDataTable.Columns("Dex_Row_Id")} 
      dtpHExportDataTable = resultsDataTable.Clone() 
      AddResultsRow(dtpHExportDataTable, 13581, "4.4", "2015-01-01", 45598) 
      AddResultsRow(dtpHExportDataTable, 13590, "5.5", "2015-01-01", 45618) 
      AddResultsRow(dtpHExportDataTable, 13604, "6.6", "2015-01-01", 45655) 
      resultsDataTable.Merge(dtpHExportDataTable) 
      ShowResult(resultsDataTable) ‘looks perfect 
      adapter1.Update(resultsDataTable) ‘database table IS updated correctly 
     End Using 
    End Using 
End Using

然而，当我在上面的代码扩展到我的电子表格工作簿页面应用程序中，resultsDataTable （由DevExpress ShowResult（resultsDataTable）显示）使用上面的代码以及下面的代码显示正确的信息。但数据库表不使用以下代码更新：

Dim worksheet As Worksheet = SpreadsheetControl.Document.Worksheets.ActiveWorksheet 
    Dim range As Range = worksheet.Selection 
    Dim rangeHasHeaders As Boolean = True 
    ' Create a data table with column names obtained from the first row in a range if it has headers. 
    ' Column data types are obtained from cell value types of cells in the first data row of the worksheet range. 
    Dim resultsDataTable As New DataTable() 
    Dim dtpHExportDataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders) 
    Dim cnString As String = "<<<ConnectionString>>>" 
    Using cnSQL1 As New SqlConnection 
     cnSQL1.ConnectionString = cnString   
    Using adapter1 = New SqlDataAdapter("SELECT SampleNo, Results, Complete_Date, Dex_Row_Id " & "FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1) 
     Dim builder1 As New SqlCommandBuilder(adapter1) 
     adapter1.UpdateCommand = builder1.GetUpdateCommand() 
     Using New SqlCommandBuilder(adapter1) 
      adapter1.Fill(resultsDataTable) 
      resultsDataTable.PrimaryKey = New DataColumn() {resultsDataTable.Columns("Dex_Row_Id")} 
      dtpHExportDataTable = resultsDataTable.Clone() 
      Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dtpHExportDataTable, rangeHasHeaders) 
      ' Perform the export. 
      exporter.Export() 
      resultsDataTable.Merge(dtpHExportDataTable) 
         ShowResult(resultsDataTable) ‘looks perfect 
      adapter1.Update(resultsDataTable)) ‘database table is NOT updated 
     End Using 
    End Using 
End Using

我不确定'worksheet.CreateDataTableExporter'做了什么，但可能是它使用原始DataRow版本，因此它看不到更改。尝试在'adapter1.Update'后面导出数据。 –

代理，工作表.CreateDataTableExporter是一个DevExpress.com扩展，它从电子表格的指定范围内获取数据，并根据需要跳过标题行并填充指定的数据表。尝试在adapter1.Update之后导出数据不起作用。 – TCIslandTime

我猜Merge（）没有设置合并行的行状态添加...所以你的resultsDataTable没有变化，你的Update（）什么都不做...... – PrfctByDsgn

答

假设您的代码更新数据库正在工作。并伴随出口更新未处理。

然后问题是暴露于另一个过程后。更新不会经历。

所以你可以这样做。

Using adapter1 = New SqlDataAdapter("SELECT SampleNo, Results, Complete_Date, Dex_Row_Id " & "FROM LIMS.dbo.Analytical_Sample_Log_ResultsInfo", cnSQL1) 
    Dim builder1 As New SqlCommandBuilder(adapter1) 
    adapter1.UpdateCommand = builder1.GetUpdateCommand() 
    Using New SqlCommandBuilder(adapter1) 
     adapter1.Fill(resultsDataTable) 
     resultsDataTable.PrimaryKey = New DataColumn() {resultsDataTable.Columns("Dex_Row_Id")} 
     dtpHExportDataTable = resultsDataTable.Clone() 

     resultsDataTable.Merge(dtpHExportDataTable) 
        ShowResult(resultsDataTable) 
     adapter1.Update(resultsDataTable) 

     Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dtpHExportDataTable, rangeHasHeaders) 
     ' Perform the export. 
     exporter.Export()) 

    End Using

这个更新在2500秒内正确行。我在存储过程中将该数据表作为参数传递 – TCIslandTime

答

我最终将数据表作为参数传递给存储过程。

-- Create User-defined Table Type 

USE LIMS 
GO 

-- Create the data type 
CREATE TYPE [dbo].[resultTable] AS TABLE 
(
[SampleNo]  [int]   NOT NULL, 
[PAprojid]  [nchar]  (10) NULL, 
[PAprojName]  [nchar]  (100) NULL, 
[STMTNAME]  [nchar]  (85) NULL, 
[DateAndTime]  [date]   NULL, 
[TestId1a]  [nchar]  (3) NULL, 
[TestType1a]  [nvarchar] (30) NULL, 
[Facility1a]  [nchar]  (80) NULL, 
[Results]  [nchar]  (10) NULL, 
[AUnits1]  [nchar]  (15) NULL, 
[SampleDate1a] [date]   NULL, 
[Complete_Date]  [date]   NULL, 
[Comments1a]  [nchar]  (100) NULL, 
[Dex_Row_Id]  [int]   NOT NULL 

PRIMARY KEY (Dex_Row_Id) 
) 
GO 

USE [LIMS] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [dbo].[usp_Update_TestResults] 
@tblResults resultTable READONLY 
AS 
BEGIN 
SET NOCOUNT ON; 

MERGE INTO LIMS.dbo.Analytical_Sample_Log_ResultsInfo P 
USING @tblResults S 
ON P.Dex_Row_Id = S.Dex_Row_Id 
WHEN MATCHED THEN 
UPDATE SET P.Results = S.Results; 
END 

    For Each row As DataRow In resultsDataTable1.Rows 
       row.SetModified() 
      Next 

      resultsDataTable1.Merge(dtTSSExportDataTable) 

      Using updateCommand As New SqlCommand("usp_Update_TestResults") 
       updateCommand.Connection = cnSQL1 
       updateCommand.CommandType = CommandType.StoredProcedure 
       updateCommand.Parameters.AddWithValue("@tblResults", resultsDataTable1) 
       cnSQL1.Open() 
       updateCommand.ExecuteNonQuery() 
       cnSQL1.Close() 
      End Using

使用的TableAdapter来更新数据库表与一个DataTable

相关推荐