C # Calling Stored Procedure notes (original)

The data access layer is now using the stored procedure.
This article records the writing of a fully functional stored procedure, and a general method for calling stored procedures with returned values implemented by C.

1. Stored Procedure:
In this example, the operation table is power (p_id, p_name, p_des)
The general null values and primary keys are detected repeatedly in this stored procedure, and transactions are used for multiple operation statements.

Create procedure myp2
@ Pid int, @ pname nvarchar (15), @ pdes nvarchar (50)
AS
Declare @ return int, @ count int
-- Check whether the parameter has a null value
If (ltrim (rtrim (@ pname) = ''or ltrim (rtrim (@ pid) = '')
Begin
Select 1 -- the input parameter has a null value.
Return
End
-- Check whether there are duplicate primary keys
Select @ count = count (1) from power where p_id = @ pid
If (@ count> 0)
Begin
Select 2 -- repeated
Return
End
-- Execute transactions
Begin tran
Insert into power (p_id, p_name, p_des) values (@ pid, @ pname, @ pdes)
If (@ error <> 0) goto l_rollback
Insert into powerLog (p_id, p_des) values (@ pid, @ pdes)
If (@ error <> 0) goto l_rollback
Commit tran
Select @ return = 4 -- Success
Goto l_ OK
Rochelle rollback:
Rollback tran
Select @ return = 3 -- failed
Rochelle:
Select @ return
GO

2. Calling stored procedures with returned values in C #
This is a common method. stored procedure parameters can be placed in arrays.
/* Execute the Stored Procedure
* Parameter description:
* StoreProName: name of the stored procedure
* Parameters: stored procedure parameter, represented by an array of the OleDbParameter type
* Result: the return value of the stored procedure. If "successful" is returned, the execution is successful. If other values are returned, the operation fails.
*/
Public void ExcuteStore (string StoreProName, OleDbParameter [] parameters, out string result)
{
OleDbCommand com1 = new OleDbCommand (StoreProName, GetDBcon ());
If (com1.Connection. State = ConnectionState. Closed)
Com1.Connection. Open ();
Com1.CommandType = CommandType. StoredProcedure;
// Add parameters to com and assign values
For (int I = 0; I <parameters. Length; I ++)
{
Com1.Parameters. Add (parameters [I]);
}
String temp = "";
Try
{
Object obj = com1.ExecuteScalar ();
If (obj! = Null)
Temp = obj. ToString ();
}
Catch (Exception ee)
{
Result = "operation failed! "+ Ee. Message. ToString ();
Return;
}
Finally
{
Com1.Connection. Close ();
}
Result = "successful"; // successful by default
// If 0 is returned, the data operation is successful.
// 1 indicates that the submitted data has a null value.
// 2 indicates that the number is repeated.
// 3 indicates data operation Error
Switch (temp)
{
Case "0 ":
Result = "successful ";
// Write logs simultaneously
// DB. WirteLog (DB. opNO, DateTime. Now. ToString ("yyyyMMddHHmmss"), thisModNO );
Break;
Case "1 ":
Result = "the submitted data has a null value! ";
Break;
Case "2 ":
Result = "The number already exists! ";
Break;
Case "3 ":
Result = "data operation error! ";
Break;
}}

Example of the above method:
OleDbParameter [] param = new OleDbParameter [2];
Param [0] = new OleDbParameter ("@ name", OleDbType. VarChar, 6 );
Param [1] = new OleDbParameter ("@ pwd", OleDbType. VarChar, 40 );
Param [0]. Value = TextBox1.Text;
Param [1]. Value = TextBox2.Text;
String outstring;
ExcuteStore ("myprocedure", param, out outstring );
If (outstring = "successful ")
Binding ();
Else
Label1.Text = outstring;
I am using the OleDb driver, connected to an old database syBase. You may be familiar with SqlParameter in the same way.