using HotelPms.Share.Data.Script;
using HotelPms.Share.IO;
using HotelPms.Share.Util;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;

namespace HotelPms.Share.Data;

/// ****************************** Description *******************************
/// ◇システム名称
/// 　汎用クラス
/// ◇概要
/// 　データベース制御用クラス(Microsolt Sql専用)
/// ◇履歴
/// 　2007/XX/XX  小木　勝龍    新規作成
///   20110727 毛利 タイムアウト時間を600s⇒6000s
/// ****************************** Declarations ******************************
public class MsSqlNet : DataAccess
{
    #region  ★★★★★　Declartions　★★★★★

    public event SqlInfoMessageEventHandler SqlInfoMessageEvent;

    #endregion

    #region  ★★★★★　Declartions　★★★★★

    public string ConnectionString { get; set; } = string.Empty;

    /// <summary>ユーザー</summary>
    public override string UseID { get; set; } = string.Empty;

    /// <summary>パスワード</summary>
    public override string PassWord { get; set; } = string.Empty;

    /// <summary>データベース所在のパソコン名</summary>
    public override string DataSource { get; set; } = string.Empty;

    /// <summary>データベース名</summary>
    public override string Catalog { get; set; } = string.Empty;

    public override string Schema { get; set; } = "dbo";

    public override int Port { get; set; } = 1433;

    /// <summary>
    /// デッドロック関連： 1205,3635,5231,5252,17888,22840,23424
    /// </summary>
    public int Message_id { get; set; } = 0;

    /// <summary>エラー情報</summary>
    public override string ErrInfo { get; set; } = string.Empty;

    /// <summary>エラーコード</summary>
    public override int ErrNo { get; set; } = 0;

    public bool PoolEnabled { get; set; } = false;

    public int MaxPoolSize { get; set; } = 1000;

    public int RetryCount { get; set; } = 12;

    public int RetryInterval { get; set; } = 5;

    /// <summary>
    /// タイマー単位のSQL
    /// </summary>
    public bool IsTimerAccess { get; set; } = false;

    public bool FireInfoMessageEventOnUserErrors { get; set; } = false;

    public bool MessageEventEnabled { get; set; } = false;

    #endregion

    #region  ★★★★★　Class Event　★★★★★

    public MsSqlNet(string connectionString)
    {
        ConnectionString = connectionString;
        SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder(connectionString);
        DataSource = ssb.DataSource;
        UseID = ssb.UserID;
        PassWord = ssb.Password;
        Catalog = ssb.InitialCatalog;
        MaxPoolSize = ssb.MaxPoolSize;
    }

    public MsSqlNet(DBConnectItem connecitem) : this(connecitem.HostName, connecitem.UserID, connecitem.Password, connecitem.DBName)
    { }

    public MsSqlNet(string dataSource, string userID, string passWord) : this(dataSource, userID, passWord, "master")
    {
    }

    public MsSqlNet(string dataSource, string userID, string passWord, string catalog)
    {
        DataSource = dataSource;
        UseID = userID;
        PassWord = passWord;
        Catalog = catalog;
        ResetConnectionString();
    }

    public override void Dispose()
    {

    }

    #endregion

    #region  ★★★★★　Public static Function　★★★★★

