So I have a situation where I am using a SqlDataAdapter to insert rows into a table in a SQL Server 2014 database.
The source of the data is an Excel spreadsheet.
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.
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
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
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 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?
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.
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)