Visual Studio - Creating/Altering Database Schemas via Code

Author

Creating/Altering Database Schemas via Code

bill

2006-05-30, 7:09 pm

I have a conumdrum. Where I am employed, the decision has been made,
against my protests, to create/alter the database for a suite of
applications in the VB code! (VS 2005/SQL Server 2005)
That is,
1) when the "main" application starts it checks if the DB exists (on
the server in an INI file)
2)If it does not exist, it creates it, not using SQL Scripts, but by
running the methods explained in step 3
3)Each table that is needed is associated with a VB class. Everytime an
instance of that class is created it checks to see if the table exists,
if not it creates it.
4) Tables are "versioned". The "version" of each table is hardcoded in
the VB code, and stored in a table in the DB.
5)Thus the same Major.Minor release of an application can be using
different table "versions"
6) If "versions" in the DB and the App do not match, then "ALTER Table"
sql statements are supposedly created automagically and can then be run
by an "administrator" -- Please note that an "administrator" is not
necessarily a programmer or DBA, it can Customer BettySue who has admin
privaleges for the application.

Is it just me or is this crazy?
I have never heard of major applications (a suite of at least 9 client
server and windows services -- used in the Public Safety Sector of all
things) managing their databases in this way. If this is common
practice, please let me know.

Any suggestions/links/horror stories I can use to help stop this
madness?

If this is the wrong place to post this, i apologize. Let me know where
to post.

Thanks,
Bill

Phil Cairns

2006-05-31, 4:09 am

bill wrote:
> I have a conumdrum. Where I am employed, the decision has been made,
> against my protests, to create/alter the database for a suite of
> applications in the VB code! (VS 2005/SQL Server 2005)
> That is,
> 1) when the "main" application starts it checks if the DB exists (on
> the server in an INI file)
> 2)If it does not exist, it creates it, not using SQL Scripts, but by
> running the methods explained in step 3
> 3)Each table that is needed is associated with a VB class. Everytime an
> instance of that class is created it checks to see if the table exists,
> if not it creates it.
> 4) Tables are "versioned". The "version" of each table is hardcoded in
> the VB code, and stored in a table in the DB.
> 5)Thus the same Major.Minor release of an application can be using
> different table "versions"
> 6) If "versions" in the DB and the App do not match, then "ALTER Table"
> sql statements are supposedly created automagically and can then be run
> by an "administrator" -- Please note that an "administrator" is not
> necessarily a programmer or DBA, it can Customer BettySue who has admin
> privaleges for the application.
>
> Is it just me or is this crazy?
> I have never heard of major applications (a suite of at least 9 client
> server and windows services -- used in the Public Safety Sector of all
> things) managing their databases in this way. If this is common
> practice, please let me know.
>
> Any suggestions/links/horror stories I can use to help stop this
> madness?

Sorry, but I use a similar mechanism. I have a table called sysparams
that contains name/value pairs, and one of the names is 'dbversion'. The
value is the current database version, and if the DB version is lagging
behind the application, the application sends a bunch of "ALTER TABLE"
or "DROP VIEW/CREATE VIEW" or whatever commands to the database before
continuing with the execution.

Mind you, all of these queries are hand-coded by me and embedded into
the application, so the idea of BettySue seeing the queries doesn't come
into it. Also, if the program is built for an earlier version, it may
well crash. However, I tend to be pretty cautious and not remove fields
if I can avoid it, and if I can't, I give the users a more vigorous
recommendation to upgrade all programs or none all.

The class I use is called DataManager, and it is common to all
applications in the suite, so the first one to be started after the
system upgrade will bring the database up to date.

Phil

wmlanza@gmail.com

2006-05-31, 7:11 pm

Yes, but this suite is comprised of at least 9 different apps, with 5-6
different developers.
The Sql statement are not "hand coded" they are generated through
functions, the alter statements are supposedly generated
"automatically". Referential integrity and indexes are at the whim of
the individual developer -- thus there is not much. There is no way to
create stored procedures and views, hence, any complicated query
(joins, etc) is one humongus string in the code.
Typed datasets are near impossible to use, using grids in design mode
is not possible, because columns are dependent on the "automatic" table
creation...blah blah blah. I apologize for ranting, but it is painful
to stand by and watch.

Phil Cairns wrote:
> bill wrote:
>
> Sorry, but I use a similar mechanism. I have a table called sysparams
> that contains name/value pairs, and one of the names is 'dbversion'. The
> value is the current database version, and if the DB version is lagging
> behind the application, the application sends a bunch of "ALTER TABLE"
> or "DROP VIEW/CREATE VIEW" or whatever commands to the database before
> continuing with the execution.
>
> Mind you, all of these queries are hand-coded by me and embedded into
> the application, so the idea of BettySue seeing the queries doesn't come
> into it. Also, if the program is built for an earlier version, it may
> well crash. However, I tend to be pretty cautious and not remove fields
> if I can avoid it, and if I can't, I give the users a more vigorous
> recommendation to upgrade all programs or none all.
>
> The class I use is called DataManager, and it is common to all
> applications in the suite, so the first one to be started after the
> system upgrade will bring the database up to date.
>
> Phil

Sponsored Links