Solved

VB.net Speed Updating SQL table using data table

Posted on 2015-01-05
2
172 Views
Last Modified: 2015-01-05
Hi

I connect to a SQL database in my VB.net project using the following code:
             
                dTable2 = New DataTable
                Dim connection As New SqlConnection(CS)
                dbadp2 = New SqlDataAdapter(sSQL, connection)
                dbadp2.Fill(dTable2)
                Me.DataGridView1.DataSource = dTable2

I then add a few thousand rows with the following VB.net code (this is very fast)
dTable2.Rows.Add(newTableRow)

My issue is then trying to save the new data back to SQL using the following code. It takes a few minutes.
Is there any faster way to do this?

            Dim builder As New SqlCommandBuilder(dbadp2)
            builder.QuotePrefix = "["
            builder.QuoteSuffix = "]"
            dbadp2.UpdateCommand = builder.GetUpdateCommand()
            dbadp2.Update(dTable2)
0
Comment
Question by:murbro
2 Comments
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40531919
You can use SQLBulkCopy. Please read this article to learn Using SqlBulkCopy To Perform Efficient Bulk SQL Operations.

Here is the Microsoft Documentation on SqlBulkCopy Class.
0
 

Author Closing Comment

by:murbro
ID: 40532938
Thanks very much
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
More on Time zones in vb 2010 12 38
How to Retrieve data from SQl database into multiple Textboxes VB.net 25 47
SqlServer no dupes 25 34
VB.NET 2008 Winforms Signing 13 16
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question