Entlib, Oracle and per user credentials under ASP.NET

Topics: Data Access Application Block
Mar 6, 2007 at 9:43 AM
Hey all,

I've been asked for a customer to write a piece of data access using EntLib 2.0. I'd like to use the standard entlib database factory class because a transition to SQL Server is still a viable option in the near future.

The problem is that the project will use Oracle till then, and there is no integrated security activated on the Oracle Server (and it won't be in the near future). For compliance reasons every user must connect using their own unique user/pass which is the same as the web application. So basically we do forms authentication, store the user/pass in the session and pass it to the datalayer when appropriate.

The problem is that I cannot find a way to merge the credentials into the connection string for that specific session. The only solution I found was to use the direct constructor of the OracleDatabase class, but that won't play nice with other configurable stuff in the web.config (like packages, prefixes and other configurable data).

Any ideas on how to fix this easily? I've considered sub-classing the OracleDatabase class, but would like an easier solution... Is there a method, event or anything that lets you manipulate the connection string just before it's use, or can I plug into the database settings class somehow and get my configuration data in there without affecting other user sessions?

Mar 6, 2007 at 4:41 PM

I just wrote this on the fly this morning, but it may work for you. It allows you to specify your Oracle Connection String on the fly but grab the package from your app.config or web.config. I don't have Oracle to test it, but it may get you in the right direction:

    class Program
        static void Main(string[] args)
            Database db = MyDatabaseFactory.Create("[your user's connection string]", "Connection String");
    public static class MyDatabaseFactory
        public static Database Create(string connectionString, string connectionStringName)
            OracleConnectionSettings oracleConnectionSettings =
                OracleConnectionSettings.GetSettings(new SystemConfigurationSource());
            if (oracleConnectionSettings != null)
                OracleConnectionData oracleConnectionData = oracleConnectionSettings.OracleConnectionsData.Get(connectionStringName);
                if (oracleConnectionData != null)
                    IOraclePackage[] packages = new IOraclePackage[oracleConnectionData.Packages.Count];
                    int i = 0;
                    foreach (IOraclePackage package in oracleConnectionData.Packages)
                        packages[i++] = package;
                    return new OracleDatabase(connectionString, packages);
            return new OracleDatabase(connectionString);

where Connection String is the name of the Connection String in your config file that has your packages assigned to it:

<?xml version="1.0" encoding="utf-8"?>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    <section name="oracleConnectionSettings" type="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.Configuration.OracleConnectionSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  <dataConfiguration defaultDatabase="Connection String" />
    <add name="Connection String">
        <add prefix="blah" name="Oracle Package" />
    <add name="Connection String" connectionString="Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
      providerName="System.Data.OracleClient" />

Much of this is grabbed from the OracleDatabaseAssembler Class in the source code because it assembles OracleDatabase Classes. I just added the ability to inject your own connection string.




David Hayden
Microsoft MVP C#
Mar 20, 2007 at 11:23 PM
Thank you. Your pointer about the OracleDatabaseAssembler led me to the ability to redirect the default System.Data.OracleClient connection to a custom Database class. I just inherited from OracleDatabase and placed an attribute to my own DatabaseAssembler above the class definition. This allows very clean integration.