Load table

[SqlJunkies User]

Hi,

I should be able to use the following code to load data from a datatable via ado.net into sql server. I want to keep the code generic so I can run it against any table. The error I get relates to primary key not being set. But I know it is because when the row is first inserted I get error msg that primary key is violated


Dim myconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim DS2 As New DataSet()

myconnection = New SqlConnection("server=blah;uid=;pwd=;database=")
myda = New SqlDataAdapter("Select * from FTVORGN2 ", myconnection)
Dim cmdBuilder As SqlCommandBuilder = _
New SqlCommandBuilder(myda)

myda.FillSchema(DS2, SchemaType.Mapped) 'this loads PK and field structure ok

myda.Fill(DS2) 'this table is actually empty so no data is brought in

DS2.Tables(0).PrimaryKey = New DataColumn() {DS2.Tables(0).Columns("FTVORGN_ORGN_CODE"), _
DS2.Tables(0).Columns("FTVORGN_EFF_DATE"), _
DS2.Tables(0).Columns("FTVORGN_NCHG_DATE")}

Dim I As Integer
Dim FieldCount As Integer
Dim x As Integer
Dim dr As DataRow

FieldCount = dsFTVORGN.Tables(0).Columns.Count

For I = 0 To dsFTVORGN.Tables(0).Rows.Count - 1

On Error Resume Next 'need this cause can't insert null into not null fields
'first add empty row
dr = DS2.Tables(0).NewRow
DS2.Tables(0).Rows.Add(dr)

'now populate
For x = 0 To FieldCount - 1

DS2.Tables(0).Rows(I).Item(x) = dsFTVORGN.Tables(0).Rows(I).Item(x)

Next

'lets try to write data from other dataset into this ds2

Next

' Update database with modified data


On Error GoTo 0
'at this point I have confirmed data is loaded into dataset just need to write it to datasource error received is:
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

myda.UpdateCommand = cmdBuilder.GetUpdateCommand()
myda.Update(DS2.Tables(0))



End Sub

Any ideas how to make this work???

 

[Sahil Malik]

Okay first of all, ADO.NET (especially 1.1) is not a replacement for bulk
copy (unless in ADO.NET 2.0 you plan on using SqlBulkCopy). Here is a
performance comparison -

http://www.thedatafarm.com/blog/PermaLink.aspx?guid=15cf7a6c-d3d9-4633-8f69-6bbf62a4d33e

How the above link basically applies to your situation is - the one by one
copy will take forever - BCP or DTS or SqlBulkCopy; either will blow the
pants off an UpdateCommand - ESPECIALLY one generated by CommandBuilder
(keep reading).

Secondly assuming inspite of the above, you really want to do datatable ->
Sql Server.

First of all why are you getting the below error?

CommandBuilder tries to generate a hyper inefficient command - it does so
because it is one size fits all; and it does that by querying the database
for the table structure. The command is awful, but that's the best it can
do - and when it does try to generate an UpdateCommand it needs to be able
to understand the SelectCommand mentioned - so it can go to the d/b and read
the table structure and generate a command for you -

I wrote up a little sample (albeit in 2.0, but the concepts still apply) and
let me introduce you to the ultra ugly command it generated

"UPDATE [Person].[Address] SET [AddressLine1] = @p1, [AddressLine2] = @p2,
[City] = @p3, [StateProvinceID] = @p4, [PostalCode] = @p5, [rowguid] = @p6,
[ModifiedDate] = @p7 WHERE (([AddressID] = @p8) AND ([AddressLine1] = @p9)
AND ((@p10 = 1 AND [AddressLine2] IS NULL) OR ([AddressLine2] = @p11)) AND
([City] = @p12) AND ([StateProvinceID] = @p13) AND ([PostalCode] = @p14) AND
([rowguid] = @p15) AND ([ModifiedDate] = @p16))"

Ugly huh?

So as you can see - there are two problems with this approach -

a) Line by line execution/insertion into the db.
b) Command builder - is inefficient in what it does.

And on a sidenote, I would recommend firing up enterprise manager, and
checking up the target table - it might really not have a PK defined in it.
Which is why you are getting this error - there's no other reason :-/.

So what do you do? What is the right approach afterall?

Well, the datatable you are inserting into the sql server - you must have
prior knowledge of the data structure it has !! You must also know what what
table it is going to (FTVORGN2). Why don't you write up an UpdateCommand
yourself, rather than letting CommandBuilder do it for you; specify the
relevant parameters; and execute this command?

If you wanna be really really cool - and efficient - but sql server
specific - you might consider sending an Xml UpdateGram into the database
and avoid a database call per row - see chapter #12 of my book for more
details on this.

AND .. if you DONOT have prior knowledge, you could resort to creating
Dynamic Sql "yourself" and not rely on CommandBuilder - don't worry Dynamic
Sql although not as good as Stored procs is not as bad as it used to be -
the query plan is cached even for dynamic sql starting Sql Server 2000.

(and one more pestering comment - try avoiding VB6 - On Error Resume Next,
..NET has a much better try/catch construct, but you might already know
that).

Hope this helped.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik