How to change database name on the fly?

[craigkenisston]

How to change database name on the fly?

I have an asp.net application in which a user connects to a sql server,
where there's a centralized database with the users table and general
settings.
Then, each user also has a database in which his particular information
is stored. There will be a few hundreds of this databases. Certain
groups of users will share a database, all tables in this database have
a userid column for identification which rows are whose. (BTW, this
strange setup is to circunvent the 4GB limit on SQL Server Express,
don't see me, ask my boss).

Now, how do I implement this in asp.net? The web.config contains a
hardcoded connection string, which right now points to the centralized
database and works up to the login point.
After that I don't know what to do next.
I'm using a BLL and DAL layers which points directly to this database
connection string.

I mean, even if I store a second connection string, how am I supposed
to change the databasename ? simple search&replace ?
Are there known techniques, approaches or pattern to do this ?

 

[Kevin Spencer]

You don't need to and should not change a database name. You don't need
multiple databases. You need to understand how to design databases. If you
need to store information about hundreds of people, you may need several
tables in a single database, but you will only need one record per table per
person. I would suggest downloading the (free) SQL Server Books Online at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx.

--
HTH,

Kevin Spencer
Microsoft MVP
Chicken Salad Surgery

What You Seek Is What You Get.

 

[craigkenisston]

Yikes !!, I'm sorry for the confusion.
I meant to change the database name in the connection string, not to
change the physical name of the database on SQL Server.

But I do need several databases, that's how this was designed. I told
the reason is to circunvent the 4Gb size limit. That's enough reason.
The $6,000 usd price of the standard version is enough reason. More
reason if you have to multplie this for 4-6, while you can have it free
(please, don't ask me use MySQL).

Just to prove I'm not crazy the data model goes like:

LogSettingsDB ( a database)
---- Users Table (a table)
---- Configuration Table
---- Other Table

Log0001DB
---- Logs Table (Stores info of John and Kevin).

Log0002DB
---- Logs Table (Stores info of Paul)

Log0003DB
---- Logs Table (Stores info of Mike, George and Anthony)

Now, the users table goes like (snippet):
-- userID: 1
-- userName: John
-- AsignedDatabase: Log0001DB

Other user:
-- userID: 2
-- userName: Kevin
-- AsignedDatabase: Log0001DB

Other user:
-- userID: 3
-- userName: Paul
-- AsignedDatabase: Log0002DB

Other user:
-- userID: 4
-- userName: Mike
-- AsignedDatabase: Log0003DB


So, after the user is logged and for this it connected to
LogSettingsDB, I need to create/generate/manipulate (this is my
question) another connection string which must be created based on the
"AssignedDatabase" column in the user's settings.


Regards,

 

[Rob MacFadyen]

Craig,

For an open connection you can do:
cn.ChangeDatabase(databaseName);

Though... I'm not sure what this would do with connection pooling. If I was
you... I would make certain (SQL Profiler) that a reused connection's
database is being reset (however that works).

You can also manipulate the connection string:

private string FixupConnectionString(string databaseName)
{
string s = ConfigurationManager.ConnectionStrings[0].ConnectionString;

return s.Replace("Initial Catalog=MyNormalDatabase",
"Initial Catalog=" + databaseName);
}


But... this has the feel of just an awful, awful solution.

Why do you need 4 to 6 licenses? Are you sure you need Standard... will
Workgroup do? Are you sure you're company isn't on a licensing plan that
entitles you to a discount (open, select, volume)?

Regards,

Rob MacFadyen

 

[...]

strange setup is to circunvent the 4GB limit on SQL Server Express,
don't see me, ask my boss).
snip

I think this may directly contravene the licence agreement for SQL Server
Express.

Regards,
Ron.

 

[craigkenisston]

Really ? How ?
Do they limit the number of database as well ?
I read the license, and I didn't see anything regarding the number of
database.
Have you ever read it ?
Anyway, I was not asking legal advise on licensing the database
product, I would go to the SQL Server groups if I wanted that advise.
Thank you.

Regards,

 

[...]

Really ? How ?
Do they limit the number of database as well ?
I read the license, and I didn't see anything regarding the number of
database.
Have you ever read it ?
Anyway, I was not asking legal advise on licensing the database
product, I would go to the SQL Server groups if I wanted that advise.
Thank you.

Regards,

<snip>
I was not referring to the number of databases, I was referring to section 4
SCOPE OF LICENCE, where it says you may not work around any technical
limitations in the software.
Anyway, good luck in sorting out your problem.
Regards,
Ron.