private void UpdateTzToDB() { bool successToLoadData = false; System.Collections.Generic.List<string> Parameters = new List<string>(); System.Collections.Generic.List<string> DataFromTextBox = new List<string>(); string cmdName = "UPDATE USP_TZ_DATA SET "; try { for (int i = 0; i < this.tabPage1.Controls.Count; i++) { if (this.tabPage1.Controls[i].Controls.Count == 0) { if ((this.tabPage1.Controls[i].GetType() == typeof(ComboBox))) { cmdName += " " + this.tabPage1.Controls[i].Name + " = :" + this.tabPage1.Controls[i].Name + ","; Parameters.Add(this.tabPage1.Controls[i].Name); DataFromTextBox.Add(PriemSpisanie.IsNullParametr((ComboBox)this.tabPage1.Controls[i])); } else if ((this.tabPage1.Controls[i].GetType() == typeof(TextBox))) { cmdName += " " + this.tabPage1.Controls[i].Name + " = :" + this.tabPage1.Controls[i].Name + ","; Parameters.Add(this.tabPage1.Controls[i].Name); DataFromTextBox.Add(PriemSpisanie.IsNullParametr((TextBox)this.tabPage1.Controls[i])); } else if (this.tabPage1.Controls[i].GetType() == typeof(DateTimePicker)) { cmdName += " " + this.tabPage1.Controls[i].Name + " = to_date(:" + this.tabPage1.Controls[i].Name + ",'DD.MM.YYYY hh24:mi:ss'),"; Parameters.Add(this.tabPage1.Controls[i].Name); DataFromTextBox.Add(((DateTimePicker)(this.tabPage1.Controls[i])).Value.ToString()); } else { continue; } } else if (this.tabPage1.Controls[i].Controls.Count > 0) { UpdateDBCycle(this.tabPage1.Controls[i], ref cmdName, ref Parameters, ref DataFromTextBox); } } for (int i = 0; i < this.tabPage2.Controls.Count; i++) { if (this.tabPage2.Controls[i].Controls.Count == 0) { if ((this.tabPage2.Controls[i].GetType() == typeof(ComboBox))) { cmdName += " " + this.tabPage2.Controls[i].Name + " = :" + this.tabPage2.Controls[i].Name + ","; Parameters.Add(this.tabPage2.Controls[i].Name); DataFromTextBox.Add(PriemSpisanie.IsNullParametr((ComboBox)this.tabPage2.Controls[i])); } else if ((this.tabPage2.Controls[i].GetType() == typeof(TextBox))) { cmdName += " " + this.tabPage2.Controls[i].Name + " = :" + this.tabPage2.Controls[i].Name + ","; Parameters.Add(this.tabPage2.Controls[i].Name); DataFromTextBox.Add(PriemSpisanie.IsNullParametr((TextBox)this.tabPage2.Controls[i])); } else if (this.tabPage2.Controls[i].GetType() == typeof(DateTimePicker)) { cmdName += " " + this.tabPage2.Controls[i].Name + " = to_date(:" + this.tabPage2.Controls[i].Name + ",'DD.MM.YYYY hh24:mi:ss'),"; Parameters.Add(this.tabPage2.Controls[i].Name); DataFromTextBox.Add(((DateTimePicker)(this.tabPage2.Controls[i])).Value.ToString()); } else { continue; } } else if ((this.tabPage2.Controls[i].Controls.Count > 0) && ((String.Compare(this.tabPage2.Controls[i].Name, "panel45")) != 0)) { UpdateDBCycle(this.tabPage2.Controls[i], ref cmdName, ref Parameters, ref DataFromTextBox); } } cmdName = cmdName.Remove(cmdName.Length - 1); cmdName += " WHERE ID_DOC = '" + number + "'"; } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox); Parameters.Clear(); DataFromTextBox.Clear(); for (int i = 1; i < 10; i++) { if (successToLoadData == true) { cmdName = "UPDATE USP_TZ_DATA_TP SET NAIM_KOMP = :NAIM_KOMP,OBOZN_KOMP = :OBOZN_KOMP, KOL = :KOL WHERE NUM_PANEL = '" + i.ToString() + "' AND ID_DOC = '" + number +"'"; Parameters.Add("NAIM_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel33.Controls.Find(("NAIM_KOMP0" + i.ToString()), true)[0])))); Parameters.Add("OBOZN_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel32.Controls.Find(("OBOZN_KOMP0" + i.ToString()), true)[0])))); Parameters.Add("KOL"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel34.Controls.Find(("KOL0" + i.ToString()), true)[0])))); successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox); Parameters.Clear(); DataFromTextBox.Clear(); } } for (int i = 10; i < 21; i++) { if (successToLoadData == true) { cmdName = "UPDATE USP_TZ_DATA_TP SET NAIM_KOMP = :NAIM_KOMP,OBOZN_KOMP = :OBOZN_KOMP, KOL = :KOL WHERE NUM_PANEL = '" + i.ToString() + "' AND ID_DOC = '" + number + "'"; Parameters.Add("NAIM_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel33.Controls.Find(("NAIM_KOMP" + i.ToString()), true)[0])))); Parameters.Add("OBOZN_KOMP"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel32.Controls.Find(("OBOZN_KOMP" + i.ToString()), true)[0])))); Parameters.Add("KOL"); DataFromTextBox.Add(PriemSpisanie.IsNullParametr(((TextBox)(this.panel34.Controls.Find(("KOL" + i.ToString()), true)[0])))); successToLoadData = SQLOracle.UpdateQuery(cmdName, Parameters, DataFromTextBox); Parameters.Clear(); DataFromTextBox.Clear(); } } if ((BMPIsLoad == 1)||(BMPIsLoad == 3)) if ((this.SCETCH1.Image != null) && (successToLoadData == true)) using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH1.Image.Save(tempFile.FilePath.ToString()); System.IO.FileStream FileStreamSCETCH1 = new System.IO.FileStream(tempFile.FilePath.ToString(), System.IO.FileMode.Open, System.IO.FileAccess.Read); byte[] SCETCH1InByte = new Byte[FileStreamSCETCH1.Length]; FileStreamSCETCH1.Read(SCETCH1InByte, 0, SCETCH1InByte.Length); FileStreamSCETCH1.Close(); IDisposable disp = (IDisposable)FileStreamSCETCH1; disp.Dispose(); successToLoadData = SQLOracle.BLOBUpdateQuery(("UPDATE USP_TZ_DATA SET SCETCH1 = :SCETCH1 WHERE ID_DOC = '" + number + "'"), "SCETCH1", SCETCH1InByte); FileStreamSCETCH1.Dispose(); } if ((BMPIsLoad == 2) || (BMPIsLoad == 3)) if ((this.SCETCH2.Image != null) && (successToLoadData == true)) using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH2.Image.Save(tempFile.FilePath.ToString()); System.IO.FileStream FileStreamBMP = new System.IO.FileStream(tempFile.FilePath.ToString(), System.IO.FileMode.Open, System.IO.FileAccess.Read); byte[] BMPInByte = new Byte[FileStreamBMP.Length]; FileStreamBMP.Read(BMPInByte, 0, BMPInByte.Length); FileStreamBMP.Close(); IDisposable disp = (IDisposable)FileStreamBMP; disp.Dispose(); successToLoadData = SQLOracle.BLOBUpdateQuery(("UPDATE USP_TZ_DATA SET SCETCH2 = :SCETCH2 WHERE ID_DOC = '" + number + "'"), "SCETCH2", BMPInByte); FileStreamBMP.Dispose(); } if (successToLoadData == true) { MessageBox.Show("Загрузка прошла успешно!"); } else { MessageBox.Show("Загрузка прошла неудачно!"); } }
/// <summary> /// Выгрузка данных в Excel; /// </summary> /// <returns></returns> public void createXLS() { ExcelClass TZExcel = new ExcelClass(); HashCode.HashCode.CheckFileByHash("техническое задание.xlt"); if (System.IO.File.Exists(Program.PathString + "\\техническое задание.xlt")) { try { TZExcel.OpenDocument(Program.PathString + "\\техническое задание.xlt"); TZExcel.Visible = false; if ((BMPIsLoad == 1) || (BMPIsLoad == 3)) { if (this.SCETCH1.Image != null) { using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH1.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp); TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing); TZExcel.WritePictureToCell(tempFile.FilePath.ToString()); } } } else if ((BMPIsLoad == 0) || (BMPIsLoad == 2)) { if (this.SCETCH1.Image != null) { TZExcel.SelectCells(this.SCETCH1.Name, Type.Missing); TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH1 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'")); } } if ((BMPIsLoad == 2) || (BMPIsLoad == 3)) { if (this.SCETCH2.Image != null) { using (TemporaryFile tempFile = new TemporaryFile()) { this.SCETCH2.Image.Save(tempFile.FilePath.ToString(), System.Drawing.Imaging.ImageFormat.Bmp); TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing); TZExcel.WritePictureToCell(tempFile.FilePath.ToString()); } } } else if ((BMPIsLoad == 0) || (BMPIsLoad == 1)) { if (this.SCETCH2.Image != null) { TZExcel.SelectCells(this.SCETCH2.Name, Type.Missing); TZExcel.WritePictureToCell(SQLOracle.LoadImageToTemp("Select SCETCH2 FROM USP_TZ_DATA WHERE ID_DOC = '" + number + "'")); } } for (int i = 0; i < this.tabPage1.Controls.Count; i++) { if (this.tabPage1.Controls[i].Controls.Count == 0) { if ((this.tabPage1.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage1.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage1.Controls[i].GetType() == typeof(ComboBox))) { TZExcel.SelectCells(this.tabPage1.Controls[i].Name, Type.Missing); TZExcel.WriteDataToCell(this.tabPage1.Controls[i].Text.ToString()); } } else if (this.tabPage1.Controls[i].Controls.Count > 0) { ExcelExport(this.tabPage1.Controls[i], ref TZExcel); } } for (int i = 0; i < this.tabPage2.Controls.Count; i++) { if (this.tabPage2.Controls[i].Controls.Count == 0) { if ((this.tabPage2.Controls[i].GetType() == typeof(DateTimePicker)) || (this.tabPage2.Controls[i].GetType() == typeof(TextBox)) || (this.tabPage2.Controls[i].GetType() == typeof(ComboBox))) { TZExcel.SelectCells(this.tabPage2.Controls[i].Name, Type.Missing); TZExcel.WriteDataToCell(this.tabPage2.Controls[i].Text.ToString()); } } else if (this.tabPage2.Controls[i].Controls.Count > 0) { ExcelExport(this.tabPage2.Controls[i], ref TZExcel); } } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } finally { TZExcel.Visible = true; TZExcel.Dispose(); } } }