C Sharpとデータベース - ストアドプロシージャ

概要

ストアドプロシージャは、データベースサーバ上に保存されたSQL文の集合であり、C#から呼び出して実行できる仕組みである。

下表に、ストアドプロシージャを使用する主なメリットを示す。

ストアドプロシージャを使用する主なメリット

メリット	説明
パフォーマンス向上	プリコンパイルされた実行計画により、 同じ処理を繰り返す場合にSQL文の解析・最適化のコストを削減できる。
セキュリティの向上	テーブルへの直接アクセスを制限し、 ストアドプロシージャ経由でのみデータ操作を許可する設計が可能である。
保守性の向上	ビジネスロジックをデータベース側に集約することで、 アプリケーション側の変更を最小限に抑えられる。
ネットワークトラフィックの削減	複数のSQL文を1回のネットワーク呼び出しで実行できるため、 通信量を抑えられる。

C#からストアドプロシージャを呼び出す際の基本パターンは、CommandType.StoredProcedure を設定し、CommandText にプロシージャ名を指定することである。

各DBMSにおけるストアドプロシージャの特徴を以下に示す。

• SQL Server

  T-SQLで記述し、OUTPUTパラメータと戻り値をサポートする。

• Oracle Database

  PL/SQLで記述し、OUTパラメータとREF CURSORで結果セットを返却する。

• MySQL

  DELIMITERで区切り記号を変更して定義し、IN / OUT / INOUT パラメータをサポートする。

パラメータの方向は ParameterDirection 列挙型で指定し、Input、Output、InputOutput、ReturnValueの4種類がある。

SQL Server

引数なしのストアドプロシージャ

ストアドプロシージャの定義

使用するストアドプロシージャは、引数なしのSELECT文の結果を返すだけのストアドプロシージャである。

 CREATE PROCEDURE [dbo].[StoredProcedure_Sample]
 AS
 BEGIN
    SET NOCOUNT ON;
 
    SELECT * FROM sys.objects
 END

C#からの呼び出し

SELECT文の結果を SqlDataAdapter の Fill メソッドで DataSet に格納する。

DataSet の Tables は配列になっているため、ストアドプロシージャがSELECT文の結果を複数返す場合にも対応可能である。

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 
 public void GetData()
 {
    var table = new DataTable();
 
    // 接続文字列の取得 (設定ファイル (xml形式) から取得)
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          // データベースの接続開始
          connection.Open();
 
          // ストアドプロシージャの指定
          command.CommandType = CommandType.StoredProcedure;
 
          // ストアドプロシージャ名の指定
          command.CommandText = "StoredProcedure_Sample";
 
          // ストアドプロシージャを実行して結果をdataSetへ格納
          var dataSet = new DataSet();
          using (var adapter = new SqlDataAdapter(command))
          {
             adapter.Fill(dataSet);
          }
 
          // 結果を表示
          this.dataGridView1.DataSource = dataSet.Tables[0];
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          // データベースの接続終了
          connection.Close();
       }
    }
 }

OUTPUTパラメータ付きストアドプロシージャ

ストアドプロシージャの定義

 CREATE PROCEDURE [dbo].[GetUserCount]
    @RoleName VARCHAR(20),
    @UserCount INT OUTPUT
 AS
 BEGIN
    SET NOCOUNT ON;
    SELECT @UserCount = COUNT(*) FROM T_USER WHERE ROLE_NAME = @RoleName
 END

