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.
Visual Basic.NET

Avatar of undefined
Last Comment
Jayadev Nair

8/22/2022 - Mon
Jayadev Nair

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
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
LordWabbit

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jayadev Nair

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
LordWabbit

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.
LordWabbit

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.
wasodg2

ASKER
Thanks LordWabbit!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jayadev Nair

Ok, I will go more deep to find that.

Thanks