Example of Ajax data loaded via .net WebMetho

lyleja · August 2020

Hi there,

Are any of you aware of a good example of how to use a c# web method to load data into a DataTables table via an Ajax call?

Specifically I'm interesting in see the syntax used in "ajax:" callback function and how the .net WebMethod itself is set up to return a Json response.

Or asking the same question in a slightly different way ... does anyone fancy completing the missing sections in the template below?

Many thanks,

John

>

Filename: GetTableData.aspx

<!DOCTYPE html>

<%@ Page Language="C#"  %>

<%@ Import Namespace="System.Web.Services" %>

<html>

    <head>
        
        <!-- Sever Side scripting -->
        <script runat="server">
                
                    [WebMethod]
            public static string GetTableData()
            {
                string strJson;
                /*              
                    get data,   format data,    return formatted data
                                        could return a hand crafted string for now, just to test.
                */
                
                return strJson;
            }
            
        </script>
        
    </head>

    <body>
    
        <h2>Load data table with objects.</h2>
        <table id="example">
            <thead>
                <tr>
                    <th>FirstColumn</th>
                    <th>SecondColumn</th>    
                </tr>
            </thead>
           
        </table>
            
        
        <!-- Client side scripting: script libraries for jQuery and DataTables -->
        <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
    
        <script type="text/javascript">
        
            $('#example').DataTable( {
                ajax: {
                    url: "GetTableData.aspx/GetTableData",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                },
                columns: [
                    { data: "FirstColumn" },
                    { data: "SecondColumn" }
                ]
                
            } );
             
    
            
        </script>
    </body>

</html>

lyleja · August 2020

It is probably poor form to answer one's own question, so I apologise for that. But having spent a fair while "failing" I have now managed to load my table successfully via AJAX and thought it might help others to see the whole example. I'm aware that I am by no means an expert so whilst I am sure the code below works, I'm also sure there are better ways of doing things, so feel free to offer improvements.

To keep it simple I have put both the server side processing (the c# bit) and the client side JavaScript in the same file, obviously you could separate them out if you wanted.

GetTableData.aspx

<!DOCTYPE html>

<%@ Page Language="C#"  %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Services" %>

<html>

    <head>
        <meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
        <title>Datatables with WEB Method</title>
        
        <!-- Sever Side scripting -->
        <script runat="server">
           
    
            [WebMethod]
            public static string GetTableData()
            {
                /* Gather Data from database and build it into a JSON formatted string  */
                
                StringBuilder strJson = new StringBuilder("{ \"data\":  [ ");
                
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SvcDesign_ConnectionString"].ConnectionString);
                using (conn) {
                    string strComma = "";
                    string sql = "SELECT ActionID, Title, ActionOwnerID, DueBy FROM dbo.tAction";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    using (cmd) {
                        conn.Open();
                        SqlDataReader rdr = cmd.ExecuteReader();
                        using (rdr) {
                        
                            while (rdr.Read()) {
                                strJson.Append(strComma);
                                if (strComma == "") strComma=", ";
                                strJson.Append("{");
                                strJson.AppendFormat("  \"ID\": \"{0}\" ", rdr["ActionID"]);
                                strJson.AppendFormat(", \"Title\": \"{0}\" ", rdr["Title"]);
                                strJson.AppendFormat(", \"ActionOwnerID\": \"{0}\" ", rdr["ActionOwnerID"]);
                                strJson.Append("}");
                                                            
                            }
                            
                        } // end using rdr
                    
                        conn.Close();
                    } // end using cmd

                } // end using conn
                strJson.Append("] }");

                return strJson.ToString();
            }
            
        </script>       
            
        <!-- Datatables Stylesheet -->
        <link rel="stylesheet" type="text/css" media="all" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css" />
    
    </head>

    <body>
    
        <h2>Load data table with objects.</h2>
        <table id="example" class="display" style="width:100%">
            <thead>
                <tr>
                    <th>Action ID</th>
                    <th>Title</th> 
                    <th>Owner ID</th>    
                </tr>
            </thead>
            
        </table>
                
        <!-- Client side scripting: script libraries for jQuery and DataTables -->
        <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
    
        <script type="text/javascript">
        
            $(document).ready(function() {

                $('#example').DataTable( {
                    "ajax": {
                        url: "GetTableData.aspx/GetTableData",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        dataSrc: function (response) { return JSON.parse(response.d).data; },
                        error: function (response) { alert("error: " + response.d); }                       
                      }
                    ,
                    "columns": [
                        { "data": "ID" },
                        { "data": "Title" },
                        { "data": "ActionOwnerID" }

                    ]
                } );
             
            
            } );
            
            
        </script>
    </body>

</html>

allan · August 2020

No - its great that you posted back with the solution you found - thanks!

The one thing I would suggest is not attempting to build the JSON yourself though - use a library such as JSON.NET to create the JSON for you from an object. Then you don't need to worry about commas, quoting and the rest of it.

Allan

Example of Ajax data loaded via .net WebMetho

Example of Ajax data loaded via .net WebMetho

Answers

Howdy, Stranger!

Categories

DataTables

Example of Ajax data loaded via .net WebMetho

Example of Ajax data loaded via .net WebMetho

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables