NEW: Support for dynamic connection strings

[mgnoonan]

As mentioned in my previous post, EasyObjects now has support for dynamic connection strings. There are numerous reasons for supporting this:

1. You don't know what server the application will be connecting to at runtime.
2. You don't know what database the application will be connecting to at runtime.
3. You want to be able to provide user logon credentials at runtime.
4. In a corporate environment, there are situations where you need to read the connection string from somewhere other than web.config or dataConfiguration.config.

I added a new class called DynamicDatabaseFactory.cs, which takes the server, database, userID and password and creates a new Database object (same as the DatabaseFactory class in the EntLib). In order to support this, the EasyObject class now has the following new properties:

• ConnectionString - set this to a valid connection string (with or without user logon credentials)
• ConnectionServer - The name of the server to connect to
• ConnectionDatabase - The name of the database to connect to
• ConnectionUserID - The UserID to use for login
• ConnectionPassword - The Password for the user
• UseIntegratedSecurity - flag indicating if the dynamic connection should use integrated security.
• DBProviderType - enumeration indicating the provider type, currently only SQL Server and Oracle are supported.

Depending on which properties you set, certain behavior is triggered in the EasyObject.GetDatabase() function. Of course, if you don't set any of these properties, the current behavior of reading from the dataConfiguration file will still work.

Here are the test scenarios that I used:

Code: Select all

         // Scenario #1 : Complete connection string, no integrated security
         this.ConnectionString = \"server=127.0.0.1;database=Northwind;User ID=sa;Password=password;Integrated Security=false;\";

         // Scenario #2 : Complete connection string, with integrated security
         this.ConnectionString = \"server=127.0.0.1;database=Northwind;Integrated Security=true;\";

         // Scenario #3 : Partial connection string, no login credentials
         this.ConnectionString = \"server=127.0.0.1;database=Northwind;Integrated Security=false;\";
         this.ConnectionUserID = \"sa\";
         this.ConnectionPassword = \"password\";

         // Scenario #4 : No connection string, no integrated security, userID and password specified
         this.ConnectionServer = \"127.0.0.1\";
         this.ConnectionDatabase = \"Northwind\";
         this.ConnectionUserID = \"sa\";
         this.ConnectionPassword = \"password\";

         // Scenario #5 : No connection string, with integrated security
         this.ConnectionServer = \"127.0.0.1\";
         this.ConnectionDatabase = \"Northwind\";
         this.UseIntegratedSecurity = true;


Comments are welcome. Release is set for tomorr... er, I mean later today!!!

[neilx]

Where is the best place to do this?

I want to set these values based on the current web server/user/Request information. e.g. If I want to use the dev database, I add ?db=dev to my url and then want the EntLib to use dev from then on.

[mgnoonan]

Hi neilx,

The EntLib team did a fair job of munging this up for me in all the versions after 1.1. The plan was to support dynamic credentials in the not-free version of EO2, but the truth is I have not found a solution for this yet (one of the reasons why EO2 has never been officially released).

My [Google-assisted] research shows that if anyone has found a solution to this problem for the EntLib, they are keeping it to themselves.

[mgnoonan]

Where is the best place to do this?

I want to set these values based on the current web server/user/Request information. e.g. If I want to use the dev database, I add ?db=dev to my url and then want the EntLib to use dev from then on.

You can still do this, but you'll have to modify the default concrete class constructor to accept a string value, which is the name of the connection string in your config file.

Code: Select all

   public class Employees : _Employees
   {
      public Employees(string key)
      {
         this.DatabaseInstanceName = key;
      }
   }


Then you can toggle the connection strings from the application code.

[neilx]

Is there a way to avoid config files and set the EntLib connection string in the application? Doing it per object seems a little repetitive. Doing it from config files that need to be copied to all projects that use the Easy0bjects Business Objects is a maintenance headache.

[mgnoonan]

Agreed, not to mention the other reasons you might not be able to specify the connection credentials in the config file. That's why I added the capability in EO 1.x.

But as I said, they did an excellent job of breaking my solution when they went to .NET 2.0, and I have not had time to devise an alternative for EO2.

The only solution I have seen online, which doesn't work for me, is to cast the database object directly to a concrete type (such as SqlDatabase or OracleDatabase) and set the connection credentials that way.

