Develop Oracle database-oriented applications in. NET,. netoracle

Develop Oracle database-oriented applications in. NET,. netoracle

In fact, this is not a new topic. However, many projects have encountered various problems when you access the Oracle database. The most basic problem is to install various clients on the client, if the version is different, there are still various problems.

It is not that difficult to calm down and look at it. Here I will summarize how to use the Oracle database in a simple and elegant manner in. NET applications.

First principle: do not rely on

The best case is that the program can complete data access by itself without installing the so-called Oracle Client. This is a very troublesome and painful task.

First, let's take a look at whether Oracle database access can be implemented without installing 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 ADO. NET code, just for demonstration)

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 seems that there is no problem, but an error will be reported during running.

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

The prompt here is that an Oracle Client is required.

This is not the expected result. In principle, let's understand this. It may be that this component is just a wrapper. It actually needs to be implemented through the Oracle Client to operate the database.

 

Use components provided by Oracle

A better suggestion is to use the managed code component officially provided by Oracle. Oracle. ManagedDataAccess. dll

Once the Nuget Package is added, the code can be reused without any changes.

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 situation is to store the connection string and other information in the configuration file. This is very simple. It is not described here.

 

Used in combination with Entity Framework

Entity Framework has been released for many years and has become a standard in almost all. NET Applications (whether or not it is used ). Now 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 topic, the previous code writing method is still relatively primitive. So how to combine Entity Framework for Oracle database programming?

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

Then, you can use Code first to write the following Code:

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 ;}
}
}

 

 

The code here is nothing surprising. The configuration file must have the following settings (the configuration file is automatically modified when the Oracle. ManagedDataAccess. EntityFramework component is added)

<? 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 = Taobao"/>
<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. 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>
<Defaconnecticonnectionfactory type = "System. Data. Entity. Infrastructure. LocalDbConnectionFactory, EntityFramework">
<Parameters>
<Parameter value = "vswitches"/>
</Parameters>
</Defaultonfactory>
<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 should be noted that, after the experiment, I found that the Entity Framework currently requires that the table to be operated must have a primary key, and the primary key must be an identity column (that is, bind a sequence by yourself, ). Otherwise, an error is reported.

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

Create or replace TRIGGER EMPLOYEES_TRG
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
<COLUMN_SEQUENCES>
BEGIN
If inserting and: NEW. EMPLOYEEID IS NULL THEN
SELECT EMPLOYEES_SEQ.NEXTVAL INTO: NEW. employeeid from sys. DUAL;
End if;
END COLUMN_SEQUENCES;
END;

 

Next, it will be much simpler in the front-end program. Below 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 should be noted that if you need to use the Database first or Model first function of Entity Frmaework, you still need to install the Oracle Client or, accurately, it should be an ODAC component.

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