Connect to Database

[shapper]

Hello,

I am using Microsoft Enterprise Library, on a class, to connect to a
database as follows:

Dim db As Database = DatabaseFactory .CreateDatabase ("DatabaseName" )

However, I would like the database name to be dynamic, i.e., specified
on the web site.

Can I define a connection string in my web site web.config and then
use it to create the database?

Or can I create my own Web.Config group to hold this information?

Thanks,
Miguel

[Cowboy \(Gregory A. Beamer\)]

You can do either. It is simply a string. You will have to set up the config
information, in the web.config, for that connection name. And, you can then
specify that particular connection, out of any number of connections, to
hit.

It would be something like this:

public Function GetDatabase(ByR ef databaseName As String) as Database
Return DatabaseFactory .CreateDatabase (databaseName)
End Function

You can then pull from config like:

Dim dbName as String = ConfigurationMa nager.AppSettin gs("databaseNam e")

This would be like this in Web.Config

<add key="databaseNa me" value="MyFirstD atabase" />
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

*************** *************** *************** ***
Think outside the box!
*************** *************** *************** ***
"shapper" <md*****@gmail. comwrote in message
news:11******** **************@ n60g2000hse.goo glegroups.com.. .

Hello,

I am using Microsoft Enterprise Library, on a class, to connect to a
database as follows:

Dim db As Database = DatabaseFactory .CreateDatabase ("DatabaseName" )

However, I would like the database name to be dynamic, i.e., specified
on the web site.

Can I define a connection string in my web site web.config and then
use it to create the database?

Or can I create my own Web.Config group to hold this information?

Thanks,
Miguel

[David R. Longnecker]

Miguel-

Unfortunately, the Factory doesn't have an overload that takes a connection
string. To directly pass a connection string, you have to bypass the factory
and directly use the database objects.

For example, if you're using Microsoft.Pract ices.Enterprise Library.Data,
you'd take that a step farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("co nnectionString" );

or, to make it more dynamic, pull in the database name from a variable, a
querystring, wherever, as long as it matches the entry in the web.config.

string databaseName = ""; // Get database name from wherever
it is coming from.
SqlDatabase db =
new SqlDatabase(
System.Configur ation.Configura tionManager.Con nectionStrings[databaseName].ConnectionStri ng);

Finally, the method I use is to create a ConnectionStrin gs class and simply
return each... creating a somewhat pseudo enum for VERY common connection
strings that I don't want to modify in 30+ web.configs if something changes;
I just want to update the framework library and push it back out to the GAC
in a server. If interested in that, check the blog entry below:

http://tiredblogger.wordpress.com/20...se-library-30/

HTH.

-dl

--
David R. Longnecker
http://blog.tiredstudent.com

sHello,
s>
sI am using Microsoft Enterprise Library, on a class, to connect to a
sdatabase as follows:
s>
sDim db As Database = DatabaseFactory .CreateDatabase ("DatabaseName" )
s>
sHowever, I would like the database name to be dynamic, i.e.,
sspecified on the web site.
s>
sCan I define a connection string in my web site web.config and then
suse it to create the database?
s>
sOr can I create my own Web.Config group to hold this information?
s>
sThanks,
sMiguel

[sloan]

I think this will work:

<add key="MyPreferre dDatabase" value="StagingC onnectionString " />

<connectionStri ngs>

<add name="Developme ntConnectionStr ing"
connectionStrin g="server=MyDev elopmentServer; database=Northw ind;User
ID=northwinduse r;password=nort hwindpassword"
providerName="S ystem.Data.SqlC lient"/>

<add name="Productio nConnectionStri ng"
connectionStrin g="server=MyPro ductionServer;d atabase=Northwi nd;User
ID=northwinduse r;password=nort hwindpassword"
providerName="S ystem.Data.SqlC lient"/>

<add name="StagingCo nnectionString"
connectionStrin g="server=MySta gingServer;data base=Northwind; User
ID=northwinduse r;password=nort hwindpassword"
providerName="S ystem.Data.SqlC lient"/>

</connectionStrin gs>
-----------The Code

private Database GetADatabase ( )
{

string preferredDBName =
System.Configur ation.Configura tionManager["MyPreferredDat abase"];
Database db = DatabaseFactory .CreateDatabase (preferredDBNam e);

return db;
}


I think that is what Cowboy was getting at.

I don't think grabbing a concrete database (SqlDatabase) is a good solution.


"David R. Longnecker" <dl*********@co mmunity.nospamw rote in message
news:46******** *************** *@msnews.micros oft.com...

Miguel-

Unfortunately, the Factory doesn't have an overload that takes a

connection

string. To directly pass a connection string, you have to bypass the

factory

and directly use the database objects.

For example, if you're using Microsoft.Pract ices.Enterprise Library.Data,
you'd take that a step farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("co nnectionString" );

or, to make it more dynamic, pull in the database name from a variable, a
querystring, wherever, as long as it matches the entry in the web.config.

string databaseName = ""; // Get database name from wherever
it is coming from.
SqlDatabase db =
new SqlDatabase(

System.Configur ation.Configura tionManager.Con nectionStrings[databaseName].Co
nnectionString) ;

