private void init(string connString)
{
connection = new MySqlConnection(connString);
connection.Open();
connection.ChangeDatabase(Server.MySQLDatabaseName);
transaction = connection.BeginTransaction();
}
/// <summary>
/// Execute a non-query on the database and return the generic type specified
/// </summary>
/// <typeparam name="T">Return type - only accepts String or Int</typeparam>
/// <param name="EstablishedConnection">An open and established connection to a MySQL database</param>
/// <param name="QueryString">SQL query to execute</param>
/// <param name="Parameters">Dictionary of named parameters</param>
/// <param name="ThrowException">Throw exception or swallow and return null</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
/// <returns>Data in the type specified</returns>
internal static T DoDatabaseWork <T>(MySqlConnection EstablishedConnection, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null)
{
return(DoDatabaseWork <T>(EstablishedConnection, QueryString,
(cmd) =>
{
cmd.Parameters.AddAllParameters(Parameters);
var executionWork = cmd.ExecuteNonQuery();
if (typeof(T) == typeof(string))
{
return executionWork.ToString();
}
else if (typeof(T) == typeof(int))
{
return executionWork;
}
else
{
return default;
}
},
ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction));
}
/// <summary>
/// Execute a non-query on the database with the specified parameters without returning a value
/// </summary>
/// <param name="EstablishedConnection">An open and established connection to a MySQL database</param>
/// <param name="QueryString">SQL query to execute</param>
/// <param name="Parameters">Dictionary of named parameters</param>
/// <param name="ThrowException">Throw swallow exception</param>
internal static void DoDatabaseWork(MySqlConnection EstablishedConnection, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null)
{
DoDatabaseWork(EstablishedConnection, QueryString,
(cmd) =>
{
cmd.Parameters.AddAllParameters(Parameters);
return(cmd.ExecuteNonQuery());
},
ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction);
}
/// <summary>
/// Execute a query on the database using the provided function, returning value of type T
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="ConfigConnectionString">A ConnectionStringTypes type to reference a connection string defined in web.config</param>
/// <param name="QueryString">SQL query to retrieve the data requested</param>
/// <param name="ActionCallback">Customized function to execute when connected to the database</param>
/// <param name="ThrowException">Throw exception or swallow and return default(T)</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
/// <returns>Data of any type T</returns>
internal static T DoDatabaseWork <T>(Enum ConfigConnectionString, string QueryString, Func <MySqlCommand, object> ActionCallback, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null, bool AllowUserVariables = false)
{
using (var conn = ConnectionHelper.GetConnectionFromString(ConfigConnectionString, AllowUserVariables))
{
return(DoDatabaseWork <T>(conn, QueryString, ActionCallback, ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction));
}
}
/// <summary>
/// Execute a query on the database using the provided function without returning a value
/// </summary>
/// <param name="ConfigConnectionString">A ConnectionStringTypes type to reference a connection string defined in web.config</param>
/// <param name="QueryString">SQL query to execute</param>
/// <param name="ActionCallback">Customized function to execute when connected to the database</param>
/// <param name="ThrowException">Throw or swallow exception</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
internal static void DoDatabaseWork(Enum ConfigConnectionString, string QueryString, Func <MySqlCommand, object> ActionCallback, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null, bool AllowUserVariables = false)
{
DoDatabaseWork <object>(ConfigConnectionString, QueryString, ActionCallback, ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction, AllowUserVariables: AllowUserVariables);
}
public Boolean NuevoEmpleado(DataTable dtEmpleado, DataTable dtDireccion, DataTable dtRh)
{
Boolean Valor = false;
int idEmpleado = 0;
MySqlConnection cnObj = new MySqlConnection();
cnObj = objConexion.Conectar();
if (cnObj != null)
{
MySqlTransaction Trans = cnObj.BeginTransaction();
MySqlCommand cmdObj = new MySqlCommand();
cmdObj.Connection = cnObj;
string strSql;
strSql = "SELECT ";
strSql += "MAX(id_empleado + 1) ";
strSql += "FROM ";
strSql += "empleado";
cmdObj.CommandText = strSql;
MySqlDataReader rdrObj = cmdObj.ExecuteReader();
while (rdrObj.Read())
{
idEmpleado = Convert.ToInt32(rdrObj[0].ToString());
}
rdrObj.Close();
foreach (DataRow dRowEmpleado in dtEmpleado.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "empleado ";
strSql += "(clave, ";
strSql += "a_paterno, ";
strSql += "a_materno, ";
strSql += "nombre, ";
strSql += "rfc, ";
strSql += "curp, ";
strSql += "nss, ";
strSql += "fecha_nac, ";
strSql += "alta, ";
strSql += "baja, ";
strSql += "estatus) ";
strSql += "VALUES ";
strSql += "('" + dRowEmpleado["clave"] + "', ";
strSql += "'" + dRowEmpleado["a_paterno"] + "', ";
strSql += "'" + dRowEmpleado["a_materno"] + "', ";
strSql += "'" + dRowEmpleado["nombre"] + "', ";
strSql += "'" + dRowEmpleado["rfc"] + "', ";
strSql += "'" + dRowEmpleado["curp"] + "', ";
strSql += "'" + dRowEmpleado["nss"] + "', ";
strSql += "'" + dRowEmpleado["fecha_nac"] + "', ";
strSql += "'" + dRowEmpleado["alta"] + "', ";
strSql += "'" + dRowEmpleado["baja"] + "', ";
strSql += "" + dRowEmpleado["estatus"] + ")";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
foreach (DataRow dRowDireccion in dtDireccion.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "direccion ";
strSql += "(id_empleado, ";
strSql += "calle, ";
strSql += "num_ext, ";
strSql += "num_int, ";
strSql += "colonia, ";
strSql += "cod_postal, ";
strSql += "id_pais, ";
strSql += "id_estado, ";
strSql += "id_ciudad, ";
strSql += "telefono, ";
strSql += "correo, ";
strSql += "celular, ";
strSql += "contacto) ";
strSql += "VALUES ";
strSql += "(" + idEmpleado + ", ";
strSql += "'" + dRowDireccion["calle"] + "', ";
strSql += "'" + dRowDireccion["num_ext"] + "', ";
strSql += "'" + dRowDireccion["num_int"] + "', ";
strSql += "'" + dRowDireccion["colonia"] + "', ";
strSql += "'" + dRowDireccion["cod_postal"] + "', ";
strSql += "" + dRowDireccion["id_pais"] + ", ";
strSql += "" + dRowDireccion["id_estado"] + ", ";
strSql += "" + dRowDireccion["id_ciudad"] + ", ";
strSql += "'" + dRowDireccion["telefono"] + "', ";
strSql += "'" + dRowDireccion["correo"] + "', ";
strSql += "'" + dRowDireccion["celular"] + "', ";
strSql += "'" + dRowDireccion["contacto"] + "')";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
foreach (DataRow dRowRh in dtRh.Rows)
{
strSql = "INSERT ";
strSql += "INTO ";
strSql += "datos_rh ";
strSql += "(id_empleado, ";
strSql += "id_banco, ";
strSql += "cuenta_banco, ";
strSql += "id_jornada, ";
strSql += "id_sucursal, ";
strSql += "id_departamento, ";
strSql += "id_puesto, ";
strSql += "salario_diario, ";
strSql += "id_metodo_pago, ";
strSql += "id_contrato, ";
strSql += "id_tipo_salario) ";
strSql += "VALUES ";
strSql += "(" + idEmpleado + ", ";
strSql += "" + dRowRh["id_banco"] + ", ";
strSql += "'" + dRowRh["cuenta_banco"] + "', ";
strSql += "" + dRowRh["id_jornada"] + ", ";
strSql += "" + dRowRh["id_sucursal"] + ", ";
strSql += "" + dRowRh["id_departamento"] + ", ";
strSql += "" + dRowRh["id_puesto"] + ", ";
strSql += "'" + dRowRh["salario_diario"] + "', ";
strSql += "" + dRowRh["id_metodo_pago"] + ", ";
strSql += "" + dRowRh["id_contrato"] + ", ";
strSql += "" + dRowRh["id_tipo_salario"] + ")";
cmdObj.CommandText = strSql;
try
{
cmdObj.ExecuteNonQuery();
}catch (MySqlException ex)
{
MessageBox.Show("Error al Ingresar los Datos, " + ex.Message, "Error Critico", MessageBoxButtons.OK, MessageBoxIcon.Error);
Trans.Rollback();
}
}
}
Trans.Commit();
}
}
cnObj.Close();
return(Valor);
}
public void iniciarTransacao()
{
transacao = conexao.BeginTransaction(); //'indico o inicio da transacao'
}
/// <summary>
/// 根据事务的锁定级别开始一个新事务
/// </summary>
/// <param name="isoLevel">事务锁定级别</param>
/// <returns>事务对象</returns>
public IDbTransaction BeginTransaction(System.Data.IsolationLevel isoLevel)
{
m_Transaction = m_Connection.BeginTransaction(isoLevel);
return(m_Transaction);
}
public void AddItem()
{
using (var con = new MySqlConnection(connst))
{
con.Open();
MySqlTransaction transaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
Guid id = Guid.NewGuid();
try
{
var query = "";
var finish = 0;
var cmd = new MySqlCommand(query, con);
cmd.CommandText = $@"INSERT INTO items (
Id,Owner,Title,Detail,StartTime,EndTime,StartPrice,NowPrice,DecidePrice,Category,Value,Finish
)
VALUES(
@Id,@Owner,@Title,@Detail,@StartTime,@EndTime,@StartPrice,@NowPrice,@DecidePrice,@Category,@Value,@Finish)";
cmd.Parameters.Add(new MySqlParameter("@Id", id));
cmd.Parameters.Add(new MySqlParameter("@Owner", this.Owner));
cmd.Parameters.Add(new MySqlParameter("@Title", this.Title));
cmd.Parameters.Add(new MySqlParameter("@Detail", this.Detail));
cmd.Parameters.Add(new MySqlParameter("@StartTime", this.StartTime));
cmd.Parameters.Add(new MySqlParameter("@EndTime", this.EndTime));
cmd.Parameters.Add(new MySqlParameter("@StartPrice", this.StartPrice));
cmd.Parameters.Add(new MySqlParameter("@NowPrice", this.StartPrice));
cmd.Parameters.Add(new MySqlParameter("@DecidePrice", this.DecidePrice));
cmd.Parameters.Add(new MySqlParameter("@Category", this.Category));
cmd.Parameters.Add(new MySqlParameter("@Value", this.Value));
cmd.Parameters.Add(new MySqlParameter("@Finish", finish));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
transaction.Rollback();
}
foreach (String path in this.PicPath)
{
try
{
var query = "";
var cmd = new MySqlCommand(query, con);
cmd.CommandText = $@"INSERT INTO images (
ItemID,path
)
VALUES(
@Id,@path)";
cmd.Parameters.Add(new MySqlParameter("@Id", id));
cmd.Parameters.Add(new MySqlParameter("@path", path));
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
transaction.Commit();
}
}
public void DeleteCharacterSummons(MySqlConnection connection, MySqlTransaction transaction, string characterId)
{
ExecuteNonQuery(connection, transaction, "DELETE FROM charactersummon WHERE characterId=@characterId", new MySqlParameter("@characterId", characterId));
}
internal void insertDB()
{
MySqlConnection con = null;
MySqlTransaction trans = null;
Dictionary <String, String> pwdictionary = passwordHash();
try
{
con = new MySqlConnection(
ConfigurationManager.ConnectionStrings["webapp"].ConnectionString);
con.Open();
trans = con.BeginTransaction();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = @"insert into
`fe-nutzer`(Loginname,Aktiv,Vorname,Nachname,Email,Algorithmus,Stretch,Salt,Hash)
values(@login,@aktiv,@vorname,@nachname,@email,@algorithmus,@stretch,@salt,@hash)";
cmd.Parameters.AddWithValue("login", this.loginname);
cmd.Parameters.AddWithValue("aktiv", false);
cmd.Parameters.AddWithValue("vorname", this.vorname);
cmd.Parameters.AddWithValue("nachname", this.nachname);
cmd.Parameters.AddWithValue("email", this.email);
cmd.Parameters.AddWithValue("algorithmus", pwdictionary["type"]);
cmd.Parameters.AddWithValue("stretch", pwdictionary["iteration"]);
cmd.Parameters.AddWithValue("salt", pwdictionary["salt"]);
cmd.Parameters.AddWithValue("hash", pwdictionary["hash"]);
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into
`fh-angehöriger`(FeNutzerFhAngeFk)
values(@FeNutzerFhAngeFk)";
cmd.Parameters.AddWithValue("FeNutzerFhAngeFk", cmd.LastInsertedId);
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into
`mitarbeiter`(NutzerFk,MA-Nummer,Telefon-Nummer,Büro)
values(@NutzerFk,@mnummer,@telefon,@buro)";
cmd.Parameters.AddWithValue("NutzerFk", cmd.LastInsertedId);
cmd.Parameters.AddWithValue("mnummer", this.manummer);
cmd.Parameters.AddWithValue("telefon", this.telefon);
cmd.Parameters.AddWithValue("buro", this.buro);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (MySqlException ex)
{
try
{
trans.Rollback();
}
catch (MySqlException ex1)
{
Console.WriteLine("Error: {0}", ex1.ToString());
}
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
if (con != null)
{
con.Close();
}
}
}
/// <summary>
/// 开始一个事务
/// </summary>
public override void BeginTrans()
{
trans = conn.BeginTransaction();
inTransaction = true;
}
} // end of cmd_hist
//////////////////////////////////////////////////////////////////////
//
// Function : cmd_send
//
// Purpose : process a "send" command
//
// Inputs : (none)
//
// Output : appropriate info
//
// Returns : nothing
//
// Example : cmd_send();
//
// Notes : (none)
//
//////////////////////////////////////////////////////////////////////
static public void cmd_send()
{
int amount = 0;
int new_balance = 0;
int new_balance2 = 0;
string username2 = "";
string sql = "";
SmartUser recipient;
MySqlConnection conn = null;
MySqlTransaction tr = null;
if (!data_fields.ContainsKey("username2"))
{
send_response(1, "username2 was not specified", "");
}
username2 = data_fields["username2"];
if (!user_index.ContainsKey(username2))
{
send_response(1, username2 + " is not a valid username", "");
}
int u_index = user_index[username2];
recipient = UsersList[u_index];
if (!data_fields.ContainsKey("amount"))
{
send_response(1, "amount was not specified", "");
}
if (Regex.IsMatch(data_fields["amount"], "^[0-9]+$"))
{
try
{
amount = Convert.ToInt32(data_fields["amount"]);
} catch (Exception ex)
{
send_response(1, "Invalid amount", ex.ToString());
}
}
else
{
send_response(1, "Invalid amount", "amount is not entirely numeric");
}
if (current_user.username == username2)
{
send_response(1, "You are not allowed to send to yourself", "");
}
if (current_user.balance < amount)
{
send_response(1, "Insufficient fundds", "try a smaller amount");
}
if (recipient.status != "active")
{
send_response(1, username2 + " is not an active user. Request denied.", "");
}
try
{
conn = new MySqlConnection(connect_string);
conn.Open();
tr = conn.BeginTransaction();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tr;
// First create the transaction history record
cmd.CommandText = "UPDATE Authors SET Name='Leo Tolstoy' WHERE Id=1";
new_balance = current_user.balance - amount;
new_balance2 = recipient.balance + amount;
sql = "INSERT INTO smart_users_history " +
"(mod_date,void_date,user1,user1_balance,user2,user2_balance,operation,status,amount) " +
"VALUES ( now() , now() , " + current_user.id + " , " + new_balance +
" , " + recipient.id + " , " + new_balance2 + " , 'send' , 'active' , " +
amount + ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// Next update the information for the sender
sql = "UPDATE smart_users set balance = " + new_balance +
" WHERE id = " + current_user.id;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
// Lastly update the information for the recipient
sql = "UPDATE smart_users set balance = " + new_balance2 +
" WHERE id = " + recipient.id;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
tr.Commit();
} catch (MySqlException ex)
{
try
{
tr.Rollback();
} catch (MySqlException ex1)
{
send_response(1, "Database Rollback Error" + ex1.ToString(), ex.ToString());
}
send_response(1, "Database Error", ex.ToString() + "\n" + sql);
} finally
{
if (conn != null)
{
conn.Close();
}
} // finally
send_response(0, "SUCCESS", "send request was successfull");
return;
} // cmd_send
public Tuple <bool, string> Post(RelocationEntity entity)
{
MySqlCommand cmd = new MySqlCommand();
// ManageSQLConnection sqlConnection = new ManageSQLConnection();
MySqlConnection sqlConnection = new MySqlConnection();
MySqlTransaction objTrans = null;
string connectionString = GlobalVariables.ConnectionString;
using (sqlConnection = new MySqlConnection(connectionString))
{
DataSet ds = new DataSet();
cmd = new MySqlCommand();
try
{
if (sqlConnection.State == 0)
{
sqlConnection.Open();
}
objTrans = sqlConnection.BeginTransaction();
cmd.Transaction = objTrans;
cmd.Connection = sqlConnection;
cmd.CommandText = "InsertRelocationDetails";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", entity.Id);
cmd.Parameters.AddWithValue("@Location", entity.Location);
cmd.Parameters.AddWithValue("@Rcode", entity.Rcode);
cmd.Parameters.AddWithValue("@Dcode", entity.Dcode);
cmd.Parameters.AddWithValue("@Status", entity.Status);
cmd.Parameters.AddWithValue("@ShopCode", entity.ShopCode);
cmd.Parameters.AddWithValue("@Reason", entity.Reason);
cmd.Parameters.AddWithValue("@FromAddress", entity.FromAddress);
cmd.Parameters.AddWithValue("@ToAddress", entity.ToAddress);
cmd.Parameters.AddWithValue("@DocDate", entity.DocDate);
cmd.Parameters.AddWithValue("@CompletedDate", entity.CompletedDate);
cmd.Parameters.AddWithValue("@NewShopNo", entity.NewShopNo);
cmd.ExecuteNonQuery();
objTrans.Commit();
cmd.Parameters.Clear();
cmd.Dispose();
//Mail sending
MailSending mail = new MailSending();
CommonEntity common = new CommonEntity
{
ToMailid = "*****@*****.**",
ToCC = "[email protected] ",
Subject = "Relocaiton request",
BodyMessage = "Hi Rajaram, <br/> Reason :" + entity.Reason + " <br/>"
+ "From Address = " + entity.FromAddress + "<br/>"
+ "To Address = " + entity.ToAddress + "<br/>"
+ "Relocation Date = " + entity.Dcode + "<br/><br/>"
+ "Regards" + "<br/>"
+ "SI Team"
};
mail.SendForAll(common);
return(new Tuple <bool, string>(true, JsonConvert.SerializeObject(ds)));
}
catch (Exception ex)
{
AuditLog.WriteError(ex.Message + " : " + ex.StackTrace);
objTrans.Rollback();
return(new Tuple <bool, string>(false, "Exception occured"));
}
finally
{
sqlConnection.Close();
cmd.Dispose();
ds.Dispose();
}
}
}
/// <summary>
/// 批量执行
/// </summary>
/// <param name="batchSize"></param>
/// <param name="timeout"></param>
public override void Execute(int batchSize = 10000, int timeout = 10 * 1000)
{
MySqlConnection newConnection = (MySqlConnection)_database.CreateConnection();
try
{
_dataTable = ToDataTable(_list);
if (_dataTable == null || _dataTable.Rows.Count == 0)
{
return;
}
string tmpPath = Path.Combine(Path.GetTempPath(), _dataTable.TableName + ".csv");
DBContext.WriteToCSV(_dataTable, tmpPath, false);
newConnection.Open();
using (MySqlTransaction tran = newConnection.BeginTransaction())
{
MySqlBulkLoader bulk = new MySqlBulkLoader(newConnection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
Local = true,
NumberOfLinesToSkip = 0,
TableName = _dataTable.TableName,
CharacterSet = "utf8"
};
try
{
bulk.Columns.AddRange(_dataTable.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
var size = bulk.Load();
tran.Commit();
}
catch (MySqlException ex)
{
if (tran != null)
{
tran.Rollback();
}
throw ex;
}
finally
{
File.Delete(tmpPath);
}
}
}
finally
{
if (newConnection.State == ConnectionState.Open)
{
newConnection.Close();
}
_list.Clear();
}
}
void RebuildRankMapping( Dictionary<int, Rank> databaseRankMapping, MySqlTransaction transaction ) {
// change all rank indices to temporary values
using( MySqlCommand updateRankIndexCmd = new MySqlCommand( UpdateRankIndexCommandText, connection, transaction ) ) {
updateRankIndexCmd.Parameters.Add( "newRank", MySqlType.SmallInt );
updateRankIndexCmd.Parameters.Add( "oldRank", MySqlType.SmallInt );
foreach( var pair in databaseRankMapping ) {
updateRankIndexCmd.Parameters[0].Value = GetRankTempIndex( pair.Value );
updateRankIndexCmd.Parameters[1].Value = pair.Key;
updateRankIndexCmd.ExecuteNonQuery();
}
}
// change all rank indices to new permanent values
transaction.ExecuteNonQuery( PermRankIncidesCommandText );
// change all previous_rank indices to temporary values
using( MySqlCommand updatePreviousRankIndexCmd = new MySqlCommand( UpdatePreviousRankIndexCommandText, connection, transaction ) ) {
updatePreviousRankIndexCmd.Parameters.Add( "newRank", MySqlType.SmallInt );
updatePreviousRankIndexCmd.Parameters.Add( "oldRank", MySqlType.SmallInt );
foreach( var pair in databaseRankMapping ) {
updatePreviousRankIndexCmd.Parameters[0].Value = GetRankTempIndex( pair.Value );
updatePreviousRankIndexCmd.Parameters[1].Value = pair.Key;
updatePreviousRankIndexCmd.ExecuteNonQuery();
}
}
// change all previous_rank indices to new permanent values
transaction.ExecuteNonQuery( PermPreviousRankIndicesCommandText );
// recreate the rank_mapping table
transaction.ExecuteNonQuery( TruncateRankMappingCommandText );
using( MySqlCommand addRankMappingCmd = new MySqlCommand( AddRankMappingCommandText, connection, transaction ) ) {
addRankMappingCmd.Parameters.Add( "index", MySqlType.SmallInt );
addRankMappingCmd.Parameters.Add( "name", MySqlType.VarChar, 64 );
foreach( var pair in rankMapping ) {
addRankMappingCmd.Parameters[0].Value = pair.Key;
addRankMappingCmd.Parameters[1].Value = pair.Value.FullName;
addRankMappingCmd.ExecuteNonQuery();
}
}
}
public UnitOfWork(string connectionString)
{
_cn = new MySqlConnection(connectionString);
_cn.Open();
_txn = _cn.BeginTransaction();
}
public Boolean saveGoodsEntryData(String TRDR_ID, String ITEM_NAME, String WT_BOX_TYPE, String WT_TXT, String LOT_1, String LOT_2, String LOT_3, String LOT_4, String LOT_5,
String LOGISTIC_DETAILS, String SHORT_BOX, String QTY_RCVD, String ORIGIN, String VEHICLE_NO, String FARE_RATE, String TOTAL_FARE, String RECV_DATE, String TRDR_FNAME_TMARK, String LOT_NO_SHORT)
{
this.TRDR_ID = TRDR_ID.Trim();
this.ITEM_NAME = ITEM_NAME.Trim();
this.WT_BOX_TYPE = WT_BOX_TYPE.Trim();
this.WT_TXT = WT_TXT.Trim();
this.LOT_1 = LOT_1;
this.LOT_2 = LOT_2;
this.LOT_3 = LOT_3;
this.LOT_4 = LOT_4;
this.LOT_5 = LOT_5;
this.LOGISTIC_DETAILS = LOGISTIC_DETAILS;
this.VEHICLE_NO = VEHICLE_NO;
this.FARE_RATE = FARE_RATE;
this.TOTAL_FARE = TOTAL_FARE;
this.SHORT_BOX = SHORT_BOX;
this.ORIGIN = ORIGIN;
this.RECV_DATE = RECV_DATE;
this.QTY_RCVD = QTY_RCVD;
this.TRDR_FNAME_TMARK = TRDR_FNAME_TMARK;
this.LOT_NO_SHORT = LOT_NO_SHORT;
//Entry of goods_entry details
String G_ENTRY_ID = "GETRYID" + GenerateUniqueID.RandomString(8);
try{
con = dataBaseConnection.getMySqlDBConnection();
con.Open();
MySqlCommand myCommand = con.CreateCommand();
// Start a local transaction
myTrans = con.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = con;
myCommand.Transaction = myTrans;
insertQuery = "insert into " + tableName + " values('" + G_ENTRY_ID + "','" + TRDR_ID + "','" + ITEM_NAME + "','" + WT_TXT + "','" + LOT_1
+ "','" + LOT_2 + "','" + LOT_3 + "','" + LOT_4 + "','" + LOT_5 + "','" + LOGISTIC_DETAILS + "','" + VEHICLE_NO + "','" + FARE_RATE + "','"
+ TOTAL_FARE + "','" + QTY_RCVD + "','" + ORIGIN + "','" + RECV_DATE + "','" + SHORT_BOX + "','" + WT_BOX_TYPE + "','" + TRDR_FNAME_TMARK + "','" + LOT_NO_SHORT + "');";
Console.WriteLine("Query:" + insertQuery);
if (!(dataAccessUtility.iscolumnAlreadyPresent(tableName, "G_ENTRY_ID", G_ENTRY_ID)))
{
insertData(insertQuery, con);
Console.WriteLine("Goods Entry Inserted");
}
else
{
throw (new CustomException("Some Thing Wrong !! Please Try Again"));
}
//Entry of stock details for each Lot
String stockTable = "stock_details";
String LOTID_1 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_2 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_3 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_4 = "LOT" + GenerateUniqueID.RandomString(8);
String LOTID_5 = "LOT" + GenerateUniqueID.RandomString(8);
String STOCK_ID = "STOCK" + GenerateUniqueID.RandomString(8);
String[] lotIds = new String[] { LOTID_1, LOTID_2, LOTID_3, LOTID_4, LOTID_5 };
String [] lotBoxes = new String[] { this.LOT_1, this.LOT_2, this.LOT_3, this.LOT_4, this.LOT_5 };
String SHORT_LOT_IND = "N";
if (!(String.IsNullOrEmpty(this.LOT_NO_SHORT)))
{
SHORT_LOT_IND = "Y," + lotIds[int.Parse(this.LOT_NO_SHORT) - 1];
}
insertQuery = "insert into " + stockTable + " values('" + G_ENTRY_ID + "','" + LOTID_1 + "','" + LOTID_2 + "','" + LOTID_3 + "','" + LOTID_4
+ "','" + LOTID_5 + "','" + STOCK_ID + "','" + SHORT_LOT_IND + "');";
if (!(dataAccessUtility.iscolumnAlreadyPresent(stockTable, "STOCK_ID", STOCK_ID)))
{
insertData(insertQuery, con);
Console.WriteLine("StockQuery:" + insertQuery);
}
else
{
MessageBox.Show("Some Thing Wrong !! Please Try Again");
return(false);
}
//Entry of lots details into lot details
String lotDetailsTable = "lot_status";
if (SHORT_LOT_IND.Contains("Y"))
{
int index = int.Parse(this.LOT_NO_SHORT) - 1;
int shortbox = int.Parse(this.SHORT_BOX);
lotBoxes[index] = (int.Parse(lotBoxes[index]) - shortbox).ToString();
}
for (int i = 0; i < lotIds.Length; i++)
{
int lotSerial = 0;
if (String.IsNullOrEmpty(lotBoxes[i]))
{
lotSerial = i + 1;
continue;
}
lotSerial = i + 1;
insertQuery = "insert into " + lotDetailsTable + " values('" + lotIds[i] + "','" + lotBoxes[i] + "','" + G_ENTRY_ID + "','0','" + lotBoxes[i] + "','" + lotSerial + "','N');";
insertData(insertQuery, con);
Console.WriteLine("Lot_details_Query:" + insertQuery);
}
myTrans.Commit();
Console.WriteLine("All records are written to database.");
finalResult = true;
return(finalResult);
}
catch (Exception e)
{
finalResult = false;
try
{
myTrans.Rollback();
return(finalResult);
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() + " was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
return(finalResult);
}
finally
{
con.Close();
}
}
private string UPDATE_ORDER()
{
MySqlCommand cmd = new MySqlCommand();
MySqlTransaction myTrans = null;
try
{
CONNECTION.open_connection();
cmd.Connection = CONNECTION.CON;
myTrans = CONNECTION.CON.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = myTrans;
cmd.CommandText = "UPDATE kot_order SET special_note=@special_note,tax_percentage=@tax_percentage,total_price=@total_price WHERE order_no=@order_no";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_no", LBL_ORDER_NO.Text);
cmd.Parameters.AddWithValue("@special_note", TXT_SPECIAL_NOTE.Text);
cmd.Parameters.AddWithValue("@tax_percentage", tax_amount);
cmd.Parameters.AddWithValue("@total_price", Convert.ToDouble(LBL_TOT_PRICE.Text));
cmd.ExecuteNonQuery();
using (MySqlDataAdapter adp = new MySqlDataAdapter("SELECT koi.item_stock_id,koi.order_qty,koi.unit_price,koi.total_price,i.qty_handle FROM kot_order_item koi JOIN stock s ON s.stock_id=koi.item_stock_id JOIN item i ON s.item_code=i.item_id WHERE koi.order_no=@order_no", CONNECTION.CON))
{
adp.SelectCommand.Parameters.Clear();
adp.SelectCommand.Parameters.AddWithValue("@order_no", LBL_ORDER_NO.Text);
DataTable tbl = new DataTable();
adp.Fill(tbl);
if (tbl.Rows.Count > 0)
{
foreach (DataRow row in tbl.Rows)
{
if (row.Field <string>(4) == "1")
{
cmd.CommandText = "UPDATE stock SET qty=qty+@order_qty WHERE stock_id=@stock_id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_qty", row.Field <double>(1));
cmd.Parameters.AddWithValue("@stock_id", row.Field <Int32>(0));
cmd.ExecuteNonQuery();
}
}
cmd.CommandText = "DELETE FROM kot_order_item WHERE order_no=@order_no";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_no", LBL_ORDER_NO.Text);
cmd.ExecuteNonQuery();
}
}
foreach (DataGridViewRow row in DGV_ORDER_ITEMS.Rows)
{
cmd.CommandText = "INSERT INTO kot_order_item(item_stock_id,order_qty,unit_price,total_price,order_no,cost_price) VALUES(@item_stock_id,@order_qty,@unit_price,@total_price,@order_no,@cost_price)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@item_stock_id", row.Cells[0].Value);
cmd.Parameters.AddWithValue("@order_qty", Convert.ToDouble(row.Cells[4].Value));
cmd.Parameters.AddWithValue("@unit_price", Convert.ToDouble(row.Cells[5].Value));
cmd.Parameters.AddWithValue("@total_price", Convert.ToDouble(row.Cells[6].Value));
cmd.Parameters.AddWithValue("@order_no", LBL_ORDER_NO.Text);
cmd.Parameters.AddWithValue("@cost_price", Convert.ToDouble(row.Cells[8].Value));
cmd.ExecuteNonQuery();
if (row.Cells[7].Value.ToString() == "1")
{
cmd.CommandText = "UPDATE stock SET qty=qty-@order_qty WHERE stock_id=@stock_id";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_qty", Convert.ToDouble(row.Cells[4].Value));
cmd.Parameters.AddWithValue("@stock_id", row.Cells[0].Value);
cmd.ExecuteNonQuery();
}
}
cmd.CommandText = "INSERT INTO kot_order_update_log(updated_by,updated_date,updated_time) VALUES(@updated_by,@updated_date,@updated_time)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@updated_by", CLS_CURRENT_LOGGER.LOGGED_IN_USERID);
cmd.Parameters.AddWithValue("@updated_date", DateTime.Now.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@updated_time", DateTime.Now.ToString("HH:mm:ss"));
cmd.ExecuteNonQuery();
myTrans.Commit();
return("done");
}
catch (Exception ex)
{
myTrans.Rollback();
MSGBOX mdg = new MSGBOX(MessageAlertHeder.Error(), ex.Message, MessageAlertImage.Error());
mdg.ShowDialog();
return("error");
}
}
/// <summary>
/// 检查更新Tags标签及关系，带事务
/// </summary>
public void Update(MySqlConnection conn, MySqlTransaction trans, string tags_title, int channel_id, int article_id)
{
int tagsId = 0;
//检查该Tags标签是否已存在
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id from " + databaseprefix + "article_tags");
strSql.Append(" where title=@title");
MySqlParameter[] parameters =
{
new MySqlParameter("@title", MySqlDbType.VarChar, 100)
};
parameters[0].Value = tags_title;
object obj1 = DbHelperMySql.GetSingle(conn, trans, strSql.ToString(), parameters);
if (obj1 != null)
{
//存在则将ID赋值
tagsId = Convert.ToInt32(obj1);
}
//如果尚未创建该Tags标签则创建
if (tagsId == 0)
{
StringBuilder strSql2 = new StringBuilder();
strSql2.Append("insert into " + databaseprefix + "article_tags(");
strSql2.Append("title,is_red,sort_id,add_time)");
strSql2.Append(" values (");
strSql2.Append("@title,@is_red,@sort_id,@add_time)");
strSql2.Append(";select @@IDENTITY");
MySqlParameter[] parameters2 =
{
new MySqlParameter("@title", MySqlDbType.VarChar, 100),
new MySqlParameter("@is_red", MySqlDbType.TinyText, 1),
new MySqlParameter("@sort_id", MySqlDbType.Int32, 4),
new MySqlParameter("@add_time", MySqlDbType.DateTime)
};
parameters2[0].Value = tags_title;
parameters2[1].Value = 0;
parameters2[2].Value = 99;
parameters2[3].Value = DateTime.Now;
object obj2 = DbHelperMySql.GetSingle(conn, trans, strSql2.ToString(), parameters2);
if (obj2 != null)
{
//插入成功后返回ID
tagsId = Convert.ToInt32(obj2);
}
}
//匹配Tags标签与文章之间的关系
if (tagsId > 0)
{
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "article_tags_relation(");
strSql3.Append("channel_id,article_id,tag_id)");
strSql3.Append(" values (");
strSql3.Append("@channel_id,@article_id,@tag_id)");
MySqlParameter[] parameters3 =
{
new MySqlParameter("@channel_id", MySqlDbType.Int32, 4),
new MySqlParameter("@article_id", MySqlDbType.Int32, 4),
new MySqlParameter("@tag_id", MySqlDbType.Int32, 4)
};
parameters3[0].Value = channel_id;
parameters3[1].Value = article_id;
parameters3[2].Value = tagsId;
DbHelperMySql.GetSingle(conn, trans, strSql3.ToString(), parameters3);
}
}
internal static MySqlParameter[] DeriveParameters(this MySqlConnection connection, MySqlCommand cmd, bool includeReturnValueParameter = false, MySqlTransaction transaction = null)
{
if (cmd == null)
{
throw new ArgumentNullException("SqlCommand");
}
var textParser = new ObjectNameParser(cmd.CommandText);
// Hack to check for schema name in the spName
string schemaName = "dbo";
string spName = textParser.UnquotedString;
int firstDot = spName.IndexOf('.');
if (firstDot > 0)
{
schemaName = cmd.CommandText.Substring(0, firstDot);
spName = spName.Substring(firstDot + 1);
}
var alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
try
{
MySqlCommandBuilder.DeriveParameters(cmd);
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
if (!includeReturnValueParameter && cmd.Parameters.Count > 0)
{
cmd.Parameters.RemoveAt(0);
}
MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (MySqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
/**
* 开始事务
* */
public void beginTrans()
{
this.openConn();
this.isTrans = true;
this.trans = this.conn.BeginTransaction();
}
public ProductSubAttribute(MySqlConnection Connection, MySqlTransaction Transaction)
: base(Connection, Transaction)
{
}
public static string Insert(string football_round_chain_id, string sys_user_id, string football_match_id, string match_result_id, string football_match_ids, string USER, string TOKEN)
{
if (!AccessToken.Read(USER, TOKEN))
{
return("登录超时");
}
IDictionary <string, string> fdict = new Dictionary <string, string>();
fdict.Add("id", football_round_chain_id);
fdict.Add("delete_flag", "IS NULL");
DataSet ds = DBHelper.Select("football_round_chain", "participant_amount,name", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("竞猜不存在");
}
string participant_amount = ds.Tables[0].Rows[0]["participant_amount"].ToString();
string football_round_chain_name = ds.Tables[0].Rows[0]["name"].ToString();
string[] football_match_idarr = football_match_ids.Split(',');
if (football_match_idarr.Length != Convert.ToInt32(participant_amount))
{
return("接龙长度异常");
}
fdict = new Dictionary <string, string>();
fdict.Add("id", sys_user_id);
fdict.Add("delete_flag", "IS NULL");
ds = DBHelper.Select("sys_user", "balance", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("用户不存在");
}
if (Convert.ToInt32(ds.Tables[0].Rows[0]["balance"]) < 6)
{
return("爱心不足6元");
}
using (MySqlConnection dbConnection = new MySqlConnection(DBHelper.strConnection))
{
dbConnection.Open();
using (MySqlTransaction trans = dbConnection.BeginTransaction())
{
IDictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("football_round_chain_id", football_round_chain_id);
dict.Add("sys_user_id", sys_user_id);
dict.Add("participant_need", (Convert.ToInt32(participant_amount) - 1).ToString());
dict.Add("chain_vote_status_id", "1");
dict.Add("vote_datetime", DateTime.Now.ToString());
DBHelper.Insert("football_round_chain_vote", dict, dbConnection, trans);
string football_round_chain_vote_id = DBHelper.SelectNewId(dbConnection, trans);
dict = new Dictionary <string, string>();
dict.Add("football_round_chain_vote_id", football_round_chain_vote_id);
dict.Add("sys_user_id", sys_user_id);
dict.Add("football_match_id", football_match_id);
dict.Add("match_result_id", match_result_id);
dict.Add("vote_datetime", DateTime.Now.ToString());
DBHelper.Insert("football_round_chain_vote_invite", dict, dbConnection, trans);
foreach (string football_match_idi in football_match_idarr)
{
if (football_match_idi.Equals(football_match_id))
{
continue;
}
dict = new Dictionary <string, string>();
dict.Add("football_round_chain_vote_id", football_round_chain_vote_id);
dict.Add("football_match_id", football_match_idi);
DBHelper.Insert("football_round_chain_vote_invite", dict, dbConnection, trans);
}
dict = new Dictionary <string, string>();
dict.Add("balance", "数字相减-6");
fdict = new Dictionary <string, string>();
fdict.Add("id", sys_user_id);
DBHelper.Update("sys_user", dict, fdict, "and", dbConnection, trans);
sys_user_balance_change.Insert(sys_user_id, "2", "-6", "发起接龙“" + football_round_chain_name + "”", "football_round_chain_vote", football_round_chain_vote_id, dbConnection, trans);
trans.Commit();
}
}
return(string.Empty);
}
/// <summary>
/// Execute a query on the database using the provided function without returning a value
/// </summary>
/// <param name="EstablishedConnection">An open and established connection to a MySQL database</param>
/// <param name="QueryString">SQL query to execute</param>
/// <param name="ActionCallback">Customized function to execute when connected to the database</param>
/// <param name="ThrowException">Throw or swallow exception</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
internal static void DoDatabaseWork(MySqlConnection EstablishedConnection, string QueryString, Func <MySqlCommand, object> ActionCallback, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null)
{
DoDatabaseWork <object>(EstablishedConnection, QueryString, ActionCallback, ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction);
}
public static string End(string id, string USER, string TOKEN)
{
if (!AccessToken.Read(USER, TOKEN))
{
return("登录超时");
}
IDictionary <string, string> fdict = new Dictionary <string, string>();
fdict.Add("football_round_chain_vote_id", id);
DataSet ds = DBHelper.Select("football_round_chain_vote_invite_view", "*", string.Empty, fdict, "and");
if (ds.Tables[0].Rows.Count == 0)
{
return("找不到该记录");
}
string price = ds.Tables[0].Rows[0]["price"].ToString();
string title = ds.Tables[0].Rows[0]["football_round_name"] + " " + ds.Tables[0].Rows[0]["football_round_chain_name"];
string chain_vote_status_id = "2";
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (dr["vote_sys_user_id"].ToString().Length == 0)
{
return("接龙未完成");
}
if (dr["match_result_id"].ToString().Length == 0)
{
return("仍有比赛未完成");
}
if (!dr["match_result_id"].Equals(dr["vote_match_result_id"]))
{
chain_vote_status_id = "3";
}
}
using (MySqlConnection dbConnection = new MySqlConnection(DBHelper.strConnection))
{
dbConnection.Open();
using (MySqlTransaction trans = dbConnection.BeginTransaction())
{
IDictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("chain_vote_status_id", chain_vote_status_id);
fdict = new Dictionary <string, string>();
fdict.Add("id", id);
DBHelper.Update("football_round_chain_vote", dict, fdict, "and", dbConnection, trans);
if (chain_vote_status_id.Equals("2") && (price.Length > 0))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
dict = new Dictionary <string, string>();
dict.Add("balance", "数字相加+" + price);
fdict = new Dictionary <string, string>();
fdict.Add("id", dr["vote_sys_user_id"].ToString());
DBHelper.Update("sys_user", dict, fdict, "and", dbConnection, trans);
sys_user_balance_change.Insert(dr["vote_sys_user_id"].ToString(), "2", price, title, "football_round_chain_vote", id, dbConnection, trans);
}
}
trans.Commit();
}
}
return(string.Empty);
}
/// <summary>
/// Execute a query on the database using the provided function, returning value of type T
/// </summary>
/// <typeparam name="T">Return type</typeparam>
/// <param name="EstablishedConnection">An open and established connection to a MySQL database</param>
/// <param name="QueryString">SQL query to retrieve the data requested</param>
/// <param name="ActionCallback">Customized function to execute when connected to the database</param>
/// <param name="ThrowException">Throw exception or swallow and return default(T)</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
/// <returns>Data of any type T</returns>
internal static T DoDatabaseWork <T>(MySqlConnection EstablishedConnection, string QueryString, Func <MySqlCommand, object> ActionCallback, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null)
{
var internalOpen = false; // indicates whether the connection was already open or not
var openedNewTransaction = false; // indicates whether a new transaction was created here or not
var currentTransaction = SqlTransaction; // preload current transaction
// reset the last execution error
LastExecutionException = null;
try
{
// if the connection isn't open, then open it and record that we did that
if (EstablishedConnection.State != ConnectionState.Open)
{
EstablishedConnection.Open();
internalOpen = true;
}
// if the caller wants to use transactions but they didn't provide one, create a new one
if (UseTransaction && SqlTransaction == null)
{
currentTransaction = EstablishedConnection.BeginTransaction();
openedNewTransaction = true;
}
// execute the SQL
using (var cmd = new MySqlCommand(QueryString, EstablishedConnection))
{
cmd.CommandTimeout = int.MaxValue;
// execute whatever code the caller provided
var result = (T)ActionCallback(cmd);
// if we opened the transaction here, just commit it because we're going to be closing it right away
if (openedNewTransaction)
{
currentTransaction?.Commit();
}
return(result);
}
}
catch (MySqlException mysqlEx) // use special handling for MySQL exceptions
{
// there was an error, roll back the transaction
if (UseTransaction)
{
currentTransaction?.Rollback();
}
// if we want exceptions to be thrown, rethrow the current one, otherwise just record the error
if (ThrowException)
{
throw new Exception(mysqlEx.Message, mysqlEx);
}
else
{
LastExecutionException = mysqlEx;
return(default);
/// <summary>
/// 执行操作，返回受影响的行数(存储过程)
/// </summary>
/// <param name="tran">已存在的事务</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="commandParameters">执行命令需要的参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQueryProc(MySqlTransaction trans, string cmdText, params MySqlParameter[] commandParameters)
{
return(ExecuteNonQuery(trans, CommandType.StoredProcedure, cmdText, commandParameters));
}
/// <summary>
/// Execute a non-query on the database and return the number of rows affected
/// </summary>
/// <typeparam name="T">Return type - only accepts String or Int</typeparam>
/// <param name="ConfigConnectionString">A ConnectionStringTypes type to reference a connection string defined in web.config</param>
/// <param name="QueryString">SQL query to execute</param>
/// <param name="Parameters">Dictionary of named parameters</param>
/// <param name="ThrowException">Throw exception or swallow and return null</param>
/// <param name="UseTransaction">Specify whether to use a transaction for this call</param>
/// <returns>Number of rows affected</returns>
internal static T DoDatabaseWork <T>(Enum ConfigConnectionString, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, bool UseTransaction = false, MySqlTransaction SqlTransaction = null, bool AllowUserVariables = false)
{
using (var conn = ConnectionHelper.GetConnectionFromString(ConfigConnectionString, AllowUserVariables))
{
return(DoDatabaseWork <T>(conn, QueryString, Parameters: Parameters, ThrowException: ThrowException, UseTransaction: UseTransaction || SqlTransaction != null, SqlTransaction: SqlTransaction));
}
}
/// <summary>
/// 执行MySql和Oracle滴混合事务
/// </summary>
/// <param name="list">SQL命令行列表</param>
/// <param name="oracleCmdSqlList">Oracle命令行列表</param>
/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
public static int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo myDE in list)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (isHave)
{
//引发事件
myDE.OnSolicitationEvent();
}
}
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
//return 0;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
//return 0;
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
//return 0;
}
continue;
}
int val = cmd.ExecuteNonQuery();
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
tx.Rollback();
throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
//return 0;
}
cmd.Parameters.Clear();
}
string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
if (!res)
{
tx.Rollback();
throw new Exception("执行失败");
// return -1;
}
tx.Commit();
return(1);
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
tx.Rollback();
throw e;
}
catch (Exception e)
{
tx.Rollback();
throw e;
}
}
}
void GenerateSchema( MySqlTransaction transaction ) {
using( MySqlCommand insertMetadataCmd = new MySqlCommand( InsertMetadataCommandText, connection, transaction ) ) {
insertMetadataCmd.Parameters.Add( "format_version", MySqlType.Int );
insertMetadataCmd.Parameters.Add( "server_version_string", MySqlType.VarChar, 255 );
insertMetadataCmd.Parameters.Add( "last_modified", DateType );
insertMetadataCmd.Parameters[0].Value = FormatVersion;
insertMetadataCmd.Parameters[1].Value = Updater.CurrentRelease.VersionString;
insertMetadataCmd.Parameters[2].Value = DateTime.UtcNow.ToUnixTime();
insertMetadataCmd.ExecuteNonQuery();
}
transaction.ExecuteNonQuery( PlayersTableSchema );
transaction.ExecuteNonQuery( RankMappingTableSchema );
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
public static int ExecuteSqlTran(System.Collections.Generic.List <CommandInfo> cmdList)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlTransaction trans = conn.BeginTransaction())
{
MySqlCommand cmd = new MySqlCommand();
try
{ int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
{
trans.Rollback();
return(0);
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
trans.Rollback();
return(0);
}
if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
trans.Rollback();
return(0);
}
continue;
}
int val = cmd.ExecuteNonQuery();
count += val;
if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
{
trans.Rollback();
return(0);
}
cmd.Parameters.Clear();
}
trans.Commit();
return(count); }
catch
{
trans.Rollback();
throw;
}
}
}
}
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, Object[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="MySqlTransactionalDataSource" /> class.
/// </summary>
/// <param name="dataSource">The data source.</param>
/// <param name="forwardEvents">if set to <c>true</c> [forward events].</param>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
internal MySqlTransactionalDataSource(MySqlDataSource dataSource, bool forwardEvents, MySqlConnection connection, MySqlTransaction transaction)
: base(new MySqlDataSourceSettings {
DefaultCommandTimeout = dataSource.DefaultCommandTimeout, StrictMode = dataSource.StrictMode, SuppressGlobalEvents = dataSource.SuppressGlobalEvents || forwardEvents
})
{
Name = dataSource.Name;
m_BaseDataSource = dataSource;
m_Connection = connection;
m_Transaction = transaction;
if (forwardEvents)
{
ExecutionStarted += (sender, e) => dataSource.OnExecutionStarted(e);
ExecutionFinished += (sender, e) => dataSource.OnExecutionFinished(e);
ExecutionError += (sender, e) => dataSource.OnExecutionError(e);
ExecutionCanceled += (sender, e) => dataSource.OnExecutionCanceled(e);
}
AuditRules = dataSource.AuditRules;
UserValue = dataSource.UserValue;
}
public override void Dispose()
{
transaction.Dispose();
connection.Dispose();
transaction = null;
connection = null;
}
public ITransaction NewTransaction()
{
MySqlTransaction transaction = new MySqlTransaction( transactionsContext.NewRow() );
transaction.Provider = this;
return transaction;
}
