public void Run(string configFile, string connectionString)
{
Log.Debug("Running MySql install");
var config = File.ReadAllText(configFile);
config = config.Replace(".SqlServerDatabaseHelper", ".MySqlDatabaseHelper");
File.WriteAllText(configFile, config);
var sql = Helper.Instance.ReadAssemblyResource("Resources.Sql.MySql.Install.sql");
Log.Debug("Read SQL from resource stream");
Log.Debug(sql);
Log.Debug("Executing SQL");
var helper = DataLayerHelper.CreateSqlHelper(connectionString);
try
{
helper.ExecuteNonQuery(sql);
} catch (Exception e)
{
Log.Warn(e);
}
Log.Debug("SQL Executed.");
}
public static void SetOrderNumber(Guid uniqueOrderId, string orderNumber, string storeAlias, int storeOrderReferenceID)
{
//uWebshop.Domain.Log.Instance.LogDebug( "SetOrderNumber orderNumber: " + orderNumber + " storeOrderReferenceID: " + storeOrderReferenceID);
umbraco.BusinessLogic.Log.Add(LogTypes.Debug, 0, "SetOrderNumber orderNumber: " + orderNumber + " storeOrderReferenceID: " + storeOrderReferenceID);
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
sqlHelper.ExecuteNonQuery("update uWebshopOrders set orderNumber = @orderNumber, storeOrderReferenceID = @storeOrderReferenceID, storeAlias = @storeAlias, updateDate = @updateDate where uniqueID = @uniqueID", sqlHelper.CreateParameter("@uniqueID", uniqueOrderId), sqlHelper.CreateParameter("@storeOrderReferenceID", storeOrderReferenceID), sqlHelper.CreateParameter("@orderNumber", orderNumber), sqlHelper.CreateParameter("@storeAlias", storeAlias), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
/// <summary>
/// Clears an initialized database
/// </summary>
public static void ClearDatabase()
{
var dataHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN) as SqlCEHelper;
if (dataHelper == null)
{
throw new InvalidOperationException("The sql helper for unit tests must be of type SqlCEHelper, check the ensure the connection string used for this test is set to use SQLCE");
}
dataHelper.ClearDatabase();
}
public static void Delete(IEnumerable <Guid> orderGuids)
{
if (!orderGuids.Any())
{
return;
}
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
sqlHelper.ExecuteNonQuery("DELETE from uWebshopOrders where uniqueID in (" + string.Join(",", orderGuids.Select(g => g.ToString()).ToArray()) + ")");
}
private void ClearDatabase()
{
var dataHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN) as SqlCEHelper;
if (dataHelper == null)
{
throw new InvalidOperationException("The sql helper for unit tests must be of type SqlCEHelper, check the ensure the connection string used for this test is set to use SQLCE");
}
dataHelper.ClearDatabase();
AppDomain.CurrentDomain.SetData("DataDirectory", null);
}
public static void DropForeignKeys(string table)
{
var databaseSettings = ConfigurationManager.ConnectionStrings[Constants.System.UmbracoConnectionName];
var dataHelper = DataLayerHelper.CreateSqlHelper(databaseSettings.ConnectionString, false) as SqlCEHelper;
if (dataHelper == null)
{
throw new InvalidOperationException("The sql helper for unit tests must be of type SqlCEHelper, check the ensure the connection string used for this test is set to use SQLCE");
}
dataHelper.DropForeignKeys(table);
}
/// <summary>
/// Gets the SQL helper.
/// </summary>
/// <returns></returns>
public ISqlHelper GetSqlHelper()
{
if (!string.IsNullOrWhiteSpace(this.ConnectionStringName))
{
var connectionStringSettings = ConfigurationManager.ConnectionStrings[this.ConnectionStringName];
if (connectionStringSettings != null)
{
return(DataLayerHelper.CreateSqlHelper(connectionStringSettings.ConnectionString));
}
}
return(uQuery.SqlHelper);
}
private void ClearDatabase()
{
var databaseSettings = ConfigurationManager.ConnectionStrings[Core.Configuration.GlobalSettings.UmbracoConnectionName];
var dataHelper = DataLayerHelper.CreateSqlHelper(databaseSettings.ConnectionString, false) as SqlCEHelper;
if (dataHelper == null)
{
throw new InvalidOperationException("The sql helper for unit tests must be of type SqlCEHelper, check the ensure the connection string used for this test is set to use SQLCE");
}
dataHelper.ClearDatabase();
AppDomain.CurrentDomain.SetData("DataDirectory", null);
}
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 IEnumerable <ICoupon> GetAllWithCouponcode(string couponCode)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var coupons = new List <Coupon>();
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopCoupons WHERE CouponCode = @couponCode", sqlHelper.CreateParameter("@couponCode", couponCode)))
{
while (reader.Read())
{
coupons.Add(new Coupon(reader));
}
}
return(coupons);
}
public IEnumerable <ICoupon> GetAll()
{
var coupons = new List <Coupon>();
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopCoupons " + null))
{
while (reader.Read())
{
coupons.Add(new Coupon(reader));
}
}
return(coupons);
}
public static Guid GetCacheGuid()
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var reader = sqlHelper.ExecuteReader("SELECT cacheGuid FROM uWebshop");
while (reader.Read())
{
reader.GetString("cacheGuid");
//stocks.Add(new StockInfo { NodeId = reader.GetInt("NodeId"), Stock = reader.GetInt("Stock"), StoreAlias = reader.GetString("StoreAlias"), OrderCount = reader.GetInt("OrderCount") });
}
return(Guid.Empty);
}
/// <summary>
/// Initializes a new database
/// </summary>
public static void InitializeDatabase()
{
ConfigurationManager.AppSettings.Set("umbracoDbDSN", @"datalayer=SQLCE4Umbraco.SqlCEHelper,SQLCE4Umbraco;data source=|DataDirectory|\Umbraco.sdf");
ClearDatabase();
var dataHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var installer = dataHelper.Utility.CreateInstaller();
if (installer.CanConnect)
{
installer.Install();
}
}
public IEnumerable <ICoupon> GetAllForDiscount(int discountId)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var coupons = new List <Coupon>();
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopCoupons WHERE DiscountId = @discountId", sqlHelper.CreateParameter("@discountId", discountId)))
{
while (reader.Read())
{
coupons.Add(new Coupon(reader));
}
}
return(coupons);
}
public static void InstallOrderTable()
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var isMySql = sqlHelper.GetType().Name.Contains("mysql"); // untested! idee naar http://our.umbraco.org/forum/developers/api-questions/33111-Detecting-database-in-use-(MS-SQL-SQL-CE-MySQL)
try
{
sqlHelper.ExecuteNonQuery(@"CREATE TABLE
[uWebshopOrders](
[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[uniqueID] [uniqueidentifier] NULL,
[customerEmail] nvarchar (500) NULL,
[customerFirstName] nvarchar (500) NULL,
[customerLastName] nvarchar (500) NULL,
[orderNumber] nvarchar (100) NULL,
[storeOrderReferenceID] int NULL,
[orderInfo] [ntext] NULL,
[orderStatus] nvarchar (100) NULL,
[transactionID] nvarchar (100) NULL,
[storeAlias] nvarchar (500) NULL,
[customerID] int NULL,
[customerUsername] nvarchar (500) NULL,
[createDate] [datetime] NULL,
[updateDate] [datetime] NULL)" );
}
catch (Exception ex)
{
//Log.Instance.LogDebug("InstallOrderTable Catch: Already Exists?");
Log.Add(LogTypes.Debug, 0, "InstallOrderTable Catch: Already Exists?");
try
{
sqlHelper.ExecuteNonQuery(@"ALTER TABLE [uWebshopOrders]
ADD [customerUsername] nvarchar (500) NULL" );
}
catch
{
}
try
{
sqlHelper.ExecuteNonQuery(@"ALTER TABLE [uWebshopOrders]
ADD [storeOrderReferenceID] int NULL" );
}
catch
{
}
}
}
public Buddha(string connection, string root)
{
ConnectionString = connection;
WebRoot = root;
if (!Directory.Exists(WebRoot + sep + karmaDir))
{
Directory.CreateDirectory(WebRoot + sep + karmaDir);
}
_sqlhelper = DataLayerHelper.CreateSqlHelper(ConnectionString);
_config = new XmlDocument();
_config.Load(WebRoot + sep + "config" + sep + "uPowers.config");
}
private void InitializeDatabase()
{
ConfigurationManager.AppSettings.Set("umbracoDbDSN", @"datalayer=SQLCE4Umbraco.SqlCEHelper,SQLCE4Umbraco;data source=|DataDirectory|\Umbraco.sdf");
ClearDatabase();
AppDomain.CurrentDomain.SetData("DataDirectory", TestHelper.CurrentAssemblyDirectory);
var dataHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
var installer = dataHelper.Utility.CreateInstaller();
if (installer.CanConnect)
{
installer.Install();
}
}
/// <summary>
/// Initializes a new database
/// </summary>
public static void InitializeDatabase()
{
ConfigurationManager.AppSettings.Set(Core.Configuration.GlobalSettings.UmbracoConnectionName, @"datalayer=SQLCE4Umbraco.SqlCEHelper,SQLCE4Umbraco;data source=|DataDirectory|\UmbracoPetaPocoTests.sdf;Flush Interval=1;");
ClearDatabase();
var databaseSettings = ConfigurationManager.ConnectionStrings[Core.Configuration.GlobalSettings.UmbracoConnectionName];
var dataHelper = DataLayerHelper.CreateSqlHelper(databaseSettings.ConnectionString, false);
var installer = dataHelper.Utility.CreateInstaller();
if (installer.CanConnect)
{
installer.Install();
}
}
public void InstallCouponsTable()
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
try
{
sqlHelper.ExecuteNonQuery(@"CREATE TABLE
[uWebshopCoupons](
[DiscountId] [int] NOT NULL,
[CouponCode] nvarchar (500) NOT NULL,
[NumberAvailable] [int] NOT NULL)" );
}
catch (Exception ex)
{
Log.Add(LogTypes.Debug, 0, "InstallCouponsTable Catch: Already Exists?");
}
}
public static OrderData GetOrderInfo(string transactionId)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopOrders WHERE transactionID = @transactionID", sqlHelper.CreateParameter("@transactionID", transactionId)))
{
while (reader.Read())
{
var orderInfo = reader.GetString("orderInfo");
if (!string.IsNullOrEmpty(orderInfo))
{
return(new OrderData(reader));
}
}
return(null);
}
}
public ICoupon Get(int discountId, string couponCode)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopCoupons WHERE DiscountId = @discountId AND CouponCode = @couponCode", sqlHelper.CreateParameter("@discountId", discountId), sqlHelper.CreateParameter("@couponCode", couponCode)))
{
while (reader.Read())
{
var orderInfo = reader.GetString("orderInfo");
if (!string.IsNullOrEmpty(orderInfo))
{
return(new Coupon(reader));
}
}
return(null);
}
}
private void InitializeDatabase()
{
ConfigurationManager.AppSettings.Set(Core.Configuration.GlobalSettings.UmbracoConnectionName, @"datalayer=SQLCE4Umbraco.SqlCEHelper,SQLCE4Umbraco;data source=|DataDirectory|\UmbracoPetaPocoTests.sdf");
ClearDatabase();
AppDomain.CurrentDomain.SetData("DataDirectory", TestHelper.CurrentAssemblyDirectory);
var databaseSettings = ConfigurationManager.ConnectionStrings[Core.Configuration.GlobalSettings.UmbracoConnectionName];
var dataHelper = DataLayerHelper.CreateSqlHelper(databaseSettings.ConnectionString, false) as SqlCEHelper;
var installer = dataHelper.Utility.CreateInstaller();
if (installer.CanConnect)
{
installer.Install();
}
}
/// <summary>
/// Retrieve HTML from the database cache for the given node
/// </summary>
/// <param name="nodeId">Id of the node</param>
/// <param name="fullHtml">string to fill with HTML</param>
/// <returns>bool indicating success/failure</returns>
public static bool GetRecord(int nodeId, out string fullHtml)
{
fullHtml = "";
if (nodeId < 1)
{
return(false);
}
var success = false;
ISqlHelper sqlHelper = null;
IRecordsReader result = null;
try
{
const string sqlQuery = "SELECT fullHTML FROM fullTextCache WHERE nodeId = @nodeId";
sqlHelper = DataLayerHelper.CreateSqlHelper(global::Umbraco.Core.ApplicationContext.Current.DatabaseContext.ConnectionString);
result = sqlHelper.ExecuteReader(sqlQuery, sqlHelper.CreateParameter("@nodeId", nodeId));
if (result != null && result.HasRecords && result.Read() && result.ContainsField("fullHTML"))
{
fullHtml = result.GetString("fullHTML");
success = true;
}
}
catch (umbraco.UmbracoException ex)
{
LogHelper.Error(typeof(DbAccess), "Error In Database Query to fullTextCache", ex);
fullHtml = "";
}
finally
{
if (result != null)
{
result.Close();
}
if (sqlHelper != null)
{
sqlHelper.Dispose();
}
}
return(success);
}
public static string GetHighestOrderNumber(ref int referenceId)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
using (var reader = sqlHelper.ExecuteReader("SELECT orderNumber, storeOrderReferenceID FROM uWebshopOrders ORDER BY id DESC"))
{
while (reader.Read())
{
referenceId = reader.Get <int>("storeOrderReferenceID");
var orderNumber = reader.Get <string>("orderNumber");
if (!string.IsNullOrEmpty(orderNumber) && !orderNumber.StartsWith("[INCOMPLETE]"))
{
return(orderNumber);
}
}
}
return(null);
}
// public static void InstallOrderNumberTable()
// {
// var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
// sqlHelper.ExecuteNonQuery(
// @"CREATE TABLE
// [uWebshopOrderNumber](
// [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
// [StoreAlias] nvarchar (500) NULL,
// [Counter] int NULL)");
// }
public static void SetCustomerInfo(Guid orderId, XElement element)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
if (element.Name == "customerEmail")
{
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< UPDATE customerEmail");
sqlHelper.ExecuteNonQuery("update uWebshopOrders set customerEmail = @customerEmail, updateDate = @updateDate where uniqueID = @uniqueID", sqlHelper.CreateParameter("@uniqueID", orderId), sqlHelper.CreateParameter("@customerEmail", element.Value), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
if (element.Name == "customerFirstName")
{
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< UPDATE customerFirstName");
sqlHelper.ExecuteNonQuery("update uWebshopOrders set customerFirstName = @customerFirstName, updateDate = @updateDate where uniqueID = @uniqueID", sqlHelper.CreateParameter("@uniqueID", orderId), sqlHelper.CreateParameter("@customerFirstName", element.Value), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
if (element.Name == "customerLastName")
{
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< UPDATE customerLastName");
sqlHelper.ExecuteNonQuery("update uWebshopOrders set customerLastName = @customerLastName, updateDate = @updateDate where uniqueID = @uniqueID", sqlHelper.CreateParameter("@uniqueID", orderId), sqlHelper.CreateParameter("@customerLastName", element.Value), sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
}
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 static List <OrderData> GetAllOrderInfos(string where = null)
{
if (where == null)
{
where = string.Empty;
}
var orderInfos = new List <OrderData>();
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
//Log.Instance.LogDebug(DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff tt") + " >>>>SQL<<<< SELECT orderInfo");
using (var reader = sqlHelper.ExecuteReader("SELECT * FROM uWebshopOrders " + where))
{
while (reader.Read())
{
orderInfos.Add(new OrderData(reader));
}
return(orderInfos);
}
}
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 static int AssignNewOrderNumberToOrderSharedBasket(int databaseId, string alias, int orderNumberStartNumber)
{
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
if (databaseId <= 0)
{
throw new Exception("No valid database id");
}
return(sqlHelper.ExecuteScalar <int>(@"begin tran
declare @storeOrderReferenceID int
set @storeOrderReferenceID = coalesce((SELECT top 1 storeOrderReferenceID FROM uWebshopOrders ORDER BY storeOrderReferenceID DESC),0) + 1
set @storeOrderReferenceID = case when @orderNumberStartNumber > @storeOrderReferenceID then @orderNumberStartNumber else @storeOrderReferenceID end
update uWebshopOrders set storeOrderReferenceID = @storeOrderReferenceID, storeAlias = @storeAlias, updateDate = @updateDate where id = @id
select @storeOrderReferenceID
commit tran", sqlHelper.CreateParameter("@id", databaseId), sqlHelper.CreateParameter("@orderNumberStartNumber", orderNumberStartNumber), sqlHelper.CreateParameter("@storeAlias", alias), sqlHelper.CreateParameter("@updateDate", DateTime.Now)));
}
public static void SetCustomerInfo(Guid orderId, string customerEmail, string customerFirstName, string customerLastName)
{
if (string.IsNullOrEmpty(customerFirstName))
{
customerFirstName = string.Empty;
}
if (string.IsNullOrEmpty(customerLastName))
{
customerLastName = string.Empty;
}
var sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);
sqlHelper.ExecuteNonQuery("update uWebshopOrders set customerEmail = @customerEmail, " +
"customerFirstName = @customerFirstName, customerLastName = @customerLastName, " +
"updateDate = @updateDate where uniqueID = @uniqueID",
sqlHelper.CreateParameter("@uniqueID", orderId),
sqlHelper.CreateParameter("@customerEmail", customerEmail),
sqlHelper.CreateParameter("@customerFirstName", customerFirstName),
sqlHelper.CreateParameter("@customerLastName", customerLastName),
sqlHelper.CreateParameter("@updateDate", DateTime.Now));
}
