Click here to Skip to main content
Click here to Skip to main content

Database CRUD Operations In Business Sites With WCF REST, Web Services As Provider And JQuery, Javascript As Consumer : Zero Postback

, 23 Sep 2011
Rate this:
Please Sign up or sign in to vote.
This article explains how to perform CRUD operations in a web application with WCF REST services and ASMX services as provider and jQuery or javascript as consumer, eliminating postbacks totally

Introduction

This article shows how to perform database CRUD (create, retrieve, update, delete) operations from web application with jQuery, javascript as client and WCF RESTful services, ASP.NET Web Services as service provider, all with zero postback. This article helps up learn advantages of RESTful APIs, power of jQuery, meticulous use of javascript and script services.This article also shows how to achieve all business functionalities without even a single postback.

Background

One has to read my earlier article at ZeroPostbackSite.aspx in order to get the grasp of what is happening over here. In my earlier aforementioned article I have explained how to call ASP.NET web services from jQuery and subsequently perform UI manipulations. Here I shall go in detail how to perform insert, update, delete operations using WCF RESTful services and how to consume them from .NET code and jQuery code. Happy reading...

Using the code

Without much ado about anything I shall directly jump to explaining the code. First of all we need to create the RESTful WCF services. In this regard, many sites tell you about many ways and they all successfully confuse you to the extent that you feel like taking "rest" after reading REST APIs. Here I am going to eliminate all those pains and show you the foolproof way how the things work. Your existing WCF services could be exposed as REST APIs or RESTful WCF services by doing three things:

a) Decorate your operations with [WebInvoke] attributes, I shall be showing how.

b) Have B = webHttpBinding for ABC of your WCF service.

c) Have <webHttp/> element declared inside your endpoint behavior configuration.

Lets now present the interface (contract):

     
[ServiceContract(Namespace="StudentContract", SessionMode=SessionMode.Allowed)]

public interface IStudent

{

    [OperationContract]

    [WebInvoke(

        Method = "GET",

        RequestFormat = WebMessageFormat.Json,

        ResponseFormat = WebMessageFormat.Json,

        BodyStyle = WebMessageBodyStyle.Wrapped

        )

    ]

    List<ListItem> GetStudents();

    [OperationContract]

    [WebInvoke(

        Method = "POST", 

        RequestFormat= WebMessageFormat.Json, 

        ResponseFormat=WebMessageFormat.Json,

        BodyStyle=WebMessageBodyStyle.Wrapped

        )

    ]

    int InsertStudent(StudentRecord stuRec);

    [OperationContract]

    [WebInvoke(

        Method = "POST",

        RequestFormat = WebMessageFormat.Json,

        ResponseFormat = WebMessageFormat.Json,

        BodyStyle = WebMessageBodyStyle.Wrapped

        )

    ]

    void InsertMark(StudentMark stuMark);

    [OperationContract]

    [WebInvoke(

        Method = "POST",

        RequestFormat = WebMessageFormat.Json,

        ResponseFormat = WebMessageFormat.Json,

        BodyStyle = WebMessageBodyStyle.Wrapped

        )

    ]

    void UpdateStudent(StudentRecord stuRec);

    [OperationContract]

    [WebInvoke(

        Method = "POST",

        RequestFormat = WebMessageFormat.Json,

        ResponseFormat = WebMessageFormat.Json,

        BodyStyle = WebMessageBodyStyle.Wrapped

        )

    ]

    void DeleteStudent(StudentRecord stuRec);

    [OperationContract]

    [WebInvoke(

        Method = "POST",

        RequestFormat = WebMessageFormat.Json,

        ResponseFormat = WebMessageFormat.Json,

        BodyStyle = WebMessageBodyStyle.Wrapped

        )

    ]

    void UpdateMarks(StudentMark stuMark);

}
 

You see how the POST method is specified, request and response formats as Json are specified, and a wrapped body style specified. If you transfer binary data like images, the body style should be bare. Now see the implementation of the above contract:

public class StudentBase

{

    public List<ListItem> GetStudents()

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        comm.CommandText = "SELECT RollNo, Name From dbo.StuRec";

        comm.Connection = conn;

        SqlDataAdapter da = new SqlDataAdapter(comm);

        DataSet ds = new DataSet();

        conn.Open();

        da.Fill(ds, "StudentRecord");

        conn.Close();

        List<ListItem> stuList = new List<ListItem>();

        if (ds.Tables != null)

