Monday, February 25, 2019

Calling SQL Server stored procedures from Entity Framework

Monday, February 25, 2019  Vikas Sharma  

Using the methods below, you can obtain the stored procedure return value, along with your data, which I've not seen in other blogs.
A stored proc should return 0 for success, and any other value for a failure.

Here is an example table, filled with data:
CREATE TABLE DemoData
(
    id INT NOT NULL PRIMARY key,
    someValue DECIMAL(4,4) NOT NULL
)
GO
INSERT INTO DemoData(id, someValue)
VALUES (1, 1.23), (2, 2.34), (3, 3.45), (4, 4.56)
 
 
Here are our example stored procedures:
 
CREATE PROCEDURE GetDemoData(@maxId INT)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, someValue FROM DemoData WHERE id <= @maxId
END
GO
 
CREATE PROCEDURE AddTwoValues(@a INT, @b INT)
AS
BEGIN
    SET NOCOUNT ON;
    RETURN @a + @b -- Don't do this. Stored procs should return
                   -- 0 for success, and any other value for failure.
END
GO
 
CREATE PROCEDURE AddTwoValuesWithResult(@a INT, @b INT, @result INT OUTPUT, @result2 INT OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    SET @result = @a + @b
    SET @result2 = @b - @a
END
GO
 
CREATE PROCEDURE ConvertToString(@someValue INT, @someString VARCHAR(20) OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    SET @someString = '*' + CAST(@someValue AS VARCHAR(20)) + '*'
END
GO
 
 Here is the C# code to call the above:
 
 
public class DemoData
{
    public Int32 Id { get; set; }
    public Decimal? SomeValue { get; set; }
}
 
public class MyDbContext : DbContext
{
    static MyDbContext()
    {
        Database.SetInitializer<MyDbContext>(null);
    }
 
    public MyDbContext()
        : base("Name=MyDbContext")
    {
    }
 
    public MyDbContext(string connectionString)
        : base(connectionString)
    {
    }
 
    public MyDbContext(string connectionString,
        System.Data.Entity.Infrastructure.DbCompiledModel model)
        : base(connectionString, model)
    {
    }
 
    // Stored Procedures
    public int AddTwoValues(int a, int b)
    {
        var procResult = new SqlParameter
        {
            ParameterName = "@procResult",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output
        };
 
        Database.ExecuteSqlCommand(
            "exec @procResult = AddTwoValues @a, @b",
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@a",
                Value = a,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            new SqlParameter
            {
                ParameterName = "@b",
                Value = b,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            procResult
        });
 
        return (int)procResult.Value;
    }
 
    public int AddTwoValuesWithResult(int a, int b, out int result, out int result2)
    {
        var resultParam = new SqlParameter
        {
            ParameterName = "@result",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output
        };
        var result2Param = new SqlParameter
        {
            ParameterName = "@result2",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output };
        var procResult = new SqlParameter
        {
            ParameterName = "@procResult",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output
        };
 
        Database.ExecuteSqlCommand(
            "exec @procResult = AddTwoValuesWithResult @a, @b, @result OUTPUT, @result2 OUTPUT",
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@a",
                Value = a,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            new SqlParameter
            {
                ParameterName = "@b",
                Value = b,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            resultParam,
            result2Param,
            procResult
        });
 
        result = (int)resultParam.Value;
        result2 = (int)result2Param.Value;
 
        return (int)procResult.Value;
    }
 
    public int ConvertToString(int someValue, out string someString)
    {
        var someStringParam = new SqlParameter
        {
            ParameterName = "@someString",
            SqlDbType = SqlDbType.VarChar,
            Size = 20,
            Direction = ParameterDirection.Output
        };
        var procResult = new SqlParameter
        {
            ParameterName = "@procResult",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output
        };
 
        Database.ExecuteSqlCommand(
            "exec @procResult = ConvertToString @someValue, @someString OUTPUT",
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@someValue",
                Value = someValue,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            someStringParam,
            procResult
        });
 
        someString = (string)someStringParam.Value;
             
        return (int)procResult.Value;
    }
 
 
    public List<DemoData> GetDemoData(int maxId, out int procResult)
    {
        var procResultParam = new SqlParameter
        {
            ParameterName = "@procResult",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.Output
        };
             
        var sqlQuery = Database.SqlQuery<DemoData>(
            "exec @procResult = GetDemoData @maxId",
            new object[]
        {
            new SqlParameter
            {
                ParameterName = "@maxId",
                Value = maxId,
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Input
            },
            procResultParam
        }).ToList();
 
        procResult = (int) procResultParam.Value;
 
        return sqlQuery;
    }
}
 
To call the above and return the results: 

using (var db = new MyDbContext())
{
    Console.WriteLine("AddTwoValuesWithResult");
    int result, result2;
    int procResult = db.AddTwoValuesWithResult(3, 7, out result, out result2);
    Console.WriteLine("result = " + result);
    Console.WriteLine("result2 = " + result2);
    Console.WriteLine("procResult = " + procResult);
    Console.WriteLine("");
 
    Console.WriteLine("ConvertToString");
    string someString;
    procResult = db.ConvertToString(56, out someString);
    Console.WriteLine("someString = " + someString);
    Console.WriteLine("procResult = " + procResult);
        Console.WriteLine("");
 
    Console.WriteLine("AddTwoValues");
    procResult = db.AddTwoValues(5, 10);
    Console.WriteLine("procResult = " + procResult);
    Console.WriteLine("");
 
    Console.WriteLine("GetDemoData");
    var list = db.GetDemoData(3, out procResult);
    Console.WriteLine("procResult = " + procResult);
    foreach (var i in list)
    {
        Console.WriteLine(i.Id + ", " + i.SomeValue);
    }
}
 Yields the following output: 
 
AddTwoValuesWithResult
result = 10
result2 = 4
procResult = 0
 
ConvertToString
someString = *56*
procResult = 0
 
AddTwoValues
procResult = 15
 
GetDemoData
procResult = 0
1 1.230
2 2.340
3 3.450
 
 
This will be available in the EntityFramework Reverse POCO Generator, available at https://visualstudiogallery.msdn.microsoft.com/ee4fcff9-0c4c-4179-afd9-7a2fb90f5838 
 
 
 
 

Posted in: ,

0 comments:

Post a Comment

Twitter Delicious Facebook Digg Stumbleupon Favorites More