Syllabus Schedule Project Labs Canvas Server Access CSS Tutorial Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

Assignment 5 - CRUD

The exercises this week use a Sql Server database and a web interface for full CRUD (create, read, update, delete) functionality. The first exercise is mostly cut-and-paste from the CRUD example discussed in class. The second exercise follows the same template but with different data fields.

  1. CRUD - crud db This exercise creates a template that may be used for future database exercises and the music store project. 


    1. Model: Create a new model named CrudModel and add the model code from the CRUD class handout.
    2. Database: Create a Sql Server database named Crud with a table named tblPerson. To create a database in VS right-click on the App_Data folder -> Add -> new Item -> Sql Server Database. To add a table double click on the database in Solution Explorer and VS will open the database in Server Explorer. Right click on the "Tables" icon and select Add Table. Name the table tblPerson and add fields for the four properties in the model (see image). PersonID needs to be primary key and identity. To define a primary key right-click on the name in the designer and select "Primary Key." To make it an autonumber select the field and in properties set "Is Identity" to true.(see image). IsIdenity
    3. Save the database by clicking "Update" and "Update Database."
    4. Add a couple of records to the table by right-clicking on it in server explorer and selecting "Show Table Data."
    5. Dapper: Dapper is a ORM (object relations manager) that maps C# objects to database tables. It reduces the amount of code needed to pass data to and from the database. Add Dapper to your project in Visual Studio by selecting: Tools --> NuGet Package Manager --> Package Manager Console. In the package manager window type: Install-Package Dapper.  You only need to do this once per project. After adding Dapper you should see Dapper.dll in the bin folder of your project. To see the bin folder you may need to click the "show all files" icon in Solution Explorer. More on Dapper
    6. DataRepository - Best practice in MVC is to place code related to data access in a folder named DataRepository (or Repository). The objective is to separate data access implementation details from the rest of the application so that any changes in data access (different datasource, database, etc.) do not impact other parts of the application. Create a new folder "DataRepository" and add a new class named CrudRepository. Paste in the code from the class handout. It provides five methods for CRUD that you will call from the controller.
    7. Controller: The controller uses seven action methods to handle CRUD functionality. Create a new controller named CrudController and add the appropriate code from the class handout.
    8. web.config: Database connection strings are stored in the web.config file. Open the web.config file in the root folder of your project and add the following two connection strings to the <configuration> section. The first connection string LocalDB is used with Visual Studio and the second, SqlExpress, is used on the server. Prior to publishing your project to the server you will need to comment the LocalDB string and uncomment the SqlExpress string.

         <add name="Crud" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)   \MSSQLLocalDB;AttachDbFileName=|DataDirectory|\Crud.mdf;Integrated Security=True" />
      <!-- <add name="Crud" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Crud.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> -->

    9. While you have web.config open configure your application to display full error messages. By default error messages from the server are hidden as a security precaution.
    10. Views: Use VS's Add View wizard to add views for listing (index view), creating, updating, and deleting. I suggest testing each view after creating it so that you quickly catch any problems. Start with "List" and test. Then Add and test, etc.
    11. Test the CRUD functionality and validation. ActionMethods
    12. Auto_Close: To improve performance Sql Server does not completely close database connections. Instead they are put into a connection pool where that can be reused. This is useful in a production environment but in a developer environment it can cause your .mdf database files to be locked, preventing you from delete them. Connection pooling can be disabled by enabling Auto_Close. The CRUD controller has an action method named "SetAutoClose."  Call this method once to enable Auto_Close on your database. Execute this method once for each new database you create.
    13. Publish to server and test. If you get the message "A network-related or instance-specific error occurred while establishing a connection to SQL Server"  there is a likely a problem with your connection string. Remember to use the Sql Express string on the server.
  2. Bird - This exercise creates CRUD functionality for a bird database. It uses a model, controller, dataRepository, sql server database, and view. The sample site does not allow editing or deleting the six existing records but you can add new records (when finished please delete anything that you added to the sample).
    1. Model: Create a model named BirdModel with the following properties:
      Property Type Validation
      BirdID int Key
      Name string required
      ImageName string required
      Description string required
    2. Database: birds database Create a Sql Server database named Birds with a table named tblBird. Add four fields corresponding to the model. Make Id an autonumber field (see A05 if you need a refresher on how to do this).
    3. Populate a couple records of the database with random text. You will replace it later with real data.
    4. DataRepository: Copy CrudRepositoy and name it BirdRepository. Change the connection string name to "Birds".  All of the method names and sql statements will need to be modified to work with Birds instead of People. Some of this can be done by using VS's "replace all" to replace "CRUD" with "Bird" and replace "Person" with "Bird".  Using case-sensitive replace will retain the correct capitalization.
    5. Web.config: Copy the two connection strings for "CRUD" and rename them "Birds." Note that two changes are needed to each string. Your web.config should now have four connection strings.
    6. Controller: Create a new controller named "BirdController."  Use "CrudController as a template and use replace to change the names.
    7. View: Create a view for the Index action method using the List template and BirdModel model. Test it. You should see a table with the random text that you added to the database.
    8. Add views for CRUD functionality: Create, Details, and Edit. Delete does not have a view.
    9. Test!
    10. The description field can be quite long. Replace the EditorFor control in Create and Edit with TextAreaFor.  The answer by Mgot90 updated 8/29/2016 shows how to format TextAreaFor (remove htmlAttributes as shown).
    11. Replace the random text you added with the data for the six birds in the sample site.
    12. Test your site. It should look similar to the sample site. In assignment 6 you will develop an interface to viewing the birds.
  3. ViewRandom - This exercise uses a SQL statement to select four random birds from the database.  The bird names a images are displayed in a view.
    1. DataRepository: Add a new method named GetRandom() (GetAllPeople is a good template). Modify the SQL to select four random items as shown in Selecting n random records with Sql Server.
    2. Controller: Add a new action method named ViewRandom that calls the GetRandom method. (the Index action method is a good template).
    3. View: Create a new view named ViewRandom (template is list).
    4. Test. Each page refresh should display different birds.
    5. Images: Static content such as images and CSS is served only from the content folder. In the content folder add a subfolder named Images and another subfolder named birds.  Images for the six birds are in Unzip the images and drag them into your VS solution. (Content added via Windows explorer cannot be seen in Visual Studio.) Note that there are two versions of each photo, a full size and a thumb.
    6. Relative file paths: For the browser to find an image it needs the path from the web server's root folder. We know the path within our application but that path above our application can change. For instance, compare the two paths:


      The solution is to use the Razor the tilde (~) character. Razor will replace the ~ with the path the root folder of your application (for more information see Web Project Paths). For instance, the two paths shown above for flickerT.jpg would be rendered with:

      <img src="~/content/images/birds/fiickerT.jpg" />

      The index page displays the thumbnail sized photos which are named with a "T" just before the dot. The following code snippet add the "T" to the name and displays the images. Put it in ViewRandom:
      <div class="row">
          @foreach (var item in Model)
              <div class="col-md-6 ">
                  <div class="thumbContainer">
                      @{string thumbImage = item.ImageName.Replace(".", "T.");}
                      <img src='~/content/images/birds/@thumbImage' class='birdThumb' alt="@item.Name" title="@item.Name" />
    7. Test!

Submission instructions: Submit assignments via the Canvas course management system. Submit the full URL for each exercise in the assignment, listing the URLs in the same order that they are listed in the assignment. To minimize typos in URLs it is strongly recommended that you copy the URLs from the address bar of the browser rather than trying to type them. Incorrect URLs will not be graded and no credit will be given.

When pages are connected via navigation (as in your music store project) it is only necessary to submit the URL of the first page.

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes