Ejemplo n.º 1
0
 protected override void DataAdapterUpdate(System.Data.DataSet tabela, string strNomeTabela)
 {
     if (m_bUserCanUpdateDB)
     {
         OpenConnection();
         if (m_Connection.State == System.Data.ConnectionState.Open)
         {
             if (tabela.GetChanges() != null)
             {
                 m_Transaction = m_Connection.BeginTransaction();
                 m_CommandSelect.Transaction = m_Transaction;
                 try
                 {
                     m_DataAdapter.Update(tabela, strNomeTabela);
                     m_Transaction.Commit();
                     DataPersist = false;
                 }
                 catch (System.Exception eEcp)
                 {
                     m_excError = eEcp;
                     m_Transaction.Rollback();
                     ShowDialogUpdateError(strNomeTabela);
                 }
             }
         }
         CloseConnection();
     }
 }
        /// <summary>
        /// 动态数据入库操作
        /// </summary>
        private static void SaveDynamicData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, AisControl controlObject)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();
            try
            {
                // 批量更新
                foreach (string cmd in dynamicSqlCommandCollection)
                {
                    command.CommandText = cmd;
                    command.ExecuteNonQuery();
                }

                // 提交数据入库
                transaction.Commit();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                }
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                }
            }
        }
Ejemplo n.º 3
0
        public string LoadDataToTargetTable(string tableName, MySql.Data.MySqlClient.MySqlConnection connection, ref DataTable dtTarget)
        {
            string errorMessage = "";
            try
            {
                using (MySql.Data.MySqlClient.MySqlTransaction tran = connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
                {
                    using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
                    {
                        cmd.Connection = connection;
                        cmd.Transaction = tran;
                        cmd.CommandText = "SELECT * FROM MTG.Cards";
                        using (MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd))
                        {
                            da.UpdateBatchSize = 1000;
                            using (MySql.Data.MySqlClient.MySqlCommandBuilder cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da))
                            {
                                rowsCopied += da.Update(dtTarget);
                                tran.Commit();
                            }
                        }
                    }
                }
                System.Console.WriteLine($"{rowsCopied} rows copied.");
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }

            return errorMessage;
        }
Ejemplo n.º 4
0
        /// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySql.Data.MySqlClient.MySqlParameter[])</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySql.Data.MySqlClient.MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySql.Data.MySqlClient.MySqlParameter[] cmdParms = (MySql.Data.MySqlClient.MySqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
Ejemplo n.º 5
0
        static void Main(string[] args)
        {
            var ids = "18,19".Split(',');
            List <Dictionary <string, object> > dics = new List <Dictionary <string, object> >();

            foreach (var id in ids)
            {
                dics.Add(new Dictionary <string, object>
                {
                    ["cid"]  = id,
                    ["name"] = "a" + id,
                    ["sort"] = id
                });
            }
            using (var conn = new MySql.Data.MySqlClient.MySqlConnection(SQLHelperFactory.Instance.ConnectionString("Insert2", null)))
            {
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                var           i     = SQLHelperFactory.Instance.ExecuteNonQuery(conn, null, "Insert2", dics);
                trans.Commit();
            }
            var list = SQLHelperFactory.Instance.QueryForList("GetMenu", null);

            //var i = SQLHelperFactory.Instance.ExecuteNonQuery("Insert2", dics);
            Console.Read();
        }
        /// <summary>
        /// Gets a fresh, open and ready-to-use connection wrapper
        /// </summary>
        public async Task <MySqlConnection> GetConnection()
        {
            var connection = new MySqlData.MySqlClient.MySqlConnection(_connectionString);

            await connection.OpenAsync();

            var currentTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            return(new MySqlConnection(connection, currentTransaction));
        }
