C Sharpとデータベース - CRUDの実行
提供: MochiuWiki : SUSE, EC, PCB
概要
C#でADO.NETを使用してデータベースに対するCRUD (Create / Read / Update / Delete) 操作を実行することができる。
各DBMS (SQL Server, Oracle Database, MySQL) に対応するデータプロバイダを使用することにより、それぞれのデータベースに対して統一的なパターンでCRUD操作を実装できる。
各DBMSに対応するデータプロバイダを下表に示す。
| DBMS | データプロバイダ | 主な接続クラス |
|---|---|---|
| SQL Server | System.Data.SqlClient または Microsoft.Data.SqlClient |
SqlConnection, SqlCommand, SqlDataReader |
| Oracle Database | Oracle.ManagedDataAccess.Client | OracleConnection, OracleCommand, OracleDataReader |
| MySQL | MySqlConnector または MySql.Data |
MySqlConnection, MySqlCommand, MySqlDataReader |
下表に、CRUD操作で使用する主なメソッドを示す。
| メソッド | 用途 |
|---|---|
| ExecuteNonQuery | INSERT / UPDATE / DELETE 文の実行 影響を受けた行数を返す。 |
| ExecuteReader | SELECT文の実行 複数レコードを逐次的に読み取る場合に使用する。 |
| ExecuteScalar | 単一の値を取得する場合に使用する。 COUNT(*)等の集計クエリに適している。 |
SQLインジェクション攻撃を防ぐために、パラメタライズドクエリの使用は必須である。
各DBMSのパラメータ形式は異なり、SQL Server / MySQL では @パラメータ名 形式、Oracle Database では :パラメータ名 形式を使用する。
複数のテーブルを更新する場合や、複数の操作を一括で行う場合は、トランザクション制御 (BeginTransaction / Commit / Rollback) を適切に行う必要がある。
トランザクションを使用することにより、途中でエラーが発生した場合にロールバックして、データの一貫性を保つことができる。
本ページのサンプルコードでは、下表のT_USERテーブルを使用する。
T_USERテーブルは、システムのユーザ認証・認可に使用される基本的なユーザ情報を管理するためのテーブルである。
| 列名 | データ型 | NULL許可 | キー | 説明 |
|---|---|---|---|---|
| ID | VARCHAR(50) | NO | PK | ユーザID 一意の識別子として使用する。 |
| PASSWORD | VARCHAR(100) | NO | - | ユーザのパスワード ハッシュ化された値を格納することを推奨する。 |
| ROLE_NAME | VARCHAR(20) | NO | - | ユーザのロール名 (例: 'ADMIN'、'USER'、'MANAGER'等) |
SQL Server
取得・抽出
パスワードの暗号化、SQLインジェクション対策 (パラメタライズドクエリ) を行うことを推奨する。
データベースから取得した各レコードを任意のクラス (以下の例では、Userクラス) にマッピングすることもできる。
以下の例で使用しているT_USERテーブルの定義を示す。
T_USERテーブルは、システムのユーザ認証・認可に使用される基本的なユーザ情報を管理するためのものである。
-- CREATE TABLE文
CREATE TABLE T_USER (
ID VARCHAR(50) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR(100) NOT NULL,
ROLE_NAME VARCHAR(20) NOT NULL
);
パスワードカラムは、セキュリティ上の理由から、平文ではなくハッシュ化された値を保存することが推奨される。
ROLE_NAMEは、アプリケーションで定義された権限レベルを表す。
IDカラムは主キー (Primary Key) として設定され、重複を許可しないものとする。
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文を使用する場合、WHERE句を必ず指定することが推奨される。
指定しない場合は、全てのレコードが削除されることに注意する。
DELETE FROM T_USER WHERE ID=@ID;
Oracle Database
Oracle Databaseへの接続には、Oracle.ManagedDataAccess.Client 名前空間を使用する。
NuGetパッケージ Oracle.ManagedDataAccess.Core をプロジェクトに追加することで利用できる。
Oracle Database向けのT_USERテーブル定義を以下に示す。
-- CREATE TABLE文 (Oracle Database)
CREATE TABLE T_USER (
ID VARCHAR2(50) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR2(100) NOT NULL,
ROLE_NAME VARCHAR2(20) NOT NULL
);
Oracle Database固有の注意事項を以下に示す。
- パラメータは、
:パラメータ名形式 (コロン) を使用する。- SQL Server / MySQL の
@パラメータ名形式とは異なる。
- SQL Server / MySQL の
command.BindByName = true;を設定することで、パラメータ名による紐付けが有効になる。- 設定しない場合は、パラメータの追加順序で紐付けが行われる。
取得・抽出
1レコードのみ取得
using Oracle.ManagedDataAccess.Client;
public User SelectById(string id)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
User user = null;
using (var connection = new OracleConnection(connectionString))
using (var command = connection.CreateCommand())
{
try
{
// データベースの接続開始
connection.Open();
// パラメータ名による紐付けを有効化
command.BindByName = true;
// SQLの準備
command.CommandText = "SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ID = :ID";
command.Parameters.Add(new OracleParameter(":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 (OracleException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
return user;
}
全レコードの取得
using Oracle.ManagedDataAccess.Client;
public List<User> SelectAll()
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
var users = new List<User>();
using (var connection = new OracleConnection(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 (OracleException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
return users;
}
挿入
1レコードのみ
using Oracle.ManagedDataAccess.Client;
public void Insert(string id, string password, string role)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
using (var connection = new OracleConnection(connectionString))
using (var command = connection.CreateCommand())
{
try
{
// データベースの接続開始
connection.Open();
// パラメータ名による紐付けを有効化
command.BindByName = true;
// SQLの準備
command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD, ROLE_NAME) VALUES (:ID, :PASSWORD, :ROLE_NAME)";
command.Parameters.Add(new OracleParameter(":ID", id));
command.Parameters.Add(new OracleParameter(":PASSWORD", password));
command.Parameters.Add(new OracleParameter(":ROLE_NAME", role));
// SQLの実行
command.ExecuteNonQuery();
}
catch (OracleException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
複数レコード
複数のテーブルにINSERT / UPDATE / DELETEを行う場合、OracleTransactionを利用する。
トランザクション処理のパターンは、SQL Serverとほぼ同様である。
using Oracle.ManagedDataAccess.Client;
public void InsertWithTransaction(string id, string password, string phone, string address)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
using (var connection = new OracleConnection(connectionString))
{
try
{
// データベースの接続開始
connection.Open();
using (var transaction = connection.BeginTransaction())
using (var command = connection.CreateCommand())
{
// トランザクションの設定
command.Transaction = transaction;
command.BindByName = true;
try
{
// 親テーブルを挿入するSQLの準備
command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (:ID, :PASSWORD)";
command.Parameters.Add(new OracleParameter(":ID", id));
command.Parameters.Add(new OracleParameter(":PASSWORD", password));
// 親テーブルを挿入するSQLの実行
command.ExecuteNonQuery();
// 子テーブルを挿入するSQLの準備
command.CommandText = @"INSERT INTO T_USER_EXT (ID, PHONE, ADDRESS) VALUES (:ID, :PHONE, :ADDRESS)";
command.Parameters.Add(new OracleParameter(":PHONE", phone));
command.Parameters.Add(new OracleParameter(":ADDRESS", address));
// 子テーブルを挿入するSQLの実行
command.ExecuteNonQuery();
// コミット
transaction.Commit();
}
catch
{
// ロールバック
transaction.Rollback();
throw;
}
}
}
catch (OracleException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
更新
1レコードのみ更新
UPDATE文を使用する場合、WHERE句を必ず指定することが推奨される。
affectedRowsで更新件数を確認することにより、対象レコードの存在チェックを行うことができる。
using Oracle.ManagedDataAccess.Client;
public void UpdateUser(string id, string password, string roleName)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
using (var connection = new OracleConnection(connectionString))
using (var command = connection.CreateCommand())
{
try
{
// データベースの接続開始
connection.Open();
// パラメータ名による紐付けを有効化
command.BindByName = true;
// SQLの準備
command.CommandText = @"UPDATE T_USER SET PASSWORD = :PASSWORD, ROLE_NAME = :ROLE_NAME WHERE ID = :ID";
command.Parameters.Add(new OracleParameter(":ID", id));
command.Parameters.Add(new OracleParameter(":PASSWORD", password));
command.Parameters.Add(new OracleParameter(":ROLE_NAME", roleName));
// SQLの実行
var affectedRows = command.ExecuteNonQuery();
// 更新対象のレコードが存在しない場合
if (affectedRows == 0)
{
throw new Exception($"ユーザID {id} が存在しません");
}
}
catch (OracleException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
削除
DELETE文を使用する場合、WHERE句を必ず指定することが推奨される。
指定しない場合は、全てのレコードが削除されることに注意する。
DELETE FROM T_USER WHERE ID = :ID;
MySQL
MySQLへの接続には、MySqlConnector 名前空間を使用する。
NuGetパッケージ MySqlConnector をプロジェクトに追加することで利用できる。
MySQL向けのT_USERテーブル定義を以下に示す。
-- CREATE TABLE文 (MySQL)
CREATE TABLE T_USER (
ID VARCHAR(50) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR(100) NOT NULL,
ROLE_NAME VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
MySQL固有の注意事項を以下に示す。
- パラメータは、
@パラメータ名形式 (アットマーク) を使用する。- SQL Serverと同じ形式であるが、Oracleとは異なる。
- トランザクションを使用する場合、テーブルのストレージエンジンが InnoDBである必要がある。
- MyISAMエンジンではトランザクションがサポートされない。
取得・抽出
1レコードのみ取得
using MySqlConnector;
public User SelectById(string id)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
User user = null;
using (var connection = new MySqlConnection(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 MySqlParameter("@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 (MySqlException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
return user;
}
全レコードの取得
using MySqlConnector;
public List<User> SelectAll()
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
var users = new List<User>();
using (var connection = new MySqlConnection(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 (MySqlException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
return users;
}
挿入
1レコードのみ
using MySqlConnector;
public void Insert(string id, string password, string role)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
using (var connection = new MySqlConnection(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 MySqlParameter("@ID", id));
command.Parameters.Add(new MySqlParameter("@PASSWORD", password));
command.Parameters.Add(new MySqlParameter("@ROLE_NAME", role));
// SQLの実行
command.ExecuteNonQuery();
}
catch (MySqlException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
複数レコード
複数のテーブルにINSERT / UPDATE / DELETEを行う場合、MySqlTransactionを利用する。
InnoDBエンジンを使用している場合のみ、トランザクションが有効である点に注意する。
using MySqlConnector;
public void InsertWithTransaction(string id, string password, string phone, string address)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
using (var connection = new MySqlConnection(connectionString))
{
try
{
// データベースの接続開始
connection.Open();
using (var transaction = connection.BeginTransaction())
using (var command = connection.CreateCommand())
{
// トランザクションの設定
command.Transaction = transaction;
try
{
// 親テーブルを挿入するSQLの準備
command.CommandText = @"INSERT INTO T_USER (ID, PASSWORD) VALUES (@ID, @PASSWORD)";
command.Parameters.Add(new MySqlParameter("@ID", id));
command.Parameters.Add(new MySqlParameter("@PASSWORD", password));
// 親テーブルを挿入するSQLの実行
command.ExecuteNonQuery();
// 子テーブルを挿入するSQLの準備
command.CommandText = @"INSERT INTO T_USER_EXT (ID, PHONE, ADDRESS) VALUES (@ID, @PHONE, @ADDRESS)";
command.Parameters.Add(new MySqlParameter("@PHONE", phone));
command.Parameters.Add(new MySqlParameter("@ADDRESS", address));
// 子テーブルを挿入するSQLの実行
command.ExecuteNonQuery();
// コミット
transaction.Commit();
}
catch
{
// ロールバック
transaction.Rollback();
throw;
}
}
}
catch (MySqlException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
更新
1レコードのみ更新
UPDATE文を使用する場合、WHERE句を必ず指定することが推奨される。
指定しない場合は、全てのレコードが更新されることに注意する。
using MySqlConnector;
public void UpdateUser(string id, string password, string roleName)
{
// 接続文字列の取得
var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
using (var connection = new MySqlConnection(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 MySqlParameter("@ID", id));
command.Parameters.Add(new MySqlParameter("@PASSWORD", password));
command.Parameters.Add(new MySqlParameter("@ROLE_NAME", roleName));
// SQLの実行
var affectedRows = command.ExecuteNonQuery();
// 更新対象のレコードが存在しない場合
if (affectedRows == 0)
{
throw new Exception($"ユーザID {id} が存在しません");
}
}
catch (MySqlException exception)
{
Console.WriteLine(exception.Message);
throw;
}
finally
{
// データベースの接続終了
connection.Close();
}
}
}
削除
DELETE文を使用する場合、WHERE句を必ず指定することが推奨される。
指定しない場合は、全てのレコードが削除されることに注意する。
DELETE FROM T_USER WHERE ID = @ID;
