This article is a convention for writing store procedure and integration with back-end code. Everybody is requested to follow the following coding conventions on this project alongside regular conventions.
Create a step-by-step guide:
A stored procedure must have a drop create option. To prevent Parameter Sniffing don't use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries. i.e: DROP PROCEDURE IF EXISTS {YOUR_STORE_PROCEDURE}
GO
CREATE PROCEDURE {YOUR_STORE_PROCEDURE}
@Param INT
AS
DECLARE @TempParam INT
SET @TempParam = @Param
BEGIN
SELECT *
FROM Customers e WHERE
CustomerId = @TempParam
END
GO
In C# code make sure you have close the DbConnection
after SP calling. For example:try
{
EnsureConnectionOpen();
//your SP calling code
}
catch (Exception ex)
{
//your exception handling plan
}
finally
{
CloseDbConnection();
}
To avoid UnitOfWork
related exception please use[UnitOfWork(isTransactional: false)]
attribute above the SP calling method (Public ).
Highlight important information in a panel like this one. To edit this panel's color or style, select one of the options in the menu below. |
The content by label feature displays related articles automatically, based on labels you choose. To edit options for this feature, select the placeholder below and tap the pencil icon.
|