Written by WATYF on Monday, 07 November 2005 (24695 hits)
Category: .NET Programming
I had a little task I wanted to perform. And this was one of those times where it wasn't as easy as my little pea-brain had imagined it would be. Basically, I wanted to copy data from a table in one database into an identical table in another database. You'd think that there would be a very straightforward way to do this using the amazing new ADO.NET... and believe it or not... there is.
"So what's the problem?", you ask? Well... apparently... NO ONE ON THE ENTIRE FREAKING INTERNET HAS EVER POSTED A SAMPLE OF HOW TO DO IT!!%#!%!
*aaah*... and that's where I come in....
My first inclination of how to do this was to pull the data from the source db into a datatable. And that part is pathetically simple... but that's about where the "pathetically simple" part of this whole ordeal ended. Once you have the data in a datatable, you think to yourself, "Great! It'll only take me five minutes to figure out how to get this simple little datatable into my target database." And then you'll spend the next 3 days pounding your face against your monitor in frustration because you're so close to getting the data where you want it, but you JUST..... CAN'T..... DO IT...!!!
But since I'm such a swell guy, I'm gonna save you the pain (not to mention the mess it would make on your monitor) by telling you how. But first... I'm gonna whine a little about how annoying it was to find this solution.
In a previous article, I complained about how people on internet techie message boards are pretty much worthless when it comes to... well... just about everything. And this was no exception. After Googling for quite a while, I didn't come up with any suggestions on how I would do this... so I switched to Google Groups. After a number of searches, I found that a LOT of other people had the same problem as me... and the more threads I read about this problem, the more frustrating it was... because no one offered a solution. Not one time did someone say "Here's exactly how to do it.", and then proceed to post code showing you how. There was just a bunch of hem'ing and haw'ing and general idiocy.
Take this poor guy for example. He wrote a very detailed post about what he was trying to do, and the solutions he had tried so for. And, without fail, the first reply was a suggestion of how to do something entirely different than what he was asking (no surprise there, of course ). So he politely restates his question... and the next reply involves someone telling him how to do one of the solutions that he clearly stated in his initial post that he was already using. So he posts again trying to get an answer that wasn't already in his initial post, but no one ever responds.
And that's pretty much how all the other threads went. Someone would post a question, then some chuckle-head would post a not-even-close solution, or make a suggestion so vague that you'd need an "Idiot to English" translation just to figure it out.
The thing is... I had the basic structure built pretty early on. I populated a DataTable from the source db, then I created a DataAdapter to the target db, and ran the DataAdapter's Update method using the DataTable. But the data wasn't getting passed to the target database and I couldn't figure out why. I kept reading off-handed references and vague mentions of things that you needed to do, such as setting the AcceptChangesDuringFill property to False, and using an InsertCommand, but, again... no one posted a sample of what they were talking about or how to implement it.
So I went to my fall-back option of searching Experts-Exchange... a site which I don't use much any more because they charge you to search their knowledgebase... (ha! charging money for information on the net... that's crazy talk!). That's OK, though, because there's a "back-door" way to search it (hint: it starts with a G and ends with oogle). So, after a bit of searching, I found a question by a guy who was trying to do the exact same thing I wanted to do, and after reading the post I noticed an oddly familiar name at the bottom... it was MINE!?!? Apparently, I had needed this same solution once before... but the solution I had figured out at the time (with no help from the people that posted suggestions, btw ) couldn't be used for my current situation (otherwise, that's what I would have done in the first place). BUT... one of the people who posted a suggestion in that thread had actually INCLUDED SOME CODE (*gasp*... how unorthodox), and one of his code snippets included an example of the usage of InsertCommand that I had heard about in a number of other places... so, once I worked that into my test code, voilà!! My data was copied.
So without further ado (or complaining), here's a quick, simple way to copy data from one database to another (or from a datatable to a database). In the following code, "con1" is a open ADO.NET connection to the source db, and "con2" is the connection to the target db.
Dim da1, da2 As DataAdapter, dt As DataTable, cmd As
'Create DataAdapter for source db
da1 = New DataAdapter("SELECT * FROM MyTable", con1)
'Set "AcceptChages" to False so that all rows in the
DataTable will be seen as "Added" rows
da1.AcceptChangesDuringFill = False
'Fill the DataTable with data from the source db
dt = New DataTable
If Not dt Is Nothing Then
'Create DataAdapter for target db
da2 = New DataAdapter("SELECT * FROM MyTable", con2)
'Create a command builder to create the insert
cmd = New CommandBuilder(da2)
'Set the InsertCommand of your target DataAdapter using
da2.InsertCommand = cmd.GetInsertCommand
'Update the target DataAdapter using the DataTable that
you filled from the source db
As you can see, the solution itself is not difficult at all... it's just a matter of finding out how to put the pieces together... a process that can, oft times, be quite agonizing when dealing with the helpful folks on the internet.
(I say this, having learned almost everything I know about programming from intenet resources... but that's beside the point.)
(P.S. I forgot to mention... if you're using the Jet engine with ADO.NET (or ADO or ODBC or whatever) you can transfer data between databases using a single SQL statement:
INSERT INTO MyOtherTable IN 'C:\My Files\MyOtherDB.vdb' SELECT * FROM MyTable
(appends to existing table in another db)
SELECT * INTO MyOtherTable IN 'C:\My Files\MyOtherDB.vdb' FROM MyTable
(creates new table in another db)
...but I'm not using Jet in this instance, otherwise I would have used those statements in the first place.)