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