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;
	try
	{
		string connString = "Provider=OraOLEDB.Oracle;Data Source=R2D2;User ID=foo;Password=bar";
		conn = new OleDbConnection(connString);
		conn.Open();
		string strQuery = "select * from siteusers where login='" + strLogin + "'";
		OleDbCommand obCmd = new OleDbCommand(strQuery, conn);
		OleDbDataReader obReader = obCmd.ExecuteReader();
		while (obReader.Read())
		{
			string strVal = obReader["password"].ToString();
			if (string.Compare(strVal, strPwd) == 0)
			{
				bRet = true;
			}
		}
	}
	catch (OleDbException ex)
	{
		Response.Write(ex.Message);
		throw ex;
	}
	finally
	{
		if (null != conn)
		{
			conn.Close();
		}
	}
	return bRet;
}