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 application

var repo = new RepositoryContainer();

// create the request and populate the parameters

var request = new GetCustomersByCity_Request
{
    CityID = 123
};

// call the stored proc called GetCustomersByCity

var response = _repo.AppDb.Sales.StoredProcs.GetCustomersByCity(request);

// access the results from the Result property

foreach (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 application

var repo = new RepositoryContainer();

// create the request and populate the parameters

var request = new GetCustomersByCity_Request
{
    CityID = 123    
};

// call the stored proc called GetCustomersByCity

var response = _repo.AppDb.Sales.StoredProcs.GetCustomersByCity(request);

// read the TotalCustomersSearched Out parameter

var total = response.TotalCustomersSearched;

// access the results from the Result property

foreach (var customer in response.Result)
{
    //do something with each customer found

}