为什么此DataAdapter不将行插入数据库？

问题描述

所以我有一种情况，我正在使用SqlDataAdapter将行插入到SQL Server 2014数据库的表中。

So I have a situation where I am using a SqlDataAdapter to insert rows into a table in a SQL Server 2014 database.

数据源是Excel

当使用一些For循环和.Columns.Add和.Rows.Add填充DataTable对象以从Excel工作表中复制数据时，插入工作正常。此工作代码未包含在这里。

The insert works fine when the DataTable object is populated using a few For loops and .Columns.Add and .Rows.Add to copy the data from the Excel sheet. This working code I have not included here.

但是，我正在重构代码以使用OleDbDataReader。这是我的函数：

However, I am refactoring the code to use an OleDbDataReader. Here is my function:

Private Function FillDataTable(path As String, name As String) As DataTable
        Dim fullpath As String = path
        Dim wsname As String = name
        Dim dt = New DataTable()
        Try
            Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fullpath & "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"
            Dim commandstring As String = "Select * From " & wsname
            Using con As New OleDbConnection(connectionstring)
                Using cmd As New OleDbCommand(commandstring, con)
                    con.Open()
                    Using dr As OleDbDataReader = cmd.ExecuteReader()
                        With dt
                            For Each c In aryFieldList
                                .Columns.Add(c.FieldName, ConvertType(c.DataType))
                            Next

                            .Columns.Add("SubmID")
                            .Columns("SubmID").DefaultValue = 0

                            .Columns.Add("S_ORDER")
                            .Columns("S_ORDER").DefaultValue = 0

                            .Columns.Add("C_ORDER")
                            .Columns("C_ORDER").DefaultValue = 0
                        End With
                        dt.Load(dr)
                    End Using
                End Using
            End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return dt

    End Function

当我调试时，从函数返回的DataTable中有数据，并且否则看起来与以前版本的代码中的DataTable相同。这是.Update数据库的代码。这两种情况的代码均保持不变。

When I debug, the DataTable that is returned from the function has data in the set, and otherwise appears to be identical to the DataTable from the previous version of code. Here is the code to .Update the database. This code is unchanged for both cases.

    Dim dt = New DataTable()
    dt = FillDataTable(fullpath, wsname)

Using cn = New SqlConnection(ConfigurationManager.ConnectionStrings("Connection").ConnectionString)
    cn.Open()
    Using adp = New SqlDataAdapter()
        Dim sb As New StringBuilder

        [...StringBuilder code to build the Insert command here...]

        Dim cmd As New SqlCommand(sb.ToString, cn)

        With adp
            .InsertCommand = cmd
            .InsertCommand.Parameters.Add("SubmID", SqlDbType.Int, 1, "SubmID")
            .InsertCommand.Parameters.Add("S_ORDER", SqlDbType.Int, 1, "S_ORDER")
            .InsertCommand.Parameters.Add("C_ORDER", SqlDbType.Int, 1, "C_ORDER")

            For Each p In aryFieldList
                If p.Excluded = False Then
                    .InsertCommand.Parameters.Add(p.FieldName, p.DataType, p.Length, p.FieldName)
                End If
            Next
                adp.Update(dt)

        End With 'adp
    End Using 'adp
End Using 'cn

不会抛出异常。调试adp.Update（dt）行没有等待时间，就好像根本不执行查询一样。我注意到这是添加的行/列DT和OleDB填充的DT之间的唯一区别-成功插入数据后会有一点等待时间。

No exceptions are ever thrown. Debugging the adp.Update(dt) line has no latency as if the query is not executed at all. That is the only difference I notice between the Rows/Columns Added DT and the OleDB populated DT--There is a slight latency time as the data is inserted successfully.

Am我缺少 DataTable 的某种基本功能或属性，或者缺少在加载期间继承或创建的属性？还有我没想到的东西吗？为什么我的 SqlDataAdapter 在源是手动创建的 DataTable 而不是 DataTable 是否由 OleDbReader 填充？

Am I missing some sort of basic functionality or property of the DataTable or maybe a property inherited or created during the Load? Is it something else I haven't thought of? Why does my SqlDataAdapter insert data into the database when the source is a DataTable created manually versus a DataTable filled by the OleDbReader?

推荐答案

每个 DataTable 跟踪 RowState 的行，因此在循环中手动添加数据是可行的，因为它们都是 Added （与手动创建<$ c $无关） c> DataTable -其行）。当您从其他来源（如Excel）加载时，不会添加/新建。

Each DataTable tracks the RowState of its rows, so manually adding data in a loop works because they are all Added (it has nothing to do with manually creating the DataTable - its the rows). When you load from some other source like Excel, they are not added/new.

如果您使用 DataAdapter 填写表格，则可以说不将 RowState 设置为不变。这对于将数据从一个数据存储迁移到另一数据存储非常有用：

If you use a DataAdapter to fill the table, you can tell it not to set the RowState to Unchanged. This is very useful for migrating data from one data store to another:

myDA.AcceptChangesDuringFill = False
...
rows = myDA.Fill(xlDT)

这篇关于为什么此DataAdapter不将行插入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！