public static ExecuteDataset ( |
||
connection | ||
commandText | string | Command to execute |
return | DataSet |
protected string GetSupplierName(uint priceId) { string customerFirmName; var drPrice = MySqlHelper.ExecuteDataset( Connection, @" select concat(suppliers.Name, '(', pricesdata.PriceName, ') - ', regions.Region) as FirmName, pricesdata.PriceCode, suppliers.HomeRegion from usersettings.pricesdata, Customers.suppliers, farm.regions where pricesdata.PriceCode = ?PriceCode and suppliers.Id = pricesdata.FirmCode and regions.RegionCode = suppliers.HomeRegion limit 1", new MySqlParameter("?PriceCode", priceId)) .Tables[0].AsEnumerable().FirstOrDefault(); if (drPrice != null) { customerFirmName = drPrice["FirmName"].ToString(); } else { throw new ReportException($"Не найден прайс-лист с кодом {priceId}."); } return(customerFirmName); }
public static List <ulong> GetAddressIds(ulong supplierId, string supplierDeliveryId) { var parametrs = new[] { new MySqlParameter("?SupplierId", supplierId), new MySqlParameter("?SupplierDeliveryId", supplierDeliveryId) }; var sql = SqlGetClientAddressId(false, true); var ds = With.Connection(c => MySqlHelper.ExecuteDataset( c, sql, parametrs)); return(ds.Tables[0].AsEnumerable().Select(r => Convert.ToUInt64(r["AddressId"])).ToList()); }
public List <uint> Query() { var parametrs = new List <MySqlParameter> { new MySqlParameter("?SupplierId", supplierId), new MySqlParameter("?SupplierDeliveryId", SupplierDeliveryId) }; if (includeClientId) { parametrs.Add(new MySqlParameter("?SupplierClientId", SupplierClientId)); } var sql = SqlGetClientAddressId(includeClientId, true); var ds = With.DeadlockWraper(() => With.Connection(c => MySqlHelper.ExecuteDataset( c, sql, parametrs.ToArray()))); return(ds.Tables[0].AsEnumerable().Select(r => Convert.ToUInt32(r["AddressId"])).ToList()); }
public override List <ulong> ParseAddressIds(MySqlConnection Connection, ulong FirmCode, string ArchFileName, string CurrentFileName) { var list = new List <ulong>(); var SQL = AddressIdQuery.SqlGetClientAddressId(true, true); string FirmClientCode, DeliveryCode; try { string[] parts = Path.GetFileNameWithoutExtension(CurrentFileName).Split('_'); FirmClientCode = parts[0]; DeliveryCode = parts[1]; } catch (Exception ex) { throw new Exception("Не получилось сформировать SupplierClientId(FirmClientCode) и SupplierDeliveryId(FirmClientCode2) из документа.", ex); } var ds = MySqlHelper.ExecuteDataset( Connection, SQL, new MySqlParameter("?SupplierId", FirmCode), new MySqlParameter("?SupplierClientId", FirmClientCode), new MySqlParameter("?SupplierDeliveryId", DeliveryCode)); foreach (DataRow drApteka in ds.Tables[0].Rows) { list.Add(Convert.ToUInt64(drApteka["AddressId"])); } if (list.Count == 0) { throw new Exception("Не удалось найти клиентов с SupplierClientId(FirmClientCode) = " + FirmClientCode + " и SupplierDeliveryId(FirmClientCode2) = " + DeliveryCode + "."); } return(list); }
/// <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(); } }
public static DataSet ExecuteDataset(MySqlConnection connection, string commandText) { return(MySqlHelper.ExecuteDataset(connection, commandText, null)); }