What is the preferable way to call a stored procedure in an async await way ?

Question

Hi,

I am migrating from EF 6 to EF Core 6.

Now the first thing , I need to know is to migrate my Stored Procedure call .

How can I create an utility/extension method to call a Store Procedure in an Async Await way with in and out type parameters ?

and Where would be the connection string ?

Answer 1

and Currently I am getting this error message in catch block Data is Null. This method or property cannot be called on Null values.

The error usually means there null values in the SQL table but the model design does not have an associated nullable type. Please see the reference documentation.

Entity Properties

I want to use Either FromSqlInterpolated or FromSqlRaw . Can we fix it ?

I showed you how to handle a nullable SQL input parameter above. Your second code sample does use a null input parameter so I'm not sure what problem you are having. Perhaps reread the docs to learn how to create a parameter in C#.

https://learn.microsoft.com/en-us/ef/core/querying/raw-sql#passing-parameters

Answer 2

I need to know is to migrate my Stored Procedure call .

Start with the documentation which is similar to EF 6.

Raw SQL Queries

How can I create an utility/extension method to call a Store Procedure in an Async Await way with in and out type parameters ?

Writing a utility, designing an extension method, and using the async/await pattern are C# constructs covered in the C# programming guide and reference documentation.

Extension Methods (C# Programming Guide)
Asynchronous programming with async and await

If you have a specific question or problem then share your code and explain what problem you are facing rather than asking open ended questions.

and Where would be the connection string ?

In .NET Core the connection string us stored in configuration like appsettings.json. Like above, this information is covered in the fundamental Core documentation.

Configuration in ASP.NET Core
Getting Started with EF Core

Answer 3

My best guess is you're having trouble passing a null parameter to a stored procedure parameter. Unfortunately you did not share the stored procedure or the error message which are the most important bits of information we need.

Anyway, EF exposes ADO.NET as explained in the docs. You must create a parameter which is exactly the same thing you need to do in T-SQL.

The following demo focuses on passing a null parameter to a stored procedure using ADO.NET

Stored procedure

CREATE PROCEDURE dbo.TestNull (
 @keyword VARCHAR(100) NULL
)
AS
BEGIN
 SELECT ISNULL(@keyword, 'Null passed.')
END

The service method.

public async Task<string?> TestNullAsync(string? keyword)
{
    using (var command = _context.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "dbo.TestNull";
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@keyword", keyword == null ? DBNull.Value : keyword));

        _context.Database.OpenConnection();
        string? result = (string?)await command.ExecuteScalarAsync();
        _context.Database.OpenConnection();

        return result;
    }
}

The Controller

namespace WebApiDbDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TestNullController : ControllerBase
    {
        private readonly INullParameterTest _service;
        public TestNullController(INullParameterTest service)
        {
            _service = service;
        }

        [HttpGet]
        public async Task<IActionResult> GetAsync(string? keyword)
        {
            return Ok(await _service.TestNullAsync(keyword));
        }
    }
}

Answer 4

Thank you AgaveJoe.

OK see my code.

 public interface IFranchiseRespository
    {
        void WriteMessage(string message);
        Task<List<FranchiseWebInquiry>> SearchFranchisesWebInquiryRes(string keyword);
    }




public class FranchiseRespository : IDisposable, IFranchiseRespository
    {
        private readonly CarRentalContext _dbContext;
        private readonly ILogger<FranchiseRespository> _logger;

        public FranchiseRespository(ILogger<FranchiseRespository> logger, CarRentalContext context)
        {
            _dbContext = context;
            _logger = logger;
        }
        public Task<List<FranchiseWebInquiry>> SearchFranchisesWebInquiryRes(string keyword)
        {

            var lObjResults = _dbContext.Franchises
                .FromSqlInterpolated($"EXECUTE dbo.GetFranchisesForWeb({keyword})")                
                .ToListAsync();          

            return lObjResults;

        }

        public void Dispose()
        {
            // Dispose of unmanaged resources.
            _dbContext.DisposeAsync();          
            // Suppress finalization.
            GC.SuppressFinalize(this);
        }

        public void WriteMessage(string message)
        {
            _logger.LogInformation($"MyDependency2.WriteMessage Message: {message}");
        }
    }


public class FranchiseWebInquiry
    {
        public int FranchisesId { get; set; }
        public int SubOfficeId { get; set; }
        public string FranchiseName { get; set; }
        public string SubOfficeName { get; set; }
        public string Code { get; set; }
        public string PickupId { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Address3 { get; set; }
        public string PostCode { get; set; }
        public string PrimaryContact { get; set; }
        public string Email { get; set; }
        public string Town { get; set; }
        public decimal Distance { get; set; }
        public DateTime MonStart { get; set; }
        public DateTime MonEnd { get; set; }
        public DateTime TueStart { get; set; }
        public DateTime TueEnd { get; set; }
        public DateTime WedStart { get; set; }
        public DateTime WedEnd { get; set; }
        public DateTime ThuStart { get; set; }
        public DateTime ThuEnd { get; set; }
        public DateTime FriStart { get; set; }
        public DateTime FriEnd { get; set; }
        public DateTime SatStart { get; set; }
        public DateTime SatEnd { get; set; }
        public DateTime SunStart { get; set; }
        public DateTime SunEnd { get; set; }
        public DateTime PickupDate { get; set; }
        public DateTime PickupTime { get; set; }
        public DateTime DropDate { get; set; }
        public DateTime DropTime { get; set; }
        public object FranchiseWeekPlan { get; set; }
        public object FranchiseFleets { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }

public class CarRentalContext : DbContext
    {
        public CarRentalContext(DbContextOptions<CarRentalContext> options)
           : base(options)
        {
        }

        public DbSet<FranchiseWebInquiry> Franchises { get; set; }     
    }


--   appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=(local);Database=test;User ID=sa;Password=test1;multipleactiveresultsets=True;Integrated Security=SSPI;Connection Timeout=180;"
  }
}

