Call and return SQL function value in MVC

Question

I am using ASP.NET MVC, SQL database with model and entity. I wrote a SQL function in the database, and C# code in controller. I am trying to call a SQL function with C# and store SQL function returned int to C# int variable. And I have trouble time trying to work it.

I wrote the test code so when I know what to do, I can use this knowledge to resolve the problem I'm having.

I've tried researching, but none of the answers helped so far.

SQL Code:

CREATE FUNCTION dbo.TestFunction(@Number INT)
RETURNS INTEGER
AS 
BEGIN
    IF (@Number = 1)
    BEGIN
        RETURN 45
    END

    RETURN 20
END

C# code:

using (websiteDBEntities d = new websiteDBEntities())
{
    int check = d.Database.ExecuteSqlCommand("SELECT dbo.TestFunction(1)");

    if (check == 45)
         return RedirectToAction("Action", "Controller");
}

return RedirectToAction("Action 2", "Controller");

I am trying to return either 20 or 45 depending on a parameter value.

So, is there a proper way to store a SQL function returned value to C# variable?

asathkum · Accepted Answer · 2019-08-24 08:12:55Z

2

Try d.Database.SqlQuery() - it returns IEnumerable of result data set. It seems that ExecuteSqlCommand() returns number of rows modified.

edited Aug 24, 2019 at 8:12

asathkum

3153 silver badges17 bronze badges

answered Aug 24, 2019 at 3:02

dvitel

5633 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JasonStones Over a year ago

Thank you sooo much. It worked. I changed my code to: var check = d.Database.SqlQuery<int>("SELECT dbo.TestFunction(1)"); if(check.Sum() == 45) return RedirectToAction("Action", "Controller");

Neeraj Agarwal · Accepted Answer · 2019-08-24 03:01:59Z

0

Your function in the second line says that the function returns a bit value and you are returning an int. You should change returns bit to returns int.

answered Aug 24, 2019 at 3:01

Neeraj Agarwal

1,0597 silver badges5 bronze badges

1 Comment

JasonStones Over a year ago

My bad, I meant Integer. I changed it.

Collectives™ on Stack Overflow

Call and return SQL function value in MVC

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related