Yesterday I posted the first in a series on using WebMatrix to build web sites. The first post created a basic contact form and collected the submitted values. Now I am going to add a database and a table to store the site's contact form submissions.
First, select the Databases node in the group navigation on the left-had side of WebMatrix. This is the accordion style tool if you are wondering, the Databases node should be close the bottom, just above the Reports node. After you have done this the main surface area in WebMatrix displays the following 'button'.
Clicking this allows you to add a database. Caution there is no setup dialog to get in the way, the database is just simply added to the site. You can also add a new database by clicking the new database button in the ribbon bar. You can also add a connection to an existing database and add a new table to a database. The database will be named after your site, for my demo it is called Simple Contact, after the site. If you add another database it will be called Simple Contact1. Be careful adding databases to your site, I have not found a way to remove them after you add them.
Now add a new table to the database called Contact. You can either right-click on the Tables node under the database or click the button in the ribbon bar.
Now you need to add columns to the table. To do this you need to right click on the table and select Definition in the context menu. Adding columns to the table is a little different experience than using SQL Management Studio. Shown below you see the column names, but you cannot edit the values here. You need to select each column and in a lower pane you can set the properties of the column; Name, Allow Nulls, Data Type, Default Value, Is Primary Key and Length. Remember this is SQL CE, not full SQL Server so you do not get all the bells and whistles, which is not a bad thing.
Inserting a new record is pretty simple. You just need to add a few lines of code to the existing page. Now let me insert a quick disclaimer, I DO NOT LIKE INLINE SQL STATEMENTS anywhere in an application. But for now we are going to run with it here. I have plans on showing how to leverage good architecture later.
But for now lets follow along with the get it done strategy. The WebMatrix team has made communicating to the database really simple by giving us a Database object. This object has several public members to Open a database and Execute a SQL statement. To open the database just call Open and pass in the name of the database, nothing else and you now have a connection to execute statements. To execute a statement, just call Execute passing in a SQL string. As you can see below you can use the @n to set a placeholder for parameters that follow the statement. Now I do not know how it deals with SQL Injection, etc. I am going to assume there are safeguards in place.
var firstName =
var lastName =
var email =
var comment =
firstName = Server.HtmlEncode(Request[
lastName = Server.HtmlEncode(Request[
email = Server.HtmlEncode(Request[
comment = Server.HtmlEncode(Request[
var db = Database.Open(
"INSERT INTO Contact (FirstName,,
LastName, email, Coment)
VALUES (@0, @1, @2, @3)"
That is pretty much it. Now when you fill in the form and submit it a new record is added to the Contact table.
As you can see the Webmatrix experience makes working with databases very easy. Again I do warn I am not a fan of the inline SQL, but remember this is not a true professional development tool. It is aimed at simple web development needs.
In my next installment we will add automatic E-mail capabilities to the site. From there I will add validation, master pages and other features that are important to have a fully functional web site.