C Sharpとデータベース - CRUDの実行

概要

C#でSQL Serverに対して変更処理(INSERT, UPDATE, DELETE)を実行する方法をまとめる。

取得・抽出

パスワードの暗号化、SQLインジェクション対策 (パラメタライズドクエリ) を行うことを推奨する。

データベースから取得した各レコードを任意のクラス (以下の例では、Userクラス) にマッピングすることもできる。

以下の例で使用しているT_USERテーブルの定義を、下表に示す。
下表のテーブルは、システムのユーザ認証・認可に使用される基本的なユーザ情報を管理するためのもである。

パスワードカラムは、セキュリティ上の理由から、平文ではなくハッシュ化された値を保存することが推奨される。
ROLE_NAMEは、アプリケーションで定義された権限レベルを表す。
IDカラムは主キー（Primary Key）として設定され、重複を許可しないものとする。

列名	データ型	NULL許可	キー	説明
ID	VARCHAR(50)	NO	PK	ユーザID 一意の識別子として使用する。
PASSWORD	VARCHAR(100)	NO	-	ユーザのパスワード ハッシュ化された値を格納することを推奨する。
ROLE_NAME	VARCHAR(20)	NO	-	ユーザのロール名 (例: 'ADMIN'、'USER'、'MANAGER'等)

 !-- CREATE TABLE文
 
 CREATE TABLE T_USER (
    ID VARCHAR(50) NOT NULL PRIMARY KEY,
    PASSWORD VARCHAR(100) NOT NULL,
    ROLE_NAME VARCHAR(20) NOT NULL
 );

 public class User
 {
    public string Id       { get; set; }
    public string Password { get; set; }
    public string RoleName { get; set; }
 }

1レコードのみ取得

 public User SelectById(string id)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
    User user = null;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // SQLの準備
          command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID = @ID";
          command.Parameters.Add(new SqlParameter("@ID", id));
 
          // SQLの実行
          using (var reader = command.ExecuteReader())
          {
             // レコードの取得
             if (reader.Read())
             {
                user = new User
                {
                   Id = reader["ID"].ToString(),
                   Password = reader["PASSWORD"].ToString(),
                   RoleName = reader["ROLE_NAME"].ToString()
                };
             }
          }
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          // データベースの接続終了
          connection.Close();
       }
    }
    return user;
 }

全レコードの取得

 public List<User> SelectAll()
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
    var users = new List<User>();
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // SQLの準備
          command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER";
 
          // SQLの実行
          using (var reader = command.ExecuteReader())
          {
             // レコードの取得
             while (reader.Read())
             {
                users.Add(new User
                {
                   Id = reader["ID"].ToString(),
                   Password = reader["PASSWORD"].ToString(),
                   RoleName = reader["ROLE_NAME"].ToString()
                });
             }
          }
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          // データベースの接続終了
          connection.Close();
       }
    }
    return users;
 }

挿入

1レコードのみ

単一テーブルにしか影響しないようなSQLは1レコードのみ実行することになる。
このようなクエリを実行する場合、トランザクションを考慮せずそのままExecuteNonQueryメソッドを実行する方法が簡単である。

 using System;
 using System.Configuration;
 using System.Data.SqlClient;
 
 public void Insert1(string id, string password, string role)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        try
        {
            // データベースの接続開始
            connection.Open();
 
            // SQLの準備
            command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (@ID, @PASSWORD, @ROLE_NAME)";
            command.Parameters.Add(new SqlParameter("@ID", id));
            command.Parameters.Add(new SqlParameter("@PASSWORD", password));
            command.Parameters.Add(new SqlParameter("@ROLE_NAME", role));
 
            // SQLの実行
            command.ExecuteNonQuery();
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
 }

複数レコード

