private void SetNewInstance()
{
queryBuilder = new SelectQueryBuilder();
selectionPara = new List <string>();
selectedTbl = new List <string>();
selectedTblRelPara = new List <string>();
joinSelectionPara = new List <string>();
relationPara = new List <string>();
wherePara = new List <string>();
relationTbl = new List <string>();
relationClmn = new List <string>();
baseTblClmn = new List <string>();
filedSelectionPara = new List <string>();
conditionalOpPara = new List <string>();
txtValuesPara = new List <string>();
ConditionPara = new List <Int32>();
betweenPara = new List <string[]>();
fieldSelComp = new List <CodeEngine.Framework.QueryBuilder.Enums.Comparison>();
orderbyPara = new CodeEngine.Framework.QueryBuilder.Enums.Sorting();
inPara = new List <string[]>();
if (ViewState["relationPara"] != null)
{
relationPara = (List <string>)ViewState["relationPara"];
}
if (ViewState["relationTbl"] != null)
{
relationTbl = (List <string>)ViewState["relationTbl"];
}
if (ViewState["relationClmn"] != null)
{
relationClmn = (List <string>)ViewState["relationClmn"];
}
if (ViewState["baseTblClmn"] != null)
{
baseTblClmn = (List <string>)ViewState["baseTblClmn"];
}
if (ViewState["joinSelectionPara"] != null)
{
joinSelectionPara = (List <string>)ViewState["joinSelectionPara"];
}
if (ViewState["selectedTbl"] != null)
{
selectedTbl = (List <string>)ViewState["selectedTbl"];
}
if (ViewState["selectedTblRelPara"] != null)
{
selectedTblRelPara = (List <string>)ViewState["selectedTblRelPara"];
}
if (ViewState["filedSelectionPara"] != null)
{
filedSelectionPara = (List <string>)ViewState["filedSelectionPara"];
}
if (ViewState["conditionalOpPara"] != null)
{
conditionalOpPara = (List <string>)ViewState["conditionalOpPara"];
}
if (ViewState["txtValuesPara"] != null)
{
txtValuesPara = (List <string>)ViewState["txtValuesPara"];
}
if (ViewState["ConditionPara"] != null)
{
ConditionPara = (List <Int32>)ViewState["ConditionPara"];
}
if (ViewState["wherePara"] != null)
{
wherePara = (List <string>)ViewState["wherePara"];
}
}
protected void btngenerate_Click(object sender, EventArgs e)
{
ViewState["count"] = null;
if (cmbTable.SelectedIndex != 0)
{
string tableName = Session["selected_table"].ToString();
for (int i = 0; i < FieldNameList.Items.Count; i++)
{
if (FieldNameList.Items[i].Selected)
{
selectionPara.Add("[" + tableName + "]" + "." + "[" + FieldNameList.Items[i].ToString() + "]");
}
}
CodeEngine.Framework.QueryBuilder.SelectQueryBuilder queryBuilder = new SelectQueryBuilder();
queryBuilder.SelectFromTable((Session["selected_table"]).ToString());
for (int i = 0; i < joinSelectionPara.Count; i++)
{
selectionPara.Add(joinSelectionPara[i]);
}
for (int i = 0; i < relationPara.Count; i++)
{
queryBuilder.AddJoin(CodeEngine.Framework.QueryBuilder.Enums.JoinType.InnerJoin, relationTbl[i], relationClmn[i], CodeEngine.Framework.QueryBuilder.Enums.Comparison.Equals, selectedTblRelPara[i], baseTblClmn[i]);
}
selectionPara.ToArray();
queryBuilder.SelectColumns(selectionPara.ToArray());
int count = 0;
foreach (string conVal in conditionalOpPara)
{
if (conVal == "<")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.LessThan);
}
if (conVal == ">")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.GreaterThan);
}
if (conVal == "<=")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.LessOrEquals);
}
if (conVal == ">=")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.GreaterOrEquals);
}
if (conVal == "=")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.Equals);
}
if (conVal == "<>")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.NotEquals);
}
if (conVal == "LIKE")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.Like);
}
if (conVal == "IN")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.In);
inPara.Add(txtValuesPara[count].Split(','));
}
if (conVal == "BETWEEN")
{
fieldSelComp.Add(CodeEngine.Framework.QueryBuilder.Enums.Comparison.Between);
betweenPara.Add(txtValuesPara[count].Split(','));
}
count++;
}
count = 0;
for (int i = 0; i < fieldSelComp.Count; i++)
{
if (fieldSelComp.Count > 0)
{
if (fieldSelComp[i] == CodeEngine.Framework.QueryBuilder.Enums.Comparison.Between)
{
queryBuilder.AddWhere(filedSelectionPara[i], fieldSelComp[i], betweenPara[count], ConditionPara[i]);
count++;
}
else if (fieldSelComp[i] == CodeEngine.Framework.QueryBuilder.Enums.Comparison.Like)
{
queryBuilder.AddWhere(filedSelectionPara[i], fieldSelComp[i], "%" + txtValuesPara[i] + "%", ConditionPara[i]);
count++;
}
else if (fieldSelComp[i] == CodeEngine.Framework.QueryBuilder.Enums.Comparison.In)
{
queryBuilder.AddWhere(filedSelectionPara[i], CodeEngine.Framework.QueryBuilder.Enums.Comparison.In, inPara[count], ConditionPara[i]);
}
else
{
queryBuilder.AddWhere(filedSelectionPara[i], fieldSelComp[i], txtValuesPara[i], ConditionPara[i]);
}
}
}
if (orderbyMethod.Text == "Ascending")
{
orderbyPara = CodeEngine.Framework.QueryBuilder.Enums.Sorting.Ascending;
}
else
{
orderbyPara = CodeEngine.Framework.QueryBuilder.Enums.Sorting.Descending;
}
for (int i = 0; i < OrderbyList.Items.Count; i++)
{
if (OrderbyList.Items[i].Selected)
{
queryBuilder.AddOrderBy(OrderbyList.Items[i].ToString(), orderbyPara);
}
}
queryBuilder.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
/// Build Final query result
SQLQueryText.Text = queryBuilder.BuildQuery();
SqlConnection sqlConn;
try
{
SqlCommand command = new SqlCommand(SQLQueryText.Text);
sqlConn = sqlConn = new SqlConnection((Session["Connection_String"]).ToString());
sqlConn.Open();
command.Connection = sqlConn;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
sqlConn.Close();
// SetNewInstance();
}
catch (SqlException ex)
{
this.Session["exceptionMessage"] = ex.Message;
// Response.Redirect("ErrorDisplay.aspx");
lblError.Text = ex.Message;
// log.Write(ex.Message + ex.StackTrace);
}
sqlConn = null;
// SetNewInstance();
}
}
