[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I show SQL results on a website via AJAX and jQuery?

Posted on 2010-03-26
7
Medium Priority
?
323 Views
Last Modified: 2012-08-13
I'd like to use my usual SQL stored procs to return a DataSet to a .NET application and manipulate the results on the front end using jQuery's AJAX features.

In plain language terms, I want multiple areas on an ASPX page performing CRUD functions asynchronously.

Okay so a couple of prerequisites:

I'm using C# on the server, not VB.
I don't want to use AJAX controls (specifically UpdatePanel).
I don't care if I use XML or JSON to result the data to jQuery (though I'm more familiar with XML).
I can't write to/create files - everything MUST be done in memory.
I need the data to be returned from public methods in either a web service, C# class or other similar file type that can contain many methods.

An extra bonus would be if I could use my existing business objects, an example of one would be a simple Person class.

Person
FirstName - string
LastName - string
Age - int

Any direction on this would be greatly appreciated!

Thanks!
0
Comment
Question by:Rosenet
  • 4
  • 3
7 Comments
 
LVL 1

Expert Comment

by:skaleem1
ID: 28698923
Add a generic handler class to your project and make ajax request calls to this class to return the data. You can pass the query string parameters to this class to control how and what data you want to retrieve asynchronously
0
 
LVL 2

Author Comment

by:Rosenet
ID: 28699517
I'm not experienced with handlers, could you please be a bit more specific?


0
 
LVL 1

Expert Comment

by:skaleem1
ID: 28701991
Right click your project in Visual Studio, slect Add-select new item, select web in the left pane of the left pane of the add new item winsdow and in the right hand side window select generic handler-Give the file a desired name and click on Add

You have already added the generic handler. Use this as a business class to return results to the ajax calls you would be calling from the aspx page through javascript code
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 2

Author Comment

by:Rosenet
ID: 28703709
Okay I've managed to set up a handler to retrieve query string params and return results in XML.
Is there an industry standard for how to allow it to call multiple methods?

So if I had different objects and different methods for those objects like so:

Customer object
-GetCustomerByID(int ID)
-GetCustomerList()

City object
-GetCityByID(int ID)
-GetCityList()

How do I let the same handler manage all of those?  Or is that not possible?

0
 
LVL 1

Accepted Solution

by:
skaleem1 earned 1500 total points
ID: 28711752
As an example if you want to call two methods GetCustomerList and GetCityList while the aspx page is being loaded, you can add the following in the javascript (see the code section). In the pageLoad method you call the main method TriggerAjaxCall() which subsequently calls the getData() method. Assume that AjaxDBConnectHandler.ashx is the name of the handler you have added to your project, the url in the getData method is passing DataReturned="GetCustomerList" as query string parameter. In the handler, based on this query string parameter, you can programmatically connect to the database, get the data and the data will be returned to the ajax response object asynchronously where you can utilize it.


<script type="text/javascript">
    
     //Gets the browser specific XmlHttpRequest Object
        function getXmlHttpRequestObject() 
        {	
            if (window.XMLHttpRequest) 
            {		
                return new XMLHttpRequest(); //Not IE	
            } 
            else if(window.ActiveXObject) 
            {		
                return new ActiveXObject("Microsoft.XMLHTTP"); //IE	
            } 
            else 
            {		
                //Display your error message here and inform the user they might want to upgrade their browser.		
                alert("Your browser doesn't support the XmlHttpRequest object.  Better upgrade your browser.");	
            }
         }
         
         //Get our browser specific XmlHttpRequest object.
         var receiveReq;
         //alert(receiveReq);
         var DataReturned;
         var AjaxSecondCallCompleted="False";
         var response;
         var HiddenIsMyPortFolio="False";
         
        function pageLoad(sender,args) 
        { 
                TriggerAjaxCall();
            
        }

 function TriggerAjaxCall() 
        {            
            if(AjaxSecondCallCompleted=="True")
                    AjaxSecondCallCompleted="False"
            DataReturned="GetCustomerList";
            getData();
        }

function getData() 
         {
         receiveReq = getXmlHttpRequestObject();
            switch(DataReturned)
            {
            case "GetCustomerList":                     
                var url = "AjaxDBConnectHandler.ashx?DataReturned=" + escape(DataReturned)  + "&rnd=" + Math.random();                
                break;
            case "GetCityList":
                var url = "AjaxDBConnectHandler.ashx?DataReturned=" + escape(DataReturned)  + "&rnd=" + Math.random();                
                break;
            default:
                //code to be executed if DataReturned is different from case 1 and 2
            }  
            
            
             receiveReq.abort();
             receiveReq.open("GET", url, true);
             
             receiveReq.onreadystatechange = updatePage;
             receiveReq.send(null);
         }

function updatePage() 
       {
         if (receiveReq.readyState == 4) 
         {
           if (receiveReq.status == 200) 
           {
            switch(DataReturned)
                {
                case "GetCustomerList":
                    //do your processing here
                    break;
                case "GetCityList":
                    //do your processing here

                    break;
                default:
                    //code to be executed if DataReturned is different from case 1 and 2
                }
                
                response = receiveReq.responseText;
                
               //do your processing here
                
                
                if(AjaxSecondCallCompleted=="False")
                {
                    AjaxSecondCallCompleted="True"
                    receiveReq = getXmlHttpRequestObject();
                    DataReturned="GetCityList";
                    getPortfolioParts();                    
                }            
           } 
          else
             alert("status is " + receiveReq.status);
         }
       }

Open in new window

0
 
LVL 2

Author Comment

by:Rosenet
ID: 28712029
Okay the short answer was to just use a case statement.

I guess the more detailed problem would be how to send dynamic parameter sets, but I will accept your responses as the solution as it did solve my initial question.

Thanks for your help!!
0
 
LVL 2

Author Closing Comment

by:Rosenet
ID: 31707600
The solution took a bit of digging as the answers were initially somewhat vague.

Also I'm not sure if this is an industry accepted solution or just ad-hoc as only one person responded.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question