Entity Framework Group Summing: 3 - Stored Procedures Part A


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.
    • 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

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.

 TopicSubtopic
   
 This Category Links 
Category:Web Sites Index:Web Sites
  Next: > Entity Framework Group Summing
<  Prev:   Entity Framework Group Summing