January 30, 2011 
Lets use WebMatrix to create a very basic db – > ui sample using jQuery goodness. We’ll get from 0 to a working sample in five minutes (more or less).
1. The WebMatrix site
First create a new site in WebMatrix using the Empty site template.
2. Database
Next define a Sql CE database file, call it db and add a table named product:
Also add some demo data in the table, so you have something to show later on.
3. Json
Now it’s time to create a file that will serve a list of records from the database using json. For that reason, go create products.cshtml with this simple code:
@{
var db = Database.Open("db");
var sql = @"SELECT * FROM product";
var data = db.Query(sql);
Json.Write(data, Response.Output);
}
So now we have data and some code to read it. So try it out by running products.cshtml. You should see a json representation of all your products in your browser window:
[{"id":1,"name":"a product"},{"id":2,"name":"another one"},{"id":3,"name":"a third"}]
4. The master template with references to js libraries
We are going to use some jQuery libraries in this sample. The jQuery base, jQuery UI for some nice looking UI and jQuery templates to render json data to html in a very conveniant template fashion.
All the libraries are available online, and there’s no need to use local copies at this point.
So, create a _layout.cshtml with the following content:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/ui-lightness/jquery-ui.css" type="text/css" media="all" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js" type="text/javascript"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js" type="text/javascript"></script>
<script src="http://ajax.microsoft.com/ajax/jquery.templates/beta1/jquery.tmpl.js" type="text/javascript"></script>
</head>
<body>
@RenderBody()
</body>
</html>
5. The main page
The last file in our WebMatrix project is the index.cshtml which only includes a jQuery UI styled button and a div placeholder for the result to begin with. On the click of that button we make a ajax call to get the data from our table, and render it to Html.
Here’s the complete code:
@{
Layout="~/_Layout.cshtml";
}
<script>
function renderResult(data)
{
// define the jQuery template with html code and
// proper property names from the data query result
var markup = "<li>${id} : ${name}</li>";
$.template("listTemplate", markup);
// render the template using the json data
// iterates automatically over each each item in data
$.tmpl("listTemplate", data ).appendTo( "#result" );
}
$(function() {
// create good looking jQuery UI button element
$( ".button" ).button();
// hook a getJSON to the click event of the button
$( "#getdata" ).click(function() {
$.getJSON('products.cshtml',renderResult);
});
});
</script>
<div id="getdata" class="button">Get data</div>
<ul id="result"></ul>
Here’s what your complete project in WebMatrix should look like, only three cshtml files and one database:
Result
Conclusion
Simple as that! Fun eh? And really useful I’d say.
Supporting non-js browsers when developing webapps sometimes feels like keeping support for Windows 3.11 when developing Windows apps.
You use “Database.Open(…)”, in which assembly or namespace can you find this?
Hi Damiaan, WebMatrix.Data.dll
I am using this code to get data into a table; how do I clear the table when I want to put new data in it?
OK, found the syntax to clear the table: $(‘#table’).empty();. Now, having a heck of a time trying to put in column headers each time the button is clicked.
Hi, you could use a <tbody> and append data there, and use thead tr th … for the headers. http://www.w3schools.com/tags/tag_thead.asp
HTH
Hola…
If I add the new record in the file json (database.cfg)..How?
greatings.
i have a ftp host but dont have any php sql suport
my idea is to use javascript for reading a kind of database
so…
my idea is,
zip file and inside csv tables
[database.zip]
|–table1.csv
|–table2.csv
|–table3.csv
|–table4.csv
what needs is:
- js zip reading
- read file csv
- query csv tables
- show results
protection zip = password md5
im still working on this but need some help
cheers