CODEWITHSUNDEEP
entity-frameworkdatatableado.netdataset
Sisyphus
Sisyphus

Reputation: 910

Entity Framework : Use stored procedure to return raw table result

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?

Upvotes: 3

Views: 5101

Answers (3)

YMH
YMH

Reputation: 3093

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

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

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

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);
}

Upvotes: 2

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89051

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);
            }
            //. . .

Upvotes: 2

Related Questions