In this article i will explain how you can create SQL Queries to execute against any database to fetch records according to your requirements. As a developer we need in almost every project to display records, filter records, some times it requires complex processing. So in this article i will start explaining how you can create, execute SQL Queries when there are more that four or five parameters involve. e.g , i want to create a member searching form, and i want to provide features some features according to that user can find records . e.g i provide, country, gender, age,order by clause . so how we can create query in order to cope this scenario. so below is the complete example.
Public Function Execute_Query(ByVal CountryID As String, ByVal Gender As String, ByVal Age As String, ByVal orderbyindex As Integer) As DataSet
Dim Query As String = " SELECT * FROM Members"
If Not CountryID = "all" Or Not Gender = "all" Or Not Age = "all" Then
'// It means some option is selected by user.
Query = Query & " WHERE"
End If
'// Check for Country id whether it is selected
If Not CountryID = "all" Then
'// It means country id is selected
Query = Query & " CountryID =" & CountryID & " "
'// Check for gender now.
If Not Gender = "all" Then
Query = Query & " AND"
End If
End If
If Not Gender = "all" Then
Query = Query & " Gender ='" & Gender & "' "
'// Check for Age
If Not Age = "all" Then
Query = Query & " AND"
End If
End If
If Not Age = "all" Then
'// Check whether age is below 18 years.
If Age < 18 Then
Query = Query & " DateDiff(year,dateofbirth,GetDate())<18 "
ElseIf Age >= 18 And Age < 53 Then
'// i set ranges of age each range containg five years e.g 18 - 22, 23 - 27
Dim end_range As Integer = Age + 4
Query = Query & " DateDiff(year,dateofbirth,GetDate())>=" & Age & " AND DateDiff(year, dateofbirth, GetDate())<=" & end_range & " "
ElseIf Age >= 53 Then
Query = Query & " DateDiff(year,dateofbirth,GetDate()>=" & Age & " "
End If
End If
'// Perform order by clause
Dim orderby As String = ""
Select Case orderbyindex
Case 0
orderbyindex = " UserName"
Case 1
orderbyindex = " UserName DESC"
Case 2
orderbyindex = " dateofbirth"
Case 3
orderbyindex = " dateofbirth DESC"
End Select
Query = Query & " ORDER BY " & orderindex & " "
'// Execute Query and return dataset.
Return Sql.ExecuteDateset(connectionstring, commandtext.text, Query)
End Function
You can use this code and generate queries according to that with your own choice for your web application. Below is sample code.
<asp:dropdownlist id="drp_country" runat="server">
<!-- code implementation -->
</asp:dropdownlist>
<asp:dropdownlist id = "drp_gender" runat="server">
<!-- code implementation -->
</asp:dropdownlist>
<asp:dropdownlist id="drp_age" runat="server">
<!-code implementation -->
</asp:dropdownlist>
<asp:dropdownlist id = "drp_order" runat="server">
<!-- code implementation -->
</asp:dropdownlist>
Code behind:
Public Fuction Load_Data()
Member_List.DataSource = Execute_Query(drp_country.selectedvalue, byval drp_gender.selectedvalue, byval drp_age.selectedvalue, byval drp_country.selectedvalue)
Member_List.Databind()
End Sub