Solved

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

Posted on 2006-06-21
4
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 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