Click here to Skip to main content
15,178,226 members
Articles / Web Development / HTML
Tip/Trick
Posted 29 Nov 2015

Stats

21.2K views
10.3K downloads
17 bookmarked

User Responsive Web Query Builder using ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.60/5 (6 votes)
29 Nov 2015CPOL2 min read
Web platform based query builder to play with your database :)

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.

Image 1

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.

C#
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)
    {
        // fetching related details for each database
        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.

ASP.NET
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.

ASP.NET
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:

ASP.NET
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Reeshabh Choudhary
Systems Engineer Infosys Ltd.
India India
Sports Addict who codes for Brain exercise. Smile | :)

Comments and Discussions

 
Questionhow to perform joins on multiple tables. Pin
Member 1263926122-Sep-20 4:37
MemberMember 1263926122-Sep-20 4:37 
AnswerRe: how to perform joins on multiple tables. Pin
Member 138230242-Jun-21 22:50
MemberMember 138230242-Jun-21 22:50 
QuestionDownload file Corrupted Pin
H & N IT Solutions5-Jul-20 4:06
MemberH & N IT Solutions5-Jul-20 4:06 
PraiseGroup by multiple columns Pin
demouser7432-Nov-19 8:14
Memberdemouser7432-Nov-19 8:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.