public Task <List <StockModel> > AddStock(int type, decimal price, int quantity, int occurence)
{
using (var command = DbSqlCommand.GetSqlCommand())
{
try
{
command.CommandText = SqlConstants.Stock.spAddStock;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@type", type);
command.Parameters.AddWithValue("@price", price);
command.Parameters.AddWithValue("@quantity", quantity);
command.Parameters.AddWithValue("@occurence", occurence);
command.Connection.Open();
var reader = command.ExecuteReader();
var stocks = new List <StockModel>();
while (reader.Read())
{
stocks.Add(new StockModel
{
ID = reader.GetInt32(0),
Quantity = reader.GetInt32(1),
Price = reader.GetDecimal(2),
Name = reader.GetString(3),
StockProperties = new StockPropertyModel
{
Id = reader.GetInt32(4),
Cost = reader.GetDecimal(5),
Tolerance = reader.GetDecimal(6),
StockType = new StockTypeModel
{
Type = reader.GetInt32(7),
Name = reader.GetString(8),
Occurence = reader.GetInt32(9),
}
}
});
}
return(Task.FromResult <List <StockModel> >(stocks));
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
finally
{
command.Connection.Close();
}
return(null);
}
}
public static void AddSqlParameters(DbSqlCommand sqlCommand, string parameterPrefix, IReadOnlyList <object> parameters)
{
if (parameters != null)
{
for (int i = 0; i < parameters.Count; i++)
{
AddSqlParameter(sqlCommand.Parameters, "@" + parameterPrefix + i, parameters[i]);
}
}
}
public void ExecuteOnEndOfSubmit(string sql, Action <int> onExecutedAction, params object[] values)
{
if (onEndOfSubmitCommands == null)
{
onEndOfSubmitCommands = new List <(DbSqlCommand, Action <int>)>();
}
var sqlCommand = new DbSqlCommand(sql);
sqlCommand.CommandTimeout = (int)Settings.CommandTimeout.TotalSeconds;
QueryHelpers.AddSqlParameters(sqlCommand, values);
onEndOfSubmitCommands.Add((sqlCommand, onExecutedAction));
}
private IReadOnlyList <object> Load(Type type, string sql, params object[] values)
{
List <object> entities = new List <object>();
using (var sqlConnection = GetConnection())
{
using (var sqlCommand = new DbSqlCommand(sql))
{
sqlCommand.Connection = sqlConnection.SqlConnection;
QueryHelpers.AddSqlParameters(sqlCommand, values);
sqlConnection.Open();
using (SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess))
{
var fillVisitor = new FillVisitor(
reader: reader,
db: this,
objectFillerFactory: new ObjectFillerFactory());
while (fillVisitor.Read())
{
object entity = QueryHelpers.Fill(type, entity: null, fillVisitor: fillVisitor);
entities.Add(entity);
}
}
}
}
var entitiesToReturn = new List <object>(entities.Count);
var configuration = GlobalDbConfiguration.GetConfigurationOrEmpty(type);
var entityFilter = configuration.EntityFilter;
var queryLogger = GlobalDbConfiguration.QueryLogger;
foreach (object entity in entities)
{
queryLogger.IncrementLoadedElementCount(increment: 1);
if (entityFilter.DoReturnEntity(Settings, entity))
{
entitiesToReturn.Add(entity);
}
}
OnEntitiesLoaded(entitiesToReturn
.Where(e => e is DbEntity)
.Select(e => e as DbEntity)
.ToList());
return(entitiesToReturn);
}
protected DbSqlCommand GetSqlCommand()
{
if (customSqlCommand != null)
{
return(customSqlCommand);
}
var sqlCommand = new DbSqlCommand
{
CommandTimeout = (int)Db.Settings.CommandTimeout.TotalSeconds,
CommandText = sqlString
};
return(sqlCommand);
}
public int Execute(string sql, TimeSpan timeout, params object[] values)
{
using (var sqlConnection = GetConnection())
{
using (var sqlCommand = new DbSqlCommand(sql))
{
sqlCommand.CommandTimeout = (int)timeout.TotalSeconds;
QueryHelpers.AddSqlParameters(sqlCommand, values);
sqlCommand.Connection = sqlConnection.SqlConnection;
sqlConnection.Open();
return(sqlCommand.ExecuteNonQuery());
}
}
}
public IList <StockPropertyModel> GetAllStockProperties()
{
using (var command = DbSqlCommand.GetSqlCommand())
{
try
{
command.CommandText = SqlConstants.StockProperty.spGetAllProperties;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Connection.Open();
var reader = command.ExecuteReader();
var stockProperties = new List <StockPropertyModel>();
while (reader.Read())
{
stockProperties.Add(new StockPropertyModel
{
Id = reader.GetInt32(3),
Cost = reader.GetDecimal(4),
Tolerance = reader.GetDecimal(5),
StockType = new StockTypeModel
{
Type = reader.GetInt32(7),
Name = reader.GetString(8),
Occurence = reader.GetInt32(7),
}
});
}
return(stockProperties);
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
finally
{
command.Connection.Close();
_logger.Debug("DbConnection");
}
return(null);
}
}
public Task <List <StockTypeModel> > GetAllStockTypes()
{
string spGetAllStocks = SqlConstants.Stock.spGetAllStocks;
using (var command = DbSqlCommand.GetSqlCommand())
{
try
{
command.CommandText = SqlConstants.StockType.spGetAllStockTypes;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Connection.Open();
var reader = command.ExecuteReader();
var stockType = new List <StockTypeModel>();
while (reader.Read())
{
stockType.Add(new StockTypeModel
{
Type = reader.GetInt32(0),
Name = reader.GetString(1),
Occurence = reader.GetInt32(2),
});
}
return(Task.FromResult <List <StockTypeModel> >(stockType));
}
catch (Exception ex)
{
_logger.Error(ex.Message);
}
finally
{
command.Connection.Close();
}
return(null);
}
}
internal QuerySql(BaseDb db, DbSqlCommand customSqlCommand)
{
this.customSqlCommand = customSqlCommand;
Db = db;
}
MultipleResultSetReader IDbInternal.LoadMultipleResults(DbSqlCommand sqlCommand)
{
var multipleResults = new MultipleResultSetReader(sqlCommand, this);
return(multipleResults);
}
public T SingleOrDefaultDb <T>(DbSqlCommand sqlCommand)
{
return(Load <T>(sqlCommand).SingleOrDefault());
}
public UpdateCommandWithAffectedEntities(DbSqlCommand dbSqlCommand, IReadOnlyList <DbEntity> updatedDbEntityInstances)
{
DbSqlCommand = dbSqlCommand;
UpdatedDbEntityInstances = updatedDbEntityInstances;
}
public static void AddSqlParameters(DbSqlCommand sqlCommand, IReadOnlyList <object> parameters)
{
AddSqlParameters(sqlCommand, "", parameters);
}
internal MultipleResultSetReader(DbSqlCommand sqlCommand, IDbInternal db)
{
this.sqlCommand = sqlCommand;
this.db = db;
}
protected static MultipleResultSetReader LoadMultipleResults(IDb db, DbSqlCommand sqlCommand)
{
return(((IDbInternal)db).LoadMultipleResults(sqlCommand));
}
public QuerySql <T, T, T> Load <T>(DbSqlCommand sqlCommand)
{
var querySql = new QuerySql <T, T, T>(this, sqlCommand);
return(querySql);
}
public void DatabaseCommandCresteCommandTesting()
{
var dbConnection = DbSqlCommand.GetSqlCommand();
Xunit.Assert.NotNull(dbConnection);
}