Generated Stored Procedure
Beta! New Middle Tier references to existing stored procedures in your MS SQL Server database generated in the
Class Library Project. The generated business layer contains calls to the respective stored procedure.
One of each of the following objects listed below is generated per stored procedure in your database.
| Generated Class (.cs) |
Quick Description (Stored Procedure Folder) |
|
1. Stored Procedure Business Layer
|
A class containing a reference to the respective stored procedure. Calling this business layer class will run the stored procedure. |
Accessing Code From the Middle Tier
The generated Stored Procedure references can only be accessed by the business layer (middle tier). This is done by design so that we don't accidentally
call any of this code from the presentation layer, or any client that wants to access the database.
Note 1: The Stored Procedure business layer classes are generated in it's own folder (not the BusinessLayer folder), the StoredProcedure folder.
Note 2: The generated code is in addition to the SQL Scripts as shown
here.
Note 3: No generated code for the Presentation Layer (UI).
Run The Stored Procedure
Runs a specific stored procedure. The name of the stored procedure being run is CustOrderHist which returns a collection of data. In this example,
_custOrderHist is dependency injected from the constructor.
List<CustOrderHist> custOrderHistList =
await _custOrderHist.Run("ALFKI");
The Set Up
To effectively achieve a clean architecture like the rest of the generated code, we need to register the object with ASP.NET Core's DI container,
and then inject it (stored procedure business layer) to the constructor of the controller, service, or class that will be using it. To test, simply call it.
Register With The DI Container
1. Register the object in the program.cs. Register it in the Web API project (service layer) if you generated it, otherwise, register it in the Web Application project.
// get the connection string
string connectionString =
builder.Configuration.GetValue<string>("Settings:ConnectionString");
// register stored proc for DI
builder.Services.AddScoped(e =>
new CustOrderHist(connectionString));
Dependency Injection
2. Inject the object in the constructor. This example uses a Web API.
[ApiController]
public class StoredProcController : ControllerBase
{
private CustOrderHist _custOrderHist;
// inject the stored proc class
public StoredProcController(CustOrderHist custOrderHist)
{
_custOrderHist = custOrderHist;
}
[Route("[controller]/getcustorderhist")]
[HttpGet]
public async Task<List<CustOrderHist>> GetCustOrderHist(string customerID)
{
// run the stored procedure and get the results
List<CustOrderHist> custOrderHistList =
await _custOrderHist.Run(customerID);
return custOrderHistList;
}
}
Use It, Or, Test It
3. Call the Web API service that runs the stored procedure business layer class. This example uses a web service,
you can call it from any app, e.g. asp.net, javascript, java, python, etc.
The stored procedure in this example returns a collection of data (uses a select statement) assigned to the
custOrderHist. You can assign the custOrderHist to a grid component who's datasource is a List, or loop through the data using a foreach statement, etc.
List<CustOrderHist> custOrderHist = null;
string customerID = "ALFKI";
string responseBody = await
Functions.HttpClientGetAsync("StoredProc/GetCustOrderHist/?customerID="
+ customerID);
if (!String.IsNullOrEmpty(responseBody))
custOrderHist = JsonConvert.DeserializeObject<List<CustOrderHist>>(responseBody);