Solved

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

Posted on 2006-06-21
4
464 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JSON  parse help 8 52
SQL Query Help Top 1 and Distinct? 6 49
How to share My.Settings in multiple application instances ? 7 54
VB.net/VSTO Excel Add-in 2 19
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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