Introduction to Working with a Database in ASP.Net Web Pages (Razor) Sites

  • Article
  • 25 minutes to read

by Tom FitzMacken

This article describes how to utilize Microsoft WebMatrix tools to create a database in an ASP.Internet Web Pages (Razor) website, and how to create pages that let you brandish, add, edit, and delete information.

What yous'll learn:

  • How to create a database.
  • How to connect to a database.
  • How to brandish information in a spider web folio.
  • How to insert, update, and delete database records.

These are the features introduced in the article:

  • Working with a Microsoft SQL Server Compact Edition database.
  • Working with SQL queries.
  • The Database class.

Software versions used in the tutorial

  • ASP.NET Web Pages (Razor) 2
  • WebMatrix 2

This tutorial also works with WebMatrix 3. You can utilise ASP.Internet Web Pages 3 and Visual Studio 2013 (or Visual Studio Express 2013 for Web); notwithstanding, the user interface will exist different.

Introduction to Databases

Imagine a typical address volume. For each entry in the address volume (that is, for each person) you have several pieces of information such as first name, last name, address, electronic mail address, and phone number.

A typical way to pic data like this is as a table with rows and columns. In database terms, each row is often referred to as a tape. Each column (sometimes referred to equally fields) contains a value for each blazon of information: first name, final proper noun, and and then on.

ID FirstName LastName Address Email Phone
i Jim Abrus 210 100th St SE Orcas WA 98031 jim@contoso.com 555 0100
2 Terry Adams 1234 Primary St. Seattle WA 99011 terry@cohowinery.com 555 0101

For most database tables, the table has to accept a column that contains a unique identifier, similar a customer number, account number, etc. This is known as the tabular array's principal cardinal, and yous utilise it to identify each row in the tabular array. In the example, the ID column is the primary key for the address volume.

With this basic understanding of databases, yous're set up to learn how to create a simple database and perform operations such as adding, modifying, and deleting data.

Tip

Relational Databases

You tin can store data in lots of ways, including text files and spreadsheets. For most concern uses, though, data is stored in a relational database.

This article doesn't go very deeply into databases. Even so, you might find it useful to understand a footling near them. In a relational database, data is logically divided into separate tables. For instance, a database for a schoolhouse might contain separate tables for students and for class offerings. The database software (such every bit SQL Server) supports powerful commands that let you lot dynamically institute relationships betwixt the tables. For example, yous tin utilize the relational database to establish a logical relationship between students and classes in order to create a schedule. Storing information in dissever tables reduces the complexity of the table structure and reduces the demand to keep redundant information in tables.

Creating a Database

This procedure shows you lot how to create a database named SmallBakery by using the SQL Server Compact Database blueprint tool that's included in WebMatrix. Although you lot can create a database using code, it'due south more typical to create the database and database tables using a pattern tool similar WebMatrix.

  1. Start WebMatrix, and on the Quick Start page, click Site From Template.

  2. Select Empty Site, and in the Site Proper name box enter "SmallBakery" and 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 proper name as your site.

  5. In the left pane, expand the SmallBakery.sdf node and and then click Tables.

  6. In the ribbon, click New Table. WebMatrix opens the table designer.

    [image]

  7. Click in the Name column and enter "Id".

  8. In the Data Blazon column, select int.

  9. Set the Is Principal Fundamental? and Is Place? options to Yeah.

    As the proper name suggests, Is Primary Cardinal tells the database that this will be the table'south 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).

  10. Click in the next row. The editor starts a new column definition.

  11. For the Name value, enter "Name".

  12. For Data Blazon, choose "nvarchar" and set the length to l. The var function of nvarchar tells the database that the data for this column will exist a string whose size might vary from record to record. (The due north prefix represents national, indicating that the field tin concord grapheme data that represents whatever alphabet or writing system — that is, that the field holds Unicode data.)

  13. Set the Permit Nulls option to No. This will enforce that the Proper noun column is not left bare.

  14. Using this same procedure, create a column named Description. Set Information Type to "nvarchar" and fifty for the length, and set Permit Nulls to false.

  15. Create a column named Price. Set up Data Type to "money" and set up Let Nulls to faux.

  16. In the box at the top, name the tabular array "Product".

    When you lot're washed, the definition volition wait like this:

    [image]

  17. Press Ctrl+Southward to save the table.

