C # Oracle development to perform stored procedures
Author: dinya
Keywords: C #, Oracle, C / S
abstract:
During the current development process, Microsoft's .NET is favored by developers with its easy-to-use and supportive support, many projects use the Oralce database as a background database, but in the development process needs to call the database through the front program Some of the objects, this article will be a brief description of the C # Oracle database development in the form of an instance.
This article is suitable for readers:
.NET ORACEL developers, Oracle primary
System environment:
OS: Windows 2000 Professional (English)
Oracle: 8.1.7.1.0
.NET: .NET 2003
text:
We use the C / S structure for developing a C / S structure using the C / S in the C / S. A considerable part of affairs will consider dealing with the Oracle database to reduce network data traffic, improve program performance. This requires us to log on, abstract, using Oracle's stored procedures, is a nice choice. Below I will use an instance to explain the process of C # uses the Oracle stored procedure to perform the business operation:
Description of Requirement:
Enter the basic information such as the username, password, user name, user phone number and user types on the front-end interface. Calling the stored procedure for adding user information in the Oracle database to perform the function of adding new user information. Require username, password, and user types cannot be empty, the username cannot be repeated, and this example uses OldDbConnection to connect to the Oracle database.
1. Build a sequence of user tables and user IDs in the database:
Create sequence seq_user_information increment by 1start with 1NomaxValuenocyClecache 10
create table user_information (user_id number primary key, - user number user_login_name varchar2 (30) not null, - login name user_password varchar2 (50) not null, - user password user_name varchar2 (20), - a user name user_telephone varchar2 ( 20)
2, bury the stored procedure for the insert operation in Oracle:
create or replace procedure insert_user_information (p_user_login_name in varchar2, p_user_password in varchar2, p_user_name in varchar2, p_user_telephone in varchar2, p_user_type in number, p_out out number) as v_count number; begin if p_user_login_name is null or p_user_password is null then p_out: = - 1; - Username and password cannot be empty, return; end if; if p_user_type is null the p_out: = - 2; - User type cannot be empty return; end if; select count (*) Into v_count from user_information a where a. user_login_name = upper (p_user_login_name); if v_count> 0 then p_out: = - 3; - the user name already exists return; end if; insert into user_information values (seq_user_information.nextval, upper (p_user_login_name), p_user_password, p_user_name, p_user_telephone, p_user_type, sysdate, sysdate; commit; p_out: = 0; - Operation success Return; Exception when osthers kiln p_out: = - 4; - Anomalous return during insertion; END; 3, build one in .NET project Database connection:
Add a class file in the project. Name: clspublic,
// Add reference:
Using system;
Using system.data;
Using system.data.oledb;
// Connecting strings
Private string connector = "provider = msdaora.1; password = fraw; user ID = fran; data source = demo; persist security info = true";
/ / Connect Oracle Database
Public OLEDBConnection connectDb ()
{
Try
{
OLEDBCONNECTION CONN = New OLEDBCONNECTION ();
CONN.CONNECTIONSTRING = Connectora;
Cn.open ();
Return conn;
}
Catch
{
Return NULL;
}
}
4. Add the following to the following file to perform the process in Oracle:
public int Insert_User_Information (string v_user_login_name, string v_user_password, string v_user_name, string v_user_telephone, int v_user_type, string proc_name) {int i; cmdOra.Parameters.Clear (); cmdOra.CommandText = proc_name; cmdOra.CommandType = CommandType.StoredProcedure; cmdOra. Connection = new clsPublic () connectDB ();. cmdOra.Parameters.Add ( "p_user_login_name", OleDbType.VarChar); cmdOra.Parameters.Add ( "p_user_password", OleDbType.VarChar); cmdOra.Parameters.Add ( "p_user_name" , OleDbType.VarChar); cmdOra.Parameters.Add ( "p_user_telephone", OleDbType.VarChar); cmdOra.Parameters.Add ( "p_user_type", OleDbType.Integer); cmdOra.Parameters.Add ( "p_out", OleDbType.Integer) ; cmdOra.Parameters [ "p_user_login_name"] Value = v_user_login_name;.. cmdOra.Parameters [ "p_user_password"] Value = v_user_password;. cmdOra.Parameters [ "p_user_name"] Value = v_user_name;. cmdOra.Parameters [ "p_user_telephone"] Value = v_user_telephone; cmdora.parameters ["p_user_type"]. value = v_user_typ e; cmdOra.Parameters [ "p_user_login_name"] Direction = ParameterDirection.Input;.. cmdOra.Parameters [ "p_user_password"] Direction = ParameterDirection.Input;. cmdOra.Parameters [ "p_user_name"] Direction = ParameterDirection.Input; cmdOra.Parameters [ "p_user_telephone"] Direction = ParameterDirection.Input;. cmdOra.Parameters [ "p_user_type"] Direction = ParameterDirection.Input;. cmdOra.Parameters [ "p_out"] Direction = ParameterDirection.ReturnValue;. try {cmdOra.ExecuteNonQuery (); i = (int) cmdora.Parameters ["p_out"]. value;
} Catch {i = -88;} finally {if (cmdora.connection.State == connectionState.Open) {cmdora.connection.close (); cmdora.connection.dispose (); cmdora.parameters.clear (); cmdora .Dispose ();}} Return i;} 5, call execution stored procedures in the form interface
Private void button3_click_1 (Object Sender, System.EventArgs E)
{
INT i = new clspublic (). INSERT_USER_INFORMATION ("Dinya", "111", "Dinya", "13877778888", 0,
"CUX_FRANCHISER.INSERT_USER_INFORMATION");
Messagebox.show (i.tostring ());
}
In the second step of this example, define an output parameter to store the result of the execution, the Oracle stored procedure allows the output parameters to be assigned directly, where. This parameter type is set in NET: cmdora.Parameters ["p_out"]. Direction = parameterDirection.ReturnValue; set it to return value so that the parameter returns the execution result to the user after calling the Insert_User_information Execute the stored procedure.
When the stored procedure is called in the fifth-step form interface, the last parameter is "CUX_FRANCHISER.INSERT_USER_INFORMATION", which CUX_FRANCHISER is a package in which INSERT_USER_INFORMATION is in the package. (Please refer to the use section of the package in Oracle related books).
It should be pointed out that the connection to connect the Oracle database will be different because of the OLEDB manufacturers. In this example, you can use Oracle, you can use ORACLE, you can download, address:
Http://www.racle.com/technology/software/tech/windows/ole_db/index.html.
postscript:
In the development process of .NET Oracle, there are other operational techniques. It is hoped that this article can play a role of brick introduction jade, just contacting the developed readers or Oracle beginners, you can refer to this article, and raise it three. You can also find them in the author's Blog: http://blog.9cbs.net/dinya2003/
About the Author:
I have been working in the mechanical industry, and I will do a Windows program development. Now, in a manufacturing company, do Oracle ERP development, currently 9CBS Forum Oracle Development Edition.
Author Mail: Dinya20@tom.com
