Results 1 to 3 of 3

Thread: creating and populating database from text file

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    43

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: creating and populating database from text file

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2012
    Posts
    43

    Re: creating and populating database from text file

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width