Template2

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

namespace ChatworkBulkSender.Utils
{
    class DBAccess
    {
        public string connection_str;

        public DBAccess()
        {
            var builder = new SqlConnectionStringBuilder();
#if DEBUG
            builder.DataSource = ConfigurationManager.AppSettings["TestDataSource"];
            builder.UserID = "enji_cw_kuzuya";
            builder.Password = "kuzuya00";
            builder.InitialCatalog = ConfigurationManager.AppSettings["TestInitialCatalog"];
            builder.ConnectTimeout = int.Parse(ConfigurationManager.AppSettings["TestTimeOut"]);
#else
            builder.DataSource = ConfigurationManager.AppSettings["DataSource"];
            builder.UserID     = "enji_user";
            builder.Password   = "YenGp@ssW0rd1001";
            builder.InitialCatalog = ConfigurationManager.AppSettings["InitialCatalog"];
            builder.ConnectTimeout = int.Parse(ConfigurationManager.AppSettings["TimeOut"]);
#endif
            connection_str = builder.ConnectionString;
        }

        /// <summary>
        /// SELECT クエリを実行
        /// </summary>
        public DataTable ExecQuery(string sql, Dictionary<string, object> parameters = null)
        {
            try
            {
                var dt = new DataTable();
                using (var con = new SqlConnection(connection_str))
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parameters != null)
                    {
                        foreach (var kv in parameters)
                            cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
                    }

                    using (var adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(dt);
                    }
                }
                return dt;
            }
            catch (Exception e)
            {
                const string SERVER_NOT_FOUND = "サーバーが見つからないかアクセスできません";
                if (e.Message.Contains(SERVER_NOT_FOUND))
                {
                    MessageBoxUtil.ShowErr(
                        SERVER_NOT_FOUND + "\r\nPCがネットワークに接続されているか確認してください。\r\n\r\n" + e.Message,
                        "DBエラー");
                    return new DataTable();
                }
                MessageBoxUtil.ShowErr(e.Message, "DBエラー");
                return new DataTable();
            }
        }

        /// <summary>
        /// INSERT/UPDATE/DELETE を実行
        /// </summary>
        public int ExecNonQuery(string sql, Dictionary<string, object> parameters = null)
        {
            return ExecNonQuery(new[] { (sql, parameters) });
        }

        /// <summary>
        /// 複数の DML をトランザクションで実行
        /// </summary>
        public int ExecNonQuery(IEnumerable<(string Sql, Dictionary<string, object> Parameters)> commands)
        {
            int affected = 0;
            using (var con = new SqlConnection(connection_str))
            {
                con.Open();
                using (var tx = con.BeginTransaction())
                using (var cmd = new SqlCommand() { Connection = con, Transaction = tx })
                {
                    try
                    {
                        foreach (var (sql, parameters) in commands)
                        {
                            if (string.IsNullOrWhiteSpace(sql)) continue;
                            cmd.CommandText = sql;
                            cmd.Parameters.Clear();
                            if (parameters != null)
                            {
                                foreach (var kv in parameters)
                                    cmd.Parameters.AddWithValue(kv.Key, kv.Value ?? DBNull.Value);
                            }
                            affected += cmd.ExecuteNonQuery();
                        }
                        tx.Commit();
                    }
                    catch
                    {
                        tx.Rollback();
                        throw;
                    }
                }
            }
            return affected;
        }

        public object ExecuteScalar(string sql, Dictionary<string,object> parameters = null)
        {
            try
            {
                using (var con = new SqlConnection(connection_str))
                using (var cmd = new SqlCommand(sql,con))
                {
                    if (parameters != null)
                    {
                        foreach (var ky in parameters)
                        {
                            cmd.Parameters.AddWithValue(ky.Key, ky.Value ?? DBNull.Value);
                        }
                    }

                    con.Open();
                    return cmd.ExecuteScalar();
                }
                
            }
            catch(Exception e)
            {
                const string SERVER_NOT_FOUND = "サーバーが見つからないかアクセスできません";
                if (e.Message.Contains(SERVER_NOT_FOUND))
                {
                    MessageBoxUtil.ShowErr(
                        SERVER_NOT_FOUND + "\r\nPCがネットワークに接続されているか確認してください。\r\n\r\n" + e.Message,
                        "DBエラー");
                    return null;
                }
                MessageBoxUtil.ShowErr(e.Message, "DBエラー");
                return null;
            }
        }
    }
}