Developing applications for Oracle databases in. NET

In fact, this is not a new topic. But before in many projects, always encountered in the Oracle database access, there will be a variety of problems, the most basic is to install a variety of clients on the client, the version is different, there are various problems.

It's not that hard to look at it in a quiet mind. I'm here to summarize how the. NET application, simple and elegant use of the Oracle database.

The first principle: do not rely on

The best case scenario is that the program can complete its own data access without the need to install the so-called Oracle Client, which is a cumbersome and painful task.

Let's start by looking at whether Oracle database access can be implemented without anything.

In fact. NET itself comes with components for Oracle database access, which is the following System.Data.OracleClient

Our code is as follows (this is the most primitive of the ADO code, just to do the demo)

Using System;
Using System.Data.OracleClient;

Namespace Consoleapplication
{
Class Program
{
static void Main (string[] args)
{
var connectionString = "User Id=system;password=password;data SOURCE=192.168.56.101:1521/ORCL";

using (var connection = new OracleConnection (connectionString))
{
var cmd = connection. CreateCommand ();
Cmd.commandtext = "SELECT * from Sys.dba_all_tables";
Connection. Open ();
var reader = cmd. ExecuteReader ();
while (reader. Read ())
{
Console.WriteLine (reader. GetString (0));

}

Reader. Close ();
Connection. Close ();

}

}
}
}

It looks like there's no problem, but it doesn't work.

Additional information:System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

The hint here is that Oracle Client is required.

This is not the result we want to see. In fact, in principle, we understand that it may be that this component is just a wrapper, it actually operates the database, and it needs to be implemented by Oracle client.

Using components provided by Oracle

A better recommendation is to use the managed code components provided by Oracle. Oracle.ManagedDataAccess.dll

As soon as you add the NuGet package, the code requires almost no changes and can be reused directly.

Using System;
Using Oracle.ManagedDataAccess.Client;

Namespace Consoleapplication
{
Class Program
{
static void Main (string[] args)
{
var connectionString = "User Id=system;password=password;data SOURCE=192.168.56.101:1521/ORCL";

using (var connection = new OracleConnection (connectionString))
{
var cmd = connection. CreateCommand ();
Cmd.commandtext = "SELECT * from Sys.dba_all_tables";
Connection. Open ();
var reader = cmd. ExecuteReader ();
while (reader. Read ())
{
Console.WriteLine (reader. GetString (0));

}

Reader. Close ();
Connection. Close ();

}

}
}
}

Of course, the ideal case is the connection string and the like, can be placed in the configuration file. This is very simple, it is not explained here.

Combined with Entity Framework use

The Entity Framework has been out for years and has become almost all. NET application (whether or not it is used). The latest version should be 6.1.3. At the same time, it should be noted that there will be a so-called entity Framework Core, and open source Https://github.com/aspnet/EntityFramework

Back to the point, the previous code is actually relatively primitive, so how to combine the Entity Framework for Oracle database programming?

First, install the following component: Oracle.ManagedDataAccess.EntityFramework

You can then write the following code by using code first

Using System.ComponentModel.DataAnnotations;
Using System.ComponentModel.DataAnnotations.Schema;
Using System.Data.Entity;

Namespace Consoleapplication
{
public class Oraclecontext:dbcontext
{
Public Oraclecontext (): Base ("Oracledbcontext")
{

}

Public dbset<employee> Employees {get; set;}

}

[Table ("EMPLOYEES", Schema = "SYSTEM")]
public class Employee
{
[Key ()]
[Column ("EMPLOYEEID")]
public int EmployeeID {get; set;}

[Column ("FIRSTNAME")]
public string FirstName {get; set;}
[Column ("LASTNAME")]
public string LastName {get; set;}
}
}

There's nothing surprising about the code here. The configuration file needs to have the following settings (generally when adding Oracle.ManagedDataAccess.EntityFramework this component, the configuration file will be automatically modified)

<?xml version= "1.0" encoding= "Utf-8"?>
<configuration>
<configSections>
<section name= "oracle.manageddataaccess.client" type= "OracleInternal.Common.ODPMSectionHandler, Oracle.manageddataaccess, version=4.121.2.0, culture=neutral, publickeytoken=89b483f429c47342 "/>
<section name= "EntityFramework" type= "System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, version=6.0.0.0, Culture=neutral, publickeytoken=b77a5c561934e089 "requirePermission=" false "/>
<!--for more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468--
</configSections>
<startup>
<supportedruntime version= "v4.0" sku= ". netframework,version=v4.5.2 "/>
</startup>
<oracle.manageddataaccess.client>
<version number= "*" >
<dataSources>
<datasource alias= "Oracle" Descriptor= "(description= (address= (protocol=tcp) (host=192.168.56.101) (PORT= 1521)) (Connect_data= (SERVICE_NAME=ORCL))) "/>
</dataSources>
</version>
</oracle.manageddataaccess.client>
<connectionStrings>
<add name= "Oracledbcontext" connectionstring= "user Id=system;password=password;data source=oracle" providerName= "Oracle.ManagedDataAccess.Client"/>
</connectionStrings>
<system.data>
<DbProviderFactories>
<remove invariant= "Oracle.ManagedDataAccess.Client"/>
<add name= "odp.net, Managed Driver" invariant= "Oracle.ManagedDataAccess.Client" description= "Oracle Data Provider For. NET, Managed Driver "type=" Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.manageddataaccess, version=4.121.2.0, culture=neutral, publickeytoken=89b483f429c47342 "/>
</DbProviderFactories>
</system.data>
<runtime>
<assemblybinding xmlns= "Urn:schemas-microsoft-com:asm.v1" >
<dependentAssembly>
<publisherpolicy apply= "No"/>
<assemblyidentity name= "oracle.manageddataaccess" publickeytoken= "89b483f429c47342" culture= "neutral"/>
<bindingredirect oldversion= "4.121.0.0-4.65535.65535.65535" newversion= "4.121.2.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
<entityFramework>
<defaultconnectionfactory type= "System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework" >
<parameters>
<parameter value= "v13.0"/>
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantname= "Oracle.ManagedDataAccess.Client" type= " Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, version=6.121.2.0, culture=neutral, publickeytoken=89b483f429c47342 "/>
<provider invariantname= "System.Data.SqlClient" type= "System.Data.Entity.SqlServer.SqlProviderServices, Entityframework.sqlserver "/>
</providers>
</entityFramework>
</configuration>

The table design of the background database is also very simple.

It is important to note that, after the experiment, I found that the current Entity Framework requires that the table must have a primary key, and that the primary key must be an identity column (that is, self-binding a sequence to achieve autogrow), otherwise it will error

In fact, the background will implement this function through a trigger.

Create or replace TRIGGER EMPLOYEES_TRG
Before INSERT on EMPLOYEES
For each ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING and:new. EMPLOYEEID is NULL and then
SELECT Employees_seq. Nextval into:new. EMPLOYEEID from SYS. DUAL;
END IF;
END column_sequences;
END;

Next in the front-end program is much simpler, here is a code snippet

var ctx = new Oraclecontext ();

CTx. Employees.add (New Employee () {FirstName = "Ares", LastName = "Chen"});
CTx. SaveChanges ();

var query = ctx. Employees.toarray ();
foreach (var item in query)
{
Console.WriteLine (item);
}

It is important to note that if you need to use the functions of the entity Frmaework database first or model first, you will need to install the Oracle Client, or it should be accurate to say that the ODAC component

Http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

Developing applications for Oracle databases in. NET