ASP.NET AJAX Auto Complete Extender with Database

Of late, there has been a lot of speculation about AJAX Control Toolkit's AutoComplete Extender. Basically , this control loads the data specific to the entered text..
So now we will see as to how to connect this control to database and fetch the data when the user enters a specific word or phrase.....

AutoComplete.aspx





Now let us see the code for web service that will help us in fetching the data..

AutoComplete.cs

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class AutoComplete : WebService
{
[WebMethod]

public string[] SearchDB(string prefixText, int count)
{
if (count == 0)
{
count = 10;
}

SqlConnection con = new SqlConnection("server=localhost;database=your database;uid=your username;pwd=your pwd");

string str = "select * from table where username like @prefixText";
con.Open();
SqlDataAdapter da = new SqlDataAdapter(str, con);

da.SelectCommand.Parameters.Add("@prefixText", SqlDbType.VarChar, 50).Value = prefixText + "%";
DataSet ds = new DataSet();
da.Fill(ds);

int cnt = ds.Tables[0].Rows.Count;
List items = new List(cnt);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{


items.Add(ds.Tables[0].Rows[i]["UserName"].ToString());
}
return items.ToArray();

}
}


Stylesheet.css
/* AutoComplete highlighted item */

.autocomplete_highlightedListItem
{
background-color: #ffff99;
color: black;
padding: 1px;
}

/* AutoComplete item */

.autocomplete_listItem
{
background-color : window;
color : windowtext;
padding : 1px;
}

Now let's check out the result after running the application ..





NOTE:Please do comment regarding the article as it'll help me to write even more specifically..

Failed to access IIS Metabase

This kind of error usually comes when you install ASP.NET first and then IIS. So as a solution to this , you just need to do one thing , write the given syntax in the command line and run it.


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet-regiis -i


Hope this helps…

Memory Exception in ASP.NET

Exceptions are an integral part of programming. when you do programming, exceptions are bound to occur.Although there are many kinds of exceptions that are prevalent, but one of the most annoying of 'em all is the 'System.OutOfMemoryException'. Although there have been many discussions/posts regarding this exception 'coz this kind of exception arises out of many possibilites. As far as my experience goes, i've found some reasons as to why this exception occurs.

1) Using a higher value for Max Pool Size in Web.Config



Reason for using Max Pool Size is , it specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.
When a connection is opened and a pool is created, multiple connections are added to the pool to bring the connection count to the configured minimum level. Connections can be subsequently added to the pool up to the configured maximum pool count. When the maximum count is reached, new requests to open a connection are queued for a configurable duration.


2) Using DataReader but not closing it after the task finishes.

Of late , this has been noticed that many times we OPEN the DataReader but don't CLOSE the reader after a specific task has been finished. So as a better practice, do remember to CLOSE the DataReader whenever you OPEN it.
Also , do keep a check on CLOSING/DISPOSING the database connection.

3) Objects not being disposed.

C#, through the .NET Framework common language runtime (CLR), automatically releases the memory used to store objects that are no longer required. The release of memory is non-deterministic; memory is released whenever the CLR decides to perform garbage collection. However, it is usually best to release limited resources such as file handles and network connections as quickly as possible. The using statement allows the programmer to specify when objects that use resources should release them. The object provided to the using statement must implement the IDisposable interface. This interface provides the Dispose method, which should release the object's resources.
A using statement can be exited either when the end of the using statement is reached or if an exception is thrown and control leaves the statement block before the end of the statement.


Font font2 = new Font("Arial", 10.0f);
using (font2)
{
// use font2
}

Hope this helps someone some day or the other......