public DataTable GetDataTable(string query, Dictionary <string, string> parameters) { DataTable dataTable = new DataTable(); if (!IsConnectionOpened()) { return(dataTable); } try { FbCommand command = new FbCommand(query, connection); if (parameters.Count > 0) { foreach (KeyValuePair <string, string> parameter in parameters) { command.Parameters.AddWithValue(parameter.Key, parameter.Value); } } FbDataAdapter fbDataAdapter = new FbDataAdapter(command); fbDataAdapter.Fill(dataTable); } catch (Exception e) { string subject = "Ошибка выполнения запроса к БД"; string body = e.Message + Environment.NewLine + e.StackTrace; MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error); SystemLogging.LogMessageToFile(subject + " " + body); connection.Close(); } return(dataTable); }
public bool ExecuteUpdateQuery(string query, Dictionary <string, object> parameters) { bool updated = false; if (!IsConnectionOpened()) { return(updated); } try { FbCommand update = new FbCommand(query, connection); if (parameters.Count > 0) { foreach (KeyValuePair <string, object> parameter in parameters) { update.Parameters.AddWithValue(parameter.Key, parameter.Value); } } updated = update.ExecuteNonQuery() > 0 ? true : false; } catch (Exception e) { string subject = "Ошибка выполнения запроса к БД"; string body = e.Message + Environment.NewLine + e.StackTrace; MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error); SystemLogging.LogMessageToFile(subject + " " + body); connection.Close(); } return(updated); }
private bool IsConnectionOpened() { if (connection.State != ConnectionState.Open) { try { connection.Open(); } catch (Exception e) { string subject = "Ошибка подключения к БД"; string body = e.Message + Environment.NewLine + e.StackTrace; MessageBox.Show(body, subject, MessageBoxButton.OK, MessageBoxImage.Error); SystemLogging.LogMessageToFile(subject + " " + body); } } return(connection.State == ConnectionState.Open); }
public static string WriteDataToExcel(List <ItemTreatmentDetails> details, string resultFilePrefix, BackgroundWorker backgroundWorker, double progressCurrent) { string templateFile = SystemLogging.AssemblyDirectory + "Template.xlsx"; double progressStep = 10.0 / details.Count; foreach (char item in Path.GetInvalidFileNameChars()) { resultFilePrefix = resultFilePrefix.Replace(item, '-'); } if (!File.Exists(templateFile)) { return("Не удалось найти файл шаблона: " + templateFile); } string resultPath = Path.Combine(SystemLogging.AssemblyDirectory, "Results"); if (!Directory.Exists(resultPath)) { Directory.CreateDirectory(resultPath); } string resultFile = Path.Combine(resultPath, resultFilePrefix + ".xlsx"); IWorkbook workbook; using (FileStream stream = new FileStream(templateFile, FileMode.Open, FileAccess.Read)) workbook = new XSSFWorkbook(stream); int rowNumber = 1; int columnNumber = 0; ISheet sheet = workbook.GetSheet("Data"); foreach (ItemTreatmentDetails item in details) { backgroundWorker.ReportProgress((int)progressCurrent, "Запись в excel строки " + rowNumber + " / " + details.Count); IRow row = sheet.CreateRow(rowNumber); //Console.WriteLine("create row: " + row); DateTime treatDate = DateTime.ParseExact(item.TREATDATE, "dd.MM.yyyy h:mm:ss", CultureInfo.InvariantCulture); double period = -1; string referralPeriodMax = string.Empty; foreach (ItemReferral referral in item.Referrals) { if (string.IsNullOrEmpty(referral.TREATDATE1)) { continue; } DateTime refDate = DateTime.ParseExact(referral.TREATDATE1, "dd.MM.yyyy h:mm:ss", CultureInfo.InvariantCulture); double days = (refDate - treatDate).TotalDays; if (days > period) { period = days; } } if (period > -1) { referralPeriodMax = period.ToString(); } string[] array = new string[] { item.TREATDATE, item.FILIALNAME, item.DEPNAME, item.DOCNAME, item.PATIENTNAME, item.HISTNUM, item.BDATE, item.TREAT_TYPE, item.MKBCODE, item.Referrals.Count.ToString(), referralPeriodMax }; //Console.WriteLine("values: " + string.Join(", ", array)); foreach (string value in array) { try { ICell cell = row.CreateCell(columnNumber); Console.WriteLine("create column: " + columnNumber); string valueToWrite = value.Replace(" 0:00:00", ""); if (double.TryParse(valueToWrite, out double result)) { cell.SetCellValue(result); } else { cell.SetCellValue(valueToWrite); } } catch (Exception e) { Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace); } columnNumber++; } foreach (ItemReferral referral in item.Referrals) { string name = referral.SCHNAME; int isActivated = string.IsNullOrEmpty(referral.TREATDATE1) ? 0 : 1; for (int i = columnNumber; i < 16000; i++) { string currentValue = ""; try { if (i < sheet.GetRow(0).LastCellNum) { currentValue = sheet.GetRow(0).GetCell(i).StringCellValue; } } catch (Exception) { } try { if (string.IsNullOrEmpty(currentValue)) { ICell cellHeader = sheet.GetRow(0).CreateCell(i); cellHeader.SetCellValue(name); ICell cellValue = row.CreateCell(i); cellValue.SetCellValue(isActivated); break; } } catch (Exception e) { Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace); } try { if (currentValue.Equals(name)) { ICell cell = row.CreateCell(i); cell.SetCellValue(isActivated); break; } } catch (Exception e) { Console.WriteLine(e.Message + Environment.NewLine + e.StackTrace); } } } columnNumber = 0; rowNumber++; progressCurrent += progressStep; } sheet = workbook.GetSheet("Services"); int rowUsed = 1; Console.WriteLine("======================================="); Console.WriteLine("sheet.LastRowNum: " + sheet.LastRowNum); foreach (ItemTreatmentDetails item in details) { foreach (ItemReferral referral in item.Referrals) { string name = referral.SCHNAME; bool isPresent = false; bool isActivated = (string.IsNullOrEmpty(referral.TREATDATE1)) ? false : true; double.TryParse(referral.SCOUNT, out double currentTotal); double.TryParse(referral.SCHCOUNT, out double currentActivated); Console.WriteLine("name: " + name + " | " + currentTotal + " " + currentActivated); for (int row = 1; row < rowUsed; row++) { try { string rowValue = sheet.GetRow(row).GetCell(0).StringCellValue; if (rowValue.Equals(name)) { double total = sheet.GetRow(row).GetCell(1).NumericCellValue; double activated = sheet.GetRow(row).GetCell(2).NumericCellValue; Console.WriteLine("finded: " + total + " - " + activated); total += currentTotal; activated += currentActivated; sheet.GetRow(row).GetCell(1).SetCellValue(total); sheet.GetRow(row).GetCell(2).SetCellValue(activated); isPresent = true; break; } } catch (Exception) { break; } } if (isPresent) { continue; } IRow rowLine = sheet.CreateRow(rowUsed); ICell cellName = rowLine.CreateCell(0); ICell cellTotal = rowLine.CreateCell(1); ICell cellActivated = rowLine.CreateCell(2); cellName.SetCellValue(name); cellTotal.SetCellValue(currentTotal); cellActivated.SetCellValue(currentActivated); rowUsed++; } } Console.WriteLine("======================================="); using (FileStream stream = new FileStream(resultFile, FileMode.Create, FileAccess.Write)) workbook.Write(stream); workbook.Close(); Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { return("Не удалось открыть приложение Excel"); } xlApp.Visible = false; Excel.Workbook wb = xlApp.Workbooks.Open(resultFile); if (wb == null) { return("Не удалось открыть книгу " + resultFile); } Excel.Worksheet ws = wb.Sheets["Data"]; if (ws == null) { return("Не удалось открыть лист Data"); } try { PerformSheet(wb, ws, xlApp); } catch (Exception e) { SystemLogging.LogMessageToFile(e.Message + Environment.NewLine + e.StackTrace); } ws = wb.Sheets["Services"]; ws.Activate(); try { PerformSheetServices(wb, ws, xlApp); } catch (Exception e) { SystemLogging.LogMessageToFile(e.Message + Environment.NewLine + e.StackTrace); } wb.Save(); wb.Close(); xlApp.Quit(); return(resultFile); }