ストアドプロシージャクラス

独習C# 第3版

独習C# 第3版

ストアドプロシージャクラス

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace LibrarySample
{
    /// <summary>
    /// ストアドプロシージャクラス
    /// </summary>
    public class DbStoredProcedure : IDisposable
    {
        #region 変数

        /// <summary>
        /// コネクションオブジェクト
        /// </summary>
        private SqlConnection connection = null;

        /// <summary>
        /// コマンドオブジェクト
        /// </summary>
        private SqlCommand command = null;

        /// <summary>
        /// ストアドプロシージャ名
        /// </summary>
        private string storedProcedureName = string.Empty;

        /// <summary>
        /// トランザクションオブジェクト
        /// </summary>
        private SqlTransaction transaction = null;

        /// <summary>
        /// 接続文字列
        /// </summary>
        private string connectionString = string.Empty;

        /// <summary>
        /// サーバー
        /// </summary>
        private string serverName = string.Empty;

        /// <summary>
        /// データベース
        /// </summary>
        private string databaseName = string.Empty;

        /// <summary>
        /// ユーザーID
        /// </summary>
        private string userId = string.Empty;

        /// <summary>
        /// パスワード
        /// </summary>
        private string password = string.Empty;

        /// <summary>
        /// 接続タイムアウト(秒)。デフォルトは600秒。
        /// 0(秒)は接続の試行が永久的に待機されるため、ConnectionString では使用しないでください。
        /// </summary>
        private int connectionTimeout = 600;

        /// <summary>
        /// コマンドが実行されるまでの待機時間 (秒)。 デフォルト60秒。
        /// 0(秒)はコマンド実行の試行が永久的に待機されるため使用しないでください。
        /// </summary>
        private int commandTimeout = 60;

        #endregion

        #region プロパティ

        /// <summary>
        /// コネクション
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                return this.connection;
            }
        }

        /// <summary>
        /// コマンドオブジェクト
        /// </summary>
        public SqlCommand Command
        {
            get
            {
                return this.command;
            }
            set
            {
                this.command = value;
            }
        }

        /// <summary>
        /// ストアドプロシージャ名
        /// </summary>
        public string StoredProcedureName
        {
            get
            {
                return this.storedProcedureName;
            }
            set
            {
                this.storedProcedureName = value;
            }
        }

        /// <summary>
        /// コマンドが実行されるまでの待機時間 (秒)。デフォルト60秒。
        /// 0(秒)はコマンド実行の試行が永久的に待機されるため使用しないでください。
        /// </summary>
        public int CommandTimeout
        {
            get
            {
                return this.commandTimeout;
            }
            set
            {
                this.commandTimeout = value;
            }
        }

        #endregion

        #region コンストラクタ
        /// <summary>
        /// コンストラクタ
        /// </summary>
        public DbStoredProcedure()
        {
            //TODO 設定ファイルから
            //接続文字列取得
            this.serverName = @"SUZUKI-XP\SQLEXPRESS";
            this.databaseName = "pubs";
            this.userId = "sa";
            this.password = "sa";
            this.connectionTimeout = 600;
            this.commandTimeout = 60;

            //接続文字列生成
            this.connectionString = this.CreateConnectionString(this.serverName, this.databaseName,
                this.userId, this.password, this.connectionTimeout);
        }
        #endregion

        #region 接続文字列を取得する
        /// <summary>
        /// 接続文字列を取得する
        /// </summary>
        /// <param name="serverName">サーバー名</param>
        /// <param name="databaseName">データベース名</param>
        /// <param name="userId">ユーザーID</param>
        /// <param name="password">パスワード</param>
        /// <param name="connectTimeout">接続タイムアウト</param>
        /// <returns>接続文字列</returns>
        private string CreateConnectionString(string serverName, string databaseName, string userId, string password, int connectTimeout)
        {
            return string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=false;User ID={2};Password={3};Connect Timeout={4}",
                serverName, databaseName, userId, password, connectionTimeout);

        }
        #endregion

        #region DBに接続する
        /// <summary>
        /// DBに接続する
        /// </summary>
        public void Connect()
        {
            //DBへ接続する
            this.connection = new SqlConnection(this.connectionString);
            this.connection.Open();
        }
        #endregion

        #region DBを切断する
        /// <summary>
        /// DBを切断する
        /// </summary>
        public void Close()
        {
            if (this.connection != null)
            {
                if (ConnectionState.Open == this.connection.State)
                {
                    this.connection.Close();
                    this.connection.Dispose();
                    this.connection = null;
                }
            }
        }
        #endregion

        #region コマンドを生成する
        /// <summary>
        /// コマンドを生成する
        /// </summary>
        /// <param name="storedProcedureName">ストアドプロシージャ名</param>
        public void CreateCommand(string storedProcedureName)
        {
            this.storedProcedureName = storedProcedureName;
            this.command = new SqlCommand(this.storedProcedureName, this.connection);
            this.command.CommandType = CommandType.StoredProcedure;
            this.command.CommandTimeout = this.commandTimeout;
        }
        #endregion

        #region 参照系ストアドプロシージャを実行する
        /// <summary>
        /// 参照系ストアドプロシージャを実行する
        /// </summary>
        /// <returns>参照結果データテーブル。</returns>
        public DataTable ExecuteQuery()
        {
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.command;
            adapter.Fill(ds);

            if (ds.Tables[0] != null)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 更新系ストアドプロシージャを実行する
        /// <summary>
        /// 更新系ストアドプロシージャを実行する
        /// </summary>
        /// <returns>影響を受けた行数</returns>
        public int ExecuteNonQuery()
        {
            this.command.Transaction = this.transaction;
            return this.command.ExecuteNonQuery();
        }
        #endregion

        #region 単一の値を取得するストアドプロシージャを実行する
        /// <summary>
        /// 単一の値を取得するストアドプロシージャを実行する
        /// </summary>
        /// <returns>結果セットの最初の行の最初の列。結果セットが空の場合は、null 参照。</returns>
        public T ExecuteScalar<T>()
        {
            return (T)(this.command.ExecuteScalar());
        }
        #endregion

        #region トランザクションを開始する
        /// <summary>
        /// トランザクションを開始する
        /// </summary>
        public void BeginTransaction()
        {
            if (this.connection != null)
            {
                if (this.transaction != null)
                {
                    this.transaction.Dispose();
                    this.transaction = null;
                }

                this.transaction = this.connection.BeginTransaction();
            }
        }
        #endregion

        #region コミットを実行する
        /// <summary>
        /// コミットを実行する
        /// </summary>
        public void Commit()
        {
            if (this.transaction != null)
            {
                this.transaction.Commit();
                this.transaction.Dispose();
                this.transaction = null;
            }
        }
        #endregion

        #region ロールバックを実行する
        /// <summary>
        /// ロールバックを実行する
        /// </summary>
        public void Rollback()
        {
            if (this.transaction != null)
            {
                this.transaction.Rollback();
                this.transaction.Dispose();
                this.transaction = null;
            }
        }

        #endregion

        #region Disposeを実行する
        /// <summary>
        /// Disposeを実行する
        /// </summary>
        public void Dispose()
        {
            this.Rollback();
            this.Close();
        }
        #endregion


        //TODO SqlDataReader用の処理 ※これは使用するかどうか未定※これは使用するかどうか未定
        #region SqlDataReader用の処理

        /// <summary>
        /// 参照系ストアドプロシージャ(SqlDataReader用)イベント
        /// </summary>
        public event Func<SqlDataReader, object> SqlDataReaderEventHandler;

        /// <summary>
        /// 参照系ストアドプロシージャを実行する(SqlDataReader用)
        /// </summary>
        /// <returns>対象とするオブジェクト</returns>
        public object ExecuteQueryForSqlDataReader()
        {
            object obj = null;

            using (SqlDataReader reader = this.command.ExecuteReader())
            {
                obj = this.SqlDataReaderEventHandler(reader);
            }

            return obj;
        }

        #endregion
    }
}

