Returning JSON data from multiple tables

  • Saturday, July 12, 2014 2:25 PM EST
  • Last Edited: Saturday, July 19, 2014 10:45 PM EST

I have come upon this situation where I need to pull a lot of data from multiple table sources to show information. These tables do not have to be related to each other. I cannot imagine how slow this page would be because I had to do all these database calls just to show little information on certain areas of a webpage. For example, for a student data page, I'd need to go through the main table for a student's personal data like address, contact numbers, student id, enrollment date etc. Then another database table to show announcements. Another would be to show upcoming school events. All these information in a single web page would require multiple database calls to pull information.

My approach was to select the areas that doesn't need to be shown right away (like areas at the very bottom of a page) so there won't be an obvious loading time while using jquery and an ajax call to retrieve then insert the html elements from the server. I use a simple union all query in a stored procedure to go through all the tables. This way, I combine 3 (or more) database calls to a single call and utilize the client's resources to draw up the html.

1. The first thing you need to do is to go through the tables that you want to combine and return in a single database call. Use union all to combine the results. Create a table name to identify the result set and where it is coming from. Then, format the columns to json data using string concatenation.

CREATE PROCEDURE GetOtherStudentDetails

      @StudentID int

AS

BEGIN

      SELECT TableName, Data FROM

      (

             SELECT 'StudentCourses' AS TableName, '{"CourseName":"'+CourseName+'","CourseCode":"'+CourseCode+'"}' AS Data

                     FROM StudentCourses

                     WHERE StudentID = @StudentID

             UNION ALL

             SELECT 'AvailablePrograms' AS TableName, '{"ProgramID":"'+ProgramID+'","ProgramName":"'+ProgramName+'","ProgramDescription":"'+ProgramDescription+'"}'

                     FROM Programs

             UNION ALL

             SELECT 'Announcements' AS TableName, '{"AnnouncementID":"'+AnnouncementID+'","Announcement":"'+Announcement+'"}'

                     FROM Announcements

                     WHERE AnnouncementsApproved = 1

 

             /**add more tables here**/

      )tblCombine

      WHERE Data IS NOT NULL

      ORDER BY TableName

END

 

2. Create a web service to call the stored procedure and consume using ajax.

3. Using ajax and jquery loop through the result set and check each time what type of data it is. Create a variable to hold the html. When done, append the html to its appropriate container.

                         

var coursesHtml = "";

var announcementsHtml = "";

var prgmsHtml = "";

                       

$.ajax({

   type: "POST",

      data: //pass  data to service if needed,

   url: "/Services.asmx/GetOtherDetails",

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

   dataType: "json",

   success: function (response) {

   //go  through each "table"

       $.each(response.d, function (key, detail) {

           //parse  string to json data

           var jsonData = $.parseJSON(detail.Data);

 

           if(detail.TableName == "StudentCourses"){

               coursesHtml += "<h3>"+jsonData.CourseName+"</h3><p>"+jsonData.CourseCode+"</p>";

           }

           //add  the other html check

       });

             //when  done append to each html containers                          

       if(coursesHtml != ""){

           $("#courses  div").append(coursesHtml);

           $("#courses").show();

       }else{

           //what  to do when empty

           $("#courses").hide();

       }

 

       //add  other if statements for other elements

   }

});

                   

 

             

comments powered by Disqus