//带参数的执行命令
public static int ExecuteCommand(MySqlTransaction mySqlTransaction,string sql, params MySqlParameter[] values)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
connection.Open();
mySqlTransaction = connection.BeginTransaction();
MySqlCommand cmd = new MySqlCommand(sql, connection, mySqlTransaction);
cmd.Parameters.AddRange(values);
cmd.ExecuteNonQuery(); // ExecuteNonQuery()一般用于UPDATE、INSERT或DELETE语句，其中唯一的返回值是受影响的记录个数
mySqlTransaction.Commit();
return 1;
}
catch
{
mySqlTransaction.Rollback();
return 0;
}
finally
{
connection.Close();
}
}
}
public void RollBackTran()
{
if (tran != null)
{
tran.Rollback();
tran = null;
}
}
/// <summary>
/// 回滚事务
/// </summary>
public static void RollbackTransaction()
{
if (m_isTransaction != false)
{
trans.Rollback();
trans.Dispose();
con.Close();
con.Dispose();
m_isTransaction = false;
}
}
public string UpdateInventoryMySQL(string ma_phieu_nhap_kho, string ma_gian_hang, string username, string connectionString)
{
List <Merchant_StockInDetail> list = new List <Merchant_StockInDetail>();
using (var db = new OrmliteConnection().openConn(connectionString))
{
list = db.Select <Merchant_StockInDetail>("ma_phieu_nhap_kho = {0} and ma_gian_hang = {1} and trang_thai = 'Aproved'".Params(ma_phieu_nhap_kho, ma_gian_hang));
}
if (list.Count > 0)
{
using (MySqlConnection con = new MySqlConnection(AppConfigs.FEConnectionString))
{
con.Open();
using (MySql.Data.MySqlClient.MySqlTransaction trans = con.BeginTransaction())
{
try
{
using (MySqlCommand cmd = new MySqlCommand("", con, trans))
{
foreach (Merchant_StockInDetail item in list)
{
cmd.CommandText = "select * from Merchant_Inventory where ma_san_pham={0} and ma_gian_hang={1}".Params(item.ma_san_pham, item.ma_gian_hang);
var rs = cmd.ExecuteReader();
if (!rs.Read())
{
cmd.CommandText = @"insert into Merchant_Inventory(ma_gian_hang, ma_san_pham, so_luong_kho, nguoi_tao, so_luong_ban, ngay_tao, ngay_cap_nhat, nguoi_cap_nhat)
values({0},{1},{2},{3},0,NOW(),null,null)".Params(item.ma_gian_hang, item.ma_san_pham, item.so_luong_thuc_te, username);
}
else
{
cmd.CommandText = @"update Merchant_Inventory set so_luong_kho = so_luong_kho + {0}, ngay_cap_nhat = NOW(), nguoi_cap_nhat = {1} where ma_gian_hang = {2} and ma_san_pham = {3}".Params(item.so_luong_thuc_te, username, item.ma_gian_hang, item.ma_san_pham);
}
rs.Close();
cmd.ExecuteNonQuery();
}
}
trans.Commit();
return("true");
}
catch (Exception e)
{
trans.Rollback();
return(e.Message.ToString());
}
}
}
}
return("Không tìm thấy thông tin Phiếu Nhập Kho");
}
// Only for inserting to the database
public String SaveInObjects(IObjectBrowser[] mnrgObj)
{
if (mnrgObj.Length == 0)
{
return("Create objects...");
}
// Connection Obj
MySqlDbManager mngrSave = new MySqlDbManager();
mngrSave.GetConn().Open();
MySqlCommand comm = mngrSave.GetConn().CreateCommand();
// Transaction Obj from connection
MySql.Data.MySqlClient.MySqlTransaction tr =
mngrSave.GetConn().BeginTransaction();
// Pass the tr to the Command
comm.Transaction = tr;
try
{
for (int i = 0; i < mnrgObj.Length; i++)
{
comm.CommandType = CommandType.Text;
mngrSave.SetObject(mnrgObj[i]);
comm.CommandText = mngrSave.createInsertString(comm);
comm.ExecuteNonQuery();
}
tr.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException ee)
{
tr.Rollback();
return(ee.ToString());
}
finally
{
mngrSave.GetConn().Close();
}
return(OAuthDbCONST.DB_MESS_DONEMULTIOBJ);
}
public bool save(QuestionarioType questionario, int id_empresa)
{
try {
_con.Open();
_transaction = _con.BeginTransaction();
_delete(id_empresa, questionario.idQuestionario);
_insert(questionario);
_transaction.Commit();
}catch(Exception error){
_transaction.Rollback();
_con.Close();
throw error;
}finally{
_con.Close();
}
return true;
}
public Boolean TransaccionMysql(String query, MySqlParameterCollection parametros, System.Data.CommandType tipo_comando)
{
Boolean respuesta = false;
int f;
try{
transaccion = canal.BeginTransaction();
MySqlCommand comando = new MySqlCommand(query, canal);
comando.CommandType = tipo_comando;
if (parametros != null) {
foreach (var item in parametros){
comando.Parameters.Add(item);
}
}
comando.Transaction = transaccion;
f= comando.ExecuteNonQuery();
transaccion.Commit();
if (f>=0){
respuesta = true;
}
else{
respuesta = false;
}
}catch (Exception ex){
transaccion.Rollback();
MessageBox.Show("error sql:" + ex.Message);
respuesta = false;
}
return respuesta;
}
public void uploadImages(C1FlexGrid poGrid)
{
for (int i = 1; i < poGrid.Rows.Count; i++)
{
try
{
object[] _progressCount = new object[2];
char[] _separator = { ',' };
char[] _whiteSpaceSeparator = { ' ' };
//begin
loMySqlTransaction = GlobalVariables.goMySqlConnection.BeginTransaction();
string _imageId = "";
//para ni sa loading text -- saving image to db
_progressCount[0] = poGrid.GetDataDisplay(i, "Title");
_progressCount[1] = 1;
ImageUpload.GetType().GetMethod("reportProgress").Invoke(ImageUpload, _progressCount);
//^para sa loading text
loImage.Title = poGrid.GetDataDisplay(i, "Title");
loImage.Path = poGrid.GetDataDisplay(i, "Local Path");
loImage.PhotographerId = poGrid.GetDataDisplay(i, "Photographer ID");
loImage.Caption = poGrid.GetDataDisplay(i, "Caption");
loImage.Location = poGrid.GetDataDisplay(i, "Location");
loImage.DateTaken = DateTime.Parse(poGrid.GetDataDisplay(i, "Date Taken"));
loImage.Type = poGrid.GetDataDisplay(i, "Type");
_imageId = loImage.insert(ref loMySqlTransaction);
//para ni sa loading text -- saving tags to db
_progressCount[1] = 2;
ImageUpload.GetType().GetMethod("reportProgress").Invoke(ImageUpload, _progressCount);
//^para sa loading text
IList<string> _tagList = poGrid.GetDataDisplay(i, "Tags").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _tagList)
{
loImageTag.ImageId = _imageId;
loImageTag.Tag = _str.ToLower().Trim();
try
{
loImageTag.insert(ref loMySqlTransaction);
}
catch { }
}
//para ni sa loading text -- saving hidden tags to db
_progressCount[1] = 3;
ImageUpload.GetType().GetMethod("reportProgress").Invoke(ImageUpload, _progressCount);
//^para sa loading text
IList<string> _hiddenTagList = new List<string>();
//start with the dates
DateTime _dateTaken = DateTime.Parse(poGrid.GetDataDisplay(i, "Date Taken"));
_hiddenTagList.Add(_dateTaken.ToString("yyyy")); //year
_hiddenTagList.Add(_dateTaken.ToString("MMM")); //short month name
_hiddenTagList.Add(_dateTaken.ToString("MMMM")); //month name
_hiddenTagList.Add(_dateTaken.ToShortDateString());
_hiddenTagList.Add(_dateTaken.ToLongDateString());
_hiddenTagList.Add(_dateTaken.ToString("yyyy-MM-dd"));
_hiddenTagList.Add(_dateTaken.ToString("dd-MM-yy"));
//title
string[] _titleArr = loImage.Title.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _titleList = GlobalFunctions.removeRedundancy(_titleArr);
foreach (string _str in _titleList)
{
_hiddenTagList.Add(_str);
}
//photographer
string[] _photographerArr = poGrid.GetDataDisplay(i, "Photographer").Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _photographerList = GlobalFunctions.removeRedundancy(_photographerArr);
foreach (string _str in _photographerList)
{
_hiddenTagList.Add(_str);
}
//caption
string[] _captionArr = loImage.Caption.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _captionList = GlobalFunctions.removeRedundancy(_captionArr);
foreach (string _str in _captionList)
{
_hiddenTagList.Add(_str);
}
//location
string[] _locationArr = loImage.Location.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _locationList = GlobalFunctions.removeRedundancy(_locationArr);
foreach (string _str in _captionList)
{
_hiddenTagList.Add(_str);
}
//remove repetitive entries
IList<string> _noRepeatHiddenTags = GlobalFunctions.removeRedundancy(_hiddenTagList.ToArray<string>());
//save to database like a baws
foreach (string _str in _noRepeatHiddenTags)
{
if (_str != "" || _str != null)
{
loImageTag.Tag = _str.ToLower().Trim();
loImageTag.ImageId = _imageId;
try
{
loImageTag.insert(ref loMySqlTransaction);
}
catch { }
}
}
loMySqlTransaction.Commit();
//File.Copy(@"" + poGrid.GetDataDisplay(i, "Local Path"), @"" + GlobalVariables.goImageServer + @"\" + _imageId + loImage.Type);
//para ni sa loading text -- copying image to server
_progressCount[1] = 4;
ImageUpload.GetType().GetMethod("reportProgress").Invoke(ImageUpload, _progressCount);
//^para sa loading text
Stream buffer;
StreamReader srImage = new StreamReader(poGrid.GetDataDisplay(i, "Local Path"));
buffer = srImage.BaseStream;
Bitmap bmImage = new Bitmap(buffer);
bmImage.Save(GlobalVariables.goImageServer + @"\" + _imageId + loImage.Type);
//para ni sa loading text -- done
_progressCount[1] = 5;
ImageUpload.GetType().GetMethod("reportProgress").Invoke(ImageUpload, _progressCount);
//^para sa loading text
}
catch (Exception ex)
{
loMySqlTransaction.Rollback();
throw;
}
}
}
private void enviarButton_Click(object sender, EventArgs e)
{
if (myconn != null)
{
verificaConnMysql();
}
else
{
MessageBox.Show("Não foi possível sincronizar, sem conexão.");
return;
}
//login web
MysqlLoginDialog mld = new MysqlLoginDialog(myconn);
mld.ShowDialog();
if (!Usuario.Logado)
{
return;
}
//VERIFICA CONEXAO
verificaConnMysql();
//INSERE TRAFOS
if (checkTodos.Checked)
{
trafoTableAdapter.Fill(citeluzDataSet.trafo);
}
else
{
DataRowView row =
(DataRowView)trafoBindingSource.Current;
trafoTableAdapter.FillBy(citeluzDataSet.trafo, (string)row["cod_trafo"]);
}
//verifica duplicidade
string duplicidade = "SELECT plaqueta_trafo, nome_lote FROM trafo " +
"WHERE plaqueta_trafo IN (";
string dupli_plaqueta = "SELECT plaqueta_id, nome_lote FROM plaqueta " +
"WHERE plaqueta_id IN (";
string dtmp = "";
foreach (DataRow row in citeluzDataSet.trafo.Rows)
{
dtmp = dtmp + "'" + row["plaqueta_trafo"] + "',";
}
dtmp = dtmp.Remove(dtmp.Length - 1, 1); //remove a virgula no final
duplicidade = duplicidade + dtmp + ") ";
duplicidade = duplicidade + " AND nome_lote = '" + txtNomeLote.Text + "'";
dtmp = "";
foreach (DataRow row in citeluzDataSet.plaqueta.Rows)
{
dtmp = dtmp + "'" + row["plaqueta_id"] + "',";
}
dtmp = dtmp.Remove(dtmp.Length - 1, 1); //remove a virgula no final
dupli_plaqueta = dupli_plaqueta + dtmp + ") ";
dupli_plaqueta = dupli_plaqueta + " AND nome_lote = '" + txtNomeLote.Text + "'";
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
//GERAR LOG DOS DUPLICADOS PARA FACILITAR CONFERENCIA
Directory.CreateDirectory(Library.appDir + "\\log");
Application.DoEvents();
string logPath =
Library.appDir + "\\log\\dupli_" + txtNomeLote.Text + ".txt";
TextWriter id = new StreamWriter(logPath);
id.WriteLine("Duplicidades em Trafos:");
id.WriteLine("----------------------------------");
mycommand = myconn.CreateCommand();
mycommand.Connection = myconn;
mycommand.CommandText = duplicidade;
MySqlDataReader mydr = mycommand.ExecuteReader();
while (mydr.Read())
{
string trafo = mydr.GetString("plaqueta_trafo");
listBox1.Items.Add("Duplicidade "+trafo);
id.WriteLine(trafo);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
mydr.Close();
mycommand.CommandText = dupli_plaqueta;
MySqlDataReader mydr_plaqueta = mycommand.ExecuteReader();
id.WriteLine(" ");
id.WriteLine("Duplicidades em Plaquetas:");
id.WriteLine("----------------------------------");
while (mydr_plaqueta.Read())
{
string plaqueta = mydr_plaqueta.GetString("plaqueta_id");
listBox1.Items.Add("Duplicidade " + plaqueta);
id.WriteLine(plaqueta);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
id.Flush();
id.Close();
if (mydr.HasRows || mydr_plaqueta.HasRows)
{
MessageBox.Show("Há duplicidades no arquivo que esta sendo lançado. Duplicidades salvas em \\log\\dupli_lote.txt");
myconn.Close();
return;
}
myconn.Close();
Application.DoEvents();
myconn.Open();
mycommand.Transaction = mytrans;
mytrans = myconn.BeginTransaction();
//insere trafos
foreach (DataRow row in citeluzDataSet.trafo.Rows)
{
insert = "INSERT INTO trafo (";
foreach (DataColumn column in citeluzDataSet.trafo.Columns)
{
if (column.ColumnName.Equals("user"))
{
insert = insert + "usuario_fk,";
}
else
{
insert = insert + column.ColumnName + ",";
}
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.trafo.Columns)
{
object valor = row[column.ColumnName];
if (column.ColumnName.Equals("plaqueta_trafo"))
{
codTrafo = (string)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
insert = insert + valor + ",";
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
//insere trafos
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Projeto " + codTrafo + " enviado");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
//INSERE PLAQUETAS
this.plaquetaTableAdapter.Fill(citeluzDataSet.plaqueta, codTrafo);
foreach (DataRow rowPlaqueta in citeluzDataSet.plaqueta.Rows)
{
insert = "INSERT INTO plaqueta (";
foreach (DataColumn column in citeluzDataSet.plaqueta.Columns)
{
insert = insert + column.ColumnName + ",";
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.plaqueta.Columns)
{
object valor = rowPlaqueta[column.ColumnName];
if (column.ColumnName.Equals("plaqueta_id"))
{
codPlaqueta = (string)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
insert = insert + valor + ",";
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Plaqueta " + codPlaqueta + " enviada");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
//insere MATERIAL
this.materialTableAdapter.Fill(citeluzDataSet.material, codPlaqueta);
foreach (DataRow rowMaterial in citeluzDataSet.material.Rows)
{
insert = "INSERT INTO material (";
foreach (DataColumn column in citeluzDataSet.material.Columns)
{
if (!column.ColumnName.Equals("id"))
{
insert = insert + column.ColumnName + ",";
}
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.material.Columns)
{
object valor = rowMaterial[column.ColumnName];
if (column.ColumnName.Equals("id"))
{
codMaterial = (int)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
if (!column.ColumnName.Equals("id"))
{
insert = insert + valor + ",";
}
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Material " + codMaterial + " enviada");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
mytrans.Commit();
myconn.Close();
listBox1.Items.Add("Envio completo.");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Cursor.Current = Cursors.Default;
Application.DoEvents();
}
/// <summary>
/// Do this in a transaction, and handle all transaction and connection issues that might occur
/// </summary>
/// <param name="act">What to do...</param>
/// <param name="isolationLevel">The isolation level to use</param>
private void DoTransaction(Action act, IsolationLevel isolationLevel = IsolationLevel.Serializable)
{
MySqlConnection conn = Connection;
try
{
RetryUtility.RetryAction(() =>
{
try
{
_transaction = conn.BeginTransaction(isolationLevel);
act();
_transaction.Commit();
}
catch (Exception)
{
try
{
_transaction.Rollback();
}
catch (Exception)
{
_connection = null;
//If we can't rollback, clear the connection; something is very wrong...
//SKIPPEDTODO: Make a logging function and maybe a security alert...
}
throw;
}
}, 3, 700);
}
catch (Exception ex)
{
throw new Exception("Something unexpected went wrong. The error has been logged. Please try again.", ex);
}
_transaction = null;
}
public string ChangeStatus(string ma_don_hang, string trang_thai_don_hang, string Username, string connectstring)
{
using (var dbConn = new OrmliteConnection().openConn(connectstring))
{
{
try
{
var exist = dbConn.FirstOrDefault <Merchant_OrderHeader>("ma_don_hang={0}".Params(ma_don_hang));
if (exist != null)
{
if (exist.trang_thai_don_hang == "New" && trang_thai_don_hang == "Confirm")
{
exist.trang_thai_don_hang = "Confirm";
}
if (exist.trang_thai_don_hang == "Confirm" && trang_thai_don_hang == "Shipping")
{
exist.trang_thai_don_hang = "Shipping";
}
if (exist.trang_thai_don_hang == "Shipping" && trang_thai_don_hang == "POD")
{
exist.trang_thai_don_hang = "POD";
}
if (exist.trang_thai_don_hang == "POD" && trang_thai_don_hang == "Completed")
{
exist.trang_thai_don_hang = "Completed";
}
exist.ngay_cap_nhat = DateTime.Now;
exist.nguoi_cap_nhat = Username;
dbConn.UpdateOnly(exist,
onlyFields: p =>
new
{
p.trang_thai_don_hang,
p.nguoi_cap_nhat,
p.ngay_cap_nhat
},
where : p => p.ma_don_hang == exist.ma_don_hang);
if (exist.trang_thai_don_hang == "Confirm")
{
List <SqlParameter> param = new List <SqlParameter>();
//Update Merchant_Product_Warehouse
param.Add(new SqlParameter("@ma_don_hang", exist.ma_don_hang));
new SqlHelper(connectstring).ExecuteQuery("p_Update_Merchant_Product_Warehouse_Order", param);
}
using (MySqlConnection con = new MySqlConnection(AppConfigs.FEConnectionString))
{
con.Open();
string sqlOrder = @"update order_merchant set trang_thai_don_hang={0}, ngay_cap_nhat={1}, nguoi_cap_nhat={2} where ma_don_hang={3} and ma_gian_hang={4}".Params(exist.trang_thai_don_hang, exist.ngay_cap_nhat, exist.nguoi_cap_nhat, exist.ma_don_hang_cha, exist.ma_gian_hang);
using (MySql.Data.MySqlClient.MySqlTransaction trans = con.BeginTransaction())
{
try
{
using (MySqlCommand cmd = new MySqlCommand(sqlOrder, con, trans))
{
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
return(e.Message.ToString());
}
}
}
}
}
catch (Exception e)
{
return("false@@" + e.Message);
}
}
}
return("true@@" + trang_thai_don_hang);
}
public void RollbackTransaction(MySqlTransaction trans, bool closeConnection)
{
trans.Rollback();
if (closeConnection && trans.Connection.State != ConnectionState.Closed)
{
trans.Connection.Close();
}
trans = null;
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
MySqlClient.MySqlConnection myConnection = null;
MySqlClient.MySqlTransaction myTransaction = null;
try
{
// Create a new connection.
using (myConnection = new MySqlClient.MySqlConnection(connectionString))
{
// Open the connection.
myConnection.Open();
// Start a new transaction.
myTransaction = myConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new MySqlClient.MySqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.MySqlDataType, commandText), myConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = myTransaction;
if (values != null)
{
foreach (MySqlClient.MySqlParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
myTransaction.Commit();
// Close the database connection.
myConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (myTransaction != null)
{
myTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (myConnection != null)
{
myConnection.Close();
}
}
}
//recupera el máximo del campo seleccionado
public override Object getMax(String table, String field)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
Object result = null;
try
{
String query = "select max(" + field + ") from " + table + ";";
command.CommandText = query;
command.Prepare();
MySqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
result = dataReader.GetValue(0);
}
if (result == System.DBNull.Value)
{
result = null;
}
dataReader.Close();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while recovering the data.");
}
finally
{
conn.Close();
}
return(result);
}
public void uploadFiles(C1FlexGrid poGrid)
{
for (int i = 1; i < poGrid.Rows.Count; i++)
{
try
{
object[] _progressCount = new object[2];
_progressCount[0] = poGrid.GetDataDisplay(i, "Title");
//update database
loMySqlTransaction = GlobalVariables.goMySqlConnection.BeginTransaction();
//para ni sa loading text -- file to db
_progressCount[1] = 1;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
loDocument.Title = poGrid.GetDataDisplay(i, "Title");
loDocument.Path = GlobalVariables.goFileServer;
loDocument.Newspaper = poGrid.GetDataDisplay(i, "Newspaper");
loDocument.Doctype = poGrid.GetDataDisplay(i, "Doc Type");
loDocument.Section = poGrid.GetDataDisplay(i, "Section");
loDocument.PublishedDate = poGrid.GetDataDisplay(i, "Published Date");
//generate preview
string _preview = "";
switch (loDocument.Doctype)
{
case ".rtf":
case ".doc":
//add preview
try
{
object readOnly = false;
object isVisible = false;
object _missing = System.Reflection.Missing.Value;
_oApplication = new Microsoft.Office.Interop.Word.Application();
//_oDocument = _oApplication.Documents.Open(poGrid.GetDataDisplay(i, "Local Path"));
object _path = @"" + poGrid.GetDataDisplay(i, "Local Path").ToString();
_oDocument = _oApplication.Documents.Open(ref _path, ref _missing,
ref readOnly, ref _missing, ref _missing, ref _missing,
ref _missing, ref _missing, ref _missing, ref _missing,
ref _missing, ref isVisible, ref _missing, ref _missing,
ref _missing, ref _missing);
_preview = GlobalFunctions.addSlashes(_oDocument.Content.Text);
}
catch (Exception ex)
{
throw ex;
}
finally
{
_oDocument.Close();
_oApplication.Quit();
}
break;
case ".txt":
//add preview
string _textPreview = File.ReadAllText(poGrid.GetDataDisplay(i, "Local Path"));
_preview = GlobalFunctions.addSlashes(_textPreview);
break;
}
loDocument.Preview = _preview;
string _docId = loDocument.insert(ref loMySqlTransaction);
char[] _separator = { ',' };
//para ni sa loading text -- authors
_progressCount[1] = 2;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
//add authors
string[] _authors = poGrid.GetDataDisplay(i, "Authors").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _authors)
{
loDocAuthor.AuthorId = _str;
loDocAuthor.DocumentId = _docId;
loDocAuthor.insert(ref loMySqlTransaction);
}
//para ni sa loading text -- editors
_progressCount[1] = 3;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
//add editors
string[] _editors = poGrid.GetDataDisplay(i, "Editors").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _editors)
{
loDocEditor.EditorId = _str;
loDocEditor.DocumentId = _docId;
loDocEditor.insert(ref loMySqlTransaction);
}
//para ni sa loading text -- tags
_progressCount[1] = 4;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
//add tags
string[] _tags = poGrid.GetDataDisplay(i, "Tags").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _tagList = GlobalFunctions.removeRedundancy(_tags);
foreach (string _str in _tagList)
{
loDocTag.Tag = _str.ToLower().Trim();
loDocTag.DocumentId = _docId;
try
{
loDocTag.insert(ref loMySqlTransaction);
}
catch { }
}
//para ni sa loading text -- hidden tags
_progressCount[1] = 5;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
//add hidden tags
IList<string> _hiddenTagList = new List<string>();
//start with the dates
DateTime _publishedDate = DateTime.Parse(poGrid.GetDataDisplay(i, "Published Date"));
_hiddenTagList.Add(_publishedDate.ToString("yyyy")); //year
_hiddenTagList.Add(_publishedDate.ToString("MMM")); //short month name
_hiddenTagList.Add(_publishedDate.ToString("MMMM")); //month name
_hiddenTagList.Add(_publishedDate.ToShortDateString());
_hiddenTagList.Add(_publishedDate.ToLongDateString());
_hiddenTagList.Add(_publishedDate.ToString("yyyy-MM-dd"));
_hiddenTagList.Add(_publishedDate.ToString("dd-MM-yy"));
//title
char[] _whiteSpaceSeparator = { ' ' };
string[] _splitTitle = loDocument.Title.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _splitTitle)
{
_hiddenTagList.Add(_str);
}
//authors
string[] _authorNames = poGrid.GetDataDisplay(i, "Names").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _authorNames)
{
string[] _nameSplit = _str.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str2 in _nameSplit)
{
_hiddenTagList.Add(_str2);
}
}
//editors
string[] _editorInitials = poGrid.GetDataDisplay(i, "Initials").Split(_separator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str in _editorInitials)
{
string[] _initialSplit = _str.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
foreach (string _str2 in _initialSplit)
{
_hiddenTagList.Add(_str2);
}
}
//also add the newspaper and section as tags
_hiddenTagList.Add(loDocument.Newspaper);
_hiddenTagList.Add(poGrid.GetDataDisplay(i, "Section Name"));
//remove repetitive entries
IList<string> _noRepeatHiddenTags = GlobalFunctions.removeRedundancy(_hiddenTagList.ToArray<string>());
//save to database like a baws
foreach (string _str in _noRepeatHiddenTags)
{
if (_str != "" || _str != null)
{
loDocTag.Tag = _str.ToLower().Trim();
loDocTag.DocumentId = _docId;
try
{
loDocTag.insert(ref loMySqlTransaction);
}
catch { }
}
}
//para ni sa loading text -- first 100 words
_progressCount[1] = 6;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
//get the document contents, but check for it's doctype first though!
switch (loDocument.Doctype)
{
case ".rtf":
case ".doc":
string _docContents = "";
_oApplication = new Microsoft.Office.Interop.Word.Application();
_oDocument = _oApplication.Documents.Open(poGrid.GetDataDisplay(i, "Local Path"));
//loop throught the first 100 words
int _numberOfWords = 0;
if (_oDocument.Words.Count >= 100)
{
_numberOfWords = 100;
}
else
{
_numberOfWords = _oDocument.Words.Count;
}
for (int _wordCount = 1; _wordCount <= _numberOfWords; _wordCount++)
{
_docContents += _oDocument.Words[_wordCount].Text;
}
string[] _docContentsArr = _docContents.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _docContentsList = GlobalFunctions.removeRedundancy(_docContentsArr);
foreach (string _str in _docContentsList)
{
if (_str.Length > 1)
{
loDocTag.Tag = _str.ToLower().Trim();
loDocTag.DocumentId = _docId;
try
{
loDocTag.insert(ref loMySqlTransaction);
}
catch { }
}
}
break;
case ".txt":
string _textContents = File.ReadAllText(poGrid.GetDataDisplay(i, "Local Path"));
string[] _textContentsArr = _textContents.Split(_whiteSpaceSeparator, StringSplitOptions.RemoveEmptyEntries);
IList<string> _textContentsList = GlobalFunctions.removeRedundancy(_textContentsArr);
foreach (string _str in _textContentsList)
{
if (_str.Length > 1)
{
loDocTag.Tag = _str.ToLower().Trim();
loDocTag.DocumentId = _docId;
try
{
loDocTag.insert(ref loMySqlTransaction);
}
catch { }
}
}
break;
}
//maypa ang mysqltransaction, mu commit!
loMySqlTransaction.Commit();
//copy file to server
File.Copy(@"" + poGrid.GetDataDisplay(i, "Local Path"), @"" + GlobalVariables.goFileServer + @"\" + _docId + loDocument.Doctype);
//para ni sa loading text -- first 100 words
_progressCount[1] = 7;
FileUpload.GetType().GetMethod("reportProgress").Invoke(FileUpload, _progressCount);
//^para sa loading text
}
catch (Exception)
{
loMySqlTransaction.Rollback();
throw;
}
finally
{
if (_oDocument != null)
{
_oDocument.Close();
_oApplication.Quit();
}
GC.Collect();
}
}
}
//recupera las claves ajenas de uno a uno y su valor
public override List <Tuple <String, Object> > getForeignKeysFromOneToOne(String table, List <Tuple <String, Object> > pks, List <String> relationFields)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
List <Tuple <String, Object> > results = new List <Tuple <String, Object> >();
try
{
String query = "select ";
for (int i = 0; i < relationFields.Count - 1; i++)
{
query += relationFields[i] + ",";
}
query += relationFields[relationFields.Count - 1];
query += " from " + table + " where ";
for (int i = 0; i < pks.Count; i++)
{
query += pks[i].Item1 + " = @" + pks[i].Item1;
if (i != pks.Count - 1)
{
query += " and ";
}
}
query += ";";
command.CommandText = query;
command.Prepare();
for (int i = 0; i < pks.Count; i++)
{
command.Parameters.AddWithValue("@" + pks[i].Item1, pks[i].Item2);
}
MySqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
for (int i = 0; i < dataReader.FieldCount; i++)
{
if (dataReader.GetValue(i) is System.DBNull)
{
results.Add(new Tuple <String, Object>(dataReader.GetName(i), null));
}
else
{
results.Add(new Tuple <String, Object>(dataReader.GetName(i), dataReader.GetValue(i)));
}
}
dataReader.Close();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while recovering the data.");
}
finally
{
conn.Close();
}
return(results);
}
//recupera todos los resultados cuyos campos coinciden con los proporcionados
public override List <List <Tuple <String, Object> > > getWhere(String table, List <Tuple <String, Object> > fields)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
List <List <Tuple <String, Object> > > results = new List <List <Tuple <String, Object> > >();
try
{
String query = "select * from " + table;
if (fields != null && fields.Count != 0)
{
query += " where ";
for (int i = 0; i < fields.Count; i++)
{
query += fields[i].Item1 + " = @" + fields[i].Item1;
if (i != fields.Count - 1)
{
query += " and ";
}
}
}
query += ";";
command.CommandText = query;
command.Prepare();
if (fields != null && fields.Count != 0)
{
for (int i = 0; i < fields.Count; i++)
{
command.Parameters.AddWithValue("@" + fields[i].Item1, fields[i].Item2);
}
}
MySqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
List <Tuple <String, Object> > result = new List <Tuple <String, Object> >();
for (int i = 0; i < dataReader.FieldCount; i++)
{
result.Add(new Tuple <String, Object>(dataReader.GetName(i), dataReader.GetValue(i)));
}
results.Add(result);
}
dataReader.Close();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while recovering the data.");
}
finally
{
conn.Close();
}
return(results);
}
//recupera de la base de datos un solo campo
public override Object getField(String table, List <Tuple <String, Object> > pks, String selectedField)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
Object result;
try
{
String query = "select " + selectedField + " from " + table + " where ";
for (int i = 0; i < pks.Count; i++)
{
query += pks[i].Item1 + " = @" + pks[i].Item1;
if (i != pks.Count - 1)
{
query += " and ";
}
}
query += ";";
command.CommandText = query;
command.Prepare();
for (int i = 0; i < pks.Count; i++)
{
command.Parameters.AddWithValue("@" + pks[i].Item1, pks[i].Item2);
}
MySqlDataReader dataReader = command.ExecuteReader();
dataReader.Read();
result = dataReader.GetValue(0);
dataReader.Close();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while recovering the data.");
}
finally
{
conn.Close();
}
return(result);
}
//Actualiza en la base de datos
public override void update(String table, List <Tuple <String, Object> > pks, List <Tuple <String, Object> > fields)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
try
{
String query = "Update " + table + " Set ";
for (int i = 0; i < fields.Count; i++)
{
query += fields[i].Item1 + " = @" + fields[i].Item1;
if (i != fields.Count - 1)
{
query += " , ";
}
}
query += " where ";
for (int i = 0; i < pks.Count; i++)
{
query += pks[i].Item1 + " = @" + pks[i].Item1;
if (i != pks.Count - 1)
{
query += " and ";
}
}
query += ";";
command.CommandText = query;
command.Prepare();
for (int i = 0; i < fields.Count; i++)
{
command.Parameters.AddWithValue("@" + fields[i].Item1, fields[i].Item2);
}
for (int i = 0; i < pks.Count; i++)
{
command.Parameters.AddWithValue("@" + pks[i].Item1, pks[i].Item2);
}
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while updating the data.");
}
finally
{
conn.Close();
}
}
//Graba en la base de datos
public override void save(String table, List <Tuple <String, Object> > pks, List <Tuple <String, Object> > fields, List <Tuple <String, Object> > foreignKeys)
{
MySqlConnection conn = new MySqlConnection(Db.getConnectionString());
conn.Open();
MySqlCommand command = conn.CreateCommand();
MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = transaction;
//Create command string
List <String> columns = new List <String>();
List <Object> values = new List <Object>();
foreach (Tuple <String, Object> pair in pks)
{
columns.Add(pair.Item1);
values.Add(pair.Item2);
}
if (fields != null)
{
foreach (Tuple <String, Object> pair in fields)
{
columns.Add(pair.Item1);
values.Add(pair.Item2);
}
}
if (foreignKeys != null)
{
foreach (Tuple <String, Object> pair in foreignKeys)
{
columns.Add(pair.Item1);
values.Add(pair.Item2);
}
}
try
{
String query = "Insert into " + table + " (";
for (int i = 0; i < columns.Count; i++)
{
query += columns[i];
if (i != columns.Count - 1)
{
query += ", ";
}
}
query += ") ";
query += "Values (";
for (int i = 0; i < columns.Count; i++)
{
query += "@" + columns[i];
if (i != columns.Count - 1)
{
query += ", ";
}
}
query += ") ";
query += ";";
command.CommandText = query;
command.Prepare();
for (int i = 0; i < columns.Count; i++)
{
command.Parameters.AddWithValue("@" + columns[i], values[i]);
}
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception e)
{
try
{
transaction.Rollback();
}
catch (Exception ex)
{
if (transaction.Connection != null)
{
throw new GeneralORMException("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
throw new GeneralORMException("An exception of type " + e.GetType() +
" was encountered while inserting the data. Neither record was written to database.");
}
finally
{
conn.Close();
}
}
void IPromotableSinglePhaseNotification.Rollback(SinglePhaseEnlistment singlePhaseEnlistment)
{
simpleTransaction.Rollback();
singlePhaseEnlistment.Aborted();
connection.CurrentTransaction = null;
}
private void enviarDados_click(object sender, EventArgs e)
{
if (myconn != null)
{
if (myconn.State == ConnectionState.Closed)
{
myconn.Open();
}
}
else
{
MessageBox.Show("Não foi possível sincronizar, sem conexão.");
return;
}
if (checkTodos.Checked)
{
projetoTableAdapter.Fill(lptDataSet.projeto);
}
else
{
DataRowView row =
(DataRowView)projetoBindingSource.Current;
projetoTableAdapter.FillById(lptDataSet.projeto, (int)row["id"]);
}
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
mycommand = myconn.CreateCommand();
mytrans = myconn.BeginTransaction();
mycommand.Connection = myconn;
mycommand.Transaction = mytrans;
foreach (DataRow row in lptDataSet.projeto.Rows)
{
insert = "INSERT INTO projeto (";
foreach (DataColumn column in lptDataSet.projeto.Columns)
{
if (!column.ColumnName.Equals("id"))
{
insert = insert + column.ColumnName + ",";
}
}
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in lptDataSet.projeto.Columns)
{
if (column.ColumnName.Equals("id"))
{
idLocal = Convert.ToInt32(row[column.ColumnName]);
}
else
{
object valor = row[column.ColumnName];
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
insert = insert + valor + ",";
}
}
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
//insere
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Projeto " + idLocal + " enviado");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
mycommand =
new MySqlCommand("SELECT @@IDENTITY", myconn);
object id_ = mycommand.ExecuteScalar();
idMysql = Convert.ToInt32(id_);
insert = "";
insereClientesByProjetoId(idLocal, idMysql);
insereFaixaByProjetoId(idLocal, idMysql);
insereSangriaByProjetoId(idLocal, idMysql);
insereSignificativoByProjetoId(idLocal, idMysql);
insereMedidorByProjetoId(idLocal, idMysql);
}
catch (Exception ex)
{
mytrans.Rollback();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
projetoTableAdapter.Fill(lptDataSet.projeto);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
mytrans.Commit();
MessageBox.Show("Dados enviados");
projetoTableAdapter.Fill(lptDataSet.projeto);
myconn.Close();
Cursor.Current = Cursors.Default;
Application.DoEvents();
}
/// <summary>
/// 返回执行响应的行数
/// </summary>
/// <param name="trans">command中执行的事物（MySqlTransaction）</param>
/// <param name="cmdType">执行类型（过程/语句）</param>
/// <param name="cmdText">执行的过程名称/查询语句</param>
/// <param name="commandParameters">command执行的MySqlParameter[]数组</param>
/// <returns>int 返回command执行响应的行数</returns>
public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
int val = 0;
MySqlCommand cmd = new MySqlCommand();
try
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Close(trans.Connection);
//Dispose(trans.Connection);
trans.Commit();
return val;
}
catch
{
trans.Rollback();
return val;
}
}
public void Rollback()
{
transaction.Rollback();
}
/// <summary>
/// 回滚事务
/// </summary>
public override void RollbackTransaction()
{
transaction.Rollback();
this.IsTransaction = false;
}
public string Cancel(string ma_don_hang, string ly_do_huy, string Username, string connectstring)
{
using (var dbConn = new OrmliteConnection().openConn(connectstring))
{
{
try
{
var exist = dbConn.FirstOrDefault <Merchant_OrderHeader>("ma_don_hang={0}".Params(ma_don_hang));
if (exist != null)
{
if (exist.trang_thai_don_hang == "New" || exist.trang_thai_don_hang == "Confirm" || exist.trang_thai_don_hang == "Shipping")
{
exist.trang_thai_don_hang = "Cancel";
exist.ly_do_huy = ly_do_huy;
}
else
{
return("false@@" + "Không thể hủy");
}
exist.ngay_cap_nhat = DateTime.Now;
exist.nguoi_cap_nhat = Username;
dbConn.UpdateOnly(exist,
onlyFields: p =>
new
{
p.trang_thai_don_hang,
p.ly_do_huy,
p.nguoi_cap_nhat,
p.ngay_cap_nhat
},
where : p => p.ma_don_hang == exist.ma_don_hang);
using (MySqlConnection con = new MySqlConnection(AppConfigs.FEConnectionString))
{
con.Open();
string sqlOrder = @"update order_merchant set trang_thai_don_hang={0} ngay_cap_nhat={1} nguoi_cap_nhat={2} where ma_don_hang={3} and ma_gian_hang={4}".Params(exist.trang_thai_don_hang, exist.ngay_cap_nhat, exist.nguoi_cap_nhat, exist.ma_don_hang_cha, exist.ma_gian_hang);
using (MySql.Data.MySqlClient.MySqlTransaction trans = con.BeginTransaction())
{
try
{
using (MySqlCommand cmd = new MySqlCommand(sqlOrder, con, trans))
{
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
return(e.Message.ToString());
}
}
}
}
}
catch (Exception e)
{
return("false@@" + e.Message);
}
}
}
return("true@@" + ly_do_huy);
}
internal void CloseFully()
{
if (settings.Pooling && driver.IsOpen)
{
// if we are in a transaction, roll it back
if ((driver.ServerStatus & ServerStatusFlags.InTransaction) != 0)
{
MySqlTransaction t = new MySqlTransaction(this, IsolationLevel.Unspecified);
t.Rollback();
}
MySqlPoolManager.ReleaseConnection(driver);
}
else
driver.Close();
driver = null;
}
public void updateUser(string pUserId, string pPassword, string pRole, string pSection)
{
try
{
loMySqlTransaction = GlobalVariables.goMySqlConnection.BeginTransaction();
loUser = new User();
loUser.userID = pUserId;
loUser.password = pPassword;
loUser.defaultSection = pSection;
loUser.update(ref loMySqlTransaction);
loUserRole = new UserRole();
loUserRole.RoleName = pRole;
loUserRole.UserId = pUserId;
loUserRole.insert(ref loMySqlTransaction);
loMySqlTransaction.Commit();
}
catch (Exception)
{
loMySqlTransaction.Rollback();
throw;
}
}