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......

App_Offline.htm .... Taking an ASP.NET 2.0 application offline

Sometimes what happens is , when you are working on some maintainence stuff on your website , then you need to STOP the application from functioning. This could prove to be a tedious task . So as a solution to this ,comes another amazing feature of Visual Studio.Net which we will see in the coming lines below.

Create a file named "app_offline.htm" in the root of the application . When ASP.NET sees it, it will shut-down the app-domain for the application (and not restart it for requests) and instead send back the contents of the app_offline.htm file in response to all new dynamic requests for the application. So when you are done updating the site, just delete the file and it will come back online.

So if you use the app_offline.htm feature, you should make sure you have at least 512 bytes of content within it to make sure that your HTML shows up to your users.

For example :

This page is U N D E R C O N S T R U C T I O N !!. Please visit after sometime. Thank You !!

I hope this article helps someone or the other someday. :-)

SQL - CLR Integration in Visual Studio .NET

The SQL – CLR integration feature has been into existence since VS 2005. The most amazing feature of this is, frankly speaking , you can create a dll using VS.Net and then you can access that dll’s logic in SQL Server 2005.. Wanna know more about this , then lets get it started…

The first and foremost point is to "ENABLE" the CLR functionality in Sql Server 2005 ,( 'coz by default it's DISABLED) which you can do it in this way ... Write the below given commands in the Sql Server Management Studio IDE in the database where you want to perform this.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step1: Create a SQL Server Database Project in Visual Studio .

I named it as "My Project", you can name it as you like.

Now it asks for a Database connection instance ,like the one shown below.





Select your desired database conection or create a new one by clicking " Add New Reference".

Step2: Right Click on the Project name and add a new item " User-Defined Function" ( I have used function here, you can create a Stored Procedure or trigger as well ... depending on your requirement ).

By default , the Function Class contains a function returning "Hello " . So we'll use that one as our function.

Step 3 : Now , BUILD the project and then the dll is created for that project in its root folder.

Now , right click on the project and click "Deploy" . This will deploy your project onto the instance of Sql Server and also creates an ASSEMBLY with the same name as per the name of your project's dll. for eg; here it's name is MyProject .

To check out whether the assembly has been created or not , execute this in Sql Server Management Studio IDE ,

Select * from sys.Assemblies

This will display the registered assemblies in that database .

Step 4: As of now the assembly is created, so now we need to create a FUNCTION that references that assembly .

CREATE function MyFunction ()
returns nvarchar(15)
AS EXTERNAL NAME MyProject.[UserDefinedFunctions].Function1

Note : MyFunction () is the name of the function you want to create.

MyProject is the name of the Assembly ,which got created when we click the DEPLOY option .

UserDefinedFunctions is the name of the Parent Class present in the file Function1.cs

Function1 is the name of the function present in the file Function1.cs

Execute the above code in Sql Server Management Studio IDE and now your Function is ready to be used . Check it out by executing the below command,

Select dbo.MyFunction ()

Now this returns the output as "Hello" ..

From this article , we have learnt as to how one can use the SQL - CLR Integration feature of Visual Studio .Net.

Hope you liked the article . Till then keep rocking and enjoy programming .

Disable Dates in a Calendar

From my previous experiences , what i have found out is that sometimes we need to disable the previous and coming dates in a calendar control. So finally i thought to post it out. It goes like this....,



protected void Calendar1_DayRender(object sender, DayRenderEventArgs e)
{
if (e.Day.Date< DateTime.Today)
{
e.Day.IsSelectable = false;
e.Cell.ToolTip = "This date is not available";
}
else if (e.Day.Date> DateTime.Today)
{
e.Day.IsSelectable = false;
e.Cell.ToolTip = "This date is not available";
}
}

Keep rockiin........

Detecting User's IP Address

If your website contains sensitive data and you don't want any spamming or any malicious use of your website ,then one thing you can do is to keep a track of the visiting user's system IP and this can be done in some easy steps given below,


string strHostName = System.Net.Dns.GetHostName();
string clientIPAddress = System.Net.Dns.GetHostAddresses(strHostName).GetValue(0).ToString();

Now set the value of clientIPAddress to either a Textbox or label or any control as per your requirement.