Using SQL Server with .NET

This part of the .NET Bookshelf tutorial shows how the sample app stores its persistent data in Microsoft SQL Server running on a Google Compute Engine VM.

This page is part of a multi-page tutorial. To start from the beginning and see instructions for setting up, go to .NET Bookshelf App.

Create the SQL Server instance

  1. Create a new Compute Engine instance.

    Go to the VM Instances page

  2. For Instance ID, enter library.

  3. Set zone to be us-west1-a.

  4. In the Boot Disk section, click the Change button.

  5. Select the Application images tab and select the SQL Server 2014 Standard on Windows Server 2012 R2 image and then click the Select button.

  6. Click the Create button to create the VM instance.

  7. Click the name of the instance when it's ready. It can take a few minutes for the instance to be ready. When the instance is ready, it is visible in the instances list.

  8. On the instance details page, click Create or reset Windows password.

  9. In the Set new Windows password dialog window, enter a username of your choice, and click Set to create the user account on your instance. Make a note of the provided password, and close the dialog.

Create the SQL Server database

  1. In the VM instances list, click the RDP link next to your SQL Server instance. Sign in with the username and password you specified during instance creation.

  2. Click the Windows start menu and type SQL Server 2014 Manage.

  3. Right-click the result titled SQL Server 2014 Management Studio and select Run as administrator. This opens a Connect to Server dialog window.

  4. Click the Connect button.

  5. Right-click Databases. Select the New database option.

  6. Name the database bookshelf, and then click OK.

Configure the SQL Server

  1. In SQL Server 2014 Management Studio, click the Security folder for the library SQL Server instance.

  2. Right-click the Logins sub-item and select the New login option.

  3. For Login name, enter dotnetapp.

  4. Change the authentication method to SQL Server Authentication.

  5. For Password, enter a password of your choice. Do not enable the Enforce password policy option.

  6. Change the Default Database to be the bookshelf database you just previously created.

  7. Click the User Mapping page in the left side of the New login dialog box:

    • Select the Map checkbox for bookshelf database.

    • Under Database role membership for: bookshelf, check all of the roles except for:

      • db_denydatareader

      • db_denydatawriter

  8. Click OK to create the new database login account.

  9. Because the user you created is set to use SQL Server Authentication, SQL Server now needs to be configured to allow this authentication method. In SQL Server 2014 Management Studio right click the library SQL Server instance you created and select Properties.

  10. Click the Security page under the left side menu of the Server Properties dialog box.

  11. For the Server Authentication setting, select SQL Server and Windows Authentication mode. Then click the OK button.

  12. Restart the SQL Server service by right clicking the library SQL Server instance and selecting Restart.

Create a Firewall Rule for SQL Server

Configure a firewall rule to allow traffic on port 1433 so other clients can connect to the newly created SQL Server instance over the public internet:

  1. In the Developers Console main menu, go to the Firewall rules section.

    Open the Firewall rules

  2. Click the Add firewall rule button.

    • Name the new firewall rule allow-tcp-1433.

    • Set Source Filter to IP Ranges.

    • For Source IP Ranges enter 0.0.0.0/0. This value allows access by all IP addresses.

    • For Allowed protocols and ports enter tcp:1433.
  3. Click the Create button to create the firewall rule.

Configuring settings

  1. In the getting-started-dotnet\aspnet\2-structured-data directory, double click 2-structured-data.sln to open the sample app in Visual Studio.

  2. In the Solution Explorer pane, click Web.config.

  3. In Web.config:

    • Set GoogleCloudSamples:ProjectId to your project ID.

    • Set GoogleCloudSamples:BookStore to sqlserver.

    • Near the bottom of the file, under <connectionStrings>, find the connectionStrings XML sub-element with the attribute name="LocalSqlServer". Update the connectionString value with the external IP address of your SQL Server instance along with the database name and the username and password for signing in, which you created previously. For example, the connectionString for a remote SQL Server on IP 104.155.20.171 with database = bookshelf, user = dotnetapp and password = test looks like this:

      connectionString="Data Source=104.155.20.171;Initial Catalog=bookshelf;Integrated Security=False;User ID=dotnetapp;Password=test;MultipleActiveResultSets=True"

  4. Save and close Web.config.

  5. In the Visual Studio menu, choose Build > Build Solution to build the solution.

  6. Generate the migration scripts that will be used to create the database tables. In the Visual Studio menu, choose Tools > Nuget Package Manager > Package Manager Console. At the PM > prompt, enter this command:

    Add-Migration Init
    
  7. Then enter this command in the Package Manager Console to create the tables within the "bookshelf" SQL Server database that will be used to store the books data for our sample application. At the PM > prompt, enter this command:

    Update-Database
    

Running the app on your local machine

In Visual Studio, press F5 to run the project.

Now you can browse the app's web pages and add, edit, and delete books.

Application structure

