Access Oracle database with ODP. net

To use ORACLE data provider for. Net (ODP. Net ),

You must first install ODP. Net or odac (Oracle Data Access Components)

(Odac contains the ODP. NET Component)

It is best to install ODT. net. In this way, Oracle Applications will be developed in Visual Studio in the future.ProgramIt will be much more convenient,

Provide one,

Http://www.oracle.com/technology/global/cn/software/tech/windows/odpnet/index.html

If you have not installed the tool, please refer to some of my previous blog posts, which have a good introduction,

In this blog, we will complete a demo. This demo extracts data from the specified data table from SQL Server,

Insert the retrieved data to the specified table in the Oracle database one by one,

I will not talk about the access to the SQL Server database,

Access to Oracle is mainly implemented through ODP. net,

Previous blog<Use the reflector tool to view the class information provided by ODP. Net>

Describes some classes and namespaces in ODP. net,

In this blog post, these classes are used to complete the demo. Of course, the classes involved will be very simple and basic classes,

That is, connection, command, dataadaptor,

If you want to use other classes under ODP. net,

You can use reflector to reflect the content in Oracle. dataaccess. dll,

First, if you want to use ODP. Net in your application to access the Oracle database,

You must add a reference to the current application,

This oracle. dataaccess can be referenced only after you have installed ORACLE data provider for. net,

After adding this reference, you can use the namespace and its sub-content of Oracle. dataaccess,

Let's take a look at the page design,

The Data Binding operation on sqldatasource is performed in the following order:

Scott. Oracle is a database connection established with Oracle through ODP. net,

Then let's take a look at the database connection string settings in Web. config.

Which includes an Oracle database connection string (oraclescott)

It also includes an SQL Server database connection string (sqlservercon)

Then let's look at code-behind.

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. Web;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Data. sqlclient;
Using system. Data;
Using Oracle. dataaccess. client;
Using Oracle. dataaccess. types;
Using Oracle. dataaccess;
Using system. Web. configuration;

Namespace webform
{
Public partial class demo _ 39: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{

}

Protected void Btnadddata_click (Object sender, eventargs E)
{
// Obtain the string used to connect to the sqlserver database in Web. config.
String sqlserverconstr =
Webconfigurationmanager. connectionstrings ["sqlservercon"].
Connectionstring;
// Obtain the string connecting to the Oracle database in Web. config.
String oracleconstr =
Webconfigurationmanager. connectionstrings ["oraclescott"].
Connectionstring;

// Retrieve the data in SQL Server and store it in dataset.
Dataset DS = new dataset ();

Using (sqlconnection sqlcon = new sqlconnection (sqlserverconstr ))
{
Using (sqlcommand sqlcom = sqlcon. createcommand ())
{
String sqlstr =
"Select employee number, name, gender, current salary from Zhang Limin Research Office ";
Sqlcom. commandtype = commandtype. text;
Sqlcom. commandtext = sqlstr;
Using (sqldataadapter sqlda = new sqldataadapter (sqlcom ))
{
// Store all the data in the dataset Dataset
Sqlda. Fill (DS );
}
}
}
Using (oracleconnection oraclecon = new oracleconnection (oracleconstr ))
{
Oraclecon. open ();
Using (oraclecommand oraclecom = oraclecon. createcommand ())
{
Oraclecom. commandtype = commandtype. text;
For (INT I = 0; I <Ds. Tables [0]. Rows. Count; I ++)
{
// Retrieve data from dataset one by one and store the data to the Oracle data table "employee"
String sqlstr = string. Format (
"Insert into employee (empid, empname, empsex, empsalary)" +
"Values ({0}, '{1}', '{2}', {3 })",
DS. Tables [0]. Rows [I] [0],
DS. Tables [0]. Rows [I] [1],
DS. Tables [0]. Rows [I] [2],
DS. Tables [0]. Rows [I] [3]);
Oraclecom. commandtext = sqlstr;

Oraclecom. executenonquery ();
}
}
}

// Data can only be retrieved once from the database
Btnadddata. Enabled = false;
}
}
}

The following shows the effect,

The following figure shows that data has not been copied from SQL Server to the Oracle database,

The original data in Oracle,

After copying data, the result is

This allows you to access the Oracle database using ODP. Net !!!