Use Oracle Database in ASP.Net Application
There are lot of examples on how ASP.Net applications are used with SQLServer
or MS Access database. But these are not the only databases that are in use in
enterprise applications. For one of recent projects for a client we have to
connect to connect Oracle9i database for pushing and pulling the data. We had
some interesting experiences along the way. So we decided that we will share
with rest of the developer community too.
What data provider is used for Oracle database?
You have following choices of data providers for non-MS SQL Server databases.
OleDb Data Provider
ODBC Data Provider
Data Provider for Oracle
Although data provider for Oracle is available for download from Microsoft, but
we decided not to use it because it is still in Beta state and we are working
on an actual high volume internet application. So the choice was either use
ODBC or OleDb data providers. Since an OleDb provider is available for
Oracle9i, we decided to use that.
Why did not we use microsoft's OleDb provider for Orcale9i? Infact we did try.
Believe it or not, we failed to make a simple connection with the database. And
on top of that the error information returned was least helpful to figure out
what the problem was. Following is the connection string we used.
"Provider=MSDAORA;Data Source=R2D2;User ID=foo; Password=bar";
We did not have any luck with above mentioned connection string. Finally we
decided that we will use Oracle Provider for OLE DB (OraOLEDB). When you
install Oracle9i, this data provider gets installed along with it.
What connection string to use?
The connection string to connect to Oracle9i is the same as the one that used
for Microsoft OLEDB provider for Oracle with only one difference that instead
of MSDAORA, you will use OraOLEDB.Oracle as the provider. To
connect to an Oracle database using OraOLEDB, the OLE DB connection string must
be as follows:
"Provider=OraOLEDB.Oracle;Data Source=R2D2;User ID=foo; Password=bar";
Make sure that you have entry for the data source in tnsnames.ora file
and TNSListener service is running. For more information on OraOLEDB
provider refer to Orcale9i documentation.
How to use it?
Following is sample code that we used for validating user information for
logging into the application.
private bool ValidateUser(string strLogin, string strPwd)
bool bRet = false;
OleDbConnection conn = null;
string connString = "Provider=OraOLEDB.Oracle;Data Source=R2D2;User ID=foo;Password=bar";
conn = new OleDbConnection(connString);
string strQuery = "select * from siteusers where login='" + strLogin + "'";
OleDbCommand obCmd = new OleDbCommand(strQuery, conn);
OleDbDataReader obReader = obCmd.ExecuteReader();
string strVal = obReader["password"].ToString();
if (string.Compare(strVal, strPwd) == 0)
bRet = true;
catch (OleDbException ex)
if (null != conn)