Use C # to connect to the ORACLE database

1. Use the OracleClient component to connect to Oracle

The System. Data. OracleClient. dll component (ADO. Net Component) in the. Net Framework provides great convenience for connecting to and using Oracle databases.

1. Use the client network service name to connect to Oracle

Based on the layered requirements of business logic and database entities, it is generally required to connect to and use the Oracle database on a client machine different from the Oracle Database Host. In this case, either access the database through the client of the C/S application, or remotely connect to the Oracle database server on the WEB server in the B/S application.

To remotely connect to Oracle through the client network service name, you must install the Oracle client tool on the client machine (you do not need to select the "Administrator" mode for the installation type, but you only need to install the tool at runtime, provides basic network service configuration tools for applications ).

The statement for establishing a connection is relatively simple. The key point is data source settings. The data source here does not refer to the database name in the SqlServer connection string, but the client network service name (for more information, see the previous article on Client installation ). For Oracle Installation in this article, data source corresponds to E: \ Oracle_Client \ oracle \ ora92 \ network \ admin \ tnsnames. the Network Service name in the ora configuration file (for the convenience of the test, the database and the independent client tool are installed in different directories of the same machine ). During the specific implementation of the connection, the corresponding items will be searched in the customer's network service configuration file based on the value of data source to obtain connection information such as the database server host address, port, and global database name.

The main code for establishing a connection is as follows:

......

Using System. Data. OracleClient;

......

// The "remotedb" corresponds to the client network service name configured in "Oracle client installation and remote connection configuration"

OracleConnection conn =

New OracleConnection ("data source = remotedb; User Id = scott; Password = scott ;");
Conn. Open ();

......

2. connect to and use Oracle locally

Local Oracle is used to connect to and use the Oracle database on the host where the Oracle database is installed. This method is not feasible from the perspective of security and load balancing. This is only used as an experiment.

The code for the local connection is actually no different from the remote connection, except that its data source points to the customer's network service name on the server. For database installation in this article, it points to E: \ Oracle_Server \ oracle \ ora92 \ network \ admin \ tnsnames. the name of the network service defined in the ora file (for more information, see "Oracle client installation and remote connection configuration ).

For this local connection method, there cannot be independently installed client tools on the server side, otherwise data source will only match the tnsnames of the independent client. ora file. Even if the corresponding network service name cannot be found, the service name on the server is no longer matched. I do not know whether it is a design error of the. Net component or a deliberate attempt to encourage remote connection and use of Oracle.

2. Use the OleDB component to connect to and access the Oracle database

The OleDB component connects to and accesses the Oracle database through the Oracle OleDB Driver (OraOLEDB. dll). The premise of using the OleDB driver is to install the runtime environment on the client. You can install required files, including OraOLEDB. dll, on a custom client without installing client tools, such as configuring customer network services. In this case, the connection string of OleDB and the Code for accessing the database are as follows:

......

Using System. Data. OleDb;

......

// DataThe Source value is directly assigned to content similar to the network service name defined in the tnsnames. ora file, and the client is no longer needed.

// Configuration file tnsnames. ora (OracleClient can also handle this)

OleDbConnection conn =
New OleDbConnection ("Provider = OraOLEDB. Oracle.1; Server = localhost;

DataSource = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = localhost) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = mydb. bawei )));

User ID = scott; Password = scott ;");
Conn. Open ();
OleDbCommand comm = new OleDbCommand ("select * from scott. emp", conn );
OleDbDataReader dr = comm. ExecuteReader ();

Console. WriteLine ("Name Position ");
While (dr. Read ())
{
Console. WriteLine (dr. GetString (1) +" "+ Dr. GetString (2 ));

}
Console. ReadLine ();
Dr. Close ();
Conn. Close ();

......

You can use the OleDB component to obtain higher efficiency and performance than OracleClient, because OleDB is a lower-layer component than ADO. NET, and ADO. NET also needs to obtain data through OleDB.

3. A simple login verification program using the Oracle Stored Procedure

1. Create a user table

As an experiment table, the user table admin only contains the username and password fields. The SQL statement for creating a table is as follows:

Create table scott. ADMIN ("NAME" VARCHAR2 (20) not null,

"PASSWORD" VARCHAR2 (20) not null)TABLESPACE "EXAMPLE"

2. Create a stored procedure to verify User Logon

