.NET での SQL Server の使用

.NET Bookshelf チュートリアルのこのパートでは、Google Compute Engine VM で実行される Microsoft SQL Server にサンプルアプリがどのように永続データを保存するかを説明します。

このページは複数ページからなるチュートリアルの一部です。最初からの説明や設定手順を確認するには、.NET Bookshelf アプリに移動してください。

SQL Server インスタンスの作成

  1. 新しい Compute Engine インスタンスを作成します。

    [VM インスタンス] ページに移動

  2. [インスタンス ID] に「library」と入力します。

  3. [ゾーン] で [us-west1-a] を選択します。

  4. [ブートディスク] セクションで、[変更] をクリックします。

  5. [アプリケーション イメージ] タブを選択し、[Windows Server 2012 R2 上の SQL Server 2014 Standard] イメージを選択して、[選択] ボタンをクリックします。

  6. VM インスタンスを作成するには、[作成] をクリックします。

  7. 準備ができたら、インスタンスの名前をクリックします。インスタンスを使用できるようになるまで数分かかることがあります。インスタンスの準備ができると、インスタンス リストにそれが表示されます。

  8. [インスタンスの詳細] ページで、[Windows パスワードを設定] をクリックします。

  9. インスタンス上にユーザー アカウントを作成するには、任意のユーザー名を入力してから、[設定] をクリックします。表示されたパスワードを書き留めて、ダイアログを閉じます。

SQL Server データベースの作成

  1. VM インスタンス リストで、SQL Server インスタンスの横にある [RDP] リンクをクリックします。インスタンスの作成時に設定したユーザー名とパスワードを使ってログインします。

  2. Windows の [スタート] メニューで、「SQL Server 2014 Manage」と入力します。

  3. [SQL Server 2014 Management Studio] を右クリックし、[管理者として実行] を選択します。

  4. [サーバーに接続] ウィンドウで、[接続] をクリックします。

  5. [データベース] を右クリックし、[新しいデータベース] を選択します。

  6. データベースに「bookshelf」という名前を付け、[OK] をクリックします。

SQL Server の構成

  1. SQL Server 2014 Management Studio で、library SQL Server インスタンスの [Security] フォルダをクリックします。

  2. [ログイン] を右クリックし、[新しいログイン] を選択します。

  3. [ログイン名] に「dotnetapp」と入力します。

  4. [認証] 方法で、[SQL Server 認証] をクリックします。

  5. [パスワード] には、任意のパスワードを入力します。[パスワード ポリシーを適用する] オプションを有効にしないでください。

  6. [デフォルト データベース] を、上記の手順で作成した bookshelf データベースに変更します。

  7. [新しいログイン] ダイアログの左側で、[ユーザー マッピング] をクリックして、次の手順を実行します。

    1. bookshelf データベースの [マップ] チェックボックスをオンにします。

    2. [データベース ロールのメンバーシップ: bookshelf] で、以下を除くすべての役割をクリックします。

      • db_denydatareader

      • db_denydatawriter

  8. 新しいデータベース ログイン アカウントを作成するには、[OK] をクリックします。

  9. 作成したユーザーは SQL Server 認証を使用するように設定されているので、この認証方法を許可するように SQL Server を構成する必要があります。[SQL Server 2014 Management Studio] で、作成した SQL Server インスタンス library を右クリックし、[プロパティ] を選択します。

  10. [サーバーのプロパティ] ダイアログの左側のメニューで、[セキュリティ] をクリックします。

  11. [サーバー認証] 設定の [SQL Server 認証モードと Windows 認証モード] を選択してから、[OK] をクリックします。

  12. SQL Server サービスを再起動するには、SQL Server インスタンス Library を右クリックし、[再起動] を選択します。

SQL Server のファイアウォール ルールの作成

ポート 1433 上のトラフィックを許可するようにファイアウォール ルールを構成します。これにより他のクライアントは、新規作成した SQL Server インスタンスにインターネット経由で接続できるようになります。

  1. Google Cloud Platform Console で、[ファイアウォール ルール] セクションに移動します。

    [ファイアウォール ルール] を開く

  2. [ファイアウォール ルールを追加] をクリックし、次のフィールドに値を入力します。

    1. 新しい [ファイアウォール ルール名] として、「allow-tcp-1433」と入力します。

    2. [ソースフィルタ] で [IP 範囲] を選択します。

    3. すべての IP アドレスへのアクセスを許可するには、[ソース IP の範囲] に「0.0.0.0/0」と入力します。

    4. [許可対象プロトコル / ポート] に「tcp:1433」と入力します。

  3. ファイアウォール ルールを作成するには、[作成] をクリックします。

