Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.net Speed Updating SQL table using data table

Posted on 2015-01-05
2
Medium Priority
?
185 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:Murray Brown
2 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 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:Murray Brown
ID: 40532938
Thanks very much
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

578 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