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