Random thoughts of wisdom - the personal blog of Michael Holm Andersen

Using Oracle with ASP.NET

I've been using Oracle in a ASP.NET 2.0 Web Application for the first time, and I must say it's a bit different that using Microsoft SQL Server. I've been through quite a bit of trial-and-error, and for those of you who are planning to use Oracle as the backend data in your .NET application, the following tips might be very useful:

Use SqlDataSource
Some articles state that using the SqlDataSource object is not working properly, however I had only a few (see below) problems using both static and dynamic SqlDataSources. The SqlDataSource can of course be used as datasource against GridView, Repeaters and other data-binding controls.  

Use dynamic SqlDataSource's if you need to change Connection String

It seems (at least in my environment) that the Oracle Client or .NET (or perhaps due to Connection Pooling ?!) cache the Connection. I needed to change the credentials for the current connection, however simply changing the ConnectionString property for a static SqlDataSource did'nt have any effect. The code run without errors, but when I checked which credentials the query actually used, it was the old values (the ones used when the SqlDataSource object was created). So I had to create my SqlDataSource programmatically like this:

SqlDataSource SqlDataSource1;
string connString = "Data Source=XX;Persist Security Info=True;User ID=ZZ;Password=ABC;Unicode=True;";
string providerName = "System.Data.OracleClient";
SqlDataSource1 = new SqlDataSource(providerName, connString, "");

AutoCommit might be necessary
Using a static declared SqlDataSource I had to use "Autocommit=true" in my ConnectionString in order to persist (commit) my changes to the database, e.g.:

string connString = "Data Source=XX;Persist Security Info=True;User ID=YY;Password=ZZ;Unicode=True;Autocommit=True";

Use to_date in your SQL

As my application is a kind of "generic table editor" all SQL is actually generated at run-time (against any table the current user has access to). This caused some problems, especially with dates in different formats. I ended up using the Oracle "to_date" function in order to explicit tell Oracle the format - here's a simplified example of how my code looks like:

protected string ConvertToExplicitOracleDate(string value)
    IFormatProvider ci = new System.Globalization.CultureInfo("en-US", true);
    DateTime dt;

    if (DateTime.TryParseExact(value, "dd-MM-yyyy HH:mm:ss", ci, System.Globalization.DateTimeStyles.None, out dt))
         value = "to_date('" + dt.ToString("dd-MM-yyyy HH:mm:ss", ci) + "', 'dd-mm-yyyy hh24:mi:ss'), ";

    return value;

Alter Session is ignored

Oracle has a command called "Alter Session" which may be used to change the values of NLS parameters at run-time. However I never got this to work, instead I used the Oracle to_date and to_number as mentioned above.

Comments (1) -

  • Jacob Buus

    14-09-2007 17:28:26 |

    Greate article. You might wanna add a section dedicated to Oracle's own "Visual Studio.NET Development Tools For Oracle", which - by the way - doesn't work that great eitherSmile

Comments are closed