Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now