Ajax Autocomplete Extender Using Sql Database

using System;

using System.Web;

using System.Collections;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

 

/// 


/// Summary description for AutoComplete

/// 


 

[WebService(Namespace = http://tempuri.org/)]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.Web.Script.Services.ScriptService]

 

public class AutoComplete : System.Web.Services.WebService

{

    [WebMethod]

    public string[] GetCountriesList(string prefixText)

    {

        DataSet dtst = new DataSet();

        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.AppSettings[ConnectionString]);

        string strSql = SELECT CountryName FROM Tbl_Countries WHERE CountryName LIKE ' + prefixText + %' ;

        SqlCommand sqlComd = new SqlCommand(strSql, sqlCon);

        sqlCon.Open();

        SqlDataAdapter sqlAdpt = new SqlDataAdapter();

        sqlAdpt.SelectCommand = sqlComd;

        sqlAdpt.Fill(dtst);

        string[] cntName = new string[dtst.Tables[0].Rows.Count];

        int i = 0;

        try

        {

            foreach (DataRow rdr in dtst.Tables[0].Rows)

            {

                cntName.SetValue(rdr[CountryName].ToString(), i);

                i++;

            }

        }

        catch { }

        finally

        {

            sqlCon.Close();

        }

        return cntName;

    }

}