2016년 9월 6일 화요일

Simple C# Database access framework



namespace NIHDatabase.RDS
{
    internal class NIHDbContext : DbContext
    {
        internal NIHDbContext(string connStr) : base(connStr) { }
    }
    public class NIHRdsService
    {
        public string _connStr { get; set; }
        public NIHRdsService() { }

        #region Multi Result
        /// <summary>
        /// Multi Result
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public DataSet SqlMultiResult(string procedureName, object parameters, Func<object, string, ProcedureAndParameterModel> func)
        {
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand  = new SqlCommand(procedureName, conn);
                adapter.SelectCommand.Parameters.AddRange(func(parameters, procedureName).Parameters);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.Fill(ds);
            }

            return ds;
        }
        #endregion

        #region SqlQuery<T>
        /// <summary>
        /// 특정 조건에 맞는 단일 데이터 조회
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <param name="func"></param>
        /// <returns>T</returns>
        public async Task<T> SqlQuerySingle<T>(string procedureName, object parameters, Func<object, string, ProcedureAndParameterModel> func)
        {
            using (var context = new NIHDbContext(_connStr))
            {
                ProcedureAndParameterModel _model = func(parameters, procedureName);
                return await context.Database.SqlQuery<T>(_model.ProcedureName, _model.Parameters).SingleAsync();
            }
        }
        /// <summary>
        /// 파라미터 없이 전체 테이블 스캔
        /// </summary>
        /// <typeparam name="T">object</typeparam>
        /// <param name="procedureName">string</param>
        /// <returns>List<T></returns>
        public async Task<List<T>> SqlQueryMulti<T>(string procedureName)
        {
            using (var context = new NIHDbContext(_connStr))
            {
                return await context.Database.SqlQuery<T>(procedureName).ToListAsync();
            }
        }
        /// <summary>
        /// 특정 조건을 맞족하는 데이터 레코드 조회
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <param name="func"></param>
        /// <returns>List<T></returns>
        public async Task<List<T>> SqlQueryMulti<T>(string procedureName, object parameters, Func<object, string, ProcedureAndParameterModel> func)
        {
            using (var context = new NIHDbContext(_connStr))
            {
                ProcedureAndParameterModel _model = func(parameters, procedureName);
                return await context.Database.SqlQuery<T>(_model.ProcedureName, _model.Parameters).ToListAsync();
            }
        }
        #endregion

        #region ExecuteSqlCommandAsync
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procedureName"></param>
        /// <param name="parameters"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        public async Task<int> ExecuteCommandAsync<T>(string procedureName, object parameters, Func<object, string, ProcedureAndParameterModel> func)
        {
            using (var context = new NIHDbContext(_connStr))
            {
                ProcedureAndParameterModel _model = func(parameters, procedureName);
                return await context.Database.ExecuteSqlCommandAsync(_model.ProcedureName, _model.Parameters);
            }
        }
        #endregion
    }
}

SqlQuery 문 생성, DataTable에서 List로 변환

namespace NIHDatabase.RDS
{
    public static class DataHelper
    {
        /// <summary>
        /// 프로시저명과, 파라미터 모델을 받아서
        /// ProcedureAndParameter타입으로 반환
        /// </summary>
        /// <typeparam name="T">Parameter Entity</typeparam>
        /// <param name="model"></param>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static ProcedureAndParameterModel GetSqlQueryString<T>(this T model, string ProcedureName)
        {
            List<SqlParameter> _parameters = new List<SqlParameter>();
            StringBuilder      _quryString = new StringBuilder(ProcedureName);

            if(model!=null)
            {
                bool _first = true;
                foreach (var prop in typeof(T).GetProperties())
                {
                    // 파라미터 명
                    var _name  = prop.Name;
                    // 파라미터 값
                    var _value = prop.GetValue(model, null);
                    // SqlParameter 값이 NULL이면 DBNull.Value로 설정
                    var _param = new SqlParameter($"@{_name}", _value == null ? DBNull.Value : _value);
                    // 파라미터 배열에 추가
                    _parameters.Add(_param);

                    // Sql Query문 설정
                    if (_first)
                    {
                        _quryString.Append($" @{_name}");
                        _first = false;
                    }
                    else
                    {
                        _quryString.Append($", @{_name}");
                    }
                }
            }
            
            return new ProcedureAndParameterModel { ProcedureName = _quryString.ToString(), Parameters = _parameters.ToArray() };
        }

        /// <summary>
        /// DataTable => List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns>List<T></returns>
        public static List<T> ToList<T>(this DataTable table) where T : new()
        {
            IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
            List<T> result = new List<T>();

            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<T>((DataRow)row, properties);
                result.Add(item);
            }

            return result;
        }

        private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
        {
            T item = new T();

            foreach (var prop in properties)
            {
                prop.SetValue(item, row[prop.Name], null);
            }
            return item;
        }
    }
}

2016년 9월 5일 월요일

mssql login failed 18456


ERROR : mssql login failed 18456

위와 같은 에러가 발생하면 SSMS(Sql Server Managerment Studio)에서 Windows Authentication 방식으로 로그인하고 다음 이미지에서와 같이 진행

2016년 4월 30일 토요일

[C#] Callback Func



예제 1

namespace Cshop_v6._0
{
    delegate int GetResultDelegate();

    class Target
    {
        public void Do(GetResultDelegate getResult)
        {
            Console.WriteLine(getResult()); // 콜백 메서드 호출
        }
    }

    class Source
    {
        public int GetResult() // 콜백 용도로 전달된 메서드
        {
            return 10;
        }

        public void Test()
        {
            Target target = new Target();
            target.Do(new GetResultDelegate(this.GetResult));
        }
    }
}

예제 2

namespace Cshop_v6._0
{
    delegate int GetResultDelegate(int x, int y);

    class Target
    {
        public void Do(GetResultDelegate getResult, int x, int y)
        {
            Console.WriteLine(getResult(x, y));
        }
    }

    class Source
    {
        public static int GetResult(int x, int y)
        {
            return x * y;
        }

        public static void Main()
        {
            Target target = new Target();
            GetResultDelegate gd = GetResult;
            target.Do(gd, 5, 10);
        }
    }
}