Ejemplo n.º 7
0
        private void SaveData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, TCPUDPServer controlObject, DateTime Sample, int FrameIndex, int ParamIndex, byte AtnDataType, byte[] Atn1Data, byte[] Atn2Data, byte[] Atn3Data, byte[] Atn4Data, byte[] Atn5Data, byte[] Atn6Data, byte[] Atn7Data, byte[] Atn8Data)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();
            try
            {
                command.Parameters.AddWithValue("@Sample", Sample);
                command.Parameters.AddWithValue("@FrameIndex", FrameIndex);
                command.Parameters.AddWithValue("@ParamIndex", ParamIndex);
                command.Parameters.AddWithValue("@AtnDataType", AtnDataType);
                command.Parameters.AddWithValue("@Atn1Data", Atn1Data);
                command.Parameters.AddWithValue("@Atn2Data", Atn2Data);
                command.Parameters.AddWithValue("@Atn3Data", Atn3Data);
                command.Parameters.AddWithValue("@Atn4Data", Atn4Data);
                command.Parameters.AddWithValue("@Atn5Data", Atn5Data);
                command.Parameters.AddWithValue("@Atn6Data", Atn6Data);
                command.Parameters.AddWithValue("@Atn7Data", Atn7Data);
                command.Parameters.AddWithValue("@Atn8Data", Atn8Data);
                // 批量更新
                foreach (string cmd in dynamicSqlCommandCollection)
                {
                    command.CommandText = cmd;



                    command.ExecuteNonQuery();
                }

                // 提交数据入库
                transaction.Commit();
                //if (controlObject != null)
                //{
                //    // 提交错误日志
                //    // controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    if (dynamicSqlCommandCollection.Count != 0)
                //    {
                //        this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    }
                //    //  this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //}
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                //if (controlObject != null)
                //{
                //   // this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //    // 提交错误日志
                //    //controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //}
                Console.WriteLine(ex);
            }
        }
Ejemplo n.º 8
0
 public void Confirm(int id)
 {
     MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction();
     m_command.Transaction = trc;
     try
     {
         Event events = this.Get(id);
         if (events.POSTED) //.EVENT_STATUS == EventStatus.Confirm)
         {
             throw new Exception("Status is already Posted/Confirm");
         }
         Period p = AssertValidPeriod(events.TRANSACTION_DATE);
         doConfirm(events, p);
         events.ProcessConfirm();
         this.UpdateStatus(events, true);
         updateStockCards(events.EVENT_ITEMS);
         trc.Commit();
     }
     catch (Exception x)
     {
         trc.Rollback();
         throw x;
     }
 }
Ejemplo n.º 9
0
 public void Confirm(int id)
 {
     MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction();
     m_command.Transaction = trc;
     try
     {
         EventJournal events = this.Get(id);
         if (events.POSTED)
         {
             throw new Exception("Status is already Posted/Confirm");
         }
         Period p = AssertValidPeriod(events.TRANSACTION_DATE);
         events.ProcessPosting();
         doConfirm(events, p);
         this.UpdateStatus(events, true);
         //updateVendorBalances(events.EVENT_JOURNAL_ITEMS);
         trc.Commit();
     }
     catch (Exception x)
     {
         trc.Rollback();
         throw x;
     }
 }
Ejemplo n.º 10
0
        public int ExecuteBulk(string SqlStatment)
        {
            MySql.Data.MySqlClient.MySqlConnection Cn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
            int result = -1;

            if (Cn.State != ConnectionState.Open)
            {
                Cn.Open();
            }

            MySql.Data.MySqlClient.MySqlCommand     command = Cn.CreateCommand();
            MySql.Data.MySqlClient.MySqlTransaction transaction;

            // Start a local transaction
            transaction = Cn.BeginTransaction(IsolationLevel.ReadCommitted);
            // Assign transaction object for a pending local transaction
            command.Transaction = transaction;

            try
            {
                foreach (string sql in SqlStatment.Split(';'))
                {
                    if (!String.IsNullOrEmpty(sql))
                    {
                        command.CommandText = sql;
                        result = command.ExecuteNonQuery();
                    }
                }


                transaction.Commit();
            }
            catch (Exception e)
            {
                transaction.Rollback(); throw e;
            }
            finally
            {
                Cn.Close();
                Cn.Dispose();
                MySql.Data.MySqlClient.MySqlConnection.ClearPool(Cn);
            }
            return(result);
        }
