C Sharpとデータベース - ストアドプロシージャのソースを表示

== 概要 ==
ストアドプロシージャは、データベースサーバ上に保存されたSQL文の集合であり、C#から呼び出して実行できる仕組みである。<br>
<br>
下表に、ストアドプロシージャを使用する主なメリットを示す。<br>
<br>
<center>
{| class="wikitable"
|+ ストアドプロシージャを使用する主なメリット
|-
! メリット !! 説明
|-
| パフォーマンス向上 || プリコンパイルされた実行計画により、<br>同じ処理を繰り返す場合にSQL文の解析・最適化のコストを削減できる。
|-
| セキュリティの向上 || テーブルへの直接アクセスを制限し、<br>ストアドプロシージャ経由でのみデータ操作を許可する設計が可能である。
|-
| 保守性の向上 || ビジネスロジックをデータベース側に集約することで、<br>アプリケーション側の変更を最小限に抑えられる。
|-
| ネットワークトラフィックの削減 || 複数のSQL文を1回のネットワーク呼び出しで実行できるため、<br>通信量を抑えられる。
|}
</center>
<br>
C#からストアドプロシージャを呼び出す際の基本パターンは、<code>CommandType.StoredProcedure</code> を設定し、<code>CommandText</code> にプロシージャ名を指定することである。<br>
<br>
各DBMSにおけるストアドプロシージャの特徴を以下に示す。<br>
* SQL Server
*: T-SQLで記述し、OUTPUTパラメータと戻り値をサポートする。
* Oracle Database
*: PL/SQLで記述し、OUTパラメータとREF CURSORで結果セットを返却する。
* MySQL
*: DELIMITERで区切り記号を変更して定義し、IN / OUT / INOUT パラメータをサポートする。
<br>
パラメータの方向は <code>ParameterDirection</code> 列挙型で指定し、Input、Output、InputOutput、ReturnValueの4種類がある。<br>
<br><br>

== SQL Server ==
==== 引数なしのストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
使用するストアドプロシージャは、引数なしのSELECT文の結果を返すだけのストアドプロシージャである。<br>
<br>
 <syntaxhighlight lang="sql">
 CREATE PROCEDURE [dbo].[StoredProcedure_Sample]
 AS
 BEGIN
    SET NOCOUNT ON;
 
    SELECT * FROM sys.objects
 END
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
SELECT文の結果を <code>SqlDataAdapter</code> の <code>Fill</code> メソッドで <code>DataSet</code> に格納する。<br>
<br>
<code>DataSet</code> の <code>Tables</code> は配列になっているため、ストアドプロシージャがSELECT文の結果を複数返す場合にも対応可能である。<br>
<br>
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
==== OUTPUTパラメータ付きストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 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
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br><br>

== Oracle Database ==
==== 引数なしのストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
Oracle PL/SQLでは <code>/</code> (スラッシュ) でプロシージャの終了を示す。<br>
<br>
 <syntaxhighlight lang="sql">
 CREATE OR REPLACE PROCEDURE UpdateAllSalaries
 IS
 BEGIN
    UPDATE EMP SET SAL = SAL * 1.1;
    COMMIT;
 END;
 /
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
==== OUTパラメータ付きストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 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;
 /
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
OUTパラメータ使用時の注意事項を以下に示す。<br>
* <code>BindByName = true</code> を設定しないと、パラメータは追加順序で位置バインドされる。
* OUTパラメータでは <code>Varchar2</code> 型のサイズ指定が必要である。
* <code>OracleDbType.Varchar2</code> の Size を 0 にすると値が取得できないことがある。
<br>
==== REF CURSORを使用した結果セットの取得 ====
OracleのストアドプロシージャからSELECT結果を返す場合、REF CURSORを使用する。<br>
<br>
SQL Serverのストアドプロシージャのように直接SELECTの結果を返すことはできない。<br>
<br>
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 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;
 /
 </syntaxhighlight>
<br>
===== C#からの呼び出し (DataReaderで読み込み) =====
 <syntaxhighlight lang="csharp">
 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;
 }
 </syntaxhighlight>
<br>
===== C#からの呼び出し (DataAdapterでDataSetに格納) =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
REF CURSOR使用時の注意事項を以下に示す。<br>
* REF CURSORパラメータは <code>OracleDbType.RefCursor</code> で指定する。
* REF CURSORを使用する場合、<code>ExecuteReader()</code> で結果を読み込むことが可能である。
* <code>DataAdapter.Fill()</code> で <code>DataSet</code> に格納することも可能である。
* 複数のREF CURSORを返す場合、<code>DataSet</code> の <code>Tables[0]</code>、<code>Tables[1]</code> でアクセスできる。
<br><br>

== MySQL ==
==== 引数なしのストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
MySQLでは DELIMITER を変更してプロシージャ定義内のセミコロンと区別する。<br>
<br>
 <syntaxhighlight lang="sql">
 DELIMITER //
 CREATE PROCEDURE GetAllUsers()
 BEGIN
    SELECT ID, PASSWORD, ROLE_NAME FROM T_USER;
 END//
 DELIMITER ;
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
==== OUTパラメータ付きストアドプロシージャ ====
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 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 ;
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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();
       }
    }
 }
 </syntaxhighlight>
<br>
==== INOUTパラメータ付きストアドプロシージャ ====
MySQLの特徴的なパラメータ種別として <code>INOUT</code> がある。<br>
<br>
INOUTパラメータは入力値と出力値を同一パラメータで兼用する。<br>
<br>
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 DELIMITER //
 CREATE PROCEDURE IncrementValue(INOUT p_value INT, IN p_increment INT)
 BEGIN
    SET p_value = p_value + p_increment;
 END//
 DELIMITER ;
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 // 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
 </syntaxhighlight>
<br>
==== 結果セットを返すストアドプロシージャ ====
MySQLではSQL Serverと同様に、ストアドプロシージャ内のSELECT文の結果をそのまま返すことができる。<br>
<br>
Oracle DatabaseのようにREF CURSORを使用する必要はない。<br>
<br>
===== ストアドプロシージャの定義 =====
 <syntaxhighlight lang="sql">
 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 ;
 </syntaxhighlight>
<br>
===== C#からの呼び出し =====
 <syntaxhighlight lang="csharp">
 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;
 }
 </syntaxhighlight>
<br><br>

== ストアドプロシージャの比較 ==
下表に、3つのDBMSでのストアドプロシージャの主な違いを示す。<br>
<br>
<center>
{| class="wikitable"
|+ ストアドプロシージャの比較
! 項目 !! SQL Server !! Oracle Database !! MySQL
|-
| 言語 || T-SQL || PL/SQL || SQL
|-
| パラメータ形式 || @パラメータ名 || :パラメータ名 || @パラメータ名
|-
| 結果セット返却 || SELECT文の結果を直接返却 || REF CURSORを使用 || SELECT文の結果を直接返却
|-
| INOUTパラメータ || OUTPUT (IN/OUT兼用) || IN OUT || INOUT
|-
| 区切り記号 || 不要 || / (スラッシュ) || DELIMITER // ... //
|}
</center>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:C_Sharp]]