public static DataTable GetDataTable(SqlDetail sqlDetail)
{
SqlDetails sqlDetails = new SqlDetails();
sqlDetails.Add(sqlDetail);
return(GetDataTableByTransaction(sqlDetails));
}
public static bool ExecuteQuery(SqlDetail sqlDetail)
{
SqlDetails sqlDetails = new SqlDetails();
sqlDetails.Add(sqlDetail);
return(ExecuteTransaction(sqlDetails));
}
public static DataTable GetDataTableByTransaction(List <string> sqlList)
{
SqlDetails sqlDetails = new SqlDetails();
foreach (string sql in sqlList)
{
sqlDetails.Add(new SqlDetail(sql));
}
return(GetDataTableByTransaction(sqlDetails));
}
public static bool ExecuteTransaction(List <string> sqlList)
{
SqlDetails sqlDetails = new SqlDetails();
foreach (string sql in sqlList)
{
sqlDetails.Add(new SqlDetail(sql));
}
return(ExecuteTransaction(sqlDetails));
}
public static DataTable GetDataTableByTransaction(SqlDetails sqlDetails)
{
MySqlConnection connection = ConnectToSql();
DataTable resultDt = null;
if (connection == null)
{
return(resultDt);
}
MySqlTransaction transaction = connection.BeginTransaction();
resultDt = new DataTable();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = 300;
foreach (SqlDetail sqlDetail in sqlDetails)
{
cmd.CommandText = sqlDetail.Sql;
SqlParameters sqlParameters = sqlDetail.SqlParameters;
foreach (SqlParameter sqlParameter in sqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
if (sqlDetail.Sql.Trim('\r', '\n', ' ').IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) == 0)
{
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
da.Fill(resultDt);
}
}
else
{
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
connection.Close();
return(resultDt);
}
catch (Exception ex)
{
transaction.Rollback();
connection.Close();
LogsHelper.Print(MessagesVariable.FailedFetchDataInMySql + "\n" + ex.ToString());
return(resultDt);
}
}
public static bool ExecuteTransaction(SqlDetails sqlDetails)
{
MySqlConnection connection = ConnectToSql();
if (connection == null)
{
return(false);
}
MySqlTransaction transaction = connection.BeginTransaction();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
cmd.CommandTimeout = 300;
foreach (SqlDetail sqlDetail in sqlDetails)
{
cmd.CommandText = sqlDetail.Sql;
foreach (SqlParameter sqlParameter in sqlDetail.SqlParameters)
{
cmd.Parameters.AddWithValue(sqlParameter.Field, sqlParameter.Value);
}
cmd.ExecuteNonQuery();
}
transaction.Commit();
connection.Close();
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
connection.Close();
LogsHelper.Print(MessagesVariable.FailedExecuteQueryInMySql + "\n" + ex.ToString());
return(false);
}
}