How to create web Api execute stored procedure

Question

How to create web Api execute stored procedure

ahmed salah 3,131

I work on SQL server 2012 and web API entity framework .NET core 2.2 so I face issue I can't implement web API execute stored Procedure below

Create proc ItemCalculateStock
@OptionId int=NULL,
@ItemId  int = NULL,
@InventoryLocation int=NULL
as
begin

if(@OptionId=1)
begin

SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining  

FROM [dbo].[Invenroty] n with(nolock)
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
and i.id=case when @ItemId is null then n.itemid else @ItemId end 

GROUP BY i.itemName,l.InventoryName
end
else
begin

SELECT i.itemName,l.InventoryName,PostingDate, case when QTY > 0  then QTY else 0 end  as PurchasedItem,case when QTY < 0  then -QTY else 0 end  as ConsumItems,case when QTY > 0 then QTY else 0 end - case when QTY < 0 then QTY else 0 end as remaining  
FROM [dbo].[Invenroty] n with(nolock)
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
and i.id=case when @ItemId is null then n.itemid else @ItemId end 


end
end

so How to get result of stored procedure on web API using Entity Framework .NET core 2.2

[HttpGet("CalculateInventoryData")]

    public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation)
    {
    // here how to get stored procedure result here
    // so i ask question to know how to get result of stored procedure above
    }

to call API I use the link below :

https://localhost:44374/api/Inventory/getInventoryData?optionId=1&ItemId=2&InventoryLocation=1

AgaveJoe 20,151 Reputation points

2021-07-06T11:55:39.563+00:00

Executing raw queries is covered in the official Entity Framework docs. Keep in mind, 2.2 is out of support and you should consider upgrading rather than using deprecated methods. If I recall the 2.2 command was .FromSql() and the new command is .FromSqlRaw().

ahmed salah 3,131

i try like that

 public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation, [FromQuery] DateTime StartDate, [FromQuery] DateTime EndDate)
        {
            var result = _connection.Database.ExecuteSqlCommand("ItemCalculateStock @OptionId ,@ItemId,@InventoryLocation,@StartDate,@EndDate ", parameters: new[] {optionId, ItemId, InventoryLocation, StartDate, EndDate});
            return Ok(result);
        }

i get error not best type found
so how to solve this issue please

AgaveJoe 20,151 Reputation points

2021-07-06T12:35:43.427+00:00

It seems you did not read the linked documentation. The ExecuteSqlCommand is not designed to return a result set. Use the ExecuteReader and populate the type manually.

Otherwise, register the type in the DbContext and use the .FromSql() method.

ahmed salah 3,131

i try like below
if can issue on adding parameter
can you help me please

 [HttpGet("CalculateInventoryData")]          
public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation)  
        {  
            DataTable dtresult = new DataTable();  
             
             
            using (var command = _connection.Database.GetDbConnection().CreateCommand())  
            {  
                command.CommandType = CommandType.StoredProcedure;  
                command.CommandText = "ItemCalculateStock";  
                command.Parameters.AddWithValue("@OptionId", optionId);  
                command.Parameters.AddWithValue("@ItemId", ItemId);  
                command.Parameters.AddWithValue("@InventoryLocation", InventoryLocation);  
             
            
            
                _connection.Database.OpenConnection();  
                using (var result = command.ExecuteReader())  
                {  
  
                    dtresult.Load(result);  
                }  
                return Ok(dtresult);  
                
            }  
        }

AgaveJoe 20,151

You are not following the linked documentation at all.

command.CommandText = "ItemCalculateStock";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@OptionId", optionId));
command.Parameters.Add(new SqlParameter("@ItemId", itemId));
_bloggingContext.Database.OpenConnection();
using (var result = command.ExecuteReader())
{
    // do something with result
}

I strongly recommend using .FromSql() and registering a model rather than manually populating the model.

Zhi Lv - MSFT 21,716 Reputation points Microsoft Vendor

2021-07-07T03:10:44.323+00:00

Hi @ahmed salah ,

i get error not best type found

Can you post the detailed error message?

Here is an article about ExecuteSqlCommand method, it looks that your code is correct. but please check the limitations to use the ExecuteSqlCommand to execute the Stored procedure: Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity. Result cannot contain related data. This means that a stored procedure cannot perform JOINs to formulate the result. Insert, Update and Delete procedures cannot be mapped with the entity, so the SaveChanges method cannot call stored procedures for CUD operations.

AgaveJoe 20,151 Reputation points

2021-07-06T11:55:39.563+00:00

Executing raw queries is covered in the official Entity Framework docs. Keep in mind, 2.2 is out of support and you should consider upgrading rather than using deprecated methods. If I recall the 2.2 command was .FromSql() and the new command is .FromSqlRaw().
ahmed salah 3,131 Reputation points

2021-07-06T12:09:12.843+00:00

i try like that

public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation, [FromQuery] DateTime StartDate, [FromQuery] DateTime EndDate) { var result = _connection.Database.ExecuteSqlCommand("ItemCalculateStock @OptionId ,@ItemId,@InventoryLocation,@StartDate,@EndDate ", parameters: new[] {optionId, ItemId, InventoryLocation, StartDate, EndDate}); return Ok(result); }

i get error not best type found
so how to solve this issue please
AgaveJoe 20,151 Reputation points

2021-07-06T12:35:43.427+00:00

It seems you did not read the linked documentation. The ExecuteSqlCommand is not designed to return a result set. Use the ExecuteReader and populate the type manually.

Otherwise, register the type in the DbContext and use the .FromSql() method.
ahmed salah 3,131 Reputation points

2021-07-06T15:09:41.35+00:00

i try like below
if can issue on adding parameter
can you help me please

[HttpGet("CalculateInventoryData")] public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation) { DataTable dtresult = new DataTable(); using (var command = _connection.Database.GetDbConnection().CreateCommand()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "ItemCalculateStock"; command.Parameters.AddWithValue("@OptionId", optionId); command.Parameters.AddWithValue("@ItemId", ItemId); command.Parameters.AddWithValue("@InventoryLocation", InventoryLocation); _connection.Database.OpenConnection(); using (var result = command.ExecuteReader()) { dtresult.Load(result); } return Ok(dtresult); } }
AgaveJoe 20,151 Reputation points

2021-07-06T16:05:56.11+00:00

You are not following the linked documentation at all.

command.CommandText = "ItemCalculateStock"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@OptionId", optionId)); command.Parameters.Add(new SqlParameter("@ItemId", itemId)); _bloggingContext.Database.OpenConnection(); using (var result = command.ExecuteReader()) { // do something with result }

I strongly recommend using .FromSql() and registering a model rather than manually populating the model.
Zhi Lv - MSFT 21,716 Reputation points Microsoft Vendor

2021-07-07T03:10:44.323+00:00

Hi @ahmed salah ,

i get error not best type found

Can you post the detailed error message?

Here is an article about ExecuteSqlCommand method, it looks that your code is correct. but please check the limitations to use the ExecuteSqlCommand to execute the Stored procedure: Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity. Result cannot contain related data. This means that a stored procedure cannot perform JOINs to formulate the result. Insert, Update and Delete procedures cannot be mapped with the entity, so the SaveChanges method cannot call stored procedures for CUD operations.

How to create web Api execute stored procedure

Activity