設定の構成

  1. Visual Studio でサンプルアプリを開くには、getting-started-dotnet\aspnet\2-structured-data ディレクトリで、2-structured-data.sln をダブルクリックします。

  2. [ソリューション エクスプローラ] パネルで、Web.config をクリックします。

  3. Web.config で次の操作を行います。

    1. GoogleCloudSamples:ProjectId を実際のプロジェクト ID に設定します。

    2. GoogleCloudSamples:BookStoresqlserver に設定します。

    3. ファイルの下の方にある <connectionStrings> で、name="LocalSqlServer" 属性が指定されている connectionStrings XML サブ要素を見つけ出します。connectionString の値を、SQL Server インスタンスの IP アドレス、データベース名、ユーザー名、パスワードで更新します。たとえば、database = bookshelfuser = dotnetapppassword = test を使用する IP 104.155.20.171 上のリモート SQL Server の connectionString は、次のようになります。

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

  4. Web.config を保存して閉じます。

  5. ソリューションをビルドするには、Visual Studio メニューで [ビルド] をクリックし、[ソリューションのビルド] を選択します。

  6. データベース テーブルを作成するには、Visual Studio のメニューで [ツール] > [Nuget パッケージ マネージャー] > [パッケージ マネージャー コンソール] を選択します。PM > プロンプトで、次のコマンドを入力します。

    Add-Migration Init
    
  7. Bookshelf アプリの書籍のデータを格納するために使われるテーブルを SQL Server データベースの中に作成します。パッケージ マネージャー コンソールで、次のコマンドを入力します。

    Update-Database
    

ローカルマシンでのアプリの実行

Visual Studio で F5 キーを押してプロジェクトを実行します。これでアプリのウェブページを閲覧し、書籍の追加、編集、削除を行えるようになります。

アプリの構造

この図は、アプリを構成するコンポーネントと、互いの関係を示しています。このアプリは、標準的な ASP.NET MVC のパターンに従っています。BooksControllerDbBookStore の間に IBookStore インターフェースが存在するので、コードを変更せずに書籍データの保管先を Cloud Datastore に切り替えることができます。

Bookshelf アプリの構造

コードの説明

このセクションでは、アプリのコードとその動作を順を追って説明します。

データモデル

Book クラスには、1 つの書籍に関する情報と、後のチュートリアルで使用される追加のフィールドが含まれています。

    [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 の DbSet は、LINQ クエリおよび CreateReadUpdateDelete(CRUD)オペレーションを SQL クエリに変換します。ApplicationDbContext クラスは、書籍からなる DbSet を保持します。

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

ユーザーがフォームで送信した内容を処理する

追加 / 編集 HTML フォームを使用すると、書籍情報の送信内容を追加、編集できます。

追加 / 編集フォームの画像

HTML フォームは、Razor テンプレートを使用して作成されます。この Razor テンプレートは、フォームの中にタイトル、著者名、出版日、説明のテキスト入力フィールドを含めることを指定します。

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

フォーム送信を処理する

[Add Book] をクリックすると、BooksController.Create() メソッドによってフォームが表示されます。フォームに値を入力して [Save] をクリックすると、BooksController.Create() メソッドがフォームの内容を受け取り、IBookStore::Create() メソッドを介してそれを SQL Server データベースに送信します。なお Create メソッドには 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);
        }

DbBookStore クラスは ApplicationDbContext クラスを呼び出し、SQL Server データベースに保存されているデータに対してクエリや CRUD オペレーションを実行します。SQL クエリは、Entity Framework と呼ばれるオブジェクト リレーショナル マッパー(ORM)を使用して作成されます。ORM を使用すると、データモデルを単純な C# クラスとして書くことができ、すべての SQL が自動的に生成されます。

DbBookStore の CRUD メソッド(Create() など)は、ApplicationDbContext クラスに対する単純な呼び出しです。

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

書籍の一覧表示

書籍を追加したら、[Books] リンクをクリックして /Books ページに移動します。このページに、現在 SQL Server データベースに保存されているすべての書籍が一覧表示されます。List() メソッドは、データベースから取得したデータを使って、すべての書籍を一覧表示します。

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
    };
}

List() メソッドは、書籍の DbSet から書籍を読み取る LINQ クエリを構成します。ページングを実装するために、このクエリは少し複雑になっています。まず、クエリによって 10 冊の書籍が読み出され、最後の書籍の IdNextPageToken に格納されます。ユーザーが [More] ボタンをクリックすると、List() メソッドが nextPageToken を展開し、最後の書籍の Id を取得して、それより大きい ID を持つ書籍を照会します。

このページは役立ちましたか?評価をお願いいたします。