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.
Instructions
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 ).
Info |
---|
Related articles
Filter by label (Content by label) | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...