Entity Framework Group Summing: 3 - Stored Procedures Part A
web code jekyll html linq grouping entity-framework sqlsvr stored-procedures
How to: Calling a Microsoft Sql Server Database Stored Procedure from a C# Entity Framework app where the result is a scalar or string type.
In the previous 2 posts in this series, a count is made of the number of times a helper has volunteered to do an activity over a season of athletics meets. In the first post, Linq was used. In the second post a Sql View is used to do the required processing on the database with ExecuteRawSql is used to get the result into the app. Both achieve the same result. A Sql View can’t be parameterized and so the View must recreated for each query where the date range of meets changes. Could a parameterized Stored Procedure be used instead?
The app is a Blazor Service app using Entity Framework for access to the Azure Sql data. For this version, Entity Framework was upgraded to version 7 (as per issues flagged in the previous blog post on this site). The Code-First approach is used where entities are defined as classes and added to the database context. The code behind is then updated add=migration
command and then update-database
command to update the database table/s.
This first post on Stored Procedures examines returning scalar and string query results. The second post on Stored Procedures returns collections.
Integer Stored Procedure output
CREATE PROCEDURE [dbo].[GetMe]
(
@Start int ,
@End int,
@output int
)
AS
BEGIN
Set @output = @Start + @End;
Return
END
The Stored Procedure
int result = 0;
var xparams = new SqlParameter[]{
new SqlParameter("@Start", 10),
new SqlParameter("@End", 20),
new SqlParameter("@output",outInt) {Direction = ParameterDirection.Output }
};
_dbContextcontext.Database.ExecuteSqlRaw("exec GetMe @Start, @End, @output result", xparams);
var ReturnValue = ((SqlParameter)xparams[2]).Value;
The code to make the Stored Procedure call and return the result
Other Scalar query results
Some other scalar date types:
- Float, real
- date
For these you change the code above as follows
- In the c# code change the data type of
output
- in the Stored Procedure
- Change @Output data type to match
- Assign it to a suitable non null value.
It has to be initialized.
- Assign it to a suitable non null value.
- Change the set assignment to a suitable expression
- eg Set @output = Convert(datetime,’23-3-2923’)
- Set @output = @Start/@End .. to ger=t a real or float
- Change @Output data type to match
But this doesn’t work for a string/varchar
String Stored Procedure Output
CREATE PROCEDURE [dbo].[GetMe]
(
@Start int ,
@End int,
@output varchar(50) output
)
AS
BEGIN
Set @output = 'Some Text';
Return
END
The Stored Procedure
string output="";
var xparams = new SqlParameter[]{
new SqlParameter("@Start", 10),
new SqlParameter("@End", 20),
new SqlParameter("@output",SqlDbType.NVarChar, 4000) {Direction = ParameterDirection.Output }
};
_ontext.Database.ExecuteSqlRaw("exec GetMe @Start, @End, @output output", xparams);
var ReturnValue = ((SqlParameter)xparams[2]).Value;
The C# code to call the Stored Procedure
Obviously we could have used this as template for the scalar types eg (int type):
...
new SqlParameter("@output",SqlDbType.int) {Direction = ParameterDirection.Output }
...
Discussion
Code has been presented that handles returning a scalar value or string from a Microsoft Sql Stored procedure to a calling sequence of C# code that includes Entity Framework, Whilst the app is a Code-First EF app, it is stated elsewhere that Stored Procedures are normally the domain of Database-First EF coding. Consideration was given to using Stored Procedures in the app in question because unlike Sql Views, they can be parameterized. The task in question requires start and end parameters. The next post looks at that implementation were an adhoc collection is returned, specifically where it does not have database context.
Topic | Subtopic | |
This Category Links | ||
Category: | Web Sites Index: | Web Sites |
Next: > | Entity Framework Group Summing | 4 - Stored Procedures Part B |
< Prev: | Entity Framework Group Summing | 2 - ExecuteRawSql |