This diagram shows the app's components and how they fit together. The app follows the classic ASP.NET MVC pattern. An IBookStore interface between the BooksController and DbBookStore lets you switch to storing book data in Google Cloud Datastore without changing any code.

Bookshelf application structure

Understanding the code

This section walks you through the application code and explains how it works.

The data model

The Book class keeps information about one book. It contains some additional fields that are used in later tutorials:

    [Bind(Include = "Title, Author, PublishedDate, Description")]
    public class Book
    {
        [Key]
        public long Id { get; set; }

        [Required]
        public string Title { get; set; }

        public string Author { get; set; }

        [Display(Name = "Date Published")]
        [DataType(DataType.Date)]
        public DateTime? PublishedDate { get; set; }

        public string ImageUrl { get; set; }

        [DataType(DataType.MultilineText)]
        public string Description { get; set; }

        public string CreatedById { get; set; }
    }

Entity Framework's DbSet converts LINQ queries and Create, Read, Update and Delete (CRUD) operations into SQL queries. The ApplicationDbContext class keeps a DbSet of Books:

public class ApplicationDbContext : DbContext
{
    // ...
    public DbSet<Book> Books { get; set; }

Handling user submissions with forms

The add/edit HTML form allows users to add and edit book submissions:

Image of add/edit Form

The HTML form is created using Razor, templates. This Razor template specifies that the form include text input fields for Title, Author, Date Published and Description:

<form action="/Books/@Model.FormAction/@Model.Book.Id" method="post" id="book-form" enctype="multipart/form-data">
    @Html.AntiForgeryToken()
    <div class="form-group">
        @Html.LabelFor(model => model.Book.Title)
        @Html.EditorFor(model => model.Book.Title, new { htmlAttributes = new { @class = "form-control" } })
        @Html.ValidationMessageFor(model => model.Book.Title, "", new { @class = "text-danger" })
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Book.Author)
        @Html.EditorFor(model => model.Book.Author, new { htmlAttributes = new { @class = "form-control" } })
        @Html.ValidationMessageFor(model => model.Book.Author, "", new { @class = "text-danger" })
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Book.PublishedDate)
        @Html.EditorFor(model => model.Book.PublishedDate, new { htmlAttributes = new { @class = "form-control", @type = "text" } })
        @Html.ValidationMessageFor(model => model.Book.PublishedDate, "", new { @class = "text-danger" })
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Book.Description)
        @Html.EditorFor(model => model.Book.Description, new { htmlAttributes = new { @class = "form-control", @type = "text" } })
        @Html.ValidationMessageFor(model => model.Book.Description, "", new { @class = "text-danger" })
    </div>

    <button type="submit" class="btn btn-success">Save</button>
</form>

Handling form submissions

When a user clicks Add Book, the BooksController.Create() method displays the form. After the user fills in the form and clicks Save, the BooksController.Create() method receives the form's contents and sends them to the SQL Server Database via the IBookStore::Create() method. Note that the Create method is annotated with HttpPost.

        // GET: Books/Create
        public ActionResult Create()
        {
            return ViewForm("Create", "Create");
        }

        // POST: Books/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(Book book)
        {
            if (ModelState.IsValid)
            {
                _store.Create(book);
                return RedirectToAction("Details", new { id = book.Id });
            }
            return ViewForm("Create", "Create", book);
        }

The DbBookStore class invokes the ApplicationDbContext class to perform queries and CRUD operations for data stored in the SQL Server Database. The SQL query is created using an Object Relational Mapper (ORM) called Entity Framework. Object relational mappers let you write data models as simple C# classes, and they generate all the SQL for you.

DbBookStore's CRUD methods, such as Create(), are simple calls to the ApplicationDbContext class.

public void Create(Book book)
{
    var trackBook = _dbcontext.Books.Add(book);
    _dbcontext.SaveChanges();
    book.Id = trackBook.Id;
}

Listing books

After users have added books, clicking the Books link navigates to the /Books page, which lists all the books currently stored in the SQL Server database. The List() method does the work of listing all the books by using data retrieved from the database.

public BookList List(int pageSize, string nextPageToken)
{
    IQueryable<Book> query = _dbcontext.Books.OrderBy(book => book.Id);
    if (nextPageToken != null)
    {
        long previousBookId = long.Parse(nextPageToken);
        query = query.Where(book => book.Id > previousBookId);
    }
    var books = query.Take(pageSize).ToArray();
    return new BookList()
    {
        Books = books,
        NextPageToken = books.Count() == pageSize ? books.Last().Id.ToString() : null
    };
}

The List() method composes a LINQ query that reads books from the Books DbSet. The query gets a little complicated in order to implement paging. The query reads ten books and then stores the Id for the last book in NextPageToken. When the user clicks the More button, the List() method unpacks the nextPageToken to get the Id of the last book and then queries for books with larger IDs.

Send feedback about...