Adding Data to the Database

Now you tin add some sample information to your database that y'all'll work with subsequently in the article.

  1. In the left pane, aggrandize the SmallBakery.sdf node and then click Tables.

  2. Right-click the Product tabular array then click Data.

  3. In the edit pane, enter the following records:

    Name Clarification Cost
    Bread Baked fresh every twenty-four hours. 2.99
    Strawberry Shortcake Made with organic strawberries from our garden. 9.99
    Apple Pie Second only to your mom'southward pie. 12.99
    Pecan Pie If y'all similar pecans, this is for you. 10.99
    Lemon Pie Made with the best lemons in the globe. 11.99
    Cupcakes Your kids and the kid in you will love these. 7.99

    Think that y'all don't have to enter anything for the Id cavalcade. When you lot created the Id column, yous ready its Is Identity holding to true, which causes information technology to automatically be filled in.

    When you lot're finished entering the data, the table designer volition look similar this:

    [image]

  4. Close the tab that contains the database information.

Displaying Information from a Database

In one case you've got a database with data in it, you tin display the data in an ASP.NET web page. To select the table rows to brandish, 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 Production Lodge BY Name";  } <!DOCTYPE html> <html>  <head>    <title>Small Bakery Products</title>    <style>        tabular array, th, td {          border: solid 1px #bbbbbb;          edge-collapse: collapse;          padding: 2px;        }     </manner>  </caput>  <body>    <h1>Minor Bakery Products</h1>    <tabular array>        <thead>            <tr>                <thursday>Id</th>                <th>Product</thursday>                <th>Clarification</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>    </tabular array>  </body> </html>

    In the first code cake, you open the SmallBakery.sdf file (database) that you created earlier. The Database.Open method assumes that the .sdf file is in your website'south App_Data binder. (Notice that you don't need to specify the .sdf extension — in fact, if y'all do, the Open method won't piece of work.)

    Note

    The App_Data folder is a special folder in ASP.Internet that'due south used to store data files. For more than information, see Connecting to a Database later in this commodity.

    You and then brand a request to query the database using the following SQL Select statement:

                      SELECT * FROM Production Lodge By Proper noun

    In the statement, Product identifies the tabular array to query. The * character specifies that the query should render all the columns from the table. (You could likewise list columns individually, separated by commas, if you wanted to come across only some of the columns.) The Order By clause indicates how the data should be sorted — in this instance, by the Proper noun column. This ways that the data is sorted alphabetically based on the value of the Proper noun column for each row.

    In the body of the page, the markup creates an HTML tabular array that will exist used to display the information. Within the <tbody> chemical 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 tabular array row (<tr> element). Then y'all create HTML table cells (<td> elements) for each cavalcade. Each time you go through the loop, the side by side available row from the database is in the row variable (you lot set this up in the foreach statement). To go an individual cavalcade from the row, yous tin utilise row.Name or row.Clarification or whatever the proper noun is of the column yous want.

  4. Run the page in a browser. (Brand sure the page is selected in the Files workspace before yous run it.) The page displays a list similar the following:

    [image]

Tip

Structured Query Language (SQL)

SQL is a language that's used in most relational databases for managing information in a database. Information technology includes commands that let yous retrieve data and update information technology, and that let you create, modify, and manage database tables. SQL is different than a programming linguistic communication (like the 1 you're using in WebMatrix) considering with SQL, the thought is that you tell the database what you want, and it's the database'south job to figure out how to get the information or perform the job. Here are examples of some SQL commands and what they do:

SELECT Id, Proper noun, Price FROM Product WHERE Toll > 10.00 ORDER By Proper name

This fetches the Id, Name, and Price columns from records in the Product table if the value of Price is more than x, and returns the results in alphabetical society based on the values of the Name column. This control volition return a result set that contains the records that meet the criteria, or an empty fix if no records lucifer.

INSERT INTO Product (Proper name, Description, Price) VALUES ("Croissant", "A flaky delight", 1.99)

This inserts a new tape into the Product table, setting the Name cavalcade to "Croissant", the Description column to "A flaky please", and the price to 1.99.

DELETE FROM Product WHERE ExpirationDate < "01/01/2008"

This control deletes records in the Product table whose expiration appointment column is earlier than January ane, 2008. (This assumes that the Production table has such a column, of course.) The date is entered here in MM/DD/YYYY format, but information technology should exist entered in the format that's used for your locale.

The Insert Into and Delete commands don't return outcome sets. Instead, they return a number that tells you how many records were affected past the command.

For some of these operations (like inserting and deleting records), the process that's requesting the operation has to have appropriate permissions in the database. This is why for product databases you often accept to supply a username and password when y'all connect to the database.

There are dozens of SQL commands, but they all follow a pattern like this. You lot can apply 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 Product database tabular array. After a new product record is inserted, the page displays the updated table using the ListProducts.cshtml page that yous 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 case, 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 InsertProducts.cshtml.

  2. Replace the existing markup with the post-obit:

                      @{     Validation.RequireField("Name", "Product proper name is required.");     Validation.RequireField("Description", "Product description is required.");     Validation.RequireField("Price", "Product price is required.");      var db = Database.Open("SmallBakery");     var Name = Request.Form["Name"];     var Clarification = Request.Form["Description"];     var Cost = Request.Form["Cost"];      if (IsPost && Validation.IsValid()) {         // Define the insert query. The values to assign to the         // columns in the Product tabular array are defined as parameters         // with the VALUES keyword.         if(ModelState.IsValid) {             var insertQuery = "INSERT INTO Product (Name, Description, Toll) " +                 "VALUES (@0, @1, @2)";             db.Execute(insertQuery, Name, Description, Price);             // Display the page that lists products.             Response.Redirect("~/ListProducts");         }     } }  <!DOCTYPE html> <html> <head>  <title>Add Products</championship>  <style type="text/css">     label {float:left; width: 8em; text-align: right;            margin-correct: 0.5em;}     fieldset {padding: 1em; border: 1px solid; width: 50em;}     legend {padding: 2px 4px; border: 1px solid; font-weight:bold;}     .validation-summary-errors {font-weight:bold; colour:crimson;            font-size: 11pt;}  </style> </caput> <body>  <h1>Add together New Product</h1>   @Html.ValidationSummary("Errors with your submission:")   <course method="postal service" activity="">    <fieldset>      <legend>Add Product</legend>      <div>        <label>Name:</label>        <input name="Proper noun" type="text" size="50" value="@Name" />      </div>      <div>        <label>Description:</label>        <input proper name="Clarification" blazon="text" size="50"            value="@Description" />      </div>      <div>        <label>Cost:</label>        <input proper noun="Price" type="text" size="50" value="@Toll" />      </div>      <div>        <label>&nbsp;</label>        <input type="submit" value="Insert" class="submit" />      </div>    </fieldset>  </form> </body> </html>

    The body of the page contains an HTML course with iii text boxes that let users enter a proper noun, clarification, 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 go the values that the user has submitted by using the Request object and assign those values to local variables.

    To validate that the user entered a value for each required column, you register each <input> element that you lot desire to validate:

                      Validation.RequireField("Proper noun", "Product name is required."); Validation.RequireField("Description", "Product description is required."); Validation.RequireField("Price", "Product cost is required.");

    The Validation helper checks that in that location is a value in each of the fields that y'all've registered. Y'all tin test whether all the fields passed validation by checking Validation.IsValid(), which you lot typically do before you process the information you get from the user:

                      if (IsPost && Validation.IsValid()) {     // Procedure data here }

    (The && operator ways AND — this test is If this is a form submission AND all the fields accept passed validation.)

    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 side by side:

                      var insertQuery =     "INSERT INTO Product (Name, Clarification, Toll) VALUES (@0, @i, @2)";

    For the values to insert, you include parameter placeholders (@0, @ane, @2).

    Note

    As a security precaution, always pass values to a SQL statement using parameters, equally you see in the preceding example. This gives you lot a chance to validate the user'south information, plus it helps protect against attempts to send malicious commands to your database (sometimes referred to every bit SQL injection attacks).

    To execute the query, you employ this statement, passing to information technology the variables that incorporate the values to substitute for the placeholders:

                      db.Execute(insertQuery, Proper name, Description, Price);

    Afterwards the Insert Into statement has executed, yous 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 accept a helper in the page that can display the accumulated error messages (if whatever):

                      @Html.ValidationSummary("Errors with your submission:")

    Find that the style block in the markup includes a CSS class definition named .validation-summary-errors. This is the proper name of the CSS class that'southward used past default for the <div> element that contains whatsoever validation errors. In this case, the CSS grade specifies that validation summary errors are displayed in red and in bold, but you can define the .validation-summary-errors form to display any formatting yous like.

Testing the Insert Folio

  1. View the page in a browser. The folio displays a form that's similar to the ane that'southward shown in the following illustration.

    [image]

  2. Enter values for all the columns, but make sure that you exit the Cost column blank.

  3. Click Insert. The page displays an error message, as shown in the following illustration. (No new record is created.)

    [image]

  4. Fill the form out completely, and then click Insert. This time, the ListProducts.cshtml page is displayed and shows the new tape.

Updating Data in a Database

After information has been entered into a tabular array, y'all might need to update information technology. This procedure shows yous how to create two pages that are similar to the ones you created for information insertion before. The first page displays products and lets users select 1 to change. The second page lets the users actually make the edits and save them.

  1. In the website, create a new CSHTML file named EditProducts.cshtml.

  2. Supervene upon the existing markup in the file with the following:

                      @{     var db = Database.Open up("SmallBakery");     var selectQueryString = "SELECT * FROM Production Club BY Name";  } <!DOCTYPE html> <html> <caput>     <title>Edit Products</title>     <manner type="text/css">         table, thursday, td {           border: solid 1px #bbbbbb;           border-plummet: collapse;           padding: 2px;         }     </way> </caput> <body>     <h1>Edit Minor Bakery Products</h1>     <table>       <thead>         <tr>           <th>&nbsp;</thursday>           <th>Name</th>           <th>Description</thursday>           <thursday>Cost</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 deviation between this page and the ListProducts.cshtml page from earlier is that the HTML tabular array in this page includes an extra column that displays an Edit link. When you click this link, it takes you to the UpdateProducts.cshtml folio (which you'll create adjacent) where you tin 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 <a> element whose href attribute is set dynamically. The href attribute specifies the page to display when the user clicks the link. Information technology too passes the Id value of the electric 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 gear up to UpdateProducts/n, where due north is a product number. When a user clicks one of these links, the resulting URL volition look something like this:

    http://localhost:18816/UpdateProducts/half dozen

    In other words, the production number to exist edited will be passed in the URL.

  3. View the page in a browser. The folio displays the data in a format like this:

    [image]

    Side by side, you'll create the page that lets users actually update the information. The update folio 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.

  4. In the website, create a new CSHTML file named UpdateProducts.cshtml.

  5. Replace the existing markup in the file with the following.

                      @{     Validation.RequireField("Proper noun", "Product name is required.");     Validation.RequireField("Clarification", "Product clarification is required.");     Validation.RequireField("Price", "Product price is required.");      var Name = "";     var Description = "";     var Toll = Decimal.Zero;      var ProductId  = UrlData[0];     if (ProductId.IsEmpty()) {          Response.Redirect("~/EditProducts");     }      var db = Database.Open("SmallBakery");      if (IsPost && Validation.IsValid()) {         var updateQueryString =             "UPDATE Product Fix Name=@0, Description=@ane, Toll=@2 WHERE Id=@3" ;         Name = Asking["Name"];         Description = Request["Description"];         Toll = Asking["Price"].AsDecimal();         db.Execute(updateQueryString, Proper name, Description, Price, ProductId);         Response.Redirect(@Href("~/EditProducts"));     }     else {         var selectQueryString = "SELECT * FROM Product WHERE Id=@0";          var row = db.QuerySingle(selectQueryString, ProductId);         Proper name = row.Proper noun;         Clarification = row.Description;         Price = row.Price;     }  }  <!DOCTYPE html> <html> <head>   <title>Add Products</championship>   <style blazon="text/css">      label { float: left; width: 8em; text-align: right;              margin-correct: 0.5em;}      fieldset { padding: 1em; border: 1px solid; width: 35em;}      fable { padding: 2px 4px;  border: 1px solid; font-weight: bold;}      .validation-summary-errors {font-weight:bold; color:cherry-red; font-size:11pt;}   </mode> </head> <trunk>   <h1>Update Product</h1>    @Html.ValidationSummary("Errors with your submission:")    <course method="post" action="">      <fieldset>        <legend>Update Product</fable>        <div>          <label>Proper noun:</label>          <input proper name="Name" type="text" size="50" value="@Proper name" />        </div>        <div>          <label>Description:</characterization>          <input name="Description" type="text" size="50"             value="@Description" />        </div>        <div>           <label>Price:</characterization>           <input proper noun="Price" blazon="text" size="50" value="@Price" />        </div>        <div>           <label>&nbsp;</label>           <input blazon="submit" value="Update" class="submit" />        </div>     </fieldset>   </class> </body> </html>

    The body of the folio contains an HTML class where a product is displayed and where users can edit it. To go the product to display, you lot utilize this SQL statement:

                      SELECT * FROM Product WHERE Id=@0

    This will select the product whose ID matches the value that's 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 manner.) To become the ID value to laissez passer to this Select argument, you tin can read the value that's passed to the page as role of the URL, using the following syntax:

                      var ProductId  = UrlData[0];

    To actually fetch the product tape, yous use the QuerySingle method, which will return but one record:

                      var row = db.QuerySingle(selectQueryString, ProductId);

    The single row is returned into the row variable. You lot can get information out of each column and assign information technology to local variables like this:

                      var Name = row.Name; var Description = row.Description; var Toll = row.Cost;

    In the markup for the form, these values are displayed automatically in individual text boxes by using embedded code similar the following:

                      <input name="Name" type="text" size="50" value="@Name" />

    That role of the code displays the product record to be updated. In one case 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'due south values from the Request object, stores the values in variables, and validates that each column has been filled in. If validation passes, the code creates the following SQL Update argument:

                      UPDATE Product SET Name=@0, Clarification=@1, Price=@2, WHERE ID=@iii

    In a SQL Update statement, you specify each cavalcade to update and the value to gear up it to. In this code, the values are specified using the parameter placeholders @0, @1, @two, and so on. (Every bit noted earlier, for security, you should always pass values to a SQL statement by using parameters.)

    When y'all call the db.Execute method, you pass the variables that comprise the values in the society 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 folio:

                      Response.Redirect(@Href("~/EditProducts"));

    The result is that the user sees an updated listing of the data in the database and can edit another product.

  6. Salve the page.

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

    [image]

  8. Brand a change and click Update. The products listing is shown once again with your updated data.

Deleting Data in a Database

This section shows how to let users delete a product from the Production database table. The instance consists of 2 pages. In the first page, users select a record to delete. The record to exist deleted is then displayed in a 2d page that lets them confirm that they desire to delete the record.

  1. In the website, create a new CSHTML file named ListProductsForDelete.cshtml.

  2. Replace the existing markup with the following:

                      @{   var db = Database.Open("SmallBakery");   var selectQueryString = "SELECT * FROM Production ORDER BY Proper noun"; } <!DOCTYPE html> <html> <caput>     <title>Delete a Product</title>     <mode>         tabular array, th, td {           edge: solid 1px #bbbbbb;           border-collapse: plummet;           padding: 2px;         }      </way> </head> <trunk>   <h1>Delete a Production</h1>   <form method="post" activeness="" name="form">     <table border="i">       <thead>         <tr>           <th>&nbsp;</th>           <th>Name</th>           <th>Description</th>           <th>Cost</thursday>         </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> </trunk> </html>

    This page is similar to the EditProducts.cshtml folio from earlier. Nonetheless, instead of displaying an Edit link for each product, information technology displays a Delete link. The Delete link is created using the following embedded lawmaking in the markup:

                      <a href="@Href("~/DeleteProduct", row.Id)">Delete</a>

    This creates a URL that looks like this when users click the link:

    http://<server>/DeleteProduct/4

    The URL calls a page named DeleteProduct.cshtml (which yous'll create next) and passes it the ID of the production to delete (here, iv).

  3. Salvage the file, merely leave it open.

  4. Create another CHTML file named DeleteProduct.cshtml. Supervene upon the existing content with the following:

                      @{   var db = Database.Open("SmallBakery");   var ProductId = UrlData[0];   if (ProductId.IsEmpty()) {     Response.Redirect("~/ListProductsForDelete");   }   var prod = db.QuerySingle("SELECT * FROM Production WHERE ID = @0", ProductId);   if( IsPost && !ProductId.IsEmpty()) {     var deleteQueryString = "DELETE FROM Product WHERE Id=@0";     db.Execute(deleteQueryString, ProductId);     Response.Redirect("~/ListProductsForDelete");   } }  <!DOCTYPE html> <html> <head>     <title>Delete Product</title> </head> <torso>   <h1>Delete Product - Confirmation</h1>   <course method="mail" activity="" proper name="form">     <p>Are y'all sure y'all want to delete the following product?</p>      <p>Name: @prod.Name <br />        Clarification: @prod.Clarification <br />        Price: @prod.Price</p>     <p><input blazon="submit" value="Delete" /></p>   </grade> </body> </html>

    This page is called by ListProductsForDelete.cshtml and lets users confirm that they want to delete a product. To listing 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 so asks the user to click a button to actually delete the record. This is an of import security measure: when you perform sensitive operations in your website like updating or deleting data, these operations should e'er be washed using a POST performance, not a Go performance. If your site is ready upwards and so that a delete performance can be performed using a GET operation, anyone tin laissez passer a URL like http://<server>/DeleteProduct/4 and delete annihilation 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 together 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 Product 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.

    [image]

  6. Click the Delete link for one of the products. The DeleteProduct.cshtml folio is displayed to confirm that you want to delete that tape.

  7. Click the Delete button. The product tape is deleted and the page is refreshed with an updated production listing.

Tip

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 is designed specifically for holding data. For instance, information technology has appropriate permissions to allow the website to read and write data, and as a security mensurate, WebMatrix does not allow admission to files from this binder.

The second way is to use a connectedness string. A connection string contains information most how to connect to a database. This tin include a file path, or it tin can include the proper noun of a SQL Server database on a local or remote server, along with a user name and countersign to connect to that server. (If yous keep data in a centrally managed version of SQL Server, such as on a hosting provider'due south site, you always use a connection cord to specify the database connectedness information.)

In WebMatrix, connection strings are usually stored in an XML file named Web.config. As the proper noun implies, y'all tin employ a Web.config file in the root of your website to store the site's configuration information, including whatsoever connectedness strings that your site might require. An example of a connection string in a Spider web.config file might expect like the following. Note $CREDENTIAL_PLACEHOLDER$ is a placeholder for the password key/value pair:

                <?xml version="1.0" encoding="utf-viii" ?> <configuration>   <connectionStrings>    <add      name="SQLServerConnectionString"      connectionString= "server=myServer;database=myDatabase;uid=username;$CREDENTIAL_PLACEHOLDER$"      providerName="Organization.Information.SqlClient" />   </connectionStrings> </configuration>

In the case, the connection string points to a database in an instance of SQL Server that'due south 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 non necessarily the aforementioned every bit 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 proper noun 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 cord illustrated in the previous example:

                @{     var db = Database.Open("SQLServerConnectionString"); }

As noted, the Database.Open method lets yous laissez passer either a database name or a connection cord, and information technology'll effigy out which to utilize. This is very useful when you deploy (publish) your website. You can utilise an .sdf file in the App_Data folder when yous're developing and testing your site. Then when you move your site to a product server, y'all can employ a connection string in the Web.config file that has the same proper noun every bit your .sdf file simply that points to the hosting provider's database — all without having to change your lawmaking.

Finally, if you desire to piece of work directly with a connection string, y'all tin call the Database.OpenConnectionString method and pass it the actual connexion string instead of only the name of one in the Web.config file. This might be useful in situations where for some reason y'all don't accept access to the connexion string (or values in it, such as the .sdf file name) until the page is running. Still, for most scenarios, you tin use Database.Open as described in this article.

Additional Resources

  • SQL Server Meaty
  • Connecting to a SQL Server or MySQL Database in WebMatrix
  • Validating User Input in ASP.Internet Web Pages Sites