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();
}