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

Question

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" />

WaveMax · Accepted Answer · 2020-03-02 11:56:46Z

1

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

answered Mar 2, 2020 at 11:56

WaveMax

2306 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dewfy · Accepted Answer · 2013-08-10 17:01:09Z

0

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

answered Aug 10, 2013 at 17:01

Dewfy

23.8k13 gold badges78 silver badges126 bronze badges

6 Comments

user1465073 Over a year ago

but im using EF, shouldnt i be using db.Database.ExecuteSqlCommand?

Dewfy Over a year ago

Well, you can safely replace SqlCommand with DbCommand. But actually if you would look at class hierarchy(msdn.microsoft.com/en-us/library/…): then you can see that SqlCommand is inherited from DbCommand. If you deal with some real abstraction - then better use DbXXX, but you explicitly specify SQL. Keep in mind explicit better than implicit

user1465073 Over a year ago

i stumbled upon a blogpost saying that i should add this in the connection string which i did and finally i wasnt encountering the errors anymore Allow User Variables=True; now my problem is that there are no errors anymore but the records do not get deleted...

int parameterValue = 2; var addParameters = new List<MySqlParameter>(); var param1 = new MySqlParameter("@userid", parameterValue); addParameters.Add(param1); db.Database.ExecuteSqlCommand("delete FROM tbl_user where user_id = @userid", addParameters); db.SaveChanges();

no errors but no deletion occurs

user1465073 Over a year ago

doesnt work

int parameterValue = 2; var addParameters = new List<MySqlParameter>(); var param1 = new MySqlParameter("@userid", parameterValue); addParameters.Add(param1); db.Database.ExecuteSqlCommand("delete FROM tbl_user where user_id = {0}", addParameters); db.SaveChanges();

user1465073 Over a year ago

doesnt work also <br> <br>

int parameterValue = 2; var addParameters = new List<MySqlParameter>(); var param1 = new MySqlParameter("@userid", parameterValue); addParameters.Add(param1); db.Database.ExecuteSqlCommand("delete FROM tbl_user where user_id = {0}", addParameters); db.SaveChanges();

no

int parameterValue = 2; var param1 = new MySqlParameter("@userid", parameterValue); db.Database.ExecuteSqlCommand("delete FROM tbl_user where user_id = {0}", param1 ); db.SaveChanges();

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related