Calling Oracle9i Store Experience Summary in .NET
The Oracle9i stored procedure in .NET can be used to use multiple data providers, such as OralceClient, OLEDB. This article will use OralceClient as an example. .NET language with C #.
First, call a stored procedure with input and output parameters
First, create a stored procedure in Oralce as follows:
Create Or Replace Procedure GetRecords (Name_Out out Varchar2, Age_in in varchar2) AS
Begin
Select Name INTO Name_out from test where agn = age_in
END;
Then, as follows in the C # project:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String queryString = "getRecords";
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("Name_out", Oracletype.varchar, 20);
CMD.Parameters ["name_out"]. Direction = parameterdirection.output;
Cmd.Parameters.Add ("age_in", 21);
Try
{
Cn.open ();
cmd.executenonquery ();
Console.Writeline ("Name IS: {0}", cmd.parameters ["name_out"]. Value.toString ());
Cn.close ();
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
}
summary:
The above is a very common call method, but when I am doing this sample program, it is not so smooth. Here to point out an unreasonable thing about OracleClient, it is the same name for the parameter name, must and the parameters in the stored procedure definition, otherwise the error will be reported. For example, if "name_out" in the code is changed to another name, it will report an exception. But I tried to use OLEDB to replace OralceClient, but there is no problem. I don't know if I will improve this in the new version of the data provider? Second, call the stored procedure that does not return data
First, create a stored procedure in Oralce as follows:
Create or Replace Procedure InsertRecord (userid in varcha2,
UserName In Varchar2, UseRage Insert INTO TEST VALUES (UserID, UserName, Userage); END; then, as follows in the C # project:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String querystring = "insertrecord";
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("UserID", "007");
Cmd.Parameters.Add ("UserName", "Dell");
Cmd.Parameters.Add ("UseRage", "40");
Try
{
Cn.open ();
cmd.executenonquery ();
Console.WriteLine ("Record Insert!");
Cn.close ();
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
}
summary:
The stored procedures that do not return data generally have Delete, INSERT, UPDATE, and so on. Although their SQL statements are different, they are the same. As long as you pass the correct parameters, you generally don't have any problems. But should still be noted that when using OralceClient as the data provider, the parameter name must be uniform and the stored procedure definition! As for the order of parameter added, it doesn't matter because there is a parameter name as a difference.
Third, Identity and Sequern
In the SQL Server, define a list of increment columns, but I can't find the settings in Oracle. However, after checking the information, I know that there is an object called Sequence in Oracle, generating a unique serial number, similar to Identity in SQL Server. So, I did the following experiment:
First, create a Sequence object called Test_SEQ in Oracle, the SQL statement is as follows:
Create Sequence Test_SEQ
Minvalue 100
MaxValue 999
START with 102
INCREMENT BY 1
Nocache;
The syntax should be more easy to understand, the minimum maximum value is used by minValue, maxvalue, respectively, the initial value is 102 (this number is dynamically changed, and I am created is 100, and after inserting 2 data, it will automatically increase 2), INCREMENT is of course a step size. In PL / SQL, you can use Test_SEQ.NEXTVAL to access the next serial number with Test_seq.currval to access the current serial number. Defined after Sequence, next is to create a stored procedure insertRecordwithsequence:
- This time I modified the definition of the Test table, and the previous examples were different. Where usersid is PK.
Create or Replace Procedure InsertRecordwithsequence (Userid Out Number,
Username in varchar2,
Userage in number
IS
Begin
INSERT INTO TEST (ID, Name, Age) - Insert a record, PK value gets from SEQUECE
VALUES (Test_Seq.NextVal, UserName, Userage);
/ * Return to the PK value. Pay attention to the usage of the DUAL table * /
SELECT TEST_SEQ.CURRVAL INTO USERID from DUAL
End insertRecordwithsequence;
Next, it is called in C #:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String queryString = "insertrecordwithsequence";
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("UserID", ORACLETYPE.NUMBER);
CMD.Parameters ["userid"]. Direction = parameterDirection.output;
Cmd.Parameters.Add ("UserName", "Jack");
Cmd.Parameters.Add ("Userage", 40);
Try
{
Cn.open ();
INT rowaffected = cmd.executenonquery ();
Console.writeline ("{0} is inserted.", Rowaffected;
Console.WriteLine ("Inserted the ID: {0}", cmd.parameters ["userid"]. Value.toString ());
Cn.close ();
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
summary:
Using the Sequence object can easily create a unique sequence, the call during the stored procedure is also very convenient, as long as sequence_name.nextval, and Sequence.Currval can get the next and the current sequence value. It is a DUAL table worth noting.
Fourth, read the return result set using DataReader
In order to return the stored procedure to the result set, you must define a cursor variable as an output parameter. This is very different from SQL Server! And also use the concept of "package" in Oracle, it seems a bit cumbersome, but it will feel very convenient after familiarity.
There are a lot of content to be referred to in the concept of "package". First, I created a package called TestPackage, the header is so defined:
Create Or Replace Package TestPackage IS
Type mycursor is ref curd; - Define a cursor variable
Procedure getRecords; - definition process, use cursor variables as return parameters
End TestPackage;
The bodies are as defined:
Create Or Replace Package Body Testpackage IS
/ * Process body * /
Procedure getRecords (RET_CURSOR OUT MyCURSOR) AS
Begin
Open RET_CURSOR for SELECT *.
End getRecords;
End TestPackage;
I have already have all the best, let us call the front desk:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String queryString = "testpackage.getRecords"; // pay attention to calling method
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
cmd.parameters.add ("Ret_Cursor", ORACLETYPE.CURSOR); // Note The type here
CMD.Parameters ["RET_CURSOR"]. Direction = parameterDirection.output;
Try
{
Cn.open ();
OracleDataReader DR = cmd.executeReader ();
INT i = 1;
While (Dr.Read ())
{
Console.writeline ("RECORD {0}:", i );
Console.writeline ("ID: {0} name: {1} age: {2}",
Dr.Getoraclenumber (0),
Dr.GETORACLESTRING (1),
Dr.getoraclenumber (2));
Console.writeLine ();
}
Dr.close (); // To remember the CN.Close () in time, you cannot close the connection until the // DataReader object is not closed.
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
}
Please see the results:
Record 1:
ID: 100 Name: Tony Age: 23
Record 2:
ID: 101 Name: Jack Age: 40
------------------ End -------------------
summary:
The package is an Oracle's unique concept, and there is no match in SQL Server. In my opinion, the package is a bit like VC , the header is .h file, the package is .cpp file. The header is only responsible for definition, and the incubation is responsible for implementation. If the package returns a plurality of cursors, DataReader accesss these cursors in order to add their order to the parameter collection, rather than accessing them in the order in which they appear in the process. You can use the DataRead's nextResult () method to advance to the next cursor.
5. Fill DataSet with the return result
ADO.NET discards the concept of Recordset in ADO, and uses new DataSet to replace. DataSet provides a more powerful feature! With the front foundation, it is also very simple to return to DataSet from Oralce from Oralce. The main thinking is to use DataAdapter to receive the returned cursor, and then populate the data set with DataAdapter's Fill () method. In the following example, I still use the TestPackage package built in the previous example.
Below is the main code for the front desk:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String queryString = "TestPackage.getRecords";
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("Ret_Cursor", ORACLETYPE.CURSOR);
CMD.Parameters ["RET_CURSOR"]. Direction = parameterDirection.output;
Try
{
Cn.open ();
OracleDataAdapter Da = New OracleDataAdapter (CMD);
DataSet DS = New Dataset ();
Da.fill (DS, "TestTable");
Cn.close ();
For (int I = 0; i <= ds.tables ["testtable"]. rows.count-1; i ) {
String id = ds.tables ["testtable"]. rows [i] ["id"]. TOSTRING ();
String name = ds.tables ["testtable"]. Rows [i] ["name"]. TOSTRING ();
String Age = ds.tables ["testtable"]. Rows [i] ["age"]. TOSTRING ();
Console.writeline ("Record {0}:", i 1);
Console.writeLine ("ID: {0} / tname: {1} / TAGE: {2} / n", ID, name, age
}
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
}
summary:
The result after the program calls and the result just used by DataReader. Here only how to use ADO.NET to call the Oracle stored procedure, and how to fill it into the data set. As for how to manipulate DataSet, it is not the discussion scope of this article. Interested readers can refer to MSDN and related books.
6. Update the database with DataAdapter
Usually retrieve the DataSet with DataAdapter, you will be modified to DataSet, which will update the database (if you just get data, Microsoft recommends using DataReader instead of DataSet). However, updating the database by storing procedure is not that simple, and cannot be updated simply by DataAdapter's Update () method. You must add InsertCommand, DeleteCommand, UpdateCommand to DataAdapter, because the stored procedure is unaware of the details of these operations, must be given.
In order to achieve this goal, I perfect the previous TestPackage package, the head head is as follows:
Create Or Replace Package TestPackage IS
TYPE mycursor is ref curd;
Procedure UpdateRecords (ID_IN in Number, Newage In Number);
Procedure SelectRecords (RET_CURSOR OUT MyCURSOR);
Procedure deleteRecords (id_in in number);
Procedure InsertRecords (name_in in varcha2, age_in in number);
End TestPackage;
The body is as follows:
create or replace package body TestPackage is procedure UpdateRecords (id_in in number, newName in varchar2, newAge in number) as begin update test set age = newAge, name = newName where id = id_in; end UpdateRecords; procedure SelectRecords (ret_cursor out mycursor) as begin open ret_cursor for select * from test; end SelectRecords; procedure DeleteRecords (id_in in number) as begin delete from test where id = id_in; end DeleteRecords; procedure InsertRecords (name_in in varchar2, age_in in number) as begin insert into test values ( Test_seq.nextval, name_in, age_in; - Test_seq is a built-established sequence object, please refer to the previous example
End INSERTRECORDS; End TestPackage;
The front desk call code is as follows, it is a bit cumbersome, please read patiently:
String connectionString = "data source = yxzhang; user id = yxzhang; password = yxzhang";
String queryString = "TestPackage.SelectRecords";
OracleConnection CN = New OracleConnection (Connectionstring);
OracleCommand CMD = New OracleCommand (QueryString, CN);
cmd.commandtype = commandtype.storedProcedure;
Cmd.Parameters.Add ("Ret_Cursor", ORACLETYPE.CURSOR);
CMD.Parameters ["RET_CURSOR"]. Direction = parameterDirection.output;
Try
{
Cn.open ();
OracleDataAdapter Da = New OracleDataAdapter (CMD);
DataSet DS = New Dataset ();
Da.fill (DS, "TestTable");
Cn.close ();
INT count = ds.tables ["testtable"]. rows.count;
/ * Print original record * /
Console.writeline ("Old Records IS:");
For (INT i = 0; i <= count - 1; i )
{
String id = ds.tables ["testtable"]. rows [i] ["id"]. TOSTRING ();
String name = ds.tables ["testtable"]. Rows [i] ["name"]. TOSTRING (); string age = ds.tables ["testtable"]. Rows [i] ["age"]. TOSTRING );
Console.writeline ("Record {0}:", i 1);
Console.writeLine ("ID: {0} / tname: {1} / TAGE: {2} / n", ID, name, age
}
Da.selectCommand = cmd; // Specify SelectCommand for DataAPter
OracleCommand Updatecmd = New OracleCommand ("TestPackage.UpdateRecords", CN);
Updatecmd.commandtype = commandtype.storedProcedure;
Updatecmd.Parameters.Add ("ID_IN", ORACLETYPE.NUMBER, 3, "ID");
Updatecmd.Parameters.add ("NewName", ORACletype.varchar, 20, "name");
Updatecmd.Parameters.Add ("Newage", ORACLETY.NUMBER, 3, "AGE");
Da.UpdateCommand = updatecmd; / / Specify UpdateCommand for DataAdapter
OracleCommand deletecmd = New OracleCommand ("TestPackage.DeleteRecords", CN);
Deletecmd.commandtype = commandtype.storedprocedure;
Deletecmd.Parameters.Add ("ID_IN", ORACLETYPE.NUMBER, 3, "ID");
Da.deleteCommand = deletecmd; / / DeleteCommand for DataAPter
OracleCommand INSERTCMD = New OracleCommand ("TestPackage.InsertRecords", CN);
Insertcmd.commandtype = commandtype.storedProcedure;
INSERTCMD.Parameters.Add ("Name_IN", ORACLETYPE.VARCHAR, 20, "Name");
INSERTCMD.Parameters.Add ("age_in", oracletype.number, 3, "age");
Da.insertCommand = INSERTCMD; / / Specify InsertCommand for DataAdapter
DataTable newtable = ds.tables ["testtable"];
/ * Modify the first record * /
NEWTABLE.ROWS [0] ["age"] = 22;
NEWTABLE.ROWS [0] ["name"] = "john";
/ * Delete a record * /
NewTable.Rows [2] .delete ();
/ * Insert a record * /
DataRow newrow = newtable.newrow (); newrow ["name"] = "bob";
NEWROW ["age"] = 99;
NewTable.Rows.Add (newrow);
Cn.open ();
Da.Update (newTable); // update the change to the database
NewTable.clear (); // Empty DataTable
Da.fill (newTable); // Get changes after changes
Cn.close ();
/ * Print new record * /
Console.writeline ("New Records IS:");
For (int i = 0; i <= newtable.rows.count - 1; i )
{
String id = newtable.rows [i] ["id"]. TOSTRING ();
String name = newtable.rows [i] ["name"]. TOSTRING ();
String agn = newtable.rows [i] ["age"]. TOSTRING ();
Console.writeline ("Record {0}:", i 1);
Console.writeLine ("ID: {0} / tname: {1} / TAGE: {2} / n", ID, name, age
}
}
Catch (OracleException EX)
{
Console.writeLine ("Exception Occurred!");
Console.writeline ("The Exception Message IS: {0}", EXMESSAGE.TOSTRING ());
}
Finally
{
Console.writeline ("---------------------------------");
}
The results of the operation are as follows:
Old Records IS:
Record 1:
ID: 100 Name: Tony Age: 23
Record 2:
ID: 101 Name: Jack Age: 34
Record 3:
ID: 103 Name: Newadd Age: 100
New Records IS:
Record 1:
ID: 100 Name: John Age: 22
Record 2:
ID: 101 Name: Jack Age: 34
Record 3:
ID: 104 Name: Bob Age: 99
------------------ End -------------------
summary:
Using the Update () method Updating the database is very useful, but only for a single table, the case of the table connection, or directly using OracleCommand's ExcutenonQuery () method to perform updates.
Seven, this article summarizes:
In .NET to call the stored procedures, functions, packages, etc. in Oralce9i in .NET, you can improve efficiency, you can also complete some complex business logic. However, call the Oracle stored procedure and the SQL Server has a great difference, and the most important thinking is: In the process of Oracle, all return values must appear in the form of output parameters. In Oracle, you have to return a result set, you must use the REF CURSOR as a return parameter, so you should use the package. Packages are similar to classes in C , encapsulates some global variables, constants, and functions, which can have their own private variables in the function. By returning a cursor to the application, the application can receive it with DataReader or DataAdapter, and do some processing. You can also update the database with DataAdapter's Update () method, of course it needs some operations you update the database (for its definition process). Since the author is written while learning, the above example does not help some, please advise!
Become the Lunar New Year, August 29, Zhang Yuxiang
