DAAB and Dynamic Connection Strings

Link. August 15, 2005. Comments [3]. Posted in: .NET
I've been working on a new project this past few days, as part of which we're using the Data Access Application Block from the Enterprise Library, which is OK (for a fairly low-level ADO.NET wrapper). One of the requirements we have is that we need to support dynamic connection strings, built at runtime, because we don't know the database server and database name we are connecting to until runtime. Now, I know this has security implications, but its required for reasons I don't plan on explaining here.

But here comes the rub: I looked everywhere, and was unable to find a way within the DAAB to support dynamic connection strings. There's just no way to create a SqlDatabase object having just a connection string (or at least the components thereof); the only way is to have it stored in the configuration file. This does us no good as a) we can't have a configuration file with that information because of what I said above and a few other reasons, and b) there's really no "known" set of database we allow connections to that can be set at deployment time.

I did end up finding a way, but I consider this a really ugly hack. The solution was to mimick the information in the configuration file by manually creating and filling an object of type DatabaseSettings, and then bypassing the normal initialization provided by the provider factory:


public DataBase Connect(string theServer, string theDatabase)
{
   ConnectionStringData csd = new ConnectionStringData();
   csd.Name = "X";
   csd.Parameters.Add(new ParameterData("Server", theServer));
   csd.Parameters.Add(new ParameterData("Database", theDatabase));
   csd.Parameters.Add(new ParameterData("Integrated Security", "true"));
   DatabaseTypeData sqldb =
      new DatabaseTypeData("Sql Server", typeof(SqlDatabase).FullName);
   InstanceData instance =
      new InstanceData(csd.Name, sqldb.Name, csd.Name);
   
   DatabaseSettings dbSettings = new DatabaseSettings();
   dbSettings.ConnectionStrings.Add(csd);
   dbSettings.DatabaseTypes.Add(sqldb);
   dbSettings.Instances.Add(instance);
   dbSettings.DefaultInstance = instance.Name;
   
   DatabaseConfigurationView configView = 
      new DummyDatabaseConfigurationView(dbSettings);
      
   SqlDatabase db = new SqlDatabase();
   db.Initialize(_configView);
   db.ConfigurationName = _dbSettings.DefaultInstance;
   // now db is initialized
   return db;
}

internal class DummyDatabaseConfigurationView : DatabaseConfigurationView
{
   private DatabaseSettings _dbSettings;

   /// <summary>
   /// Creates a new instance of the class
   /// </summary>
   public DummyDatabaseConfigurationView(DatabaseSettings dbSettings)
      : base(new ConfigurationContext())
   {
      _dbSettings = dbSettings;
   }

   public override DatabaseSettings GetDatabaseSettings()
   {
      return _dbSettings;
   }
} // class DummyDatabaseConfigurationView

Is there really no other way? If the answer is no, please do take this into consideration for future versions!



Tuesday, November 01, 2005 4:18:18 PM (SA Pacific Standard Time, UTC-05:00)

We had to use an encrypted connection string and thus just simply inherited from SqlDatabase overriding the GetConnection method. In here we decyphered the ConnectionString and used the base class method for creating the connection.



You could simply add properties to the derived class that would inject those parts into the ConnectionString to override the defaults. This seems like it would be easier most appropriate.



I didn't provide the real e-mail but it is fred_hirschfeld a_t hotmail d_o_t com.

Fred Hirschfeld
Monday, May 01, 2006 4:25:38 PM (SA Pacific Standard Time, UTC-05:00)
Oh man that is ugly. Fortunately, they added dynamic connection strings in the DAAB for .NET 2.0. Tom Hollander talks about it in his blot. Unfortunately, we are stuck in .NET 1.1! Thanks for posting your solution.
Monday, May 01, 2006 5:55:29 PM (SA Pacific Standard Time, UTC-05:00)
Here's solution translated to VB.NET

Imports System.Data.SqlClient
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Configuration


Public Class DataHelper

Public Shared Function DynamicConnect(ByVal theServer As String, ByVal theDatabase As String, ByVal theUser As String, ByVal thePassword As String) As Microsoft.Practices.EnterpriseLibrary.Data.Database
Dim csd As New ConnectionStringData
csd.Name = "X"
csd.Parameters.Add(New ParameterData("Server", theServer))
csd.Parameters.Add(New ParameterData("Database", theDatabase))
csd.Parameters.Add(New ParameterData("Integrated Security", "false"))
csd.Parameters.Add(New ParameterData("User ID", theUser))
csd.Parameters.Add(New ParameterData("Password", thePassword))

Dim sqldb As DatabaseTypeData = New DatabaseTypeData("Sql Server", "Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase")
Dim instance As New InstanceData(csd.Name, sqldb.Name, csd.Name) 'not sure about 3rd arg

Dim dbSettings As New DatabaseSettings
dbSettings.ConnectionStrings.Add(csd)
dbSettings.DatabaseTypes.Add(sqldb)
dbSettings.Instances.Add(instance)
dbSettings.DefaultInstance = instance.Name

Dim configView As DatabaseConfigurationView = New DataHelper.DummyDatabaseConfigurationView(dbSettings) 'set to dummy Class

Dim db As New Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase
db.Initialize(configView)
db.ConfigurationName = dbSettings.DefaultInstance
Return db

End Function

Private Class DummyDatabaseConfigurationView
Inherits DatabaseConfigurationView
Private _dbSettings As DatabaseSettings
Sub New(ByVal dbSettings As DatabaseSettings)
MyBase.new(New ConfigurationContext)
Me._dbSettings = dbSettings
End Sub
Public Overrides Function GetDatabaseSettings() As DatabaseSettings
Return Me._dbSettings
End Function
End Class

End Class
Comments are closed.

Syndicate

About

Tomas Restrepo is a software developer located in Colombia, South America. His interests include .NET, Connected Systems, PowerShell and lately dynamic programming languages. More...

tomasrestrepo @ twitter My Flickr photostream My saved links on delicious My Technorati Profile

email: tomas@winterdom.com
msn: tomasr@passport.com

View my profile on LinkedIn

MVP logo

Ads


Categories

Statistics

Total Posts: 1050
This Year: 1
This Month: 1
This Week: 0
Comments: 825

Archive

Other

Copyright © 2002-2008, Tomas Restrepo.

Powered by: newtelligence dasBlog 2.2.8279.16125

Sign In