public static ExecuteNonQuery ( |
||
connection | ||
commandText | string | SQL command to be executed |
Результат | int |
private void ApplyNonOptimal(MySqlTransaction trans) { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.report_properties SET PropertyValue = ?PPropertyValue WHERE ID = ?PID", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64)); UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName; UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PPropertyValue", MySqlDbType.VarString)); UpdCmd.Parameters["PPropertyValue"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PPropertyValue"].SourceColumn = PPropertyValue.ColumnName; UpdCmd.Parameters["PPropertyValue"].SourceVersion = DataRowVersion.Current; MyDA.UpdateCommand = UpdCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtNonOptionalParams.TableName].TableName); }
public static void InsertOrUpdateTable(string queryInsert, string queryUpdate, params MySqlParameter[] parameters) { // Пробуем вставить строку в таблицу try { With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryInsert, parameters); }); } catch (Exception) { // Если не получилось вставить строку, пробуем обновить ее With.Connection(connection => { MySqlHelper.ExecuteNonQuery(connection, queryUpdate, parameters); }); } }
public void UpdateLastCheck() { LastSuccessfulCheck = DateTime.Now; With.Connection(c => { MySqlHelper.ExecuteNonQuery(c, @" update farm.Sources src join usersettings.PriceItems pim on src.Id = pim.SourceId set src.LastSuccessfulCheck = ?LastSuccessfulCheck where pim.Id = ?PriceItemId", new MySqlParameter("?PriceItemId", PriceItemId), new MySqlParameter("?LastSuccessfulCheck", LastSuccessfulCheck)); }); }
public void ApplyChanges(MySqlConnection masterConnection, IProgressNotifier formProgress, List <DataRow> rows) { operatorName = Environment.UserName.ToLower(); CalculateChanges(masterConnection, formProgress, rows); var updateSynonymProducerEtalonSQL = daSynonymFirmCr.UpdateCommand.CommandText; var insertSynonymProducerEtalonSQL = daSynonymFirmCr.InsertCommand.CommandText; var changes = dtSynonymFirmCr.GetChanges(DataRowState.Modified); if (changes != null) { stat.SynonymFirmCrCount += changes.Rows.Count; } formProgress.Status = "Применение изменений в базу данных..."; DataRow lastUpdateSynonym = null; try { With.DeadlockWraper(c => { var humanName = GetHumanName(c, operatorName); var helper = new Common.MySql.MySqlHelper(c, null); var commandHelper = helper.Command("set @inHost = ?Host; set @inUser = ?UserName;"); commandHelper.AddParameter("?Host", Environment.MachineName); commandHelper.AddParameter("?UserName", operatorName); commandHelper.Execute(); //Заполнили таблицу логов для синонимов наименований daSynonym.SelectCommand.Connection = c; daSynonym.Update(dtSynonym); formProgress.ApplyProgress += 10; var insertExclude = new MySqlCommand(@" insert into Farm.Excludes(CatalogId, PriceCode, ProducerSynonym, DoNotShow, Operator, OriginalSynonymId) value (?CatalogId, ?PriceCode, ?ProducerSynonym, ?DoNotShow, ?Operator, ?OriginalSynonymId);", c); insertExclude.Parameters.AddWithValue("?PriceCode", priceId); insertExclude.Parameters.AddWithValue("?Operator", humanName); insertExclude.Parameters.Add("?ProducerSynonym", MySqlDbType.VarChar); insertExclude.Parameters.Add("?DoNotShow", MySqlDbType.Byte); insertExclude.Parameters.Add("?CatalogId", MySqlDbType.UInt32); insertExclude.Parameters.Add("?OriginalSynonymId", MySqlDbType.UInt32); foreach (var exclude in excludes.Where(e => e.Id == 0)) { if (!IsExcludeCorrect(c, exclude)) { continue; } insertExclude.Parameters["?ProducerSynonym"].Value = exclude.ProducerSynonym; insertExclude.Parameters["?DoNotShow"].Value = exclude.DoNotShow; insertExclude.Parameters["?CatalogId"].Value = exclude.CatalogId; insertExclude.Parameters["?OriginalSynonymId"].Value = exclude.GetOriginalSynonymId(); insertExclude.ExecuteScalar(); } //Заполнили таблицу логов для синонимов производителей daSynonymFirmCr.SelectCommand.Connection = c; daSynonymFirmCr.UpdateCommand.Connection = c; daSynonymFirmCr.InsertCommand.Connection = c; var dtSynonymFirmCrCopy = dtSynonymFirmCr.Copy(); foreach (DataRow drInsertProducerSynonym in dtSynonymFirmCrCopy.Rows) { lastUpdateSynonym = drInsertProducerSynonym; daSynonymFirmCr.InsertCommand.CommandText = insertSynonymProducerEtalonSQL; daSynonymFirmCr.UpdateCommand.CommandText = updateSynonymProducerEtalonSQL; //обновляем по одному синониму производителя, т.к. может быть добавление в исключение daSynonymFirmCr.Update(new[] { drInsertProducerSynonym }); } MySqlHelper.ExecuteNonQuery(c, @" update usersettings.pricescosts, usersettings.priceitems set priceitems.LastSynonymsCreation = now() where pricescosts.PriceCode = ?PriceCode and priceitems.Id = pricescosts.PriceItemId", new MySqlParameter("?PriceCode", priceId)); formProgress.ApplyProgress += 10; //Заполнили таблицу логов для запрещённых выражений daForbidden.SelectCommand.Connection = c; var dtForbiddenCopy = dtForbidden.Copy(); daForbidden.Update(dtForbiddenCopy); formProgress.ApplyProgress += 10; //Обновление таблицы нераспознанных выражений daUnrecUpdate.SelectCommand.Connection = c; var dtUnrecUpdateCopy = dtUnrecUpdate.Copy(); daUnrecUpdate.Update(dtUnrecUpdateCopy); formProgress.ApplyProgress += 10; // Сохраняем запрещенные имена производителей var deleteUnrec = new MySqlCommand("delete from farm.UnrecExp where LOWER(FirmCr) = ?FirmName and Status = 1", c); var insertForbiddenProducer = new MySqlCommand(@" insert into Farm.Forbiddenproducers(Name) value (?Name);", c); insertForbiddenProducer.Parameters.Add("?Name", MySqlDbType.VarChar); foreach (var producer in ForbiddenProducers.Where(e => e.Id == 0)) { insertForbiddenProducer.Parameters["?Name"].Value = producer.Name; insertForbiddenProducer.ExecuteScalar(); // удаляем нераспознанные выражения с таким же наименованием производителя deleteUnrec.Parameters.Clear(); deleteUnrec.Parameters.AddWithValue("?FirmName", producer.Name.ToLower()); deleteUnrec.ExecuteNonQuery(); } }); } catch (Exception e) { if (e.Message.Contains("Duplicate entry")) { Mailer.SendDebugLog(dtSynonymFirmCr, e, lastUpdateSynonym); } throw; } formProgress.ApplyProgress = 80; formProgress.Status = String.Empty; formProgress.Error = String.Empty; formProgress.Status = "Перепроведение пpайса..."; formProgress.ApplyProgress = 80; try { #if !DEBUG _remotePriceProcessor.RetransPriceSmartMsMq(priceId); #endif } catch (Exception e) { formProgress.Error = "При перепроведении файлов возникла ошибка, которая отправлена разработчику."; _logger.Error(String.Format("Ошибка при перепроведении прайс листа {0}", priceId), e); } _logger.DebugFormat("Перепроведение пpайса завершено."); formProgress.ApplyProgress = 100; }
protected void btnApply_Click(object sender, EventArgs e) { Validate(); if (!IsValid) { return; } CopyChangesToTable(); MySqlTransaction trans; MyCn.Open(); trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.reports SET ReportCaption = ?RReportCaption, ReportTypeCode = ?RReportTypeCode, GeneralReportCode = ?RGeneralReportCode, Enabled = ?REnabled WHERE ReportCode = ?RReportCode", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString)); UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName; UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64)); UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName; UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64)); UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName; UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte)); UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input; UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName; UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"])); MySqlCommand DelCmd = new MySqlCommand(@" DELETE from reports.reports WHERE ReportCode = ?RDelReportCode", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64)); DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input; DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName; DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original; MySqlCommand InsCmd = new MySqlCommand(@" INSERT INTO reports.reports SET ReportCaption = ?RReportCaption, ReportTypeCode = ?RReportTypeCode, GeneralReportCode = ?RGeneralReportCode, Enabled = ?REnabled ", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString)); InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input; InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName; InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64)); InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input; InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName; InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte)); InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input; InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName; InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", Request["r"])); MyDA.UpdateCommand = UpdCmd; MyDA.DeleteCommand = DelCmd; MyDA.InsertCommand = InsCmd; var strHost = HttpContext.Current.Request.UserHostAddress; var strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName); trans.Commit(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } var report = DbSession.Load <GeneralReport>(Convert.ToUInt64(Request["r"])); report.EMailSubject = tbEMailSubject.Text; report.ReportFileName = tbReportFileName.Text; report.ReportArchName = tbReportArchName.Text; report.NoArchive = NoArchive.Checked; report.MailPerFile = MailPerFile.Checked; report.SendDescriptionFile = SendDescriptionFile.Checked; report.Format = ReportFormatDD.Text; DbSession.Save(report); foreach (GridViewRow dr in fileGridView.Rows) { var idField = ((HiddenField)dr.FindControl("Id")).Value; var property = DbSession.Get <FileSendWithReport>(Convert.ToUInt32(idField)); var file = ((FileUpload)dr.FindControl("UploadFile")); if (file.HasFile) { property.FileName = file.FileName; File.WriteAllBytes(property.FileNameForSave, file.FileBytes); DbSession.Save(property); } } PostData(); }
/// <summary> /// Дублирует все значения свойств из одного отчета в другой (клонирование) /// </summary> /// <param name="sourceReportId">код исходного отчета из таблицы reports.reports</param> /// <param name="destinationReportId">код отчета-приемника из таблицы reports.reports</param> public static void CopyReportProperties(ulong sourceReportId, ulong destinationReportId) { Report _sourceReport = Report.Find(sourceReportId); Report _destinationReport = Report.Find(destinationReportId); if (_sourceReport.ReportType != _destinationReport.ReportType) { throw new ReportTunerException( String.Format( "Тип клонируемого отчета отличается от конечного отчета. Тип исходного отчета: {0}. Тип отчета-приемника: {1}", _sourceReport.ReportType.ReportTypeName, _destinationReport.ReportType.ReportTypeName)); } DataSet dsReportProperties = MySqlHelper.ExecuteDataset( ConnectionHelper.GetConnectionString(), @" SELECT rp.*, rtp.PropertyType FROM reports.report_properties rp, reports.report_type_properties rtp where rp.ReportCode = ?SourceReportId and rtp.Id = rp.PropertyId order by rp.PropertyID; SELECT rpv.* FROM reports.report_properties rp, reports.report_property_values rpv where rp.ReportCode = ?SourceReportId and rpv.ReportPropertyId = rp.Id; SELECT rp.*, rtp.PropertyType FROM reports.report_properties rp, reports.report_type_properties rtp where rp.ReportCode = ?DestinationReportId and rtp.Id = rp.PropertyId order by rp.PropertyID; ", new MySqlParameter("?SourceReportId", sourceReportId), new MySqlParameter("?DestinationReportId", destinationReportId)); DataTable dtSourceProperties = dsReportProperties.Tables[0]; DataTable dtSourcePropertiesValues = dsReportProperties.Tables[1]; DataTable dtDestinationProperties = dsReportProperties.Tables[2]; StringBuilder sbCommand = new StringBuilder(); foreach (DataRow drSourceProperty in dtSourceProperties.Rows) { DataRow[] drDestinationProperties = dtDestinationProperties.Select("PropertyId = " + drSourceProperty["PropertyId"]); if (drDestinationProperties.Length == 0) { //Свойство не существует, поэтому просто вставляем новое sbCommand.AppendFormat("insert into reports.report_properties (ReportCode, PropertyId, PropertyValue) values ({0}, {1}, '{2}');\r\n", destinationReportId, drSourceProperty["PropertyId"], drSourceProperty["PropertyValue"]); if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase)) { sbCommand.AppendLine("set @LastReportPropertyId = last_insert_id();"); foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"])) { sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values (@LastReportPropertyId, '{0}');\r\n", drSourcePropertiesValue["Value"]); } } } else { //Свойство существует, поэтому обновляем запись sbCommand.AppendFormat("update reports.report_properties set PropertyValue = '{0}' where Id = {1};\r\n", drSourceProperty["PropertyValue"], drDestinationProperties[0]["Id"]); if (drSourceProperty["PropertyType"].ToString().Equals("LIST", StringComparison.OrdinalIgnoreCase)) { sbCommand.AppendFormat("delete from reports.report_property_values where ReportPropertyId = {0};\r\n", drDestinationProperties[0]["Id"]); foreach (DataRow drSourcePropertiesValue in dtSourcePropertiesValues.Select("ReportPropertyId = " + drSourceProperty["Id"])) { sbCommand.AppendFormat("insert into reports.report_property_values (ReportPropertyId, Value) values ({0}, '{1}');\r\n", drDestinationProperties[0]["Id"], drSourcePropertiesValue["Value"]); } } } } MySqlConnection connection = new MySqlConnection(ConnectionHelper.GetConnectionString()); connection.Open(); try { MySqlTransaction transaction = connection.BeginTransaction(); MySqlHelper.ExecuteNonQuery(connection, sbCommand.ToString()); transaction.Commit(); } finally { connection.Close(); } }
protected void btnApply_Click(object sender, EventArgs e) { CopyChangesToTable(); var _deletedReports = new List <ulong>(); var _updatedReports = new List <ulong>(); DataTable dtInserted; MyCn.Open(); var trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { var UpdCmd = new MySqlCommand(@" UPDATE reports.general_reports SET Allow = ?Allow, Public = ?Public, Comment = ?Comment, FirmCode = if(PayerID = ?payerID, FirmCode, (select min(Id) from (select s.Id from Customers.Suppliers s where s.Payer = ?payerID) tbl)), PayerID = ?payerID WHERE GeneralReportCode = ?GeneralReportCode", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte)); UpdCmd.Parameters["Allow"].Direction = ParameterDirection.Input; UpdCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName; UpdCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte)); UpdCmd.Parameters["Public"].Direction = ParameterDirection.Input; UpdCmd.Parameters["Public"].SourceColumn = Public.ColumnName; UpdCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString)); UpdCmd.Parameters["Comment"].Direction = ParameterDirection.Input; UpdCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName; UpdCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("GeneralReportCode", MySqlDbType.Int64)); UpdCmd.Parameters["GeneralReportCode"].Direction = ParameterDirection.Input; UpdCmd.Parameters["GeneralReportCode"].SourceColumn = GeneralReportCode.ColumnName; UpdCmd.Parameters["GeneralReportCode"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add("?payerID", MySqlDbType.Int64).SourceColumn = GRPayerID.ColumnName; MySqlCommand DelCmd = new MySqlCommand(@" DELETE from reports.general_reports WHERE GeneralReportCode = ?GRDelCode", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("GRDelCode", MySqlDbType.Int64)); DelCmd.Parameters["GRDelCode"].Direction = ParameterDirection.Input; DelCmd.Parameters["GRDelCode"].SourceColumn = GeneralReportCode.ColumnName; DelCmd.Parameters["GRDelCode"].SourceVersion = DataRowVersion.Original; MySqlCommand InsCmd = new MySqlCommand(@" INSERT INTO reports.general_reports (PayerId, Allow, Public, Comment, FirmCode) select ?PayerId, ?Allow, ?Public, ?Comment, min(Id) from ( select s.Id from Customers.Suppliers s where s.Payer = ?payerID ) tbl; select last_insert_id() as GRLastInsertID; ", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("Allow", MySqlDbType.Byte)); InsCmd.Parameters["Allow"].Direction = ParameterDirection.Input; InsCmd.Parameters["Allow"].SourceColumn = Allow.ColumnName; InsCmd.Parameters["Allow"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("Public", MySqlDbType.Byte)); InsCmd.Parameters["Public"].Direction = ParameterDirection.Input; InsCmd.Parameters["Public"].SourceColumn = Public.ColumnName; InsCmd.Parameters["Public"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PayerId", MySqlDbType.Int64)); InsCmd.Parameters["PayerId"].Direction = ParameterDirection.Input; InsCmd.Parameters["PayerId"].SourceColumn = GRPayerID.ColumnName; InsCmd.Parameters["PayerId"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("Comment", MySqlDbType.VarString)); InsCmd.Parameters["Comment"].Direction = ParameterDirection.Input; InsCmd.Parameters["Comment"].SourceColumn = Comment.ColumnName; InsCmd.Parameters["Comment"].SourceVersion = DataRowVersion.Current; MyDA.UpdateCommand = UpdCmd; MyDA.DeleteCommand = DelCmd; MyDA.InsertCommand = InsCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); DataTable dtDeleted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Deleted); if (dtDeleted != null) { foreach (DataRow drDeleted in dtDeleted.Rows) { var code = Convert.ToUInt64(drDeleted[GeneralReportCode.ColumnName, DataRowVersion.Original]); var comment = drDeleted[Comment.ColumnName, DataRowVersion.Original].ToString(); SendDeleteAlert(code, comment, strUser, strHost); _deletedReports.Add(code); } MyDA.Update(dtDeleted); } dtInserted = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Added); if (dtInserted != null) { foreach (DataRow drInsert in dtInserted.Rows) { if (!Convert.IsDBNull(drInsert[GRPayerID.ColumnName]) && (drInsert[GRPayerID.ColumnName] is long)) { MyDA.Update(new DataRow[] { drInsert }); _updatedReports.Add(Convert.ToUInt64(drInsert["GRLastInsertID"])); } } } DataTable dtUpdated = DS.Tables[dtGeneralReports.TableName].GetChanges(DataRowState.Modified); if (dtUpdated != null) { foreach (DataRow drUpdate in dtUpdated.Rows) { if (drUpdate["Comment", DataRowVersion.Original] != drUpdate["Comment", DataRowVersion.Current] || drUpdate["Public", DataRowVersion.Original] != drUpdate["Public", DataRowVersion.Current] || drUpdate["Allow", DataRowVersion.Original] != drUpdate["Allow", DataRowVersion.Current]) { _updatedReports.Add(Convert.ToUInt64(drUpdate["GeneralReportCode"])); } } MyDA.Update(dtUpdated); } trans.Commit(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } //Удаляем задания для отчетов и обновляем комментарии в заданиях (или создаем эти задания) // А также включаем/выключаем задание при изменении галки "Включен" UpdateTasksForGeneralReports(_deletedReports, _updatedReports); PostData(); if (dgvReports.Rows.Count > 0) { btnApply.Visible = true; } else { btnApply.Visible = false; } if (dtInserted != null) { if (!Request.Url.OriginalString.Contains("#")) { Response.Redirect(Request.Url.OriginalString + "#addedPage"); } } }
protected void btnApply_Click(object sender, EventArgs e) { CopyChangesToTable(); MySqlTransaction trans; MyCn.Open(); trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.enum_values SET Value = ?evValue, DisplayValue = ?evDisplayValue WHERE ID = ?evID", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString)); UpdCmd.Parameters["evValue"].Direction = ParameterDirection.Input; UpdCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName; UpdCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString)); UpdCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input; UpdCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName; UpdCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("evID", MySqlDbType.Int64)); UpdCmd.Parameters["evID"].Direction = ParameterDirection.Input; UpdCmd.Parameters["evID"].SourceColumn = evID.ColumnName; UpdCmd.Parameters["evID"].SourceVersion = DataRowVersion.Current; MySqlCommand DelCmd = new MySqlCommand(@" DELETE from reports.enum_values WHERE ID = ?evDelID", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("evDelID", MySqlDbType.Int64)); DelCmd.Parameters["evDelID"].Direction = ParameterDirection.Input; DelCmd.Parameters["evDelID"].SourceColumn = evID.ColumnName; DelCmd.Parameters["evDelID"].SourceVersion = DataRowVersion.Original; MySqlCommand InsCmd = new MySqlCommand(@" INSERT INTO reports.enum_values SET Value = ?evValue, DisplayValue = ?evDisplayValue, PropertyEnumID = ?evPEID", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("evValue", MySqlDbType.VarString)); InsCmd.Parameters["evValue"].Direction = ParameterDirection.Input; InsCmd.Parameters["evValue"].SourceColumn = evValue.ColumnName; InsCmd.Parameters["evValue"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("evDisplayValue", MySqlDbType.VarString)); InsCmd.Parameters["evDisplayValue"].Direction = ParameterDirection.Input; InsCmd.Parameters["evDisplayValue"].SourceColumn = evDisplayValue.ColumnName; InsCmd.Parameters["evDisplayValue"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("evPEID", Request["e"])); MyDA.UpdateCommand = UpdCmd; MyDA.DeleteCommand = DelCmd; MyDA.InsertCommand = InsCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtEnumValues.TableName].TableName); trans.Commit(); PostData(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } if (dgvEnumValues.Rows.Count > 0) { btnApply.Visible = true; } else { btnApply.Visible = false; } }
public virtual List <Offer> GetOffers(int clientId, uint sourcePriceCode, uint?noiseSupplierId, bool allAssortment, bool byCatalog, bool withProducers) { ClientCode = clientId; InvokeGetActivePrices(); var assortmentSupplierId = Convert.ToUInt32( MySqlHelper.ExecuteScalar(Connection, @" select FirmCode from usersettings.pricesdata where pricesdata.PriceCode = ?PriceCode ", new MySqlParameter("?PriceCode", sourcePriceCode))); //Заполняем код региона прайс-листа как домашний код региона клиента, относительно которого строится отчет var SourceRegionCode = Convert.ToUInt64( MySqlHelper.ExecuteScalar(Connection, @" select RegionCode from Customers.Clients where Id = ?ClientCode", new MySqlParameter("?ClientCode", ClientCode))); var enabledCost = MySqlHelper.ExecuteScalar( Connection, "select CostCode from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode", new MySqlParameter("?SourcePC", sourcePriceCode), new MySqlParameter("?SourceRegionCode", SourceRegionCode)); if (enabledCost != null) { MySqlHelper.ExecuteNonQuery( Connection, @" drop temporary table IF EXISTS Usersettings.SourcePrice; create temporary table Usersettings.SourcePrice engine=MEMORY select * from ActivePrices where PriceCode = ?SourcePC and RegionCode = ?SourceRegionCode;", new MySqlParameter("?SourcePC", sourcePriceCode), new MySqlParameter("?SourceRegionCode", SourceRegionCode)); } var joinText = allAssortment || sourcePriceCode == 0 ? " Left JOIN " : " JOIN "; string withWithoutPropertiesText; if (byCatalog) { withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", GetCatalogProductNameSubquery("p.id")); } else { withWithoutPropertiesText = String.Format(@" if(C0.SynonymCode is not null, S.Synonym, {0}) ", QueryParts.GetFullFormSubquery("p.id", true)); } var firmcr = withProducers ? " and ifnull(C0.CodeFirmCr,0) = ifnull(c00.CodeFirmCr,0) " : string.Empty; var producerId = withProducers ? " ifnull(c00.CodeFirmCr, 0) " : " 0 "; var producerName = withProducers ? " if(c0.SynonymFirmCrCode is not null, Sfc.Synonym , Prod.Name) " : " '-' "; var result = new List <Offer>(); DataAdapter.SelectCommand.CommandText = string.Format( @" select p.CatalogId, c00.ProductId, {0} as ProducerId, {1} as ProductName, {2} as ProducerName, c00.Id as CoreId, c00.Code, Prices.FirmCode as SupplierId, c00.PriceCode as PriceId, Prices.RegionCode as RegionId, c00.Quantity, if(if(round(cc.Cost * Prices.Upcost, 2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2)) > c00.MaxBoundCost, c00.MaxBoundCost, if(round(cc.Cost*Prices.UpCost,2) < c00.MinBoundCost, c00.MinBoundCost, round(cc.Cost * Prices.Upcost, 2))) as Cost, c0.Id as AssortmentCoreId, c0.Code as AssortmentCode, c0.CodeCr as AssortmentCodeCr, {9} as AssortmentSupplierId, c0.PriceCode as AssortmentPriceId, {10} as AssortmentRegionId, c0.Quantity as AssortmentQuantity, {7} as AssortmentCost from Usersettings.ActivePrices Prices join farm.core0 c00 on c00.PriceCode = Prices.PriceCode join farm.CoreCosts cc on cc.Core_Id = c00.Id and cc.PC_CostCode = Prices.CostCode join catalogs.Products as p on p.id = c00.productid join Catalogs.Catalog as cg on p.catalogid = cg.id {3} farm.Core0 c0 on c0.productid = c00.productid {4} and C0.PriceCode = {5} {6} left join Catalogs.Producers Prod on c00.CodeFirmCr = Prod.Id left join farm.Synonym S on C0.SynonymCode = S.SynonymCode left join farm.SynonymFirmCr Sfc on C0.SynonymFirmCrCode = Sfc.SynonymFirmCrCode {8} WHERE {11} ", producerId, withWithoutPropertiesText, producerName, joinText, firmcr, sourcePriceCode, (enabledCost != null) ? @" left join farm.CoreCosts cc0 on cc0.Core_Id = c0.Id and cc0.PC_CostCode = " + enabledCost + @" left join Usersettings.SourcePrice c0Prices on c0Prices.CostCode = " + enabledCost : "", (enabledCost != null) ? @" if(cc0.Cost is null, 0, if(if(round(cc0.Cost * c0Prices.Upcost, 2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2)) > c0.MaxBoundCost, c0.MaxBoundCost, if(round(cc0.Cost*c0Prices.UpCost,2) < c0.MinBoundCost, c0.MinBoundCost, round(cc0.Cost * c0Prices.Upcost, 2))) )" : " null ", @"", assortmentSupplierId, SourceRegionCode, sourcePriceCode == 0 ? " c00.Junk = 0 " : @" ({1} (c0.PriceCode <> c00.PriceCode) or (Prices.RegionCode <> {0}) or (c0.Id = c00.Id)) and (c00.Junk = 0 or c0.Id = c00.Id)".Format(SourceRegionCode, allAssortment || sourcePriceCode == 0 ? "(c0.PriceCode is null) or" : string.Empty)); Random random = null; if (noiseSupplierId.HasValue) { random = new Random(); } #if DEBUG Debug.WriteLine(DataAdapter.SelectCommand.CommandText); #endif using (var reader = DataAdapter.SelectCommand.ExecuteReader()) { foreach (var row in reader.Cast <IDataRecord>()) { var offer = new Offer(row, noiseSupplierId, random); result.Add(offer); } } return(result); }
protected void btnApply_Click(object sender, EventArgs e) { CopyChangesToTable(); MySqlTransaction trans; MyCn.Open(); trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.reports SET ReportCaption = ?RReportCaption, ReportTypeCode = ?RReportTypeCode, GeneralReportCode = ?RGeneralReportCode, Enabled = ?REnabled WHERE ReportCode = ?RReportCode", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString)); UpdCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName; UpdCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64)); UpdCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName; UpdCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RReportCode", MySqlDbType.Int64)); UpdCmd.Parameters["RReportCode"].Direction = ParameterDirection.Input; UpdCmd.Parameters["RReportCode"].SourceColumn = RReportCode.ColumnName; UpdCmd.Parameters["RReportCode"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte)); UpdCmd.Parameters["REnabled"].Direction = ParameterDirection.Input; UpdCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName; UpdCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId)); MySqlCommand DelCmd = new MySqlCommand(@" DELETE from reports.reports WHERE ReportCode = ?RDelReportCode", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("RDelReportCode", MySqlDbType.Int64)); DelCmd.Parameters["RDelReportCode"].Direction = ParameterDirection.Input; DelCmd.Parameters["RDelReportCode"].SourceColumn = RReportCode.ColumnName; DelCmd.Parameters["RDelReportCode"].SourceVersion = DataRowVersion.Original; MySqlCommand InsCmd = new MySqlCommand(@" INSERT INTO reports.reports SET ReportCaption = ?RReportCaption, ReportTypeCode = ?RReportTypeCode, GeneralReportCode = ?RGeneralReportCode, Enabled = ?REnabled ", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("RReportCaption", MySqlDbType.VarString)); InsCmd.Parameters["RReportCaption"].Direction = ParameterDirection.Input; InsCmd.Parameters["RReportCaption"].SourceColumn = RReportCaption.ColumnName; InsCmd.Parameters["RReportCaption"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("RReportTypeCode", MySqlDbType.Int64)); InsCmd.Parameters["RReportTypeCode"].Direction = ParameterDirection.Input; InsCmd.Parameters["RReportTypeCode"].SourceColumn = RReportTypeCode.ColumnName; InsCmd.Parameters["RReportTypeCode"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("REnabled", MySqlDbType.Byte)); InsCmd.Parameters["REnabled"].Direction = ParameterDirection.Input; InsCmd.Parameters["REnabled"].SourceColumn = REnabled.ColumnName; InsCmd.Parameters["REnabled"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("RGeneralReportCode", _templateReportId)); MyDA.UpdateCommand = UpdCmd; MyDA.DeleteCommand = DelCmd; MyDA.InsertCommand = InsCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtReports.TableName].TableName); trans.Commit(); PostData(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } if (dgvReports.Rows.Count > 0) { btnApply.Visible = true; } else { btnApply.Visible = false; } }
protected void btnApply_Click(object sender, EventArgs e) { if (Page.IsValid) { CopyChangesToTable(); MySqlTransaction trans; MyCn.Open(); trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.report_type_properties SET PropertyName = ?PName, DisplayName = ?PDisplayName, DefaultValue = ?PDefaultValue, PropertyType = ?PType, Optional = ?POptional, PropertyEnumID = ?PEnumID, SelectStoredProcedure = ?PStoredProc WHERE ID = ?PID", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString)); UpdCmd.Parameters["PName"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PName"].SourceColumn = PName.ColumnName; UpdCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString)); UpdCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName; UpdCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString)); UpdCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName; UpdCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString)); UpdCmd.Parameters["PType"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PType"].SourceColumn = PType.ColumnName; UpdCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte)); UpdCmd.Parameters["POptional"].Direction = ParameterDirection.Input; UpdCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName; UpdCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64)); UpdCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName; UpdCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString)); UpdCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName; UpdCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("PID", MySqlDbType.Int64)); UpdCmd.Parameters["PID"].Direction = ParameterDirection.Input; UpdCmd.Parameters["PID"].SourceColumn = PID.ColumnName; UpdCmd.Parameters["PID"].SourceVersion = DataRowVersion.Current; MySqlCommand DelCmd = new MySqlCommand(@" DELETE from reports.report_type_properties WHERE ID = ?PDelID", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("PDelID", MySqlDbType.Int64)); DelCmd.Parameters["PDelID"].Direction = ParameterDirection.Input; DelCmd.Parameters["PDelID"].SourceColumn = PID.ColumnName; DelCmd.Parameters["PDelID"].SourceVersion = DataRowVersion.Original; MySqlCommand InsCmd = new MySqlCommand(@" INSERT INTO reports.report_type_properties SET PropertyName = ?PName, DisplayName = ?PDisplayName, DefaultValue = ?PDefaultValue, PropertyType = ?PType, PropertyEnumID = ?PEnumID, Optional = ?POptional, SelectStoredProcedure = ?PStoredProc, ReportTypeCode = ?rtc ", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("PName", MySqlDbType.VarString)); InsCmd.Parameters["PName"].Direction = ParameterDirection.Input; InsCmd.Parameters["PName"].SourceColumn = PName.ColumnName; InsCmd.Parameters["PName"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PDisplayName", MySqlDbType.VarString)); InsCmd.Parameters["PDisplayName"].Direction = ParameterDirection.Input; InsCmd.Parameters["PDisplayName"].SourceColumn = PDisplayName.ColumnName; InsCmd.Parameters["PDisplayName"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PDefaultValue", MySqlDbType.VarString)); InsCmd.Parameters["PDefaultValue"].Direction = ParameterDirection.Input; InsCmd.Parameters["PDefaultValue"].SourceColumn = PDefaultValue.ColumnName; InsCmd.Parameters["PDefaultValue"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PType", MySqlDbType.VarString)); InsCmd.Parameters["PType"].Direction = ParameterDirection.Input; InsCmd.Parameters["PType"].SourceColumn = PType.ColumnName; InsCmd.Parameters["PType"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("POptional", MySqlDbType.Byte)); InsCmd.Parameters["POptional"].Direction = ParameterDirection.Input; InsCmd.Parameters["POptional"].SourceColumn = POptional.ColumnName; InsCmd.Parameters["POptional"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PEnumID", MySqlDbType.Int64)); InsCmd.Parameters["PEnumID"].Direction = ParameterDirection.Input; InsCmd.Parameters["PEnumID"].SourceColumn = PEnumID.ColumnName; InsCmd.Parameters["PEnumID"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("PStoredProc", MySqlDbType.VarString)); InsCmd.Parameters["PStoredProc"].Direction = ParameterDirection.Input; InsCmd.Parameters["PStoredProc"].SourceColumn = PStoredProc.ColumnName; InsCmd.Parameters["PStoredProc"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("rtc", Request["rtc"])); MyDA.UpdateCommand = UpdCmd; MyDA.DeleteCommand = DelCmd; MyDA.InsertCommand = InsCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtProperties.TableName].TableName); trans.Commit(); PostData(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } btnApply.Visible = dgvProperties.Rows.Count > 0; } }
/** * 从雅虎获取股票历史数据存款数据库 * */ public static void loadAllHisDataFromYaohu(DateTime bdate, DateTime edate) { string gpcodes = getGpsForLoadYaohHis(); char[] ch = new char[] { ',' }; string[] gpcodeList = gpcodes.Split(ch); string code = ""; DataTable hisData = null; MySqlHelper help = new MySqlHelper(); //已经保存的历史数据 MySqlParameter[] parms = new MySqlParameter[] { }; DataTable codeDatas = help.ExecuteDataTable("SELECT DISTINCT(g.Code) FROM gpyaohhis g where g.AdjClose IS NOT NULL", parms); StringBuilder sb = new StringBuilder(); foreach (DataRow row in codeDatas.Rows) { code = row["code"].ToString(); if (code.IndexOf("sh") != -1) { code = code.Replace("sh", ""); code = code + ".ss"; } else { code = code.Replace("sz", ""); code = code + ".sz"; } sb.Append(code).Append(","); } string saveCodes = sb.ToString(); if (!string.IsNullOrEmpty(saveCodes)) { saveCodes = saveCodes.Substring(0, saveCodes.LastIndexOf(",")); } //Console.WriteLine("####saveCodes:[" + saveCodes + "]"); //个股http://table.finance.yahoo.com/table.csv?a=9&b=24&c=2015&d=10&e=23&f=2015&s=603883.ss string tmpCode = ""; string oldCode = ""; string excodes = "399006,603021"; for (int i = 0; i < gpcodeList.Length; i++) { code = gpcodeList[i]; oldCode = code; tmpCode = code.Replace("sh", "").Replace("sz", ""); if (String.IsNullOrEmpty(code)) { continue; } if (code.IndexOf("sh") != -1) { code = code.Replace("sh", ""); code = code + ".ss"; } else { code = code.Replace("sz", ""); code = code + ".sz"; } if (saveCodes.IndexOf(code) != -1 || excodes.IndexOf(tmpCode) != -1) { continue; } Console.WriteLine("开始[" + code + "]"); //历史数据 try { hisData = SinaAPI.getGuoPiaoHisDataFromYaoh(bdate, edate, code); } catch (Exception e) { Console.Write(e.Message.ToString()); Console.Write("update cnt:"+help.ExecuteNonQuery("update gp set updhis='1' where code='" + oldCode + "'")); continue; } if (hisData == null || hisData.Rows.Count <= 0) { return; } Console.Write(hisData.Rows.Count.ToString()); //保存到数据库 hisData.TableName = "gpyaohhis"; MySqlHelper.BulkInsert(help.ConnectionString, hisData); Console.WriteLine("结束[" + code + "]"); } }
private void ApplyOptimal(MySqlTransaction trans) { MySqlCommand UpdCmd = new MySqlCommand(@" UPDATE reports.report_properties SET PropertyValue = ?OPPropertyValue WHERE ID = ?OPID", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64)); UpdCmd.Parameters["OPID"].Direction = ParameterDirection.Input; UpdCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName; UpdCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("OPPropertyValue", MySqlDbType.VarString)); UpdCmd.Parameters["OPPropertyValue"].Direction = ParameterDirection.Input; UpdCmd.Parameters["OPPropertyValue"].SourceColumn = OPPropertyValue.ColumnName; UpdCmd.Parameters["OPPropertyValue"].SourceVersion = DataRowVersion.Current; MySqlCommand InsCmd = new MySqlCommand(@" INSERT reports.report_properties SET ReportCode = ?rp, PropertyID = ?OPrtpID, PropertyValue = ?OPPropertyValue ", MyCn, trans); InsCmd.Parameters.Clear(); InsCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64)); InsCmd.Parameters["OPID"].Direction = ParameterDirection.Input; InsCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName; InsCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("OPPropertyValue", MySqlDbType.VarString)); InsCmd.Parameters["OPPropertyValue"].Direction = ParameterDirection.Input; InsCmd.Parameters["OPPropertyValue"].SourceColumn = OPPropertyValue.ColumnName; InsCmd.Parameters["OPPropertyValue"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("OPrtpID", MySqlDbType.Int64)); InsCmd.Parameters["OPrtpID"].Direction = ParameterDirection.Input; InsCmd.Parameters["OPrtpID"].SourceColumn = OPrtpID.ColumnName; InsCmd.Parameters["OPrtpID"].SourceVersion = DataRowVersion.Current; InsCmd.Parameters.Add(new MySqlParameter("rp", Request["rp"])); MySqlCommand DelCmd = new MySqlCommand(@" DELETE FROM reports.report_properties WHERE ID = ?OPID", MyCn, trans); DelCmd.Parameters.Clear(); DelCmd.Parameters.Add(new MySqlParameter("OPID", MySqlDbType.Int64)); DelCmd.Parameters["OPID"].Direction = ParameterDirection.Input; DelCmd.Parameters["OPID"].SourceColumn = OPID.ColumnName; DelCmd.Parameters["OPID"].SourceVersion = DataRowVersion.Original; MyDA.UpdateCommand = UpdCmd; MyDA.InsertCommand = InsCmd; MyDA.DeleteCommand = DelCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtOptionalParams.TableName].TableName); }
protected void btnApply_Click(object sender, EventArgs e) { CopyChangesToTable(); string ins = String.Empty; string del = String.Empty; foreach (DataRow dr in DS.Tables[dtProcResult.TableName].Rows) { if (dr.RowState == DataRowState.Modified) { if (dr[Enabled.ColumnName, DataRowVersion.Original].ToString() == dr[Enabled.ColumnName, DataRowVersion.Current].ToString()) { dr.RejectChanges(); } } } MySqlTransaction trans; MyCn.Open(); trans = MyCn.BeginTransaction(IsolationLevel.ReadCommitted); try { MySqlCommand UpdCmd = new MySqlCommand(@" insert into reports.report_property_values (ReportPropertyID, Value) select r.ID, ?Value from reports.report_properties r where r.ID = ?RPID and ?Enabled = 1; delete from reports.report_property_values where ReportPropertyID = ?RPID and Value = ?Value and ?Enabled = 0;", MyCn, trans); UpdCmd.Parameters.Clear(); UpdCmd.Parameters.Add(new MySqlParameter("Value", MySqlDbType.Int64)); UpdCmd.Parameters["Value"].Direction = ParameterDirection.Input; UpdCmd.Parameters["Value"].SourceColumn = PRID.ColumnName; UpdCmd.Parameters["Value"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("Enabled", MySqlDbType.Byte)); UpdCmd.Parameters["Enabled"].Direction = ParameterDirection.Input; UpdCmd.Parameters["Enabled"].SourceColumn = Enabled.ColumnName; UpdCmd.Parameters["Enabled"].SourceVersion = DataRowVersion.Current; UpdCmd.Parameters.Add(new MySqlParameter("RPID", property.Id)); MyDA.UpdateCommand = UpdCmd; string strHost = HttpContext.Current.Request.UserHostAddress; string strUser = HttpContext.Current.User.Identity.Name; if (strUser.StartsWith("ANALIT\\")) { strUser = strUser.Substring(7); } MySqlHelper.ExecuteNonQuery(trans.Connection, "set @INHost = ?Host; set @INUser = ?User", new MySqlParameter[] { new MySqlParameter("Host", strHost), new MySqlParameter("User", strUser) }); MyDA.Update(DS, DS.Tables[dtProcResult.TableName].TableName); trans.Commit(); DS.Tables[dtProcResult.TableName].AcceptChanges(); PostData(); } catch { trans.Rollback(); throw; } finally { MyCn.Close(); } if (dgvListValues.Rows.Count > 0) { btnApply.Visible = true; } else { btnApply.Visible = false; } }