private IEnumerable <string> GetDataFromDb(string pathArg)
{
_repositiry = new AdoRepository(_arg.ConnectionString);
var path = Path.Combine(Directory.GetCurrentDirectory(), PathScripts, pathArg);
var sqlArray = File.ReadAllLines(path);
var sql = string.Join(Environment.NewLine, sqlArray);
sql = sql.Replace(Scheme, _arg.Scheme);
sql = sql.Replace(TableName, _arg.TableName);
DataTable table;
DataSet set;
using (set = _repositiry.GetDataSetBySimpleSql(sql))
{
if (set == null || set.Tables.Count == 0)
{
throw new ArgumentException(ErrorDidNotGetData);
}
table = set.Tables[0];
}
var list = (from DataRow row in table.Rows select row[0].ToString()).ToList();
return(list);
}
/// <summary>
/// 단순 쿼리문에 대해서, ExecuteNonQuery를 수행합니다.
/// </summary>
/// <param name="queries"></param>
protected virtual void ExecuteQueries(IEnumerable <string> queries)
{
foreach (var query in queries.Where(q => q.IsNotWhiteSpace()))
{
AdoRepository.ExecuteNonQuery(query);
}
}
private void SetStringArrayJS(IncomeStatementTab expenses, string ConnectionString)
{
//Writing an array with this format for the chart
/*['Month', 'Bolivia', 'Ecuador', 'Madagascar', 'Papua New Guinea', 'Rwanda'],
* ['2004/05', 165, 938, 522, 998, 450],
* ['2005/06', 135, 1120, 599, 1268, 288],
* ['2006/07', 157, 1167, 587, 807, 397],
* ['2007/08', 139, 1110, 615, 968, 215],
* ['2008/09', 136, 691, 629, 1026, 366] */
var mostExpensiveCategories = (from ex in expenses.report
where ex.bitmap == 1
orderby ex.total descending
select ex.category).Take(4);
var ado = new AdoRepository <POCO.yearlyExpensePerCategoryLine>(ConnectionString);
_yearlyExpensePerCategoryLines = ado.YearlyExpensePerCategory(_dossierId,
mostExpensiveCategories.ElementAt(0),
mostExpensiveCategories.ElementAt(1),
mostExpensiveCategories.ElementAt(2),
mostExpensiveCategories.ElementAt(3),
true);
StringBuilder sb = new StringBuilder();
//header
sb.Append("['year'");
foreach (var cat in mostExpensiveCategories)
{
sb.Append(",'" + cat + "'");
}
sb.Append("]");
_JSDataArray += sb.ToString() + "\n";
sb = new StringBuilder();
for (int i = _beginYear; i <= _endYear; i++)
{
sb.Append(",['" + i + "'");
foreach (var cat in mostExpensiveCategories)
{
decimal tot = (from line in _yearlyExpensePerCategoryLines
where line.year == i && line.category == cat
select line.total).FirstOrDefault();
string strTot = tot.ToString().Replace(',', '.');
sb.Append("," + strTot);
}
sb.Append("]");
_JSDataArray += sb.ToString() + "\n";
sb = new StringBuilder();
}
}
public void CanCloseDataReader()
{
try {
using (var reader = AdoRepository.ExecuteReader("SELECT * FROM sysobjects")) {
// 예외를 일으켜본다. 그래도 Data Reader가 제대로 닫히는지 본다.
throw new InvalidOperationException("고의로 일으킨 예외입니다.");
}
}
catch {
// eat exception
}
}
public void FluentByNameMapper_Load()
{
INameMapper nameMapper = new CapitalizeNameMapper();
using (var cmd = AdoRepository.GetCommand("CustOrderHist2")) {
var orderHistories = AdoRepository.ExecuteInstance <CustomerOrderHistory>(nameMapper, cmd, CustomerTestParameter);
Assert.Greater(orderHistories.Count, 0);
CollectionAssert.AllItemsAreNotNull((ICollection)orderHistories);
CollectionAssert.AllItemsAreInstancesOfType((ICollection)orderHistories, typeof(CustomerOrderHistory));
}
}
public void ListRecordsFullSearch(int dossierId,
string ConnectionString,
string BeginDate,
string EndDate,
int recordCategoryId,
int recordSubcategoryId,
string description,
string comment)
{
ObjectCache cache = MemoryCache.Default;
SetParamsForSearch(dossierId, beginDate, endDate, CurrentPage, ItemsPerPage);
//TODO: write stored prodecure
_listRecordsSess = null;
AdoRepository <POCO.record> AdoRep = new AdoRepository <POCO.record>(ConnectionString);
if (!IsPostBack)
{
//TODO: Implement a caching system different than Session.
//Try Object Cache
_listRecordsSess = AdoRep.recordFullSearch(dossierId,
beginDate,
endDate,
recordCategoryId,
recordSubcategoryId,
description,
comment);
//var list = _context.sp_records_fullsearch(dossierId, recordSubcategoryId, recordCategoryId, beginDate, endDate, description, comment);
cache.Remove(_ClientSessionId);
cache.Add(_ClientSessionId, _listRecordsSess, DateTimeOffset.MaxValue);
}
else
{
_listRecordsSess = (List <POCO.record>)cache[_ClientSessionId];
//reading object from cache
}
SetPagingParams();
SetPageElems();
}
public static void InsertActionTest()
{
var originalCount = TotalCount();
var row = AdoRepository.ExecuteNonQuery(SQL_REGION_INSERT);
Assert.AreEqual(1, row);
var row2 = AdoRepository.ExecuteNonQuery(SQL_REGION_INSERT2);
Assert.AreEqual(1, row2);
var insertedCount = TotalCount();
Assert.AreEqual(originalCount + 2, insertedCount);
}
/// <summary>
/// 요청 쿼리 문을 수행하여, <see cref="ResultSet"/> 으로 빌드하여, 응답 결과를 적용합니다.
/// </summary>
protected virtual void OpenQuery(RequestItem requestItem, ResponseItem responseItem, IAdoParameter[] adoParameters)
{
if (IsDebugEnabled)
{
log.Debug("쿼리문을 수행하여 결과셋을 설정합니다...");
}
using (var cmd = AdoRepository.GetCommand(requestItem.Query)) {
var reader = AdoRepository.ExecuteReader(cmd, adoParameters);
var resultSet = reader.CreateResultSet(NameMapper,
requestItem.FirstResult.GetValueOrDefault(),
requestItem.MaxResults.GetValueOrDefault(int.MaxValue));
responseItem.ResultSet = resultSet;
}
}
public void NotCommitted()
{
var originalCount = TotalCount();
AdoWith.ForTesting(() => {
AdoRepository.ExecuteNonQuery(SQL_REGION_INSERT);
AdoRepository.ExecuteNonQuery(SQL_REGION_INSERT2);
var insertedCount = TotalCount();
Assert.AreEqual(originalCount + 2, insertedCount);
});
var rollbackCount = TotalCount();
Assert.AreEqual(originalCount, rollbackCount);
}
/// <summary>
/// 요청 쿼리 문을 수행하는데,
/// <see cref="IAdoRepository.ExecuteNonQuery(string,IAdoParameter[])" /> 나
/// <see cref="IAdoRepository.ExecuteScalar(string,IAdoParameter[])"/> 를 실행합니다.
/// </summary>
/// <param name="requestItem"></param>
/// <param name="responseItem"></param>
/// <param name="adoParameters"></param>
protected virtual void ExecuteQuery(RequestItem requestItem, ResponseItem responseItem, IAdoParameter[] adoParameters)
{
// Scalar 값 구하기
if (requestItem.ResponseFormat == ResponseFormatKind.Scalar)
{
if (IsDebugEnabled)
{
log.Debug("ExecuteNonScalar 를 수행하여, ResultValue 값을 설정합니다...");
}
responseItem.ResultValue = AdoRepository.ExecuteScalar(requestItem.Query, adoParameters);
return;
}
// 쿼리문이라면, 실행
var query = requestItem.Query;
if (AdoTool.IsSqlString(query))
{
if (IsDebugEnabled)
{
log.Debug("ExecuteNonQuery 를 수행하고, 영향받은 행의 수를 ResultValue에 설정합니다...");
}
responseItem.ResultValue = AdoRepository.ExecuteNonQuery(requestItem.Query, adoParameters);
return;
}
if (IsDebugEnabled)
{
log.Debug("ExecuteProcedure를 수행하고, Parameter 정보들을 ResultSet에 설정합니다.");
}
// Procedure 라면, Output Parameter 등을 ResultSet으로 반환함.
//! 단 Procedure가 ResultSet을 반환해야 하는 경우는 OpenQuery를 수행할 수 있도록 ResponseFormat 을 ResponseFormatKind.ResultSet 으로 할 것
//
var parameters = AdoRepository.ExecuteProcedure(query, adoParameters);
if (parameters != null)
{
responseItem.ResultValue = parameters.GetReturnValue();
var row = new ResultRow(parameters.ToDictionary(p => p.Name, p => p.Value));
responseItem.ResultSet.Add(row);
}
}
public void Batch_ExecuteNonQuery()
{
const int BatchSize = 30;
const string commandText = @"DELETE FROM dbo.Region where RegionID > @RegionId";
var commandSet = new NSoft.NFramework.Data.SqlServer.SqlCommandSet
{
Connection = (SqlConnection)AdoTool.CreateTransactionScopeConnection(AdoRepository.Db)
};
commandSet.Append((SqlCommand)AdoRepository.GetSqlStringCommand(SQL_REGION_INSERT));
commandSet.Append((SqlCommand)AdoRepository.GetSqlStringCommand(SQL_REGION_INSERT2));
for (int i = 0; i < BatchSize; i++)
{
var command = AdoRepository.GetSqlStringCommand(commandText, new AdoParameter("RegionId", i + 100, DbType.Int32));
commandSet.Append((SqlCommand)command);
}
var executedCount = commandSet.ExecuteNonQuery();
// 새로 추가한 위의 2개가 영향을 받습니다.
executedCount.Should().Be(4);
}
public void GetDbType_Binary()
{
var type = typeof(byte[]);
Assert.Equal(DbType.Binary, AdoRepository <TestEntity> .GetDbType(type));
}
static void Main(string[] args)
{
var adoRepo = new AdoRepository <Truck>(@"Data Source=DESKTOP-C5BMDJ8\SQLEXPRESS;Initial Catalog=Shipment;Integrated Security=True");
var adoStopwatch = new Stopwatch();
adoStopwatch.Start();
var rnd = new Random();
var obj = new object();
Parallel.For(0, 100000, (i) =>
{
var model = new Truck
{
BrandName = $"ado brand {i}",
Volume = rnd.Next(10000, 100000),
FuelConsumption = rnd.Next(10, 30),
Payload = rnd.Next(9000, 50000),
RegistrationNumber = $"TEST{i}",
Year = 2021
};
lock (obj)
{
adoRepo.Create(model);
adoRepo.CommitChanges();
model = adoRepo.GetByField(new Dictionary <System.Linq.Expressions.Expression <Func <Truck, object> >, object> {
{ tr => tr.BrandName, model.BrandName }
});
}
Console.WriteLine($"{i}");
});
adoStopwatch.Stop();
adoRepo.Dispose();
var adoDiconnectedRepo = new AdoRepositoryDisconnectedAproach <Truck>(@"Data Source=DESKTOP-C5BMDJ8\SQLEXPRESS;Initial Catalog=Shipment;Integrated Security=True");
var adoDiconnectedStopwatch = new Stopwatch();
adoDiconnectedStopwatch.Start();
Parallel.For(0, 100000, (i) =>
{
var model = new Truck
{
BrandName = $"ado dis brand {i}",
Volume = rnd.Next(10000, 100000),
FuelConsumption = rnd.Next(10, 30),
Payload = rnd.Next(9000, 50000),
RegistrationNumber = $"TEST{i}",
Year = 2021
};
lock (obj)
{
adoDiconnectedRepo.Create(model);
adoDiconnectedRepo.CommitChanges();
model = adoDiconnectedRepo.GetByField(new Dictionary <System.Linq.Expressions.Expression <Func <Truck, object> >, object> {
{ tr => tr.BrandName, model.BrandName }
});
}
Console.WriteLine($"{i}");
});
adoDiconnectedStopwatch.Stop();
adoDiconnectedRepo.Dispose();
var efRepo = new EFRepository <Truck>();
var efStopwatch = new Stopwatch();
efStopwatch.Start();
Parallel.For(0, 100000, (i) =>
{
var model = new Truck
{
BrandName = $"ef brand {i}",
Volume = rnd.Next(10000, 100000),
FuelConsumption = rnd.Next(10, 30),
Payload = rnd.Next(9000, 50000),
RegistrationNumber = $"TEST{i}",
Year = 2021
};
lock (obj)
{
efRepo.Create(model);
efRepo.CommitChanges();
model = efRepo.GetByField(new Dictionary <System.Linq.Expressions.Expression <Func <Truck, object> >, object> {
{ tr => tr.BrandName, model.BrandName }
});
}
Console.WriteLine($"{i}");
});
efStopwatch.Stop();
efRepo.Dispose();
var dpRepo = new EFRepository <Truck>();
var dpStopwatch = new Stopwatch();
dpStopwatch.Start();
Parallel.For(0, 100000, (i) =>
{
var model = new Truck
{
BrandName = $"dapper brand {i}",
Volume = rnd.Next(10000, 100000),
FuelConsumption = rnd.Next(10, 30),
Payload = rnd.Next(9000, 50000),
RegistrationNumber = $"TEST{i}",
Year = 2021
};
lock (obj)
{
dpRepo.Create(model);
dpRepo.CommitChanges();
model = dpRepo.GetByField(new Dictionary <System.Linq.Expressions.Expression <Func <Truck, object> >, object> {
{ tr => tr.BrandName, model.BrandName }
});
}
Console.WriteLine($"{i}");
});
dpStopwatch.Stop();
dpRepo.Dispose();
Console.WriteLine($"ADO: {adoStopwatch.Elapsed:'HH':'mm':'ss.fffffff'}\r\nADO disc: {adoDiconnectedStopwatch.Elapsed:'HH':'mm':'ss.fffffff'}\r\nEF: {efStopwatch.Elapsed:'HH':'mm':'ss.fffffff'}\r\nDapper: {dpStopwatch.Elapsed:'HH':'mm':'ss.fffffff'}");
}
}
}
public static void DeleteActionTest()
{
var row = AdoRepository.ExecuteNonQueryBySqlString(SQL_REGION_DELETE);
}
public ModernDomainLayer(MainArg arg)
{
_arg = arg;
_repositiry = new AdoRepository(_arg.ConnectionString);
}
public void GetDbType_DateTime()
{
var type = typeof(DateTime);
Assert.Equal(DbType.DateTime, AdoRepository <TestEntity> .GetDbType(type));
}
public void GetDbType_String()
{
var type = typeof(string);
Assert.Equal(DbType.String, AdoRepository <TestEntity> .GetDbType(type));
}
public void GetDbType_Int()
{
var type = typeof(int);
Assert.Equal(DbType.Int32, AdoRepository <TestEntity> .GetDbType(type));
}
public void GetParameterName_ShouldPrependSymbolAt()
{
var propertyInfo = typeof(TestEntity).GetProperty("Id");
Assert.Equal("@Id", AdoRepository <TestEntity> .GetParameterName(propertyInfo));
}
public AdoController(AdoRepository repository)
{
this._repository = repository;
}
public static int TotalCount()
{
return(AdoRepository.ExecuteScalar(SQL_REGION_COUNT).AsInt(0));
}
