HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

/*-------------------------------------------------------------------------------

* 

* *****************  Version 1  *****************

* author: HumKyung Baek Date: 2014.09.29 - access MSSql 

--------------------------------------------------------------------------------*/

namespace ID2PSN

{

    public class AppMSSqlDatabase : AbstractDatabase<SqlConnection, SqlCommand, SqlDataAdapter>

    {

        private const string oConnString = "server={0};uid={1};pwd={2};database={3}";

        /// <summary>

        /// 호스트

        /// </summary>

        public string Host { get; set; }

        /// <summary>

        /// 아이디

        /// </summary>

        public string Id { get; set; }

        /// <summary>

        /// 암호

        /// </summary>

        public string Password { get; set; }

        /// <summary>

        /// 데이타베이스

        /// </summary>

        public string Database { get; set; }

        /// <summary>

        /// connection string

        /// </summary>

        public string ConnectionString { get; set; }

        /// 15.06.23 added by soohyun

        public const int WINDOWS_Authen = 0;

        public const int SERVER_Authen = 1;

        private static int SqlAuthentication_;

        public int SqlAuthentication

        {

            set { SqlAuthentication_ = value; }

        }

        // up to here

        /// <summary>

        /// return connect string to MSSql

        /// </summary>

        /// <author>humkyung</author>

        /// <date>2014.09.29</date>

        /// <returns>Connection string</returns>

        protected override string GetConnectionString()

        {

            string connString = this.ConnectionString;

            if (string.IsNullOrEmpty(this.ConnectionString))

            {

                if (SERVER_Authen == SqlAuthentication_)

                {

                    connString = string.Format(oConnString, Host, Id, Password, Database);

                }

                else if (WINDOWS_Authen == SqlAuthentication_)

                {

                    connString = string.Format("Data Source={0};Database={1};Integrated Security=SSPI", Host, Database);

                }

            }

            return connString;

        }

        /// <summary>

        /// get table name list

        /// </summary>

        /// <author>humkyung</author>

        /// <date>2014.09.29</date>

        /// <param name="sTableName"></param>

        /// <returns></returns>

        public override List<string> GetTableNames()

        {

            List<string> res = new List<string> { };

            {

                string query = "SELECT NAME FROM SYS.TABLES";

                using (var ds = ExecuteDataSet(GetSqlStringCommand(query)))

                {

                    foreach (DataRow dr in ds.Tables[0].Rows)

                    {

                        res.Add(dr["NAME"].ToString());

                    }

                }

            }

            return res;

        }

        public override List<string> GetCommonTableNames()

        {

            List<string> res = new List<string> { };

            {

                string query = "SELECT T.name AS table_name, C.name AS column_name" +

                    "FROM ARS_COMMON.sys.tables AS T INNER JOIN ARS_COMMON.sys.columns AS C ON T.object_id = C.object_id WHERE T.name = 'PSN_REVISION'";

                using (var ds = ExecuteDataSet(GetSqlStringCommand(query)))

                {

                    foreach (DataRow dr in ds.Tables[0].Rows)

                    {

                        res.Add(dr["column_name"].ToString());

                    }

                }

            }

            return res;

        }

        public override List<string> GetColumnNames(string TableName)

        {

            List<string> res = new List<string> { };

            {

                //string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + TableName + "'";

                string query = "select name from dbo.syscolumns where id = object_id('" + TableName + "')";

                using (var ds = ExecuteDataSet(GetSqlStringCommand(query)))

                {

                    foreach (DataRow dr in ds.Tables[0].Rows)

                    {

                        res.Add(dr["name"].ToString());

                    }

                }

            }

            return res;

        }

        /// <summary>

        /// get column list of table

        /// </summary>

        /// <author>humkyung</author>

        /// <date>2012.07.17</date>

        /// <param name="oColumnList"></param>

        /// <param name="sTableName"></param>

        /// <returns></returns>

        public List<ColInfo> GetColumnInfoListOf(string sTableName)

        {

            List<ColInfo> res = new List<ColInfo> { };

            string sSql = string.Format(

                            @"SELECT c.name 'Column Name', t.Name 'Data type'

                            FROM sys.columns c 

                            INNER JOIN 

                            sys.types t ON c.user_type_id = t.user_type_id 

                            LEFT OUTER JOIN 

                            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id 

                            LEFT OUTER JOIN 

                            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id 

                            WHERE 

                            c.object_id = OBJECT_ID('{0}')", sTableName);

            using (DbDataReader reader = ExecuteReader(GetSqlStringCommand(sSql)))

            {

                while (reader.Read())

                {

                    res.Add(new ColInfo() { Name = reader.GetString(0), DataType = reader.GetString(1) });

                }

            }

            return res;

        }

        /// <summary>

        /// execute non-query

        /// </summary>

        /// <author>humkyung</author>

        /// <date>2014.09.29</date>

        /// <param name="sSql"></param>

        /// <returns>int</returns>

        public new int ExecuteNonQuery(string sSql)

        {

            DbCommand cmd = GetSqlStringCommand(sSql);

            return cmd.ExecuteNonQuery();

        }

    }

}