As for the maintenance headache that is copying the config files, you could put them in machine.config, if that is allowed. That would affect all applications at once.

Your original question involved changing databases dynamically using the query string, so the credentials have to be read from somewhere and passed around to the business layer, how were you planning to accomplish that? By prompting the user and then storing the credentials?

[neilx]

Machine Config is ok for one computer, but we have 4 dev machines, a staging machine and a production machine. I would prefer one location for connection strings. Our main client apps use the database to store connection info for each client and the app calculates it on the fly.

Generally, only our IT staff change connections during a session, and this is usually for debugging and troubleshooting. We append an identifier to the Querystring and some code works out which database(s) to use for the page.

I will use the this.DatabaselnstanceName = ... approach for now and see how annoying keeping config files up to date gets.

Thanks.

[neilx]

It looks like I can use a custom configuration source instead of an app.config or web.config file!

I see in EasyObject.cs you have commented out the code that EntLib broke so I have added in to my copy of the project the appropriate code to set the default database to the current object's dynamic information. Now I can define the DataSource, InitialCatalog, UserID and Password at runtime.

It isn't up to release standards, but you are welcome to have it.

[mgnoonan]

Sure, you can send it over as a zip to my email address.

[gajanaik]

Hello Matt
I like the feature of dynamic connection string .. i tried this in my code
public class Asset : _Asset
{
public Asset()
{
this.ConnectionServer = \"gaja\";
this.ConnectionDatabase = \"MyData\";
this.ConnectionUserID = \"sa\";
this.ConnectionPassword = \"abc\";
this.UseIntegratedSecurity = false;
this.DefaultCommandType = CommandType.Text;
}
}
But it fails.. if i remove this then the application reads the connectionstring from webconfig file and it works... the moment i put this it fails.. do i need to do something special.. i have ur latest Easyobjects.. do i need to have the paid version of easyobjects. also u are talking about dynamicdbfactory.cs. i don't see that in the easy object src solutions.

your advice is very much appriciated.
Thanks
Gaja

[mgnoonan]

Right, this solution currently only works for EasyObjects version 1.x. I'm still working on a solution for 2.0.

[gajanaik]

Thank for the reply matt..
As i was searching for net for dynamic connection string properties..i found this..
http://easyobjects.tigris.org/
so was this link for easy objects 1.1

so do u still have paid version of easy objects and if so what is the difference between the paid and the free version.
Also i belive enterprise 3.1 has features for dynamic connection string was reading briefly on it some blogs on the net.. could not follow . I thought DAAB always had that feature.

So how did u acheive dynamic connection in ver 1.1. also i see ES has the feature.
What can i do to get it to work temporarily . or can i help u in some way.
I am a fan of easy objects and have used it in couple of my projects..

Thanks a lot for all the good work
Gaja

[mgnoonan]

Thank for the reply matt..
As i was searching for net for dynamic connection string properties..i found this..
http://easyobjects.tigris.org/
so was this link for easy objects 1.1

Yes.

so do u still have paid version of easy objects and if so what is the difference between the paid and the free version.
Also i belive enterprise 3.1 has features for dynamic connection string was reading briefly on it some blogs on the net.. could not follow . I thought DAAB always had that feature.

No, it never had it and people have been asking them to support this since day one, practically. That's why I chose to add the feature to EasyObjects. All the "solutions" I have seen posted online involve casting the database object directly as a certain type, such as SqlDatabase and then using the ConnectionBuilder classes to generate the connection string. While this might work for a given situation, it does not fit in with EasyObjects. IMHO, any solution works for *all* the supported databases, otherwise it is just a hack.

So how did u acheive dynamic connection in ver 1.1. also i see ES has the feature.

Suffice it to say that my solution for 1.1 does not work in 2.0 because of changes to EntLib architecture, and to a lesser degree because of the way connection strings are stored in 2.0.

What can i do to get it to work temporarily . or can i help u in some way.
I am a fan of easy objects and have used it in couple of my projects..

My current thinking is that I will need to create a "ConnectionBuilderFactory" class that will wrap the connection string builder objects, returning the proper type as needed and then building the connection string on the fly. But I am not anywhere near final coding to implement this.

You can try going with the direct cast solution, if you are only using one database platform.