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

更新

UPDATE文を使用する場合、WHERE句を必ず指定することが推奨される。
指定しない場合は、全てのレコードが更新されることに注意する。

また、更新対象のレコードが存在するかどうか確認することが推奨される。

1レコードのみ更新

 public void UpdateUser(string id, string password, string roleName)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // SQLの準備
          command.CommandText = @"UPDATE T_USER SET PASSWORD = @PASSWORD, ROLE_NAME = @ROLE_NAME WHERE ID = @ID";
          command.Parameters.Add(new SqlParameter("@ID", id));
          command.Parameters.Add(new SqlParameter("@PASSWORD", password));
          command.Parameters.Add(new SqlParameter("@ROLE_NAME", roleName));
 
          // SQLの実行
          var affectedRows = command.ExecuteNonQuery();
 
          // 更新対象のレコードが存在しない場合
          if (affectedRows == 0)
          {
             throw new Exception($"ユーザID {id} が存在しません");
          }
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

複数レコードの更新

 public void UpdateUsersByRole(string oldRole, string newRole)
 {
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;

    using (var connection = new SqlConnection(connectionString))
    {
       // トランザクションの宣言
       SqlTransaction transaction = null;
 
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // トランザクションの開始
          transaction = connection.BeginTransaction();
 
          using (var command = connection.CreateCommand())
          {
             // コマンドにトランザクションを設定
             command.Transaction = transaction;
 
             // 更新対象の件数を確認
             command.CommandText = @"SELECT COUNT(*) FROM T_USER WHERE ROLE_NAME = @OLD_ROLE";
             command.Parameters.Add(new SqlParameter("@OLD_ROLE", oldRole));
 
             int targetCount = (int)command.ExecuteScalar();
             if (targetCount == 0)
             {
                throw new Exception($"No users found with role: {oldRole}");
             }
 
             // パラメータの追加
             command.Parameters.Add(new SqlParameter("@NEW_ROLE", newRole));
 
             // UPDATE文の実行
             command.CommandText = @"UPDATE T_USER SET ROLE_NAME = @NEW_ROLE WHERE ROLE_NAME = @OLD_ROLE";
 
             var affectedRows = command.ExecuteNonQuery();
 
             // 更新件数の確認
             if (affectedRows != targetCount)
             {
                throw new Exception($"更新予定件数は {targetCount} 件でしたが、実際の更新件数は {affectedRows} 件でした");
             }
 
             // 必要に応じて他のテーブルの更新等を実行
             // ...略
 
             // 全ての処理が成功した場合はコミット
             transaction.Commit();
          }
       }
       catch (Exception exception)
       {
          Console.WriteLine($"エラーが発生 : {exception.Message}");
 
          try
          {
             // エラーが発生した場合はロールバック
             if (transaction != null)
             {
                transaction.Rollback();
                Console.WriteLine("Transaction rolled back.");
             }
          }
          catch (Exception rollbackException)
          {
             Console.WriteLine($"Rollback failed: {rollbackException.Message}");
          }
 
          throw;  // 元の例外を再スロー
       }
       finally
       {
          // 接続のクローズ
          if (connection.State == System.Data.ConnectionState.Open)
          {
             connection.Close();
          }
       }
    }
 }

 // 一般的な使用例
 
 try
 {
    var userService = new UserService();
    userService.UpdateUsersByRole("USER", "PREMIUM_USER");
 }
 catch (Exception ex)
 {
    // エラー処理
    Console.WriteLine($"更新処理に失敗 : {ex.Message}");
 }

また、TransactionScopeクラスを使用することにより、宣言的なトランザクション管理が可能となる。

 // トランザクションスコープを使用した使用例
 
 public void UpdateUsersByRoleWithTransactionScope(string oldRole, string newRole)
 {
    using (var scope = new TransactionScope())
    {
       // 接続文字列の取得
       var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
       using (var connection = new SqlConnection(connectionString))
       {
          try
          {
             // データベースの接続開始
             connection.Open();
 
             // SQLの準備
             using (var command = connection.CreateCommand())
             {
                command.CommandText = @"UPDATE T_USER SET ROLE_NAME = @NEW_ROLE WHERE ROLE_NAME = @OLD_ROLE";
                command.Parameters.Add(new SqlParameter("@OLD_ROLE", oldRole));
                command.Parameters.Add(new SqlParameter("@NEW_ROLE", newRole));
 
                // SQLの実行
                var affectedRows = command.ExecuteNonQuery();
                if (affectedRows == 0)
                {
                    throw new Exception($"{oldRole} を持つユーザが存在しません");
                }
             }
 
             // 全ての処理が成功した場合のみコミット
             scope.Complete();
          }
          catch
          {  // エラーが発生した場合は自動的にロールバック
             throw;
          }
       }
    }
 }

その他のクエリ

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