bucket-sort logo bucket-sort

プログラミングとインフラエンジニアリングの覚え書き

[C#] SQL Server に接続するためのサンプルデータベースを用意する

C# , .NET , Data Access bucket-sort

前回は、ADO.NET の全体像を見ました。

今回は、サンプルコードを動かすためのデータベースを用意します。
以降の記事では、車の在庫を管理する小さなデータベースを使って、接続、検索、追加、更新、削除、トランザクション、一括投入を試します。

SQL Server 環境を用意する

学習用途であれば、SQL Server Developer Edition、SQL Server Express、Docker コンテナーなどを使えます。

Docker を使う場合の例です。

docker run -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=YourStrong!Passw0rd" \
  -p 1433:1433 \
  --name sqlserver-dev \
  -d mcr.microsoft.com/mssql/server:2022-latest

この例では、ローカルの 1433 番ポートで SQL Server へ接続できます。

接続文字列の例です。

Server=localhost,1433;User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=True;

Windows 認証を使う環境では、次のような形になることもあります。

Server=localhost;Database=AutoLot;Trusted_Connection=True;TrustServerCertificate=True;

管理ツールについて

SQL Server へ接続して SQL を実行するには、次のようなツールを使えます。

  • SQL Server Management Studio
  • Azure Data Studio
  • Visual Studio の SQL Server オブジェクトエクスプローラー
  • sqlcmd

どのツールでも構いません。
大切なのは、SQL を実行し、データベース、テーブル、データを確認できることです。

サンプルデータベースを作成する

まず、学習用のデータベースを作ります。

CREATE DATABASE AutoLot;
GO

作成したデータベースへ切り替えます。

USE AutoLot;
GO

すでに存在する場合に作り直したいときは、本番環境ではなく学習環境だけで慎重に実行してください。

DROP DATABASE AutoLot;
GO

車の在庫テーブルを作る

車の在庫を表すテーブルを作ります。

CREATE TABLE Inventory
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Make NVARCHAR(50) NOT NULL,
    Color NVARCHAR(50) NOT NULL,
    PetName NVARCHAR(50) NOT NULL
);
GO

IDENTITY(1,1) は、自動採番される整数列です。
PRIMARY KEY は、行を一意に識別する主キーです。

注文テーブルを作る

在庫と関連する注文テーブルも作ります。

CREATE TABLE Orders
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CarId INT NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CarId は、どの車に対する注文かを表します。

テーブル間の関係を作る

Orders.CarId が Inventory.Id を参照するように、外部キーを作ります。

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Inventory
FOREIGN KEY (CarId)
REFERENCES Inventory(Id);
GO

これにより、存在しない車に対する注文を作れなくなります。
データベース側で整合性を守れるようになります。

テストデータを追加する

在庫テーブルにテストデータを入れます。

INSERT INTO Inventory (Make, Color, PetName)
VALUES
    (N'Honda', N'Blue', N'Vega'),
    (N'Ford', N'Red', N'Rusty'),
    (N'Toyota', N'Black', N'Night'),
    (N'BMW', N'Silver', N'Silver Fox');
GO

注文テーブルにもデータを入れます。

INSERT INTO Orders (CarId, CustomerName)
VALUES
    (1, N'Alice'),
    (2, N'Bob');
GO

確認します。

SELECT * FROM Inventory;
SELECT * FROM Orders;

ストアドプロシージャを作る

車の愛称を取得するストアドプロシージャを作ります。

CREATE PROCEDURE GetPetName
    @carId INT,
    @petName NVARCHAR(50) OUTPUT
AS
BEGIN
    SELECT @petName = PetName
    FROM Inventory
    WHERE Id = @carId;
END;
GO

実行例です。

DECLARE @name NVARCHAR(50);

EXEC GetPetName
    @carId = 1,
    @petName = @name OUTPUT;

SELECT @name AS PetName;

後の記事で、ADO.NET からこのストアドプロシージャを呼び出します。

C# から接続確認する

ADO.NET から接続できるか確認します。

using Microsoft.Data.SqlClient;

string connectionString =
    "Server=localhost,1433;Database=AutoLot;User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=True;";

await using SqlConnection connection = new SqlConnection(connectionString);

await connection.OpenAsync();

Console.WriteLine("接続できました。");

接続できない場合は、次の点を確認します。

  • SQL Server が起動しているか
  • ポート番号が合っているか
  • ユーザー名とパスワードが正しいか
  • データベース名が正しいか
  • TrustServerCertificate=True が必要な環境か

接続文字列をコードに直書きしない

サンプルでは分かりやすくするために接続文字列をコードに書いています。
実務では、設定ファイル、環境変数、ユーザーシークレットなどへ移しましょう。

appsettings.json の例です。

{
  "ConnectionStrings": {
    "AutoLot": "Server=localhost,1433;Database=AutoLot;User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=True;"
  }
}

読み込み側です。

using Microsoft.Extensions.Configuration;

IConfiguration configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json")
    .AddEnvironmentVariables()
    .Build();

string connectionString =
    configuration.GetConnectionString("AutoLot")
    ?? throw new InvalidOperationException("接続文字列がありません。");

パスワードを含む接続文字列は、公開リポジトリへ入れないように注意します。

まとめ

今回は、ADO.NET のサンプルで使う SQL Server データベースを用意しました。
在庫テーブル、注文テーブル、外部キー、テストデータ、ストアドプロシージャまで作成しました。

次回は、データベースプロバイダーを抽象化し、特定の具象型に依存しすぎないデータアクセスの書き方を見ていきます。

Related Posts