>
Finally, the method I use is to create a ConnectionStrin gs class and

simply

return each... creating a somewhat pseudo enum for VERY common connection
strings that I don't want to modify in 30+ web.configs if something

changes;

I just want to update the framework library and push it back out to the

GAC

in a server. If interested in that, check the blog entry below:

http://tiredblogger.wordpress.com/20...se-library-30/

>
HTH.

-dl

--
David R. Longnecker
http://blog.tiredstudent.com

sHello,
s>
sI am using Microsoft Enterprise Library, on a class, to connect to a
sdatabase as follows:
s>
sDim db As Database = DatabaseFactory .CreateDatabase ("DatabaseName" )
s>
sHowever, I would like the database name to be dynamic, i.e.,
sspecified on the web site.
s>
sCan I define a connection string in my web site web.config and then
suse it to create the database?
s>
sOr can I create my own Web.Config group to hold this information?
s>
sThanks,
sMiguel

[David R. Longnecker]

That's a good point, Sloan.

A question though: While I realize the that bypassing the factory of the
EntLibs may not be clean, have you come across a best practice for directly
passing in connection strings without using an app/web.config file? What
I ran into, and the reason I directly use the Database objects, is that our
connection strings are anything but stable and for our framework libraries,
it made more sense to create class pseudo-enumerators and pass them along
rather than change 30+ app/web.config files throughout our enterprise. Make
a change to a library, reroll that library to the 2 servers those apps read
from, and be done. The apps are spread throughout the enterprise, so placing
them in the machine.config (if even possible) didn't even seem as clean of
a solution.

The solution you provide still requires manually editing, re-encrypting,
and rerolling config files to the application sources.

Thanks for the explaination^^.

-dl

--
David R. Longnecker
http://blog.tiredstudent.com

sI think this will work:
s>
s<add key="MyPreferre dDatabase" value="StagingC onnectionString " />
s>
s<connectionStr ings>
s>
s<add name="Developme ntConnectionStr ing"
sconnectionStri ng="server=MyDe velopmentServer ;database=North wind;User
sID=northwindus er;password=nor thwindpassword"
sproviderName=" System.Data.Sql Client"/>
s>
s<add name="Productio nConnectionStri ng"
sconnectionStri ng="server=MyPr oductionServer; database=Northw ind;User
sID=northwindus er;password=nor thwindpassword"
sproviderName=" System.Data.Sql Client"/>
s>
s<add name="StagingCo nnectionString"
sconnectionStri ng="server=MySt agingServer;dat abase=Northwind ;User
sID=northwindus er;password=nor thwindpassword"
sproviderName=" System.Data.Sql Client"/>
s>
s</connectionStrin gs>
s>
s-----------The Code
s>
sprivate Database GetADatabase ( )
s{
sstring preferredDBName =
sSystem.Configu ration.Configur ationManager["MyPreferredDat abase"];
sDatabase db = DatabaseFactory .CreateDatabase (preferredDBNam e);
s>
sreturn db;
s>
s}
s>
sI think that is what Cowboy was getting at.
s>
sI don't think grabbing a concrete database (SqlDatabase) is a good
ssolution.
s>
s"David R. Longnecker" <dl*********@co mmunity.nospamw rote in message
snews:46******* *************** **@msnews.micro soft.com...
s>

>Miguel-

Unfortunatel y, the Factory doesn't have an overload that takes a

sconnection
s>

>string. To directly pass a connection string, you have to bypass the

sfactory
s>

>and directly use the database objects.

For example, if you're using
Microsoft.Prac tices.Enterpris eLibrary.Data, you'd take that a step
farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("co nnectionString" );

or, to make it more dynamic, pull in the database name from a
variable, a querystring, wherever, as long as it matches the entry in
the web.config.

string databaseName = ""; // Get database name from wherever
it is coming from.
SqlDatabase db =
new SqlDatabase(

sSystem.Configu ration.Configur ationManager.Co nnectionStrings[databaseN
same].Co nnectionString) ;
s>

>Finally, the method I use is to create a ConnectionStrin gs class and

ssimply
s>

>return each... creating a somewhat pseudo enum for VERY common
connection strings that I don't want to modify in 30+ web.configs if
something

schanges;
s>

>I just want to update the framework library and push it back out to
the

sGAC
s>

>in a server. If interested in that, check the blog entry below:

shttp://tiredblogger.wordpress.com/20...onnection-stri
sngs-for-enterprise-library-30/
s>

>HTH.

-dl

--
David R. Longnecker
http://blog.tiredstudent.com
sHello,
s>
sI am using Microsoft Enterprise Library, on a class, to connect to
a
sdatabase as follows:
s>
sDim db As Database =
DatabaseFactor y.CreateDatabas e("DatabaseName ")
s>
sHowever, I would like the database name to be dynamic, i.e.,
sspecified on the web site.
s>
sCan I define a connection string in my web site web.config and
then
suse it to create the database?
s>
sOr can I create my own Web.Config group to hold this information?
s>
sThanks,
sMiguel