Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Visual Basic 2008 Programmer's Reference ISBN: 978-0-470-18262-8
This is the forum to discuss the Wrox book Visual Basic 2008 Programmer's Reference by Rod Stephens; ISBN: 9780470182628
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Visual Basic 2008 Programmer's Reference ISBN: 978-0-470-18262-8 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 9th, 2010, 02:18 PM
Registered User
 
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating DB Tables Programmatically

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
Reply With Quote
  #2 (permalink)  
Old March 9th, 2010, 05:52 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old March 10th, 2010, 11:14 AM
Registered User
 
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
which privilege for creating a db and tables? hertendreef SQL Server 2005 5 July 11th, 2007 04:04 PM
creating code programmatically mumphis Excel VBA 0 September 2nd, 2005 09:29 AM
Report - programmatically creating penta Access 12 May 19th, 2005 04:53 PM
Creating a table programmatically Bob Burns ASP.NET 1.0 and 1.1 Basics 2 July 15th, 2004 11:44 AM
Creating Tables Programmatically stu9820 ASP.NET 1.0 and 1.1 Basics 10 October 22nd, 2003 04:25 PM



All times are GMT -4. The time now is 01:17 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.