Solved

Copy entire datatable to SQL Server table!  500 Points, need it FAST.

Posted on 2006-06-21
4
460 Views
Last Modified: 2008-03-06
This question should require less than no brains, but I am a dummy I guess.

VB .NET 2003

I have a datatable in memory.  I have a SQL 2005 table in the database, which is truncated empty.  datatable and SQL table are laid out identically with identical field names. I need to fill the SQL table with the contents of the datatable.  

Can this be done any other way than a loop and a formed SQL Insert string?  The table has over 200 columns, and I am too lazy and impatient to do it that way unless I have no alternative.  I know in ADODB.Recordset in the old ASP, I was able to copy between recordsets with a nested loop, but cannot see the way to this solution!  Thanks for your help.

J
0
Comment
Question by:johnftamburo
  • 2
4 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 16954908
Sub UpdateDatabase(ByVal dt as System.Data.DataTable)
      Dim conn As SqlConnection
      Try
            conn = New SqlConnection("your connection string here")
            conn.Open()
        Catch ex As SqlException
           
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
        End Try
      Dim ds as New Dataset
      Dim dsTemp as New Dataset
       Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Table1", conn)
Dim cmdBuilder as SqlCommandBuilder
try
        ''fill dataset
         da.Fill(ds, "Table1")

      ''add datatable to a temp dataset
      dsTemp.Tables.Add(dt)
      ''merge two datasets together
      ds.Merge(dsTemp)
      ''setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database
      cmdBuilder = new SqlCommandBuilder(da)

      da.Update(ds, "Table1")
Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
Finally

        End Try
End Sub

0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 350 total points
ID: 16954923
Sub UpdateDatabase(ByVal dt as System.Data.DataTable)
     Dim conn As SqlConnection
     Try
            conn = New SqlConnection("your connection string here")
            conn.Open()
        Catch ex As SqlException
           
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
        End Try
     Dim ds as New Dataset
     Dim dsTemp as New Dataset
      Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Table1", conn)
      Dim cmdBuilder as SqlCommandBuilder
      try
              ''fill dataset
               da.Fill(ds, "Table1")

                 ''add datatable to a temp dataset
                 dsTemp.Tables.Add(dt)
                 ''merge two datasets together
                 ds.Merge(dsTemp)
                 ''setup the InsertCommand, UpdateCommand, and DeleteCommand to reconcile the changes to the database
                 cmdBuilder = new SqlCommandBuilder(da)

                 da.Update(ds, "Table1")
      Catch ex As Exception
           
            Console.Write("ERROR: " & ex.Message)
      Finally
            If conn.State = ConnectionState.Open Then
                      conn.Close()
                  End If
        End Try
End Sub
0
 
LVL 41

Accepted Solution

by:
graye earned 150 total points
ID: 16959316
Actually, if the in-memory "detached" dataset's schema is the same as that of the database... and the destination table is empty (or at least will not have any rows in common), then you can skip the fill() and merge() steps.

So a slightly simplier version of YZlat's example would work:

     Dim cmdBuilder as SqlCommandBuilder
     try
               cmdBuilder = new SqlCommandBuilder(da)

               da.Update(ds, "Table1")
     Catch ex As Exception
0
 

Author Comment

by:johnftamburo
ID: 16961895
Hi. The solution worked!  The assist from graye was also nice!  Thanks, J
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HTTPS jquery doesn't work 9 61
ASP.NET MVC 2 31
Send SMS from VB via Sierra Wireless Modem 2 21
Help with adding DLL file in Windows project 20 31
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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