-- Program.cs

using CarRentalWidget.BLL.DAL;
using CarRentalWidget.BLL.DB;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddCors(options =>
{
    options.AddPolicy(name: "MyPolicy",
                policy =>
                {
                    policy.WithOrigins("https://localhost:7260/")
                            .WithMethods("POST", "PUT", "DELETE", "GET");
                });
});
// Add services to the container.
builder.Services.AddControllersWithViews();
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddSqlServer<CarRentalContext>(connectionString);
//builder.Services.AddDatabaseDeveloperPageExceptionFilter();
builder.Services.AddScoped<IFranchiseRespository, FranchiseRespository>();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseRouting();
app.UseCors();
app.MapControllerRoute(
    name: "default",
    pattern: "{controller}/{action=Index}/{id?}");
app.MapFallbackToFile("index.html"); 
app.Run();





[ApiController]
    [Route("api/[controller]")]
    public class CarRentalController : ControllerBase
    {
        private readonly IFranchiseRespository _repository;    

        private readonly ILogger<CarRentalController> _logger;

        public CarRentalController(ILogger<CarRentalController> logger, IFranchiseRespository repository)
        {
            _logger = logger;
            _repository = repository;
        }

        [EnableCors]
        [HttpGet("allfranchises")]
        //[Consumes("application/json")]
        public async Task<ActionResult<List<FranchiseWebInquiry>>> GetActiveFranchises()
        {

            List<FranchiseWebInquiry> items = await _repository.SearchFranchisesWebInquiryRes(null);

            return items;      
        }
    }

This is the code. Now you can check it.

Answer 5

I am getting different kind of errors.

let me show my update logic now.

this is my updated model

 [Keyless]
    public class FranchiseWebInquiry
    {
        public int FranchisesId { get; set; }
        public int SubOfficeId { get; set; }
        public string FranchiseName { get; set; }
        public string SubOfficeName { get; set; }
        public string Code { get; set; }
        public string PickupId { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Address3 { get; set; }
        public string PostCode { get; set; }
        public string PrimaryContact { get; set; }
        public string Email { get; set; }
        public string Town { get; set; }     
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }




public class FranchiseRespository : IDisposable, IFranchiseRespository
{
    private readonly CarRentalContext _dbContext;
    private readonly ILogger<FranchiseRespository> _logger;

    public FranchiseRespository(ILogger<FranchiseRespository> logger, CarRentalContext context)
    {
        _dbContext = context;
        _logger = logger;
    }
    public Task<List<FranchiseWebInquiry>> SearchFranchisesWebInquiryRes(string keyword)
    {
        try
        {              
             var items = _dbContext.GetFranchiseList(keyword);
            return items;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex.Message);
            return null;
            //throw;
        }           
    }

    public void Dispose()
    {
        // Dispose of unmanaged resources.
        _dbContext.Dispose();
        //_logger = null;
        // Suppress finalization.
        GC.SuppressFinalize(this);
    }

    public void WriteMessage(string message)
    {
        _logger.LogInformation($"MyDependency2.WriteMessage Message: {message}");
    }
}


 public interface IFranchiseRespository
    {
        void WriteMessage(string message);
        Task<List<FranchiseWebInquiry>> SearchFranchisesWebInquiryRes(string keyword);
    }





public class CarRentalContext : DbContext
    {
        public CarRentalContext(DbContextOptions<CarRentalContext> options)
           : base(options)
        {
        }

        public DbSet<FranchiseWebInquiry>? Franchises { get; set; }
        public virtual Task<List<FranchiseWebInquiry>> GetFranchiseList(string keyword)
        {

            return this.Set<FranchiseWebInquiry>()//.FromSqlInterpolated<FranchiseWebInquiry>($"EXECUTE dbo.GetFranchisesForWeb({keyword})")
                .FromSqlRaw("EXECUTE dbo.GetFranchisesForWeb {0}", keyword)
                        .AsNoTracking()
                        .ToListAsync();            
        } 
    }

Remaining files have already been shared above.

and this is the stored procedure (This is an example and doesn't contain full logic in it)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFranchisesForWeb]
    @pStrSearchQuery NVarChar(1000)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT Distinct [t0].[Id] AS [FranchisesId], null AS [SubOfficeId], CONCAT( 'F-', [t0].[Id]) AS [PickupId], [t0].[Name] AS [FranchiseName], null AS [SubOfficeName], [t0].[Code], [t0].[Address1], [t0.[Address2], [t0].[Address0], [t0].[Postcode] AS [PostCode], [t0].[Number] AS [PrimaryContact], 
    [t0].[Email], [t0].[Name] AS [Town], [t0].[Latitude], [t0].[Longitude]
    FROM [Franchise] AS [t0]    
    [t0].[Title] = @pStrSearchQuery;

END

and Currently I am getting this error message in catch block

 Data is Null. This method or property cannot be called on Null values.

I want to use Either FromSqlInterpolated or FromSqlRaw . Can we fix it ?