CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
skiskd
skiskd

Reputation: 423

How to pass a null value into a stored procedure with Entity Framework?

I have an MVC application using Entity Framework. I want to pass a parameter having a null value to a stored procedure. I want to pass MerchantID as null in some cases.

GetValues(int[] TicketID,int? MerchantID,bool IsOpen)
{

//TicketID has values 1123,1122 etc
//MerchantID sometimes null
//IsOpen true/false

  DataTable tbldepartmentid = new DataTable("Departmentid");
  tbldepartmentid.Columns.Add("VALUE", typeof(int));
  
  foreach (var id in TicketID)
      tbldepartmentid.Rows.Add(id);

 List<GetTroubleTicketDetails_Result> GetTroubleTicketDetails = _getTroubleTicketDetails_Result.ExecuteCustomStoredProc("Tickets.GetDetails", " @GroupID,@MerchantID,@Open",
                 new SqlParameter("GroupID", SqlDbType.Structured) { Value = tbldepartmentid, TypeName = "dbo.tblTVPForCSVINT" }
                 , new SqlParameter("MerchantID", MerchantID)
                 , new SqlParameter("Open", IsOpen)).ToList();
                return GetTroubleTicketDetails;
}

My problem is that when I pass MerchantID=null, it gives me the below error:

"The parameterized query '(@GroupID [dbo].[tblTVPForCSVINT] READONLY,@MerchantID nvarchar(' expects the parameter '@MerchantID', which was not supplied."

How can I pass a null value for MerchantID?

Upvotes: 13

Views: 12488

Answers (2)

Greg Gum
Greg Gum

Reputation: 37909

This is an example which uses entity framework core 7. The SP in question has two parameters which may be null:

var zoneParameter = new SqlParameter("zone", zone ?? (object)DBNull.Value);

var csrIdParameter = new SqlParameter("csr_id", csrId ?? (object)DBNull.Value);
        
result = await _dbContext.UtilitySPByUtility
 .FromSql($"{SqlStrings.SP.GetIntroRateByUtility} @utility_id = {utilityId}, @commodity_id = {commodityId}, @zone = {zoneParameter}, @csr_id = {csrIdParameter} ").ToListAsync();

Note that there are no quotes around the @zone parameter, even though it is a string.

Also note that it uses FromSql, not FromSqlRaw which can lead to a SqlInjection attack and is not recommended by MS.

This is the SQL which is generated:

 Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (37ms) [Parameters=[p0='?' (Size = 4000), p1='?' (DbType = Int32), p2='?' (DbType = Int32), zone='?' (Size = 1), csr_id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
 @p0 @utility_id = @p1, @commodity_id = @p2, @zone = @zone, @csr_id = @csr_id

Upvotes: 0

Jurgen Camilleri
Jurgen Camilleri

Reputation: 3589

You need to pass SqlInt32.Null instead of null as follows:

new SqlParameter("MerchantID", MerchantID ?? SqlInt32.Null)

Upvotes: 29

Related Questions