A WebMethod is the simplest way to enable communication between a browser with an HTML page with JavaScript and a JQuery Ajax call and ASP.NET. An Ajax call allows us to call methods on the server without any postback (page refresh).
Create a table on a SQL Server database. This table will be used to query data and display it in the HTML page.
CREATE TABLE dbo.tb_GuysStThomas
(
[lTaskID] int IDENTITY (1,1) NOT NULL,
[lDepth] int NULL,
[sTaskType] nvarchar(255) NULL,
[sDescription] nvarchar(255) NULL,
[dtStartDate] datetime NULL,
[dtEndDate] datetime NULL,
[fPercentCompleted] real DEFAULT (0) NULL,
[bSummary] bit DEFAULT (0) NULL,
[bHasTasks] bit DEFAULT (0) NULL,
CONSTRAINT [PK_dbo_GuysStThomas] PRIMARY KEY CLUSTERED ([lTaskID] ASC)
);
This table is from the Work Breakdown Structure Example of the ActiveGantt scheduler component. It contains String types, integers, dates and real datatypes.
Run the following script to add some data to the table:
SET IDENTITY_INSERT dbo.tb_GuysStThomas ON;
INSERT INTO dbo.tb_GuysStThomas ([lTaskID],[lDepth],[sTaskType],[sDescription],[dtStartDate],[dtEndDate],[fPercentCompleted],[bSummary],[bHasTasks]) VALUES (1,0,N'A',N'Capital Plan',{ts '2007-03-08 12:00:00.000'},{ts '2007-10-19 00:00:00.000'},0.4053452,0,1);
INSERT INTO dbo.tb_GuysStThomas ([lTaskID],[lDepth],[sTaskType],[sDescription],[dtStartDate],[dtEndDate],[fPercentCompleted],[bSummary],[bHasTasks]) VALUES (2,0,N'F',N'Strategic Projects',{ts '2006-11-01 12:00:00.000'},{ts '2007-09-14 00:00:00.000'},0.7531173,1,1);
INSERT INTO dbo.tb_GuysStThomas ([lTaskID],[lDepth],[sTaskType],[sDescription],[dtStartDate],[dtEndDate],[fPercentCompleted],[bSummary],[bHasTasks]) VALUES (3,1,N'F',N'Infrastructure Work Team',{ts '2007-02-01 12:00:00.000'},{ts '2007-09-05 00:00:00.000'},0.772742,1,1);
INSERT INTO dbo.tb_GuysStThomas ([lTaskID],[lDepth],[sTaskType],[sDescription],[dtStartDate],[dtEndDate],[fPercentCompleted],[bSummary],[bHasTasks]) VALUES (4,2,N'A',N'Guys Tower Façade Feasability',{ts '2007-02-01 12:00:00.000'},{ts '2007-08-01 00:00:00.000'},0.6038781,0,1);
...
SET IDENTITY_INSERT dbo.tb_GuysStThomas OFF;
The SET IDENTITY_INSERT command Allows explicit values to be inserted into the identity column of a table. The above script is abbreviated for clarity, you can find the complete script here:
Open a new session of Visual Studio 2012 or 2013, and click on new project:
Create a new ASP.NET web form application by clicking on ASP.NET Empty Web Application. In this case we have named the project WebApplication1. Once you click on the OK button you will have an empty ASP.NET web forms application.
Right click on WebApplication1 in Solution Explorer, select Add from the dropdown menu and choose New Item...
Select Web Form from the available options and name it: Default.aspx.
Right click on WebApplication1 in the Solution Explorer, select Add from the dropdown list and select New Folder. Rename this folder to Scripts.
Select the newly renamed folder Scripts and right click.
Select Add from the dropdown list and select Existing Item.
Navigate to the folder where the JQuery files are stored and select jquery-2.0.3.js and jquery-2.0.3.min.js. You can download both files from this location:
http://code.jquery.com/jquery-2.0.3.js
http://code.jquery.com/jquery-2.0.3.min.js
jQuery is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML. jQuery's syntax makes it easier to navigate a document, select DOM elements, create animations, handle events, and more importantly: develop Ajax applications. The min.js is a compacted version of the .js file and should be used for production, debugging should always be done using the .js file. Other than that both files contain the same code.
Select Default.aspx from the Solution Explorer, right click on it and select View Markup from the dropdown box.
Change the HTML markup code to the following:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<script src="Scripts/jquery-2.0.3.js"></script>
<style>
table, th, td {
border: 1px solid black;
}
</style>
<script>
function mp_GetData() {
$.ajax({
type: "POST",
url: "Default.aspx/GetData",
contentType: "application/json; charset=utf-8",
dataType: "json",
error: function (jqXHR, sStatus, sErrorThrown) {
alert( 'data: ' + sErrorThrown);
alert( 'Get Data Error: ' + sStatus);
},
success: function (data) {
$("#oTable").empty();
var oTable = data.d;
for (i = 0; i <= oTable.Rows.length - 1; i++) {
$("#oTable").append("<tr><td>" + oTable.Rows[i].lTaskID + "</td><td>" + oTable.Rows[i].sDescription + "</td><td>" + new Date(parseInt(oTable.Rows[i].dtStartDate.substr(6))) + "</td><td>" + new Date(parseInt(oTable.Rows[i].dtEndDate.substr(6))) + "</td></tr>");
}
}
});
}
</script>
</head>
<body>
<button type="button" onclick="mp_GetData();">Click to Retrieve Data</button>
<table id="oTable">
</table>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
In the head section we're including a reference to the debug version of the JQuery library and we're defining a function called mp_GetData that makes an Asynchronous JQuery Ajax call. This Ajax function calls a web method in Default.aspx called GetData. In the body we create a button that when clicked calls mp_GetData. mp_GetData first calls the server using a POST command and when it receives data from the server in JSON format in the success section and adds rows to the Table called oTable in the body section. At this point and time the client side code is complete.
JSON is short for JavaScript Object Notation and is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs.
Right click again in Default.aspx in Solution Explorer and select View Code. Change the Form's code to the following:
using System;
using System.Collections.Generic;
using System.Web.Services;
using System.Data.SqlClient;
namespace WebApplication1
{
public class clsRow
{
public int lTaskID { get; set; }
public string sDescription { get; set; }
public DateTime dtStartDate { get; set; }
public DateTime dtEndDate { get; set; }
public double fPercentCompleted { get; set; }
}
public class clsTable
{
public clsTable()
{
Rows = new List<clsRow>();
}
public List<clsRow> Rows { get; set; }
}
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod()]
public static clsTable GetData()
{
clsTable oTable = new clsTable();
using (SqlConnection oConn = new SqlConnection("server=YOURCOMPUTER\\SQLEXPRESS;uid=yourUserID;pwd=yourPassword;database=yourDatabase;"))
{
oConn.Open();
SqlCommand oCmd = new SqlCommand("SELECT * FROM tb_GuysStThomas", oConn);
SqlDataReader oReader = oCmd.ExecuteReader();
while (oReader.Read() == true)
{
clsRow oRow = new clsRow();
oRow.lTaskID = Convert.ToInt32(oReader["lTaskID"]);
oRow.sDescription = oReader["sDescription"].ToString();
oRow.dtStartDate = Convert.ToDateTime(oReader["dtStartDate"]);
oRow.dtEndDate = Convert.ToDateTime(oReader["dtEndDate"]);
oTable.Rows.Add(oRow);
}
oReader.Close();
}
return oTable;
}
}
}
The class clsTable is simply a List of clsRow objects. When using a web method with ASP.NET, the framework automatically serializes the clsTable class to the appropriate JSON string without any intervention. That's why we have to populate the clsTable object from the SqlDataReader.
Now click on Debug -> Start Debugging or press the F5 key.
You will get an HTML page with a button:
If you click on the Click to Retrieve Data button, the table on the HTML page will be refreshed with rows from the database:
