jeudi 12 mars 2015

C# Mysql Transaction with many query

I have a Mysql class to make query like this.



protected MySqlConnection _connection = null;
public bool Connect()
{
try
{
this._connection = new MySqlConnection(this._connectionString);
this._connection.Open();
return true;
}
catch (MySqlException ex)
{
//throw new MySQLException(ex.Message, ex.Number);
return false;
}
}

public MySQLParameters Prepare(MySqlCommand command)
{
try
{
command.Connection = this._connection;
command.CommandType = CommandType.Text;
return new MySQLParameters(command);
}
catch (MySqlException ex)
{
throw new MySQLException(ex.Message, ex.Number);
}
}

public void Query(MySqlCommand command)
{
try
{
command.Connection = this._connection;
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
throw new MySQLException(ex.Message, ex.Number);
}
}


Now I want to use this with Transaction


But I don't know where to add the transaction commit and rollback


Because now I'm use my class like this.



RemoteMySQL mySql = new RemoteMySQL();
mySql.Connect();
string sql = "INSERT INTO `table1` SET ";
sql += "`name` = @name, ";
sql += "`lastname` = @lastname;";
MySQLParameters parameters = mySql.Prepare(sql);
parameters["name"] = name;
parameters["lastname"] = lastname;
mySql.Query(parameters.GetCommand());

sql = "INSERT INTO `table2` SET ";
sql += "`item` = @item, ";
sql += "`detail` = @detail;";
parameters = mySql.Prepare(sql);
parameters["item"] = item;
parameters["detail"] = detail;
mySql.Query(parameters.GetCommand());

// I want to commit here
// If can't insert to table2 will rollback table1 too.


I have a look at http://ift.tt/1C8TdbG (on trasaction)


But still don't know how to use with my code (where to put commit and rollback)


Thank you for help.


Aucun commentaire:

Enregistrer un commentaire