Easy 8 Step HTML To SQL Data Transfer in a ASP.NET Web Form using jQuery, JSON, XML, Web Methods, node() Methods and Extension Methods.

• Download source - 39.7 KB

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
    
    --Create a temp table to store the xml data that was passed    
    DECLARE	@Temp TABLE ([ContactID] BIGINT, [FirstName] VARCHAR(50), 
                        [LastName] VARCHAR(50), [Company] VARCHAR(50))   
    --Insert the data from the xml variable into the temp table using nodes() method.
    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 the ContactID is null then it is new id and we need to insert it
    IF EXISTS ( SELECT * FROM @Temp	WHERE [ContactID] IS NULL ) 
        BEGIN
            INSERT	INTO dbo.[_Contacts] ( FirstName, LastName, Company )
            SELECT	FirstName, LastName, Company
            FROM	@Temp 
        END
    --If the ContactID is not null then if the id exist the update any columns 
    --that are not null in the temp table. I do this so you can pass only the parameters 
    --that need to be updated.
    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    

    --Return the new updated table
    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>