Ejemplo n.º 11
0
        public override bool Actualizar(Dto.Rol obj)
        {
            using (MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection())
            {
                cnn.ConnectionString = base.CurrentConnectionString.ConnectionString;
                cnn.Open();

                MySql.Data.MySqlClient.MySqlTransaction trans = cnn.BeginTransaction();

                try
                {
                    using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
                    {
                        cmd.CommandText = "seguridad.uspGestionRoles";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("intAccion", uspAcciones.Actualizar));
                        this.CargarParametros(cmd, obj);

                        int intRegistrosAfectados = base.CurrentDatabase.ExecuteNonQuery(cmd, trans);

                        if (intRegistrosAfectados > 0)
                        {
                            //Guardamos los permisos
                            DPermiso objDALPermisos = new DPermiso();
                            objDALPermisos.Insertar(obj.permisos, (short)obj.idrol, trans);

                            trans.Commit();

                            return(true);
                        }
                        else
                        {
                            return(false);
                        }
                    }
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
Ejemplo n.º 12
0
        bool NumSql(List <NumberModel> dtList, string database)
        {
            bool          result = false;
            string        conStr = System.Configuration.ConfigurationManager.ConnectionStrings["wsnkey"].ConnectionString;
            IDbConnection cn     = new MySql.Data.MySqlClient.MySqlConnection(conStr);

            //new System.Data.SqlClient.SqlConnection(conStr);
            try
            {
                cn.Open();
                IDbTransaction tran = cn.BeginTransaction();

                foreach (NumberModel item in dtList)
                {
                    IDbCommand cmd    = cn.CreateCommand();
                    string     cmdtxt = "INSERT INTO " + database + "(feed_id,value,`key`,at) VALUES(@feed_id,@value,@key,@at)";
                    //string cmdtxt = "INSERT INTO "+database+"() VALUES(@at)";
                    cmd.CommandText = cmdtxt;
                    cmd.Transaction = tran;
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@feed_id", item.Feed_id));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@value", item.Value));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@at", item.at));
                    cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@key", item.key));
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();

                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);

                result = false;
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                }
            }
            return(result);
        }
Ejemplo n.º 13
0
        public override bool Insertar(Dto.Maquina obj)
        {
            using (MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(base.CurrentConnectionString.ConnectionString))
            {
                cnn.Open();

                MySql.Data.MySqlClient.MySqlTransaction trans = cnn.BeginTransaction();

                try
                {
                    using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
                    {
                        cmd.CommandText = "produccion.uspGestionMaquinas";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("intAccion", uspAcciones.Insertar));
                        this.CargarParametros(cmd, obj);

                        obj.idmaquina = Convert.ToByte(base.CurrentDatabase.ExecuteScalar(cmd, trans));

                        //Guardamos las variaciones
                        DMaquinaVariacionesProduccion objDALVariaciones = new DMaquinaVariacionesProduccion();
                        objDALVariaciones.Insertar(obj.VariacionesProduccion, trans);

                        //Guardamos los permisos
                        DMaquinaDatosPeriodicos objDALDatPeriodicos = new DMaquinaDatosPeriodicos();
                        objDALDatPeriodicos.Insertar(obj.DatosPeriodicos, trans);

                        trans.Commit();

                        return(obj.idmaquina > 0);
                    }
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 删除过时的AIS数据操作
        /// </summary>
        private static void DeleteAISdata(MySql.Data.MySqlClient.MySqlConnection connection, AisControl controlObject)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();

            DateTime lastDynamicTime = DateTime.Now.Subtract(new TimeSpan(0, 4, 0, 0));
            DateTime lastStaticTime  = DateTime.Now.Subtract(new TimeSpan(15, 0, 0, 0));


            string sqlDeleteAisData = "delete from realtimeaisdynamicinfotable where AISTimeStamp < '" + lastDynamicTime + "';";

            sqlDeleteAisData += "delete from realtimeaisstaticinfotable where Timestamp < '" + lastStaticTime + "';";
            try
            {
                command.CommandText = sqlDeleteAisData;
                command.ExecuteNonQuery();

                // 提交数据入库
                transaction.Commit();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("AIS过时数据删除操作成功。");
                }

                command.Dispose();
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                if (controlObject != null)
                {
                    // 提交错误日志
                    controlObject.AddStatusString("AIS过时数据删除操作失败。\r\n错误信息为:" + ex.ToString());
                }
            }
        }
