Code on .vb file
Imports System.Collections.Generic
Imports System.Web.Services
Imports ENTech.WebControls
Imports System.Data.SqlClient
Imports System
Imports System.Data
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Partial Class _Default
Inherits System.Web.UI.Page
Private Shared Function GetCityMenuItemsFromDataReader(ByVal reader As SqlDataReader, _
ByVal usePaging As Boolean, _
ByVal pageIndex As Integer, _
ByVal pageSize As Integer, _
ByVal appendCountryName As Boolean) As Generic.List(Of AutoSuggestMenuItem)
Dim menuItems As New Generic.List(Of AutoSuggestMenuItem)
Dim city As String
Dim cityCode As String
Dim state As String
Dim country As String
Dim label As String
Dim menuItem As AutoSuggestMenuItem
Dim rowIndex As Integer = 0
'Handle paging
Dim startRowIndex As Integer = 0
Dim endRowIndex As Integer = 0
If usePaging Then
startRowIndex = pageIndex * pageSize
endRowIndex = startRowIndex + pageSize
End If
While reader.Read()
If usePaging Then
'Get to the start of the page
If rowIndex < startRowIndex Then
rowIndex = rowIndex + 1
Continue While
'Break out of the loop if end of the page
If rowIndex >= endRowIndex Then
Exit While
End If
End If
End If
'Build label using City, Country & State
city = reader.GetString(0)
cityCode = reader.GetString(1)
country = reader.GetString(2)
If IsDBNull(reader.GetValue(3)) Then
state = ""
Else
state = reader.GetString(3)
End If
label = city
'Append either city or country
If (state <> "") Then
label &= " "
Else
If appendCountryName Then
label &= " "
End If
End If
menuItem = New AutoSuggestMenuItem()
menuItem.Label = label
menuItem.Value = cityCode
menuItems.Add(menuItem)
rowIndex = rowIndex + 1
End While
GetCityMenuItemsFromDataReader = menuItems
End Function
Private Shared Function GetConnectionString() As String
Dim connString As String = "connection string"
GetConnectionString = connString
End Function
<WebMethod()> _
Public Shared Function GetCitySuggestions(ByVal keyword As String, _
ByVal countryCode As String, _
ByVal usePaging As Boolean, _
ByVal pageIndex As Integer, _
ByVal pageSize As Integer) As String
Dim menuItems As Generic.List(Of AutoSuggestMenuItem)
Dim connString As String = GetConnectionString()
Dim cn As SqlConnection = New SqlConnection(connString)
'May need to repeat the following 3 times
'Instead just save it to variable
Dim sqlFromAndWhere As String = " FROM (product INNER JOIN brand ON product.productcate=brand.productcate) " & _
" LEFT OUTER JOIN vendor ON product.id=vendor.id " & _
" WHERE (product.productcate LIKE '" & keyword.Replace("'", "''") & "%')"
'Append country code to WHERE clause if specified
If Not String.IsNullOrEmpty(countryCode) Then
sqlFromAndWhere &= " brand.productcate='" & countryCode & "'"
End If
Dim sql As String
If usePaging Then
'Select only menu items up to specified page
'In Sql Server use ROW_NUMBER to only get the values for current page
Dim numItems As Integer = (pageIndex + 1) * pageSize
sql = "SELECT product.productcate as CityName, " & _
"product.productunit as CityCode, " & _
"brand.productcate as CountryName, " & _
"vendor.vendorname as StateName " & _
sqlFromAndWhere & _
" ORDER BY product.productcate"
Else
sql = "SELECT product.productcate as CityName, " & _
"product.productunit as CityCode, " & _
"brand.productcate as CountryName, " & _
"vendor.vendorname as StateName " & _
sqlFromAndWhere & _
" ORDER BY product.productcate"
End If
Dim cmd As SqlCommand = New SqlCommand(sql, cn)
cn.Open()
'I use datareader because it is usually much faster then dataSet
'But cached DataSet may also work
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim appendCountryName As Boolean = String.IsNullOrEmpty(countryCode)
menuItems = GetCityMenuItemsFromDataReader(reader, usePaging, pageIndex, pageSize, appendCountryName)
reader.Close()
'When using paging need to get totalResults
Dim totalResults As Integer = -1
If usePaging And pageIndex = 0 Then
'Only do it when page index is 0
sql = "SELECT COUNT(*)" & sqlFromAndWhere
cmd = New SqlCommand(sql, cn)
totalResults = CType(cmd.ExecuteScalar(), Integer)
End If
cn.Close()
GetCitySuggestions = AutoSuggestMenu.ConvertMenuItemsToJSON(menuItems, totalResults)
End Function
<WebMethod()> _
Public Shared Function GetCountrySuggestions(ByVal keyword As String, ByVal usePaging As Boolean, ByVal pageIndex As Integer, ByVal pageSize As Integer) As String
Dim menuItems As New Generic.List(Of AutoSuggestMenuItem)
Dim connString As String = GetConnectionString()
Dim cn As SqlConnection = New SqlConnection(connString)
Dim sql As String = "SELECT productunit, productcate FROM product " & _
" WHERE productunit LIKE '" & keyword.Replace("'", "''") & "%'" & _
" ORDER BY productunit"
Dim cmd As New SqlCommand(sql, cn)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim menuItem As AutoSuggestMenuItem
While reader.Read()
menuItem = New AutoSuggestMenuItem()
menuItem.Label = reader.GetString(0)
menuItem.Value = reader.GetString(1)
menuItems.Add(menuItem)
End While
reader.Close()
cn.Close()
GetCountrySuggestions = AutoSuggestMenu.ConvertMenuItemsToJSON(menuItems, -1) 'Total results (-1) is ignored if paging is not used }
End Function
<WebMethod()> _
Public Shared Function GetCountryInfo(ByVal cityCode As String) As String
Dim writer As New XJsonWriter()
Dim connString As String = GetConnectionString()
Dim cn As New SqlConnection(connString)
Dim sql As String = "SELECT product.productcate, product.productunit, brand.productcate as CityName " & _
" FROM product INNER JOIN brand ON brand.id=product.id " & _
" WHERE product.productunit='" & cityCode & "'"
Dim cmd As New SqlCommand(sql, cn)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
If reader.Read() Then
writer.WriteNameValue("countryCode", reader.GetString(0))
writer.WriteNameValue("countryName", reader.GetString(1))
writer.WriteNameValue("cityName", reader.GetString(2))
Else
Throw New ArgumentException("City code '" & cityCode & "' is not valid", "cityCode")
End If
reader.Close()
cn.Close()
Return writer.ToString()
End Function
End Class
Submit an article or tip