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