Binding AutoCompleteExtender to a Cities text-box in ASP.NET

In this article, we discuss how the Ajax AutoCompleteExtender can be used to display a list of matching cities given a country and the first few letters of the city.

The first step is to add a reference to the AjaxControlToolkit:

<%@ Register Assembly=”AjaxControlToolkit” Namespace=”AjaxControlToolkit” TagPrefix=”Ajax” %>

Create an instance of ScriptManager inside the form:

<form id=”form1″ runat=”server”>

<asp:ScriptManager ID=”ScriptManager1″ runat=”server” />

</form>

Next, create an instance of the countries drop down list (inside the form):

<asp:DropDownList ID=”ddlCountry” runat=”server” Width=”170px”      AutoPostBack=”true”
CssClass=”mediumblack” DataSourceID=”SqlDataSource3″ DataTextField=”COUNTRY_NAME” DataValueField=”CC” OnSelectedIndexChanged=”ddlCountry_SelectedIndexChanged”>
<asp:ListItem Text=”Select Country” />
</asp:DropDownList>

Note that the DataTextField has the country’s name, while the DataValueField (the selected value of the drop-down) has the country code (CC).

Define the data source for the countries drop-down:

<asp:SqlDataSource ID=”SqlDataSource3″ runat=”server” ConnectionString=”<%$ ConnectionStrings:WhatIfGamesConnectionString2 %>”
ProviderName=”<%$ ConnectionStrings:WhatIfGamesConnectionString2.ProviderName %>”
SelectCommand=”SELECT CC, COUNTRY_NAME FROM COUNTRIES ORDER BY COUNTRY_NAME”></asp:SqlDataSource>

Define the data source for the cities:

<asp:SqlDataSource ID=”SqlDataSource2″ runat=”server” ConnectionString=”<%$ ConnectionStrings:WhatIfGamesConnectionString2 %>”
ProviderName=”<%$ ConnectionStrings:WhatIfGamesConnectionString2.ProviderName %>”
SelectCommand=”SELECT CITIES.NAME FROM CITIES, COUNTRIES WHERE COUNTRIES.CC = CITIES.CC AND COUNTRIES.CC = @CC ORDER BY CITIES.NAME”>
         <SelectParameters>
                <asp:ControlParameter ControlID=”ddlCountry” Name=”CC”                                                        PropertyName=”SelectedValue” />
         </SelectParameters>
</asp:SqlDataSource>

Next, define the text-box that will display the cities using auto-complete:

<asp:TextBox ID=”txtCity” runat=”server” CssClass=”mediumblack” AutoPostBack=”true”
ontextchanged=”txtCity_TextChanged”></asp:TextBox>
<Ajax:AutoCompleteExtender
ServicePath=”Cities.asmx”
ServiceMethod=”GetCities”
MinimumPrefixLength=”1″
CompletionInterval=”100″
EnableCaching=”false”
EnableViewState=”true”
CompletionSetCount=”1″
TargetControlID=”txtCity”
ID=”AutoCompleteExtender1″
runat=”server”
FirstRowSelected=”false”
UseContextKey=”true”
CompletionListElementID=”Panel1″
CompletionListItemCssClass=”listitem”
CompletionListHighlightedItemCssClass=”hoverlistitem”
>
</Ajax:AutoCompleteExtender>
<asp:Panel ID=”Panel1″ runat=”server” style=”font-family:Calibri; font-size:medium; color:White;”>
</asp:Panel>

The panel, Panel1 is defined to display the auto-complete items in a particular font.

The styles for the auto-complete items are defined as follows (in a .css file):

.listitem {

     background: SteelBlue;

     color: White;

}

.hoverlistitem {

     background: Blue;

     color: White;

}

The AutoCompleteExtender’s TargetControlID is set to “txtCity”. The service path and service method refer to the web-service that will return the list of cities:

ServicePath=”cities.asmx”

ServiceMethod=”GetCities”

In the ASP.NET code-behind, define the following methods:

In the Page_Load event-handler, insert the following:

if (!IsPostBack)
{
      ddlCountry.DataBind();
}

Define the selected index changed event-handler for ddlCountry:

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
try
    {
         SqlDataSource2.DataBind();
         AutoCompleteExtender1.ContextKey = ddlCountry.SelectedValue;
     }