Ejemplo n.º 15
0
        public override bool Insertar(Dto.Rol obj)
        {
            using (MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(base.CurrentConnectionString.ConnectionString))
            {
                cnn.Open();

                MySql.Data.MySqlClient.MySqlTransaction trans = cnn.BeginTransaction();

                try
                {
                    using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
                    {
                        cmd.CommandText = "seguridad.uspGestionRoles";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("intAccion", uspAcciones.Insertar));
                        this.CargarParametros(cmd, obj);

                        obj.idrol = Convert.ToInt16(base.CurrentDatabase.ExecuteScalar(cmd, trans));

                        //Guardamos los permisos
                        DPermiso objDALPermisos = new DPermiso();
                        objDALPermisos.Insertar(obj.permisos, (short)obj.idrol, trans);

                        trans.Commit();

                        return(obj.idrol > 0);
                    }
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
Ejemplo n.º 16
0
 public void TopUpCustomer(string fromTenantCode, string toTenantCode, decimal money)
 {
     using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
     {
         conn.Open();
         var tran = conn.BeginTransaction();
         string sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0}  where TenantCode = '{1}';",
             money, fromTenantCode);
         sql += string.Format(@" update movo_tenant_account set Balance = Balance + {0}  where TenantCode = '{1}';",
             money, toTenantCode);
         MySqlHelper.ExecuteNonQuery(conn, sql);
         EMTenantAccountDetail fromTenantDetail = new EMTenantAccountDetail()
         {
             ConsumeMoney = money,
             ConsumeType = EnumConsumeType.RechargeToCustomer,
             OperateType = EnumOperateType.Outlay,
             CreateTime = DateTime.Now,
             TargetTenantCode = toTenantCode,
             TenantCode = fromTenantCode,
             OperatorTenantCode = fromTenantCode,
         };
         IEMTenantAccountDetailService serv = new EMTenantAccountDetailService();
         serv.Insert(conn,fromTenantDetail);
         EMTenantAccountDetail toTenantDetail = new EMTenantAccountDetail()
         {
             ConsumeMoney = money,
             ConsumeType = EnumConsumeType.AccountIncome,
             OperateType = EnumOperateType.Income,
             CreateTime = DateTime.Now,
             TenantCode = toTenantCode,
             OperatorTenantCode = fromTenantCode,
         };
         serv.Insert(conn,toTenantDetail);
         tran.Commit();
     }
 }