CREATE OR REPLACEPROCEDURE "SCOTT". "P_LOGIN" (v_Name varchar2,

V_Password varchar2, B _Passed out char)
N_Count number;
Begin
Select count (*) into n_Count from admin where name = v_Name and password = v_Password;
If n_Count> 0 then
B _Passed: = '1 ';
Else
B _Passed: = '0 ';
End if;
End;
3. Use PL/SQL in SQL tools such as SQL Plus to test whether the stored procedure is available:

Set serveroutput on;
Declare
V_Name varchar2 (20 );
V_password varchar (20 );
B _Passed char (1 );
Begin
V_Name: = 'mxh ';
V_Password: = 'mxh ';
P_Login (v_Name, v_Password, B _Passed );
If B _Passed = '1' then
DBMS_OUTPUT.PUT_LINE ('success ');
End if;
End;

4. Create a logon window. Experiment C # Call the stored procedure:

(1) compile the data logging class for accessing and operating the database:

// DataAccess. cs

Using System;
Using System. Data;
Using System. Data. OracleClient;

Namespace OraLoginProcedure
{
Public class DataAccess
{
Private string connStr = "data source = yourdb; User Id = scott; Password = scott ;";
Private OracleConnection conn = null;

Public DataAccess ()
{
}

Public DataAccess (string strConnection)
{
This. connStr = strConnection;
}

Public OracleConnection getConnection ()
{
Try
{

If (conn = null)
Conn = new OracleConnection (connStr );
If (conn. State = ConnectionState. Open)
Conn. Open ();
Return conn;
}
Catch (OracleException e)
{
Throw e;
}
}

Public void closeConnection ()
{
If (conn. State = ConnectionState. Open)
Conn. Close ();
}

// Execute the Stored Procedure

Public void RunProcedure (string storedProcName, OracleParameter [] parameters)
{
OracleCommand cmd = new OracleCommand (storedProcName, getConnection ());
Cmd. CommandType = CommandType. StoredProcedure;
Foreach (OracleParameter parameter in parameters)
{
Cmd. Parameters. Add (parameter );
}
Cmd. ExecuteNonQuery (); // execute the Stored Procedure
CloseConnection ();
}

// Perform logon verification (in actual applications, the code for executing the business logic should be separated from the basic code for data operations

// Implement clear software hierarchy and enhance code reusability)

Public bool Login (string username, string password)
{
OracleParameter [] parameters = {
New OracleParameter ("v_Name", OracleType. VarChar, 20 ),
New OracleParameter ("v_Password", OracleType. VarChar, 20 ),
New OracleParameter ("B _Passed", OracleType. Char, 1)
};
Parameters [0]. Value = username;
Parameters [1]. Value = password;
Parameters [0]. Direction = ParameterDirection. Input;
Parameters [1]. Direction = ParameterDirection. Input;
Parameters [2]. Direction = ParameterDirection. Output;
Try
{
RunProcedure ("P_LOGIN", parameters );
If (parameters [2]. Value. ToString () = "1 ")
Return true;
Else
Return false;
}
Catch (Exception e)
{
Throw e;
}
}
}
}

(2) Compile the logon verification interface program:

// Form1.cs

......

Using System. Data;

Namespace OraLoginProcedure
{
Public class Login: System. Windows. Forms. Form
{
Private System. Windows. Forms. Button button1; // The logon Button.

Private System. Windows. Forms. TextBox textBox1; // user name input box

Private System. Windows. Forms. TextBox textBox2; // password input box
Private System. Windows. Forms. Label label1;
Private System. Windows. Forms. Label label2;

......

Public Login ()
{
InitializeComponent ();

}

......

///

/// Main entry point of the application.
///
[STAThread]
Static void Main ()
{
Application. Run (new Login ());
}

Private void button#click (object sender, System. EventArgs e)
{
Try
{
String username = textBox1.Text. Trim ();
String password = textBox2.Text. Trim ();
This. Close (); // After the interface is closed, the control input is not displayed.
DataAccess da = new DataAccess ();
If (da. Login (username, password ))
MessageBox. Show ("Hello" + username );
Else
MessageBox. Show ("Login failed ");
Application. Exit ();
}
Catch (Exception ex)
{
This. Close ();
MessageBox. Show (ex. ToString ());
Application. Exit ();
}
}
}
}