public FormCriteriaAnd(FormColumnSelection frm, QColumn col) : this() { this.frm = frm; this.column = col; onLoadForm(); }
public int CompareTo(object obj) { QColumn columnToCompare = obj as QColumn; if (columnToCompare.Index < this.Index) { return(1); } if (columnToCompare.Index > this.Index) { return(-1); } return(0); }
public CustomChildNode(QColumn column) : base(column.Name) { this.column = column; }
//============= Get list of available columns of a table returned by a user defined function ===================// public List<QColumn> GetColumnsFromTableFunction(string table) { List<QColumn> columns = new List<QColumn>(); try { SqlCommand cmdSelect = new SqlCommand(); using (cmdSelect) { cmdSelect.CommandText = "SELECT name, xtype from sys.syscolumns " + "where id in (select id from sysobjects where " + "name = '" + table + "')" + "and number != 1"; cmdSelect.CommandType = CommandType.Text; cmdSelect.Connection = sqlConnection; cmdSelect.CommandTimeout = 180; } daColumnFunction.SelectCommand = cmdSelect; DataSet dsColumn = new DataSet(); daColumnFunction.Fill(dsColumn, "Columns"); DataTableReader dtReader = dsColumn.CreateDataReader(); while (dtReader.Read()) { string colname = dtReader.GetValue(0).ToString(); string xtype = dtReader.GetValue(1).ToString(); string datatype = ""; if (xtype == "48" || xtype == "52" || xtype == "56" || xtype == "59" || xtype == "60" || xtype == "62" || xtype == "104" || xtype == "106" || xtype == "108" || xtype == "122" || xtype == "127" || xtype == "173" || xtype == "56" ) { datatype = "decimal"; } else { datatype = "varchar"; } QColumn column = new QColumn(colname, datatype); columns.Add(column); } } catch (SqlException se) { Debug.WriteLine(se.ToString()); } catch (Exception e) { Debug.WriteLine(e.ToString()); } //UNCOMMENT THE CODE BELOW FOR USING CONNECTED LAYER //using (this.SqlConnection) //{ // Connect(); // using (SqlCommand cmd = new SqlCommand("SELECT name, xtype from sys.syscolumns " + // "where id in (select id from sysobjects where " + // "name = '" + table + "')" + // "and number != 1", this.SqlConnection)) // { // using (IDataReader dr = cmd.ExecuteReader()) // { // while (dr.Read()) // { // string colname = dr.GetValue(0).ToString(); // string xtype = dr.GetValue(1).ToString(); // string datatype = ""; // if (xtype == "48" // || xtype == "52" // || xtype == "56" // || xtype == "59" // || xtype == "60" // || xtype == "62" // || xtype == "104" // || xtype == "106" // || xtype == "108" // || xtype == "122" // || xtype == "127" // || xtype == "173" // || xtype == "56" // ) // { // datatype = "decimal"; // } // else // { // datatype = "varchar"; // } // QColumn column = new QColumn(colname, datatype); // columns.Add(column); // } // } // } //} //this.Close(); return columns; }
//because when we set an aggregate, the code automatically detect a column to have a having clause, so we need to perform check // if an aggregate is non group by, we put the having clause back to where clause and vice versa private void performTransferHavingToWhereClause(QColumn colBack) { if (colBack.Aggregate != null) { if (colBack.Aggregate.Name.Equals("LEN")) //if a column has an aggregate that does not need group by, put criteria into where clause { selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).Criterias = colBack.HavingCriterias; selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).HavingCriterias = null; generateSQLStatement(); return; } //a column that has an aggregate which needs a group by, put the criteria into having clause selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).HavingCriterias = colBack.HavingCriterias; generateSQLStatement(); return; } else //a column that does not have an aggregate, simply put into where clause { selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).Criterias = colBack.Criterias; generateSQLStatement(); return; } }
//======== Handle checkbox event in datagrid ========================================/ private void dgvSelectedColumns_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (e.ColumnIndex == dgvSelectedColumns.Columns["Output"].Index) //when re-check checkbox { if ((bool)dgvSelectedColumns.Rows[e.RowIndex].Cells["Output"].Value) { //recheck checkbox string column = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Column"].Value; string table = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Table"].Value; string datatype = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["DataType"].Value; string indexString = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Index"].Value; int index = int.Parse(indexString); string selectedField = table + "." + "[" + column + "]"; QColumn col = new QColumn(selectedField, datatype); col.Index = index; selectedColumns.Add(col); selectedColumns.Sort(); bindGroupColumns(); generateSQLStatement(); } else //when uncheck the checkbox in datagridview { dgvSelectedColumns.Rows[e.RowIndex].Cells["Alias"].Value = ""; //when uncheck the box, make sure to clear the Alias cell dgvSelectedColumns.Rows[e.RowIndex].Cells["Aggregate"].Value = Aggregates.NONE; string column = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Column"].Value; string table = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Table"].Value; string datatype = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["DataType"].Value; string selectedField = table + "." + "[" + column + "]"; foreach (var item in selectedColumns.Where(c => c.Name.Equals(selectedField))) { item.Aggregate = null; } selectedColumns.RemoveAll(c => c.Name.Equals(selectedField)); foreach (var item in selectedColumns.Where(c => c.Name.Equals(selectedField))) { item.Aggregate = null; aggregatedColumns.Remove(item); } bindGroupColumns(); generateSQLStatement(); } } if (e.ColumnIndex == dgvSelectedColumns.Columns["CriteriaAnd"].Index) //when re-check checkbox { string column = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Column"].Value; string table = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Table"].Value; string datatype = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["DataType"].Value; string indexString = (string)dgvSelectedColumns.Rows[e.RowIndex].Cells["Index"].Value; int index = int.Parse(indexString); string selectedField = table + "." + "[" + column + "]"; var col = selectedColumns.Where(c => string.Equals(c.Name, selectedField)).First(); FormCriteriaAnd frmCriteriaAnd = new FormCriteriaAnd(this, col); frmCriteriaAnd.ShowDialog(); } }
public void OnPostBack(QColumn colBack) { List<QCriteria> criterias = colBack.Criterias; if (selectedColumns.Contains(colBack)) { if (colBack.Aggregate != null) { if (colBack.Aggregate.Name.Equals("LEN")) //if a column has an aggregate that does not need group by, put criteria into where clause { selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).Criterias = colBack.Criterias; selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).HavingCriterias = null; generateSQLStatement(); return; } //a column that has an aggregate which needs a group by, put the criteria into having clause selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).HavingCriterias = colBack.Criterias; selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).Criterias = null; generateSQLStatement(); return; } else //a column that does not have an aggregate, simply put into where clause { selectedColumns.ElementAt(selectedColumns.IndexOf(colBack)).Criterias = colBack.Criterias; generateSQLStatement(); return; } } }
//=============Handling item selecting on TreeView=================================// private void tvAvailableColumns_AfterSelect(object sender, TreeViewEventArgs e) { if (e.Node.ForeColor == Color.Purple) //if a node is purple, that means a user select already, simply returns { return; } if (e.Node.Parent != null && e.Node.Parent.GetType() == typeof(CustomParentNode)) //if select child node which is column of a table { CustomParentNode cpn = (CustomParentNode)e.Node.Parent; CustomChildNode ccn = (CustomChildNode)e.Node; table = cpn.Table; view = cpn.View; tableFunction = cpn.TableFunction; if (view == null && tableFunction == null) tableName = table.Name; else if (table == null && tableFunction == null) tableName = view.Name; else if (table != null && view == null) tableName = tableFunction.Name; index++; columnName = ccn.Column.Name; alias = ccn.Column.Alias; dataType = ccn.Column.DataType; populateDataGridView(dgvSelectedColumns); e.Node.Parent.ForeColor = Color.Purple; e.Node.ForeColor = Color.Purple; //-------------------------------------Tricky part here:------------------------------------------------------// //check dictionary for values of a given key. If the key has no value, add new value associated with key //If the dictionary already has the key, add more values for that key List<QColumn> existing; if (!sqlFields.TryGetValue(tableName, out existing)) { existing = new List<QColumn>(); sqlFields[tableName] = existing; } QColumn c = new QColumn(tableName + "." + "[" + columnName + "]", dataType); c.Index = index; existing.Add(c); existing.Sort(); //-------------------------------------End Tricky part here:------------------------------------------------------// //after we populate our dictionary, pull out keys and list of value populateSelectedTableList(); if (selectedColumns.Count == 0) { populateSelectedColumnList(); } else { QColumn col = new QColumn(tableName + "." + "[" + columnName + "]", dataType); col.Index = index; selectedColumns.Add(col); selectedColumns.Sort(); } } else { tableName = e.Node.Text; columnName = "*"; } bindGroupColumns(); generateSQLStatement(); }