-1

I need use ExecuteNonQuery at those syntax's below? And they are correct? Objective use the da.DeleteCommand; which syntax's I gain performance? 

MySqlCommand cmd = new MySqlCommand("DELETE FROM users_login WHERE UserID = @UserID;", coon);
cmd.Parameters.Add("@UserID", MySqlDbType.Int16).Value = oUsuario.UserID;

MySqlDataAdapter da = new MySqlDataAdapter();
da.DeleteCommand = cmd;

coon.Open();
ok = true;

/**OR use this**/

MySqlCommand cmd = new MySqlCommand("DELETE FROM users_login WHERE UserID = @UserID;", coon);
cmd.Parameters.Add("@UserID", MySqlDbType.Int16).Value = oUsuario.UserID;

coon.Open();
cmd.ExecuteNonQuery();
ok = true;
Bruno Casali
  • 1,339
  • 2
  • 17
  • 32
  • You are asking if these commands are correct. Have you tried them? Are they working? – Andre Calil Aug 23 '13 at 13:04
  • ExecuteNonQuery should do it for delete also. Not sure about what delete command does. – lloydom Aug 23 '13 at 13:05
  • If you want to execute the query immediately use `ExecuteNonQuery`, if you want to modify all in-memory in a `DataTable/DataSet` before you send the changes to the database use the `DataAdapter`. – Tim Schmelter Aug 23 '13 at 13:08
  • I must have expressed myself badly I need this... If I use in a WebSite (Web Application), which sintaxes I gain performance? – Bruno Casali Aug 23 '13 at 13:13
  • @BrunoCasali: your question can not be answered. A DataAdapter can more than a Command alone. So if you need a DataAdapter use that, if you don't need it use the command. – Tim Schmelter Aug 23 '13 at 13:17

2 Answers2

2

Yes, if you're executing a DELETE statement, ExecuteNonQuery() on your MySqlCommand instance is what you want to use. It's also what you'd use if you're executing an INSERT or UPDATE statement or a stored procedure that doesn't return a resultset.

chris.house.00
  • 3,273
  • 1
  • 27
  • 36
  • executing `da.DeleteCommand = cmd;` ? I gain Performance OR has the same resultset? – Bruno Casali Aug 23 '13 at 13:15
  • DataAdapters are for working with a DataSet. If you're using a DataAdapter to fill, manipulate and post changes to a DataSet back to a database then it makes sense to use the DeleteCommand property on a DataAdapter instance. If you just want to execute an arbitrary DELETE statement against a database then just use ExecuteNonQuery on a Command instance. – chris.house.00 Aug 23 '13 at 13:20
  • Thanks, you resolved part of the problem, but i need now:And About performance? – Bruno Casali Aug 23 '13 at 13:25
  • What about performance? What specific concern about performance do you have? – chris.house.00 Aug 23 '13 at 13:27
  • Good, I discovered, two syntaxes to the same problem, i want know what are the syntaxes that I gain more performance? – Bruno Casali Aug 23 '13 at 13:31
  • On the surface it may seem like these are two solutions to the same problem but really they're not. DataAdapter is specifically for working with a DataSet and propogating changes to that DataSet back to a database. Command is for executing arbitrary SQL statements or stored procedures. It doesn't make sense to discuss the performance of one approach vs. the other because they handle two different scenarios. – chris.house.00 Aug 23 '13 at 13:34
1

In case you are using a nonquery but the delete won't work... you may need to use a transaction:

coon.Open();
var trans = coon.BeginTransaction();

var query = "DELETE FROM users_login WHERE UserID = @UserID;";
MySqlCommand cmd = new MySqlCommand(query, coon, trans);

cmd.Parameters.Add("@UserID", MySqlDbType.Int16).Value = oUsuario.UserID;
cmd.ExecuteNonQuery();

trans.Commit();
coon.Close();
J0XUE
  • 11
  • 1