creating and populating database from text file

HI, I am a former Delphi (5) programmer migrating my app to VB 2010.
But the terminology is a bit different for VB and I'm having a hard time
translating between the two. Here's a greatly simplified list of what I want
to do:

0. Create the following databases to these specifications:
Database "Topics":
Column 1 - Topic Code (key, char(4))
Column 2 - Topic Description (varchar)
Database "Lots":
Column 1 - LotNumber (key, int)
Column 2 - Topic Code (char(4)) - for lookup in Topics database
Column 3 - Lot Description (varchar)

Metacode to build files:

1. Open text file containing topic data (topics.txt), read and parse to give 2
fields, topic code and description.

2. Read to end of file and write one row in Topics database for each line in topics.txt

3. Open text file containing lots data (lots.txt), read and parse to give 3
fields, lot number, topic code and lot description.

4. Read to end of file and write one row in Lots databsae for each line in
lots.txt. Of course, topic code should match up with the appropriate row in the
Topics database.

I want to do all this in VB code, not with SQL, including checking to see if
the tables are already created, if not then actually creating the tables,
establishing the relation between Topics and Lots and then filling the tables.
There is of course a lot more going on that I haven't mentioned (error checking,
calculations, additional fields, additional tables, etc.) but once I can see
how to do the basics, I'm sure I can figure out the rest.

Any help will be greatly appreciated.

Mike

You may already realise this but, if you're working with a database, you're working with SQL. You're simply executing the SQL from your VB code rather than in Management Studio or with SQLCMD or the like.

There are really only a few ways to execute SQL using ADO.NET:

1. Create a command object and call ExecuteNonQuery to execute a non-query statement.
2. Create a command object and call ExecuteScalar to execute a query statement and get a single value from the result set.
3. Create a command object and call ExecuteReader to execute a query and read the result set row by row.
4. Create a data adapter and call Fill to execute a query statement and populate a DataTable with the result set.
5. Create a data adapter and call Update to execute one to three non-query statements to save changes from a DataTable.
6. Create a SqlBulkCopy object and call WriteToServer to quickly insert multiple records.

Number 6 is specific to SQL Server and numbers 4 and 5 are really just wrappers for numbers 3 and 1 as well.

In your case, creating and building the database will require you to execute the appropriate CREATE DATADASE and CREATE TABLE statements. Those are non-query statements so that means number 1. As such, any example that calls ExecuteNonQuery is relevant. One such example could be found by following the CodeBank link in my signature and checking out my thread on Retrieving & Saving Data. For creating the database you would use a connection string that didn't specify a database to connect to and for creating the tables you would use a connection string that specified connecting to the database you just created.

For inserting all the data, I would suggest that using SqlBulkCopy would be the best option. In that case, I'd suggest reading the file line by line using a TextFieldParser and adding a row to a DataTable for each line. You can then pass that DataTable when calling WriteToServer.

Another option would be using OleDb to read the file like a database table. You could then use number 3 above to read the file and pass the data reader created as an argument when calling WriteToServer.

Thanks for your help. There is a lot here to digest.