customizing oracle connection for globalization parameter.

Topics: Data Access Application Block
Sep 20, 2011 at 11:12 AM

Hi, I'm using entlib with oracle and everything works properly.

I need to work on globalization parameters to set the connection to the database based on the location of the user.

proceeded without entlib like this:

OracleConnection con = new OracleConnection(oracle_connection_string);
con.Open();

// get globalization object to set values
OracleGlobalization glb = OracleGlobalization.GetThreadInfo();
glb.Sort = "BINARY_CI";
glb.Comparison = "LINGUISTIC";

// set new globalization (nls) values for the session
con.SetSessionInfo(glb);

// get command object and set statement to execute
OracleCommand cmd = con.CreateCommand();

please, how can I do this with entlib?

thanks

Sep 20, 2011 at 11:17 AM

Hi,

You can set your parameters in your application's configuration file; to be specific, in the appSettings section. To access those, you can use the ConfigurationManager class.

Sep 20, 2011 at 1:01 PM

Thanks for the answer but I think I have not explained well.

I did not want to know how to take the parameters from the config but how to set globalization in the db connection to use directly the methods DatabaseFactory.CreateDatabase ().Execute???(dbCmd) (??? = Dataset, NonQuery, Scalar, etc..) .

In the entlib source class DataBase I saw that the Execute??? methods always set the connection of the command using the method PrepareCommand (cmd, wrapper.connection) and then lost the customization of the connection made ​​before.

using the following sample code:

Database db = DatabaseFactory.CreateDatabase();
string sql = @"select id, companyname from table1 order by companyname";
DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);
or
Database db = DatabaseFactory.CreateDatabase();
OracleCommand cmd = new OracleCommand(); 
cmd.CommandText = @"select id, companyname from table1 order by companyname";
cmd.CommandType = CommandType.Text;
DataSet ds = db.ExecuteDataSet(cmd);

what I can do to customize the db connection?

tanks.

Sep 22, 2011 at 10:41 AM

Hi,

I believe the is no other way on modifying the connection string inside the Database object as the connection string is set to read only. The work around would be by using the Fluent Configuration API. Hope this helps.

 

Noel Angelo Bolasoc
Avanade Software
Avanade, Inc.
Contact Us

 

Sep 22, 2011 at 3:48 PM

maybe you can create a new Database class derived from the OracleDatabase class by overriding the getConnection method.
 I prepared these class:

namespace Core.DataAccessLayer.Databases
{
    [ConfigurationElementType(typeof(OracleDatabaseData)), OraclePermission(SecurityAction.Demand)]
    public class OdpDatabase : Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase
    {
        public OdpDatabase(string connectionString)
            : base(connectionString)
        {
        }

        public OdpDatabase(string connectionString, IEnumerable<IOraclePackage> packages)
            : base(connectionString, packages)
        {
        }

        public OdpDatabase(string connectionString, IEnumerable<IOraclePackage> packages, IDataInstrumentationProvider instrumentationProvider)
            : base(connectionString, packages, instrumentationProvider)
        {
        }

        public override DbConnection CreateConnection()
        {
            DbConnection dbCon = base.CreateConnection();

            OracleConnection oraCon = dbCon as OracleConnection;
            if (oraCon != null)
            {
                OracleGlobalization oraGlb = OracleGlobalization.GetThreadInfo();
                oraGlb.Sort = "BINARY_CI";
                oraGlb.Comparison = "LINGUISTIC";
                oraCon.SetSessionInfo(oraGlb);
            }

            return dbCon;
        }
    }
}


 and set the config as follows:

<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
  </configSections>
  <dataConfiguration defaultDatabase="OracleConnectionString" >
    <providerMappings>
      <add databaseType="Core.DataAccessLayer.Databases.OdpDatabase, Core.DataAccessLayer.Databases" name="Oracle.DataAccess.Client" />
    </providerMappings>
  </dataConfiguration>
    <connectionStrings>
        <add name="OracleConnectionString" connectionString="my_conn_str" providerName="Oracle.DataAccess.Client"/>
      </connectionStrings>
</configuration>


 but when I run I get this error:
 

The type 'Core.DataAccessLayer.Databases.OdpDatabase, Core.DataAccessLayer.Databases' cannot be resolved. Please verify the spelling is correct or that the full type name is provided."


 how can I fix this?  what's wrong?

thanks.

 
 
 
Sep 23, 2011 at 10:15 AM

Is the Core.DataAccessLayer.Databases located on another dll? Can you check if you have reference to that dll?

 

Noel Angelo Bolasoc
Avanade Software
Avanade, Inc.
Contact Usl

Sep 23, 2011 at 2:19 PM

yes, the class is in another library and there is a reference to it. I think the configuration of the provider that is not correct. There is an example of how to create a custom database provider?
Thank you Noel.

Sep 23, 2011 at 3:26 PM

Yes, I have a sample solution here though not created to log on Oracle but basically will give you the idea. Kindly drop us an email on entlib.support@avanade.com

 

Noel Angelo Bolasoc
Avanade Software
Avanade, Inc.
Contact Us