Connect to Database

  • Thread starter Thread starter shapper
  • Start date Start date
S

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
 
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(ByRef databaseName As String) as Database
Return DatabaseFactory.CreateDatabase(databaseName)
End Function

You can then pull from config like:

Dim dbName as String = ConfigurationManager.AppSettings("databaseName")

This would be like this in Web.Config

<add key="databaseName" value="MyFirstDatabase" />


--
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!
************************************************
 
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.Practices.EnterpriseLibrary.Data,
you'd take that a step farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("connectionString");

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.Configuration.ConfigurationManager.ConnectionStrings[databaseName].ConnectionString);

Finally, the method I use is to create a ConnectionStrings 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/2007/05/06/embedded-connection-strings-for-enterprise-library-30/

HTH.

-dl

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

s> Hello,
s>
s> I am using Microsoft Enterprise Library, on a class, to connect to a
s> database as follows:
s>
s> Dim db As Database = DatabaseFactory.CreateDatabase("DatabaseName")
s>
s> However, I would like the database name to be dynamic, i.e.,
s> specified on the web site.
s>
s> Can I define a connection string in my web site web.config and then
s> use it to create the database?
s>
s> Or can I create my own Web.Config group to hold this information?
s>
s> Thanks,
s> Miguel
 
I think this will work:



<add key="MyPreferredDatabase" value="StagingConnectionString" />



<connectionStrings>

<add name="DevelopmentConnectionString"
connectionString="server=MyDevelopmentServer;database=Northwind;User
ID=northwinduser;password=northwindpassword"
providerName="System.Data.SqlClient"/>

<add name="ProductionConnectionString"
connectionString="server=MyProductionServer;database=Northwind;User
ID=northwinduser;password=northwindpassword"
providerName="System.Data.SqlClient"/>

<add name="StagingConnectionString"
connectionString="server=MyStagingServer;database=Northwind;User
ID=northwinduser;password=northwindpassword"
providerName="System.Data.SqlClient"/>



</connectionStrings>


-----------The Code

private Database GetADatabase ( )
{

string preferredDBName =
System.Configuration.ConfigurationManager["MyPreferredDatabase"];


Database db = DatabaseFactory.CreateDatabase(preferredDBName);

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 said:
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.Practices.EnterpriseLibrary.Data,
you'd take that a step farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("connectionString");

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.Configuration.ConfigurationManager.ConnectionStrings[databaseName].Co
nnectionString);

Finally, the method I use is to create a ConnectionStrings 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/2007/05/06/embedded-connection-strings-for-enterprise-library-30/

HTH.

-dl

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

s> Hello,
s>
s> I am using Microsoft Enterprise Library, on a class, to connect to a
s> database as follows:
s>
s> Dim db As Database = DatabaseFactory.CreateDatabase("DatabaseName")
s>
s> However, I would like the database name to be dynamic, i.e.,
s> specified on the web site.
s>
s> Can I define a connection string in my web site web.config and then
s> use it to create the database?
s>
s> Or can I create my own Web.Config group to hold this information?
s>
s> Thanks,
s> Miguel
Click to expand...
 
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

s> I think this will work:
s>
s> <add key="MyPreferredDatabase" value="StagingConnectionString" />
s>
s> <connectionStrings>
s>
s> <add name="DevelopmentConnectionString"
s> connectionString="server=MyDevelopmentServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="ProductionConnectionString"
s> connectionString="server=MyProductionServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="StagingConnectionString"
s> connectionString="server=MyStagingServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> </connectionStrings>
s>
s> -----------The Code
s>
s> private Database GetADatabase ( )
s> {
s> string preferredDBName =
s> System.Configuration.ConfigurationManager["MyPreferredDatabase"];
s> Database db = DatabaseFactory.CreateDatabase(preferredDBName);
s>
s> return db;
s>
s> }
s>
s> I think that is what Cowboy was getting at.
s>
s> I don't think grabbing a concrete database (SqlDatabase) is a good
s> solution.
s>
s> s>s> connection
s>s> factory
s>s> System.Configuration.ConfigurationManager.ConnectionStrings[databaseN
s> ame].Co nnectionString);
s>s> simply
s>s> changes;
s>s> GAC
s>s> http://tiredblogger.wordpress.com/2007/05/06/embedded-connection-stri
s> ngs-for-enterprise-library-30/
s>
 
My company (and I) actually wrote some wrapper code for the Enterprise
Library.

And we have a "configuration management" system, where we give back our
(adapter design pattern) Database object, which is just a wrapped enterprise
library database.

We have a static helper that gives back the above object.

