public List <T> ExecuteList <T>(IDbTransaction p_transaction, string p_sqlWhere)
where T : new()
{
TableInformation t_tableInformation = this.GetTableInformations(typeof(T));
SqlCommandHelper t_sqlHelper = new SqlCommandHelper(m_providerConfiguration, t_tableInformation);
string t_sql = t_sqlHelper.CreateSelectByWhere(p_sqlWhere);
IDbCommand cmd = p_transaction.Connection.CreateCommand();
cmd.Transaction = p_transaction;
cmd.CommandText = t_sql;
IDataReader t_reader = cmd.ExecuteReader();
try
{
List <T> t_results = new List <T>();
while (t_reader.Read())
{
T t_genericObject = new T();
this.PopulateEntity(t_reader, t_tableInformation, t_genericObject);
t_results.Add(t_genericObject);
}
return(t_results);
}
finally
{
t_reader.Close();
}
}
public void ExecuteUpdateCommand(IDbTransaction p_transaction, object p_object)
{
TableInformation t_tableInformation = this.GetTableInformations(p_object.GetType());
SqlCommandHelper t_sqlHelper = new SqlCommandHelper(m_providerConfiguration, t_tableInformation);
string t_sql = t_sqlHelper.CreateUpdate(p_object);
IDbCommand t_cmd = p_transaction.Connection.CreateCommand();
foreach (FieldInformation t_currFieldinformation in t_tableInformation.Fields)
{
if (t_currFieldinformation.IsPrimaryKey)
{
continue;
}
IDbDataParameter t_param = t_cmd.CreateParameter();
t_param.ParameterName = t_sqlHelper.CreateParameterName(t_currFieldinformation.Name);
t_param.Value = p_object.GetType().GetProperty(t_currFieldinformation.PropertyName).GetValue(p_object, null);
t_cmd.Parameters.Add(t_param);
}
IDbDataParameter t_paramPK = t_cmd.CreateParameter();
t_paramPK.ParameterName = t_sqlHelper.CreateParameterName(t_tableInformation.PrimaryKey.Name);
t_paramPK.Value = p_object.GetType().GetProperty(t_tableInformation.PrimaryKey.PropertyName).GetValue(p_object, null);
t_cmd.Parameters.Add(t_paramPK);
t_cmd.CommandText = t_sql.ToString();
t_cmd.Transaction = p_transaction;
t_cmd.ExecuteNonQuery();
}
public T FindBySQL <T>(IDbTransaction p_transaction, string p_sqlWhere)
where T : new()
{
TableInformation t_tableInformation = this.GetTableInformations(typeof(T));
SqlCommandHelper t_sqlHelper = new SqlCommandHelper(m_providerConfiguration, t_tableInformation);
string t_sql = t_sqlHelper.CreateSelectByWhere(p_sqlWhere);
IDbCommand cmd = p_transaction.Connection.CreateCommand();
cmd.Transaction = p_transaction;
cmd.CommandText = t_sql;
IDataReader t_reader = cmd.ExecuteReader();
T t_genericObject = default(T);
t_genericObject = new T();
if (t_reader.Read())
{
this.PopulateEntity(t_reader, t_tableInformation, t_genericObject);
}
t_reader.Close();
return(t_genericObject);
}
public int DeleteNotifications(string categories, int maxCount, DateTime toDate)
{
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
var query = string.Format(
@"DELETE FROM [dbo].[NotificationsHttp]
WHERE NotificationId IN (SELECT Id FROM [dbo].[Notifications]
WHERE EventId IN
(" + EventsSubQuery + @"))
DELETE FROM [dbo].[Notifications]
WHERE EventId IN
(" + EventsSubQuery + ")",
maxCount, categories);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@ActualDate";
parameter.Value = toDate;
command.Parameters.Add(parameter);
return(SqlCommandHelper.ExecuteNonQuery(command));
}
}
}
public void ExecuteFindByID(IDbTransaction p_transaction, object p_object)
{
TableInformation t_tableInformation = this.GetTableInformations(p_object.GetType());
SqlCommandHelper t_sqlHelper = new SqlCommandHelper(m_providerConfiguration, t_tableInformation);
string t_sql = t_sqlHelper.CreateSelectByPrimaryKey();
IDbCommand cmd = p_transaction.Connection.CreateCommand();
cmd.Transaction = p_transaction;
cmd.CommandText = t_sql;
IDbDataParameter t_param = cmd.CreateParameter();
t_param.ParameterName = t_sqlHelper.CreateParameterName(t_tableInformation.PrimaryKey.Name);
t_param.Value = p_object.GetType().GetProperty(t_tableInformation.PrimaryKey.PropertyName).GetValue(p_object, null);
cmd.Parameters.Add(t_param);
IDataReader t_reader = cmd.ExecuteReader();
if (t_reader.Read())
{
this.PopulateEntity(t_reader, t_tableInformation, p_object);
}
t_reader.Close();
}
public override async Task AddCustomerActivity(Guid customerId, Enums.Customers.CustomerActivityType customerActivityType, DateTime date, Guid userId)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@customerId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = customerId
},
new SqlParameter("@userId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = userId
},
new SqlParameter("@customerActivityType", System.Data.SqlDbType.Int)
{
Value = (int)customerActivityType
},
new SqlParameter("@date", System.Data.SqlDbType.SmallDateTime)
{
Value = date
}
};
await commandHelper.ExecuteNonQueryAsync(_addCustomerActivityCmdText, false, parameters);
}
public override async Task <Option <ICustomerComponentUow> > ExistingCustomerUowAsync(Guid customerId)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[1]
{
new SqlParameter("@customerId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = customerId
}
};
bool exists = Convert.ToBoolean(await commandHelper.ExecuteScalarAsync(_existsCustomerCmdTxt, false, parameters));
if (!exists)
{
return(Option <ICustomerComponentUow> .None());
}
return(Option <ICustomerComponentUow> .Some(new CustomerComponentUow(
new CustomerComponent(
customerId,
new SqlCustomersStorage(),
new SqlCustomerHistorization(),
new CstMexDateComponent(),
new SqlUserHistorizationComponent()
))));
}
public T FindByID <T>(IDbTransaction p_transaction, object p_objectPrimaryKey)
where T : new()
{
TableInformation t_tableInformation = this.GetTableInformations(typeof(T));
SqlCommandHelper t_sqlHelper = new SqlCommandHelper(m_providerConfiguration, t_tableInformation);
string t_sql = t_sqlHelper.CreateSelectByPrimaryKey();
IDbCommand cmd = p_transaction.Connection.CreateCommand();
cmd.Transaction = p_transaction;
cmd.CommandText = t_sql;
IDbDataParameter t_param = cmd.CreateParameter();
t_param.ParameterName = t_sqlHelper.CreateParameterName(t_tableInformation.PrimaryKey.Name);
t_param.Value = p_objectPrimaryKey;
cmd.Parameters.Add(t_param);
IDataReader t_reader = cmd.ExecuteReader();
T t_genericObject = default(T);
t_genericObject = new T();
if (t_reader.Read())
{
this.PopulateEntity(t_reader, t_tableInformation, t_genericObject);
}
t_reader.Close();
return(t_genericObject);
}
public override async Task AddBillingHistorizationAsync(Guid billId, BillActivityType billActivityType, DateTime serverDate, Guid userId)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@billId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = billId
},
new SqlParameter("@billActivityType", System.Data.SqlDbType.Int)
{
Value = (int)billActivityType
},
new SqlParameter("@date", System.Data.SqlDbType.SmallDateTime)
{
Value = serverDate
},
new SqlParameter("@userId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = userId
}
};
await commandHelper.ExecuteNonQueryAsync(_addBillingHistorizationCmdTxt, false, parameters);
}
public int UpdateMetricsHistory(string categories, int maxCount, DateTime toDate)
{
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
var query = string.Format(
@"UPDATE [dbo].[MetricHistory] SET StatusEventId = NULL
WHERE StatusEventId IN
(" + EventsSubQuery + ")",
maxCount, categories);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@ActualDate";
parameter.Value = toDate;
command.Parameters.Add(parameter);
return(SqlCommandHelper.ExecuteNonQuery(command));
}
}
}
public override async Task AddOrderHistorizationAsync(Guid orderId, OrderStatus orderStatus, DateTime serverDate, Guid userId)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@orderId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = orderId
},
new SqlParameter("@orderStatus", System.Data.SqlDbType.Int)
{
Value = (int)orderStatus
},
new SqlParameter("@date", System.Data.SqlDbType.SmallDateTime)
{
Value = serverDate
},
new SqlParameter("@userId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = userId
}
};
await commandHelper.ExecuteNonQueryAsync(_addOrderHistorizationCmdTxt, false, parameters);
}
public async Task <Option <IProductComponent> > CurrentAsync(Guid id)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[1]
{
new SqlParameter("@productId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = id
}
};
bool exists = Convert.ToBoolean(await commandHelper.ExecuteNonQueryAsync(_existsProductCmdTxt, false, parameters));
if (!exists)
{
return(Option <IProductComponent> .None());
}
return(Option <IProductComponent> .Some(
new ProductComponentDecorator(
new ProductComponent(id, new SqlProductStorage())
)
));
}
public override async Task <bool> TryAddPhoneCallAsync(Guid customerId, string description, Guid userId, DateTime date)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@customerId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = customerId
},
new SqlParameter("@description", System.Data.SqlDbType.VarChar)
{
Value = description
},
new SqlParameter("@userId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = userId
},
new SqlParameter("@date", System.Data.SqlDbType.SmallDateTime)
{
Value = date
},
};
return(await commandHelper.ExecuteNonQueryAsync(_addPhoneCallCmdTxt, false, parameters) > 0);
}
public override async Task <bool> CreateOrderHeaderAsync(Guid orderId, Guid customerId, Guid excursionId, DateTime serverDate)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@orderId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = orderId
},
new SqlParameter("@customerId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = customerId
},
new SqlParameter("@excursionId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = excursionId
},
new SqlParameter("@serverDate", System.Data.SqlDbType.SmallDateTime)
{
Value = serverDate
}
};
return(await commandHelper.ExecuteNonQueryAsync(_createOrderHeaderCmdTxt, false, parameters) > 0);
}
public override async Task AddPaymentAsync(Guid orderId, Guid billId, decimal amount, DateTime serverDate)
{
SqlCommandHelper commandHelper = new SqlCommandHelper(_connectionString);
SqlParameter[] parameters = new SqlParameter[4]
{
new SqlParameter("@orderId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = orderId
},
new SqlParameter("@billId", System.Data.SqlDbType.UniqueIdentifier)
{
Value = billId
},
new SqlParameter("@amount", System.Data.SqlDbType.Money)
{
Value = amount
},
new SqlParameter("@date", System.Data.SqlDbType.SmallDateTime)
{
Value = serverDate
}
};
await commandHelper.ExecuteNonQueryAsync(_addPaymentCmdTxt, false, parameters);
}
protected override SqlCommand CreateInsertSqlCommand(FlashObservation value)
{
var sqlCommand = new SqlCommand(FlashObservationQueries.CreateInsertQuery());
SqlCommandHelper.FillFlashObservation(sqlCommand, value);
return(sqlCommand);
}
protected override SqlCommand GetDeleteCommand(int id)
{
var sqlCommand = new SqlCommand(FlashObservationQueries.CreateDeleteQuery());
SqlCommandHelper.FillParam(sqlCommand, "@Id", id);
return(sqlCommand);
}
public double GetAverageEnergy(Coordinates rectTopLeft, Coordinates rectBottomRight)
{
return(this.ExecuteScalarStoredProcedure <double>("dbo.GetAverageEnergy_RectTopLeft_RectBottomRight",
command =>
{
SqlCommandHelper.FillParam(command, "@RectTopLeft", rectTopLeft);
SqlCommandHelper.FillParam(command, "@RectBottomRight", rectBottomRight);
}));
}
public double GetAverageEnergy(DateTime startFrom, DateTime endBy)
{
return(this.ExecuteScalarStoredProcedure <double>("dbo.GetAverageEnergy_StartFrom_EndBy",
command =>
{
SqlCommandHelper.FillParam(command, "@StartFrom", startFrom);
SqlCommandHelper.FillParam(command, "@EndBy", endBy);
}));
}
public void CreateDeleteExpiredSessionsCmd_Should_Create_SqlCommand_Without_Parameters()
{
var helper = new SqlCommandHelper(SqlCommandTimeout);
var cmd = helper.CreateDeleteExpiredSessionsCmd(SqlStatement);
VerifyBasicsOfSqlCommand(cmd);
Assert.Empty(cmd.Parameters);
}
public void CreateResetItemTimeoutCmd_Should_Create_SqlCommand_With_Right_Parameters()
{
var helper = new SqlCommandHelper(SqlCommandTimeout);
var cmd = helper.CreateResetItemTimeoutCmd(SqlStatement, SessionId);
VerifyBasicsOfSqlCommand(cmd);
VerifySessionIdParameter(cmd);
Assert.Equal(1, cmd.Parameters.Count);
}
public void CreateReleaseItemExclusiveCmd_Should_Create_SqlCommand_With_Right_Parameters(object lockId)
{
var helper = new SqlCommandHelper(SqlCommandTimeout);
var cmd = helper.CreateReleaseItemExclusiveCmd(SqlStatement, SessionId, lockId);
VerifyBasicsOfSqlCommand(cmd);
VerifySessionIdParameter(cmd);
VerifyLockCookieParameter(cmd, lockId);
Assert.Equal(2, cmd.Parameters.Count);
}
public bool Delete <E>(E e) where E : class, new()
{
string commandText = SqlCommandHelper.GetInstance().DeleteSql(e);
using (SqlConnection connection = new SqlConnection(connectionString_)) {
using (SqlCommand command = new SqlCommand()) {
command.CommandText = commandText;
return(ExecuteNonQuery(command, connection) == 1);
}
}
}
private void ExecuteReaderStoredProcedureAsync()
{
var storedProcedureName = this.Request.QueryString["storedProcedureName"];
if (string.IsNullOrEmpty(storedProcedureName))
{
throw new ArgumentException("storedProcedureName query string parameter is not defined.");
}
SqlCommandHelper.ExecuteReaderAsync(ConnectionString, storedProcedureName, CommandType.StoredProcedure);
}
/// <summary>
/// 分次更新多筆資料
/// </summary>
public virtual int Update(IEnumerable <T> models, IEnumerable <string> columns, IEnumerable <string> whereColumns)
{
int count = 0;
foreach (T model in models)
{
var cmd = SqlCommandHelper.GenerateUpdate(model, columns, whereColumns);
count += SqlHelper.ExecuteNonQuery(cmd);
}
return(count);
}
public bool Update <E>(E e) where E : class, new()
{
string commandText = SqlCommandHelper.GetInstance().UpdateSql(e);
using (SqlConnection connection = new SqlConnection(connectionString_)) {
using (SqlCommand sqlCommand = new SqlCommand()) {
sqlCommand.CommandText = commandText;
SqlParameter[] sqlParameters = GetParameters(e);
return(ExecuteNonQuery(sqlCommand, connection) == 1);
}
}
}
public void CreateTempInsertUninitializedItemCmd_Should_Create_SqlCommand_With_Right_Parameters()
{
var helper = new SqlCommandHelper(SqlCommandTimeout);
var cmd = helper.CreateTempInsertUninitializedItemCmd(SqlStatement, SessionId, BufferLength, Buffer, SessionTimeout);
VerifyBasicsOfSqlCommand(cmd);
VerifySessionIdParameter(cmd);
VerifySessionItemLongParameter(cmd);
VerifyTimeoutParameter(cmd);
Assert.Equal(3, cmd.Parameters.Count);
}
/// <summary>
/// 新增一筆資料並更新 Identity
/// </summary>
public virtual bool Insert(T model, IEnumerable <string> columns)
{
var cmd = SqlCommandHelper.GenerateInsert(model, columns);
object identity = SqlHelper.ExecuteScalar(cmd);
if (identity != null && identity != DBNull.Value)
{
var prop = CSharpHelper.GetIdentityProperty <T>();
prop.SetValue(model, identity);
}
return(true);
}
public int DeleteEventStatuses(string categories, int maxCount, DateTime toDate)
{
var count = 0;
using (var connection = Context.CreateConnection())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
var query = string.Format(
@"DELETE FROM [dbo].[EventStatuses]
WHERE
EventId IN
(" + EventsSubQuery + @")",
maxCount, categories);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@ActualDate";
parameter.Value = toDate;
command.Parameters.Add(parameter);
count += SqlCommandHelper.ExecuteNonQuery(command);
}
using (var command = connection.CreateCommand())
{
command.CommandTimeout = 0;
var query = string.Format(
@"DELETE FROM [dbo].[EventStatuses]
WHERE
StatusId IN
(" + EventsSubQuery + ")",
maxCount, categories);
command.CommandText = query;
var parameter = command.CreateParameter();
parameter.ParameterName = "@ActualDate";
parameter.Value = toDate;
command.Parameters.Add(parameter);
count += SqlCommandHelper.ExecuteNonQuery(command);
}
}
return(count);
}
public void CreateUpdateStateItemLongCmd_Should_Create_SqlCommand_With_Right_Parameters()
{
var helper = new SqlCommandHelper(SqlCommandTimeout);
var cmd = helper.CreateUpdateStateItemLongCmd(SqlStatement, SessionId, Buffer, BufferLength, SessionTimeout, LockId);
VerifyBasicsOfSqlCommand(cmd);
VerifySessionIdParameter(cmd);
VerifySessionItemLongParameter(cmd);
VerifyTimeoutParameter(cmd);
VerifyLockCookieParameter(cmd, LockId);
Assert.Equal(4, cmd.Parameters.Count);
}