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)); }