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());
}
}
}
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;
}
/// <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;
}
}
}
}
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));
}
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);
}
}
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;
}
}
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;
}
}
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);
}
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;
}
}
}
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);
}
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());
}
}
}
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;
}
}
}
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();
}
}
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();
}
}
}
}
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();
}
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);
}
}