Geekly Articles each Day
📜 ⬆️ ⬇️

Access Oracle Database with ADO.NET Entity Framework


Good day.

When developing software, it has always been guided by a simple rule: the less third-party components, platforms, technologies are used in a developing project, the better. Almost all ingenious is simple. Unfortunately, the developer does not always have the opportunity to freely choose the tools and systems with which he works. So I got the project Windows Forms + ODAC + Oracle DB Server.

I was very happy to hear about the release of the Oracle Data Access Components (ODAC) for the Microsoft Entity Framework and the LINQ to Entities (Beta2) . I was waiting for this moment and now it has happened! You can not use third-party ORM - all turnkey.
')
How to teach an application to use the Entity Framework through an ODAC client installed on the end machines is described in a step-by-step instruction from Oracle . Who cares how to get rid of the need to install an Oracle client on users' machines, please under cat.

General requirements



To user system:
  1. OS is not lower than MS Windows XP SP3
  2. Microsoft .NET Framework 4
  3. Additional ~ 135 MB of free disk space

To the developer system:
  1. Visual Studio 2010 Service Pack 1
  2. Oracle Database server 9.2 or higher
  3. ODAC for Microsoft Entity Framework and LINQ to Entities


Link Libraries

To organize access to the Oracle database, you need to add the following libraries from the ODAC.NET package to the project: connect the library "client path" \ client_1 \ odp.net \ bin \ 4 \ Oracle.DataAccess.dll (~ 1.4 MB) as Reference and set the “ Copy local ” property to “ true ”.

The following files just need to be added to the project and set the “ Copy to Output Directory ” property to “ Copy if newer ”:

The connection string should look the same as in the tnsnames.ora file:

Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = ServerNameOrIP) (PORT = PortNumber))) (CONNECT_DATA = (SERVICE_NAME = DBName))); User Id = UserName; Password = UserPa $$ w0rd;

This is enough to provide access to the database without installing the client on the user's machine:
private bool TestConnect()
{
try
{
var oracleConnection = new OracleConnection
{
ConnectionString =
"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ServerNameOrIP)(PORT=PortNumber)))(CONNECT_DATA=(SERVICE_NAME=DBName)));User Id=UserName;Password=UserPa$$w0rd;"
};
oracleConnection.Open();
var oracleCommand = new OracleCommand
{
CommandText = "select sysdate from dual" ,
Connection = oracleConnection,
Transaction = null
};
var oracleDataAdapter = new OracleDataAdapter {SelectCommand = oracleCommand};
var sysDateDataSet = new DataSet( "SomeName" );
oracleDataAdapter.Fill(sysDateDataSet, "dateTimeTable" );
return sysDateDataSet.Tables[0].Rows.Count > 0;
}
catch (Exception exx)
{
MessageBox.Show( string .Format( "Could not connect directly to an Oracle database: \n {0}" , exx.Message));
return false ;
}
* This source code was highlighted with Source Code Highlighter .

What you need to do to use the Entity Framework Model without installing an Oracle client

Create, or your own ADO.NET Entity Data Model, or, as in the example, “HRModel” with the context “HREntities”. To do this, use the Add Wizard, as shown in the Oracle manual mentioned above .

The project configuration file (App.Config / Web.Config) will automatically (if you select this corresponding item in the wizard) a connection string to the Oracle database is added:
< add name ="HREntities" connectionString ="metadata=res://*/HRModel.csdl|res://*/HRModel.ssdl|res://*/HRModel.msl;provider=Oracle.DataAccess.Client;provider connection string="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ServerNameOrIP)(PORT=PortNumber)))(CONNECT_DATA=(SERVICE_NAME=DBName)));User Id=UserName;Password=UserPa$$w0rd;"" providerName ="System.Data.EntityClient" />

Here you should pay special attention to the fact that the value assigned to the attribute “ connection connection ” must be in double quotes (to denote them, you must use the code & _q_u_o_t). This is also worth considering when dynamically forming the connection string.

