Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-06-21
4
Medium Priority
?
474 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 1400 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 600 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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