CODEWITHSUNDEEP
c#mysqlentity-frameworkstored-procedures
Ivy
Ivy

Reputation: 2305

How to call Stored Procedures with EntityFramework?

I have generated an EF4 Model from a MySQL database and I have included both StoredProcedures and Tables.

I know how to make regular instert/update/fetch/delete operations against the EF but I can't find my StoredProcedures.

This was what I was hoping for: 

using (Entities context = new Entities())
{
    context.MyStoreadProcedure(Parameters); 
}

Edit 1:

This is how it looked without EF: 

sqlStr = "CALL updateGame(?,?,?,?,?,?,?)";

commandObj = new OdbcCommand(sqlStr, mainConnection);
commandObj.Parameters.Add("@id,", OdbcType.Int).Value = inGame.id;
commandObj.Parameters.Add("@name", OdbcType.VarChar, 255).Value = inGame.name;
commandObj.Parameters.Add("@description", OdbcType.Text).Value = ""; //inGame.description;
commandObj.Parameters.Add("@yearPublished", OdbcType.DateTime).Value = inGame.yearPublished;
commandObj.Parameters.Add("@minPlayers", OdbcType.Int).Value = inGame.minPlayers;
commandObj.Parameters.Add("@maxPlayers", OdbcType.Int).Value = inGame.maxPlayers;
commandObj.Parameters.Add("@playingTime", OdbcType.VarChar, 127).Value = inGame.playingTime;    

return Convert.ToInt32(executeScaler(commandObj));

PS. I can change EF version if needed

Edit 1: 

CREATE DEFINER=`106228`@`%` PROCEDURE `updateGame`(
    inId INT,
    inName VARCHAR(255),
    inDescription TEXT,
    inYearPublished DATETIME,
    inMinPlayers INT,
    inMaxPlayers INT,
    inPlayingTime VARCHAR(127)
)

Upvotes: 38

Views: 151792

Answers (7)

Dib
Dib

Reputation: 2093

Based on your original request to be able to call a stored proc like this...

using (Entities context = new Entities())
{
    context.MyStoreadProcedure(Parameters); 
}

Mindless passenger has a project that allows you to call a stored proc from entity frame work like this....

using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}

... and I am working on a stored procedure framework (here) which you can call like in one of my test methods shown below...

[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
    [TestMethod]
    public void GetTenantForName_ReturnsOneRecord()
    {
        // ARRANGE
        const int expectedCount = 1;
        const string expectedName = "Me";

        // Build the paraemeters object
        var parameters = new GetTenantForTenantNameParameters
        {
            TenantName = expectedName
        };

        // get an instance of the stored procedure passing the parameters
        var procedure = new GetTenantForTenantNameProcedure(parameters);

        // Initialise the procedure name and schema from procedure attributes
        procedure.InitializeFromAttributes();

        // Add some tenants to context so we have something for the procedure to return!
        AddTenentsToContext(Context);

        // ACT
        // Get the results by calling the stored procedure from the context extention method 
        var results = Context.ExecuteStoredProcedure(procedure);

        // ASSERT
        Assert.AreEqual(expectedCount, results.Count);
    }
}

internal class GetTenantForTenantNameParameters
{
    [Name("TenantName")]
    [Size(100)]
    [ParameterDbType(SqlDbType.VarChar)]
    public string TenantName { get; set; }
}

[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
    : StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
    public GetTenantForTenantNameProcedure(
        GetTenantForTenantNameParameters parameters)
        : base(parameters)
    {
    }
}

Upvotes: 3

Hooman Bahreini
Hooman Bahreini

Reputation: 15569

This is an example of querying MySQL procedure using Entity Framework

This is the definition of my Stored Procedure in MySQL:

CREATE PROCEDURE GetUpdatedAds (
    IN curChangeTracker BIGINT
    IN PageSize INT
) 
BEGIN
   -- select some recored...
END;

And this is how I query it using Entity Framework:

 var curChangeTracker = new SqlParameter("@curChangeTracker", MySqlDbType.Int64).Value = 0;
 var pageSize = new SqlParameter("@PageSize", (MySqlDbType.Int64)).Value = 100;

 var res = _context.Database.SqlQuery<MyEntityType>($"call GetUpdatedAds({curChangeTracker}, {pageSize})");

Note that I am using C# String Interpolation to build my Query String.

Upvotes: 1

user2997069
user2997069

Reputation: 121

You have use the SqlQuery function and indicate the entity to mapping the result.

I send an example as to perform this:

var oficio= new SqlParameter
{
    ParameterName = "pOficio",
    Value = "0001"
};

using (var dc = new PCMContext())
{
    return dc.Database
             .SqlQuery<ProyectoReporte>("exec SP_GET_REPORTE @pOficio",
                                        oficio)
             .ToList();
}

Upvotes: 12

Dave
Dave

Reputation: 837

Basically you just have to map the procedure to the entity using Stored Procedure Mapping.

Once mapped, you use the regular method for adding an item in EF, and it will use your stored procedure instead.

Please see: This Link for a walkthrough. The result will be adding an entity like so (which will actually use your stored procedure)

using (var ctx = new SchoolDBEntities())
        {
            Student stud = new Student();
            stud.StudentName = "New sp student";
            stud.StandardId = 262;

            ctx.Students.Add(stud);
            ctx.SaveChanges();
        }

Upvotes: 2

ESG
ESG

Reputation: 9435

Once your stored procedure is imported in your model, you can right click in it (from the model browser, in the Context.Store/Stored Procedures section), and click Add Function Import. If you need a complex type as a result, you can create it right there.

Upvotes: 4

Quinton Bernhardt
Quinton Bernhardt

Reputation: 4803

One way is to use the Database property off the DbContext:

SqlParameter param1 = new SqlParameter("@firstName", "Frank");
SqlParameter  param2 = new SqlParameter("@lastName", "Borland");
context.Database.ExecuteSqlCommand("sp_MyStoredProc @firstName, @lastName", 
                              param1, param2);

EF5 definitely supports that.

Upvotes: 74

tam tam
tam tam

Reputation: 1900

This is what I recently did for my Data Visualization Application which has a 2008 SQL Database. In this example I am recieving a list returned from a stored procedure:

public List<CumulativeInstrumentsDataRow> GetCumulativeInstrumentLogs(RunLogFilter filter)
    {
        EFDbContext db = new EFDbContext();
        if (filter.SystemFullName == string.Empty)
        {
            filter.SystemFullName = null;
        }
        if (filter.Reconciled == null)
        {
            filter.Reconciled = 1;
        }
        string sql = GetRunLogFilterSQLString("[dbo].[rm_sp_GetCumulativeInstrumentLogs]", filter);
        return db.Database.SqlQuery<CumulativeInstrumentsDataRow>(sql).ToList();
    }

And then this extension method for some formatting in my case:

public string GetRunLogFilterSQLString(string procedureName, RunLogFilter filter)
        {
            return string.Format("EXEC {0} {1},{2}, {3}, {4}", procedureName, filter.SystemFullName == null ? "null" : "\'" + filter.SystemFullName + "\'", filter.MinimumDate == null ? "null" : "\'" + filter.MinimumDate.Value + "\'", filter.MaximumDate == null ? "null" : "\'" + filter.MaximumDate.Value + "\'", +filter.Reconciled == null ? "null" : "\'" + filter.Reconciled + "\'");

        }

Upvotes: 1

Related Questions