Database Component hxj. Data (8) (Stored Procedure)

The execution of SQL statements described in the previous section describes the execution of stored procedures.

The fromproc method is used to execute the stored procedure.

The execution of the parameter-free stored procedure is as follows:

DbSession.Default.FromProc("Ten Most Expensive Products").ToDataTable();

"Ten Most Expensive Products" is the name of the stored procedure.

 

Execute the stored procedure with parameters:

DbSession.Default.FromProc("Sales by Year")
                .AddInParameter("Beginning_Date", DbType.DateTime, "1995-01-01")
                .AddInParameter("Ending_Date", DbType.DateTime, "1996-12-01")
                .ToDataTable();

This stored procedure in the database

create procedure "Sales by Year" 
    @Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

GO

There are two parameters: beginning_date and ending_date. The execution of stored procedures is similar to that of SQL statements. However, when stored procedures have more parameters, there will be input and output parameters. Add Input and Output Parameters Using addinputoutputparameter

The addoutparameter method adds the output parameter addreturnvalueparameter to the return parameter value. The example is as follows:

ProcSection proc = DbSession.Default.FromProc("testoutstore")     .AddInParameter("in1", System.Data.DbType.Int32, 1)     .AddOutParameter("out1", System.Data.DbType.Int32)     .AddOutParameter("out2", System.Data.DbType.String,100);proc.ExecuteNonQuery();Dictionary<string, object> returnValue = proc.GetReturnValues();foreach (KeyValuePair<string, object> kv in returnValue){    Response.Write("ParameterName:" + kv.Key + "    ;ReturnValue:" + Convert.ToString(kv.Value));    Response.Write("<br />");}

The getreturnvalues () method returns the returned value. The execution of stored procedures is also very simple. The next section describes other simple auxiliary methods of dbsession.