Ejemplo n.º 17
0
        public void InsertBatch <T>(string connection, List <T> models, int batch = 0) where T : class, IModel
        {
            using (var c = new MySqlData.MySqlClient.MySqlConnection(connection))
            {
                c.Open();

                using (var transaction = c.BeginTransaction())
                {
                    try
                    {
                        var map = _sql?.Configuration?.GetMap <T>();
                        if (map == null)
                        {
                            throw new Exception($"Class Map for:{typeof(T).Name} could not be found.");
                        }

                        var name  = map.TableName;
                        var table = models.ToDataTable();
                        if (table.Rows.Count == 0)
                        {
                            return;
                        }

                        var builder = new StringBuilder();
                        builder.Append("SELECT TABLE_NAME");
                        builder.Append(", COLUMN_NAME");
                        builder.Append(", DATA_TYPE");
                        builder.Append(", CHARACTER_MAXIMUM_LENGTH");
                        builder.Append(", CHARACTER_OCTET_LENGTH");
                        builder.Append(", NUMERIC_PRECISION");
                        builder.Append(", NUMERIC_SCALE AS SCALE");
                        builder.Append(", COLUMN_DEFAULT");
                        builder.Append(", IS_NULLABLE");
                        builder.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
                        builder.Append(" WHERE TABLE_NAME = @Table");

                        var schema = new List <Schema>();

                        //get table schema (e.g. names and datatypes for mapping)
                        using (var command = new MySqlData.MySqlClient.MySqlCommand(builder.ToString(), c))
                        {
                            var parameter = new MySqlData.MySqlClient.MySqlParameter();
                            parameter.Value         = map.TableName;
                            parameter.ParameterName = "@Table";
                            parameter.MySqlDbType   = MySqlData.MySqlClient.MySqlDbType.String;

                            command.Parameters.Add(parameter);

                            using (var sql = new MySqlData.MySqlClient.MySqlDataAdapter(command))
                            {
                                var result     = new DataTable();
                                var parameters = map.Properties
                                                 .Where(x => x.Ignored == false)
                                                 .Where(x => x.IsReadOnly == false)
                                                 .Where(x => x.KeyType == KeyType.NotAKey);

                                sql.Fill(result);

                                schema = (from p in parameters
                                          join s in result.AsEnumerable() on p.ColumnName equals s.Field <string>("COLUMN_NAME")
                                          select new Schema()
                                {
                                    ColumnName = s.Field <string>("COLUMN_NAME"),
                                    DataType = s.Field <string>("DATA_TYPE"),
                                    Size = s.Field <object>("CHARACTER_OCTET_LENGTH")
                                }).ToList();
                            }
                        }

                        using (var command = new MySqlData.MySqlClient.MySqlCommand($"INSERT INTO {map.TableName} ({string.Join(",", schema.Select(x => x.ColumnName))}) VALUES ({string.Join(",", schema.Select(x => $"@{x.ColumnName}"))});", c))
                        {
                            command.UpdatedRowSource = UpdateRowSource.None;

                            foreach (var type in schema)
                            {
                                var parameter = new MySqlData.MySqlClient.MySqlParameter();
                                parameter.ParameterName = $"@{type.ColumnName}";
                                parameter.SourceColumn  = type.ColumnName;

                                switch (type.DataType.ToLower())
                                {
                                case "varchar":
                                case "char":
                                case "text":
                                    parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.String;
                                    parameter.Size        = Int32.Parse(type.Size.ToString());
                                    break;

                                case "datetime":
                                    parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.DateTime;
                                    break;

                                case "int":
                                    parameter.MySqlDbType = MySqlData.MySqlClient.MySqlDbType.Int32;
                                    break;

                                default:
                                    throw new NotImplementedException();
                                }

                                command.Parameters.Add(parameter);
                            }

                            using (var adapter = new MySqlData.MySqlClient.MySqlDataAdapter())
                            {
                                adapter.InsertCommand = command;

                                var timer = Stopwatch.StartNew();

                                _log.LogTrace <MySqlQuery>($"Bulk Insert on {name}. {models.Count} rows queued for insert.");

                                timer.Start();

                                if (batch > 0)
                                {
                                    adapter.UpdateBatchSize = 100;
                                }

                                adapter.Update(table);

                                transaction.Commit();

                                _log.LogTrace <MySqlQuery>($"Bulk Insert on {name} complete in: {timer.Elapsed.ToString(@"hh\:mm\:ss\:fff")}");
                            }
                        }
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();

                        throw;
                    }
                    finally
                    {
                        c.Close();
                    }
                }
            }
        }