Then in the project configuration file you need to create a section in which we add our data provider to those already registered in the system, the list of which is in the file "% windir% \ Microsoft.NET \ Framework \ v4.0.30319 \ Config \ machine.config". If ODAC is installed on the machine, then the “Oracle.DataAccess.Client” provider will already be registered in the system configuration file and an attempt to add our provider will cause an error when the application starts: “Collision” InvariantName 'is constrained to be unique. Value 'Oracle.DataAccess.Client' is already present. ”

To prevent such a situation, using the tag
< remove invariant = "Oracle.DataAccess.Client" />
provider “Oracle.DataAccess.Client” will be removed from the list of data providers, if present. Then we add our supplier. It will be added to the list of EntityClient providers registered in the system:
< system.data >
< DbProviderFactories > <br> < remove invariant ="Oracle.DataAccess.Client" />
< add name ="Oracle.DataAccess.Client" invariant ="Oracle.DataAccess.Client" description ="Oracle Data Provider for .NET" type ="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.112.2.40, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</ DbProviderFactories >
</ system.data >


Subtleties

When a model is dynamically connected to the database using the “correct” method from MS , attention should be paid to the not quite correct formation of the connection string through the EntityConnectionStringBuilder object
string providerName = "Oracle.DataAccess.Client" ;
string dataSourse = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ServerNameOrIP)(PORT=PortNumber)))(CONNECT_DATA=(SERVICE_NAME=DBName)));User Id=UserName;Password=UserPa$$w0rd;”;
var sqlBuilder =
new SqlConnectionStringBuilder
{
DataSource = dataSourse
};
string providerString = sqlBuilder.ToString();
var entityBuilder =
new EntityConnectionStringBuilder
{
Provider = providerName,
ProviderConnectionString = providerString,
Metadata =
@"res://*/HRModel.csdl|res://*/HRModel.ssdl|res://*/HRModel.msl"
};
using ( var conn =
new EntityConnection(entityBuilder.ToString()))
{
conn.Open();
Console .WriteLine( "Just testing the connection." );
conn.Close();
}

* This source code was highlighted with Source Code Highlighter .

As a result, entityBuilder.ToString () will return a connection string with extra single quotes, which enclose the entire value of the provider connection string attribute
metadata=…;provider=Oracle.DataAccess.Client;provider connection string= ' Data Source= \" (DESCRIPTION=(… ); User Id=UserName;Password=UserPa$$w0rd; \"'

While the Oracle provider requires a connection string of the following format:
metadata=…;provider=Oracle.DataAccess.Client;provider connection string= \" Data Source=(DESCRIPTION(…); User Id=UserName;Password=UserPa$$w0rd; \"

- otherwise it gives an error.
While you can form a connection string ugly:
private bool DynamicConnect()
{
const string providerName = "Oracle.DataAccess.Client" ;
const string serverName = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ServerNameOrIP)(PORT=PortNumber)))(CONNECT_DATA=(SERVICE_NAME=DBName)));User Id=UserName;Password=UserPa$$w0rd;" ;
const string metadata = "metadata=res://*/HRModel.csdl|res://*/HRModel.ssdl|res://*/HRModel.msl" ;
var entBild = string .Format( "metadata={0};provider={1};provider connection string=\"Data Source={2}\";" , metadata, providerName, serverName);
try
{
var conn = new EntityConnection(entBild);
conn.Open();
var hrEntities = new HREntities(conn);
var tmpResult = hrEntities.BRIDGE.Count();
conn.Close();
return true ;
}
catch (Exception exx)
{
MessageBox.Show( string .Format( "Could not connect directly to an Oracle database: \n {0}" , exx.Message));
return false ;
}
}

* This source code was highlighted with Source Code Highlighter .

Perhaps this is the echo of the beta version - let's see how it will be in the release, which, as Oracle promises, will take place in the fourth quarter of 2011.


As a result, you can write to the asset:


In the passive can be entered:

In the near future, there is a desire to compare the speed of work through the ODAC for Microsoft Entity Framework + Oracle vs System.Data.SqlClient + MS SQL Server.

Thank you for your time.

List of sources on the topic:

Source: https://habr.com/ru/post/127603/

More articles:


All Articles