Stored Procedures

What are Stored Procedures?#

This feature in The Sharp Factory Framework allows the developer to call SQL Stored Procedures.

These stored procedures are bound to a particular SQL Schema.

The Sharp Factory App generates a method per SQL Stored Procedure added to the model.

The naming convention is RepositoryContainer.{Model Name}.{SQL Schema}.StoredProcs.{Stored Procedure Name}

The Request#

The parameters are wrapped in a Request class.

The naming convention is {Stored Procedure Name}_Request.

Parameters#

Stored procedures can have Input parameters and, Input-Output parameters.

• Input: These parameters are sent to the stored procedure.

• Input-Output: These parameters can be modified inside the store procedure and the value will be sent back to the caller.

  These parameters can be used as Input, Output or Input-Ouput.

Stored Proc Result Class#

All stored procedures have a return value of type int. This is a characteristic from SQL Server.

A stored procedure can in addition return a result set like rows from a table.

The Sharp Factory App generates a generic class called StoredProcResult.

This class has two properties:

1. ReturnValue: integer that represents the return value that all stored procedures have in SQL Server.

2. Result: List<T> where T is the result set product of a select statement within the stored procedure.

Result Property#

When the stored procedure returns a result set from a select statement an entity will be generated to populate the Result property of the StoredProcResult.

The naming convention is {Stored Procedure Name}_Result.

This class represents T in the Result property of the StoredProcResult.

Stored Procedure Results#

The code generated by The Sharp Factory App will adapt to several scenarios possible:

1. No result set: when the stored procedure does not return a result set from a select statement.

   The return type for the method generated is an integer that represents the ReturnValue(all stored procedures return an integer regardless of whether they also return a result set from a select statement).

2. Returns a result set: when the stored procedure does return a result set from a select statement.

   The return type for the method generated is an instance of StoredProcResult<T> where T is {Stored Procedure Name}_Result.

Calling Stored Procedures#

The following C# code samples show how to consume Stored Procedures in different scenarios.

With Result Set#

// declare an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create the request and populate the parametersvar request = new GetCustomersByCity_Request{    CityID = 123};// call the stored proc called GetCustomersByCityvar response = _repo.AppDb.Sales.StoredProcs.GetCustomersByCity(request);// access the results from the Result propertyforeach (var customer in response.Result){    //do something with each customer found}

Using Out Parameter#

// declare an instance of the Repository Container// remember this can be a singleton if you// declare it early on and reuse it for the// lifetime of your applicationvar repo = new RepositoryContainer();// create the request and populate the parametersvar request = new GetCustomersByCity_Request{    CityID = 123    };// call the stored proc called GetCustomersByCityvar response = _repo.AppDb.Sales.StoredProcs.GetCustomersByCity(request);// read the TotalCustomersSearched Out parametervar total = response.TotalCustomersSearched;// access the results from the Result propertyforeach (var customer in response.Result){    //do something with each customer found}