C#からの呼び出し

 using System;
 using System.Configuration;
 using System.Data;
 using System.Data.SqlClient;
 
 public int GetUserCount(string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["sqlsvr"].ConnectionString;
 
    using (var connection = new SqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUserCount";
 
          // INPUTパラメータ
          command.Parameters.Add(new SqlParameter("@RoleName", SqlDbType.VarChar, 20)).Value = roleName;
 
          // OUTPUTパラメータ
          var outputParam = new SqlParameter("@UserCount", SqlDbType.Int);
          outputParam.Direction = ParameterDirection.Output;
          command.Parameters.Add(outputParam);
 
          command.ExecuteNonQuery();
 
          return (int)outputParam.Value;
       }
       catch (Exception exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

Oracle Database

引数なしのストアドプロシージャ

ストアドプロシージャの定義

Oracle PL/SQLでは / (スラッシュ) でプロシージャの終了を示す。

 CREATE OR REPLACE PROCEDURE UpdateAllSalaries
 IS
 BEGIN
    UPDATE EMP SET SAL = SAL * 1.1;
    COMMIT;
 END;
 /

C#からの呼び出し

 using System;
 using System.Configuration;
 using System.Data;
 using Oracle.ManagedDataAccess.Client;
 
 public void ExecuteStoredProcedure()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "UpdateAllSalaries";
 
          command.ExecuteNonQuery();
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

OUTパラメータ付きストアドプロシージャ

ストアドプロシージャの定義

 CREATE OR REPLACE PROCEDURE GetUserDetails (
    p_id IN VARCHAR2,
    p_password OUT VARCHAR2,
    p_role_name OUT VARCHAR2
 )
 IS
 BEGIN
    SELECT PASSWORD, ROLE_NAME INTO p_password, p_role_name
    FROM T_USER WHERE ID = p_id;
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
       p_password := NULL;
       p_role_name := NULL;
 END;
 /

C#からの呼び出し

 using System;
 using System.Configuration;
 using System.Data;
 using Oracle.ManagedDataAccess.Client;
 
 public User GetUserDetails(string id)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUserDetails";
          command.BindByName = true;
 
          // INパラメータ
          command.Parameters.Add("p_id", OracleDbType.Varchar2, 50).Value = id;
 
          // OUTパラメータ
          command.Parameters.Add("p_password", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;
          command.Parameters.Add("p_role_name", OracleDbType.Varchar2, 20).Direction = ParameterDirection.Output;
 
          command.ExecuteNonQuery();
 
          return new User
          {
             Id = id,
             Password = command.Parameters["p_password"].Value?.ToString(),
             RoleName = command.Parameters["p_role_name"].Value?.ToString()
          };
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

OUTパラメータ使用時の注意事項を以下に示す。

• BindByName = true を設定しないと、パラメータは追加順序で位置バインドされる。
• OUTパラメータでは Varchar2 型のサイズ指定が必要である。
• OracleDbType.Varchar2 の Size を 0 にすると値が取得できないことがある。

REF CURSORを使用した結果セットの取得

OracleのストアドプロシージャからSELECT結果を返す場合、REF CURSORを使用する。

SQL Serverのストアドプロシージャのように直接SELECTの結果を返すことはできない。

ストアドプロシージャの定義

 CREATE OR REPLACE PROCEDURE GetUsersByRole (
    p_role_name IN VARCHAR2,
    p_cursor OUT SYS_REFCURSOR
 )
 IS
 BEGIN
    OPEN p_cursor FOR
       SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ROLE_NAME = p_role_name;
 END;
 /

C#からの呼び出し (DataReaderで読み込み)

 using System;
 using System.Collections.Generic;
 using System.Configuration;
 using System.Data;
 using Oracle.ManagedDataAccess.Client;
 
 public List<User> GetUsersByRole(string roleName)
 {
    var list = new List<User>();
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUsersByRole";
          command.BindByName = true;
 
          // INパラメータ
          command.Parameters.Add("p_role_name", OracleDbType.Varchar2, 20).Value = roleName;
 
          // REF CURSORパラメータ (OUTパラメータとして定義)
          command.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                list.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 list;
 }

C#からの呼び出し (DataAdapterでDataSetに格納)

 public DataTable GetUsersByRoleAsDataTable(string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
 
    using (var connection = new OracleConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUsersByRole";
          command.BindByName = true;
 
          command.Parameters.Add("p_role_name", OracleDbType.Varchar2, 20).Value = roleName;
          command.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 
          var dataSet = new DataSet();
          using (var adapter = new OracleDataAdapter(command))
          {
             adapter.Fill(dataSet);
          }
 
          return dataSet.Tables[0];
       }
       catch (OracleException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

REF CURSOR使用時の注意事項を以下に示す。

• REF CURSORパラメータは OracleDbType.RefCursor で指定する。
• REF CURSORを使用する場合、ExecuteReader() で結果を読み込むことが可能である。
• DataAdapter.Fill() で DataSet に格納することも可能である。
• 複数のREF CURSORを返す場合、DataSet の Tables[0]、Tables[1] でアクセスできる。

MySQL

引数なしのストアドプロシージャ

ストアドプロシージャの定義

MySQLでは DELIMITER を変更してプロシージャ定義内のセミコロンと区別する。

 DELIMITER //
 CREATE PROCEDURE GetAllUsers()
 BEGIN
    SELECT ID, PASSWORD, ROLE_NAME FROM T_USER;
 END//
 DELIMITER ;

C#からの呼び出し

 using System;
 using System.Configuration;
 using System.Data;
 using MySqlConnector;
 
 public DataTable GetAllUsers()
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetAllUsers";
 
          var dataSet = new DataSet();
          using (var adapter = new MySqlDataAdapter(command))
          {
             adapter.Fill(dataSet);
          }
 
          return dataSet.Tables[0];
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

OUTパラメータ付きストアドプロシージャ

ストアドプロシージャの定義

 DELIMITER //
 CREATE PROCEDURE GetUserCountByRole(
    IN p_role_name VARCHAR(20),
    OUT p_count INT
 )
 BEGIN
    SELECT COUNT(*) INTO p_count FROM T_USER WHERE ROLE_NAME = p_role_name;
 END//
 DELIMITER ;

C#からの呼び出し

 using System;
 using System.Configuration;
 using System.Data;
 using MySqlConnector;
 
 public int GetUserCountByRole(string roleName)
 {
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUserCountByRole";
 
          // INパラメータ
          command.Parameters.Add("@p_role_name", MySqlDbType.VarChar, 20).Value = roleName;
 
          // OUTパラメータ
          var outputParam = new MySqlParameter("@p_count", MySqlDbType.Int32);
          outputParam.Direction = ParameterDirection.Output;
          command.Parameters.Add(outputParam);
 
          command.ExecuteNonQuery();
 
          return (int)outputParam.Value;
       }
       catch (MySqlException exception)
       {
          Console.WriteLine(exception.Message);
          throw;
       }
       finally
       {
          connection.Close();
       }
    }
 }

INOUTパラメータ付きストアドプロシージャ

MySQLの特徴的なパラメータ種別として INOUT がある。

INOUTパラメータは入力値と出力値を同一パラメータで兼用する。

ストアドプロシージャの定義

 DELIMITER //
 CREATE PROCEDURE IncrementValue(INOUT p_value INT, IN p_increment INT)
 BEGIN
    SET p_value = p_value + p_increment;
 END//
 DELIMITER ;

C#からの呼び出し

 // INOUTパラメータは ParameterDirection.InputOutput を指定
 var inoutParam = new MySqlParameter("@p_value", MySqlDbType.Int32);
 inoutParam.Direction = ParameterDirection.InputOutput;
 inoutParam.Value = 10;
 command.Parameters.Add(inoutParam);
 
 command.Parameters.Add("@p_increment", MySqlDbType.Int32).Value = 5;
 
 command.ExecuteNonQuery();
 
 var result = (int)inoutParam.Value; // 15

結果セットを返すストアドプロシージャ

MySQLではSQL Serverと同様に、ストアドプロシージャ内のSELECT文の結果をそのまま返すことができる。

Oracle DatabaseのようにREF CURSORを使用する必要はない。

ストアドプロシージャの定義

 DELIMITER //
 CREATE PROCEDURE GetUsersByRole(IN p_role_name VARCHAR(20))
 BEGIN
    SELECT ID, PASSWORD, ROLE_NAME FROM T_USER WHERE ROLE_NAME = p_role_name;
 END//
 DELIMITER ;

C#からの呼び出し

 public List<User> GetUsersByRole(string roleName)
 {
    var list = new List<User>();
    var connectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
 
    using (var connection = new MySqlConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
       try
       {
          connection.Open();
 
          command.CommandType = CommandType.StoredProcedure;
          command.CommandText = "GetUsersByRole";
 
          command.Parameters.Add("@p_role_name", MySqlDbType.VarChar, 20).Value = roleName;
 
          using (var reader = command.ExecuteReader())
          {
             while (reader.Read())
             {
                list.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 list;
 }

ストアドプロシージャの比較

下表に、3つのDBMSでのストアドプロシージャの主な違いを示す。

ストアドプロシージャの比較

項目	SQL Server	Oracle Database	MySQL
言語	T-SQL	PL/SQL	SQL
パラメータ形式	@パラメータ名	:パラメータ名	@パラメータ名
結果セット返却	SELECT文の結果を直接返却	REF CURSORを使用	SELECT文の結果を直接返却
INOUTパラメータ	OUTPUT (IN/OUT兼用)	IN OUT	INOUT
区切り記号	不要	/ (スラッシュ)	DELIMITER // ... //