public static bool ExecuteSqlWithTransaction(ref string erreur, string Connect, List <SqlCommand> cmds)
{
ConnexionDB.MaConn = new SqlConnection(Connect);
ConnexionDB.connect();
transaction = MaConn.BeginTransaction("SampleTransaction");
try
{
//foreach (var sql in cm)
//{
// sqlCmd.CommandText = sql;
// sqlCmd.CommandType = CommandType.Text;
// sqlCmd.ExecuteNonQuery();
//}
foreach (var cmd in cmds)
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd = MaConn.CreateCommand();
sqlCmd.Transaction = transaction;
sqlCmd.CommandText = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters)
{
sqlCmd.Parameters.AddWithValue(p.ParameterName, p.Value);
}
sqlCmd.ExecuteNonQuery();
}
transaction.Commit();
ConnexionDB.deconnect();
return(true);
}
catch (Exception ex)
{
Console.WriteLine();
try
{
transaction.Rollback();
transaction.Dispose();
erreur = ex.Message;
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
ConnexionDB.deconnect();
return(false);
}
}
public static bool ExecuteSqlWithTransactionbluck(ref string erreur, string Connect, string table, DataRow[] rowArray)
{
ConnexionDB.MaConn = new SqlConnection(Connect);
ConnexionDB.connect();
transaction = MaConn.BeginTransaction("SampleTransaction");
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Connect))
{
bulkCopy.DestinationTableName = table;
//try
//{
// Write the array of rows to the destination.
bulkCopy.WriteToServer(rowArray);
//}
//catch (Exception ex)
//{
// Console.WriteLine(ex.Message);
//}
}
transaction.Commit();
ConnexionDB.deconnect();
return(true);
}
catch (Exception ex)
{
Console.WriteLine();
try
{
transaction.Rollback();
transaction.Dispose();
erreur = ex.Message;
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
ConnexionDB.deconnect();
return(false);
}
}
//public static bool ExecuteSqlWithTransaction(List<string> listSql, string Connect, ListBox ListLogTXT)
//{
// ConnexionDB.MaConn = new SqlConnection(Connect);
// ConnexionDB.connect();
// transaction = MaConn.BeginTransaction("SampleTransaction");
// SqlCommand sqlCmd = new SqlCommand();
// sqlCmd = MaConn.CreateCommand();
// sqlCmd.Transaction = transaction;
// try
// {
// foreach (var sql in listSql)
// {
// sqlCmd.CommandText = sql;
// sqlCmd.CommandType = CommandType.Text;
// sqlCmd.ExecuteNonQuery();
// }
// transaction.Commit();
// ConnexionDB.deconnect();
// return true;
// }
// catch (Exception ex)
// {
// Console.WriteLine();
// ListLogTXT.Items.Add("Erreur Dintégration !! "+ ex.Message );
// try
// {
// transaction.Rollback();
// transaction.Dispose();
// }
// catch (Exception ex2)
// {
// // This catch block will handle any errors that may have occurred
// // on the server that would cause the rollback to fail, such as
// // a closed connection.
// Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
// Console.WriteLine(" Message: {0}", ex2.Message);
// }
// ConnexionDB.deconnect();
// return false;
// }
//}
public static bool ExecuteSqlWithTransaction(List <string> listSql, string Connect)
{
ConnexionDB.MaConn = new SqlConnection(Connect);
ConnexionDB.connect();
transaction = MaConn.BeginTransaction("SampleTransaction");
SqlCommand sqlCmd = new SqlCommand();
sqlCmd = MaConn.CreateCommand();
sqlCmd.Transaction = transaction;
try
{
foreach (var sql in listSql)
{
sqlCmd.CommandText = sql;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.ExecuteNonQuery();
}
transaction.Commit();
ConnexionDB.deconnect();
return(true);
}
catch (Exception ex)
{
Console.WriteLine();
try
{
transaction.Rollback();
transaction.Dispose();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
ConnexionDB.deconnect();
return(false);
}
}