        {

            int recordCount = ds.Tables["StudentRecord"].Rows.Count;

            if (recordCount > 0)

            {

                DataTable dtab = ds.Tables["StudentRecord"];

                for (int i = 0; i < recordCount; i++)

                {

                    stuList.Add(new ListItem(

                        dtab.Rows[i]["Name"].ToString(),

                        dtab.Rows[i]["RollNo"].ToString()

                    ));

                }

            }

        }

        comm.Dispose();

        return stuList;

    }

    public int InsertStudent(StudentRecord stuRec)

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        string name = stuRec.Name;

        comm.CommandText = "INSERT INTO dbo.StuRec(Name) VALUES (@stuName); SELECT SCOPE_IDENTITY()";

        comm.Parameters.Add(new SqlParameter("@stuName", SqlDbType.VarChar)).Value = name;

        comm.Connection = conn;

        conn.Open();

        object roll = comm.ExecuteScalar();

        conn.Close();

        comm.Dispose();

        return int.Parse(roll.ToString());

    }

    public void UpdateMarks(StudentMark stuMark)

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        int roll = stuMark.RollNo;

        int mark = stuMark.Mark;

        comm.CommandText = "UPDATE dbo.StuMark SET Marks = @mark WHERE RollNo = @stuRoll";

        comm.Parameters.Add(new SqlParameter("@mark", SqlDbType.Int)).Value = mark;

        comm.Parameters.Add(new SqlParameter("@stuRoll", SqlDbType.Int)).Value = roll;

        comm.Connection = conn;

        conn.Open();

        comm.ExecuteNonQuery();

        conn.Close();

        comm.Dispose();

    }

    public void UpdateStudent(StudentRecord stuRec)

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        int roll = stuRec.RollNo;

        string name = stuRec.Name;

        comm.CommandText = "UPDATE dbo.StuRec SET Name = @stuName WHERE RollNo = @stuRoll";

        comm.Parameters.Add(new SqlParameter("@stuName", SqlDbType.VarChar)).Value = name;

        comm.Parameters.Add(new SqlParameter("@stuRoll", SqlDbType.Int)).Value = roll;

        comm.Connection = conn;

        conn.Open();

        comm.ExecuteNonQuery();

        conn.Close();

        comm.Dispose();

    }

    public void InsertMark(StudentMark stuMark)

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        int roll = stuMark.RollNo;

        int mark = stuMark.Mark;

        comm.CommandText = "INSERT INTO dbo.StuMark(RollNo,Marks) VALUES (@stuRoll, @stuMarks)";

        comm.Parameters.Add(new SqlParameter("@stuRoll", SqlDbType.Int)).Value = roll;

        comm.Parameters.Add(new SqlParameter("@stuMarks", SqlDbType.Int)).Value = mark;

        comm.Connection = conn;

        conn.Open();

        comm.ExecuteNonQuery();

        conn.Close();

        comm.Dispose();

    }

    public void DeleteStudent(StudentRecord stuRec)

    {

        string connStr = ConfigurationManager.ConnectionStrings["KovairSiteConnectionString"].ToString();

        SqlConnection conn = new SqlConnection(connStr);

        SqlCommand comm = new SqlCommand();

        int roll = stuRec.RollNo;

        comm.CommandText = "DELETE FROM dbo.StuMark WHERE RollNo = @rollNo";

        comm.Parameters.Add(new SqlParameter("@rollNo", SqlDbType.Int)).Value = roll;

        comm.Connection = conn;

        conn.Open();

        comm.ExecuteNonQuery();

        comm.CommandText = "DELETE FROM dbo.StuRec WHERE RollNo = @rollNo";

        comm.ExecuteNonQuery();

        conn.Close();

        comm.Dispose();

    }

}

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

public class Student : StudentBase, IStudent

{

}
   

See the trick I have used above. I have first implemented all the methods inside StudentBase class and then inherited Student from StudentBase and IStudent. Also notice that I have enabled session in the contract and AspNetCompatibilityRequirements for the service class Student.

Now modify the .svc file accordingly:

<%@ ServiceHost 
  Language="C#" 
  Debug="true" 
  Service="StudentService.Student" 
  CodeBehind="StudentService.svc.cs" 
  
%>

See that I have mentioned the fully qualified name of my class in Service attribute. Now lets see the important part of our web.config file.

<system.serviceModel>

    <services>

        <service name="StudentService.Student" behaviorConfiguration="StudentService.ServiceStudentBehavior">

        <!-- Service Endpoints -->

            <endpoint address="http://localhost/StudentService/StudentService.svc" binding="webHttpBinding" contract="Kovair.Interface.IStudent" behaviorConfiguration="WebHttpBehavior"/>

            <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>

        </service>

    </services>

    <behaviors>

        <serviceBehaviors>

            <behavior name="StudentService.ServiceStudentBehavior">

