//带参数的执行命令   
        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;
     }
 }
Example #3
0
 /// <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");
        }
Example #5
0
        // 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;
        }
Example #7
0
        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;
        }
Example #8
0
        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;
                }
            }
        }
Example #9
0
        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();
        }
Example #10
0
        /// <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;
        }
Example #11
0
        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);
        }
Example #12
0
        public void RollbackTransaction(MySqlTransaction trans, bool closeConnection)
        {
            trans.Rollback();

            if (closeConnection && trans.Connection.State != ConnectionState.Closed)
            {
                trans.Connection.Close();
            }

            trans = null;
        }
Example #13
0
        /// <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);
        }
Example #15
0
        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();
            }
        }
Example #21
0
 void IPromotableSinglePhaseNotification.Rollback(SinglePhaseEnlistment singlePhaseEnlistment)
 {
     simpleTransaction.Rollback();
     singlePhaseEnlistment.Aborted();
     connection.CurrentTransaction = null;
 }
Example #22
0
        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();
        }
Example #23
0
 /// <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;
     }
 }
Example #24
0
 public void Rollback()
 {
     transaction.Rollback();
 }
Example #25
0
 /// <summary>
 /// 回滚事务
 /// </summary>
 public override void RollbackTransaction()
 {
     transaction.Rollback();
     this.IsTransaction = false;
 }
Example #26
0
        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);
        }
Example #27
0
        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;
        }
Example #28
0
        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;
            }
        }