Link to home
Start Free TrialLog in
Avatar of wasodg2
wasodg2

asked on

Programmatically created Data Tables and Inserting into SQL tables

Hello,

I am working on a web form that Programmatically creates datatables to pass on to other pages using the session state. I am binding those datatables to detail views and at that point if the user accepts what he see's on the form he clicks a button which would use the datatable I've created to insert a row into a few SQL tables on the server.

Is there an easy way to acomplish this?

I have muliple tables that need to update multiple SQL tables.
Avatar of Jayadev Nair
Jayadev Nair
Flag of United States of America image

If the table you have is of same schema of that of SQL tables then it would be easy.
At the time you need to insert/Prior, using a DataAdapter, Fill a dataset with the tables you want to insert from SQL. and from your local dataset, just add the rows you want to insert to SQL datasets and then call dataAdapter.Update(SQLDataset). Issue appropriate InsertCommands to the dataAdapter
Avatar of wasodg2
wasodg2

ASKER

My situation is that the original dataset did not come from SQL so I don't see how the update command would work, I fit did come from SQL then yes, I can see that working.

The Schema for the created  datatable is the same as the SQL table I wish to insert.

Below is the code on the last page where the Datatable is passed by the session state then set as the datatable the bound to the details view.

        Dim Authorization_Table As Data.DataTable = Session("Authorization_Table")
        Dim Authorization_Row As Data.DataRow
        Authorization_View.DataSource = Authorization_Table
        Authorization_View.DataBind()

so how do I take this datatable and insert it to the SQL database?
ASKER CERTIFIED SOLUTION
Avatar of LordWabbit
LordWabbit

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, I agree with Lordwabbit to iterate each row and insert. but there is another way that will give better performance. Use ForEach method of System.Array.

Private Sub DoInsert()
 Dim rows() as Syste.Data.DataRow
 yourSourceDataTable.rows.copyto(rows,0)

 System.Array.ForEach(Of System.Data.DataRow)(rows,AddressOf ActionHandler)
End Sub

Private Sub ActionHandler(Row as System.Data.DataRow)
 ' Write code of LordWabbit
 ' or use query like
 myCommand.CommandText="Insert into myTable(Field1) Values(" & row("Field1") & ");"
 myCommand.ExecuteNonQuery()
End Sub

This will perform more than For Each Loop as LordWabbit posted
hey, thanks, didn't even know about that, i learned something new today, not sure why you think it would give better performance though, calling a method would involve popping data on and off the stack for each element, will need to write some code to test that myself.
compued - wrote two console projects to test which would be faster - cycled through 10 thousand plus records
for each - 62 milliseconds
System.Array.ForEach - 119 milliseconds

reckon it's the CopyTo which makes a big difference also takes up more memory.
Avatar of wasodg2

ASKER

Thanks LordWabbit!
Ok, I will go more deep to find that.

Thanks