Use OracleClient, OLEDB, ODBC, Visual Studio, and ODP. NET to connect to the Oracle database

--- The following is OracleClient

The following describes how to access the Oracle database through the ASP. NET page through System. Data. OracleClient,

In fact, it is relatively simple,

First, let's introduce the environment,

My machine is installed with the Oracle 10g R2 version,

To complete ASP. NET access to Oracle, there are still some settings for Oracle,

Let's take a look at the two files below.

Complete the machine name and IP address settings in the two files identified above,

Set as follows:

After setting, you can access the Oracle database through OracleClient,

Let's take a look at the character string settings for connecting to Oracle,

As you can see above, the connection string is basically the same as the link Access or SQL Server,

Data Source specifies the database name. The database is specified when the Oracle database is created,

Do not explain the User ID and Password,

It can be seen that scott, a pre-defined Oracle user, accessed me,

After the connection string is set, a page is written to access the Oracle database,

The basic functions of the page are described as follows,

The data in the Oracle database is loaded to the DropDownList for the first time,

In addition, you can select a user name in the DropDownList to obtain all information about the user,

The function is very simple (because it is mainly used to access the Oracle database)

Let's look at Code-Behind.

The above is the Code-Behind part. In fact, apart from some different calling methods,

Basically, you can use ADO. NET to access SQL Server,

Then we can see the effect,

Let's look at Oracle again,

I use SCOTT to access Oracle, and Oracle automatically creates an Emp table during installation,

The Demo above is used by SCOTT to access the EMP data table,

We can see the structure of the Emp table from the above,

Then let's take a look at the data in it,

Let's also look at the basic data of user WARD,

From the above picture, we can get the data from the above ASP. NET page, and we can see that the data is the same,

--- The following is odbc,

To be honest, use OracleClient, Oledb, and Odbc to access the Oracle database,

It is relatively simple. It is nothing more than a note that the database connection string is OK,

The following is a database connection string.

Then let's take a look at the Demo Code-Behind.

 

A simple Demo is to use ODBC to access the EMP data table from the Oracle database,

Then retrieve all the data in the table and bind it to a GridView. The result is OK,

The effect is

At this point, the data in Oracle is successfully obtained !!!

 

--- The following is oledb,

It seems that Microsoft has declared that it will give up its support for OracleClient since. NET 4.0,

So the following blog will introduce more methods for. NET accessing the Oracle database,

In fact, there are many ways to access the Oracle database, such as using ODBC,

Or OracleClient implementation, or Oledb implementation,

Or through Oracle Data Provider for. NET (ODP. NET) provided by Oracle,

For the last ODP. NET, you must add the components provided by Oracle,

Another interesting thing is that Oracle Developer Tools for Visual Studio provided by Oracle,

After installation, you can directly access the Oracle database in Visual Studio like SQL Server,

However, this blog post will only introduce Oledb,

From ease to difficulty, we will introduce other methods for accessing the Oracle database later,

In fact, using Oledb to access the Oracle database is very simple, just like using OracleClient in the previous blog,

Only some basic methods and classes are different, and then the connection string to the database is slightly changed,

It is also worth mentioning that when OracleClient accesses the Oracle database,

There is no need for too many data types in Oracle databases,

In OracleClient, there are some methods for getting values for Oracle Database types,

For example

When accessing Oracle in Oledb, you must control the data type conversion,

For example

Now let's take a look at the Demo,

Let's take a look at the connection string first.

This Demo achieves the same effect as the Demo used to access the Oracle database using OracleClient in the previous article,

You can also log on with SCOTT and access the EMP data table in the database to obtain the data,

From the above we can see that this Demo and the previous Demo have no changes,

Connect to Oracle using Visual Studio

 

First, let's take a look at some of my environments,

The operating system is the flagship version of Win 7,

Oracle 10g R2,

Visual Studio is Visual Studio 2008,

All of the preceding descriptions are completed by manually writing the Oracle database connection string to access the Oracle database,

This section describes three methods: OracleClient, Oledb, and ODBC,

In fact, Oracle operations are not as complicated as imagined,

Especially when Oracle provides some plug-ins under Visual Studio, it is easier to use them,

This makes your operations on Oracle in VS similar to those on SQL Server,

Actually, VS has three data providers for accessing Oracle,

One is the. NET Framework data provider for Oracle.

One is:. NET Framework data provider for OLEDB

One is: Oracle Data Provider for. NET

In the third case, you must first install ODP. NET provided by Oracle to achieve access,

This will be introduced in the following blog,

It must be mentioned that to access the Oracle database in,

You must configure the Oracle database first,

Some common configuration errors are mainly caused

D: \ oracle \ product \ 10.2.0 \ db_1 \ network \ ADMIN

Configuration of two files in this directory

The configuration of these two files is crucial. The listener. ora is the information of the listener service specified by the user,

If the file is set incorrectly, the listener may fail to start,

For simplicity, cut down the items in the image directly,

It is mainly to set the HOST,

Then configure tnsnames. ora.

This file is also very important. It directly involves whether your VS or application can access the Oracle database,

The setting description is that the HOST is used to specify the HOST name or IP address of the Oracle server,

SERVICE_NAME specifies the global database name,

Generally, after the following two steps, you can determine whether the preceding two files are correctly set,

First, after you have set the above two files, you can restart the listening service,

If the listening service can be started properly, the listener. ora configuration file is basically correct,

Then, you can use the tnsping database name to determine whether the configuration of tnsnames. ora is correct,

For example, my global database name is Oracle,

I will use tnsping Oracle to determine whether the configuration of tnsnames. ora is correct,

If a similar result is obtained, it indicates that the configuration file is correct,

Now let's look at the two methods above,

Then I create a new connection

Change the data source (first use "user Oracle's. NET Framework data provider ")

Oracle is used to specify the name of the Oracle database to be connected.

You can see

Let's take a look at a Demo to test the above settings.

Demo is simple: Put A GridView on a page,

Then, bind all the data in the EMP table in Oracle. SCOTT to display the data,

You can check the effect directly.

--------------------------------------------- Gorgeous separation line -----------------------------------------

--------------------------------------------- Gorgeous separation line -----------------------------------------

--------------------------------------------- Gorgeous separation line -----------------------------------------

The preceding section describes how to use the first data provider to access Oracle,

Next, we will introduce the. NET Framework data provider for OLEDB.

Add a connection to the Oracle database as before,

However, the data provider used this time is used for the. NET Framework data provider of OLEDB.

I still use scott users to access the Oracle database,

After the connection is successful, you can see

Let's look at a Demo to access the Oracle database through the database connection.

This Demo is no different from the previous Demo. You can access Oracle to obtain data in the EMP table,

This is not a demonstration here,

 

 

--- ODP. NET accessing the Oracle databaseTo 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, it will be much easier to develop Oracle Applications in Visual Studio in the future,

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 voidBtnAddData_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
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

In this way, ODP. NET is successfully used to access the Oracle database !!!