テストストアドプロシージャ

USE [pubs]
GO
/****** オブジェクト:  StoredProcedure [dbo].[TestProcedure]    スクリプト日付: 07/01/2009 20:17:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[TestProcedure]
(
  @au_idIN varchar (11),
  @numTitlesOUT Integer OUTPUT
)
AS 
select 
 A.au_fname, A.au_lname, T.title 
from 
 authors as A join titleauthor as TA 
  on A.au_id=TA.au_id join titles as T 
   on T.title_id=TA.title_id
where 
  A.au_id=@au_idIN
set 
  @numTitlesOUT = @@Rowcount

return (5) 
USE [pubs]
GO
/****** オブジェクト:  StoredProcedure [dbo].[TestProcedure2]    スクリプト日付: 07/01/2009 20:18:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    • =============================================
    • Author:
    • Create date:
    • Description:
    • =============================================
Create Procedure [dbo].[TestProcedure2] ( @au_idIN varchar (11) ) As /* set nocount on */ select count (T.title) from authors as A join titleauthor as TA on A.au_id=TA.au_id join titles as T on T.title_id=TA.title_id where A.au_id=@au_idIN Return(5)
USE [pubs]
GO
/****** オブジェクト:  StoredProcedure [dbo].[TestProcedure3]    スクリプト日付: 07/01/2009 20:19:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    • =============================================
    • Author:
    • Create date:
    • Description:
    • =============================================
Create Procedure [dbo].[TestProcedure3] ( @au_idIN varchar (11), @au_fnam varchar (30) ) As /* set nocount on */ Update authors set au_fname = @au_fnam where au_id = @au_idin return (5)