    public static void ClearPool()
    {
        try
        {
            SqlConnection.ClearAllPools();
        }
        catch { }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="item"></param>
    /// <param name="createSql"></param>
    /// <returns>0.正常 -1.SQL発行エラー 1.DB接続エラー 2.DBが既に存在する</returns>
    public static int CreateDB(DBConnectItem item, string createSql)
    {
        using (MsSqlNet dbAccess = new MsSqlNet(item.HostName, item.UserID, item.Password, "master"))
        {
            if (dbAccess.ErrNo != 0) { return 1; }
            if (dbAccess.ExistsDataBase(item.DBName)) { return 2; }

            if(dbAccess.ExecuteNonQuery(createSql) == -1)
            {
                return -1;
            }
            else
            {
                return 0;
            }
        }
    }

    #endregion

    #region  ★★★★★　Public Function　★★★★★

    public void ResetConnectionString()
    {
        SqlConnectionStringBuilder ssb = new SqlConnectionStringBuilder();
        ssb.DataSource = DataSource;
        ssb.UserID = UseID;
        ssb.Password = PassWord;
        ssb.InitialCatalog = Catalog;
        ssb.MaxPoolSize = MaxPoolSize;            
        ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
        //ssb.ApplicationName = System.IO.Path.GetFileNameWithoutExtension(Application.ExecutablePath);
        ConnectionString = ssb.ToString();
    }

    /// <summary>
    /// 可変戻り値
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="fillReaderHandler"></param>
    /// <returns></returns>
    public override T GetDataReader<T>(string sql, Action<DbDataReader, T> Fetch)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataReader({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataReader({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                T t = new T();
                using (SqlConnection dbConnect = Open())
                {
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 6000;
                        using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default))
                        {
                            Fetch(reader, t);
                        }  //自動クロス
                    }
                }
                return t;
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return default; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return default;
    }

    public override DataSet GetDataSet(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataSet({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataSet({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (SqlConnection dbConnect = Open())
                {
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 6000;
                        DataSet dataset = new DataSet();
                        using (SqlDataAdapter adapter = new SqlDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            adapter.Fill(dataset);
                        }
                        return dataset;
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return null; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return null;
    }

    public override DataSet GetDataSetWithTran(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataSetWithTran({0})", sql), GetLogLevel());
        string sqlCommand = string.Format(@"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
    BEGIN TRANSACTION;
    {0}
    COMMIT TRANSACTION;
    SELECT 0 AS ErrorNumber;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;     
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;", sql);

        DataSet result = null;
        for (int i = 0; i < 5; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataSetWithTran({0})", sql, i), OperationLog.LogLevelType.Normal); }
            result = GetDataSet(sqlCommand);
            if (result != null && result.Tables.Count > 0 && result.Tables[result.Tables.Count - 1].Rows.Count > 0 && CConvert.ToInt(result.Tables[result.Tables.Count - 1].Rows[0]["ErrorNumber"]) != 0)
            {
                DataRow row = result.Tables[result.Tables.Count - 1].Rows[0];
                string err = string.Format("ErrorNumber：{0}" + Environment.NewLine, row["ErrorNumber"]);
                err += string.Format("ErrorSeverity：{0}" + Environment.NewLine, row["ErrorSeverity"]);
                err += string.Format("ErrorState：{0}" + Environment.NewLine, row["ErrorState"]);
                err += string.Format("ErrorProcedure：{0}" + Environment.NewLine, row["ErrorProcedure"]);
                err += string.Format("ErrorLine：{0}" + Environment.NewLine, row["ErrorLine"]);
                err += string.Format("ErrorMessage：{0}" + Environment.NewLine, row["ErrorMessage"]);
                ErrNo = CConvert.ToInt(row["ErrorNumber"]);
                ErrInfo = err;
                OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataSetWithTran({0})={1}", sqlCommand, ErrInfo), GetLogLevel());

                //トランザクション (プロセス ID 61) が、ロック 個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。
                if (CConvert.ToInt(row["ErrorNumber"]) == 1205 || err.Contains("デッドロック"))
                {
                    Thread.Sleep(100);
                    continue;
                }
            }
            break;
        }
        return result;

    }

    public override DataTable GetDataTable(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.GetDataTable({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.GetDataTable({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (SqlConnection dbConnect = Open())
                {
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 60000;
                        DataTable dataTable = new DataTable();
                        using (SqlDataAdapter adapter = new SqlDataAdapter())
                        {
                            adapter.SelectCommand = command;
                            adapter.Fill(dataTable);
                        }
                        return dataTable;
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return null; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return null;
    }

    public override object ExecuteScalar(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.ExecuteScalar({0})", sql), GetLogLevel());
        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.ExecuteScalar({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (SqlConnection dbConnect = Open())
                {
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 60000;
                        return command.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return null; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return null;
    }

    public override int ExecuteGetID(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.ExecuteGetID({0})", sql), GetLogLevel());

        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.ExecuteGetID({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (SqlConnection dbConnect = Open())
                {
                    sql = sql + " SELECT CAST(scope_identity() AS int) ";
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        command.CommandTimeout = 60000;
                        return CConvert.ToInt(command.ExecuteScalar().ToString(), -1);  //NULL可能性がある
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return -1; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return -1;
    }

    public override int ExecuteNonQuery(string sql)
    {
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.ExecuteNonQuery({0})", sql), GetLogLevel());

        for (int i = 0; i < RetryCount; i++)
        {
            if (i > 0) { OperationLog.Instance.WriteLog(string.Format("{1}⇒MsSqlNet.ExecuteNonQuery({0})", sql, i), OperationLog.LogLevelType.Normal); }
            try
            {
                ErrClear();
                using (SqlConnection dbConnect = Open())
                {
                    using (SqlCommand command = new SqlCommand(sql, dbConnect))
                    {
                        int row = command.ExecuteNonQuery();
                        return (row == -1 ? 0 : row);
                    }
                }
            }
            catch (Exception ex)
            {
                if (!IsTransient(ex, sql)) { return -1; }
            }
            Thread.Sleep(RetryInterval * 1000);
        }
        return -1;
    }

    public override bool ExecuteNonQueryWithTran(string sql)
    {
        try
        {
            OperationLog.Instance.WriteLog(string.Format("MsSqlNet.ExecuteNonQueryWithTran({0})", sql), GetLogLevel());
            using (DataSet result = GetDataSetWithTran(sql))
            {
                if (result == null) { return false; }
                else if (result.Tables.Count > 0 && result.Tables[result.Tables.Count - 1].Rows.Count > 0 && CConvert.ToInt(result.Tables[result.Tables.Count - 1].Rows[0]["ErrorNumber"]) != 0) { return false; }
            }
            return true;
        }
        catch (Exception ex)
        {
            ErrNo = -1;
            ErrInfo = ex.Message;
            OperationLog.Instance.WriteLog(string.Format("ExecuteNonQueryWithTran：{0},{1},{2}", ErrNo, ErrInfo, sql));
            return false;
        }
    }

    #region  ★★★★★　DB情報取得よく使う関数　★★★★★

    public virtual bool CanConnect()
    {
        try
        {
            using (SqlConnection dbConnect = Open())
            {
                return dbConnect.State == ConnectionState.Open;
            }
        }
        catch
        {
            return false;
        }
    }

    public virtual bool ExistsObject(string name)
    {
        string sql = string.Format("IF OBJECT_ID('{0}','U') IS NOT NULL SELECT 1 ELSE SELECT 0;", name);
        return CConvert.ToInt(ExecuteScalar(sql)) == 1;
    }

    /// <summary>
    /// DBの一覧を取得
    /// </summary>
    /// <returns></returns>
    public virtual bool ExistsDataBase(string name)
    {
        string sql = string.Format("IF EXISTS(SELECT -1 FROM sys.databases WHERE name = '{0}') SELECT 1 ELSE SELECT 0;", name);
        return CConvert.ToInt(ExecuteScalar(sql)) == 1;
    }

    public virtual bool ExistsProctrue(string name)
    {
        string sql = string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) SELECT 1 ELSE SELECT 0;", name);
        return CConvert.ToInt(ExecuteScalar(sql)) == 1;

    }

    /// <summary>
    /// Identity項目があるかどうか
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual bool IsExistsIdentity(string tableName)
    {
        string sql = string.Format(@"IF EXISTS(Select Top 1 1 FROM sysobjects WHERE objectproperty(id, 'TableHasIdentity') = 1 AND upper(name) = upper('{0}')) 
 SELECT 1 ELSE SELECT 0", tableName);
        bool hasIdentity = CConvert.ToInt(ExecuteScalar(sql)) == 1;
        return hasIdentity;
    }

    /// <summary>
    /// DBの生成
    /// </summary>
    /// <returns></returns>
    public virtual bool CreateSimpleDB(string tagDBName)
    {
        string sql = string.Format(@"IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}') RETURN;
CREATE DATABASE {0};
ALTER DATABASE [{0}] SET RECOVERY SIMPLE;
ALTER DATABASE [{0}] SET AUTO_SHRINK ON;", tagDBName);
        return ExecuteNonQuery(sql) != -1;
    }

    public virtual bool CreateSimpleDB(string tagDBName, string logicName, string filePath)
    {
        if (!filePath[filePath.Length - 1].Equals(Path.DirectorySeparatorChar)) { filePath += Path.DirectorySeparatorChar.ToString(); }
        string sql = string.Format(@"IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}') RETURN;
CREATE DATABASE [{0}] ON  PRIMARY 
( NAME = N'{1}', FILENAME = N'{2}{0}.mdf')
 LOG ON 
( NAME = N'{1}_log', FILENAME = N'{2}{0}.ldf');
ALTER DATABASE [{0}] SET RECOVERY SIMPLE;
ALTER DATABASE [{0}] SET AUTO_SHRINK ON;", tagDBName, logicName, filePath);
        return ExecuteNonQuery(sql) != -1;
    }

    /// <summary>
    /// DB所在フォルダー
    /// </summary>
    /// <returns></returns>
    public string GetDBFileDir()
    {
        string file = CConvert.ToString(ExecuteScalar(string.Format("SELECT physical_name FROM sys.database_files WHERE name = '{0}'", Catalog)));
        return Path.GetDirectoryName(file);
    }

    /// <summary>
    /// メモリーテーブルを使用する
    /// </summary>
    /// <returns></returns>
    public virtual bool SetMemoryTableEnabled()
    {
        string sql = string.Format(@"ALTER DATABASE {0} ADD FILEGROUP [FG_MemoryTable] CONTAINS MEMORY_OPTIMIZED_DATA", Catalog);
        if (ExecuteNonQuery(sql) == -1) { return false; }

        string dir = GetDBFileDir();
        if (!dir[dir.Length - 1].Equals(Path.DirectorySeparatorChar)) { dir += Path.DirectorySeparatorChar.ToString(); }

        sql = string.Format(@"ALTER DATABASE {0}  
ADD FILE  
(  
    NAME = '{0}_MemoryTable',  
    FILENAME ='{1}{0}'  
)  
TO FILEGROUP [FG_MemoryTable];", Catalog, dir);
        return ExecuteNonQuery(sql) != -1;
    }

    /// <summary>
    /// DBの一覧を取得
    /// </summary>
    /// <returns></returns>
    public virtual DataSet GetDataBaseList()
    {
        string sql = "SELECT name FROM sys.databases WHERE name NOT IN('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')";
        return GetDataSet(sql);
    }

    /// <summary>
    /// DBの削除
    /// </summary>
    /// <returns></returns>
    public virtual bool DropProcedure(string name)
    {
        string sql = string.Format(@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}];", name);
        return ExecuteNonQuery(sql) != -1;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>       
    public virtual bool DropFunction(string name)
    {
        string sql = string.Format(@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[{0}];", name);
        return ExecuteNonQuery(sql) != -1;
    }

    /// <summary>
    /// DBの削除
    /// </summary>
    /// <returns></returns>
    public virtual bool DropDB(string tagDBName)
    {
        string sql = string.Format("DROP DATABASE {0};", tagDBName);
        return ExecuteNonQuery(sql) != -1;
    }

    public virtual bool GetDDL(string path, string itemName)
    {
        System.Data.DataTable ddl = GetDataTable(string.Format("SELECT definition FROM sys.sql_modules WHERE execute_as_principal_id is null and object_id =(OBJECT_ID(N'{0}'))", itemName));
        if (ddl != null && ddl.Rows.Count > 0)
        {
            FileOperation.Delete(path + Path.DirectorySeparatorChar + itemName + ".sql");
            System.IO.File.WriteAllText(path + Path.DirectorySeparatorChar + itemName + ".sql", ddl.Rows[0]["definition"].ToString(), System.Text.Encoding.Default);
            ddl.Dispose();
            return true;
        }
        else
        {
            return false;
        }
    }

    public virtual bool GetDDLByArray(string path, string[] ogjList)
    {
        List<string> list = new List<string>(ogjList);
        return GetDDLByList(path, list, null);
    }

    public virtual bool GetDDLByArray(string path, string[] ogjList, Action<string, int> callBack)
    {
        List<string> list = new List<string>(ogjList);
        return GetDDLByList(path, list, callBack);
    }

    public virtual bool GetDDLByList(string path, List<string> ogjList, Action<string, int> callBack)
    {
        foreach (string item in ogjList)
        {
            GetDDL(path, item);
            if (callBack != null) { callBack(item, 0); }
        }
        return true;
    }

    public virtual bool GetAllDDL(string path)
    {
        return GetAllDDL(path, null);
    }

    public virtual bool GetAllDDL(string path, Action<string, int> callBack)
    {
        System.Data.DataTable table = null;

        //関数
        table = GetDataTable("SELECT * FROM sys.objects WHERE type in (N'FN', N'IF', N'TF', N'FS', N'FT') and is_ms_shipped = 0");
        int maxCount = table.Rows.Count;
        int curCount = 0;
        foreach (DataRow row in table.Rows)
        {
            curCount++;
            System.Diagnostics.Debug.WriteLine(string.Format("{0}/{1} : {2}", curCount, maxCount, row["name"]));

            if (CConvert.IsSmallAlpha(row["name"].ToString().Substring(0, 1))) { continue; }
            GetDDL(path, row["name"].ToString());
            if (callBack != null) { callBack(row["name"].ToString(), 6); }
        }
        table.Dispose();

        //プロシージャ
        table = GetDataTable("SELECT * FROM sys.objects WHERE type in (N'P', N'PC') and is_ms_shipped = 0");
        maxCount = table.Rows.Count;
        curCount = 0;
        foreach (DataRow row in table.Rows)
        {
            curCount++;
            System.Diagnostics.Debug.WriteLine(string.Format("{0}/{1} : {2}", curCount, maxCount, row["name"]));

            if (CConvert.IsSmallAlpha(row["name"].ToString().Substring(0, 1))) { continue; }
            GetDDL(path, row["name"].ToString());
            if (callBack != null) { callBack(row["name"].ToString(), 5); }
        }
        table.Dispose();

        return true;
    }

    /// <summary>
    /// テーブル一覧を取得
    /// </summary>
    /// <returns></returns>
    public override DataTable GetTableList()
    {
        return GetDataTable(@"Select Name FROM sys.sysobjects Where XType='U' AND NOT name IN('sysdiagrams','dtproperties') ORDER BY Name");
    }

    /// <summary>
    /// テーブル一覧を取得
    /// </summary>
    /// <returns></returns>
    public override DataTable GetTableList(string whereAdding)
    {
        return GetDataTable($"Select Name FROM SysObjects Where XType='U' AND NOT name IN('sysdiagrams','dtproperties') AND {whereAdding} ORDER BY Name");
    }

    /// <summary>
    /// テーブル名より項目一覧を取得
    /// </summary>
    /// <param name="table"></param>
    /// <returns></returns>
    public virtual DataTable GetFieldList(string table)
    {
        string sql = @$"SELECT CASE WHEN C.xtype = 'PK' THEN 1 ELSE 0 END PKFlg ,A.name Field,D.name Type,A.is_nullable,A.max_length,
       CASE WHEN D.name = 'int' THEN 7 
	        WHEN D.name = 'tinyint' THEN 2 
			WHEN D.name = 'bit' THEN 1
			WHEN D.name = 'nvarchar' THEN A.max_length / 2
	        WHEN D.name IN ('date','datetime') THEN 10 
       ELSE A.max_length END MaxLen,
	   CASE WHEN B.value IS NULL THEN A.name ELSE CAST(B.value AS NVARCHAR) END Caption
FROM sys.columns A
     Left outer join (SELECT syscolumns.name,sysobjects.xtype 
						FROM syscolumns,sysobjects,sysindexes,sysindexkeys 
						WHERE syscolumns.id = object_id('{table}') 
						  AND sysobjects.xtype = 'PK' 
						  AND sysobjects.parent_obj = syscolumns.id   
						  AND sysindexes.id = syscolumns.id 
						  AND sysobjects.name = sysindexes.name 
						  AND sysindexkeys.id = syscolumns.id 
						  AND sysindexkeys.indid = sysindexes.indid 
						  AND syscolumns.colid = sysindexkeys.colid) C 
	 On C.name = A.name
     LEFT JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.column_id = B.minor_id AND B.name='MS_Description'
     LEFT JOIN sys.types D ON A.user_type_id = D.user_type_id
WHERE A.object_id = object_id('{table}')
";
        return GetDataTable(sql);
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="identityOn"></param>
    /// <returns></returns>
    public virtual string GetTableScript(string tableName, int identityOn)
    {
        try
        {
            string sql = @"IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_GetCreateTableScript]') AND type in (N'P', N'PC')) SELECT 1 ELSE SELECT 0;";
            if (CConvert.ToInt(ExecuteScalar(sql)) == 0)
            {
                sql = ResourceFile.GetScript(ResourceFile.ResType.SP_GetCreateTableScript);
                ExecuteNonQuery(sql);
            }

            StringBuilder script = new StringBuilder();
            using (DataTable table = GetDataTable(string.Format(@"EXECUTE SP_GetCreateTableScript '{0}',{1}", tableName, identityOn)))
            {
                foreach (DataRow row in table.Rows)
                {
                    script.AppendLine(row["v_desc"].ToString());
                }
            }
            return script.ToString();
        }
        catch
        {
            return string.Empty;
        }
    }

    #endregion

    #endregion

    #region  ★★★★★　Private Function　★★★★★

    /// <summary> SQLのメッセージ通知 例：RAISERROR('10,SHORI,1122',0,1) WITH NOWAIT
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void DBConnect_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        if (SqlInfoMessageEvent != null) { SqlInfoMessageEvent(sender, e); }
        System.Diagnostics.Debug.WriteLine(e.Message);
    }

    private void ErrClear()
    {
        ErrNo = 0;
        ErrInfo = string.Empty;
    }

    public SqlConnection Open()
    {
        SqlConnection dbConnect = new SqlConnection();
        dbConnect.ConnectionString = ConnectionString;
        if (MessageEventEnabled)
        {
            dbConnect.FireInfoMessageEventOnUserErrors = FireInfoMessageEventOnUserErrors;  //SqlExceptionの場合、出力しない
            dbConnect.InfoMessage += new SqlInfoMessageEventHandler(DBConnect_InfoMessage);
        }
        dbConnect.Open();
        return dbConnect;
    }

    /// <summary>
    /// 一時的なエラー
    /// </summary>
    /// <param name="ex"></param>
    /// <returns></returns>
    private bool IsTransient(Exception ex, string sql)
    {
        SqlException exSql = null;
        if (ex is SqlException)
        {
            exSql = ex as SqlException;
            Message_id = exSql.Number;
            ErrNo = exSql.ErrorCode;
        }
        else
        {
            ErrNo = -1;
        }
        ErrInfo = ex.Message;
        OperationLog.Instance.WriteLog(string.Format("MsSqlNet.IsTransient({0},{1},{2},{3})", Message_id, ErrNo, ErrInfo, sql), OperationLog.LogLevelType.Normal);

        if (exSql != null)
        {
            // Enumerate through all errors found in the exception.
            foreach (SqlError err in exSql.Errors)
            {
                switch (err.Number)
                {
                    // SQL Error Code: 40501
                    // The service is currently busy. Retry the request after 10 seconds.
                    // Code: (reason code to be decoded).
                    case 40501:
                    case 10928:
                    case 10929:
                    case 10053:
                    case 10054:  //サーバーに要求を送信しているときに、トランスポート レベルのエラーが発生しました。 (provider: TCP プロバイダ, error: 0 - 既存の接続はリモート ホストに強制的に切断されました。)
                    case 10060:
                    case 40197:
                    case 40540:
                    case 40613:
                    case 40143:
                    case 233:
                    case 64:
                    case 1205:  //トランザクション (プロセス ID %1!) が、%2! 個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。
                    case 3635:   //データベース ID %2!、ファイル ID %3! の '%1!' メタデータの処理中にエラーが発生しました。トランザクション='%4!'。詳細コンテキスト='%5!'。場所='%6!'(%7!)。操作を再試行してください。問題が解決しない場合は、データベース管理者に問い合わせて、ロックとメモリ構成を調査してください。アプリケーションで競合のデッドロックが生じている可能性を調査してください。
                    case 5231:  //オブジェクト ID %1! (オブジェクト '%2!'): このオブジェクトを確認のためにロックしようとして、デッドロックが発生しました。このオブジェクトはスキップされたので、処理されません。
                    case 40095:  //レプリケーション トランザクション (プロセス ID %1!) が、%2! 個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。操作が再試行されます。
                        return true;
                }
            }
        }
        else if (ex is TimeoutException)
        {
            return true;
        }
        else if (ErrInfo.Contains("現在の接続の状態は '終了"))
        {
            return true;
        }
        return false;
    }

    private OperationLog.LogLevelType GetLogLevel()
    {
        return IsTimerAccess ? OperationLog.LogLevelType.Level8 : OperationLog.LogLevelType.Level7;
    }

    #endregion

}