Working With Data (Beginners Guide to Database in ASP.NET Web Pages Part 1)
This blog describes how to access data from a database and how to display and update it using ASP.NET Web pages. The final document can be found at this public location: Working With Data. For more information and support related to this post refer to the following:
| Link | Description |
| WebMatrix | The tool I used to build the Web site for testing the examples shown in this post. |
| WebMatrix Forum | The forum dedicated to WebMatrix. |
| Book | The book that contains Working With Data chapter. |
| Book Samples | Downloadable samples associated with the chapters in the book. |
What you'll learn:
- How to create a database.
- How to connect to a database. er
- How to display data in a web page.
- How to insert, update, and delete database records.
These are the features introduced in the chapter:
- Working with a Microsoft SQL Server Compact Edition database.
- Working with SQL queries.
- The Database class.
- The WebGrid helper.
Introduction to Databases
Imagine a typical address book. For each entry in the address book (that is, for each person) you have several pieces of information (data) such as first name, last name, address, email address, and phone number.
A typical way to picture data like this is as a table with rows and columns. In database terms, each row is often referred to as a record. Each column (sometimes referred to as fields) contains a value for each type of data: first name, last name, and so on.
ID |
FirstName |
LastName |
Address |
Phone |
|
1 |
Jim |
Abrus |
210 100th St SE Orcas WA 98031 |
555 0100 |
|
2 |
Terry |
Adams |
1234 Main St. Seattle WA 99011 |
555 0101 |
For most database tables, the table has to have a column that contains a unique identifier, like a customer number, account number, etc. This is known as the table's primary key, and you use it to identify each row in the table. In the example, the ID column is the primary key for the address book.
With this basic understanding of databases, you're ready to learn how to create a simple database and perform operations such as adding, modifying, and deleting data.
Relational Databases. You can store data in lots of ways, including text files and spreadsheets. For most business uses, though, data is stored in a relational database. This topic doesn't go very deeply into databases. However, you might find it useful to understand a little about them. In a relational database, information is logically divided into separate tables. For example, a database for a school might contain separate tables for students and for class offerings. The database software (such as SQL Server) supports powerful commands that let you dynamically establish relationships between the tables. For example, you can use the relational database to establish a logical relationship between students and classes in order to create a schedule. Storing data in separate tables reduces the complexity of the table structure and reduces the need to keep redundant data in tables.
Creating a Database
This procedure shows you how to create a database named SmallBakery by using the SQL Server Compact Database design tool that’s included in WebMatrix. Although you can create a database using code, it is more typical to create the database and database tables using a design tool like WebMatrix.
1. Start WebMatrix, and on the Quick Start page, click Site From Template.
2. Select Empty Site and in the Site Name box enter SmallBakery, and then click OK. The site is created and displayed in WebMatrix.
3. In the left pane, click the Databases workspace.
4. In the ribbon, click New Database. An empty database is created with the same name as your site.
5. In the left pane, expand the SmallBakery.sdf node and then click Tables.
6. In the ribbon, click New Table. WebMatrix opens the table designer.
7. Under Column Properties, for (Name), enter Id.
8. For the new Id column, set Is Identity and Is Primary Key to true.
As the name suggests, Is Primary Key tells the database that this will be the table's primary key. Is Identity tells the database to automatically create an ID number for every new record and to assign it the next sequential number (starting at 1).
9. In the ribbon, click New Column.
10. Under Column Properties panel, for (Name), enter Name.
11. Set Allow Nulls to false. This will enforce that the Name column is not left blank.
12. Set Data Type to nvarchar. The var part of nvarchar tells the database that the data for this column will be a string whose size might vary from record to record. (The n prefix represents national, indicating that the field can hold character data that represents any alphabet or writing system--that is, that the field holds Unicode data.)
13. Using this same process, create a column named Description. Set Allow Nulls to false and set Data Type to nvarchar.
14. Create a column named Price. Set Allow Nulls to false and set Data Type to money.
15. Press CTRL+S to save the table and name the table "Products".
When you're done, the definition will look like this:
Adding Data to the Database
Now you can add some sample data to your database that you'll work with later in the chapter.
1. In the left pane, click the Databases workspace.
2. In the left pane, expand the SmallBakery.sdf node and then click Tables.
3. Right-click the Products table and then click Data.
4. In the edit pane, enter the following records:
Name |
Description |
Price |
Bread |
Baked fresh every day. |
2.99 |
Strawberry Shortcake |
Made with organic strawberries from our garden. |
9.99 |
Apple Pie |
Second only to your mom's pie. |
12.99 |
Pecan Pie |
If you like pecans, this is for you. |
10.99 |
Lemon Pie |
Made with the best lemons in the world. |
11.99 |
Cupcakes |
Your kids and the kid in you will love these. |
7.99 |
Remember that you don't have to enter anything for the Id column. When you created the Id column, you set its IsIdentity property to true, which causes it to automatically be filled in.
When you are finished entering the data, the table designer will look like this:
5. Close the tab that contains the database data.
Displaying Data from a Database
Once you've got a database with data in it, you can display the data in an ASP.NET Web page. To select the table rows to display, you use a SQL statement, which is a command that you pass to the database.
1. In the left pane, click the Files workspace.
2. In the root of the website, create a new CSHTML page named ListProducts.cshtml.
3. Replace the existing markup with the following:
@{
var db = Database.Open("SmallBakery");
var selectQueryString = "SELECT * FROM Products ORDER BY Name";
}
<!DOCTYPE html>
<html>
<head>
<title>Small Bakery Products</title>
<style>
table, th, td {
border: solid 1px #bbbbbb;
border-collapse:collapse;
padding:2px;
}
</style>
</head>
<body>
<h1>Small Bakery Products</h1>
<table>
<thead>
<tr>
<th>Id</th>
<th>Product</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var row in db.Query(selectQueryString)){
<tr>
<td>@row.Id</td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</body>
</html>
In the first code block (highlighted in the example), you open the SmallBakery.sdf file (database) that you created earlier. The Database.Open method assumes that the .sdf file is in your website’s App_Data folder. (Notice that you don't need to specify the .sdf extension -- in fact, if you do, the Open method won't work.)
Note The App_Data folder is a special folder in ASP.NET that is used to store data files. For more information, see Connecting to a Database later in this post.
You then make a request to query the database using the following SQL Select statement:
SELECT * FROM Products ORDER BY Name
In the statement, Products identifies the table to query. The * character specifies that the query should return all the columns from the table. (You could also list columns individually, separated by commas, if you wanted to see only some of the columns.) The Order By clause indicates how the data should be sorted – in this case, by the Name column. This means that the data is sorted alphabetically based on the value of the Name column for each row.
In the body of the page, the markup creates an HTML table that will be used to display the data. Inside the tbody element, you use a foreach loop to individually get each data row that's returned by the query. For each data row, you create an HTML table row (tr element). Then you create HTML table cells (td) for each column. Each time you go through the loop, the next available row from the database is in the row variable (you set this up in the foreach statement). To get an individual column from the row, you can use row.Name or row.Description or whatever the name is of the column you want.
4. Run the page in a browser.
Structured Query Language (SQL).
SQL is a language that's used in most relational databases for managing data in a database. It includes commands that let you retrieve data and update it, and that let you create, modify, and manage database tables. SQL is different than a programming language (like the one you're using in WebMatrix) because with SQL, the idea is that you tell the database what you want, and it's the database's job to figure out how to get the data or perform the task. Here are examples of some SQL commands and what they do:
SELECT Id, Name, Price FROM Products WHERE Price > 10.00 ORDER BY Name
This fetches the Id, Name, and Price columns from records in the Products table if the value of Price is more than 10, and returns the results in alphabetical order based on the values of the Name column. This command will return a result set that contains the records that meet the criteria, or an empty set if no records do.
INSERT INTO Products (Name, Description, Price) VALUES ("Croissant", "A flaky delight", 1.99)
This inserts a new record into the Products table, setting the Name column to "Croissant", the Description column to "A flaky delight", and the price to 1.99.
DELETE FROM Products WHERE ExpirationDate < "01/01/2008"
This command deletes records in the Products table whose expiration date column is earlier than January 1, 2008. (This assumes that the Products table has such a column, of course.) The date is entered here in MM/DD/YYYY format, but it should be entered in the format that's used for your locale.
The Insert Into and Delete commands don’t return result sets. Instead, they return a number that tells you how many records were affected by the command.
For some of these operations (like inserting and deleting records), the process that is requesting the operation has to have appropriate permissions in the database. This is why for production databases you often have to supply a username and password when you connect to the database.
There are hundreds of SQL commands, but they all follow a pattern like this. You can use SQL commands to create database tables, count the number of records in a table, calculate prices, and perform many more operations.
Inserting Data in a Database
This section shows how to create a page that lets users add a new product to the Products database table. After a new product record is inserted, the page displays the updated table using the ListProducts.cshtml page that you created in the previous section.
The page includes validation to make sure that the data that the user enters is valid for the database. For example, code in the page makes sure that a value has been entered for all required columns.
Note For some of these operations (like inserting and deleting records), the process that is requesting the operation has to have appropriate permissions in the database. For production databases (as opposed to the test database that you are working with in WebMatrix) you often have to supply a username and password when you connect to the database. For information about how to set up membership and about ways to authorize user to perform tasks on the site, see Adding Security and Membership. We’ll have more on this on the next blog.
1. In the website, create a new CSHTML file named InsertProducts.cshtml.
2. Replace the existing markup with the following:
@{
var db = Database.Open("SmallBakery");
var Name = Request["Name"];
var Description = Request["Description"];
var Price = Request["Price"];
if (IsPost) {
// Read product name.
Name = Request["Name"];
if (Name.IsEmpty()) {
Validation.AddFieldError("Name", "Product name is required.");
}
// Read product description.
Description = Request["Description"];
if (Description.IsEmpty()) {
Validation.AddFieldError("Description",
"Product description is required.");
}
// Read product price
Price = Request["Price"];
if (Price.IsEmpty()) {
Validation.AddFieldError("Price", "Product price is required.");
}
// Define the insert query. The values to assign to the
// columns in the Products table are defined as parameters
// with the VALUES keyword.
if(Validation.Success) {
var insertQuery = "INSERT INTO Products (Name, Description, Price) " +
"VALUES (@0, @1, @2)";
db.Execute(insertQuery, Name, Description, Price);
// Display the page that lists products.
Response.Redirect(@Href("~/ListProducts"));
}
}
}
<!DOCTYPE html>
<html>
<head>
<title>Add Products</title>
<style type="text/css">
label { float: left; width: 8em; text-align: right;
margin-right: 0.5em;}
fieldset { padding: 1em; border: 1px solid; width: 35em;}
legend { padding: 2px 4p; border: 1px solid; font-weight: bold;}
.validation-summary-errors {font-weight:bold; color:red; font-size:11pt;}
</style>
</head>
<body>
<h1>Add New Product</h1>
@Html.ValidationSummary("Errors with your submission:")
<form method="post" action="">
<fieldset>
<legend>Add Product</legend>
<div>
<label>Name:</label>
<input name="Name" type="text" size="50" value="@Name" />
</div>
<div>
<label>Description:</label>
<input name="Description" type="text" size="50"
value="@Description" />
</div>
<div>
<label>Price:</label>
<input name="Price" type="text" size="50" value="@Price" />
</div>
<div>
<label> </label>
<input type="submit" value="Insert" class="submit" />
</div>
</fieldset>
</form>
</body>
</html>
<input name="Description" type="text" size="50"
value="@Description" />
</div>
<div>
<label>Price:</label>
<input name="Price" type="text" size="50" value="@Price" />
</div>
<div>
<label> </label>
<input type="submit" value="Insert" class="submit" />
</div>
</fieldset>
</form>
</body>
</html>
The body of the page contains an HTML form with three text boxes that let users enter a name, description, and price. When users click the Insert button, the code at the top of the page opens a connection to the SmallBakery.sdf database . You then get the values that the user has submitted from the Request object and assign those values to local variables.
To validate that the user entered a value for each required column, you do this:
Name = Request["Name"]; if (Name.IsEmpty()) { Validation.AddFieldError("Name", "Product name is required.");}
If the value of the Name column is empty, you use the AddFieldError method of the Validation helper and pass it an error message. You repeat this for each column you want to check. After all the columns have been checked, you perform this test:
if(Validation.Success) { // ... }
If all the columns validated (none were empty), you go ahead and create a SQL statement to insert the data and then execute it as shown next:
var insertQuery = "INSERT INTO Products (Name, Description, Price) VALUES (@0, @1, @2)";
For the values to insert, you include parameter placeholders (@0, @1, @2).
Note As a security precaution, always pass values to a SQL statement using parameters, as you see in the preceding example. This gives you a chance to validate the user's data, plus it helps protect against attempts to send malicious commands to your database (sometimes referred to as SQL injection attacks).
To execute the query, you use this statement, passing to it the variables that contain the values to substitute for the placeholders:
db.Execute(insertQuery, Name, Description, Price);
After the Insert Into statement has been executed, you send the user to the page that lists the products using this line:
Response.Redirect("~/ListProducts");
If validation didn't succeed, you skip the insert. Instead, you have a helper in the page that can display the accumulated error messages (if any):
@Html.ValidationSummary("Errors with your submission:")
Notice that the style block in the markup includes a CSS class definition named .validation-summary-errors. This is the name of the CSS class that's used by default for the div element that contains any validation errors. In this case, the CSS class specifies that validation summary errors are displayed in red and in bold, but you can define the .validation-summary-errors class to display any formatting you like.
3. View the page in a browser.
The page displays a form that is similar to the one that is shown in the following illustration.
4. Enter values for all the columns, but make sure that you leave the Price column blank.
5. Click Insert.
The page displays an error message, as shown in the following illustration. (No new record is created.)
6. Fill the form out completely, and then click Insert.
The ListProducts.cshtml page is displayed and shows the new record.
Updating Data in a Database
After data has been entered into a table, you might need to update it. This procedure shows you how to create two pages that are similar to the ones you created for data insertion earlier. The first page displays products and lets users select one to change. The second page lets the users actually make the edits and save them.
Important In a production website, you typically restrict who's allowed to make changes to the data. For information about how to set up membership and about ways to authorize user to perform tasks on the site, see Adding Security and Membership.
1. In the website, create a new CSHTML file named EditProducts.cshtml.
2. Replace the existing markup in the file with the following:
@{
var db = Database.Open("SmallBakery");
var selectQueryString = "SELECT * FROM Products ORDER BY Name";
}
<!DOCTYPE html>
<html>
<head>
<title>Edit Products</title>
<style type="text/css">
table, th, td {
border: solid 1px #bbbbbb;
border-collapse:collapse;
padding:2px;
}
</style>
</head>
<body>
<h1>Edit Small Bakery Products</h1>
<table>
<thead>
<tr>
<th> </th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var row in db.Query(selectQueryString)) {
<tr>
<td><a href="@Href("~/UpdateProducts", row.Id)">Edit</a></td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</body>
</html>
The only difference between this page and the ListProducts.cshtml page from earlier is that the HTML table in this page
includes an extra column that displays an Edit link. When you click this link, it takes you to the UpdateProducts.cshtml
page (which you'll create next) where you can edit the selected record.
Look at the code that creates the Edit link:
<a href="@Href("~/UpdateProducts", row.Id)">Edit</a></td>
This creates an HTML anchor (an a element) whose href attribute is set dynamically. The href attribute specifies the page to display when the user clicks the link. It also passes the Id value of the current row to the link. When the page runs, the page source might contain links like these:
<a href="UpdateProducts/1">Edit</a></td>
<a href="UpdateProducts/2">Edit</a></td>
<a href="UpdateProducts/3">Edit</a></td>
Notice that the href attribute is set to UpdateProducts/n, where n is a product number. When a user clicks one of these links, the resulting URL will look something like this:
https://localhost:18816/UpdateProducts/6
In other words, the product number to be edited will be passed in the URL.
3. View the page in a browser. The page displays the data in a format similar to the following illustration.
Next, you'll create the page that lets users actually update the data. The update page includes validation to validate the data that the user enters. For example, code in the page makes sure that a value has been entered for all required columns.
1. In the website, create a new CSHTML file named UpdateProducts.cshtml.
2. Replace the existing markup in the file with the following:
PrFont34Bin0BinSub0Frac0Def1Margin0Margin0Jc1Indent1440Lim0Lim1@{
var db = Database.Open("SmallBakery");
var selectQueryString = "SELECT * FROM Products WHERE Id=@0";
var ProductId = UrlData[0];
if (ProductId.IsEmpty()) {
Response.Redirect(@Href("~/EditProducts"));
}
var row = db.QuerySingle(selectQueryString, ProductId);
var Name = row.Name;
var Description = row.Description;
var Price = row.Price;
if (IsPost) {
Name = Request["Name"];
if (String.IsNullOrEmpty(Name)) {
Validation.AddFieldError("Name", "Product name is required.");
}
Description = Request["Description"];
if (String.IsNullOrEmpty(Description)) {
Validation.AddFieldError("Description",
"Product description is required.");
}
Price = Request["Price"];
if (String.IsNullOrEmpty(Price)) {
Validation.AddFieldError("Price", "Product price is required.");
}
if(Validation.Success) {
var updateQueryString =
"UPDATE Products SET Name=@0, Description=@1, Price=@2 WHERE Id=@3" ;
db.Execute(updateQueryString, Name, Description, Price, ProductId);
Response.Redirect(@Href("~/EditProducts"));
}
}
}
<!DOCTYPE>
<html>
<head>
<title>Add Products</title>
<style type="text/css">
label { float: left; width: 8em; text-align: right;
margin-right: 0.5em;}
fieldset { padding: 1em; border: 1px solid; width: 35em;}
legend { padding: 2px 4p; border: 1px solid; font-weight: bold;}
.validation-summary-errors {font-weight:bold; color:red; font-size:11pt;}
</style>
</head>
<body>
<h1>Update Product</h1>
@Html.ValidationSummary("Errors with your submission:")
<form method="post" action="" >
<fieldset>
<legend>Update Product</legend>
<div>
<label>Name:</label>
<input name="Name" type="text" size="50" value="@Name" />
</div>
<div>
<label>Description:</label>
<input name="Description" type="text" size="50"
value="@Description" />
</div>
<div>
<label>Price:</label>
<input name="Price" type="text" size="50" value="@Price" />
</div>
<div>
<label> </label>
<input type="submit" value="Update" class="submit" />
</div>
</fieldset>
</form>
</body>
</html>
The body of the page contains an HTML form where a product is displayed and where users can edit it. To get the product to display, you use this SQL statement:
SELECT * FROM Products WHERE Id=@0
This will select the product whose ID matches the value that is passed in the @0 parameter. (Because Id is the primary key and therefore must be unique, only one product record can ever be selected this way.) To get the ID value to pass to this Select statement, you can read the value that is passed to the page as part of the URL, using the following syntax:
var ProductId = UrlData[0];
To actually fetch the product record, you use the QuerySingle method, which will return just one record:
var row = db.QuerySingle(selectQueryString, ProductId);
The single row is returned into the row variable. You can get data out of each column and assign it to local variables like this:
var Name = row.FirstName; var Description = row.LastName; var Price = row.Address;
In the markup for the form, these values are displayed automatically in individual text boxes by using embedded code like the following:
<input name="Name" type="text" size="50" value="@Name" />
That part of the code displays the product record to be updated. Once the record has been displayed, the user can edit individual columns.
When the user submits the form by clicking the update button, the code in the if(IsPost) block runs. This gets the user's values from the Request object and then stores the values in variables and validates that each column has been filled in. If validation passes, the code creates the following SQL Update statement:
UPDATE Products SET Name=@0, Description=@1, Price=@2, WHERE ID=@3
In a SQL Update statement, you specify each column to update and the value to set it to. In this code, the values are specified using the parameter placeholders @0, @1, @2, and so on. (As noted earlier, for security, you should always pass values to a SQL statement by using parameters.)
When you call the db.Execute method, you pass the variables that contain the values in the order that corresponds to the parameters in the SQL statement:
db.Execute(updateQueryString, Name, Description, Price, ProductId);
After the Update statement has been executed, you call the following method in order to redirect the user back to the edit page:
Response.Redirect(@Href("~/EditProducts"));
The effect is that the user sees an updated listing of the data in the database and can edit another product.
3. Save the page.
4. Run the EditProducts.cshtml page (not the update page) and then click Edit to select a product to edit. The UpdateProducts.cshtml page is displayed, showing the record you selected.
5. Make a change and click Update. The products list is shown again with your updated data.
Deleting Data in a Database
This section shows how to let users delete a product from the Products database table. The example consists of two pages. In the first page, users select a record to delete. The record to be deleted is then displayed in a second page that lets them confirm that they want to delete the record.
Important In a production website, you typically restrict who's allowed to make changes to the data. For information about how to set up membership and about ways to authorize user to perform tasks on the site, see Adding Security and Membership.
1. In the website, create a new CSHTML file named ListProductsForDelete.cshtml.
2. Replace the existing markup with the following:
PrFont34Bin0BinSub0Frac0Def1Margin0Margin0Jc1Indent1440Lim0Lim1@{
var db = Database.Open("SmallBakery");
var selectQueryString = "SELECT * FROM Products ORDER BY Name";
}
<!DOCTYPE html >
<html>
<head>
<title>Delete a Product</title>
<style>
table, th, td {
border: solid 1px #bbbbbb;
border-collapse:collapse;
padding:2px;
}
</style>
</head>
<body>
<h1>Delete a Product</h1>
<form method="post" action="" name="form">
<table border="1">
<thead>
<tr>
<th> </th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var row in db.Query(selectQueryString)) {
<tr>
<td><a href="@Href("~/DeleteProduct", row.Id)">Delete</a></td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</form>
</body>
</html>
This page is similar to the EditProducts.cshtml page from earlier. However, instead of displaying an Edit link for each product, it displays a Delete link. The Delete link is created using the following embedded code in the markup:
<a href="@Href("~/DeleteProduct", row.Id)">Delete</a>
This creates a URL that looks like this when users click the link:
https://<server>/DeleteProduct/4
The URL calls a page named DeleteProduct.cshtml (which you'll create next) and passes it the ID of the product to delete (here, 4).
3. Save the file, but leave it open.
4. Create another CHTML file named DeleteProduct.cshtml and replace the existing content with the following:
PrFont34Bin0BinSub0Frac0Def1Margin0Margin0Jc1Indent1440Lim0Lim1@{
var db = Database.Open("SmallBakery");
var ProductId = UrlData[0];
if (ProductId.IsEmpty()) {
Response.Redirect(@Href("~/ListProductsForDelete"));
}
var prod = db.QuerySingle("SELECT * FROM PRODUCTS WHERE ID = @0", ProductId);
if( IsPost && !ProductId.IsEmpty()) {
var deleteQueryString = "DELETE FROM Products WHERE Id=@0";
db.Execute(deleteQueryString, ProductId);
Response.Redirect("~/ListProductsForDelete");
}
}
<!DOCTYPE html >
<html
<head>
<title>Delete Product</title>
</head>
<body>
<h1>Delete Product - Confirmation</h1>
<form method="post" action="" name="form">
<p>Are you sure you want to delete the following product?</p>
<p>Name: @prod.Name <br />
Description: @prod.Description <br />
Price: @prod.Price</p>
<p><input type="submit" value="Delete" /></p>
</form>
</body>
</html>
This page is called by ListProductsForDelete.cshtml and lets users confirm that they want to delete a product. To list the product to be deleted, you get the ID of the product to delete from the URL using the following code:
var ProductId = UrlData[0];
The page then asks the user to click a button to actually delete the record. This is an important security measure: when you perform sensitive operations in your website like updating or deleting data, these operations should always be done using a POST operation, not a GET operation. If your site is set up so that a delete operation can be performed using a GET operation, anyone can pass a URL like https://<server>/DeleteProduct/4 and delete anything they want from your database. By adding the confirmation and coding the page so that the deletion can be performed only by using a POST, you add a measure of security to your site.
The actual delete operation is performed using the following code, which first confirms that this is a post operation and that the ID isn't empty:
if( IsPost && !ProductId.IsEmpty()) { var deleteQueryString = "DELETE FROM Products WHERE Id=@0"; db.Execute(deleteQueryString, ProductId); Response.Redirect("~/ListProductsForDelete");}
The code runs a SQL statement that deletes the specified record and then redirects the user back to the listing page.
5. Run ListProductsForDelete.cshtml in a browser.
6. Click the Delete link for one of the products. The DeleteProduct.cshtml page is displayed to confirm that you want to delete that record.
7. Click the Delete button. The product record is deleted and the page is refreshed with an updated product listing.
Displaying Data Using the WebGrid Helper
So far when you've displayed data in a page, you've done all the work yourself. But there's also an easier way—use the WebGrid helper. The helper can render an HTML table for you that displays data, and it supports options for formatting, for creating a way to page through the data, and for letting users sort just by clicking a column heading.
1. In the website, create a new CSHTML file named ListProducts_WebGrid.cshtml.
2. Replace the existing markup with the following:
@{
var db = Database.Open("SmallBakery");
var selectQueryString = "SELECT * FROM Products ORDER BY Id";
var data = db.Query(selectQueryString);
var grid = new WebGrid(data, defaultSort: "Name", rowsPerPage: 5);
}
<!DOCTYPE html>
<html>
<head>
<title>Displaying Data Using WebGrid </title>
<style type="text/css">
.grid { margin: 4px; border-collapse: collapse; width: 600px; }
.head { background-color: #E8E8E8; font-weight: bold; color: #FFF; }
.grid th, .grid td { border: 1px solid #C0C0C0; padding: 5px; }
.alt { background-color: #E8E8E8; color: #000; }
.product { width: 200px; }
</style>
</head>
<body>
<h1>Small Bakery Products</h1>
@grid.GetHtml(
tableStyle: "grid",
headerStyle: "head",
alternatingRowStyle: "alt",
columns: grid.Columns(
grid.Column("Name", "Product", style: "product"),
grid.Column("Description", format:@<i>@item.Description</i>),
grid.Column("Price", format:@<text>$@item.Price</text>)
)
)
</html>
You start by opening the SmallBakery.sdf database file, as usual, and by creating up a SQL Select statement:
SELECT * FROM Products ORDER BY Id
To use the WebGrid helper, you do this:
var data = db.Query(selectQueryString);
var grid = new WebGrid(data, defaultSort: "Name", rowsPerPage: 5);
This creates a new WebGrid object and assigns it to the grid variable. As part of creating the WebGrid object, you first execute the SQL statement (via db.Query), then pass the results to the WebGrid object. When you create the WebGrid object, you can also specify options, like a default sort order (here, the Name column) and how many items to display on each "page" of the grid.
To render the data using the WebGrid helper, you use this code statement in the body of the page:
@grid.GetHtml()
(For grid, you use whatever variable you used when you created the WebGrid object.)
This renders the results of the query in a grid. If you want, you can specify just individual columns to display:
@grid.GetHtml(
columns: grid.Columns(
grid.Column("Name"),
grid.Column("Description"),
grid.Column("Price" )
)
)
As noted, you can specify many options for the WebGrid helper. In the complete example above, you see how you can specify formatting (CSS styles) for the grid as a whole, plus formatting or style for individual columns, plus column heading text:
@grid.GetHtml(
tableStyle: "grid",
headerStyle: "head",
alternatingRowStyle: "alt",
columns: grid.Columns(
grid.Column("Name", "Product", style: "product"),
grid.Column("Description", format:@<i>@item.Description</i>),
grid.Column("Price", format:@<text>$@item.Price</text>)
)
3. View the page in a browser. Click a column heading to sort by that column. Click a number at the bottom to page through the data.
Connecting to a Database
You can connect to a database in two ways. The first is to use the Database.Open method and to specify the name of the database file (less the .sdf extension):
var db = Database.Open("SmallBakery");
The Open method assumes that the .sdf file is in the website’s App_Data folder. This folder has a number of characteristics designed specifically for holding data. For example, it has appropriate permissions to allow the website to read and write data, and as a security measure, WebMatrix does not allow access to files from this folder.
The second way is to use a connection string. A connection string contains information about how to connect to a database. This can include a file path, or it can include the name of a SQL Server database on a local or remote server, along with a user name and password to connect to that server. (If you keep data in a centrally managed version of SQL Server, such as on a hosting provider's site, you always use a connection string to specify the database connection information.)
In WebMatrix, connection strings are usually stored in an XML file named Web.config. As the name implies, you can use a Web.config file in the root of your website to store configuration information for your website, including any connection strings that your site might require. An example of a connection string in a Web.config file might look like the following:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add
name="SQLServerConnectionString"
connectionString= "server=myServer;database=myDatabase;uid=username;pwd=password"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
In the example, the connection string points to a database in an instance of SQL Server that's running on a server somewhere (as opposed to a local .sdf file). You would need to substitute the appropriate names for myServer and myDatabase, and specify SQL Server login values for username and password. (The username and password values are not necessarily the same as your Windows credentials or as the values that your hosting provider has given you for logging in to their servers. Check with the administrator for the exact values you need.)
The Database.Open method is flexible, because it lets you pass either the name of a database .sdf file or the name of a connection string that's stored in the Web.config file. The following example shows how to connect to the database using the connection string illustrated in the previous example:
@{
var db = Database.Open("SQLServerConnectionString");
}
As noted, the Database.Open method lets you pass either a database name or a connection string, and it will figure out which to use. This is very useful when you deploy (publish) your website. You can use an .sdf file in the App_Data folder when you're developing and testing your site. Then when you move your site to a production server, you can use a connection string in the Web.config file that has the same name as your .sdf file but that points to the hosting provider's database -- all without having to change your.
Finally, if you want to work directly with a connection string, you can call the Database.OpenConnectionString method and pass it the actual connection string instead of just the name of one in the Web.config file. This might be useful in situations where for some reason you don't have access to the connection string (or values in it, such as the .sdf file name) until the page is running. However, for most scenarios, you can use Database.Open as described in this chapter.
Additional Resources
Note that for beta, you can't have an .sdf file AND a connection string with the same name. However, this will change for CTP1, so I'm going to suggest that we just not worry about mentioning that at the moment -- for the few users who are messing about with connection strings, they'll discover this soon enough if they encounter the problem.