ストアドプロシージャクラス使用例

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace StoredProcedureTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //------------------------------------DataSet
            {
                //utility
                using (DbStoredProcedure dbsp = new DbStoredProcedure())
                {
                    dbsp.Connect();
                    dbsp.CreateCommand("TestProcedure");

                    SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
                    RetVal.Direction = ParameterDirection.ReturnValue;

                    SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
                    IdIn.Direction = ParameterDirection.Input;
                    IdIn.Value = "213-46-8915";

                    SqlParameter NumTitles = dbsp.Command.Parameters.Add("@numtitlesout", SqlDbType.VarChar, 11);
                    NumTitles.Direction = ParameterDirection.Output;

                    DataTable dt = dbsp.ExecuteQuery();

                    Console.WriteLine("Number of Rows: " + NumTitles.Value);
                    Console.WriteLine("Return Value: " + RetVal.Value);

                    Console.ReadLine();
                }
            }

            //------------------------------------ExecuteScalar
            {
                //utility
                using (DbStoredProcedure dbsp = new DbStoredProcedure())
                {
                    string strCount;

                    dbsp.Connect();
                    dbsp.CreateCommand("TestProcedure2");

                    SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
                    RetVal.Direction = ParameterDirection.ReturnValue;

                    SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
                    IdIn.Direction = ParameterDirection.Input;

                    IdIn.Value = "213-46-8915";
                    strCount = dbsp.ExecuteScalar<int>().ToString();

                    Console.WriteLine("Number of Rows: " + strCount);
                    Console.WriteLine("Return Value: " + RetVal.Value);

                    Console.ReadLine();
                }
            }

            //------------------------------------ExecuteNonQuery
            {
                using (SqlConnection PubsConn = new SqlConnection(@"server=VAIO-RA50\SQLEXPRESS;uid=sa;pwd=sa;database=pubs"))
                {
                    string strRowAffect;
                    SqlCommand testCMD = new SqlCommand("TestProcedure3", PubsConn);
                    testCMD.CommandType = CommandType.StoredProcedure;

                    SqlParameter RetVal = testCMD.Parameters.Add("RetVal", SqlDbType.Int);
                    RetVal.Direction = ParameterDirection.ReturnValue;

                    SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
                    IdIn.Direction = ParameterDirection.Input;
                    IdIn.Value = "213-46-8915";

                    SqlParameter FnameIn = testCMD.Parameters.Add("@au_fnam", SqlDbType.VarChar, 30);
                    FnameIn.Direction = ParameterDirection.Input;
                    FnameIn.Value = "Marjorie";

                    PubsConn.Open();
                    strRowAffect = testCMD.ExecuteNonQuery().ToString();

                    Console.WriteLine("Number of Rows: " + strRowAffect);
                    Console.WriteLine("Return Value: " + RetVal.Value);
                    Console.ReadLine();
                }

                //utility
                using (DbStoredProcedure dbsp = new DbStoredProcedure())
                {
                    string strRowAffect = string.Empty; ;

                    dbsp.Connect();
                    dbsp.CreateCommand("TestProcedure3");

                    SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
                    RetVal.Direction = ParameterDirection.ReturnValue;

                    SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
                    IdIn.Direction = ParameterDirection.Input;
                    IdIn.Value = "213-46-8915";

                    SqlParameter FnameIn = dbsp.Command.Parameters.Add("@au_fnam", SqlDbType.VarChar, 30);
                    FnameIn.Direction = ParameterDirection.Input;
                    FnameIn.Value = "Marjorie";

                    try
                    {
                        dbsp.BeginTransaction();
                        strRowAffect = dbsp.ExecuteNonQuery().ToString();
                        dbsp.Commit();
                    }
                    catch (Exception)
                    {
                        dbsp.Rollback();
                    }

                    Console.WriteLine("Number of Rows: " + strRowAffect);
                    Console.WriteLine("Return Value: " + RetVal.Value);

                    Console.ReadLine();
                }
            }
        }
    }
}