Ejemplo n.º 18
0
        private void enviar(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Application.DoEvents();

            //inicia a transacao
            try
            {
                myconn.Open();
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;
                Application.DoEvents();
                MessageBox.Show(ex.Message);
                return;
            }

            MySql.Data.MySqlClient.MySqlTransaction mytrans;
            mytrans = myconn.BeginTransaction();

            try
            {
                foreach (db.eletrocadDataSet.trafoRow row in eletrocadDataSet.trafo.Rows)
                {
                    //insere transformadores
                    MySql.Data.MySqlClient.MySqlCommand mycommand = myconn.CreateCommand();
                    mycommand.Connection  = myconn;
                    mycommand.Transaction = mytrans;

                    mycommand.CommandText =
                        "INSERT INTO `eletrocad`.`trafo` " +
                        "(`chave`, " +
                        "`cia`, " +
                        "`gps_time`, " +
                        "`alimentador`, " +
                        "`usuario_id`, " +
                        "`programacao_ip_id`) " +
                        "VALUES " +
                        "(@chave, " +
                        "@cia, " +
                        "@gps_time, " +
                        "@alimentador, " +
                        "@usuario_id, " +
                        "@programacao_ip_id)";

                    mycommand.Parameters.AddWithValue("chave", row.chave);
                    if (row.IsciaNull())
                    {
                        mycommand.Parameters.AddWithValue("cia", null);
                    }
                    else
                    {
                        mycommand.Parameters.AddWithValue("cia", row.cia);
                    }
                    mycommand.Parameters.AddWithValue("gps_time", row.gps_time);
                    if (row.IsalimentadorNull())
                    {
                        mycommand.Parameters.AddWithValue("alimentador", null);
                    }
                    else
                    {
                        mycommand.Parameters.AddWithValue("alimentador", row.alimentador);
                    }
                    mycommand.Parameters.AddWithValue("usuario_id", row.usuario_id);
                    mycommand.Parameters.AddWithValue("programacao_ip_id", row.programacao_ip_id);

                    mycommand.ExecuteNonQuery();

                    listEnviar.Items.Add("Trafo enviado: " + row.chave);
                    Application.DoEvents();

                    //INSERE POSTES
                    foreach (db.eletrocadDataSet.posteRow rowPoste in row.GetposteRows())
                    {
                        MySql.Data.MySqlClient.MySqlCommand mycommandPoste = myconn.CreateCommand();
                        mycommandPoste.Transaction = mytrans;

                        mycommandPoste.CommandText =
                            "INSERT INTO `eletrocad`.`poste` " +
                            "(`bairro`, " +
                            "`logradouro`, " +
                            "`gps_time`, " +
                            "`sequencia`, " +
                            "`barramento`, " +
                            "`medido`, " +
                            "`trafo_id`, " +
                            "`lat`, " +
                            "`lon`, " +
                            "`obs`, " +
                            "`gpgga`, " +
                            "`medidor`, " +
                            "`posicao_trafo`, " +
                            "`ligacao_clandestina`, " +
                            "`condicao_risco_id`) " +
                            "VALUES " +
                            "(@bairro , " +
                            "@logradouro , " +
                            "@gps_time , " +
                            "@sequencia , " +
                            "@barramento , " +
                            "@medido , " +
                            "@trafo_id , " +
                            "@lat , " +
                            "@lon , " +
                            "@obs , " +
                            "@gpgga , " +
                            "@medidor , " +
                            "@posicao_trafo , " +
                            "@ligacao_clandestina , " +
                            "@condicao_risco_id)";

                        if (rowPoste.IsbairroNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("bairro", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("bairro", rowPoste.bairro);
                        }

                        if (rowPoste.IslogradouroNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("logradouro", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("logradouro", rowPoste.logradouro);
                        }

                        mycommandPoste.Parameters.AddWithValue("gps_time", rowPoste.gps_time);
                        mycommandPoste.Parameters.AddWithValue("sequencia", rowPoste.sequencia);

                        if (rowPoste.IsbarramentoNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("barramento", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("barramento", rowPoste.barramento);
                        }

                        if (rowPoste.IsmedidoNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("medido", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("medido", rowPoste.medido);
                        }

                        mycommandPoste.Parameters.AddWithValue("trafo_id", mycommand.LastInsertedId);
                        mycommandPoste.Parameters.AddWithValue("lat", rowPoste.lat);
                        mycommandPoste.Parameters.AddWithValue("lon", rowPoste.lon);

                        if (rowPoste.IsobsNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("obs", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("obs", rowPoste.obs);
                        }

                        if (rowPoste.IsgpggaNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("gpgga", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("gpgga", rowPoste.gpgga);
                        }

                        if (rowPoste.IsmedidorNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("medidor", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("medidor", rowPoste.medidor);
                        }

                        if (rowPoste.Isposicao_trafoNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("posicao_trafo", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("posicao_trafo", rowPoste.posicao_trafo);
                        }

                        if (rowPoste.Isligacao_clandestinaNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("ligacao_clandestina", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("ligacao_clandestina", rowPoste.ligacao_clandestina);
                        }

                        if (rowPoste.Iscondicao_risco_idNull())
                        {
                            mycommandPoste.Parameters.AddWithValue("condicao_risco_id", null);
                        }
                        else
                        {
                            mycommandPoste.Parameters.AddWithValue("condicao_risco_id", rowPoste.condicao_risco_id);
                        }

                        mycommandPoste.ExecuteNonQuery();


                        //INSERE luminaria
                        foreach (db.eletrocadDataSet.poste_has_tipo_luminariaRow rowPosteHasTipoLuminaria
                                 in rowPoste.Getposte_has_tipo_luminariaRows())
                        {
                            MySql.Data.MySqlClient.MySqlCommand mycommandPosteHasTipoLuminaria = myconn.CreateCommand();
                            mycommandPosteHasTipoLuminaria.Transaction = mytrans;

                            //mfrn@0830$X-PRO
                            mycommandPosteHasTipoLuminaria.CommandText = "INSERT INTO `eletrocad`.`poste_has_tipo_luminaria` " +
                                                                         "(`poste_id`,`tipo_luminaria_id`,`lampada_id`,`reator_id`,`braco_id`,`aceso`," +
                                                                         "`lat`, " +
                                                                         "`lon`, " +
                                                                         "`gps_time`, " +
                                                                         "`quantidade`,`ativacao_id`,`fase_id`) VALUES " +
                                                                         "(@posteId, @tipoId,@lampada,@reator,@braco,@aceso,@lat,@lon,@gps_time," +
                                                                         "@quantidade, " +
                                                                         "@ativacao, " +
                                                                         "@fase)";

                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("posteId", mycommandPoste.LastInsertedId);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("tipoId", rowPosteHasTipoLuminaria.tipo_luminaria_id);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("lampada", rowPosteHasTipoLuminaria.lampada_id);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("aceso", rowPosteHasTipoLuminaria.aceso);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("lat", rowPosteHasTipoLuminaria.lat);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("lon", rowPosteHasTipoLuminaria.lon);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("gps_time", rowPosteHasTipoLuminaria.gps_time);

                            if (rowPosteHasTipoLuminaria.Isreator_idNull())
                            {
                                mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("reator", null);
                            }
                            else
                            {
                                mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("reator", rowPosteHasTipoLuminaria.reator_id);
                            }

                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("braco", rowPosteHasTipoLuminaria.braco_id);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("quantidade", rowPosteHasTipoLuminaria.quantidade);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("ativacao", rowPosteHasTipoLuminaria.ativacao_id);
                            mycommandPosteHasTipoLuminaria.Parameters.AddWithValue("fase", rowPosteHasTipoLuminaria.fase_id);

                            mycommandPosteHasTipoLuminaria.ExecuteNonQuery();
                        }

                        //INSERE uso mútuo
                        foreach (db.eletrocadDataSet.poste_has_uso_mutuoRow rowPosteHasUsoMutuo
                                 in rowPoste.Getposte_has_uso_mutuoRows())
                        {
                            MySql.Data.MySqlClient.MySqlCommand mycommandPosteHasUsoMutuo = myconn.CreateCommand();
                            mycommandPosteHasUsoMutuo.Transaction = mytrans;

                            mycommandPosteHasUsoMutuo.CommandText = "INSERT INTO `eletrocad`.`poste_has_uso_mutuo` " +
                                                                    "(`poste_id`,`uso_mutuo_id`) VALUES (@posteId, @usoId)";

                            mycommandPosteHasUsoMutuo.Parameters.AddWithValue("posteId", mycommandPoste.LastInsertedId);
                            mycommandPosteHasUsoMutuo.Parameters.AddWithValue("usoId", rowPosteHasUsoMutuo.uso_mutuo_id);

                            mycommandPosteHasUsoMutuo.ExecuteNonQuery();
                        }

                        listEnviar.Items.Add("Sequência: " + rowPoste.sequencia + " componentes inseridos");
                        Application.DoEvents();
                    }

                    //listEnviar.Items.Add("Trafo enviado: "+row.chave);
                    //Application.DoEvents();
                }
                mytrans.Commit();
                listEnviar.Items.Add("Trafos enviados com sucesso!");
            }
            catch (Exception ex)
            {
                try
                {
                    mytrans.Rollback();
                }
                catch (MySql.Data.MySqlClient.MySqlException myex)
                {
                    if (mytrans.Connection != null)
                    {
                        listEnviar.Text = ex.Message + " An exception of type " + myex.GetType() +
                                          " was encountered while attempting to roll back the transaction.";
                    }
                }

                MessageBox.Show("Não foi possível enviar: " + ex.Message);
            }
            finally
            {
                myconn.Close();
            }
            Cursor.Current = Cursors.Default;
            Application.DoEvents();
        }
Ejemplo n.º 19
0
 public void DecuntMoney(string tenantCode, string opratorTenantCode, EnumConsumeType type, decimal money, string remark)
 {
     using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))
     {
         conn.Open();
         var tran = conn.BeginTransaction();
         string sql = string.Format(@"update movo_tenant_account set Balance = Balance - {0}  where TenantCode = '{1}';",
            money, tenantCode);
         MySqlHelper.ExecuteNonQuery(conn, sql);
         EMTenantAccountDetail detail = new EMTenantAccountDetail()
         {
             ConsumeMoney = money,
             ConsumeType = type,
             OperateType = EnumOperateType.Outlay,
             CreateTime = DateTime.Now,
             TenantCode = tenantCode, 
             Remark = remark,
             OperatorTenantCode = opratorTenantCode, 
         };
         IEMTenantAccountDetailService serv = new EMTenantAccountDetailService();
         serv.Insert(conn, detail);
         tran.Commit();
     }
 }
        private void SaveData(MySql.Data.MySqlClient.MySqlConnection connection, List <string> dynamicSqlCommandCollection, DateTime Sample, int FrameIndex, int ParamIndex, byte AtnDataType, byte[] Atn1Data, int[] Atn2Data, int[] Atn3Data, int[] Atn4Data, int[] Atn5Data, int[] Atn6Data, int[] Atn7Data, int[] Atn8Data)
        {
            // 开始事务处理
            MySql.Data.MySqlClient.MySqlTransaction transaction = connection.BeginTransaction();
            MySql.Data.MySqlClient.MySqlCommand     command     = connection.CreateCommand();
            //byte[] tmp = new byte[1024];
            //int[] tmpw = new int[1024];
            //for (int i = 0; i < 1024; i++)
            //{
            //    tmp[i] = 1;
            //    tmpw[i] = 2;

            //}
            //byte[] tmp;
            //foreach (var value in Atn1Data)
            //{
            //    byte[] byteArray = BitConverter.GetBytes(value);
            //  //  Console.WriteLine("{0,16}{1,10}{2,17}", value,
            //                   //   BitConverter.IsLittleEndian ? "Little" : " Big",
            //                   ///   BitConverter.ToString(byteArray));
            //}
            try
            {
                command.Parameters.AddWithValue("@Sample", Sample);
                command.Parameters.AddWithValue("@FrameIndex", FrameIndex);
                command.Parameters.AddWithValue("@ParamIndex", ParamIndex);
                command.Parameters.AddWithValue("@AtnDataType", AtnDataType);
                command.Parameters.AddWithValue("@Atn1Data", Atn1Data);
                command.Parameters.AddWithValue("@Atn2Data", Atn2Data);
                command.Parameters.AddWithValue("@Atn3Data", Atn3Data);
                command.Parameters.AddWithValue("@Atn4Data", Atn4Data);
                command.Parameters.AddWithValue("@Atn5Data", Atn5Data);
                command.Parameters.AddWithValue("@Atn6Data", Atn6Data);
                command.Parameters.AddWithValue("@Atn7Data", Atn7Data);
                command.Parameters.AddWithValue("@Atn8Data", Atn8Data);
                // 批量更新
                foreach (string cmd in dynamicSqlCommandCollection)
                {
                    command.CommandText = cmd;



                    command.ExecuteNonQuery();
                }

                // 提交数据入库
                transaction.Commit();
                //if (controlObject != null)
                //{
                //    // 提交错误日志
                //    // controlObject.AddStatusString("动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    if (dynamicSqlCommandCollection.Count != 0)
                //    {
                //        this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //    }
                //    //  this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作成功。动态目标数量[" + dynamicSqlCommandCollection.Count.ToString() + "]");
                //}
            }
            catch (Exception ex)
            {
                // 放弃当前的数据入库操作
                transaction.Rollback();
                //if (controlObject != null)
                //{
                //   // this.Invoke(new updateControlDelegate(updateControl), "动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //    // 提交错误日志
                //    //controlObject.AddStatusString("动态数据入库操作失败。\r\n错误信息为:" + ex.ToString());
                //}
                Console.WriteLine(ex);
            }
        }