That static helper reads and creates the db object from data stored in a
database somewhere.

I think we provide an ApplicationUUID. and maybe a Development , Staging ,
Production enum.

Does that make sense?

It was involved, but we have a central place to change db information.




David R. Longnecker said:
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

s> I think this will work:
s>
s> <add key="MyPreferredDatabase" value="StagingConnectionString" />
s>
s> <connectionStrings>
s>
s> <add name="DevelopmentConnectionString"
s> connectionString="server=MyDevelopmentServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="ProductionConnectionString"
s> connectionString="server=MyProductionServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="StagingConnectionString"
s> connectionString="server=MyStagingServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> </connectionStrings>
s>
s> -----------The Code
s>
s> private Database GetADatabase ( )
s> {
s> string preferredDBName =
s> System.Configuration.ConfigurationManager["MyPreferredDatabase"];
s> Database db = DatabaseFactory.CreateDatabase(preferredDBName);
s>
s> return db;
s>
s> }
s>
s> I think that is what Cowboy was getting at.
s>
s> I don't think grabbing a concrete database (SqlDatabase) is a good
s> solution.
s>
s> s>s> connection
s>s> factory
s>s> System.Configuration.ConfigurationManager.ConnectionStrings[databaseN
s> ame].Co nnectionString);
s>s> simply
s>s> changes;
s>s> GAC
s>s> http://tiredblogger.wordpress.com/2007/05/06/embedded-connection-stri
s> ngs-for-enterprise-library-30/
s>
Click to expand...
 
Interesting idea indeed... thanks for the background!

-dl

--
David R. Longnecker
http://blog.tiredstudent.com
My company (and I) actually wrote some wrapper code for the Enterprise
Library.

And we have a "configuration management" system, where we give back
our (adapter design pattern) Database object, which is just a wrapped
enterprise library database.

We have a static helper that gives back the above object.

That static helper reads and creates the db object from data stored in
a database somewhere.

I think we provide an ApplicationUUID. and maybe a Development ,
Staging , Production enum.

Does that make sense?

It was involved, but we have a central place to change db information.

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
s> I think this will work:
s>
s> <add key="MyPreferredDatabase" value="StagingConnectionString" />
s>
s> <connectionStrings>
s>
s> <add name="DevelopmentConnectionString"
s>
connectionString="server=MyDevelopmentServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="ProductionConnectionString"
s>
connectionString="server=MyProductionServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> <add name="StagingConnectionString"
s> connectionString="server=MyStagingServer;database=Northwind;User
s> ID=northwinduser;password=northwindpassword"
s> providerName="System.Data.SqlClient"/>
s>
s> </connectionStrings>
s>
s> -----------The Code
s>
s> private Database GetADatabase ( )
s> {
s> string preferredDBName =
s> System.Configuration.ConfigurationManager["MyPreferredDatabase"];
s> Database db = DatabaseFactory.CreateDatabase(preferredDBName);
s>
s> return db;
s>
s> }
s>
s> I think that is what Cowboy was getting at.
s>
s> I don't think grabbing a concrete database (SqlDatabase) is a good
s> solution.
s>
message
s> s>
Miguel-

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

For example, if you're using
Microsoft.Practices.EnterpriseLibrary.Data, you'd take that a step
farther to .Sql or .Oracle, etc.

From there you could use

SqlDatabase db = new SqlDatabase("connectionString");

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(
Click to expand...
s>
System.Configuration.ConfigurationManager.ConnectionStrings[databaseN
s> ame].Co nnectionString);
s>
Finally, the method I use is to create a ConnectionStrings class
and
Click to expand...
s> simply
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
Click to expand...
s> changes;
s>
I just want to update the framework library and push it back out to
the
Click to expand...
s> GAC
s>
in a server. If interested in that, check the blog entry below:
Click to expand...
s>
http://tiredblogger.wordpress.com/2007/05/06/embedded-connection-stri
s> ngs-for-enterprise-library-30/
s>
HTH.

-dl

--
David R. Longnecker
http://blog.tiredstudent.com
s> Hello,
s>
s> I am using Microsoft Enterprise Library, on a class, to connect
to
a
s> database as follows:
s>
s> Dim db As Database =
DatabaseFactory.CreateDatabase("DatabaseName")
s>
s> However, I would like the database name to be dynamic, i.e.,
s> specified on the web site.
s>
s> Can I define a connection string in my web site web.config and
then
s> use it to create the database?
s>
s> Or can I create my own Web.Config group to hold this
information?
s>
s> Thanks,
s> Miguel
Click to expand...
Click to expand...
Click to expand...
 
Back
Top