catch (Exception ex)
    {
         ErrorLabel.Text = “ddlCountry_SelectedIndexChanged(): ” + ex.Message;
     }
}

The country code (ddlCountry.SelectedValue) is assigned to the ContextKey parameter of the AutoCompleteExtender.

The following event-handler is optional and used only when a GridView will be populated with matching cities:

protected void txtCity_TextChanged(object sender, EventArgs e)
{
    try
    {
     SqlDataSource2.SelectCommand = @”SELECT CITIES.NAME FROM CITIES, COUNTRIES
     WHERE COUNTRIES.CC = CITIES.CC
     AND COUNTRIES.CC = @CC
     AND CITIES.NAME LIKE ‘” + txtCity.Text + “%'” + // Add a % before txtCity.txt to match    the pattern anywhere in the city name
     @”ORDER BY CITIES.NAME”;
     SqlDataSource2.DataBind(); 

     GridView2.DataBind();
    }
    catch (Exception ex)
    {
      ErrorLabel.Text = “txtCity_TextChanged(): ” + ex.Message;
    }
}

Now define the web-service that will be consumed by AutoCompleteExtender1:

using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Windows.Forms;
using System.Web.Configuration;

/// <summary>
/// Summary description for Cities
/// </summary>
[WebService(Namespace = “http://processwaresystems.com&#8221;)]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class Cities : System.Web.Services.WebService
{
string m_connectionString = ConfigurationManager.ConnectionStrings[“WhatIfGamesConnectionString2”].ConnectionString;

public Cities()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public string HelloWorld()
{
return “Hello World”;
}

[WebMethod]
public string[] HelloWorld2()
{
return new string[] {“Hello World”, “Hello India”};
}

[System.Web.Script.Services.ScriptMethod]
[WebMethod]
public string[] GetCities(string prefixText, int count, string contextKey) 
{
   try
   {
     //return new string[] { “A Test1”, “B Test2”, “C Test3” };

     string selectCommand = @”SELECT CITIES.NAME FROM CITIES, COUNTRIES
     WHERE COUNTRIES.CC = CITIES.CC 
     AND COUNTRIES.CC = ‘” + contextKey + “‘ ” +
    “AND CITIES.NAME LIKE ‘” + prefixText + “%’ ” + // Add a % before prefixText to match the         pattern anywhere in the city name
     @”ORDER BY CITIES.NAME”;

     //MessageBox.Show(selectCommand);

     SqlConnection Connection = new SqlConnection(“Data Source=                <machine_name>;Initial Catalog=WORLD;User ID=’Ganesh Gopalan’;Password=<password>”);

     SqlCommand cmd = new SqlCommand(selectCommand, Connection);
     cmd.CommandType = CommandType.Text;
     SqlDataAdapter da = new SqlDataAdapter();
     da.SelectCommand = cmd;
     DataTable dt = new DataTable();
     da.Fill(dt);

     if (dt == null || dt.Rows.Count == 0)
         return null;

     string[] l = new string[dt.Rows.Count];

     int i = 0;

     foreach (DataRow row in dt.Rows)
     {
         l[i] = row[“NAME”].ToString();
         i++;
     }

     return l;
}
catch (Exception ex)
    {
         MessageBox.Show(“GetCities(): ” + ex.Message + “\r\n” + ex.StackTrace);
         return null;
    }
  }
}

The HelloWorld and HelloWorld2 web-methods are purely for testing. The real work is done by the GetCities web-method.

Note that the signature for the web-method that will fetch the cities must match the following signature exactly (including parameter names), or else the auto-complete will not work (e.g. if you change the first parameter from prefixText to prefix, you will get no results):

public string[] GetCities(string prefixText, int count, string contextKey) 

Screenshots of the database (country codes and country names and cities for country code = ‘AS’ (Australia)):

Countries

Figure 1 – Countries table.

CitiesAustralia

Figure 2 – Cities table showing cities for Australia. The total no. of cities for all countries is 2,881,140.

AutoComplete

Figure 3 – Autocomplete for cities beginning with ‘Mel’ for Australia.

The cities are available courtesy GeoDataSource.com (World Cities database free edition) and have been populated into the database.

That’s it. Comments and feed-back are welcome!

 

 

Advertisements

My Experience with App Development for Windows Phone

Some time back, I decided to become a Windows Phone developer. You have to sign up through http://dev.windowsphone.com and pay a small fee. For US based development the fee is $19 per year, while if you’re in India it is INR 860.

I developed 2 versions of a Photo Calendar. A free version that displays only the months in the current year and a paid version that is a perpetual calendar that allows you to jump to a random month or year. The app includes 60 stunning photos taken in the US, Cayman Islands and India.

The target platform is Windows Phone 7, so the app runs on both WP7 and WP8.

The process consists of uploading your XAP (app) file, your icons and 8 screenshots for potential users. The icons include an app tile that is 300 x 300 pixels and a background image that is 1000 x 800 pixels. The background image could be used by the Windows Phone Store to showcase your app.

WPSSubmitApp

The Windows Phone Store app submission page.

If your application targets Windows Phone 7, you can upload up to 8 screenshots in WVGA i.e. 480 x 800. If, however, you target Windows Phone 8, you may have to upload screenshots in higher resolutions as well e.g. 768 x 1280 and 720 x 1280.

These images are in addition to the standard icons that are part of the XAP. The standard icons include the ApplicationIcon.png file, the Background.png file and the SplashScreenImage.jpg file. These are not uploaded separately.

On the Windows Phone Developer website, some information is automatically read from your XAP file, such as the version and size.

Once all the screenshots, XAP(s) and icons have been uploaded, Microsoft will begin the app certification process. This took about 5 days for me. Microsoft probably checks for crashes and app responsiveness. Once the app passes certification, you will get an e-mail from Microsoft that your app has been accepted.

Congratulations

Message saying that the app has passed certification.

If you have a Windows Phone, you can check out the free version (Photo Calendar Single) here: http://www.windowsphone.com/en-us/store/app/photo-calendar-single/3800f2b4-f2bf-4a2d-8cf8-4d2b335e3652

Photo Calendar Multi (the paid version with more features) can be found here: http://www.windowsphone.com/en-us/store/app/photo-calendar-multi/b592482e-ecf6-4fc0-90d8-4de0eb175a83

Comments and feedback are welcome.

PhotoCalendarSingleWPStore

The Photo Calendar Single app on the Windows Phone Store.

PhotoCalendarMultiWPStore

The Photo Calendar Multi app on the Windows Phone Store.

P1020917

The Photo Calendar Single app ready to installed on a Samsung Windows Phone.

 

Developing for Windows Phone 8

The Windows Phone 7 SDK can be installed on Windows 7, but the Windows Phone SDK 8.0 can be installed only on Windows 8.

Also, in order to use the emulator for Windows Phone 8, certain conditions need to be met on the system that will host the development.

  • The system must support Hyper-V (virtualization software on Windows).
  • The processor on the system must support Second Level Address Translation (SLAT), also known as Extended Page Tables on Intel processors. These are supported by any processor that begins with I such as core i3, i5 and i7.
  • Virtualization must be enabled in the BIOS.
  • Data Execution Prevention must be enabled.

A tool called Coreinfo, available at http://technet.microsoft.com/en-us/sysinternals/cc835722 can be used to check if the above features are supported.

Coreinfo -v must be run  from a cmd prompt with administrative privileges.

coreinfo

Coreinfo output on a system with virtualization and SLAT, but with Hyper-V absent (the – indicates the feature is absent, while the * indicates it is present).

Some systems (e.g. laptops bought in “emerging markets” such as India) have a basic edition of Windows known as Windows 8 Single Language or Windows SL. However, Windows 8 SL does not support Hyper-V, a must for running the Windows Phone 8 emulator. As a result, you may have to upgrade your edition of Windows 8 SL to Windows 8 Professional. This can be done by going to Computer Properties and selecting “Get more features with a new edition of Windows 8”. You will then be prompted to purchase a product key online.

Once the key is purchased, you will get a dialog that says “Adding New Features”, “This might take a while depending on whether there are updates. Your computer might restart more than once”. That’s it. There are no ISOs to download, no DVDs to use – the additional features (including Hyper-V) are installed automatically from the local Windows 8 SL installation on your hard drive.

SystemInfo

The “Get more features with a new edition” link in System Info (from Computer | Properties), on a system running Windows 7.

WindowsFeatures

From Control Panel | Programs | Turn Windows Features on or off. A system with Hyper-V absent. Once Hyper-V is installed, it will show up here.

Once Hyper-V is installed, you should be able to run the Windows Phone 8 emulator.