Introduction
As jQuery becomes ever more widespread, we get asked a lot about integration with databases. This article is target at those taking their first step into the world of jQuery and integrating pages with a Database. The same principles could also be used with a different datasource such as XML.
Background
The first stage is to load some simple html containing a Members details from a ficticious Membership Database when the user enters a Membership Number. This is not designed to be a secure mechanism but purely to explain the steps involved in the process.
Following this, the code is expanded to show how a collection can be used rather than simple text.
Simple Text Display
The first method we will look at is where basic information is sent back to the client and displayed within a container. We start by setting the standard Javascript blocks on the page. The first script calls the library and in this example uses the standard js file that comes with Visual Studio.
<script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script type="text/javascript">
</script>
Next we set up the html that will be located in the html of the page itself.
<ul>
<li>
<label>Member ID</label>
<input id="member_id" type="text" />
<input id="blnLoadMember" type="button" value="Get Details" />
</li>
</ul>
<div id="MemberDetails"></div>
Next we add the jQuery script elements. As this is a process that will be called by some user interaction, we will need to add the script and also add an event to the button that we added to the page.
function GetMember() {
$('input[type=button]').attr('disabled', true);
$("#MemberDetails").html('');
$("#MemberDetails").addClass("loading");
$.ajax({
type: "POST",
url: "Members.asmx/GetMemberDetails",
data: "{'MemberNumber': '" + $("#txt_id").val() + "'}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnGetMemberSuccess,
error: OnGetMemberError
});
}
function OnGetMemberSuccess(data, status) {
}
function OnGetMemberError(request, status, error) {
}The process uses three functions. Whilst these could be combined to make the script more efficient, they are left as three seperate functions here to help explain the different steps in the process that they represent.
GetMember
This is the main stage in our process and is called by the button being clicked. Once called it follows a number of stages:
- Sets the button to disabled - to prevent duplicate clicks
- Clears the html in the MemberDetails div.
- Adds the loading class to the div - this in reality would show a loading icon.
- Calls the ajax element to process the data
- Follows either the success or error results by calling the appropriate function.
Our code will utilise a Web Service to return the data from the SQL Server and calls it by specifying a number of parameters:
- type - you would normally use Get or Post here depending on your requirement
- url - the path to the web service asmx file and the web method you are going to call
- data - in this instance the MemberNumber that we are going to pass
- contentType, dataType - specifying we are going to use json formatting
- success - what to do on success
- error - what to do following an error
data: "{'MemberNumber': '" + $("#member_id").val() + "'}"
Here, the data being sent as MemberNumber is made up from a call to the member_id object. In this case it is the textbox on the page.
Next, the Web Service element of the process. This is a simple service set up to use a SQLDataReader and retrieve the data from a SQL Server. The web service is placed in the members.asmx file.
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.Script.Services
<WebService(Namespace:="<a href="http://tempuri.org/">http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class Members
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetMemberDetails(MemberNumber As String) As String
Dim con As SqlConnection
Dim dr As SqlDataReader
Dim cmd As New SqlCommand
Dim Details As String = String.Empty
Dim ConString As String = ConfigurationManager.ConnectionStrings("CMS_Database").ToString
con = New SqlConnection(ConString)
con.Open()
Try
With cmd
.CommandType = CommandType.Text
.CommandText = "SELECT strMemberNumber, strScreenName, strForename, strSurname, strEmail " _
& "FROM Memberships WHERE strMemberNumber = @strMemberNumber"
.Connection = con
.Parameters.AddWithValue("@strMemberNumber", MemberNumber)
End With
dr = cmd.ExecuteReader
While dr.Read()
Details = "<table><tr><tr><td>" & dr("strMemberNumber").ToString() & "</td></tr>" _
& "<tr><td>" & dr("strScreenName").ToString() & "</td></tr>" _
& "<tr><td>" & dr("strForename").ToString() & " " & dr("strSurname").ToString() & "</td></tr>" _
& "<tr><td>" & dr("strEmail").ToString() & "</td></tr></table>"
End While
con.Close()
Return Details
Catch x As Exception
Return x.ToString()
Finally
con.Dispose()
End Try
End Function
End ClassThe web service receives the call and processes the call to the database where the users details are stored as a string containing table html.
This string is then returned.
Please note that the data is returned wrapped in a data.d json format, this is to prevent passing of script data directly.
Once returned, the success process is called as shown below:
function OnGetMemberSuccess(data, status) {
$("#MemberDetails").removeClass("loading");
$("#MemberDetails").html(data.d);
$('input[type=button]').attr('disabled', false);
}The "loading" class is removed from the container. Next the entire data is written to the html inside the container.
Should an error be encountered, the onGetMemberError is called and displays the error in the same way.
function OnGetMemberError(request, status, error) {
$("#MemberDetails").removeClass("loading");
$("#MemberDetails").html(request.statusText);
$('input[type=button]').attr('disabled', false);
}
After each call, the button is re-enabled again to allow processing.
Finally, we add the event handler to the button which will call the jQuery script itself.
<input id="blnLoadMember" type="button" value="Get Details" onclick="GetMember(); return false;"/>
This completes the simple call section and shows how we can process a simple call - displaying the text sent back by a Web Service.
Displaying Collections
Sometimes, we require more than to display simple text and may wish to return the data in a format that can be displayed on multiple end clients. In such cases, just the raw data is returned and formatted as determined by the client.
Firstly, we enter the core elements of the page again as before.
<script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
<script type="text/javascript">
</script>
Next we set up the html that will be located in the html of the page itself.
<p><input id="btnLoadAll" type="button" value="Get All Members" /></p>
<div id="MemberList"></div>
Next we add the jQuery script elements. This follows a similar layout to our first simple example.
function GetAllMembers() {
$("#MemberList").html('');
$("#MemberList").addClass("loading");
$.ajax({
type: "POST",
url: "Members.asmx/GetAllMembers",
data: "{}",
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnGetAllMembersSuccess,
error: OnGetAllMembersError
});
}
function OnGetAllMembersSuccess(data, status) {
}
function OnGetAllMembersError(request, status, error) {
}As we are looking to retrieve all the data in this process, we do not pass any data to the web method but process the returning data in the same manner.
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.Script.Services
Public Class Member
Public ScreenName As String
Public MemberNumber As String
Public Forename As String
Public Surname As String
Public Email As String
End Class
<WebService(Namespace:="<a href="http://tempuri.org/">http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<System.Web.Script.Services.ScriptService()> _
Public Class Members
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetAllMembers() As List(Of Member)
Dim con As SqlConnection
Dim dr As SqlDataReader
Dim cmd As New SqlCommand
Dim Details As String = String.Empty
Dim ConString As String = ConfigurationManager.ConnectionStrings("CMS_Database").ToString
con = New SqlConnection(ConString)
con.Open()
Try
With cmd
.CommandType = CommandType.Text
.CommandText = "SELECT strMemberNumber, strScreenName, strForename, strSurname, strEmail " _
& "FROM Memberships ORDER BY strSurname"
.Connection = con
End With
dr = cmd.ExecuteReader
Dim Members As New List(Of Member)
While dr.Read()
Dim Member As New Member
With Member
.ScreenName = dr("strScreenName")
.MemberNumber = dr("strMemberNumber")
.Forename = dr("strForename")
.Surname = dr("strSurname")
.Email = dr("strEmail")
End With
Members.Add(Member)
End While
con.Close()
Return Members
Catch x As Exception
Finally
con.Dispose()
End Try
End Function
End Class
When the Web Method is called, the SQLDataReader iterates through the results creating instances of the Member class to add to the list. This list is then returned via the web service.
On a successful completion of processing the data, the script iterates through the results and displays them as shown.
function OnGetAllMembersSuccess(data, status) {
$("#MemberList").removeClass("loading");
var members = data.d;
$('#MemberList').empty();
$.each(members, function (index, member) {
$('#MemberList').append('<ul><li><label>Screen Name</label>' + member.ScreenName + '</li>' +
'<li><label>Number</label>' + member.MemberNumber + '</li>' +
'<li><label>Name</label>' + member.Forename + ' ' + member.Surname + '</li>' +
'<li><label>Email</label>' + member.Email + '</li></ul>');
});
}Each entry in the json formatted response is then iterated through and each relevant key written to the page. The structure replicates that created in the Member class used to populate the data.
The results that are written to the container can then be formatted using css to fit in with the style adopted by the website.
Finally, the event handler is added to the button to call the script.
<input id="btnLoadAll" type="button" value="Get All Members" onclick="GetAllMembers(); return false;"/>
Points of Interest
This is only a simple demonstration to show the process of utilising a datasource via a web service. In this example, we have used a SQL Server Database and a SQLDataAdapter - however any datasource could be used.
If you are using Visual Studio or another RAD tool, some comment out the following line:
<System.Web.Script.Services.ScriptService()> _
You should ensure they are enabled to call the webmethods from a script.
