データベースアクセスクラス

SQL Server 2016の教科書 開発編

基礎からわかる C#

データベースアクセスクラス作成の参考

• SQL インジェクション
• SQLインジェクションのまとめ
• .NET: SQL Server ストアドプロシージャのreturnを取得する

データベースアクセスクラス

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

namespace LibrarySample
{
    /// <summary>
    /// データベースアクセスクラス
    /// </summary>
    public class DbAccess : IDisposable
    {
        #region 変数

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

        /// <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;

        /// <summary>
        /// クエリー文字列
        /// </summary>
        private string quearyString = string.Empty;

        /// <summary>
        /// パラメータリスト
        /// </summary>
        private List<SqlParameter> sqlParameters = new List<SqlParameter>();

        #endregion

        #region プロパティ

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

        /// <summary>
        /// クエリー文字列
        /// </summary>
        public string QuearyString
        {
            get
            {
                return this.quearyString;
            }
            set
            {
                this.quearyString = value;
            }
        }

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

        #endregion

        #region コンストラクタ
        /// <summary>
        /// コンストラクタ
        /// </summary>
        public DbAccess()
        {
            //TODO どのようにして各情報を取得するかを検討
            //接続文字列取得
            this.serverName = @"SUZUKI-XP\SQLEXPRESS";
            this.databaseName = "TEST_DB";
            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()
        {
            this.connection = new SqlConnection();
            this.connection.ConnectionString = 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 SqlParameterを登録する
        /// <summary>
        /// SqlParameterを登録する
        /// </summary>
        /// <param name="parameter">パラメータ</param>
        public void SetSqlParameter(SqlParameter parameter)
        {
            this.sqlParameters.Add(parameter);
        }
        #endregion

        #region SqlParameterをクリアする
        /// <summary>
        /// SqlParameterをクリアする
        /// </summary>
        public void ClearSqlParameter()
        {
            this.sqlParameters.Clear();
        }
        #endregion

        #region 参照系SQLを実行する
        /// <summary>
        /// 参照系SQLを実行する
        /// </summary>
        /// <returns>
        /// データを取得した場合は参照結果データテーブル。
        /// データを取得できない場合はnull。
        /// </returns>
        public DataTable ExecuteQuery()
        {
            return this.ExecuteQuery(string.Empty);
        }
        #endregion

        #region 参照系SQLを実行する
        /// <summary>
        /// 参照系SQLを実行する
        /// </summary>
        /// <param name="tableName">テーブルマップに使用するテーブル名</param>
        /// <returns>
        /// データを取得した場合は参照結果データテーブル。
        /// データを取得できない場合はnull。
        /// </returns>
        public DataTable ExecuteQuery(string tableName)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(this.QuearyString, this.connection))
            {
                adapter.SelectCommand.CommandTimeout = this.commandTimeout;
                adapter.SelectCommand.Parameters.AddRange(this.sqlParameters.ToArray());
                adapter.SelectCommand.Transaction = this.transaction;
                DataSet ds = new DataSet();

                //テーブルマップに使用するテーブル名を設定している場合は対応する
                if (!string.IsNullOrEmpty(tableName))
                {
                    adapter.Fill(ds, tableName);
                }
                else
                {
                    adapter.Fill(ds);
                }

                //データを取得した場合はDataTable、取得できない場合はnullを返す
                if (ds.Tables[0] != null)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 更新系SQLを実行する
        /// <summary>
        /// 更新系SQLを実行する
        /// </summary>
        /// <returns>
        /// 影響を受けた行数。
        /// </returns>
        public int ExecuteNonQuery()
        {
            using (SqlCommand cmd = new SqlCommand(this.quearyString, this.connection))
            {
                cmd.CommandTimeout = this.commandTimeout;
                cmd.Transaction = this.transaction;
                cmd.Parameters.AddRange(this.sqlParameters.ToArray());
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region 単一の値を取得を実行する
        /// <summary>
        /// 単一の値を取得を実行する
        /// </summary>
        /// <returns>
        /// 結果セットの最初の行の最初の列。
        /// 結果セットが空の場合はnull。
        /// </returns>
        public T ExecuteScalar<T>()
        {
            using (SqlCommand cmd = new SqlCommand(this.quearyString, this.connection))
            {
                cmd.CommandTimeout = this.commandTimeout;
                cmd.Transaction = this.transaction;
                cmd.Parameters.AddRange(this.sqlParameters.ToArray());
                return (T)(cmd.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

        #region Like用文字列エスケープ処理
        /// <summary>
        /// Like用文字列エスケープ処理
        /// </summary>
        /// <param name="likeString">エスケープ処理対照文字列</param>
        /// <returns>/// エスケープ処理結果文字列。</returns>
        public static string EscapeLikeString(string likeString)
        {
            //パラメータチェック
            if (string.IsNullOrEmpty(likeString))
            {
                return string.Empty;
            }

            //エスケープ処理
            return Regex.Replace(likeString, @"([\%_\[])", "[$1]");
        }
        #endregion


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

        #region イベント

        /// <summary>
        /// データ変換用イベント
        /// </summary>
        public event Func<SqlDataReader, object> SqlDataReaderEventHandler;

        #endregion

        #region 参照系SQLを実行する（SqlDataReaderを使用）
        /// <summary>
        /// 参照系SQLを実行する（SqlDataReaderを使用）
        /// </summary>
        /// <returns>オブジェクト変数。</returns>
        public object ExecuteQueryForSqlDataReader()
        {
            object obj = null;

            using (SqlCommand cmd = new SqlCommand(this.quearyString, this.connection))
            {
                cmd.CommandTimeout = this.commandTimeout;

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

            return obj;
        }
        #endregion

        #endregion
    }
}

Like使用サンプルソース

using (DbAccess dba = new DbAccess())
{
    dba.Connect();
    dba.ClearSqlParameter();
    dba.QuearyString = "SELECT ID,NAME FROM T_USER WHERE COMMENT LIKE '%'+ @P_COMMENT + '%'";
    SqlParameter param = new SqlParameter("@P_COMMENT", SqlDbType.VarChar);
    param.Value = DbAccess.EscapeLikeString("い");
    dba.SetSqlParameter(param);

    DataTable dt = dba.ExecuteQuery();

    string data= "";
    foreach (DataRow dr in dt.Rows)
    {
       data += dr[0].ToString() + " - " + dr[1].ToString() + Environment.NewLine;
    }

    Console.WriteLine(data);
    Console.ReadLine();
}