Call a stored procedure in ASP.NET Core 3.1/5.0

user_16241 0
Viewed 1655

I have a simple ASP.NET Core service that needs to have exactly one interaction with a database. It calls a stored procedure that returns a BigInt/long.

It seems a bit of overkill to fire up all the stuff to run Entity Framework Core for this. Especially since EF Core does not do stored procedures very well (as of the last time I checked).

Is there a .NET Core only way to call and get the results of a stored procedure (without using EF Core or other frameworks)?

asp.net5 .net-5 asp.net-core-3.1 .net-core-3.1 c#
2 Answers

Something like this is the minimum you would need. Obviously you might want to store the connection string in a config file.

await using DbConnection connection = new SqlConnection("Connection_String");
await connection.OpenAsync();
await using var command = connection.CreateCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "Stored_Proc_Name";
// Declare any parameters here
var p = command.CreateParameter();
p.ParameterName = "IsCool";
p.Value = true;
p.DbType = System.Data.DbType.Boolean;
command.Parameters.Add(p);

var result = await command.ExecuteScalarAsync();
if (result == null) {
    throw new Exception("Bad");
}

long numValue = (long) result;
user_1494360

I had a similar problem with Razor page in a .NET Core 5 web app. In order to get the connection strings into scope, I had to inject the configuration into the class. The code looks something like this:

 public class AskSQLModel : PageModel
{

    public AskSQLModel(IConfiguration _config)
    {
        this.Configuration = _config;
    }
    public IConfiguration Configuration { get; set; }

    /// <summary>
    /// Property to hold value returned from stored procedure
    /// </summary>
    public long ReturnValue { get; set; }


    public void OnGet()
    {

        string cn = this.Configuration["ConnectionStrings:SQLConnect"];
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(cn);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "GetLong";

        cmd.Connection.Open();
        ReturnValue = (long)cmd.ExecuteScalar();
        cmd.Connection.Close();
    }
}

The Razor page code looks like this:

@page
@model AskSQLModel
@{
}

<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>This page will get a value from SQL Server.</p>

    <h4>The value is: @Model.ReturnValue</h4>

</div>

And finally the resulting page displays this:

Welcome
This page will get a value from SQL Server.

The value is: 17
user_157141920
Related