Entity Framework : Use stored procedure to return raw table result

Question

Is it possible in EF to use stored procedures to return raw DataTable/DataSet like in classic ADO.net instead of returning a mapped/translated entity?

score 2 · Accepted Answer · answered Jun 13 '17 at 12:14

EF is built on top of ADO.NET, so whenever you need to you can directly access the DbConnection for a DbContext and use it directly. Eg

        using (var db = new MyDbContext())
        {
            db.Database.Connection.Open();
            var con = (SqlConnection)db.Database.Connection;
            var cmd = new SqlCommand("exec MyProc", con);
            DataTable dt = new DataTable();
            using (var rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);
            }
            //. . .

score 2 · Answer 2 · answered Jun 13 '17 at 12:48

Yes it is possible.

Below I described how I did this. A stored procedure is part of your database. Therefore it is best to add the stored procedure to the class which accesses your database and creates the database model: your dbContext.

public class MyDbContext : DbContext
{
    // TODO: add DbSet properties

    #region stored procedures
    // TODO (1): add a function that calls the stored procedure
    // TODO (2): add a function to check if the stored procedure exists
    // TODO (3): add a function that creates the stored procedure
    // TODO (4): make sure the stored procedure is created when the database is created
    #endregion stored procedure
}

TODO (1): Procedure that calls the stored procedure:

private const string MyStoredProcedureName = ...;
private const string myParamName1 = ...;
private const string myParamName2 = ...;

public void CallMyStoredProcedure(MyParameters parameters)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(myParamName1, parameters.GetParam1Value(),
        new SqlParameter(myParamName2, parameters.GetParam2Value(),
        ... // if needed add more parameters
    };
    const string sqlCommand = @"Exec " + MyStoredProcedureName
        + "  @" + myParamName1
        + ", @" + myParamName2
        ... // if needed add more parameters
        ;
    this.Database.ExecutSqlCommand(sqlComman, functionParameters);
}

TODO (2) Check if stored procedure exists

// returns true if MyStoredProcedure exists
public bool MyStoredProcedureExists()
{
    return this.ProcedureExists(MyStoredProcedureName);
}

// returns true if stored procedure with procedureName exists
public bool StoredProcedureExists(string procedureName)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"procedurename", procedureName),
    };
    string query = @"select [name] from sys.procedures where name= @procedurename";
    return this.Database.SqlQuery<string>(query, functionParameters)
        .AsEnumerable()                       // bring to local memory
        .Where(item => item == procedureName) // take only the procedures with desired name
        .Any();                               // true if there is such a procedure
}

TODO (3) Create the stored procedure:

public void CreateProcedureUpdateUsageCosts(bool forceRecreate)
{
    bool storedProcedureExists = this.MyStoredProcedureExists();

    // only create (or update) if not exists or if forceRecreate:
    if (!storedProcedureExists || forceRecreate)
    {  // create or alter:
        Debug.WriteLine("Create stored procedures");

        // use a StringBuilder to create the SQL command that will create the
        // stored procedure
        var x = new StringBuilder();

        // ALTER or CREATE?
        if (!storedProcedureExists)
        {
            x.Append(@"CREATE");
        }
        else
        {
            x.Append(@"ALTER");
        }

        // procedure name:
        x.Append(@" procedure ");
        x.AppendLine(MyStoredProcedureName);

        // parameters: (only as an example)
        x.AppendLine(@"@ReportPeriod int,");
        x.AppendLine(@"@CustomerContractId bigint,");
        x.AppendLine(@"@CallType nvarChar(80),");
        // etc.

        // code
        x.AppendLine(@"as");
        x.AppendLine(@"begin");
        // only as example some of my procedure
        x.AppendLine(@"Merge [usagecosts]");
        x.AppendLine(@"Using (Select @ReportPeriod as reportperiod,");
        x.AppendLine(@"              @CustomerContractId as customercontractId,");
        ...
        x.AppendLine(@"end");

        // execute the created SQL command
        this.Database.ExecuteSqlCommand(x.ToString());
    }
}

TODO (4) Make sure the stored procedure is created when the database is created

In MyDbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // TODO: if needed add fluent Api to build model

    // create stored procedure
    this.CreateProcedureUpdateUsageCosts(true);
}

Usage:

using (var dbContext = new MyDbContext(...))
{
   MyParameters parms = FillMyParameters();
   dbContext.CallMyStoredProcedure(parms);
}

For beginners it will look like over engineered but for reducing time and complexity it will be best approach. One more thing what i want to know from you, is there any alternative to create step 3. actually i dont want to do thing like Append the string. thank you for sharing — adnan umar, Sep 30 '22 at 03:42
If you don't want to create the strings, it is always possible to write them as two strings, one for Create and one for Alter: `const string SqlCreateText = "Create Procedure " + myStoredProcedureName + "... etc, ";` — Harald Coppoolse, Oct 03 '22 at 11:13

score 0 · Answer 3 · answered Aug 06 '22 at 22:27

0

Check this: https://stackoverflow.com/a/73263659/1898992.

You just need to replace the sql query with your stored procedure.

answered Aug 06 '22 at 22:27

Mimina

2,603
2
29
21

Entity Framework : Use stored procedure to return raw table result

3 Answers3