Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Single Application -> Multiple Databases

Reply
Thread Tools

Single Application -> Multiple Databases

 
 
David W
Guest
Posts: n/a
 
      10-26-2007
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.




 
Reply With Quote
 
 
 
 
Mark Rae [MVP]
Guest
Posts: n/a
 
      10-26-2007
"David W" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...

> 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

 
Reply With Quote
 
 
 
 
sloan
Guest
Posts: n/a
 
      10-26-2007
//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" <(E-Mail Removed)> wrote in message
news:u%(E-Mail Removed)...
> 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.
>
>
>
>



 
Reply With Quote
 
David W
Guest
Posts: n/a
 
      10-26-2007

"Mark Rae [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "David W" <(E-Mail Removed)> wrote in message
> news:u%(E-Mail Removed)...
>
>> 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





 
Reply With Quote
 
Mark Rae [MVP]
Guest
Posts: n/a
 
      10-26-2007
"David W" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
resolve single line with multiple items into mutliple lines, single items ela Perl Misc 12 04-06-2009 06:47 PM
one single transaction , several databases' connections YeTsun@gmail.com Java 3 07-31-2007 10:56 AM
multiple web applications - single webservice - multiple databases Philip Gray ASP .Net Web Services 0 08-25-2005 09:25 AM
[podcast] Expert panel discussion of XQuery, native XML databases, SQL/XML databases Ken North XML 0 07-14-2005 05:50 AM
dealing with single quote in databases in ASP.NET pages SStory ASP .Net 1 11-04-2003 10:06 PM



Advertisments