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

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!
LVL 2
RosenetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skaleem1Commented:
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
RosenetAuthor Commented:
I'm not experienced with handlers, could you please be a bit more specific?


0
skaleem1Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

RosenetAuthor Commented:
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
skaleem1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RosenetAuthor Commented:
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
RosenetAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Applications

From novice to tech pro — start learning today.