Introduction
This tip helps you build a user responsive web based query builder in ASP.NET and also gives you insight into some basic features of ASP.NET and how to use web based user controls, styles from CSS, etc. Later on, we can export data to PDF or Excel format. Works perfectly fine with Microsoft Edge as well.
Background
After three weeks of browsing the web for a query builder which can support web platform and multiple ups and downs, I have finally managed to develop a standalone web application which can interact with the end user.
Using the Code
Let's jump to the main code part, where we will be fetching data from database after making a connection and then displaying them in order to apply query.
Here, I have tried to make this application User Responsive. User will have to enter machine name and the application will try to read available SQL Server database and later on fetch related tables or views, on which User wants to apply query conditions.
DataBaseName = new List<string>();
machine_Name = txtMachineName.Text;
Session["Machine_Name"] = machine_Name;
string ConString = "Data Source=" + machine_Name +
";Integrated Security=True;MultipleActiveResultSets=True;";
using (var con1 = new SqlConnection(ConString))
{
con1.Open();
DataTable databases = con1.GetSchema("Databases");
foreach (DataRow database in databases.Rows)
{
String databaseName = database.Field<String>("database_name");
DataBaseName.Add(databaseName.ToString());
short dbID = database.Field<short>("dbid");
DateTime creationDate = database.Field<DateTime>("create_date");
}
Similarly, we can also fetch Table/Views present in the database selected.
Session["selected_table"] = selectTable;
string sqlQuery = "Select * from " + selectTable;
SqlCommand command = new SqlCommand(sqlQuery);
SqlConnection sqlConn = new SqlConnection((Session["Connection_String"]).ToString());
sqlConn.Open();
command.Connection = sqlConn;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dt = new DataTable();
adapter.Fill(dt);
After we have selected details about Database and Tables to be selected, we can further move on to extract attributes of Table/View selected.
I have used a web based User Control for applying where condition query on extracted column names (attributes).
Please look into the source code for details about how to create a web based User Control and register it on the main page.
Advantage: You don't need to explicitly refine a collection of controls in repeated fashion.
Coming back to our application, once user is done with applying conditions on Table/View attributes, we proceed to the query generation part. Depending on the conditions, I have kept on appending my query builder (CodeEngine.Framework.QueryBuilder.SelectQueryBuilder) string and finally executing this to fetch result.
SQLQueryText.Text = queryBuilder.BuildQuery(); // queryBuilder is a
// local variable of CodeEngine.Framework.QueryBuilder.SelectQueryBuilder
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);
}
Further on, we can process our results into Excel/Pdf format. It is a very simple procedure.
Export to Excel:
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Export_Result.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages from ResultGrid i.e. data grid used for displaying results
ResultGrid.AllowPaging = false;
ResultGrid.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
Points of Interest
Well, I am new to ASP.NET, having previously worked on WPF, initially, it was difficult for me to design UI and make use of user controls and CSS styles in my application in ASP.NET, but once I was done with it, I felt pretty confident and intrigued.
