public static void StoreOrder(OrderData orderData)
{
if (string.IsNullOrWhiteSpace(orderData.OrderXML))
{
throw new Exception("Saving order without XML");
}
// id, storeOrderReferenceID and orderNumber are either generated or delicate to manage
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
if (sqlHelper.ConnectionString.Contains("|DataDirectory|") || DataLayerHelper.IsEmbeddedDatabase(GlobalSettings.DbDSN) || GlobalSettings.DbDSN.ToLower().Contains("mysql"))
{
// SQLCE might get a performance hit (extra query)
var orderExists = orderData.DatabaseId > 0 || GetOrderInfo(orderData.UniqueId) != null;
sqlHelper.ExecuteNonQuery(orderExists ? @"UPDATE uWebshopOrders set orderInfo = @orderInfo, orderStatus = @orderStatus, updateDate = @updateDate,
storeAlias = @storeAlias, customerID = @customerID, customerUsername = @customerUsername,
customerEmail = @customerEmail, customerFirstName = @customerFirstName, orderNumber = @orderNumber,
customerLastName = @customerLastName, transactionID = @transactionID where uniqueID = @uniqueID" : @"insert into uWebshopOrders(uniqueID, orderInfo, orderStatus, createDate, updateDate, storeAlias, customerID, customerUsername, customerEmail, customerFirstName, customerLastName, transactionID, orderNumber, storeOrderReferenceID)
values(@uniqueID, @orderInfo, @orderStatus, @createDate, @updateDate, @storeAlias, @customerID, @customerUsername, @customerEmail, @customerFirstName, @customerLastName, @transactionID, @orderNumber, @storeOrderReferenceID)" , sqlHelper.CreateParameter("@orderInfo", orderData.OrderXML), sqlHelper.CreateParameter("@uniqueID", orderData.UniqueId), orderData.StoreOrderReferenceId.HasValue ? sqlHelper.CreateParameter("@storeOrderReferenceID", orderData.StoreOrderReferenceId.GetValueOrDefault()) : sqlHelper.CreateParameter("@storeOrderReferenceID", DBNull.Value), string.IsNullOrWhiteSpace(orderData.OrderReferenceNumber) ? sqlHelper.CreateParameter("@orderNumber", DBNull.Value) : sqlHelper.CreateParameter("@orderNumber", orderData.OrderReferenceNumber), string.IsNullOrWhiteSpace(orderData.OrderStatus) ? sqlHelper.CreateParameter("@orderStatus", DBNull.Value) : sqlHelper.CreateParameter("@orderStatus", orderData.OrderStatus), sqlHelper.CreateParameter("@createDate", DateTime.Now), sqlHelper.CreateParameter("@updateDate", DateTime.Now), string.IsNullOrWhiteSpace(orderData.StoreAlias) ? sqlHelper.CreateParameter("@storeAlias", DBNull.Value) : sqlHelper.CreateParameter("@storeAlias", orderData.StoreAlias), orderData.CustomerId == null ? sqlHelper.CreateParameter("@customerID", DBNull.Value) : sqlHelper.CreateParameter("@customerID", orderData.CustomerId), string.IsNullOrWhiteSpace(orderData.CustomerUsername) ? sqlHelper.CreateParameter("@customerUsername", DBNull.Value) : sqlHelper.CreateParameter("@customerUsername", orderData.CustomerUsername), string.IsNullOrWhiteSpace(orderData.CustomerEmail) ? sqlHelper.CreateParameter("@customerEmail", DBNull.Value) : sqlHelper.CreateParameter("@customerEmail", orderData.CustomerEmail), string.IsNullOrWhiteSpace(orderData.CustomerFirstName) ? sqlHelper.CreateParameter("@customerFirstName", DBNull.Value) : sqlHelper.CreateParameter("@customerFirstName", orderData.CustomerFirstName), string.IsNullOrWhiteSpace(orderData.CustomerLastName) ? sqlHelper.CreateParameter("@customerLastName", DBNull.Value) : sqlHelper.CreateParameter("@customerLastName", orderData.CustomerLastName), string.IsNullOrWhiteSpace(orderData.TransactionId) ? sqlHelper.CreateParameter("@transactionID", DBNull.Value) : sqlHelper.CreateParameter("@transactionID", orderData.TransactionId));
if (!orderExists)
{
// another performance hit for sqlCE, select identity not possible within same command
var insertedId = sqlHelper.ExecuteScalar <int>("select id from uWebshopOrders where uniqueID = @uniqueID", sqlHelper.CreateParameter("@uniqueID", orderData.UniqueId));
if (orderData.DatabaseId == 0 && insertedId > 0)
{
orderData.SetGeneratedDatabaseId(insertedId);
}
}
}
else
{
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< combined INSERTorUPDATE orderInfo");
var insertedId = sqlHelper.ExecuteScalar <int>(@"IF (SELECT Count(ID) FROM uWebshopOrders WHERE uniqueID = @uniqueId)=0
BEGIN
insert into uWebshopOrders(uniqueID, orderInfo, orderStatus, createDate, updateDate, storeAlias, customerID, customerUsername, customerEmail, customerFirstName, customerLastName, transactionID, orderNumber, storeOrderReferenceID)
values(@uniqueID, @orderInfo, @orderStatus, @createDate, @updateDate, @storeAlias, @customerID, @customerUsername, @customerEmail, @customerFirstName, @customerLastName, @transactionID, @orderNumber, @storeOrderReferenceID)
select @@IDENTITY
END
ELSE
BEGIN
update uWebshopOrders set orderInfo = @orderInfo, orderStatus = @orderStatus, updateDate = @updateDate,
storeAlias = @storeAlias, customerID = @customerID, customerUsername = @customerUsername,
customerEmail = @customerEmail, customerFirstName = @customerFirstName, orderNumber = @orderNumber, storeOrderReferenceID = @storeOrderReferenceID,
customerLastName = @customerLastName, transactionID = @transactionID where uniqueID = @uniqueID
select -1
END" , sqlHelper.CreateParameter("@orderInfo", orderData.OrderXML), sqlHelper.CreateParameter("@uniqueID", orderData.UniqueId), orderData.StoreOrderReferenceId.HasValue ? sqlHelper.CreateParameter("@storeOrderReferenceID", orderData.StoreOrderReferenceId.GetValueOrDefault()) : sqlHelper.CreateParameter("@storeOrderReferenceID", DBNull.Value), string.IsNullOrWhiteSpace(orderData.OrderReferenceNumber) ? sqlHelper.CreateParameter("@orderNumber", DBNull.Value) : sqlHelper.CreateParameter("@orderNumber", orderData.OrderReferenceNumber), string.IsNullOrWhiteSpace(orderData.OrderStatus) ? sqlHelper.CreateParameter("@orderStatus", DBNull.Value) : sqlHelper.CreateParameter("@orderStatus", orderData.OrderStatus), sqlHelper.CreateParameter("@createDate", DateTime.Now), sqlHelper.CreateParameter("@updateDate", DateTime.Now), string.IsNullOrWhiteSpace(orderData.StoreAlias) ? sqlHelper.CreateParameter("@storeAlias", DBNull.Value) : sqlHelper.CreateParameter("@storeAlias", orderData.StoreAlias), orderData.CustomerId == null ? sqlHelper.CreateParameter("@customerID", DBNull.Value) : sqlHelper.CreateParameter("@customerID", orderData.CustomerId), string.IsNullOrWhiteSpace(orderData.CustomerUsername) ? sqlHelper.CreateParameter("@customerUsername", DBNull.Value) : sqlHelper.CreateParameter("@customerUsername", orderData.CustomerUsername), string.IsNullOrWhiteSpace(orderData.CustomerEmail) ? sqlHelper.CreateParameter("@customerEmail", DBNull.Value) : sqlHelper.CreateParameter("@customerEmail", orderData.CustomerEmail), string.IsNullOrWhiteSpace(orderData.CustomerFirstName) ? sqlHelper.CreateParameter("@customerFirstName", DBNull.Value) : sqlHelper.CreateParameter("@customerFirstName", orderData.CustomerFirstName), string.IsNullOrWhiteSpace(orderData.CustomerLastName) ? sqlHelper.CreateParameter("@customerLastName", DBNull.Value) : sqlHelper.CreateParameter("@customerLastName", orderData.CustomerLastName), string.IsNullOrWhiteSpace(orderData.TransactionId) ? sqlHelper.CreateParameter("@transactionID", DBNull.Value) : sqlHelper.CreateParameter("@transactionID", orderData.TransactionId));
if (orderData.DatabaseId == 0 && insertedId > 0)
{
orderData.SetGeneratedDatabaseId(insertedId);
}
}
}
public static void SetOrderInfo(Guid orderId, string serializedOrderInfoObject, string orderStatus)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
if (sqlHelper.ConnectionString.Contains("|DataDirectory|") || DataLayerHelper.IsEmbeddedDatabase(GlobalSettings.DbDSN) || GlobalSettings.DbDSN.ToLower().Contains("mysql"))
{
// SQLCE gets a performance hit (extra query, no way around it)
var orderExists = GetOrderInfo(orderId) != null;
//if (orderExists)
// Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< UPDATE orderInfo");
//else
// Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< INSERT orderInfo");
sqlHelper.ExecuteNonQuery(orderExists ? @"UPDATE uWebshopOrders set orderInfo = @orderInfo, orderStatus = @orderStatus, updateDate = @updateDate where uniqueID = @uniqueID" : @"INSERT into uWebshopOrders(uniqueID, orderInfo, orderStatus, createDate, updateDate) values(@uniqueID, @orderInfo, @orderStatus, @createDate, @updateDate)", sqlHelper.CreateParameter("@orderInfo", serializedOrderInfoObject), sqlHelper.CreateParameter("@uniqueID", orderId), sqlHelper.CreateParameter("@orderStatus", orderStatus), sqlHelper.CreateParameter("@createDate", DateTime.Now), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
else
{
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< combined INSERTorUPDATE orderInfo");
sqlHelper.ExecuteNonQuery("IF (SELECT Count(ID) FROM uWebshopOrders WHERE uniqueID = @uniqueId)=0 BEGIN insert into uWebshopOrders(uniqueID, orderInfo, orderStatus, createDate, updateDate) " + "values(@uniqueID, @orderInfo, @orderStatus, @createDate, @updateDate) END ELSE BEGIN update uWebshopOrders set orderInfo = @orderInfo, orderStatus = @orderStatus, updateDate = @updateDate where uniqueID = @uniqueID END", sqlHelper.CreateParameter("@orderInfo", serializedOrderInfoObject), sqlHelper.CreateParameter("@uniqueID", orderId), sqlHelper.CreateParameter("@orderStatus", orderStatus), sqlHelper.CreateParameter("@createDate", DateTime.Now), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
}
public void Save(int discountId, IEnumerable <ICoupon> coupons)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
sqlHelper.ExecuteNonQuery("delete from [uWebshopCoupons] WHERE DiscountId = @discountId", sqlHelper.CreateParameter("@discountId", discountId));
if (coupons.Any())
{
if (sqlHelper.ConnectionString.Contains("|DataDirectory|") || DataLayerHelper.IsEmbeddedDatabase(GlobalSettings.DbDSN) || GlobalSettings.DbDSN.ToLower().Contains("mysql"))
{
foreach (var coupon in coupons)
{
sqlHelper.ExecuteNonQuery(@"INSERT into uWebshopCoupons(DiscountId, CouponCode, NumberAvailable) values(@discountId, @couponcode, @numberavailable)", sqlHelper.CreateParameter("@discountId", coupon.DiscountId), sqlHelper.CreateParameter("@couponcode", coupon.CouponCode), sqlHelper.CreateParameter("@numberavailable", coupon.NumberAvailable));
}
}
else
{
sqlHelper.ExecuteNonQuery("insert into [uWebshopCoupons] (DiscountId, CouponCode, NumberAvailable) VALUES " + string.Join(", ", coupons.Select(c => "(" + c.DiscountId + ", '" + c.CouponCode + "', " + c.NumberAvailable + ")").ToArray()));
}
}
}
public bool UsesSQLCEDatabase()
{
return(DataLayerHelper.IsEmbeddedDatabase(GlobalSettings.DbDSN));
}