<!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->

                <serviceMetadata httpGetEnabled="true"/>

<!-- To receive exception details in faults for debugging purposes, set the value below to true. Set to false before deployment to avoid disclosing exception information -->

                <serviceDebug includeExceptionDetailInFaults="true"/>

            </behavior>

        </serviceBehaviors>

        <endpointBehaviors>

            <behavior name="WebHttpBehavior">

                <webHttp/>

            </behavior>

        </endpointBehaviors>

    </behaviors>

    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />

</system.serviceModel>

See that for the binding I have specified webHttpBinding and for behavior I have specified <webHttp/> element for the endpoint. Also I have enabled compatibility with ASP.NET such that I can leverage usage of HttpContext.Current.Session if I want. WCF services, however, has ServiceContext too.

Now lets come to the client part, which is again a web application in my case. Look jow I have added reference to my WCF RESTful service inside <ScriptManager> tag in the .master file.

<asp:ScriptManager ID="KovairScriptManager" runat="server" EnableScriptGlobalization="true"

    EnablePageMethods="true">

    <Services>

        <asp:ServiceReference Path="http://localhost/StudentService/StudentService.svc" />

    </Services>

</asp:ScriptManager>

Here I have added asp:ServiceReference element in order for the scripts to be "aware" of the RESTful services. Now, the GetStudents() operation of the StudentService have been called both during initial page load (not postback) and during internal $.ajax calls from jQuery. The Page_Load method is shown:

protected void Page_Load(object sender, EventArgs e)

{

    if (!Page.IsPostBack)

    {

        string webUri = ConfigurationManager.AppSettings["WcfServiceUri"].ToString();

        WebChannelFactory<IStudent> wcf = new WebChannelFactory<IStudent>

            (

                new WebHttpBinding(),

                new Uri(webUri)

            );

        IStudent channel = wcf.CreateChannel();

        List<ListItem> items = channel.GetStudents();

        ListItem newItem = new ListItem();

        newItem.Value = "-1";

        newItem.Text = "Select a value";

        ddlStudents.Items.Add(newItem);

        foreach (ListItem item in items)

        {

            ddlStudents.Items.Add(item);

        }

    }

}

I have called my RESTful service without using a proxy. It is the best way. For normal WCF services we use ChannelFactory class but here you need to use WebChannelFactory class as WebChannelFactory class is aware of [WebInvoke] attribute. Also see I have used WebHttpBinding here. Now see how I am calling the services from my jQuery / javascript code. It is self explanatory, provided you have gone through my earlier article as mentioned at the beginning of this article.

<script type = "text/javascript">

$(function() {

	$('#ModDiv').hide();

	$('#<%=btnModify.ClientID%>').hide();

	$('#<%=btnDelete.ClientID%>').hide();

	});

	function GetStudentDetail() {

		$('#<%=btnModify.ClientID%>').hide();

		$('#<%=btnDelete.ClientID%>').hide();

		var control = $get('<%= this.ddlStudents.ClientID %>');

		var NewStudent = {};

		NewStudent.RollNo = 22;

		NewStudent.Name = "Jacob";

		// Create a data transfer object (DTO) with the proper structure.

		var DTO = { 'stu': NewStudent };

		var rollNo = control.options[control.selectedIndex].value;

		var name = control.options[control.selectedIndex].text;

		NewStudent.RollNo = rollNo;

		NewStudent.Name = name;

		$.ajax({

			type: 'POST',

			url: 'http://localhost/StudentService/StudentWebService.asmx/GetStudentDetail',

			data: JSON.stringify(DTO),

			contentType: 'application/json; charset=utf-8',

			dataType: 'json',

			success: function(response, status) {

			var list = (typeof response.d) == 'string' ? eval('(' + response.d + ')') : response.d;

			var oup = "RollNo: " + list.RollNo.toString() + " Marks: " + list.Mark.toString();

			$('#<%=lblResult.ClientID %>').html(oup);

			},

			failure: function(response) {

			alert(response.d);

			}

		}

	);

	$('#<%=btnModify.ClientID%>').toggle(500);

	$('#<%=btnDelete.ClientID%>').toggle(500);

	return false;

}

