Using Cloud SQL with .NET

This part of the .NET Bookshelf tutorial shows how the sample app stores its persistent data in Google Cloud SQL.

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.

  1. Create a Cloud SQL Second Generation instance. For Instance ID, enter library. In the High availability subsection, uncheck the Create failover replica option and then click Create at the bottom of the page. It might take a few minutes for the instance to be created. When the instance is ready, it appears in the Instances list.

  2. In the Instances list, click your Cloud SQL instance, and then click Access Control.

  3. In the Authorization subsection, under Authorized Networks, click the add (+) button. In the Network field, enter 0.0.0.0/0 to allow access from all IP addresses. Click Done, and then click Save.

  4. In the Users subsection, click Create user account. For User name, enter dotnetapp. For Password, enter a strong password of your choice. Click Create.

  5. Click Databases. Click the New database button. Name the database bookshelf, and then click Add.

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

    • Near the bottom of the file, under <connectionStrings>, find the connectionStrings XML sub-element with the attribute name="LocalMySqlServer". Update the connectionString value with the IP address, username, and password of your Cloud SQL instance.

  4. Save and close Web.config.

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

  6. 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" Cloud SQL database that will be used to store the books data for our sample application:

    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.

Deploying the app to Compute Engine

  1. In Visual Studio, in the Solution Explorer pane, right click 2-structured-data, and choose Publish. Publish application

  2. In the Publish Web dialog, select Custom as your publish target.

  3. In the New Custom Profile dialog, for the profile name, enter bookshelf-profile. Click OK.

  4. Fill out your profile:

    • For Server, enter the external IP address of your Compute Engine instance.

    • For Site name, enter Default Web Site.

    • For User name, enter the username of the Windows user account you created on your Compute Engine instance.

    • For Password, enter the password of the Windows user account you created on your Compute Engine instance.

    • For Destination URL, enter this URL:

      http://[EXTERNAL_IP_ADDRESS_OF_YOUR_COMPUTE_ENGINE_INSTANCE]

  5. Click Validate Connection to ensure that the properties are correct.

  6. Because the Microsoft IIS installation in your deployment uses a self-signed certificate by default, you'll see a Certificate Error during the validation process. Check the box to Save this certificate for future sessions of Visual Studio, and click Accept to accept the certificate.

  7. If your configuration is valid, click Settings. Click File Publish Options, and check Remove additional files at destination. This is important for later steps when you publish new web sites to the same Compute Engine instance.

  8. Click Publish to deploy the sample web application. After publishing completes, Visual Studio opens the application in your default web browser:

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 lives between the BooksController and DbBookStore so that we can 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 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 clicks Save, the BooksController.Create() method receives the form's contents and sends them to the Cloud SQL 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 to perform queries and CRUD operations for data stored in the Cloud SQL 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.

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

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...