Due to popular demand, I’ve gone ahead and created a new version of the AJAX Chat Tutorial using ASP.NET and SQL Server as the back end rather than PHP and MySQL.

View the results of the ASP.NET AJAX Web Chat Tutorial

Download the complete source of the ASP.NET AJAX Web Chat Tutorial.

For the most part, it’s all pretty much the same except for the back-end file. Because of this, I’m just going to let you read the pages already written about the front end at the original AJAX Chat Tutorial. Just note that you need to change the AJAX request URL from .php to .aspx in the functions sendChatText, getChatText and resetChat.

I’m pretty sure that I could have done this in a more elegant fashion, but it serves the purpose for learning some basic AJAX with ASP.NET.

The first step to create the AJAX web chat is to setup the database. I kept it real simple for the sake of this tutorial with just one table that holds our chat messages.  You can create this table in an existing database if you would like.

USE [ASP_AJAX_Chat]
GO
/****** Object:  Table [dbo].[message]    Script Date: 04/01/2007 12:23:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[message](
	[message_id] [INT] IDENTITY(1,1) NOT NULL,
	[chat_id] [INT] NOT NULL CONSTRAINT [DF_message_chat_id]  DEFAULT ((0)),
	[user_id] [INT] NOT NULL CONSTRAINT [DF_message_user_id]  DEFAULT ((0)),
	[user_name] [nvarchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[message] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[post_time] [DATETIME] NOT NULL CONSTRAINT [DF_message_post_time]  DEFAULT (getdate()),
 CONSTRAINT [PK_message] PRIMARY KEY CLUSTERED 
(
	[message_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I named my database ASP_AJAX_Chat and set it up in a SQL Server 2005 Express instance. You should be able to use this script in any version of SQL Server though.

Basically, this table just holds all the different chat messages sent to the server, who sent the message and what time it was posted.

So lets get right into the ASP.NET back-end page.  The first two lines should look very familiar if you have ever written an ASP.NET page before.  We’re going to import the System.Data.SqlClient library so that we can access our database

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>

Next, we are simply going to set some HTTP headers for the AJAX response. Most of these headers simply keep the browser from caching the AJAX response. If the browser cached the response, then we wouldn’t see any of the new messages.  The other header that we are sending is the Content-Type which tells the client the response will be coming back in the form of XML.  This makes it easier on the client side because we can simply use the JavaScript function XmlHttpRequest.responseXML() to get a JavaScript variable with the XML document.

    'Add some headers to keep the response from getting cached.
    Response.AddHeader("Expires", "Mon, 26 Jul 1997 05:00:00 GMT")
    Response.AddHeader("Last-Modified", Date.UtcNow)
    Response.AddHeader("Cache-Control", "no-cache, must-revalidate")
    Response.AddHeader("Pragma", "no-cache")
    Response.AddHeader("Content-Type", "text/xml; charset=utf-8")

Next thing we need to do is setup our database connection. You will need to set this to the connection string that connects to your database with the correct permissions. I don't really want to get into database permissions and connection strings because it's too big of a subject, but there is more than enough information out there on the Internet.

    'Enter you database connection string here.
    Dim conn As New SqlConnection("Data Source=YOURMACHINE\SQLEXPRESS;Initial Catalog=ASP_AJAX_Chat;Integrated Security=True")
    conn.Open() 

This code also opens the database connection so we are ready to make queries against it.

Next, we check to see if a new message was sent from the AJAX request that we should add to the chat.  Request.Form accesses all of the parameters sent in the HTTP POST.

    'Add any new messages that may have gotten sent.
    If Not Request.Form("message") Is Nothing Then

If a new message was sent to the server, then we create a new prepared statement in the form of a SqlCommand.  Using prepared statements helps us avoid SQL injection attacks.  I always tend to rely on the languages built-in string preparation for interacting with the database with user input as I'm sure it's more reliable and well tested than anything I would come up with.

        'Create a new insert command using a parameterized query.  
        'This helps eliminate the risk of SQL injection attacks.
        Dim insCommand As New SqlCommand("INSERT INTO message(chat_id, user_id, user_name, message, post_time) VALUES (@chat_id, 1, @name, @message, GETDATE())", conn) 

Our parameterized query contains 3 variables: chat_id, name and message that we need to set before we execute the query.  We set these by adding parameters to our insert SqlCommand.

        'Set our parameter values.  There is probably a prettier way of doing this.
        Dim paramChatID As New SqlParameter("chat_id", Data.SqlDbType.Int)
        paramChatID.Value = CInt(Request.QueryString("chat"))
        insCommand.Parameters.Add(paramChatID)
        Dim paramName As New SqlParameter("name", Data.SqlDbType.VarChar)
        paramName.Value = Request.Form("name")
        insCommand.Parameters.Add(paramName)
        Dim paramMsg As New SqlParameter("message", Data.SqlDbType.VarChar)
        paramMsg.Value = Request.Form("message")
        insCommand.Parameters.Add(paramMsg)

Once we have all of our parameters setup, we execute the query.  We call ExecuteNonQuery because we won't be returning any results.

        'Execute the query to add the message to the database.
        insCommand.ExecuteNonQuery()

Next we're going to check to see if there was a chat reset request sent.  Looking at the code, it would probably make more sense for this to come before inserting a new message, but since this is just a proof of concept, I'm going to be lazy and not change it around.

    '//Check to see if a reset request was sent.
    If Not Request.Form("action") Is Nothing AndAlso Request.Form("action") = "reset" Then
        Dim delCommand As New SqlCommand("DELETE FROM message WHERE chat_id = @chat_id")
        Dim paramChatID As New SqlParameter("chat_id", Data.SqlDbType.Int)
        paramChatID.Value = CInt(Request.QueryString("chat"))
        delCommand.Parameters.Add(paramChatID)
        delCommand.ExecuteNonQuery()
    End If

Just like our message insert, we create a new parameterized query since we are working with user submitted input.

Now that we are finished running any necessary actions against the database, we can create the actual AJAX response. We will store the response in a string variable name xml and send it all at once to the client. 

    'Create the acutal response.
    Dim xml As String = "<?xml version=""1.0"" ?><root>"

First, we check to make sure they passed a chat room, although in this example it doesn't matter.  If they didn't pass a chat_id, then we will inform them the request is invalid.

    'Check to make sure a chat room was passed.  If not alert them of their mistake
    If Request.QueryString("chat") Is Nothing Then
        xml &= "You are not currently in a chat session"
        xml &= "<message id=""0"">"
        xml &= "<user>Admin</user>"
        xml &= "<text>You are not currently in a chat session.</text>"
        xml &= "<time>" & Date.Now.Hour & " " & Date.Now.Minute & "</time>"
        xml &= "</message>"
    Else

To save on bandwidth, we aren't going to send the entire message stack, just the messages that have been added since last time the user got the set of messages.  We set last to 0 since that would be the beginning of the message stack.  If a last message was sent, the we set that to the current last message.  Last is based off the primary key of the table which auto-increments, so we know all messages coming in will have a higher primary key than last.

        Dim last As Integer = 0
        If Not Request.QueryString("last") Is Nothing Then
            'I don't believe that we need to set last as a parameter.
            'Afterall, if someone tries to hack it, either the value will get set to zero,
            'or an exception will be thrown, the query will never get executed with anything
            'other than an Integer value.
            last = CInt(Request.QueryString("last"))
        End If

Once we have our last variable set, we will create our SQL SELECT statement.  Since chat_id is still taken from user input, we will set that up as a prepared parameter in the query.  We don't have to worry about last since we are setting it up and it is a strongly typed integer.  If any value other than an integer is attempted to be assigned to last, then the system will throw an exception and the hack attempt will fail.

        'Create our message select command.
        Dim msgCommand As New SqlCommand("SELECT message_id, user_name, message, post_time FROM message WHERE chat_id = @chat_id and message_id > " & last, conn)
        Dim paramChatID As New SqlParameter("chat_id", Data.SqlDbType.Int)
        paramChatID.Value = CInt(Request.QueryString("chat"))
        msgCommand.Parameters.Add(paramChatID)

This time, instead of executing a "Non Query" which doesn't return any results, we will execute a query that will return a SqlDataReader.  The SqlDataReader will allow use to move through the different result rows to add each new message to the response.  While reader.Read will return a new row until the reader as moved to the end of the record set.

        Dim reader As SqlDataReader = msgCommand.ExecuteReader(Data.CommandBehavior.CloseConnection)
        '//Loop through each message and create an XML message node for each.
        'Notice that we're making use of Server.HtmlEncode to avoid script injection attacks.
        While reader.Read
            xml &= "<message id=""" & reader.Item("message_id") & """>"
            xml &= "<user>" & Server.HtmlEncode(reader.Item("user_name")) & "</user>"
            xml &= "<text>" & Server.HtmlEncode(reader.Item("message")) & "</text>"
            xml &= "<time>" & reader.Item("post_time") & "</time>"
            xml &= "</message>"
        End While

As you can tell, I'm simply create the response string by manually adding to the xmo variable.  A lot of people might think this is sloppy and say I'm not a real programmer since I'm not using an XmlBuilder, but hey - it works and isn't that the only real measure of success?  We could create this string quite a few different ways and some might be better than this, but here is a quick dirty way to make it work.

Notice that I call the function HtmlEncode on each of the values that are input from the user.  If these weren't here, then the user could inject JavaScript which would cause some weird issues for the users of the chat application.  This will simply take any HTML values and turn them into their HTML entity equivalent.

        xml &= "</root>"
        'Send out the AJAX response.
        Response.Write(xml)
    End If

Now that we are finished adding any new messages to our AJAX response, we will go ahead and close out our XML root element and send the response to the client.

And that's it - an ASP.NET back-end for the AJAX Chat Tutorial.

Go and read the about the front end from the original tutorial and you are ready to rock!