public int DeleteBulkUpdate(Guid eventId)
{
var exludeUpdateCol = new List <string> {
"SysId", "ExecuteStartDate", "EventType"
};
var cmdList = new List <SqlCommand>();
var bulkupdateEvent = new BulkUpdateEvent {
SysId = eventId, Status = (int)BulkUpdateStatus.已刪除
};
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", bulkupdateEvent, exludeUpdateCol, new List <string> {
"SysId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductDateUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductEventUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductPreOrderUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductSortUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
cmdList.Add(SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].ProductStatusUpdate", bulkupdateEvent, exludeUpdateCol, new List <string> {
"EventId=@SysId"
}));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList));
}
private bool AddLog(APIHelper helper)
{
PrescoAPILog prescoAPILog = MapAPILog(helper);
var cmd = SqlExtension.GetInsertSqlCmd("PrescoAPILog", prescoAPILog);
return(SqlDbmanager.ExecuteNonQry(cmd));
}
public Order Save(Order order)
{
using (SqlConnection connection = SqlExtension.GetConnection())
{
try
{
connection.Open();
var sqlString = "INSERT INTO Orders VALUES (@CustomerId, @Amount, @VAT)";
var command = sqlString.GetCommand(connection);
command.Parameters.AddWithValue("@CustomerId", order.CustomerId);
command.Parameters.AddWithValue("@Amount", order.Amount);
command.Parameters.AddWithValue("@VAT", order.VAT);
order.OrderId = command.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(order);
}
public static ResponceModel Add(string username, string content, string pic)
{
if (content != "")
{
//SOLVED BUG ??
byte[] data = null;
if (pic != null)
{
data = ToolUtil.HexToByte(pic);
}
MsgUni msg = new MsgUni(username, content, data)
{
time = DateTime.Now.ToString(),
updatetime = DateTime.Now.ToString(),
};
msg.Add();
msg = SqlExtension.GetLastRecord <MsgUni>();
if (data != null)
{
msg.SavePic();
}
return(new ResponceModel(200, "添加成功", msg));
}
else
{
return(new ResponceModel(403, "留言内容为空"));
}
}
public static Customer Save(Customer customer)
{
using (SqlConnection connection = SqlExtension.GetConnection())
{
try
{
connection.Open();
var sqlString = "INSERT INTO Customer OUTPUT INSERTED.CustomerId VALUES (@Name, @Country, @DateOfBirth)";
var command = sqlString.GetCommand(connection);
command.Parameters.AddWithValue("@Name", customer.Name);
command.Parameters.AddWithValue("@Country", customer.Country);
command.Parameters.AddWithValue("@DateOfBirth", customer.DateOfBirth);
customer.CustomerId = (int)command.ExecuteScalar();
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(customer);
}
public static Customer Load(int customerId)
{
var customer = new Customer();
using (SqlConnection connection = SqlExtension.GetConnection())
{
try
{
connection.Open();
var sqlString = $"SELECT * FROM Customer WHERE CustomerId = {customerId}";
SqlDataReader reader = sqlString.GetReader(connection);
while (reader.Read())
{
customer.Name = reader["Name"].ToString();
customer.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
customer.Country = reader["Country"].ToString();
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(customer);
}
public ExtensionBuilder(
string configuration,
[NotNull] MappingSchema mapping,
[NotNull] SqlExtension extension,
[NotNull] ConvertHelper convertHeper,
[NotNull] MemberInfo member,
[NotNull] Expression[] arguments)
{
if (mapping == null)
{
throw new ArgumentNullException("mapping");
}
if (extension == null)
{
throw new ArgumentNullException("extension");
}
if (convertHeper == null)
{
throw new ArgumentNullException("convertHeper");
}
if (arguments == null)
{
throw new ArgumentNullException("arguments");
}
Mapping = mapping;
Configuration = configuration;
Extension = extension;
_convert = convertHeper;
Member = member;
Method = member as MethodInfo;
Arguments = arguments;
}
public SqlExtensionParam(string?name, SqlExtension extension)
{
Name = name;
Extension = extension;
#if DEBUG
_paramNumber = Interlocked.Add(ref _paramCounter, 1);
#endif
}
private SqlCommand InsertShipmentHistory(ShipmentHistory shiphistory)
{
var cmd = SqlExtension.GetInsertSqlCmd("ShipmentSTHistory", shiphistory);
//SSH24.SSH05,SSH03
cmd.CommandText = @"IF NOT EXISTS(SELECT SSH03 FROM ShipmentSTHistory WHERE SSH24= @SSH24 AND SSH05=@SSH05 AND SSH03=@SSH03)
BEGIN " + SqlExtension.GetInsertStr("ShipmentSTHistory", shiphistory) + " END ";
return(cmd);
}
public List <SqlCommand> AddBulkUpdate(List <UpdateDetails> updateDetails)
{
var cmdList = new List <SqlCommand>();
foreach (ProductStatusUpdate pEvent in updateDetails)
{
cmdList.Add(SqlExtension.getInsertSqlCmd("[ScheduleEvent].[dbo].ProductStatusUpdate", pEvent));
}
return(cmdList);
}
public int AddBulkUpdate(BulkUpdateData productBulkUpdateData)
{
_bulkUpdateDetailsService = _updateDetailsFactory.CreateService(productBulkUpdateData.UpdateEvent.EventType);
var cmdList = new List <SqlCommand>();
cmdList.Add(SqlExtension.getInsertSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", productBulkUpdateData.UpdateEvent));
cmdList.AddRange(_bulkUpdateDetailsService.AddBulkUpdate(productBulkUpdateData.UpdateDetails));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList));
}
public void ToWhereSql_EqualsStringValue_SqlIsCorrect()
{
var dbCommandMock = new Mock <IDbCommand> {
DefaultValue = DefaultValue.Mock
};
var whereSql = SqlExtension.ToWhereSql((Expression <Func <CustomExpression, bool> >)(model => model.MyStringAttribute == "Value"), dbCommandMock.Object);
Assert.AreEqual("mystringattribute = @mystringattribute", whereSql);
}
public List <SqlCommand> AddBulkUpdate(List <UpdateDetails> updateDetails)
{
var cmdList = new List <SqlCommand>();
foreach (var pEvent in updateDetails)
{
var addevent = (ProductDateUpdate)pEvent;
cmdList.Add(SqlExtension.getInsertSqlCmd("[ScheduleEvent].[dbo].ProductDateUpdate", addevent));
}
return(cmdList);
}
private int AddShipNumber(List <ShipmentNumber> shipmentNumbers)
{
var cmdList = new List <SqlCommand>();
foreach (var number in shipmentNumbers)
{
cmdList.Add(SqlExtension.GetInsertSqlCmd("PrescoShipment", number));
}
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
return(rval);
}
public void ToWhereSql_EqualsNestedPropertyValue_SqlIsCorrect()
{
var dbCommandMock = new Mock <IDbCommand> {
DefaultValue = DefaultValue.Mock
};
var filter = new CascadedFilter {
CustomExpressionFilter = new CustomExpression {
MyStringAttribute = "Value"
}
};
var whereSql = SqlExtension.ToWhereSql((Expression <Func <CustomExpression, bool> >)(model => model.MyStringAttribute == filter.CustomExpressionFilter.MyStringAttribute), dbCommandMock.Object);
Assert.AreEqual("mystringattribute = @mystringattribute", whereSql);
}
private int UpdateShipNumber(List <ShipmentNumber> shipmentNumbers)
{
var cmdlist = new List <SqlCommand>();
foreach (var shipnumber in shipmentNumbers)
{
shipnumber.Status = (int)ShipNumberStatus.Used;
shipnumber.UDate = DateTime.Now;
cmdlist.Add(SqlExtension.GetUpdateSqlCmd("PrescoShipment", shipnumber, new List <string> {
"Id"
}, "Id=@Id"));
}
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdlist);
return(rval);
}
public void ToWhereSql_EqualsStringValue_HasParameter()
{
var parameters = new Fakes.DataParameterCollection();
var dbCommandMock = new Mock <IDbCommand> {
DefaultValue = DefaultValue.Mock
};
dbCommandMock.SetupGet(command => command.Parameters).Returns(parameters);
dbCommandMock.Setup(command => command.CreateParameter()).Returns(() => Mock.Of <IDbDataParameter>());
var dbCommand = dbCommandMock.Object;
SqlExtension.ToWhereSql((Expression <Func <CustomExpression, bool> >)(model => model.MyStringAttribute == "Value"), dbCommand);
Assert.IsTrue(dbCommand.Parameters.Contains("@mystringattribute"));
Assert.AreEqual("Value", (dbCommand.Parameters["@mystringattribute"] as IDbDataParameter).Value);
Assert.AreEqual(DbType.String, (dbCommand.Parameters["@mystringattribute"] as IDbDataParameter).DbType);
}
public void ToWhereSql_Contains_SqlIsCorrect()
{
var parameters = new Fakes.DataParameterCollection();
var dbCommandMock = new Mock <IDbCommand> {
DefaultValue = DefaultValue.Mock
};
dbCommandMock.SetupGet(command => command.Parameters).Returns(parameters);
dbCommandMock.Setup(command => command.CreateParameter()).Returns(() => Mock.Of <IDbDataParameter>());
var dbCommand = dbCommandMock.Object;
var whereExpression =
(Expression <Func <CustomExpression, bool> >)(model => model.MyStringAttribute.Contains("Test"));
var whereSql = SqlExtension.ToWhereSql(whereExpression, dbCommand);
Assert.AreEqual("mystringattribute LIKE '%' + @mystringattribute + '%'", whereSql);
}
public int UpdateShipmentStatus(List <PrescoShipment> shipStatusRequests)
{
var updatelist = GetUpdateShipments(shipStatusRequests);
var updateCmd = new List <SqlCommand>();
foreach (var shiphistory in updatelist)
{
var shipment = new Shipment {
ST12 = shiphistory.SSH05, ST02 = shiphistory.SSH03
};
updateCmd.Add(SqlExtension.GetUpdateSqlCmd("Shipment", shipment, new List <string> {
"ST02", "ST03", "ST13"
}, "ST02=@ST02", null));
updateCmd.Add(InsertShipmentHistory(shiphistory));
}
return(SqlDbmanager.ExecuteNonQryMutiSqlCmd(updateCmd));
}
public bool UpdateBulkUpdate(BulkUpdateData productBulkUpdate)
{
_bulkUpdateDetailsService = _updateDetailsFactory.CreateService(productBulkUpdate.UpdateEvent.EventType);
var excludeUpdateColumns = new List <string> {
"ProductID", "SysId", "ExecuteDate"
};
var whereColumns = new List <string> {
"SysId=@SysId"
};
var cmd = SqlExtension.getUpdateSqlCmd("[ScheduleEvent].[dbo].BulkUpdateEvent", productBulkUpdate.UpdateEvent, excludeUpdateColumns, whereColumns);
var cmdList = new List <SqlCommand>();
cmdList.Add(cmd);
cmdList.AddRange(_bulkUpdateDetailsService.EditBulkUpdate(productBulkUpdate.UpdateDetails));
return(SqlDbmanager.executeNonQryMutiSqlCmd(cmdList) == 1);
}
public ExtensionBuilder(
string configuration,
[NotNull] IDataContext dataContext,
[NotNull] SelectQuery query,
[NotNull] SqlExtension extension,
[NotNull] ConvertHelper convertHeper,
[NotNull] MemberInfo member,
[NotNull] Expression[] arguments)
{
DataContext = dataContext ?? throw new ArgumentNullException(nameof(dataContext));
Query = query ?? throw new ArgumentNullException(nameof(query));
Configuration = configuration;
Extension = extension ?? throw new ArgumentNullException(nameof(extension));
_convert = convertHeper ?? throw new ArgumentNullException(nameof(convertHeper));
Member = member;
Method = member as MethodInfo;
Arguments = arguments ?? throw new ArgumentNullException(nameof(arguments));
}
public static List <Customer> LoadAll()
{
IOrderRepository orderRepository = new OrderRepository();
List <Order> orders = orderRepository.LoadAll();
var customers = new List <Customer>();
using (SqlConnection connection = SqlExtension.GetConnection())
{
try
{
connection.Open();
var sqlString = "SELECT * FROM Customer";
SqlDataReader reader = sqlString.GetReader(connection);
while (reader.Read())
{
var customer = new Customer
{
CustomerId = (int)reader["CustomerId"],
Name = reader["Name"].ToString(),
DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString()),
Country = reader["Country"].ToString(),
Orders = orders.Where(e => e.CustomerId == (int)reader["CustomerId"]).ToList()
};
customers.Add(customer);
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(customers);
}
private int AddOrderLog(List <OrderRequest> request, APIHelper aPIHelper)
{
var cmdList = new List <SqlCommand>();
var prescoAPILog = MapAPILog(aPIHelper);
foreach (var item in request)
{
var orderlog = new PrescoOrderLog();
orderlog.SysId = Guid.NewGuid();
orderlog.PrescoAPILogID = prescoAPILog.SysId;
orderlog.PrescoShipID = item.ShipNo;
orderlog.GMShipID = item.OrderNo;
cmdList.Add(SqlExtension.GetInsertSqlCmd("Prescoorderlog", orderlog));
}
cmdList.Add(SqlExtension.GetInsertSqlCmd("PrescoAPILog", prescoAPILog));
var rval = SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
return(rval);
}
public ExtensionBuilder(
string?configuration,
object?builderValue,
IDataContext dataContext,
SelectQuery query,
SqlExtension extension,
ConvertHelper convertHeper,
MemberInfo member,
Expression[] arguments)
{
Configuration = configuration;
BuilderValue = builderValue;
DataContext = dataContext ?? throw new ArgumentNullException(nameof(dataContext));
Query = query ?? throw new ArgumentNullException(nameof(query));
Extension = extension ?? throw new ArgumentNullException(nameof(extension));
_convert = convertHeper ?? throw new ArgumentNullException(nameof(convertHeper));
Member = member;
Method = member as MethodInfo;
Arguments = arguments ?? throw new ArgumentNullException(nameof(arguments));
}
private void AddMolPayTransaction(List <TransactionAPIResponse> transactionAPIResponses, MolPayMember member)
{
var cmdList = new List <SqlCommand>();
foreach (var response in transactionAPIResponses)
{
response.SysId = Guid.NewGuid();
if (response.SettlementDate.Contains("0000-00-00"))
{
response.SettlementDate = null;
}
if (response.PaidDate.Contains("0000-00-00"))
{
response.PaidDate = null;
}
response.MemberName = member.MemberName;
response.MemberId = member.MemberId;
cmdList.Add(SqlExtension.GetInsertSqlCmd("MolPayTransaction", response));
}
SqlDbmanager.ExecuteNonQryMutiSqlCmd(cmdList);
}
public List <Order> LoadAll()
{
var orders = new List <Order>();
using (SqlConnection connection = SqlExtension.GetConnection())
{
try
{
connection.Open();
var sqlString = "SELECT * FROM Orders";
SqlDataReader reader = sqlString.GetReader(connection);
while (reader.Read())
{
var order = new Order
{
OrderId = (int)reader["OrderId"],
CustomerId = (int)reader["CustomerId"],
Amount = (double)reader["Amount"],
VAT = (double)reader["VAT"]
};
orders.Add(order);
}
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
}
return(orders);
}
public static ResponceModel Update(string time)
{
if (DateTime.TryParse(time, out DateTime result))
{
var data = from item in SqlExtension.GetLastRecords <MsgUni>(200) where (item.GetUpdateTime() > result) select item;
// Mark: 因传输图片过于消耗流量，故特意将图片的获取分离开来。
// 提取图片。
foreach (var item in data)
{
item.ReadPic();
}
return(new ResponceModel(200, "获取留言成功", new
{
time = DateTime.Now.ToString(),
content = data.ToArray()
}));
}
else
{
return(new ResponceModel(403, "时间格式不正确"));
}
}
public static SqlExpression BuildSqlExpression(SqlExtension root, Type systemType, int precedence, bool isAggregate, bool canBeNull)
{
var sb = new StringBuilder();
var resolvedParams = new Dictionary <SqlExtensionParam, string>();
var resolving = new HashSet <SqlExtensionParam>();
var newParams = new List <ISqlExpression>();
Func <string, string, string> valueProvider = null;
Stack <SqlExtension> current = new Stack <SqlExtension>();
valueProvider = (name, delimiter) =>
{
var found = root.GetParametersByName(name);
if (current.Count != 0)
{
found = current.Peek().GetParametersByName(name).Concat(found);
}
string result = null;
foreach (var p in found)
{
if (resolvedParams.TryGetValue(p, out var paramValue))
{
result = paramValue;
}
else
{
if (resolving.Contains(p))
{
throw new InvalidOperationException("Circular reference");
}
resolving.Add(p);
var ext = p.Extension;
if (ext != null)
{
current.Push(ext);
paramValue = ResolveExpressionValues(ext.Expr, valueProvider);
current.Pop();
}
else
{
sb.Length = 0;
if (p.Expression != null)
{
paramValue = string.Format("{{{0}}}", newParams.Count);
newParams.Add(p.Expression);
}
}
resolvedParams.Add(p, paramValue);
if (string.IsNullOrEmpty(paramValue))
{
continue;
}
if (!string.IsNullOrEmpty(result))
{
result = result + delimiter;
}
result = result + paramValue;
}
if (delimiter == null && !string.IsNullOrEmpty(result))
{
break;
}
}
return(result);
};
var expr = ResolveExpressionValues(root.Expr, valueProvider);
var sqlExpression = new SqlExpression(systemType, expr, precedence, isAggregate, newParams.ToArray())
{
CanBeNull = canBeNull
};
return(sqlExpression);
}
SqlExtensionParam BuildExtensionParam(IDataContext dataContext, SelectQuery query, MemberInfo member, Expression[] arguments, ConvertHelper convertHelper)
{
var method = member as MethodInfo;
var type = member.GetMemberType();
if (method != null)
{
type = method.ReturnType ?? type;
}
else if (member is PropertyInfo)
{
type = ((PropertyInfo)member).PropertyType;
}
var extension = new SqlExtension(type, Expression, Precedence, ChainPrecedence, IsAggregate, CanBeNull);
SqlExtensionParam result = null;
if (method != null)
{
var parameters = method.GetParameters();
for (var i = 0; i < parameters.Length; i++)
{
var arg = arguments[i];
var param = parameters[i];
var names = param.GetCustomAttributes(true).OfType <ExprParameterAttribute>()
.Select(a => a.Name ?? param.Name)
.Distinct()
.ToArray();
if (names.Length > 0)
{
var sqlExpressions = arg is NewArrayExpression arrayInit
? arrayInit.Expressions.Select(convertHelper.Convert).ToArray()
: new[] { convertHelper.Convert(arg) };
foreach (var name in names)
{
foreach (var sqlExpr in sqlExpressions)
{
extension.AddParameter(name, sqlExpr);
}
}
}
}
}
if (BuilderType != null)
{
var callBuilder = _builders.GetOrAdd(BuilderType, t =>
{
if (Activator.CreateInstance(BuilderType) is IExtensionCallBuilder res)
{
return(res);
}
throw new ArgumentException(
$"Type '{BuilderType}' does not implement {nameof(IExtensionCallBuilder)} interface.");
}
);
var builder = new ExtensionBuilder(Configuration, dataContext, query, extension, convertHelper, member, arguments);
callBuilder.Build(builder);
result = builder.ResultExpression != null ? new SqlExtensionParam(TokenName, builder.ResultExpression) : new SqlExtensionParam(TokenName, builder.Extension);
}
result = result ?? new SqlExtensionParam(TokenName, extension);
return(result);
}
public SqlExtensionParam(string name, SqlExtension extension)
{
Name = name;
Extension = extension;
}
