CODEWITHSUNDEEP
asp.netmysqlentity-frameworkstored-procedures
Pinakin Shah
Pinakin Shah

Reputation: 877

ASP.NET MVC EF Calling MySQL Stored Procedure with Out Parameters

I am using Entity Framework ExecuteStoreQuery to call a MySql stored procedure with output parameter. The store procedure also returns a result set.

I get the following error.

OUT or INOUT argument 2 for routine mydb.mysp is not a variable or NEW pseudo-variable in BEFORE trigger

What is wrong? Is there a better way to call MySql sp with mix of out params and resultset?

Here is the code

     var outParam = new MySqlParameter();
                outParam.ParameterName = "totalRowsOut";
                outParam.MySqlDbType = MySqlDbType.Int64;
                outParam.Direction = ParameterDirection.Output;

var data =  db.ExecuteStoreQuery<PropertySearchResultRow>
(
    "call mysp(@input1, @totalRowsOut);", 
    new MySqlParameter("input1", 10),
    outParam
);

var result = data.ToList();
int totalRowsCount = (int)outParam.Value;

Upvotes: 0

Views: 3612

Answers (3)

harold gonzales
harold gonzales

Reputation: 21

Here is my sample code working with VS2012 and Mysql connector 6.8.3 hope this helps someone

public static IList<MyClass> GetCustOrderHist(string someParameter)
    {
        IList<MyClass> data = ((IObjectContextAdapter)TestDashboardEntities).ObjectContext.ExecuteStoreQuery<MyClass>("CALL CustOrderHist({0});", someParameter).ToList();

        return data;
    }

    public class MyClass
    {
        public string ProductName { get; set; }
        public int TOTAL { get; set; }
    }

Upvotes: -1

Pinakin Shah
Pinakin Shah

Reputation: 877

Finally ended up using the ado.net MySqlCommand to call the stored proc and get the out value. Mysql and EF integration is bad.

Upvotes: 2

alwayslearning
alwayslearning

Reputation: 4633

You just need to specify the name of the stored procedure and pass in the parameters. Something like this

var data =  db.ExecuteStoreQuery<PropertySearchResultRow>
(
    "mysp",new MySqlParameter("input1", 10),outParam
);

Upvotes: 0

Related Questions