Introduction
This article uses 8 steps to create an example of how to read and write from HTML to a SQL database in a .NET web application using jQuery, JSON, and XML. This example is very streamlined, it only uses the absolute parts that are essential to the actual data transfer. I intentionally left out all validation and error checking.
I have read many books, articles and blogs on data transfer and this article is an attempt to tie all of that together in one easy to understand example. I hate wordy code articles so I tried to keep my comments brief.
HTML
Step 1
Create a new website in Visual Studio and add a web form with the following code. This website will update a very basic contact list. The webpage contains an insert div and an update div. It contains the very minimum code I could use to accomplish the data transfer.
<div style="width: 600px">
<div style="float: left; width: 300px">
<label>Insert</label>
<div>
<label for="txtFirstName_i">First Name</label>
<input id="txtFirstName_i" type="text" />
</div>
<div>
<label for="txtLastName_i">Last Name</label>
<input id="txtLastName_i" type="text" />
</div>
<div>
<label for="txtCompany_i">Company</label>
<input id="txtCompany_i" type="text" />
</div>
<input id="btnInsert" type="button" value="Insert" />
</div>
<div style="float: left; width: 300px">
<label>Update/Delete</label>
<div>
<label for="cboContacts">Contacts</label>
<select id="cboContacts">
<option></option>
</select>
</div>
<div>
<label for="txtFirstName_u">First Name</label>
<input id="txtFirstName_u" type="text" />
</div>
<div>
<label for="txtLastName_u">Last Name</label>
<input id="txtLastName_u" type="text" />
</div>
<div>
<label for="txtCompany_u">Company</label>
<input id="txtCompany_u" type="text" />
</div>
<div>
<input id="btnUpdate" type="button" value="Update" />
<input id="btnDelete" type="button" value="Delete" />
</div>
</div>
</div>
Step 2
Download the latest jQuery and JSON2 libraries and add them to your new website.
SQL
Step 3
Create our SQL table.
CREATE TABLE [dbo].[_Contacts](Contacts](
[ContactID] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Company] [varchar](50) NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [) ON [PRIMARY]
Step 4
Once we have the project setup, we start with our SQL code and create the one stored procedure that we will need to update and insert data into our datatable. This stored procedure takes the XML data that we pass it and stores it in a temp datatable using a nodes() method. Once the data is in the temp table, if the entry exists, it will update our new datatable and if it does not, it will insert it into our new datatable.
CREATE PROCEDURE [dbo].[_UpdateContact]
@XML XML
AS
SET NOCOUNT ON
DECLARE @Temp TABLE ([ContactID] BIGINT, [FirstName] VARCHAR(50),
[LastName] VARCHAR(50), [Company] VARCHAR(50))
INSERT INTO @Temp
SELECT x.value('(ContactID/text())[1]', 'bigint'),
x.value('(FirstName/text())[1]', 'varchar(50)'),
x.value('(LastName/text())[1]', 'varchar(50)'),
x.value('(Company/text())[1]', 'varchar(50)')
FROM @XML.nodes('//Contact') IDX ( x )
IF EXISTS ( SELECT * FROM @Temp WHERE [ContactID] IS NULL )
BEGIN
INSERT INTO dbo.[_Contacts] ( FirstName, LastName, Company )
SELECT FirstName, LastName, Company
FROM @Temp
END
ELSE
BEGIN
UPDATE t1
SET t1.[FirstName] = ISNULL(t2.[FirstName], t1.[FirstName]),
t1.[LastName] = ISNULL(t2.[LastName], t1.[LastName]),
t1.[Company] = ISNULL(t2.[Company], t1.[Company])
FROM _Contacts t1
JOIN @Temp t2 ON t1.[ContactID] = t2.[ContactID]
END
SELECT ContactID, FirstName, LastName, Company
FROM dbo._Contacts
.NET
Step 5
In order to simplify our conversions, we need create a helper module that uses Extension Methods to convert the class properties to XML and datatables to JSON data.
Public Module Convert
<Extension()> _
Public Function XML(ByVal Record As Object, RecordType As System.Type) As String
Using sw As New StringWriter()
Using xw As XmlWriter = XmlWriter.Create(sw, New XmlWriterSettings() _
With {.OmitXmlDeclaration = True})
Dim x As New XmlSerializer(RecordType)
x.Serialize(xw, Record)
Return sw.ToString
End Using
End Using
End Function
<Extension()> _
Public Function JSON(ByVal dt As DataTable) As String
Dim l As New List(Of Dictionary(Of String, Object))()
For Each dr As DataRow In dt.Rows
Dim dic As New Dictionary(Of String, Object)
For Each dc As DataColumn In dt.Columns
dic.Add(dc.ColumnName, dr(dc.ColumnName).ToString())
Next
l.Add(dic)
Next
Dim js As New JavaScriptSerializer
Return js.Serialize(l)
End Function
End Module
Step 6
Next, we will create a data class to handle all of the .NET data functions (select, insert, update and delete). The update and insert are combined into one function that passes back the updated table. The select function is standard query. The delete function also passed back the updated table.
Public Class Contact
Public Property ContactID() As String = ""
Public Property FirstName() As String = ""
Public Property LastName() As String = ""
Public Property Company() As String = ""
Private Shared Conn As String = "data source=localhost\SQLEXPRESS; initial catalog=jocoder;_
Trusted_Connection=True;"
Shared Function GetTable() As DataTable
GetTable = New DataTable
Using cmd As New SqlCommand("SELECT ContactID, FirstName, LastName, Company " & _
"FROM dbo.[_Contacts]", New SqlConnection(Conn))
cmd.Connection.Open()
GetTable.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
End Using
End Function
Shared Function Update(ByVal Record As Contact) As DataTable
Update = New DataTable
Using cmd As New SqlCommand("_UpdateContact", New SqlConnection(Conn)) _
With {.CommandType = CommandType.StoredProcedure}
cmd.Parameters.Add(New SqlParameter_
("@XML", SqlDbType.Xml)).Value=Record.XML(GetType(Contact))
cmd.Connection.Open()
Update.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
End Using
End Function
Shared Function Delete(ByVal ContactID As String) As DataTable
Delete = New DataTable
Using cmd As New SqlCommand("DELETE FROM dbo.[_Contacts] WHERE ContactID=@ContactID; " &
"SELECT ContactID, FirstName, LastName, Company FROM dbo.[_Contacts]",
New SqlConnection(Conn))
cmd.Parameters.AddWithValue("@ContactID", ContactID)
cmd.Connection.Open()
Delete.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
End Using
End Function
End Class
Step 7
The last part of the .NET code is to add Web Methods that we can call from the JavaScript.
Partial Class _Default
Inherits System.Web.UI.Page
<WebMethod()> _
Public Shared Function GetContacts() As String
Return Contact.GetTable.JSON
End Function
<WebMethod()> _
Public Shared Function UpdateRecord(ByVal record As Contact) As String
Return Contact.Update(record).JSON()
End Function
<WebMethod()> _
Public Shared Function DeleteContact(ByVal ContactID As String) As String
Return Contact.Delete(ContactID).JSON()
End Function
End Class
JavaScript
Step 8
The final step is to add the following JavaScript to the header of the web form. This script initially populates the options in a select tag with the current contact list and sets up the change and click events for the controls on the page.
<script src="jquery.js" type="text/javascript"></script>
<script src="json2.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
var contacts
$(document).ready(function () {
getContacts();
$('#cboContacts').change(function () {
$('select option[value=""]').remove();
var contactid = $("#cboContacts").val()
$.each(contacts, function () {
if (this.ContactID == contactid) {
$("#txtFirstName_u").val(this.FirstName);
$("#txtLastName_u").val(this.LastName);
$("#txtCompany_u").val(this.Company);
return;
}
});
});
$("#btnUpdate").click(function () {
if ($("#cboContacts").val() != "") {
var record = {};
record.ContactID = $("#cboContacts").val();
record.FirstName = $("#txtFirstName_u").val();
record.LastName = $("#txtLastName_u").val();
record.Company = $("#txtCompany_u").val();
updateContact(record);
}
});
$("#btnInsert").click(function () {
var record = {};
record.FirstName = $("#txtFirstName_i").val();
record.LastName = $("#txtLastName_i").val();
record.Company = $("#txtCompany_i").val();
insertContact(record);
});
$("#btnDelete").click(function () {
if ($("#cboContacts").val() != null)
deleteContact();
});
});
function getContacts() {
var url = location.pathname + "/GetContacts"
getJSON(url, null, function (response)
{ contacts = response; populateContacts(); })
}
function populateContacts() {
var options = $("#cboContacts");
options.empty();
options.append($("<option />").val("").text(""));
$.each(contacts, function () {
options.append($("<option />")
.val(this.ContactID)
.text(this.LastName + ", " + this.FirstName));
});
}
function insertContact(record) {
var contactid = $("#cboContacts").val()
var url = location.pathname + "/UpdateRecord"
var data = JSON.stringify({ "record": record });
getJSON(url, data,
function (response) {
contacts = response;
populateContacts();
$("#txtFirstName_i").val("");
$("#txtLastName_i").val("");
$("#txtCompany_i").val("");
if (contactid != "")
$("#cboContacts").val(contactid).change();
})
}
function updateContact(record) {
var contactid = $("#cboContacts").val()
var url = location.pathname + "/UpdateRecord"
var data = JSON.stringify({ "record": record });
getJSON(url, data,
function (response) {
contacts = response;
populateContacts();
if (contactid != "")
$("#cboContacts").val(contactid).change();
alert("Data Saved");
})
}
function deleteContact() {
var url = location.pathname + "/DeleteContact"
var data = JSON.stringify({ "ContactID": $("#cboContacts").val() });
getJSON(url, data,
function (response) {
contacts = response;
populateContacts();
$("#cboContacts").val("");
$("#txtFirstName_u").val("");
$("#txtLastName_u").val("");
$("#txtCompany_u").val("");
})
}
function getJSON(url, data, success) {
$.ajax({
type: "POST",
url: url,
data: data,
contentType: "application/json; charset=utf-8",
datatype: "json",
success: function (response) {
success(eval(response.d));
},
error: function (response) {
alert(response.responseText);
}
});
}
</script>
