CODEWITHSUNDEEP
c#mysqlentity-framework-5prepared-statement
user1465073
user1465073

Reputation: 325

How to use prepared statement using entity framework with MYSQL DB?

I have been trying to incorporate prepared statements into my application which is using EF 5.0. Kindly look at my script below.

int parameterValue = 100000;
var addParameters = new List<MySqlParameter>();
var param1 = new MySqlParameter();
param1.Direction = System.Data.ParameterDirection.Input;
param1.Value = parameterValue;
param1.ParameterName = "user_id";
addParameters.Add(param1);
db.Database.ExecuteSqlCommand(@"delete FROM tbl_user where user_id = @user_id", addParameters);

it produces this error

Exception: {"Fatal error encountered during command execution."}
InnerException: {"Parameter '@user_id' must be defined."}

so based on InnerException description i changed the parameter nae to "@user_id"

int parameterValue = 100000;
var addParameters = new List<MySqlParameter>();
var param1 = new MySqlParameter();
param1.Direction = System.Data.ParameterDirection.Input;
param1.Value = parameterValue;
param1.ParameterName = "@user_id";
addParameters.Add(param1);
db.Database.ExecuteSqlCommand(@"delete FROM tbl_user where user_id = @user_id", addParameters);

but still the same error.

What could i be doing wrong?

Thanks in advance.

============================================== Additional info

<package id="EntityFramework" version="5.0.0" targetFramework="net40" />
<package id="MySql.Data" version="6.7.4" targetFramework="net40" />
<package id="MySQL.Data.Entities" version="6.7.4.1" targetFramework="net40" />

Upvotes: 1

Views: 6552

Answers (2)

WaveMax
WaveMax

Reputation: 210

Since you are using EF I think you should use something like this:

int parameterValue = 100000;
db.Database.ExecuteSqlCommand(@"delete FROM tbl_user where user_id = {0}", parameterValue);

Upvotes: 1

Dewfy
Dewfy

Reputation: 23624

Let me just show you good practice code that show what you want + recommendation on resource releasing:

using (SqlCommand comm = conn.CreateCommand()) //release good practice
{
    comm.CommandText = "delete FROM tbl_user where user_id = @user_id";
    comm.CommandType = CommandType.Text; //can be skipped for your case
    comm.Parameters.Add(new SqlParameter("@user_id", SqlDbType.Int)).Value =
        parameterValue;
    comm.ExecuteNonQuery();
}

Upvotes: 0

Related Questions