private void rotaBitButton1_Click(object sender, EventArgs e) { FuncGeneral.KillProgram("EXCEL"); ExcelHelper xls = new ExcelHelper(); xls.Workbook_Hinzufuegen(); this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); int i = 0; bool add_select = true; foreach (string ert in this.m_Tablelist) { string table = m_Tablelist[i]; string[] cols = m_ColumnlistOutput[i]; foreach (string col in cols) { if (add_select == true) { add_select = false; this.m_SqlHelper.SQLBuilderHinzufuegen("SELECT TOP 100", SQLHelper.SQLBuilder.DeleteSQL); } string suchen = table + "." + col + ","; this.m_SqlHelper.SQLBuilderHinzufuegen(suchen, SQLHelper.SQLBuilder.NoSpace); } i++; } string sql = ""; string sql_where = ""; string sql_inner_join = ""; sql = this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql = FuncGeneral.DeleteRight(sql, 1); this.m_SqlHelper.SQLBuilderHinzufuegen(sql, SQLHelper.SQLBuilder.DeleteSQL); sql = this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql_inner_join = this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql_where = this.m_SqlHelperWhere.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); this.textBox2.Text = sql + sql_inner_join + sql_where; xls.SQL2Excel(this.textBox2.Text, m_ConnectionString); string file_name = "Messwerte" + FuncString.GetOnlyNumeric(FuncString.GetTimestamp()); xls.DateiSpeichern(file_name); xls.ExcelHelperClose(); xls = null; }
private void btnStartQuery_Click(object sender, EventArgs e) { string cbx_name = ""; string txt_name = ""; string conjunction = ""; bool add_where = true; bool add_select = true; this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.DeleteSQL); this.m_SqlHelperWhere.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.DeleteSQL); for (int i = 1; i <= 6; i++) { cbx_name = "CbxTabelle" + FuncString.FillForward(i.ToString(), "0", 2); ComboBox obj_cbx_tabelle = (ComboBox)FuncGeneral.GetControlByName(this, cbx_name); cbx_name = "CbxSpalte" + FuncString.FillForward(i.ToString(), "0", 2); ComboBox obj_cbx_column = (ComboBox)FuncGeneral.GetControlByName(this, cbx_name); txt_name = "txtFilter" + FuncString.FillForward(i.ToString(), "0", 2); TextBox obj_txt_filter = (TextBox)FuncGeneral.GetControlByName(this, txt_name); if (obj_cbx_tabelle.SelectedItem != null) { string table = obj_cbx_tabelle.SelectedItem.ToString(); if (obj_cbx_column.SelectedItem != null) { if (add_select == true) { add_select = false; this.m_SqlHelper.SQLBuilderHinzufuegen("SELECT TOP 100", SQLHelper.SQLBuilder.DeleteSQL); } string column = obj_cbx_column.SelectedItem.ToString(); string suchen = table + column; TableStruct table_struct = (TableStruct)this.m_Datas[suchen]; string display = table_struct.Display; this.m_SqlHelper.SQLBuilderHinzufuegen(table, SQLHelper.SQLBuilder.NoSpace); this.m_SqlHelper.SQLBuilderHinzufuegen(".", SQLHelper.SQLBuilder.NoSpace); this.m_SqlHelper.SQLBuilderHinzufuegen(column, SQLHelper.SQLBuilder.NoSpace); if (display.Length > 0) { this.m_SqlHelper.SQLBuilderHinzufuegen(" AS"); this.m_SqlHelper.SQLBuilderHinzufuegen(display, SQLHelper.SQLBuilder.NoSpace); this.m_SqlHelper.SQLBuilderHinzufuegen(",", SQLHelper.SQLBuilder.NoSpace); } else { this.m_SqlHelper.SQLBuilderHinzufuegen(",", SQLHelper.SQLBuilder.NoSpace); } if (obj_txt_filter.Text.Length > 0) { if (add_where == true) { add_where = false; this.m_SqlHelperWhere.SQLBuilderHinzufuegen("WHERE", SQLHelper.SQLBuilder.DeleteSQL); } if (conjunction.Length > 0) { this.m_SqlHelperWhere.SQLBuilderHinzufuegen(conjunction); } if ((table_struct.Columntype == "char") || (table_struct.Columntype == "nchar")) { this.m_SqlHelperWhere.SQLBuilderHinzufuegen(table_struct.Table + "." + table_struct.Column); this.m_SqlHelperWhere.SQLBuilderHinzufuegen("LIKE"); this.m_SqlHelperWhere.SQLBuilderHinzufuegen("N'%", SQLHelper.SQLBuilder.NoSpace); this.m_SqlHelperWhere.SQLBuilderHinzufuegen(obj_txt_filter.Text, SQLHelper.SQLBuilder.NoSpace); this.m_SqlHelperWhere.SQLBuilderHinzufuegen("%'", SQLHelper.SQLBuilder.NoSpace); } else { this.m_SqlHelperWhere.SQLBuilderHinzufuegen(table_struct.Table + "." + table_struct.Column); this.m_SqlHelperWhere.SQLBuilderHinzufuegen("="); this.m_SqlHelperWhere.SQLBuilderHinzufuegen(obj_txt_filter.Text, SQLHelper.SQLBuilder.NoSpace); } if (i >= 1 || i < 3) { conjunction = " AND"; } if (i == 4) { conjunction = " " + this.cbxAndOr01.Text; } if (i == 5) { conjunction = " " + this.cbxAndOr02.Text; } } } } } string sql = ""; string sql_where = ""; string sql_inner_join = ""; sql = this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql = FuncGeneral.DeleteRight(sql, 1); this.m_SqlHelper.SQLBuilderHinzufuegen(sql, SQLHelper.SQLBuilder.DeleteSQL); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("FROM", SQLHelper.SQLBuilder.DeleteSQL); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("MW_KENNDATEN_EINGABE"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("INNER JOIN"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("MW_ERGEBNIS"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("ON"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("MW_KENNDATEN_EINGABE.LINK = MW_ERGEBNIS.Link"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("INNER JOIN"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("MW_WERTE_MESSUNG"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("ON"); this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("MW_KENNDATEN_EINGABE.LINK = MW_WERTE_MESSUNG.Link"); sql = this.m_SqlHelper.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql_inner_join = this.m_SqlHelperInnerJoin.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); sql_where = this.m_SqlHelperWhere.SQLBuilderHinzufuegen("", SQLHelper.SQLBuilder.NoSpace); this.textBox1.Text = sql + sql_inner_join + sql_where; this.BindData(this.textBox1.Text, this.m_BS_Auswahl); }
private void CreateTableColumnList() { this.m_TableInfo.Clear(); string table_name = ""; this.m_Columnlist.Clear(); this.m_ColumnlistOutput.Clear(); List <string> columns = new List <string>(); List <string> all_columns = new List <string>(); this.m_Tablelist.Clear(); bool first_start = true; string table = ""; string left_txt = ""; foreach (string txt in this.richTextBox1.Lines) { if (txt.Length > 0) { table = txt; left_txt = txt.Substring(0, 1); if (left_txt == "[") { table = FuncGeneral.DeleteLeft(txt, 1); table = FuncGeneral.DeleteRight(table, 1); this.m_Tablelist.Add(table); if (first_start == true) { first_start = false; } else { this.m_Columnlist.Add(columns.ToArray()); this.m_ColumnlistOutput.Add(all_columns.ToArray()); } columns.Clear(); all_columns.Clear(); table_name = table; this.FillTableInfo(table_name); } else { TableStruct table_struc; table_struc.Table = table_name; string display = ""; left_txt = txt.Substring(0, 1); bool add_to_select = false; if (left_txt != "#") { display = txt; add_to_select = true; } else { display = FuncGeneral.DeleteLeft(txt, 1); } string[] daten = display.Split(';'); if (daten.Count() > 1) { table_struc.Column = daten[0]; table_struc.Display = daten[1]; } else { table_struc.Column = display; table_struc.Display = ""; } if (add_to_select) { columns.Add(table_struc.Column); } string key = table_struc.Table + table_struc.Column; all_columns.Add(table_struc.Column); table_struc.Columntype = (string)this.m_TableInfo[key]; this.m_Datas.Add(key, table_struc); } } } this.m_Columnlist.Add(columns.ToArray()); this.m_ColumnlistOutput.Add(all_columns.ToArray()); columns = null; }