function GetStudents() {

	$('#<%=ddlStudents.ClientID %>').empty().append('<option selected="selected" value="0">Loading...</option>');

	$.ajax({

		type: 'GET',

		url: 'http://localhost/StudentService/StudentService.svc/GetStudents',

		data: {},

		contentType: 'application/json; charset=utf-8',

		dataType: 'json',

		success: function(response, status) {

		var list = response;

		var control = $get('<%=ddlStudents.ClientID %>');

		control.options.length = 0;

		var newOption = new Option("Please Select ", "-1");

		control.options[0] = newOption;

		var i = 1;

		for (i = 1; i <= list.GetStudentsResult.length; i++) {

			newOption = new Option(list.GetStudentsResult[i - 1].Text, list.GetStudentsResult[i - 1].Value);

			control.options[i] = newOption;

			}

		},

		failure: function(response) {

		alert(response.d);

		}

	}

	);

	return false;

}

function ShowMod(isNew) {

	$('#ModDiv').toggle(500);

	if (isNew) {

	$get('<%= this.txtName.ClientID %>').focus();

	}

	else {



		var control = $get('<%= this.ddlStudents.ClientID %>');

		var name = control.options[control.selectedIndex].text;

		var marks = $get('<%= this.lblResult.ClientID %>').outerText;

		var gotMarks = marks.substring(marks.lastIndexOf(':') + 1);

		$get('<%= this.txtName.ClientID %>').value = name;

		$get('<%= this.txtMarks.ClientID %>').value = gotMarks;

		$get('<%= this.txtName.ClientID %>').focus();

		}

		return false;

	}

function InsertMarks(roll) {

	var NewMarks = {};

	NewMarks.RollNo = roll;

	NewMarks.Mark = $get('<%= this.txtMarks.ClientID %>').value;

	var DTO2 = { 'stuMark': NewMarks };

	$.ajax({

		type: 'POST',

		url: 'http://localhost/StudentService/StudentService.svc/InsertMark',

		data: JSON.stringify(DTO2),

		contentType: 'application/json; charset=utf-8',

		dataType: 'json',

		success: function(response) {

			setTimeout("GetStudents()", 100);

		},

		failure: function(response) {

		alert(response.d);

	}

	}

	);

	$get('<%= this.txtName.ClientID %>').value = "";

	$get('<%= this.txtMarks.ClientID %>').value = "";

}

function SaveData(isNew) {

	var txtControl = $get('<%= this.txtName.ClientID %>');

	var rollNo = -99;

	var NewStudent = {};

	NewStudent.RollNo = rollNo;

	NewStudent.Name = txtControl.value;

	var DTO1 = { 'stuRec': NewStudent };

	if (isNew) {

	$.ajax({

		type: 'POST',

		url: 'http://localhost/StudentService/StudentService.svc/InsertStudent',

		data: JSON.stringify(DTO1),

		contentType: 'application/json; charset=utf-8',

		dataType: 'json',

		success: function(response) {

		rollNo = response.InsertStudentResult;

		setTimeout(function() { InsertMarks(rollNo) }, 100); 

	},

	failure: function(response) {

	alert(response.d);

	}

	}

	);

	}

	else {

		var control = $get('<%= this.ddlStudents.ClientID %>');

		rollNo = control.options[control.selectedIndex].value;

		NewStudent.RollNo = rollNo;

		// Create a data transfer object (DTO) with the proper structure.

		DTO = { 'stuRec': NewStudent };

		$.ajax({

			type: 'POST',

			url: 'http://localhost/StudentService/StudentService.svc/UpdateStudent',

			data: JSON.stringify(DTO),

			contentType: 'application/json; charset=utf-8',
			
			dataType: 'json',

			success: function(response) { UpdateMarks(); },

			failure: function(response) {

			alert(response.d);

		}

	}

	);

		$('#<%=btnModify.ClientID%>').toggle(500);

	}

	$('#ModDiv').toggle(500);

	$('#<%=lblResult.ClientID %>').html("");

	return false;

}

function UpdateMarks() {

	var control = $get('<%= this.ddlStudents.ClientID %>');

	rollNo = control.options[control.selectedIndex].value;

	var NewMark = {};

	NewMark.RollNo = rollNo;

	NewMark.Mark = $get('<%= this.txtMarks.ClientID %>').value;

	var DTO4 = { 'stuMark': NewMark };

	$.ajax({

		type: 'POST',

		url: 'http://localhost/StudentService/StudentService.svc/UpdateMarks',

		data: JSON.stringify(DTO4),

		contentType: 'application/json; charset=utf-8',

		dataType: 'json',

		success: function(response) { setTimeout("GetStudents()", 100); },

		failure: function(response) {

			alert(response.d);

		}

	}

	);

}

