//带参数的执行命令 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(); } } }
private void btDelete_Click(object sender, EventArgs e) { if ((dgvDirections.SelectedRows.Count == 0) || (dgvDirections.SelectedRows[0].Cells[dgvDirections_Type.Index].Value.ToString() == "Н")) { MessageBox.Show("Выберите профиль"); } else if (SharedClasses.Utility.ShowChoiceMessageBox("Удалить выбранный профиль?", "Удаление профиля")) { try { _DB_Connection.Delete(DB_Table.PROFILES, new Dictionary <string, object> { { "faculty_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }); UpdateTable(); } catch (MySqlException ex) { if (ex.Number == 1217 || ex.Number == 1451) { List <object[]> appEntrances = _DB_Connection.Select(DB_Table.APPLICATIONS_ENTRANCES, new string[] { "application_id" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value), new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value), new Tuple <string, Relation, object>("profile_short_name", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value) }); if (appEntrances.Count > 0) { MessageBox.Show("На данный профиль подано заявление. Удаление невозможно."); } else if (SharedClasses.Utility.ShowChoiceMessageWithConfirmation("Профиль включен в кампанию. Выполнить удаление?", "Связь с кампанией")) { using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction()) { _DB_Connection.Delete(DB_Table.CAMPAIGNS_PROFILES_DATA, new Dictionary <string, object> { { "profiles_direction_faculty", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "profiles_direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "profiles_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }, transaction); _DB_Connection.Delete(DB_Table.PROFILES, new Dictionary <string, object> { { "faculty_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }, transaction); transaction.Commit(); } UpdateTable(); } } } } }
public void CommitTran() { if (tran != null) { tran.Commit(); tran = null; } }
/// <summary> /// 提交事务 /// </summary> public static void CommitTransaction() { if (m_isTransaction != false) { trans.Commit(); m_isTransaction = false; con.Close(); con.Dispose(); } }
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 static void CloseConnectionAndEndTransaction(MySqlConnection connection, MySqlTransaction transaction) { if (transaction != null) transaction.Commit(); CloseConnection(connection); }
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(); }
//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(); } }
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(); } } }
void IPromotableSinglePhaseNotification.SinglePhaseCommit(SinglePhaseEnlistment singlePhaseEnlistment) { simpleTransaction.Commit(); singlePhaseEnlistment.Committed(); connection.CurrentTransaction = null; }
/// <summary> /// 提交事务 /// </summary> public override void CommitTransaction() { transaction.Commit(); this.IsTransaction = false; }
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; } }
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(); }
//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 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); }
//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 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(); } }
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; } } }
public void Commit() { transaction.Commit(); }
/// <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 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); }
public void CommitTransaction(MySqlTransaction trans, bool closeConnection) { trans.Commit(); if (closeConnection && trans.Connection.State != ConnectionState.Closed) { trans.Connection.Close(); } trans = 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); }
/// <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(); } } }
/// <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; }