473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Single Application -> Multiple Databases

We have a setup where we have a single web application, but the user can be
attached to any of a hundred different (identically structured) databases
depending on their login credentials.

Currently we are storing the connection string in Session and in each page's
Page_Init we reset any SqlDataSource's connection string. This works
reasonably well, though not ideal:
SqlDataSource1.ConnectionString = Session("dbconn")

I would like to start experimenting with creating a full data access layer
using an ObjectDataSource and bound controls. This is easy enough to set up
after following the tutorials on www.asp.net and I added the DataSet/XSD,
but I do not see a way to set the underlying connection string. It is
pulling the original connection string from web.config. I don't see a class
file that I can edit and I can't access the properties of the DAL through
the ObjectDataSource.

Any idea how I can accomplist this?

Thanks.


Oct 26 '07 #1
4 8480
"David W" <sp****@nospam.post.comwrote in message
news:u%****************@TK2MSFTNGP04.phx.gbl...
We have a setup where we have a single web application, but the user can
be attached to any of a hundred different (identically structured)
databases depending on their login credentials.
Any particular reason for this structure, AAMOI...?
Currently we are storing the connection string in Session and in each
page's Page_Init we reset any SqlDataSource's connection string. This
works reasonably well, though not ideal:
SqlDataSource1.ConnectionString = Session("dbconn")

I would like to start experimenting with creating a full data access layer
using an ObjectDataSource and bound controls. This is easy enough to set
up after following the tutorials on www.asp.net and I added the
DataSet/XSD, but I do not see a way to set the underlying connection
string. It is pulling the original connection string from web.config. I
don't see a class file that I can edit and I can't access the properties
of the DAL through the ObjectDataSource.
I guess it would depend on the backend RDBMS. If it's SQL Server, I would go
for the factory pattern described in Microsoft's DAAB:
http://www.dotnetjunkies.com/Article...7F100A9F9.dcik
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Oct 26 '07 #2
//SqlDataSource1.ConnectionString = Session("dbconn")//

Ahhh......
Just stored the connection string NAME.... as the most recent picked item


{
ConnectionStringSettingsCollection connectionStrings =
ConfigurationManager.ConnectionStrings;

ConnectionStringSettings connection;
foreach ( connection in connectionStrings) {

string connectionStringName = connection.Name;
string connectionString = connection.ConnectionString;
string providerName = connection.ProviderName;

Debug.Print(connectionStringName);
}

this.GridView1.DataSource = connectionStrings;
this.GridView1.DataBind();
}

Get rid of those stupid "auto generated adapters" (FillTable) things when
you create your Strong DataSet.
Those things are the devil.
You can get something like the EnterpriseLibrary.Data to help if you want.
Either way, write a real DAL object ... which will populate your strong
dataset... and pull the connection string info from the config file, using
the simple NAME passed into your DAL object.
That auto generated stuff is for the birds, esp when you want to pick the
database dynamically.
class ZebraData
{
public static ZebraStrongDS GetAllZebras( string connectionStringName ,
string zebraColor )
{
//pull the connection string from the config file with the objects above
//
ZebraStrongDS returnDS = new ZebraStrongDS ();

// i threw in zebraColor as a param...
//call LoadDataSet method here, to populate the returnDS with
resultset(s) from a stored procedure or something

}

}

You can check my blog, I have several NLayered (and now 1 NTiered example)
there:
http://sholliday.spaces.live.com/Blog/

"David W" <sp****@nospam.post.comwrote in message
news:u%****************@TK2MSFTNGP04.phx.gbl...
We have a setup where we have a single web application, but the user can
be attached to any of a hundred different (identically structured)
databases depending on their login credentials.

Currently we are storing the connection string in Session and in each
page's Page_Init we reset any SqlDataSource's connection string. This
works reasonably well, though not ideal:
SqlDataSource1.ConnectionString = Session("dbconn")

I would like to start experimenting with creating a full data access layer
using an ObjectDataSource and bound controls. This is easy enough to set
up after following the tutorials on www.asp.net and I added the
DataSet/XSD, but I do not see a way to set the underlying connection
string. It is pulling the original connection string from web.config. I
don't see a class file that I can edit and I can't access the properties
of the DAL through the ObjectDataSource.

Any idea how I can accomplist this?

Thanks.


Oct 26 '07 #3

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:eD**************@TK2MSFTNGP06.phx.gbl...
"David W" <sp****@nospam.post.comwrote in message
news:u%****************@TK2MSFTNGP04.phx.gbl...
>We have a setup where we have a single web application, but the user can
be attached to any of a hundred different (identically structured)
databases depending on their login credentials.

Any particular reason for this structure, AAMOI...?
By law (HIPAA), for performace (smaller tables and multiple servers),
management (easier to move around databases to different servers as needed,
backups, restores, etc.), security (separate logins for each database, and
no chance of accidental crossover (easier row level security). Each
customer has their own database and there is no crossover (other than some
lookup tables that get pushed to every database each release) so it works
out very well in our situation. There is one base database that stores the
location of each database for each customer.

Yes, its SQL Server 2005 Standard.

I'll look at that appication block, but I don't think that ties in directly
to the DAL from looking at it.

-Dave

Oct 26 '07 #4
"David W" <sp****@nospam.post.comwrote in message
news:eX**************@TK2MSFTNGP04.phx.gbl...
I'll look at that appication block, but I don't think that ties in
directly to the DAL from looking at it.

That's precisely what it is:
http://msdn.microsoft.com/msdnmag/is...07/DataPoints/
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

Oct 26 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robb Gilmore | last post by:
We inherited an ASP.NET application which is not thread-safe. Many copies of this application are in the field and customers are having problems, which we believe is due to the thread safety...
18
by: Steve Barnett | last post by:
I want to ensure that there is only ever one instance of my app running on a single PC at any time. I understand that I can achieve this by using a mutex and, if I can't take ownership of the...
3
by: R Millman | last post by:
under ASP.NET, single stepping in debug mode appears not to stop within event procedures. i.e. 1) Create web page with submit button and event procedure for the click event in the code behind...
3
by: Ben Fidge | last post by:
Is is possible to make just a single page within an ASP.NET application use SSL? I've written an e-commerce application and want to make just the checkout page use SSL for secure transmission. ...
3
by: Joe Fawcett | last post by:
Dear All We have an application where users login, their name/password details are stored in a table, and now we want to add a third party bulletin board that lives in its own virtual directory....
11
by: Pieter | last post by:
Hi, I'm having some troubles with my numeric-types in my VB.NET 2005 application, together with a SQL Server 2000. - I first used Single in my application, and Decimal in my database. But a...
7
by: ashley.ward | last post by:
We have been using VB6 to develop small custom apps that access an Oracle database, in order to extend a larger product that is developed by our colleagues in Germany (who use C++ and Java). As...
4
by: ad | last post by:
I used vs2005 to develop a winform project. How can I make my project as a single executable file?
1
by: xtseeker | last post by:
We have a multi-threaded .NET application, the problem is that the previous programmer didn't care to have locks on the shared variables. Somehow the application doesn't end up in Locked state on a...
1
by: archana | last post by:
Hi all, I am confuse regarding concept of code behind model and single file model.. I have heard that single file model compiles dynamically. suppose i have application in which i am having...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.