複数のテーブルにINSERT / UPDATE / DELETEを行う場合、トランザクションを利用する場合が多い。

 using System;
 using System.Configuration;
 using System.Data.SqlClient;
 
 public void Insert2(string id, string password, string phone, string address)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    {
        try
        {
            // データベースの接続開始
            connection.Open();
 
            using (var transaction = connection.BeginTransaction())
            using (var command = new SqlCommand() { Connection = connection, Transaction = transaction })
            {
                try
                {
                    // 親テーブルを挿入するSQLの準備
                    command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (@ID, @PASSWORD)";
                    command.Parameters.Add(new SqlParameter("@ID", id));
                    command.Parameters.Add(new SqlParameter("@PASSWORD", password));
 
                    // 親テーブルを挿入するSQLの実行
                    command.ExecuteNonQuery();
 
                    // 子テーブルを挿入するSQLの準備
                    command.CommandText = @"INSERT INTO T_USER_EXT (ID, PHONE, ADDRESS) VALUES (@ID, @PHONE, @ADDRESS)";
                    ////command.Parameters.Add(new SqlParameter("@ID", id));         // <- 上で既に @ID を追加済みのため再投入は不要。
                    command.Parameters.Add(new SqlParameter("@PHONE", phone));
                    command.Parameters.Add(new SqlParameter("@ADDRESS", address));
 
                    // 子テーブルを挿入するSQLの実行
                    command.ExecuteNonQuery();
 
                    // コミット
                    transaction.Commit();
                }
                catch
                {
                    // ロールバック
                    transaction.Rollback();
                    throw;
                }
            }
        }
        catch (Exception exception)
        {
            Console.WriteLine(exception.Message);
            throw;
        }
        finally
        {
            // データベースの接続終了
            connection.Close();
        }
    }
 }

その他のクエリ

INSERT

INSERT INTO T_USER(ID, PASSWORD) VALUES(@ID, @PASSWORD);

UPDATE

UPDATE T_USER SET PASSWORD=@PASSWORD WHERE ID=@ID;

DELETE

DELETE FROM T_USER WHERE ID=@ID;

パラメタライズドクエリに関する注意

ORDER BY句

ORDER BY句ではパラメータを直接使用できない。
これは多くのデータベースシステムの制限である。

ORDER BY句を指定する場合は、カラム名やカラムの位置 (数値) を指定する必要がある。

ソートの順序を動的に変更する場合は、"プログラム側で条件分岐する方法"、あるいは、"動的SQLを使用する方法"がある。

 // プログラム側で条件分岐する方法
 
 public List<User> SelectByRole(string roleName, string sortColumn, bool isAscending)
 {
    var sql = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ROLE_NAME = @ROLE_NAME ";
 
    // ソート列を検証 (SQLインジェクション対策)
    switch (sortColumn.ToUpper())
    {
       case "ID":
          sql += "ORDER BY ID ";
          break;
       case "ROLE_NAME":
          sql += "ORDER BY ROLE_NAME ";
          break;
       default:
          sql += "ORDER BY ID "; // デフォルトのソート
          break;
    }
 
    sql += isAscending ? "ASC" : "DESC";
 
    // 以下、通常のデータベース処理
    // ...略
 }

 // 動的SQLの構築
 
 // SQLインジェクションのリスクに注意する
 // パフォーマンスへの影響を考慮する
 
 var sql = @"
             SELECT ID, PASSWORD, ROLE_NAME 
             FROM T_USER 
             WHERE ROLE_NAME = @ROLE_NAME 
             ORDER BY 
                CASE @SortDirection 
                   WHEN 'ASC' THEN
                      CASE @SortColumn
                         WHEN 'ID' THEN ID
                         WHEN 'ROLE_NAME' THEN ROLE_NAME
                         ELSE ID
                      END
                END ASC,
                CASE @SortDirection 
                   WHEN 'DESC' THEN
                      CASE @SortColumn
                         WHEN 'ID' THEN ID
                         WHEN 'ROLE_NAME' THEN ROLE_NAME
                         ELSE ID
                      END
                   END DESC";
 
 command.CommandText = sql;
 command.Parameters.Add(new SqlParameter("@ROLE_NAME", roleName));
 command.Parameters.Add(new SqlParameter("@SortColumn", sortColumn));
 command.Parameters.Add(new SqlParameter("@SortDirection", sortDirection));