[2005] Entry Level Programmer needs help with VB and Database!!

[Caskoapone]

Hello,

My name's Andrew and I'm current a student at a Technical School here in the US. I just recieved my first entry level job at a small Solutions Development company and they're giving me a "trial by fire"! I have a problem and I hope that, speaking with more experience coders like yourselves, I'll be able to get a grasp of what and where I need to go.

I've been given the task of converting data from an old database to a new database. I'm not sure as of yet (still waiting for an email about this from our C.T.O.) about the old database, but I'm quite sure the new database will be SQL Server '05. They've instructed me to use a VB.NET Console Application, but to be honest, I have NO clue what to do. The old/new database will hold around 200+ tables and the users insert the data from a front end Windows App.

I'm not sure if they want this Console App to be a one-time use only, or a multiple use because the client has literally thousands of users that use the Front End App and it may take some time for them to convert to a newer version of the Front End App (which will send the data to the new database).

Any advice/ideas/suggestions/death threats would be greatly appreciated!!!

Thanks!
Andrew

[dolot]

Let's see...

1. Do you have to transfer just the data or will you have to create the table structure also?

2. Is the table structure the same between the two databases or will it be different?

If the table stuctures were exactly the same, then you would just have to loop through the table names and pull all the data from each table and insert it into the new database However, even if the table structure is the same, there will probably be some differences with regard to field types. i.e. one database may use a byte field as a boolean representation whereas the other uses a boolean data type. The maximum character field lengths may be different, etc... That will probably be the biggest thing you have to take into account.

In any case, the basic framework will be the same. Get the data from one table in the source database and insert that data into the corresponding table in the destination database - with some data transformation in the process most likely.

Now if the data structures are different, then it becomes more involved. i.e. taking the data from one table and spreading it into 4 or 5. However, this isn't an elephant. It isn't even a donkey. It's more like a turkey. You can eat this in a few sittings.

Hope this helps!

[RickyH]

often times for basic database changeovers, i use something as simple as an excel spreadsheet as a middleware app while I'm transferring data. Usually, it's faster than writing an entire applicaiton to just transfer data to a new database on a one time basis.

[dolot]

I'm totally with you on the middleware, Ricky. I usually use Access for stuff like this. It seems poor Caskoapone is being forced to use a console app.

[Caskoapone]

Thanks for the advice. Apparently, from what I've now been told, I'm taking the old database and converting it with no data to the new database. I'm only configuring the new fields to the old fields. I'll actually be (attempting to) using the System.IO.Streamreader/writer because the data that will be inserted into the new database will be coming to us in .txt format.

Don't ask, it's a State Agency that we're dealing with. lol

Guess I'll have to break out the reference books to see how I can use the IO stuff to read/write into the new database.

[techgnome]

Ugh.... Nooo..... use IO.StreamReader to read the data from the text files.... use ADO.NET to write the data into the database.... if you try to write directly tp the DB file with stream anything.... you will need to know how the DBMS structures their files, and that gets messy very fast....

-tg

[dolot]

I must concur with techgnome. Writing directly to the database files is about as low level a way of accessing the data as you can get. You'd basically be re-writing an odbc driver. That's fairly major, and totally unnecessary.

[stricknyn]

If you're using SQL 2005, I'd uses SSIS to load the text files. It's alot quicker than writing the code to do it.

Strick

[Tom Sawyer]

First thing to do would be to go to your boss and have him fire the moron who decided to handle a database update by using text files and have him replaced by a project manager who's actually touched a database at some point since 1985.

If that doesn't work, create ADO.NET DataTables that are mapped to the tables in the new database and then use a stream reader to get the data from the files and put them into the DataTables and then use the Update() function to put them into the new database. This will give you the additional flexibility of handling the inevitable "Oh, can we do this with the data as well when we're putting it in?" requests.

[techgnome]

If you're using SQL 2005, I'd uses SSIS to load the text files. It's alot quicker than writing the code to do it.

Strick

IT depends.... for a one-off deal, then yes, I agree.... as a repeatable process... not so much...I'm not writing it off completely, it jsut depends on the circumstances....

-tg

[Tewl]

Ah but is this text file a dump from the preceeding database or a flat file of some type.

[stricknyn]

Thats the whole purpose of SSIS. It's not a one time type of tool. It lets you completely design a loading process, then schedule it using SQL Server jobs to run hourly,daily, weekly, monthly, etc. Whenever you want the SSIS package to run. Its made just for loading and transfering data from one place to another. I'm a coder and I'd much rather build a SSIS package to handle loading data than to have to code it.

Strick

[techgnome]

Again... it really depends.... I've got an app that imports a gawd awful amount of data on a daily basis... because of the nature of the setup, SSIS wasn't/isn't an option. So I had to code it myself... I know that SSIS isn't a one-time deal, it can be a repeatable action.

If we really are talking about a flat file, and the format is never going to change, SSIS might be an option.... I think the real question is: how often is this going to happen? once a day? Is it something that can be schedualed for a specific time? Or does it need to happen on demand (this is where I see the potentially biggest problem with SSIS - 1) getting it started and 2) monitoring it through completion - it's great for schedualing, but for on demand actions.... eehhh)

-tg