Add table prefix programmatically

[Lupus]

Hi!

I'm working on a webapplication which uses some SQL tables. In some
cases, we need to install different versions in one database. That's
why we add prefixes to all tables (iee. cl1_users and cl2_users).

At the moment we add %PRE% in the querystring. The DAL performs a
simple replace() to put the table prefix on each place.

I was wondering if there is an easier way to use table prefixes ? Does
ADO.NET have a feature to use a prefix ? If so, we can easily put the
prefix in a config file and avoid human mistakes...


Kind regards
Mathew

 

[Patrice]

This is unrelated. ADO.NET doesn't have such a feature but you can still
store this prefix in the web config file.

Not sure why you need this. It could be perhaps also done by using schema or
aliases in SQL Server 2005 but I would also see if this is really needed at
all (I don't remember to have needed such a requirement).

 

[Smartkid]

I suggest using "schema"s if you are using SQL2K5, or "user"s for SQL2K.
Create a schema/user for each version.

For example,
MyAppV1.Users
MyAppV2.Users

This will simplifies the DAL as no table name transformation happens.
You can switch to another version by simply changing the connection string.

And you may also want to create a schema/user named MyAppCur for the
current version that contains views for the current version of tables.