Problem with Windows Service that connect to Oracle

Today Joseph was creating a windows service, which had an schedule task to execute something on an Oracle Server.

Everything worked out of the Windows Service but as soon as he added it to the Windows Service, it fail.

He suspected it had something to do with the 64 bit platform so he made sure to compile for AnyCPU, but it did not work...

Answer: It was the OracleClient fault. The OracleClient for ADO.NET do not work for AnyCPU. You need to use the 64 or 32 bit version.

Using MARS or There is already an open DataReader associated with this Command which must be closed first.

This is a very strange error that you can find sometimes when working with ADO.NET.

David McKean from MSFT says:

This occurs when you have multiple DataReaders open concurrently on the same connection,
ie you call SqlCommand.ExecuteReader but don't close the SqlDataReader returned by this
method before calling it again (either on the same command or another command on the same connection).

It requires a feature called MultipleActiveResultSets which is not available in all providers.

For example SQL2000 does not support it, it was implemented starting from SQL2005.

Also .NET 2.0 must be used.

For more information about enabling Multiple Active Result Sets see: http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.80).aspx

A good recommendation to make sure that the the readers are closed is to put them inside a using statement, in that case,
no matter if an exception happened they will be closed and disposed.

If you are using SQL Server 2000, MARS is not available so you can create two different connection objects.

Another good article about this issue is: http://blogs.msdn.com/b/spike/archive/2009/08/20/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-closed-first-explained.aspx

But in general to use it is just a change in the connection string:

<connectionStrings>
    <clear />
      <add name="VasquezDB" 
         connectionString="Data Source=rvasquez;Initial Catalog=VasquezDB;
                 Integrated Security=True;MultipleActiveResultSets=Yes" />
 </connectionStrings>

Good Luck

NOTE: a good link with more details about MARS is:

http://blog.typps.com/2011/04/mars-multiple-active-result-sets.html

No more System.Data.OracleClient

 Microsoft announced that they won’t support the System.Data.OracleClient anymore :(

The message says that it will still be available in .NET 4.0 but “deprecated” and that it wont impact existing applicatoions.

So what are my options?

Well you have to go to a third party. Not now but eventually.

Oracle Data Provider for .NET (ODP.NET) (Oracle states that ODP.NET is Free)

DataDirect ADO.NET Provider for Oracle

dotConnect for Oracle

Migration to ODP.NET

Installation

Microsoft OracleClient to ODP.NET Application Migration: Code Migration

Oracle provide general instructions. In summary you need to:

1. Add the references to Oracle.DataAccess
2. Change namespaces: System.Data.OracleClient by  Oracle.DataAccess.Client; and maybe add “using Oracle.DataAccess.Types”
3. Update the connection String (most attributes are the same). Change Integrated Security by  "User Id=/", change “Server” attribute to “Data Source” and remove Unicode
4. The following is anoying but you have to add after all OracleCommands something like OracleCommand1.BindByName = true;

Deployment

• Oracle Universal Installer (either in normal installation or Silent Install)
• XCopy (I like this :) ) just remember to download the ODAC xcopy version