Using Cloud SQL with .NET

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

This page is part of a multi-page tutorial. To start from the beginning and read the setup instructions, go to .NET Bookshelf app.

  1. Create a Cloud SQL Second Generation instance.

    Create an instance

    1. For Instance ID, enter library.
    2. At the bottom of the page, click Create. It might take a few minutes to create the instance. When the instance is ready, it appears in the Instances list.
  2. Download and set up the Cloud SQL Proxy to securely access your Cloud SQL Second Generation instance.

    Set up the Cloud SQL Proxy

Configuring settings

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

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

  3. In Web.config, complete the following steps:

    1. Set GoogleCloudSamples:ProjectId to your project ID.

    2. Set GoogleCloudSamples:BookStore to mysql.

    3. 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, click Build, and then click Build Solution to build the solution.

  6. To create the database tables, in the Visual Studio menu, go to Tools > Nuget Package Manager > Package Manager Console. At the PM > prompt, enter the following command:

    Add-Migration Init
    
  7. Create the tables in the Cloud SQL database that are used to store the books' data for the Bookshelf app. In the Package Manager Console, enter the following 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 to 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 click Publish.

    Publish app

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

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

  4. Fill out your profile:

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

    2. For Site name, enter Default Web Site.

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

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

    5. For Destination URL, enter http://[EXTERNAL_IP_ADDRESS_OF_YOUR_COMPUTE_ENGINE_INSTANCE].

      Where:

      • [EXTERNAL_IP_ADDRESS_OF_YOUR_COMPUTE_ENGINE_INSTANCE] represents the external IP address of your Compute Engine instance.
  5. To check your profile credentials and configuration, click Validate Connection.

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

  7. Click Settings.

  8. Click File Publish Options, and select Remove additional files at destination. This is important for later steps when you publish new web sites to the same Compute Engine instance.

  9. To deploy the sample web app, click Publish. After publishing completes, Visual Studio opens the app in your default web browser.

App 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 you can switch to storing your book data in Cloud Datastore without changing any code.

Bookshelf app structure

Understanding the code

This section walks you through the app's code and explains how it works.

The data model

The Book class contains information about one book as well as 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 form lets you add and edit book submissions in the app.

Image of add/edit form

The HTML form is created by using Razor templates. The Razor template specifies that the form includes 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 you click Add Book, the BooksController.Create() method displays the form. After you fill in the form and click Save, the BooksController.Create() method receives the form's contents and sends the contents 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 by 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 books are added, click the Books link to go 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 book's 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 you click 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.

Bu sayfayı yararlı buldunuz mu? Lütfen görüşünüzü bildirin:

Şunun hakkında geri bildirim gönderin...