DataTable populated from SQL Server database

Hi Experts,
I found this DataTable example with a hard coded table.
I was wondering if it is possible to populate it from a SQL Server database instead.
If yes, could you please let me know how to modify the code?

<!DOCTYPE html>
<html>
<head>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
        <script type="text/javascript" charset="utf8" src="http://code.jquery.com/jquery-1.12.4.js"></script>
        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready(function() {
    $('#example').DataTable( {
        "lengthMenu": [[5, 10, 25, 50, -1], [5, 10, 25, 50, "All"]]
    } );
} );
</script>
</head>

<body>

<table id="example" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </tfoot>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>

        </tbody>
    </table>

</body>
</html>

Open in new window

romsomIT DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
datatable supports the loading of json data. hence, you probably can generate it via backend scripts and load it to datatable via jquery scripts
0
Mark WillsTopic AdvisorCommented:
Well, there are things that you can do from SQL Server as far as data is concerned, even presenting as XML:

Have a read of : https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server

And barely touching the edges, a couple of very simple examples
-- step 1 - create some test data to exemplify the XML options / samples
 
create table #my_xml_sample (id int, lname varchar(20), name varchar(20), address1 varchar(20))
insert #my_xml_sample values (1,'eyes','angel','11 the street')
insert #my_xml_sample values (2,'mark','aki','22 the street')
insert #my_xml_sample values (3,'pen','jim','22 the street')
insert #my_xml_sample values (4,'wills','mark','44 the street')
 
 
-- sample 1 - the simple query
 
select lname, id, name, address1 from #my_xml_sample for xml path('Contacts'), root('ContactMaster')
GO
 
-- sample 2 - the simple query but with lname as header tag
 
select lname as "@lname", id , name , address1 from #my_xml_sample for xml path('Contacts'), root('ContactMaster')
GO
 
-- sample 3 - inline group selects
 
select lname as "@LastName", id as "@ID", name as "@Name"
from #my_xml_sample g for xml path('contacts'), root('ContactMaster'), type, elements
GO
 
-- sample 4 - sub group selects
 
select lname as "LastName",
       (select id as "@ID", name as "@NAME", address1 as "@ADDRESS" from #my_xml_sample s where s.lname = g.lname FOR XML PATH ('Individuals'), type, elements)
from #my_xml_sample g group by lname for xml path('Contacts'), root('ContactMaster')
GO
 


select lname as "@LastName",
       (select id as "@ID", name as "@NAME", address1 as "@ADDRESS" from #my_xml_sample s where s.lname = g.lname FOR XML PATH ('Individuals'), type, elements)
from #my_xml_sample g group by lname for xml path('Contacts'), root('ContactMaster')
GO


-- sample 5 - using explicit for tree type XML
 
SELECT 1 as Tag,                                        -- this first one sets up structure only
       NULL as Parent,
       NULL as [ContactMaster!1!Contacts!element],
       NULL as [Contacts!2!Lname!element],
       NULL as [Individuals!3!ID], --!element],
       NULL as [Individuals!3!Name], --!element],
       NULL as [Individuals!3!Address] --!element]
            
UNION ALL
Select distinct 2,1, lname, lname,NULL,NULL,NULL from #my_xml_sample
 
UNION ALL
Select distinct 3,2, lname, lname,id,name,address1 from #my_xml_sample
 
order by 3,4,5
       
FOR XML Explicit
 
GO

Open in new window

For your requirements, I reckon it could be constructed, but would be a little fiddley to begin with. Probably best to use tools such as https://datatables.net/ 

But, it isnt just the data, it is also how users interact. So, inclined to agree with Ryan.
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
romsomIT DeveloperAuthor Commented:
Thank you, I've done some further reading. Your comments were very useful.
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
Microsoft SQL Server

From novice to tech pro — start learning today.