function DeleteStudent() {

	var rollNo = $get('<%= this.lblResult.ClientID %>').outerText;

	var gotRoll = rollNo.substring(rollNo.indexOf(':') + 1, rollNo.indexOf('M') - 1);

	var roll = parseInt(gotRoll.trim());

	var NewStudents = {};

	NewStudents.RollNo = roll;

	NewStudents.Name = "Jacob";

	// Create a data transfer object (DTO) with the proper structure.

	var DTO3 = { 'stuRec': NewStudents };

	$.ajax({

		type: 'POST',

		url: 'http://localhost/StudentService/StudentService.svc/DeleteStudent',

		data: JSON.stringify(DTO3),

		contentType: 'application/json; charset=utf-8',

		dataType: 'json',

		success: function(response) {

			setTimeout("GetStudents()", 100);

		},

		failure: function(response) {

		alert(response.d);

		}

	}

	);

	$('#<%=btnModify.ClientID%>').toggle(500);

	$('#<%=btnDelete.ClientID%>').toggle(500);

	$('#<%=lblResult.ClientID %>').html("");

	$('#<%=ddlStudents.ClientID %>').val("-1");

return false;

}

</script>

Now see that how I have meticulously used setTimeout function of javascript in between successive jQuery $.ajax calls. The $.ajax calls succeeds if it is able to "call" the WCF service, it does not have patience to wait till the service execution completes, also the service is executed in another thread and the thread which is calling returns. Hence one has to wait certain milliseconds for synchronization purpose if results of one service call would be used in the next one.

 

Points Of Interest

I have shown how to perform database CRUD operations using both RESTful services and web services decorated with [ScriptService] attribute (last article), all with zero postback. Also I have shown that the same RESTful WCF service could be called from C# code as well as from jQuery / javascript code.

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

AmbarRay
Architect Pramerica Systems Ireland
India India
CORE COMPETENCIES
 
 Design and Architecture for Microsoft SOA implementations using BizTalk, WCF, WF, SQL Server Integration Services & ESB Toolkit.
 
 Web Application design and implementation using ASP.NET MVC with jQuery & Entity Framework with LINQ for persistence layer.
 
 Designing and developing desktop applications using WPF, WinForms.
 
 SQL Server Database design & programming
 
EXPERIENCE SUMMARY
 
 Eleven years total, out of which 04 years as architect, 07 years as designer and developer in various Microsoft technologies like WinForms, ASP.NET WebForms, ASP.NET MVC, BizTalk, WCF, WF, WPF, SQL Server, LINQ, EF etc. Worked in various roles mainly architect, designer, team leader and developer.
 
 Hands on experience with ASP.NET web applications (both MVC and Web Forms) and desktop based applications as well as smart client applications using latest technologies harnessing .NET Framework 4.0, C# 4.0, ASP.NET 4.0, WCF, WF, WPF and LINQ.
 
 Hands-on working experience in Application integration, business process management and service oriented applications using BizTalk Server 2010, ESB Toolkit 2.1, WCF 4.0 services and SQL Server 2008 Integration Services.
 
 Thorough working knowledge of OOAD and agile / incremental development process.
 
 Experience in leading team of developers for on schedule project delivery with quality.
 
 Experience with low level programming like embedded C, C++ as well as systems programming on unix platform.
REMARKABLE PROFESSIONAL ACHIEVEMENTS
 
 Got Microsoft Community Contributor Award in year 2011 with MCC ID# 4034514.
 
 Published article in MSDN Magazine Feb 2011 edition on MDM with F#: http://msdn.microsoft.com/en-us/magazine/gg598923.aspx
http://www.codeproject.com/News/14767/Pattern-Matching-Database-Records-with-Fsharp.aspx
 
 Published highly popular article on BizTalk in www.dotnetcurry.com
http://www.dotnetcurry.com/ShowArticle.aspx?ID=683
 
 Umpteen blogs in BizTalk server forums and ESB toolkit forums.

Comments and Discussions

 
QuestionWhy .asmx came into picture in your code? PinmemberMember 1027912021-Nov-13 18:35 
Question[My vote of 1] this is not REST PinmemberSelvin22-Sep-11 23:29 
AnswerRe: [My vote of 1] this is not REST PinmemberAmbarRay23-Sep-11 3:30 
Thanks for your suggestion. For GetStudents method I have used GET at both the WCF service side and jQuery.ajax() call side. Unfortunately $.ajax() does not support anything more than GET or POST. I have updated the code.
GeneralRe: [My vote of 1] this is not REST PinmemberSelvin23-Sep-11 4:22 
Questionwcf or just plain webservice? PinmemberHaBiX22-Sep-11 23:25 
Questionugly code PinmemberSeishin#22-Sep-11 22:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 23 Sep 2011
Article Copyright 2011 by AmbarRay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid