Creating DB Tables Programmatically

JEofVA · March 9th, 2010, 03:18 PM

I'm working on a project that records data continuously streaming in through a serial port, and is parsed into a custom data format, and then, after being used in various ways, is suppose to be put into a database table for storage. I've worked out how to capture, parse and use the data, but I'm just not clear on storing it.

Since each database table will only hold a single days data, its name will be composed of the current date string at runtime (e.g., "20100309abc"). Since the program may be started and stopped many times per day, one of the things that have to happen when the main form loads is that the database needs to be searched to determine whether the current days table has been created yet or not. If it has already been created then that table gets added to, otherwise a new table needs to be created and set up with all the appropriate fields before being written to.

I've seen lots of tutorials that show 36 ways to do this at design time, but none that show how to do it at run time. I broke down and bought Rod Stephen's "Visual Basic 2008 - Programmer's Reference" to help on me with the database questions I have, but I'm still confused as to whether I should be using TableAdapters, DataSets, connection strings, or what. Is there anyone out there that thinks they could give me some guidance. (I will provide more information upon request)

I appreciate any assistance I can get,

Jonathan

Old Pedant · March 9th, 2010, 06:52 PM

Well, the first comment/question to you needs to be: WHY???

It's almost always a really bad idea to have more than one table in your database with the same design. If the only thing distinguishing your tables is the date that the data is entered into them, then you should use just one table and you should just add a single field (a DATETIME field, of course) to hold the date that the data pertains to.

If somebody has imposed this restriction upon you, you should question their database design skills. Even if there is a need for the data to be (say) exported on a daily basis, you would surely still be better off keeping it all in a single table and using queries to do the exporting as needed.

Having said all that...

If you still have to do this for some horrible reason, it's not hard. You can certainly simply execute a SQL statement to create the table:

Code:

    CREATE TABLE name (
        fieldname fieldtype,
        ...
    )

Just put the entire CREATE into a string and use ExecuteNonQuery and presto, you have a table.

A more practical way might be to have one prototype table that you "clone". Unfortunately, SQL Server doesn't have a "create table xxx like yyy" as some DBs do. But you can simply do

Code:

    SELECT *
    INTO newtablename
    FROM existingtablename

Provide a single dummy record in the existing table and voila! You have NEAR-clone in the new table. ("Near" because IDENTITY columns will become just INT, and you won't copy any foreign key info, and and and... But all those are easily fixed by follow-up ALTER TABLE statements.)

You *can* ask SQL Server if a table already exists, but if you are really bent on doing this ugly thing, I think I would create a separate table, just to keep track of which tables already exist.

But please please please consider NOT creating separate tables. It's just not a good solution, honest.

JEofVA · March 10th, 2010, 12:14 PM

Thanks for getting back to me so quickly on this matter. I understand that the design I've chosen is not optimum, but given the trade-offs I think I'm looking at, it was the only solution that I could come up with.

The problem is that my project will be collecting 1800 records per hour and will be running off and on for up to 6 hour per day. The records are essentially GPS location fixes with 3 other data bits plus the date and time. As the points are coming in I will be, among other tasks, plotting their geographic positions on a map in the UI that is color-coded to represent one of the other data bits, and I will be having to redraw the map with every new record that comes in. It is my understanding that working with tables that have over 5,000 records or so greatly encumbers a database's ability to process records. I thought that creating a fresh table at the start of each day's run, and therefore working with smaller tables, might be a way to keep the record-reading fast enough to keep up with the influx of data.

What are your thoughts on the reading efficiency vs. size issue?

Jonathan