Odp.net access to the Oracle database

Odp.net access to the Oracle database

To use Oracle Data Provider for. NET (odp.net),

You must first install odp.net or ODAC (Oracle Data Access components)

(ODAC contains odp.net this component)

It's a good idea to install odt.net, so it's a lot easier to develop Oracle applications in Visual Studio later,

Let's just offer one,

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

If you have not installed a friend, please refer to the author in front of some of the posts, which have a better introduction,

This blog post, will be completed a demo, this demo is to remove the data from the specified data table from SQL Server,

The extracted data is then inserted into the specified table in the Oracle database,

In which, access to the SQL Server database I will not say,

Access to Oracle is mainly achieved through odp.net,

A previous post << use the Reflector tool to view information such as classes provided by Odp.net >>

Introduces some classes and namespaces in Odp.net,

In this article blog post is to use these classes to complete the Demo, of course, the class involved will be very simple, the most basic class,

Which is Connection,command, dataadaptor these,

If you need to use some of the other classes under Odp.net,

you are you can use Reflector to reflect the contents of a Oracle.DataAccess.dll,

The first thing to do is to use odp.net in your application to access the Oracle database.

You must add a reference to the current application,

This oracle.dataaccess must be referenced after you have installed Oracle Data Provider for. NET.

Once this reference has been added, it is possible to use the Oracle.dataaccess namespace and its child content since then.

Let's look at the page design first,

The data binding operations on SqlDataSource are performed in the following order

where SCOTT. Oracle is a database connection I've established with Oracle through Odp.net,

Then just look at the settings for the database connection string in Web. config.

Which, including an Oracle database connection string (Oraclescott)

Also includes a SQL Server database connection string (Sqlservercon)

And then I came to see 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)
{
//gets the string   of the connection SQL Server database in Web. config;
            string sqlserverconstr = 
                webconfigurationmanager.connectionstrings["Sqlservercon"].&NBSP;
                 connectionstring; 
              Get a string that connects to an Oracle database in Web. config
String oracleconstr =
webconfigurationmanager.connectionstrings["Oraclescott"].
ConnectionString;

//First remove data from SQL Server and store it in the dataset
DataSet ds = new DataSet ();

using (SqlConnectionSqlcon = new SqlConnection (SQLSERVERCONSTR))
{
using (SqlCommandsqlcom = Sqlcon.createcommand ())
{
String sqlstr =
"Select employee number, name, gender, current salary from Zhang Limin Lab";
Sqlcom.commandtype = CommandType.Text;
Sqlcom.commandtext = Sqlstr;
using (SqlDataAdapter SqlDA= New SqlDataAdapter (sqlcom))
{
//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 and store to 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 taken once from the database
btnadddata.enabled = false;
}
}
}

Here's how it works,

The following is before you copy data from SQL Server to the Oracle database,

Oracle's original data,

After the copy data is executed, the result is

This successfully achieved the use of odp.net access to the Oracle database!!!

Odp.net access to the Oracle database