CODEWITHSUNDEEP
c#sql-serverentity-frameworkstored-proceduresasp.net-web-api
yuro
yuro

Reputation: 2209

Working with stored procedures (SQL Server) in WebAPI2

I don't know how to implement a stored procedure in my WebAPI controller in C#. I've created a simple stored procedure in my SQL Server database.

Here the example code:

CREATE PROCEDURE GetAllGroups 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, name, code
    FROM dbo.Groups
END
GO

The stored procedure I've added in my Entity Framework Model. Now I want to use it in the WebAPI Controller, but I don't know how. I want to output all Groups.

Here is my WebAPI controller of Group:

public class QR_GroupController : ApiController
{
  private SampleContext db = new SampleContext();

  // GET: api/group
  public IQueryable<Group> GetAllGroups()
  {
     return db.Groups;
  }
}

EDIT:

My new Code:

public IQueryable<QR_Group> GetAllGroups()
{
   //The Stored procedure
   return db.GetAllGroups();
}

EDIT3:

That's the currently error:

Cannot implicitly convert type 'int' to 'System.Linq.IQueryable'

How can I define a List in the stored procedure?

Upvotes: 1

Views: 1467

Answers (2)

M.Ali
M.Ali

Reputation: 69524

Stored procedure fixes:

CREATE PROCEDURE GetAllGroups 
    @Id int,
    @name nvarchar(100),   --<-- define a length for param
    @code nvarchar(100)    --<-- define a length for param
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM dbo.Groups
    WHERE Name = @Name   --<-- and finally actually use those params in your query
      AND Code = @Code 
      AND Id   = @Id 
END
GO

Upvotes: 0

user3587767
user3587767

Reputation: 152

Error resolution is, you are not calling it as method. GetAllGroups should be called with () as follows

return db.GetAllGroups();

Upvotes: 2

Related Questions