public MySqlTransactionScope(MySqlConnection con, Transaction trans, MySqlTransaction simpleTransaction) { connection = con; baseTransaction = trans; this.simpleTransaction = simpleTransaction; }
public static DataRow ExecuteDatarow(MySqlConnection connection, MySqlTransaction transaction, string commandText, params MySqlParameter[] parms) { var dt = ExecuteDataTable(connection, transaction, commandText, parms); if (dt == null) return null; if (dt.Rows.Count == 0) return null; return dt.Rows[0]; }
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, Dictionary<string, object> cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = cmdType; if (cmdParms != null) { foreach (var param in cmdParms) { var parameter = cmd.CreateParameter(); parameter.ParameterName = param.Key; parameter.Value = param.Value; cmd.Parameters.Add(parameter); } } }
public MySqlCommand(string commandText, MySqlConnection connection, MySqlTransaction transaction) { CommandText = commandText; DbConnection = connection; DbTransaction = transaction; m_parameterCollection = new MySqlParameterCollection(); }
/// <include file='docs/mysqlcommand.xml' path='docs/ctor4/*'/> public MySqlCommand(string cmdText, MySqlConnection connection, MySqlTransaction transaction) : this(cmdText, connection) { curTransaction = transaction; }
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1, MySqlTransaction tr) { SqlDataAdapter1 = new MySqlDataAdapter(); SqlInsertCommand1 = new MySqlCommand("TesoreriaMov_Insertar", SqlConnection1); SqlUpdateCommand1 = new MySqlCommand("TesoreriaMov_Actualizar", SqlConnection1); SqlDeleteCommand1 = new MySqlCommand("TesoreriaMov_Borrar", SqlConnection1); SqlInsertCommand1.Transaction = tr; SqlUpdateCommand1.Transaction = tr; SqlDeleteCommand1.Transaction = tr; SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1; SqlDataAdapter1.InsertCommand = SqlInsertCommand1; SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1; // IMPLEMENTACIÓN DE LA ORDEN UPDATE SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdMovTESM"); SqlUpdateCommand1.Parameters.Add("p_fecha", MySqlDbType.DateTime, 20,"FechaTESM"); SqlUpdateCommand1.Parameters.Add("p_pc", MySqlDbType.Int32, 11, "IdPcTESM"); SqlUpdateCommand1.Parameters.Add("p_detalle", MySqlDbType.VarChar, 200, "DetalleTESM"); SqlUpdateCommand1.Parameters.Add("p_importe", MySqlDbType.Double, 50, "ImporteTESM"); SqlUpdateCommand1.CommandType = CommandType.StoredProcedure; // IMPLEMENTACIÓN DE LA ORDEN INSERT SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdMovTESM"); SqlInsertCommand1.Parameters.Add("p_fecha", MySqlDbType.DateTime, 20, "FechaTESM"); SqlInsertCommand1.Parameters.Add("p_pc", MySqlDbType.Int32, 11, "IdPcTESM"); SqlInsertCommand1.Parameters.Add("p_detalle", MySqlDbType.VarChar, 200, "DetalleTESM"); SqlInsertCommand1.Parameters.Add("p_importe", MySqlDbType.Double, 50, "ImporteTESM"); SqlInsertCommand1.CommandType = CommandType.StoredProcedure; // IMPLEMENTACIÓN DE LA ORDEN DELETE SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 11, "IdMovTESM"); SqlDeleteCommand1.CommandType = CommandType.StoredProcedure; return SqlDataAdapter1; }
/// <summary> ///使用现有的SQL事务执行一个sql命令(不返回数据集) /// </summary> /// <remarks> ///举例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="trans">一个现有的事务</param> /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> /// <param name="cmdText">存储过程名称或者sql命令语句</param> /// <param name="commandParameters">执行命令所用参数的集合</param> /// <returns>执行命令所影响的行数</returns> public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; }
//带参数的执行命令 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 ControladoresRenombraSectorizacion(string idSistema, string idSectorizacion) { Sectorizaciones s = new Sectorizaciones(); s.IdSistema = idSistema; s.IdSectorizacion = idSectorizacion; // // ResetServiciosCD40("ConexionBaseDatosCD40"); MySql.Data.MySqlClient.MySqlTransaction trans = GestorBDCD40.StartTransaction(true); try { string consulta = "DELETE FROM Sectorizaciones WHERE IdSistema='" + idSistema + "' AND IdSectorizacion='" + idSectorizacion + "'"; GestorBDCD40.ExecuteNonQuery(consulta, trans); consulta = "UPDATE Sectorizaciones SET IdSectorizacion='" + idSectorizacion + "' WHERE IdSistema='" + idSistema + "' AND IdSectorizacion='TEMPORARY_CONTROLLER_SCTZ'"; GestorBDCD40.ExecuteNonQuery(consulta, trans); GestorBDCD40.Commit(trans); } catch (MySql.Data.MySqlClient.MySqlException) { GestorBDCD40.RollBack(trans); } //finally //{ // // ResetServiciosCD40("ConexionBaseDatosCD40_Trans"); //} }
public void Dispose() { transaction.Dispose(); connection.Dispose(); transaction = null; connection = null; }
private void GeneraSectoresSector(MySql.Data.MySqlClient.MySqlTransaction trans, string idsistema, string nucleo, string nomSector, KeyValuePair <string, string[]> top) { try { SectoresSector ss = new SectoresSector(); foreach (string s in top.Value) { ss.IdSistema = idsistema; ss.IdNucleo = nucleo; ss.IdSector = nomSector; ss.IdSectorOriginal = s; // Comprobar que esta combinación de sectores no existe en la tabla Sectores // en cuyo caso no hay nada que generar if (GestorBDCD40.ListSelectSQL(ss, trans).Count > 0) { continue; } ss.EsDominante = top.Value[0] == s; // si es el primer usuario, es dominante GestorBDCD40.InsertSQL(ss, trans); } } catch (Exception) { } }
public MasterPOSConnection(MySqlConnection Connection, MySqlTransaction Transaction) { mConnection = Connection; mTransaction = Transaction; GetConnection(); }
private string DameNombreSector(MySql.Data.MySqlClient.MySqlTransaction trans, string[] listaSectores, string idSistema, string idNucleo) { System.Text.StringBuilder lista = new System.Text.StringBuilder(); foreach (string s in listaSectores) { lista.AppendFormat("'{0}',", s); } // Buscar una agrupación if (lista.Length > 0) { lista = lista.Remove(lista.Length - 1, 1); CD40.BD.Utilidades u = new CD40.BD.Utilidades(GestorBDCD40.ConexionMySql); string nomAgrupacion = u.GetAgrupacion(listaSectores.Length, lista.ToString()); // Si no la encuentra, aplicar algoritmo if (nomAgrupacion == null) { nomAgrupacion = GeneraAlgoritmo(idSistema, idNucleo, lista.ToString(), trans); } return(nomAgrupacion); } return(""); }
private void init(string connString) { connection = new MySqlConnection(connString); connection.Open(); connection.ChangeDatabase(Server.MySQLDatabaseName); transaction = connection.BeginTransaction(); }
public Boolean dbAtualizarContador(INFOParametros pObjParametros, MySqlTransaction pObjTrans) { StringBuilder strSQL = null; double contador = pObjParametros.NumeroContador + 1; try { strSQL = new StringBuilder(); strSQL.AppendLine("UPDATE parametros SET "); strSQL.AppendLine(" NR_CONTADOR = " + contador); if (dbExecutarQuery(strSQL.ToString(), pObjTrans) > 0) { return true; } else { return false; } } catch (Exception ex) { throw ex; } finally { strSQL = null; } }
private static MySqlDataAdapter AdaptadorInsert(MySqlConnection SqlConnection1, MySqlTransaction tr) { SqlDataAdapter1 = new MySqlDataAdapter(); SqlInsertCommand1 = new MySqlCommand("Articulos_Insertar", SqlConnection1); SqlInsertCommand1.Transaction = tr; SqlDataAdapter1.InsertCommand = SqlInsertCommand1; // IMPLEMENTACIÓN DE LA ORDEN INSERT SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.VarChar, 55, "IdArticuloART"); SqlInsertCommand1.Parameters.Add("p_idItem", MySqlDbType.Int32, 11, "IdItemART"); SqlInsertCommand1.Parameters.Add("p_idColor", MySqlDbType.Int32, 11, "IdColorART"); SqlInsertCommand1.Parameters.Add("p_idAlicuota", MySqlDbType.Int16, 3, "IdAliculotaIvaART"); SqlInsertCommand1.Parameters.Add("p_talle", MySqlDbType.VarChar, 50, "TalleART"); SqlInsertCommand1.Parameters.Add("p_idProveedor", MySqlDbType.Int32, 11, "IdProveedorART"); SqlInsertCommand1.Parameters.Add("p_descripcion", MySqlDbType.VarChar, 55, "DescripcionART"); SqlInsertCommand1.Parameters.Add("p_descripcionWeb", MySqlDbType.VarChar, 50, "DescripcionWebART"); SqlInsertCommand1.Parameters.Add("p_precioCosto", MySqlDbType.Decimal, 19, "PrecioCostoART"); SqlInsertCommand1.Parameters.Add("p_precioPublico", MySqlDbType.Decimal, 19, "PrecioPublicoART"); SqlInsertCommand1.Parameters.Add("p_precioMayor", MySqlDbType.Decimal, 19, "PrecioMayorART"); SqlInsertCommand1.Parameters.Add("p_fecha", MySqlDbType.DateTime, 19, "FechaART"); SqlInsertCommand1.Parameters.Add("p_imagen", MySqlDbType.VarChar, 50, "ImagenART"); SqlInsertCommand1.Parameters.Add("p_imagenBack", MySqlDbType.VarChar, 50, "ImagenBackART"); SqlInsertCommand1.Parameters.Add("p_imagenColor", MySqlDbType.VarChar, 50, "ImagenColorART"); SqlInsertCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebART"); SqlInsertCommand1.Parameters.Add("p_nuevo", MySqlDbType.Int32, 1, "NuevoART"); SqlInsertCommand1.CommandType = CommandType.StoredProcedure; return SqlDataAdapter1; }
public void Execute(MySqlConnection connection, MySqlTransaction transaction) { CallProcInsertWorldEntry(connection, transaction, mDungeonList.World); foreach (DataDungeon dungeon in mDungeonList.Dungeons) CallProcInsertDungeonEntry(connection, transaction, mDungeonList.World, dungeon); }
private void btDelete_Click(object sender, EventArgs e) { if ((dgvDirections.SelectedRows.Count == 0) || (dgvDirections.SelectedRows[0].Cells[dgvDirections_Type.Index].Value.ToString() == "Н")) { MessageBox.Show("Выберите профиль"); } else if (SharedClasses.Utility.ShowChoiceMessageBox("Удалить выбранный профиль?", "Удаление профиля")) { try { _DB_Connection.Delete(DB_Table.PROFILES, new Dictionary <string, object> { { "faculty_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }); UpdateTable(); } catch (MySqlException ex) { if (ex.Number == 1217 || ex.Number == 1451) { List <object[]> appEntrances = _DB_Connection.Select(DB_Table.APPLICATIONS_ENTRANCES, new string[] { "application_id" }, new List <Tuple <string, Relation, object> > { new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value), new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value), new Tuple <string, Relation, object>("profile_short_name", Relation.EQUAL, dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value) }); if (appEntrances.Count > 0) { MessageBox.Show("На данный профиль подано заявление. Удаление невозможно."); } else if (SharedClasses.Utility.ShowChoiceMessageWithConfirmation("Профиль включен в кампанию. Выполнить удаление?", "Связь с кампанией")) { using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction()) { _DB_Connection.Delete(DB_Table.CAMPAIGNS_PROFILES_DATA, new Dictionary <string, object> { { "profiles_direction_faculty", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "profiles_direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "profiles_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }, transaction); _DB_Connection.Delete(DB_Table.PROFILES, new Dictionary <string, object> { { "faculty_short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_FacultyName.Index].Value }, { "direction_id", dgvDirections.SelectedRows[0].Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.SelectedRows[0].Cells[dgvDirections_ShortName.Index].Value } }, transaction); transaction.Commit(); } UpdateTable(); } } } } }
public static string ExecuteScalar(string SQL, MySqlConnection conn, MySqlTransaction tran) { if (conn.State != ConnectionState.Open) conn.Open(); MySqlCommand cmd = new MySqlCommand(SQL, conn, tran); return cmd.ExecuteScalar().ToString(); }
/// <summary> /// Sets the auto commit of the current item /// </summary> /// <param name="commit">The new autocommit value</param> private void initTransaction() { this.command = client.getNewCommand(); this.transaction = client.getTransaction(); this.command.Transaction = transaction; this.command.Connection = transaction.Connection; this.finishedTransaction = false; }
public void Close() { if (conn != null && conn.State == ConnectionState.Open) { if (trans != null && useTrans) { trans.Commit(); trans = null; } conn.Close(); } }
public void Commit(int id) { if (this.transaction != null && id == transactionId && id != -1) { transaction.Commit (); this.CloseConnection (); transaction = null; } }
private void initTransaction() { base.command = base.client.createNewCommand(); this.transaction = base.client.getTransaction(); base.command.Transaction = this.transaction; base.command.Connection = this.transaction.Connection; this.finishedTransaction = false; }
public void Commit() { if (_tran != null) { _tran.Commit(); _tran.Dispose(); _tran = null; } }
public void CommitTran() { if (_tran != null) { _tran.Commit(); _tran = null; } }
public void CommitTran() { if (tran != null) { tran.Commit(); tran = null; } }
public void RollBackTran() { if (tran != null) { tran.Rollback(); tran = null; } }
public int BeginTransaction() { if (this.transaction == null) { this.OpenConnection (); this.transaction = connection.BeginTransaction (); return ++transactionId; } return -1; }
public void CommitTransaction() { if (_trans != null) { _trans.Commit(); _trans.Dispose(); _trans = null; } else throw new TransException("didn't have transaction to do it"); }
private int CallProcRecordBattleEncounter(MySqlConnection connection, MySqlTransaction transaction, DataActiveBattle battle) { using (MySqlCommand command = new MySqlCommand("record_battle_encounter", connection, transaction)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@battle_id", battle.BattleId); return command.ExecuteNonQuery(); } }
private void InitTransaction() { CommandMySql = Client.CreateNewCommandMySql(); _transactionmysql = Client.GetTransactionMySql(); CommandMySql.Transaction = _transactionmysql; CommandMySql.Connection = _transactionmysql.Connection; _finishedTransaction = false; }
public static void ExecuteNonQuery(string SQL, MySqlConnection conn, MySqlTransaction tran) { if (conn.State != ConnectionState.Open) conn.Open(); MySqlCommand cmd = new MySqlCommand(SQL, conn, tran); cmd.ExecuteNonQuery(); cmd.Dispose(); }
void IPromotableSinglePhaseNotification.Initialize() { string valueName = Enum.GetName( typeof(System.Transactions.IsolationLevel), baseTransaction.IsolationLevel); System.Data.IsolationLevel dataLevel = (System.Data.IsolationLevel)Enum.Parse( typeof(System.Data.IsolationLevel), valueName); simpleTransaction = connection.BeginTransaction(dataLevel); }
public MySqlCommand GetCommand(string sql, MySqlTransaction trans) { //Command MySqlCommand command = new MySqlCommand(); command.CommandText = sql; command.Connection = trans.Connection; command.Transaction = trans; return command; }
/// <summary> /// Stop the current transaction and close the connection. /// </summary> public void EndTransaction() { Contract.Requires(this.TransactionStarted()); Contract.Ensures(!this.TransactionStarted()); transaction.Commit(); transaction = null; connection.Close(); }
int CallProcInsertEnemyEntry(MySqlConnection connection, MySqlTransaction transaction, uint EnemyId, string EnemyName) { using (MySqlCommand command = new MySqlCommand("insert_enemy_entry", connection, transaction)) { command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@enemy_id", EnemyId); command.Parameters.AddWithValue("@enemy_name", EnemyName); return command.ExecuteNonQuery(); } }
private void ResetSectorizacion(MySql.Data.MySqlClient.MySqlTransaction trans, string idsistema) { Sectorizaciones sctz = new Sectorizaciones(); sctz.IdSistema = idsistema; sctz.IdSectorizacion = "TEMPORARY_CONTROLLER_SCTZ"; GestorBDCD40.DeleteSQL(sctz, trans); GestorBDCD40.InsertSQL(sctz, trans); }
public static void AsignaDestinoARecurso(ParametrosRecursoGeneral t, MySql.Data.MySqlClient.MySqlTransaction tran) { try { CD40.BD.Utilidades u = new CD40.BD.Utilidades(tran.Connection); u.UpdateDestinoSQL(t, tran); } catch (MySql.Data.MySqlClient.MySqlException) { } }
/// <summary> /// 执行事务 /// </summary> /// <param name="trans">MySql.Data.MySqlClient.MySqlTransaction对象</param> /// <param name="cmdType">命令类型(存储过程或SQL语句)</param> /// <param name="cmdText">SQL语句或存储过程名</param> /// <param name="cmdParms">MySql.Data.MySqlClient.MySqlCommand参数数组</param> /// <returns>返回受引响的记录行数</returns> public static int ExecuteNonQuery(MySql.Data.MySqlClient.MySqlTransaction trans, CommandType cmdType, string cmdText, params MySql.Data.MySqlClient.MySqlParameter[] cmdParms) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandTimeout = 600; PrepareCommand(trans.Connection, trans, cmd, cmdType, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return(val); }
public static void LiberaDestinoDeRecurso(ParametrosRecursoGeneral t, MySql.Data.MySqlClient.MySqlTransaction tran) { try { CD40.BD.Utilidades u = new CD40.BD.Utilidades(tran.Connection); //MySql.Data.MySqlClient.MySqlTransaction trans = GestorBDCD40.StartTransaction(true); u.LiberaDestinoSQL(t, tran); //GestorBDCD40.Commit(trans); } catch (MySql.Data.MySqlClient.MySqlException) { } }
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"); }
private void GenerarTop(MySql.Data.MySqlClient.MySqlTransaction trans, string idsistema, string nucleo, KeyValuePair <string, string[]> top) { try { if (top.Value.Length > 0) { string nomSector = DameNombreSector(trans, top.Value, idsistema, nucleo); if (nomSector != "") { // Crear el nuevo sector GeneraSector(trans, idsistema, nucleo, nomSector, top.Value); // Insertar el nuevo sector en la sectorización SectoresSectorizacion ss = new SectoresSectorizacion(); ss.IdSistema = idsistema; ss.IdSectorizacion = "TEMPORARY_CONTROLLER_SCTZ"; ss.IdTop = top.Key; ss.IdNucleo = nucleo; ss.IdSector = nomSector; GestorBDCD40.InsertSQL(ss, trans); // Crear el nuevo SectoresSector GeneraSectoresSector(trans, idsistema, nucleo, nomSector, top); } } else { string nomSector = "**FS**"; // Crear el nuevo sector GeneraSector(trans, idsistema, nucleo, nomSector, null); // Insertar el nuevo sector en la sectorización SectoresSectorizacion ss = new SectoresSectorizacion(); ss.IdSistema = idsistema; ss.IdSectorizacion = "TEMPORARY_CONTROLLER_SCTZ"; ss.IdTop = top.Key; ss.IdNucleo = nucleo; ss.IdSector = nomSector; GestorBDCD40.InsertSQL(ss, trans); } } catch (Exception) { } }
// 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); }
private void GeneraSector(MySql.Data.MySqlClient.MySqlTransaction trans, string idSistema, string idNucleo, string nomSector, string[] listSectores) { try { //ServiciosCD40.ServiciosCD40 g = new ServiciosCD40.ServiciosCD40(); Sectores s = new Sectores(); s.IdSistema = idSistema; s.IdSector = nomSector; s.IdNucleo = idNucleo; // Comprobar que esta combinación de sectores no existe en la tabla Sectores // en cuyo caso no hay nada que generar if (GestorBDCD40.ListSelectSQL(s, trans).Count > 0) { return; } s.IdParejaUCS = s.IdNucleoParejaUCS = s.IdSistemaParejaUCS = null; s.SectorSimple = false; s.Tipo = "R"; s.TipoPosicion = "C"; // Obtener literales de los usuarios if (listSectores != null) { System.Text.StringBuilder lista = new System.Text.StringBuilder(); foreach (string user in listSectores) { lista.AppendFormat("'{0}',", user); } lista = lista.Remove(lista.Length - 1, 1); s.PrioridadR2 = GetPrioridadSector(s.IdSistema, s.IdNucleo, lista.ToString()); } s.TipoHMI = 0; s.NumSacta = 0; GestorBDCD40.InsertSQL(s, trans); } catch (Exception) { } }
// copied & adapted from MySqlHelper public static int ExecuteNonQuery(MSC.MySqlConnection connection, MSC.MySqlTransaction trx, string commandText, params MSC.MySqlParameter[] commandParameters) { var mySqlCommand = new MSC.MySqlCommand(); mySqlCommand.Connection = connection; if (trx != null) { mySqlCommand.Transaction = trx; } mySqlCommand.CommandText = commandText; mySqlCommand.CommandType = CommandType.Text; if (commandParameters != null) { foreach (var commandParameter in commandParameters) { mySqlCommand.Parameters.Add(commandParameter); } } var num = mySqlCommand.ExecuteNonQuery(); mySqlCommand.Parameters.Clear(); return(num); }
// copied & adapted from MySqlHelper private static object ExecuteScalar(MSC.MySqlConnection connection, MSC.MySqlTransaction trx, string commandText, params MSC.MySqlParameter[] commandParameters) { var mySqlCommand = new MSC.MySqlCommand(); mySqlCommand.Connection = connection; if (trx != null) { mySqlCommand.Transaction = trx; } mySqlCommand.CommandText = commandText; mySqlCommand.CommandType = CommandType.Text; if (commandParameters != null) { foreach (var commandParameter in commandParameters) { mySqlCommand.Parameters.Add(commandParameter); } } var obj = mySqlCommand.ExecuteScalar(); mySqlCommand.Parameters.Clear(); return(obj); }
//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); }
public string ChangeStatus(string ma_don_hang, string trang_thai_don_hang, string Username, string connectstring) { using (var dbConn = new OrmliteConnection().openConn(connectstring)) { { try { var exist = dbConn.FirstOrDefault <Merchant_OrderHeader>("ma_don_hang={0}".Params(ma_don_hang)); if (exist != null) { if (exist.trang_thai_don_hang == "New" && trang_thai_don_hang == "Confirm") { exist.trang_thai_don_hang = "Confirm"; } if (exist.trang_thai_don_hang == "Confirm" && trang_thai_don_hang == "Shipping") { exist.trang_thai_don_hang = "Shipping"; } if (exist.trang_thai_don_hang == "Shipping" && trang_thai_don_hang == "POD") { exist.trang_thai_don_hang = "POD"; } if (exist.trang_thai_don_hang == "POD" && trang_thai_don_hang == "Completed") { exist.trang_thai_don_hang = "Completed"; } exist.ngay_cap_nhat = DateTime.Now; exist.nguoi_cap_nhat = Username; dbConn.UpdateOnly(exist, onlyFields: p => new { p.trang_thai_don_hang, p.nguoi_cap_nhat, p.ngay_cap_nhat }, where : p => p.ma_don_hang == exist.ma_don_hang); if (exist.trang_thai_don_hang == "Confirm") { List <SqlParameter> param = new List <SqlParameter>(); //Update Merchant_Product_Warehouse param.Add(new SqlParameter("@ma_don_hang", exist.ma_don_hang)); new SqlHelper(connectstring).ExecuteQuery("p_Update_Merchant_Product_Warehouse_Order", param); } using (MySqlConnection con = new MySqlConnection(AppConfigs.FEConnectionString)) { con.Open(); string sqlOrder = @"update order_merchant set trang_thai_don_hang={0}, ngay_cap_nhat={1}, nguoi_cap_nhat={2} where ma_don_hang={3} and ma_gian_hang={4}".Params(exist.trang_thai_don_hang, exist.ngay_cap_nhat, exist.nguoi_cap_nhat, exist.ma_don_hang_cha, exist.ma_gian_hang); using (MySql.Data.MySqlClient.MySqlTransaction trans = con.BeginTransaction()) { try { using (MySqlCommand cmd = new MySqlCommand(sqlOrder, con, trans)) { cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception e) { trans.Rollback(); return(e.Message.ToString()); } } } } } catch (Exception e) { return("false@@" + e.Message); } } } return("true@@" + trang_thai_don_hang); }
public 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); }
//Actualiza en la base de datos public override void update(String table, List <Tuple <String, Object> > pks, List <Tuple <String, Object> > fields) { MySqlConnection conn = new MySqlConnection(Db.getConnectionString()); conn.Open(); MySqlCommand command = conn.CreateCommand(); MySql.Data.MySqlClient.MySqlTransaction transaction = conn.BeginTransaction(); command.Connection = conn; command.Transaction = transaction; try { String query = "Update " + table + " Set "; for (int i = 0; i < fields.Count; i++) { query += fields[i].Item1 + " = @" + fields[i].Item1; if (i != fields.Count - 1) { query += " , "; } } query += " where "; for (int i = 0; i < pks.Count; i++) { query += pks[i].Item1 + " = @" + pks[i].Item1; if (i != pks.Count - 1) { query += " and "; } } query += ";"; command.CommandText = query; command.Prepare(); for (int i = 0; i < fields.Count; i++) { command.Parameters.AddWithValue("@" + fields[i].Item1, fields[i].Item2); } for (int i = 0; i < pks.Count; i++) { command.Parameters.AddWithValue("@" + pks[i].Item1, pks[i].Item2); } command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception e) { try { transaction.Rollback(); } catch (Exception ex) { if (transaction.Connection != null) { throw new GeneralORMException("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } throw new GeneralORMException("An exception of type " + e.GetType() + " was encountered while updating the data."); } finally { conn.Close(); } }
public void BeginTransaction() { Open(); transaction = connection.BeginTransaction(); }
public void BeginTran() { tran = conn.BeginTransaction(); }
public void Guardar(ref MySql.Data.MySqlClient.MySqlConnection cn, ref MySql.Data.MySqlClient.MySqlTransaction tr) { IGlobal.guardar_datos(_SQL, ref this._datos, ref cn, ref tr); }
//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); }
//public static readonly string DBConnectionStringMatch = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlconntionMatch"].ToString(); //public static readonly string DatabaseName = System.Configuration.ConfigurationManager.AppSettings["DatabaseName"].ToString(); //public static readonly string DatabaseNameMatch = System.Configuration.ConfigurationManager.AppSettings["DatabaseNameMatch"].ToString(); #endregion #region PrepareCommand /// <summary> /// Command预处理 /// </summary> /// <param name="conn">MySqlConnection对象</param> /// <param name="trans">MySql.Data.MySqlClient.MySqlTransaction对象,可为null</param> /// <param name="cmd">MySql.Data.MySqlClient.MySqlCommand对象</param> /// <param name="cmdType">CommandType,存储过程或命令行</param> /// <param name="cmdText">SQL语句或存储过程名</param> /// <param name="cmdParms">MySql.Data.MySqlClient.MySqlCommand参数数组,可为null</param> private static void PrepareCommand(MySql.Data.MySqlClient.MySqlConnection conn, MySql.Data.MySqlClient.MySqlTransaction trans, MySql.Data.MySqlClient.MySqlCommand cmd, CommandType cmdType, string cmdText, MySql.Data.MySqlClient.MySqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = cmdType; if (cmdParms != null) { foreach (MySql.Data.MySqlClient.MySqlParameter parm in cmdParms) { cmd.Parameters.Add(parm); } } }
//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); }
/// <summary> /// 开始事务处理 /// </summary> public static void BeginTransaction() { OpenConnecion(); trans = con.BeginTransaction(); m_isTransaction = true; }
//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); }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; MySqlClient.MySqlConnection myConnection = null; MySqlClient.MySqlTransaction myTransaction = null; try { // Create a new connection. using (myConnection = new MySqlClient.MySqlConnection(connectionString)) { // Open the connection. myConnection.Open(); // Start a new transaction. myTransaction = myConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new MySqlClient.MySqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.MySqlDataType, commandText), myConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = myTransaction; if (values != null) { foreach (MySqlClient.MySqlParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. myTransaction.Commit(); // Close the database connection. myConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (myTransaction != null) { myTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (myConnection != null) { myConnection.Close(); } } }
/// <summary> /// 执行Sql语句 /// </summary> /// <param name="strSql">sql语句(insert update delete)</param> public static int UpdateQuery(string strSql, MySql.Data.MySqlClient.MySqlConnection con, MySql.Data.MySqlClient.MySqlTransaction trans) { int iReturn; // 打开 MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.Connection = con; cmd.Transaction = trans; cmd.CommandText = strSql; iReturn = cmd.ExecuteNonQuery(); if (!m_isTransaction) { CloseConnection(); } return(iReturn); }