| public BeginTransaction ( ) : MySql.Data.MySqlClient.MySqlTransaction | ||
| return | MySql.Data.MySqlClient.MySqlTransaction | |
public static bool SettingsSetPassword(string password)
{
using (MySqlConnection mySqlCon = new MySqlConnection(LiveDocs.backend.database.Database.ConnectionString))
{
mySqlCon.Open();
using (MySqlTransaction mySqlTransaction = mySqlCon.BeginTransaction())
{
using (MySqlCommand mySqlCommand = mySqlCon.CreateCommand())
{
mySqlCommand.Transaction = mySqlTransaction;
try
{
mySqlCommand.Parameters.AddWithValue("@password", password);
mySqlCommand.Parameters.AddWithValue("@username", HttpContext.Current.Session["username"]);
mySqlCommand.Parameters.AddWithValue("@token", HttpContext.Current.Session["token"]);
mySqlCommand.CommandText =
"UPDATE Users SET Password=Password(@password) WHERE UserName=@username AND LoginToken=@token";
mySqlCommand.ExecuteNonQuery();
mySqlTransaction.Commit();
}
catch (Exception)
{
mySqlTransaction.Rollback();
return false;
}
}
}
}
return true;
}
/// <summary>
/// Execute several commands on the same transaction
/// </summary>
/// <param name="commands"></param>
/// <returns></returns>
public Result executeBatch(List<MySqlCommand> commands)
{
Result result = new Result(true);
using (MySqlConnection cn = new MySqlConnection(DefaultConfig.ConnectionString))
{
cn.Open();
using (MySqlTransaction trans = cn.BeginTransaction())
{
try
{
foreach (MySqlCommand cmd in commands)
{
cmd.Transaction = trans;
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Log.error(ex, "Batch");
result.ErrorDetails = ex;
result.Success = false;
}
}
}
return result;
}
public static int ExecuteSqlTran(string[] strSQLS)
{
using (MySqlConnection conn = new MySqlConnection(connString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < strSQLS.Length; n++)
{
string strsql = strSQLS[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
throw;
}
finally
{
conn.Close();
}
}
}
public void EditSpecification(Specification s)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "EditPackageSpecification";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", s.ID);
cmd.Parameters.AddWithValue("PWeight", s.Weight);
cmd.Parameters.AddWithValue("DeimensionHeight", s.Height);
cmd.Parameters.AddWithValue("DimensionWidth", s.Width);
cmd.Parameters.AddWithValue("DimensionLength", s.Length);
cmd.ExecuteNonQuery();
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
}
}
}
/// <summary>
/// Voegt data toe aan een nieuwe regel in de database en commit deze transactie.
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParameters"></param>
/// <returns></returns>
//insert
public int Insert(string sql, List <MySqlParameter> sqlParameters)
{
int affected;
this.Open();
try
{
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (MySqlParameter param in sqlParameters)
{
cmd.Parameters.Add(param);
}
affected = cmd.ExecuteNonQuery();
transaction.Commit();
return(affected);
}
catch (Exception)
{
return(0);
}
finally
{
this.Close();
}
}
public static void LoadFileToDb(string connStr, string tableName, string filePath)
{
if (filePath == null || !File.Exists(filePath))
{
return;
}
var conn = new MySqlConnection(connStr);
var bl = new MySqlBulkLoader(conn)
{
TableName = tableName, FieldTerminator = ",", LineTerminator = "\r\n", FileName = filePath
};
conn.Open();
MySqlTransaction trans = conn.BeginTransaction();
try
{
// Upload data from file
int count = bl.Load();
trans.Commit();
conn.Close();
}
catch (Exception ex)
{
trans.Rollback();
}
}
public void ChangePassword(User u, String password)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "ChangePassword";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("pUID", u.username);
cmd.Parameters.AddWithValue("pPwd", password);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
public int CreateTransaction(Transaction t)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewTransaction";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DateOfOrder", t.DateOfOrder);
cmd.Parameters.AddWithValue("PurchaceID", t.OrderID);
cmd.Parameters.AddWithValue("CustomerID", t.CustomerID);
cmd.Parameters.AddWithValue("BankingID", t.BankID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateClientUser(ClientUser user)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewClientUser";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("AccountID", user.AccountID);
cmd.Parameters.AddWithValue("pName", user.Name);
cmd.Parameters.AddWithValue("pUsername", user.AccountID);
cmd.Parameters.AddWithValue("pPwd", user.Name);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
private void init(string connString) {
connection = new MySqlConnection(connString);
connection.Open();
connection.ChangeDatabase(Server.MySQLDatabaseName);
transaction = connection.BeginTransaction();
}
public void Commit()
{
var connectionString = repository.GetConnectionString();
using (var conn = new MySqlConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
foreach (var command in commands)
{
MysqlHelper.LogCommand(repository, command);
try
{
MysqlHelper.ResetParameterNullValue(command);
command.Transaction = trans;
command.Connection = conn;
command.ExecuteNonQuery();
}
catch (Exception e)
{
throw new KoobooException(e.Message + "SQL:" + command.CommandText, e);
}
}
trans.Commit();
}
}
//Execute post content events
foreach (var action in postActions)
{
action();
}
Clear();
}
public int CreateGoods(Goods g)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewGoods";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("ItemName", g.Name);
cmd.Parameters.AddWithValue("HandlingRequirements", g.Name);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateContact(Contact c)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewContact";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PForename", c.Forename);
cmd.Parameters.AddWithValue("PSurname", c.Surname);
cmd.Parameters.AddWithValue("JobTitle", c.Position);
cmd.Parameters.AddWithValue("TelNumber", c.PhoneNumber);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateUser(User u)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewUserAccount";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PUsername", u.username);
cmd.Parameters.AddWithValue("PPWD", u.password);
cmd.Parameters.AddWithValue("EmployeeID", u.EmployeeID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public void DeleteContact(int ID)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "DeleteContact";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("ContactID", ID);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
public int CreateRole(Role r)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewRole";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("RoleTile", r.Title);
cmd.Parameters.AddWithValue("AccessLevel", r.AccessLevel);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateBank(Bank b)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewBank";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SortCode", b.SortCode);
cmd.Parameters.AddWithValue("AccountNumber", b.ID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateAccountType(Account_Type a)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewAccountType";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("AccountName", a.Name);
cmd.Parameters.AddWithValue("PBenifit", a.Benefit);
cmd.Parameters.AddWithValue("PCost", a.Cost);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int ResultOfExeInsertSql(string sql)
{
int result = 0;
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connection);
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
mySqlCmd.CommandText = sql.ToString();
result = mySqlCmd.ExecuteNonQuery();
mySqlCmd.Transaction.Commit();
return result;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("InvoiceWinningNumberDao-->ResultOfExeInsertSql-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
public int CreateDepot(Depot d)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewRole";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepotName", d.DepotName);
cmd.Parameters.AddWithValue("FloorSpace", d.FloorSpace);
cmd.Parameters.AddWithValue("NumOfVehicles", d.NumVehicles);
cmd.Parameters.AddWithValue("AddressID", d.Address);
cmd.Parameters.AddWithValue("DepotManager", d.ManagerID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public MySqlTransaction BeginTransaction()
{
//Connection
MySqlConnection conn = new MySqlConnection(ConnectionString.Instance.String);
conn.Open();
return conn.BeginTransaction();
}
/// <summary>
/// 执行SQL语句，使用事务
/// </summary>
/// <param name="SQLString">SQL语句</param>
public static int ExecuteSql(string SQLString)
{
// SQLString = SQLString.Replace("'", "''").Replace(@"\",@"\\");
int result = -1;
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection;
MySqlTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
cmd.CommandText = SQLString;
result = cmd.ExecuteNonQuery();
tx.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException E)
{
result = -1;
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
return result;
}
}
//带参数的执行命令
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 int CreateMessage(Message message)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewMessage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("mText", message.MessageBody);
cmd.Parameters.AddWithValue("mEmail", message.Email);
cmd.Parameters.AddWithValue("mName", message.Name);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateNewDepartment(Department department)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepartmentTitle", department.Title);
cmd.Parameters.AddWithValue("AddressID", department.Address);
cmd.Parameters.AddWithValue("DepartmentHead", department.Head);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreatePackage(Package p)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", p.SpecificationID);
cmd.Parameters.AddWithValue("GoodsID", p.GoodsID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public void StartTran()
{
if (myTranOne == null)
{
connMysql.Open();
myTranOne = connMysql.BeginTransaction();
}
}
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
if (rolenames.Any(rolename => !RoleExists(rolename)))
{
throw new ProviderException("Role name not found.");
}
foreach (string username in usernames)
{
if (username.Contains(","))
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string r in rolenames)
{
if (IsUserInRole(username, r))
{
throw new ProviderException("User is already in role.");
}
}
}
var parameters = new[] {
new MySqlParameter("?roleName", MySqlDbType.VarChar),
new MySqlParameter("?userName", MySqlDbType.VarChar)
};
string query = @"INSERT INTO `UserRoles` (`id_user`, `id_role`) VALUES
((SELECT `id_user` FROM `Users` WHERE `userName` = ?userName LIMIT 1),
(SELECT `id_role` FROM `Roles` WHERE `name` = ?roleName LIMIT 1));";
//tak jest wydajniej o jakies 3,000 razy xD
using (var conn = new MySqlConnection(connectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
using (var cmd = new MySqlCommand(query, conn, trans))
{
cmd.Parameters.AddRange(parameters);
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
cmd.Parameters[0].Value = rolename;
cmd.Parameters[1].Value = username;
if (cmd.ExecuteNonQuery() == 0)
throw new ProviderException("Something has happened! User `" + username + "` failed in assignin to `" + rolename + "`!");
}
}
trans.Commit();
}
}
}
}
public QueryHelper()
{
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD="******";";
_connection = new MySqlConnection(connectionString);
_connection.Open();
//var setTimeZone = ConfigurationManager.AppSettings["mysqlTimeZoneConnection"];
//QueryInsertUpdateDelete(setTimeZone);
Transactions = _connection.BeginTransaction();
}
/// <summary>
/// 修改短信查车
/// </summary>
/// <param name="smsQuery"></param>
/// <returns></returns>
public void UpdateSmsVehicle(EMSMSQueryVehicle smsQuery)
{
MySqlConnection con = new MySqlConnection(MySqlDB.ConnString);
con.Open();
MySqlTransaction trans = con.BeginTransaction();
MySqlCommand cmd = con.CreateCommand();
cmd.Transaction = trans;
try
{
string sCmdText = string.Format(@"update gps_smsqueryvehiclesetting set TenantCode=@TenantCode,CustomerName=@CustomerName,CustomerTelephone=@CustomerTelephone,CreateDate=@CreateDate,IsProfessional=@IsProfessional where sms_vehicle_set_id=@sms_vehicle_set_id");
MySqlParameter[] parm = new MySqlParameter[] {
new MySqlParameter("sms_vehicle_set_id",smsQuery.sms_vehicle_set_id),
new MySqlParameter("TenantCode",smsQuery.TenantCode),
new MySqlParameter("CustomerName",smsQuery.CustomerName),
new MySqlParameter("CustomerTelephone",smsQuery.CustomerTelephone),
new MySqlParameter("CreateDate",smsQuery.CreateDate),
new MySqlParameter("IsProfessional",smsQuery.IsProfessional),
new MySqlParameter("LastSendTime",null)
};
cmd.CommandText = sCmdText;
cmd.Parameters.AddRange(parm);
cmd.ExecuteNonQuery();
string delsql = string.Format(@"delete from gps_SMSVehicle where sms_vehicle_set_id={0}", smsQuery.sms_vehicle_set_id);
cmd.CommandText = delsql;
cmd.ExecuteNonQuery();
IList<EMSMSVehicle> list = smsQuery.SMSVehicle;
if (list != null && list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
ulong ismsvehicle_id = GetMaxSMSVehicleID() + ulong.Parse(i.ToString()) + 1;
string sTenantCode = smsQuery.TenantCode;
string sVehicleCode = list[i].VehicleCode.ToString();
string sLicenseNumber = list[i].LicenseNumber;
ulong isms_vehicle_set_id = smsQuery.sms_vehicle_set_id;
string sInsert = string.Format(@"insert into gps_SMSVehicle(smsvehicle_id,TenantCode,VehicleCode,LicenseNumber,sms_vehicle_set_id) values({0},'{1}','{2}','{3}',{4})", ismsvehicle_id, sTenantCode, sVehicleCode, sLicenseNumber, isms_vehicle_set_id);
cmd.CommandText = sInsert;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
con.Close();
}
}
public void DoWork()
{
using (MySqlConnection conn = new MySqlConnection(GlobalUtils.TopSecret.MySqlCS))
using (MySqlCommand command = new MySqlCommand())
{
try
{
conn.Open();
//conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (MySqlTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
MySqlDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader);
while (reader.NextResult())
ShowResultSet(reader);
}
}
//var stats = conn.RetrieveStatistics();
//using (TextWriter tw = new StreamWriter(path + ".stats"))
//{
// tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
// Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
// stats["SelectRows"],
// stats["IduRows"]);
//}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}
public int ExecuteNonQuery(string sql)
{
try {
int affected;
MySqlTransaction my_transaction = conexion.BeginTransaction();
MySqlCommand cmd = conexion.CreateCommand();
cmd.CommandText = sql;
affected = cmd.ExecuteNonQuery();
my_transaction.Commit();
return(affected);
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
return(-1);
}
void IPromotableSinglePhaseNotification.Initialize()
{
simpleTransaction = connection.BeginTransaction();
}
public void BeginTransaction()
{
Open();
transaction = connection.BeginTransaction();
}
public override void BeginTransaction()
{
ts = privateconn.BeginTransaction();
}
/// <summary>
/// 开始事务处理
/// </summary>
public static void BeginTransaction()
{
OpenConnecion();
trans = con.BeginTransaction();
m_isTransaction = true;
}
public void BeginTran()
{
tran = conn.BeginTransaction();
}
public APIGatewayProxyResponse FunctionHandler(JObject input, ILambdaContext context)
{
try
{
string envID = "ConcurLive"; //Used to pull enivronment settings. Hard coded for THK for now. Can change in future versions
string firstSubmitDate = "2020-11-01"; //THK Beginning of life for this integration
string nextSubmitDate = ""; //used to calculate criteria for web call
Token tokenResponse = new Token();
string requestUrl = $"https://us.api.concursolutions.com/oauth2/v0/token";
var httpClientHandler = new HttpClientHandler();
httpClientHandler.ServerCertificateCustomValidationCallback = (message, cert, chain, errors) => { return(true); };
string myConnectionString = "server=db6.cqc3tpt63rhe.us-west-1.rds.amazonaws.com;uid=StellarAdmin;pwd=Stellar1c;database=HonestKitchen";
using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection())
{
conn.ConnectionString = myConnectionString;
Console.WriteLine("Opening Connection");
conn.Open();
var configInfoCmd = conn.CreateCommand();
configInfoCmd.CommandType = CommandType.Text;
configInfoCmd.CommandText = $"Select ConfigurationInfo from Environments Where EnvKey='{envID}'";
string sConfigInfo = configInfoCmd.ExecuteScalar().ToString();
ConfigInfo configInfo = JsonConvert.DeserializeObject <ConfigInfo>(sConfigInfo);
//Get highest submitted date from staging and subtract 14 days
#region Get Last Submitted Date
using (MySql.Data.MySqlClient.MySqlConnection conn1 = new MySql.Data.MySqlClient.MySqlConnection())
{
conn1.ConnectionString = myConnectionString;
Console.WriteLine("Opening Connection");
conn1.Open();
string dateSQL = $"Select coalesce(date_add(Max(SubmittedDate), INTERVAL -30 DAY),'{firstSubmitDate}') CutOff From Concur_ExpenseReports";
using (var cmd = conn1.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = dateSQL;
using (var sqlResult = cmd.ExecuteReaderAsync())
{
sqlResult.Wait(0);
sqlResult.Result.Read();
nextSubmitDate = sqlResult.Result.GetValue(sqlResult.Result.GetOrdinal("CutOff")).ToString();
}
}
}
#endregion
#region Get Token from Concur for subsequent calls
var client = new HttpClient(httpClientHandler);
{
//Setup API call and retrieve token
Console.WriteLine("Starting Web Request");
HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, requestUrl);
var nvc = new List <KeyValuePair <string, string> >();
string clientSecret = Decrypt(configInfo.client_secret);
//string grantType = "password";
string grantType = "refresh_token";
string userName = configInfo.user_name;
string password = Decrypt(configInfo.password);
string credType = "password";
string clientID = configInfo.client_id;
string refresh_token = "9ccd78e3-32c7-4efb-9d9c-f7cf365b76e5";
nvc.Add(new KeyValuePair <string, string>("client_secret", clientSecret));
nvc.Add(new KeyValuePair <string, string>("grant_type", grantType));
//nvc.Add(new KeyValuePair<string, string>("username", userName));
//nvc.Add(new KeyValuePair<string, string>("password", password));
nvc.Add(new KeyValuePair <string, string>("credtype", credType));
nvc.Add(new KeyValuePair <string, string>("client_id", clientID));
nvc.Add(new KeyValuePair <string, string>("refresh_token", refresh_token));
request.Content = new FormUrlEncodedContent(nvc);
request.RequestUri = new Uri(requestUrl);
Task <HttpResponseMessage> content = client.PostAsync(request.RequestUri, request.Content);
content.Wait();
Task <string> apiResponse = content.Result.Content.ReadAsStringAsync();
tokenResponse = JsonConvert.DeserializeObject <Token>(apiResponse.Result.ToString());
}
#endregion
#region Get Reports
{
//Setup and call Get Reports API
requestUrl = "https://www.concursolutions.com/api/v3.0/expense/reports";
requestUrl += $"?limit=100&user=ALL&ApprovalStatusCode=A_APPR&submitDateAfter={nextSubmitDate}";
HttpRequestMessage getReports = new HttpRequestMessage(HttpMethod.Get, requestUrl);
getReports.RequestUri = new Uri(requestUrl);
getReports.Headers.Add("Accept", "application/json");
getReports.Headers.Add("Content", "application/json");
getReports.Headers.Add("Authorization", "Bearer " + tokenResponse.access_token);
Task <HttpResponseMessage> content = client.SendAsync(getReports);
content.Wait();
Task <string> apiResponse = content.Result.Content.ReadAsStringAsync();
try
{
dynamic json = JsonConvert.DeserializeObject(apiResponse.Result.ToString());
//Loop through reports to get report detail and pass to database
foreach (dynamic reportJSON in json.Items)
{
string reportID = reportJSON.ID;
string strReportJSON = reportJSON.ToString();
#region Get report details and insert to DB
Console.WriteLine(reportID.ToString());
requestUrl = $"https://www.concursolutions.com/api/expense/expensereport/v2.0/report/{reportID}";
HttpRequestMessage getReportLines = new HttpRequestMessage(HttpMethod.Get, requestUrl);
getReportLines.RequestUri = new Uri(requestUrl);
getReportLines.Headers.Add("Accept", "application/json");
getReportLines.Headers.Add("Content", "application/json");
getReportLines.Headers.Add("Authorization", "Bearer " + tokenResponse.access_token);
Task <HttpResponseMessage> linecontent = client.SendAsync(getReportLines);
linecontent.Wait(120000);
Task <string> apiLineResponse = linecontent.Result.Content.ReadAsStringAsync();
string linejson = apiLineResponse.Result.ToString();
Console.WriteLine("Report Retrieved");
var myTrans = conn.BeginTransaction();
var cmdSQL = conn.CreateCommand();
cmdSQL.CommandTimeout = 600;
cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.CommandText = "Concur_Expenses_Insert";
cmdSQL.Parameters.AddWithValue("_ReportID", reportID.ToString());
cmdSQL.Parameters.AddWithValue("_ReportName", reportJSON.Name.ToString());
cmdSQL.Parameters.AddWithValue("_CreatedDate", reportJSON.CreateDate.ToString());
cmdSQL.Parameters.AddWithValue("_SubmittedDate", reportJSON.SubmitDate.ToString());
cmdSQL.Parameters.AddWithValue("_EnvID", envID.ToString());
cmdSQL.Parameters.AddWithValue("_ReportHeader", strReportJSON.ToString());
cmdSQL.Parameters.AddWithValue("_ReportDetail", linejson.ToString());
Console.WriteLine("Starting Insert");
cmdSQL.ExecuteNonQuery();
Console.WriteLine("End Insert");
#endregion
}
}
#endregion
catch (Exception ex)
{
Console.Write(ex);
}
}
}
var resp = new APIGatewayProxyResponse
{
StatusCode = 200,
//Body = input.ToString()
};
return(resp);
}
catch (Exception ex)
{
var resp = new APIGatewayProxyResponse
{
StatusCode = 400,
//Body = input.ToString()
};
return(resp);
}
}
// Inserta y graba un registro nuevo
private void Grabar()
{
DialogResult oRpt;
oRpt = MessageBox.Show("Esta seguro de grabar este registro?", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (oRpt != DialogResult.Yes)
{
return;
}
char cCero = '0';
string cValues = "";
int nSecuencia = 0;
int nRegistros = 0;
string cConsultaSQL;
string cUsuario = frmLogin.cUsuarioActual;
DataTable dtExistencia = new DataTable();
string cCod_Pro = "";
// Inicio clsConexion y transacccion
MySql.Data.MySqlClient.MySqlConnection oCnn = new MySql.Data.MySqlClient.MySqlConnection(); // Objeto de clsConexion a la base de datos
MySql.Data.MySqlClient.MySqlDataAdapter daDatos = new MySql.Data.MySqlClient.MySqlDataAdapter(); // Objeto Adaptador para leer datos de la Base de datos
MySql.Data.MySqlClient.MySqlCommand cmdExec = new MySql.Data.MySqlClient.MySqlCommand(); // objeto comando para ejecutar sentencias sql
DataTable dtDatos = new DataTable(); // datatable para recibir los datos de la base de datos
StringBuilder sbQuery = new StringBuilder(); //Iniciar el cuadro de diálogo de entretenimiento:
MySqlTransaction oTransaccion = null;
try
{
this.Cursor = Cursors.WaitCursor;
oCnn.ConnectionString = cCadenaclsConexion;
oCnn.Open();
oTransaccion = oCnn.BeginTransaction();
cmdExec = oCnn.CreateCommand();
cmdExec.Connection = oCnn;
cmdExec.CommandTimeout = 0; // para consultas largas
cmdExec.Transaction = oTransaccion;
// Busco la proxima secuencia para el tipo de movimiento
this.txtCod_pro.Clear();
DataTable dtTmp = clsProcesos.DatosGeneralEscalar("mproduct", " max(cod_pro) ");
if (dtTmp.Rows.Count > 0)
{
DataRow DR;
DR = dtTmp.Rows[0];
this.txtCod_pro.Text = Convert.ToString(Convert.ToInt32(DR["retorno"].ToString()) + 1).ToString().Trim().PadLeft(6, cCero);
}
else
{
this.txtCod_pro.Text = "000001";
}
// inserto en MPRODUCT
nSecuencia = 0;
cValues = " VALUES ('" + txtCod_pro.Text.Trim() + "','" + txtDes_pro.Text.Trim() + "','" +
Convert.ToDecimal(txtCosto.Text) + "','" + Convert.ToDecimal(txtPrecio.Text.Trim()) + "','" + Convert.ToDecimal(txtPrecio.Text.Trim()) + "','" +
cboFamilia.SelectedValue.ToString() + "','" + cboSubCategoria.SelectedValue.ToString() + "','" +
this.cboPresentacion.SelectedValue.ToString() + "'," + Convert.ToUInt32(chkInventariable.Checked) + ") ";
sbQuery.Clear();
sbQuery.Append("INSERT INTO MPRODUCT(cod_PRO,DES_PRO,costo,pre_max,pre_min,cod_cat,tipo_pro,unidad,inventario) ");
sbQuery.Append(cValues);
// temporal
VFPToolkit.strings.StrToFile(sbQuery.ToString(), @"consulta.sql", true);
cmdExec.CommandText = sbQuery.ToString();
nRegistros = cmdExec.ExecuteNonQuery();
if (nRegistros == 0)
{
MessageBox.Show("No se insertaron los datos del Articulo, favor verificar", "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
oTransaccion.Rollback();
oCnn.Close();
this.CursorDefault();
return;
}
/*
* // Actualizo Stock en mproduct
* sbQuery.Clear();
* sbQuery.Append("update mproduct set cant_exist = cant_exist + " + Convert.ToDecimal(registro.Cells["Cantidad"].Value).ToString() + ", ");
* sbQuery.Append(" almacen1 = almacen1 + " + Convert.ToDecimal(registro.Cells["Cantidad"].Value).ToString());
*/
// Confirmo Transaccion y actualizo BD
// Imprimo documento
// Limpio form
oTransaccion.Commit();
MessageBox.Show("Transaccion grabada con exito!!.", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Limpiar();
this.NoEditar();
cModo = "Inicio";
this.Botones();
}
catch (MySqlException ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
oTransaccion.Rollback();
this.CursorDefault();
return;
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
// oTransaccion.Connection.State = ConnectionState.
oTransaccion.Rollback();
this.CursorDefault();
return;
}
finally
{
}
} // Grabar()
private void btnEliminar_Click(object sender, EventArgs e)
{
DialogResult oRpt;
oRpt = MessageBox.Show("Esta seguro de querer eliminar esta Articulo?", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (oRpt != DialogResult.Yes)
{
return;
}
char cCero = '0';
string cValues = "";
int nSecuencia = 0;
int nRegistros = 0;
string cConsultaSQL;
string cUsuario = frmLogin.cUsuarioActual;
DataTable dtExistencia = new DataTable();
string cCod_Pro = "";
// Inicio clsConexion y transacccion
MySql.Data.MySqlClient.MySqlConnection oCnn = new MySql.Data.MySqlClient.MySqlConnection(); // Objeto de clsConexion a la base de datos
MySql.Data.MySqlClient.MySqlDataAdapter daDatos = new MySql.Data.MySqlClient.MySqlDataAdapter(); // Objeto Adaptador para leer datos de la Base de datos
MySql.Data.MySqlClient.MySqlCommand cmdExec = new MySql.Data.MySqlClient.MySqlCommand(); // objeto comando para ejecutar sentencias sql
DataTable dtDatos = new DataTable(); // datatable para recibir los datos de la base de datos
StringBuilder sbQuery = new StringBuilder(); //Iniciar el cuadro de diálogo de entretenimiento:
MySqlTransaction oTransaccion = null;
try
{
this.Cursor = Cursors.WaitCursor;
oCnn.ConnectionString = cCadenaclsConexion;
oCnn.Open();
oTransaccion = oCnn.BeginTransaction();
cmdExec = oCnn.CreateCommand();
cmdExec.Connection = oCnn;
cmdExec.CommandTimeout = 0; // para consultas largas
cmdExec.Transaction = oTransaccion;
// Verifico que articulo no tenga movimeinto historico
DataTable dtMovInventario = clsProcesos.DatosGeneral("invent", " where trim(invent.cod_1) = '" + txtCod_pro.Text.Trim() + "'", " order by secuencia");
if (dtMovInventario.Rows.Count > 0)
{
MessageBox.Show("No se puede eliminar este Articulo, pues ya tiene movimiento historico en la BD, favor verificar", "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
oTransaccion.Rollback();
oCnn.Close();
this.CursorDefault();
return;
}
// Actualizo en MPRODUCT
nSecuencia = 0;
sbQuery.Clear();
sbQuery.Append("delete from mproduct where trim(mproduct.cod_pro) = '" + txtCod_pro.Text.Trim() + "'");
// temporal
VFPToolkit.strings.StrToFile(sbQuery.ToString(), @"consulta.sql", true);
cmdExec.CommandText = sbQuery.ToString();
nRegistros = cmdExec.ExecuteNonQuery();
if (nRegistros == 0)
{
MessageBox.Show("No se pudo eliminar el Articulo, favor verificar", "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
oTransaccion.Rollback();
oCnn.Close();
this.CursorDefault();
return;
}
// Confirmo Transaccion y actualizo BD
// Imprimo documento
// Limpio form
oTransaccion.Commit();
MessageBox.Show("Artituclo eliminado con exito!!.", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Limpiar();
this.NoEditar();
cModo = "Inicio";
this.Botones();
}
catch (MySqlException ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
oTransaccion.Rollback();
this.CursorDefault();
return;
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
// oTransaccion.Connection.State = ConnectionState.
oTransaccion.Rollback();
this.CursorDefault();
return;
}
finally
{
}
}
public bool AddComanda(ComandaPrinc mycomanda, List <ComandaItem> items, double sconto)
{
string sqlMaster = @"INSERT INTO comanda (data,n_coperti,totalecomanda,sconto) VALUES(?datam,?coperti,?ttcom,?pconto);
SELECT LAST_INSERT_ID();";
string sqlItemOriginal = @"INSERT INTO comanda_alimento (id_comanda,id_alimento,qta,prezzo) VALUES(?idc,?ida,?qta,?przz);
";
string sql_UpdateAlimentoqta = @"UPDATE alimento set quantit = ?qtaDisp where id =?idAl";
string sql = string.Empty;
int _privcomanda;
_connection.Open();
MySqlCommand myCommand = _connection.CreateCommand();
MySqlCommand myCommandItem = _connection.CreateCommand();
MySqlCommand myCommandUpdateComanda = _connection.CreateCommand();
MySqlTransaction myTrans;
myTrans = _connection.BeginTransaction();
myCommandUpdateComanda.Connection = myCommandItem.Connection = myCommand.Connection = _connection;
myCommandUpdateComanda.Transaction = myCommandItem.Transaction = myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = sqlMaster;
myCommand.Parameters.AddWithValue("?datam", mycomanda.dataarr);
myCommand.Parameters.AddWithValue("?coperti", mycomanda.ncoperti);
myCommand.Parameters.AddWithValue("?ttcom", mycomanda.totPriceComanda);
myCommand.Parameters.AddWithValue("?pconto", sconto);
_privcomanda = Convert.ToInt32(myCommand.ExecuteScalar());
foreach (ComandaItem joi in items)
{
myCommandItem.CommandText = sqlItemOriginal;
myCommandItem.Parameters.AddWithValue("?idc", _privcomanda);
myCommandItem.Parameters.AddWithValue("?ida", joi.IdAlimento);
myCommandItem.Parameters.AddWithValue("?qta", joi.Quant);
myCommandItem.Parameters.AddWithValue("?przz", joi.totPrice);
myCommandItem.ExecuteNonQuery();
myCommandItem.Parameters.Clear();
myCommandUpdateComanda.CommandText = sql_UpdateAlimentoqta;
myCommandUpdateComanda.Parameters.AddWithValue("?qtaDisp", joi.quantRimasta);
myCommandUpdateComanda.Parameters.AddWithValue("?idAl", joi.IdAlimento);
myCommandUpdateComanda.ExecuteNonQuery();
myCommandUpdateComanda.Parameters.Clear();
}
myTrans.Commit();
return(true);
}
catch (Exception e)
{
try
{
System.Windows.Forms.MessageBox.Show(e.ToString());
myTrans.Rollback();
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
//Console.WriteLine("An exception of type " + ex.GetType() +
// " was encountered while attempting to roll back the transaction.");
}
}
return(false);
}
finally
{
_connection.Close();
}
}
} // Grabar()
// Actualiza Registro
private void Actualizar()
{
DialogResult oRpt;
oRpt = MessageBox.Show("Esta seguro de grabar cambios a este registro?", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (oRpt != DialogResult.Yes)
{
return;
}
char cCero = '0';
string cValues = "";
int nSecuencia = 0;
int nRegistros = 0;
string cConsultaSQL;
string cUsuario = frmLogin.cUsuarioActual;
DataTable dtExistencia = new DataTable();
string cCod_Pro = "";
// Inicio clsConexion y transacccion
MySql.Data.MySqlClient.MySqlConnection oCnn = new MySql.Data.MySqlClient.MySqlConnection(); // Objeto de clsConexion a la base de datos
MySql.Data.MySqlClient.MySqlDataAdapter daDatos = new MySql.Data.MySqlClient.MySqlDataAdapter(); // Objeto Adaptador para leer datos de la Base de datos
MySql.Data.MySqlClient.MySqlCommand cmdExec = new MySql.Data.MySqlClient.MySqlCommand(); // objeto comando para ejecutar sentencias sql
DataTable dtDatos = new DataTable(); // datatable para recibir los datos de la base de datos
StringBuilder sbQuery = new StringBuilder(); //Iniciar el cuadro de diálogo de entretenimiento:
MySqlTransaction oTransaccion = null;
try
{
this.Cursor = Cursors.WaitCursor;
oCnn.ConnectionString = cCadenaclsConexion;
oCnn.Open();
oTransaccion = oCnn.BeginTransaction();
cmdExec = oCnn.CreateCommand();
cmdExec.Connection = oCnn;
cmdExec.CommandTimeout = 0; // para consultas largas
cmdExec.Transaction = oTransaccion;
// Actualizo en MPRODUCT
nSecuencia = 0;
sbQuery.Clear();
sbQuery.Append("update MPRODUCT set cod_PRO = '" + txtCod_pro.Text.Trim() + "', ");
sbQuery.Append(" des_PRO = '" + txtDes_pro.Text.Trim() + "', ");
sbQuery.Append(" costo = '" + Convert.ToDecimal(txtCosto.Text) + "', ");
sbQuery.Append(" pre_max = '" + Convert.ToDecimal(txtPrecio.Text.Trim()) + "', ");
sbQuery.Append(" pre_min = '" + Convert.ToDecimal(txtPrecio.Text.Trim()) + "', ");
sbQuery.Append(" cod_cat = '" + cboFamilia.SelectedValue.ToString() + "', ");
sbQuery.Append(" tipo_pro = '" + cboSubCategoria.SelectedValue.ToString() + "', ");
sbQuery.Append(" unidad = '" + cboPresentacion.SelectedValue.ToString() + "', ");
sbQuery.Append(" inventario = " + Convert.ToUInt32(chkInventariable.Checked));
sbQuery.Append(" where trim(mproduct.cod_pro) = '" + txtCod_pro.Text.Trim() + "'");
// temporal
VFPToolkit.strings.StrToFile(sbQuery.ToString(), @"consulta.sql", true);
cmdExec.CommandText = sbQuery.ToString();
nRegistros = cmdExec.ExecuteNonQuery();
if (nRegistros == 0)
{
MessageBox.Show("No se actualizaron los datos del Articulo, favor verificar", "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
oTransaccion.Rollback();
oCnn.Close();
this.CursorDefault();
return;
}
// Confirmo Transaccion y actualizo BD
// Imprimo documento
// Limpio form
oTransaccion.Commit();
MessageBox.Show("Transaccion grabada con exito!!.", "Sistema SysHospitalARD v1.0",
MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Limpiar();
this.NoEditar();
cModo = "Inicio";
this.Botones();
}
catch (MySqlException ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
oTransaccion.Rollback();
this.CursorDefault();
return;
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message, "Sistema SysHospitalARD v1.0", MessageBoxButtons.OK,
MessageBoxIcon.Information);
clsExceptionLog.LogError(ex, false);
// oTransaccion.Connection.State = ConnectionState.
oTransaccion.Rollback();
this.CursorDefault();
return;
}
finally
{
}
}
/// <summary>
/// 开始事务处理
/// </summary>
public override void BeginTransaction()
{
transaction = connection.BeginTransaction();
this.IsTransaction = true;
}
/// <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();
}
}
}
private void enviarButton_Click(object sender, EventArgs e)
{
if (myconn != null)
{
verificaConnMysql();
}
else
{
MessageBox.Show("Não foi possível sincronizar, sem conexão.");
return;
}
//login web
MysqlLoginDialog mld = new MysqlLoginDialog(myconn);
mld.ShowDialog();
if (!Usuario.Logado)
{
return;
}
//VERIFICA CONEXAO
verificaConnMysql();
//INSERE TRAFOS
if (checkTodos.Checked)
{
trafoTableAdapter.Fill(citeluzDataSet.trafo);
}
else
{
DataRowView row =
(DataRowView)trafoBindingSource.Current;
trafoTableAdapter.FillBy(citeluzDataSet.trafo, (string)row["cod_trafo"]);
}
//verifica duplicidade
string duplicidade = "SELECT plaqueta_trafo, nome_lote FROM trafo " +
"WHERE plaqueta_trafo IN (";
string dupli_plaqueta = "SELECT plaqueta_id, nome_lote FROM plaqueta " +
"WHERE plaqueta_id IN (";
string dtmp = "";
foreach (DataRow row in citeluzDataSet.trafo.Rows)
{
dtmp = dtmp + "'" + row["plaqueta_trafo"] + "',";
}
dtmp = dtmp.Remove(dtmp.Length - 1, 1); //remove a virgula no final
duplicidade = duplicidade + dtmp + ") ";
duplicidade = duplicidade + " AND nome_lote = '" + txtNomeLote.Text + "'";
dtmp = "";
foreach (DataRow row in citeluzDataSet.plaqueta.Rows)
{
dtmp = dtmp + "'" + row["plaqueta_id"] + "',";
}
dtmp = dtmp.Remove(dtmp.Length - 1, 1); //remove a virgula no final
dupli_plaqueta = dupli_plaqueta + dtmp + ") ";
dupli_plaqueta = dupli_plaqueta + " AND nome_lote = '" + txtNomeLote.Text + "'";
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
//GERAR LOG DOS DUPLICADOS PARA FACILITAR CONFERENCIA
Directory.CreateDirectory(Library.appDir + "\\log");
Application.DoEvents();
string logPath =
Library.appDir + "\\log\\dupli_" + txtNomeLote.Text + ".txt";
TextWriter id = new StreamWriter(logPath);
id.WriteLine("Duplicidades em Trafos:");
id.WriteLine("----------------------------------");
mycommand = myconn.CreateCommand();
mycommand.Connection = myconn;
mycommand.CommandText = duplicidade;
MySqlDataReader mydr = mycommand.ExecuteReader();
while (mydr.Read())
{
string trafo = mydr.GetString("plaqueta_trafo");
listBox1.Items.Add("Duplicidade " + trafo);
id.WriteLine(trafo);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
mydr.Close();
mycommand.CommandText = dupli_plaqueta;
MySqlDataReader mydr_plaqueta = mycommand.ExecuteReader();
id.WriteLine(" ");
id.WriteLine("Duplicidades em Plaquetas:");
id.WriteLine("----------------------------------");
while (mydr_plaqueta.Read())
{
string plaqueta = mydr_plaqueta.GetString("plaqueta_id");
listBox1.Items.Add("Duplicidade " + plaqueta);
id.WriteLine(plaqueta);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
id.Flush();
id.Close();
if (mydr.HasRows || mydr_plaqueta.HasRows)
{
MessageBox.Show("Há duplicidades no arquivo que esta sendo lançado. Duplicidades salvas em \\log\\dupli_lote.txt");
myconn.Close();
return;
}
myconn.Close();
Application.DoEvents();
myconn.Open();
mycommand.Transaction = mytrans;
mytrans = myconn.BeginTransaction();
//insere trafos
foreach (DataRow row in citeluzDataSet.trafo.Rows)
{
insert = "INSERT INTO trafo (";
foreach (DataColumn column in citeluzDataSet.trafo.Columns)
{
if (column.ColumnName.Equals("user"))
{
insert = insert + "usuario_fk,";
}
else
{
insert = insert + column.ColumnName + ",";
}
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.trafo.Columns)
{
object valor = row[column.ColumnName];
if (column.ColumnName.Equals("plaqueta_trafo"))
{
codTrafo = (string)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
insert = insert + valor + ",";
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
//insere trafos
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Projeto " + codTrafo + " enviado");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
//INSERE PLAQUETAS
this.plaquetaTableAdapter.Fill(citeluzDataSet.plaqueta, codTrafo);
foreach (DataRow rowPlaqueta in citeluzDataSet.plaqueta.Rows)
{
insert = "INSERT INTO plaqueta (";
foreach (DataColumn column in citeluzDataSet.plaqueta.Columns)
{
insert = insert + column.ColumnName + ",";
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.plaqueta.Columns)
{
object valor = rowPlaqueta[column.ColumnName];
if (column.ColumnName.Equals("plaqueta_id"))
{
codPlaqueta = (string)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
insert = insert + valor + ",";
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Plaqueta " + codPlaqueta + " enviada");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
//insere MATERIAL
this.materialTableAdapter.Fill(citeluzDataSet.material, codPlaqueta);
foreach (DataRow rowMaterial in citeluzDataSet.material.Rows)
{
insert = "INSERT INTO material (";
foreach (DataColumn column in citeluzDataSet.material.Columns)
{
if (!column.ColumnName.Equals("id"))
{
insert = insert + column.ColumnName + ",";
}
}
insert = insert + "nome_lote,";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ") VALUES (";
foreach (DataColumn column in citeluzDataSet.material.Columns)
{
object valor = rowMaterial[column.ColumnName];
if (column.ColumnName.Equals("id"))
{
codMaterial = (int)valor;
}
if (column.DataType == typeof(string))
{
valor = Library.removeCrase(valor.ToString());
valor = "'" + valor + "'";
}
else
{
if (valor.ToString().Length == 0)
{
valor = "null";
}
}
if (!column.ColumnName.Equals("id"))
{
insert = insert + valor + ",";
}
}
insert = insert + "'" + txtNomeLote.Text.Trim().ToUpper() + "',";
insert = insert.Remove(insert.Length - 1, 1);
insert = insert + ");";
try
{
mycommand.CommandText = insert;
mycommand.ExecuteNonQuery();
listBox1.Items.Add("Material " + codMaterial + " enviada");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Application.DoEvents();
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
}
catch (Exception ex)
{
mytrans.Rollback();
myconn.Close();
MessageBox.Show(ex.Message);
listBox1.Items.Add(ex.Message);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
trafoTableAdapter.Fill(citeluzDataSet.trafo);
Cursor.Current = Cursors.Default;
Application.DoEvents();
return;
}
}
mytrans.Commit();
myconn.Close();
listBox1.Items.Add("Envio completo.");
listBox1.SelectedIndex = listBox1.Items.Count - 1;
Cursor.Current = Cursors.Default;
Application.DoEvents();
}
private bool ManipulandoDadosNaBase(StringBuilder querySql)
{
// retorna true em caso de sucesso.
bool executou = false;
MySqlTransaction transacaoSql = null;
MySqlConnection conexaoSql = null;
MySqlCommand comandoSql = null;
try
{
// Abrindo conexão
conexaoSql = new MySqlConnection(GetConfiguracoes());
conexaoSql.Open();
// recuperando instancia de MySqlCommand
comandoSql = conexaoSql.CreateCommand();
// Iniciando transacao
transacaoSql = conexaoSql.BeginTransaction();
// Definindo conexao e transacao para MySqlCommand
comandoSql.Connection = conexaoSql;
comandoSql.Transaction = transacaoSql;
// Definindo query para comandoSql
comandoSql.CommandText = querySql.ToString();
// executando query
executou = comandoSql.ExecuteNonQuery() > 0;
// integridade referencial, confirmando execucao.
transacaoSql.Commit();
}
catch (Exception ex)
{
try
{
// integridade referencial, desfazendo execucao.
transacaoSql.Rollback();
}
catch (MySqlException e)
{
if (transacaoSql.Connection != null)
throw new Exception("Uma exceção do tipo " + e.GetType() + " foi encontrado ao tentar desfazer a transação.");
}
throw new Exception("Uma exceção do tipo " + ex.GetType() + " foi encontrado enquanto a query estava sendo processada.");
}
finally
{
if (conexaoSql != null)
conexaoSql.Close();
if (comandoSql != null)
comandoSql.Dispose();
if (transacaoSql != null)
transacaoSql.Dispose();
}
return executou;
}