MochiuWiki : SUSE, EC, PCB

「C Sharpとデータベース - CRUDの実行」の版間の差分

📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

← 古い編集
ビジュアルウィキテキスト
編集の要約なし
 
(同じ利用者による、間の8版が非表示)
1行目: 1行目:
== 概要 ==
== 概要 ==
C#でSQL Serverに対して変更処理(INSERT, UPDATE, DELETE)を実行する方法をまとめる。<br><br>
C#でSQL Serverに対して変更処理(INSERT, UPDATE, DELETE)を実行する方法をまとめる。<br>
<br><br>


== 1行のみ実行 ==
== 取得・抽出 ==
単一テーブルにしか影響しないようなSQLは1行だけ実行することになる。<br>
パスワードの暗号化、SQLインジェクション対策 (パラメタライズドクエリ) を行うことを推奨する。<br>
このようなクエリを実行する場合、トランザクションを考慮せずそのままExecuteNonQuery()メソッドを実行する方法が簡単である。<br>
<br>
データベースから取得した各レコードを任意のクラス (以下の例では、Userクラス) にマッピングすることもできる。<br>
<br>
以下の例で使用しているT_USERテーブルの定義を、下表に示す。<br>
下表のテーブルは、システムのユーザ認証・認可に使用される基本的なユーザ情報を管理するためのもである。<br>
<br>
パスワードカラムは、セキュリティ上の理由から、平文ではなくハッシュ化された値を保存することが推奨される。<br>
ROLE_NAMEは、アプリケーションで定義された権限レベルを表す。<br>
IDカラムは主キー（Primary Key）として設定され、重複を許可しないものとする。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
! style="background-color:#66CCFF;" | 列名
! style="background-color:#66CCFF;" | データ型
! style="background-color:#66CCFF;" | NULL許可
! style="background-color:#66CCFF;" | キー
! style="background-color:#66CCFF;" | 説明
|-
| ID || VARCHAR(50) || NO || PK || ユーザID<br>一意の識別子として使用する。
|-
| PASSWORD || VARCHAR(100) || NO || - || ユーザのパスワード<br>ハッシュ化された値を格納することを推奨する。
|-
| ROLE_NAME || VARCHAR(20) || NO || - || ユーザのロール名<br>(例: 'ADMIN'、'USER'、'MANAGER'等)
|}
</center>
<br>
<syntaxhighlight lang="sql">
!-- CREATE TABLE文
CREATE TABLE T_USER (
    ID VARCHAR(50) NOT NULL PRIMARY KEY,
    PASSWORD VARCHAR(100) NOT NULL,
    ROLE_NAME VARCHAR(20) NOT NULL
);
</syntaxhighlight>
<br>
<syntaxhighlight lang="c#">
public class User
{
    public string Id      { get; set; }
    public string Password { get; set; }
    public string RoleName { get; set; }
}
</syntaxhighlight>
<br>
==== 1レコードのみ取得 ====
<syntaxhighlight lang="c#">
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;
}
</syntaxhighlight>
<br>
==== 全レコードの取得 ====
<syntaxhighlight lang="c#">
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;
}
</syntaxhighlight>
<br><br>


== 挿入 ==
==== 1レコードのみ ====
単一テーブルにしか影響しないようなSQLは1レコードのみ実行することになる。<br>
このようなクエリを実行する場合、トランザクションを考慮せずそのままExecuteNonQueryメソッドを実行する方法が簡単である。<br>
<br>
<syntaxhighlight lang="c#">
  using System;
  using System;
  using System.Configuration;
  using System.Configuration;
45行目: 194行目:
     }
     }
  }
  }
</syntaxhighlight>
<br>
<br>
 
==== 複数レコード ====
== トランザクション処理 ==
複数のテーブルにINSERT / UPDATE / DELETEを行う場合、トランザクションを利用する場合が多い。<br>
複数のテーブルにINSERT / UPDATE / DELETEを行う場合、トランザクションを利用する場合が多い。<br>
 
<syntaxhighlight lang="c#">
  using System;
  using System;
  using System.Configuration;
  using System.Configuration;
111行目: 260行目:
     }
     }
  }
  }
</syntaxhighlight>
<br><br>
<br><br>


== その他のクエリ ==
== 更新 ==
'''INSERT'''
UPDATE文を使用する場合、WHERE句を必ず指定することが推奨される。<br>
  INSERT INTO T_USER(ID, PASSWORD) VALUES(@ID, @PASSWORD);
指定しない場合は、全てのレコードが更新されることに注意する。<br>
<br>
また、更新対象のレコードが存在するかどうか確認することが推奨される。<br>
<br>
==== 1レコードのみ更新 ====
<syntaxhighlight lang="c#">
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();
      }
    }
}
</syntaxhighlight>
<br>
==== 複数レコードの更新 ====
<syntaxhighlight lang="c#">
public void UpdateUsersByRole(string oldRole, string newRole)
{
    // 接続文字列の取得
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;


'''UPDATE'''
    using (var connection = new SqlConnection(connectionString))
  UPDATE T_USER SET PASSWORD=@PASSWORD WHERE ID=@ID;
    {
      // トランザクションの宣言
      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();
          }
      }
    }
}
</syntaxhighlight>
<br>
<syntaxhighlight lang="c#">
// 一般的な使用例
try
{
    var userService = new UserService();
    userService.UpdateUsersByRole("USER", "PREMIUM_USER");
}
catch (Exception ex)
{
    // エラー処理
    Console.WriteLine($"更新処理に失敗 : {ex.Message}");
}
</syntaxhighlight>
<br>
また、TransactionScopeクラスを使用することにより、宣言的なトランザクション管理が可能となる。<br>
<br>
<syntaxhighlight lang="c#">
// トランザクションスコープを使用した使用例
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;
          }
      }
    }
}
</syntaxhighlight>
<br><br>


== その他のクエリ ==
'''DELETE'''
'''DELETE'''
  DELETE FROM T_USER WHERE ID=@ID;
  DELETE FROM T_USER WHERE ID=@ID;
<br><br>
== パラメタライズドクエリに関する注意 ==
==== ORDER BY句 ====
ORDER BY句ではパラメータを直接使用できない。<br>
これは多くのデータベースシステムの制限である。<br>
<br>
ORDER BY句を指定する場合は、カラム名やカラムの位置 (数値) を指定する必要がある。<br>
<br>
ソートの順序を動的に変更する場合は、<u>"プログラム側で条件分岐する方法"</u>、あるいは、<u>"動的SQLを使用する方法"</u>がある。<br>
<br>
<syntaxhighlight lang="c#">
// プログラム側で条件分岐する方法
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";
    // 以下、通常のデータベース処理
    // ...略
}
</syntaxhighlight>
<br>
<syntaxhighlight lang="c#">
// 動的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));
</syntaxhighlight>
<br><br>
<br><br>




[[カテゴリ:C_Sharp]]
[[カテゴリ:C_Sharp]]
「http://mochiuwiki.e2.valueserver.jp/index.php?title=C_Sharpとデータベース_-_CRUDの実行」から取得