public static bool RecordExists(string connStringName, string tableName, Dictionary <string, object> values) { var sb = new StringBuilder(); SqlStatement sql = new SqlStatement(connStringName); sql.Add("SELECT " + values.First().Key); sql.Add(" FROM " + tableName); object result = null; int indexer = 1; foreach (string s in values.Keys) { if (indexer == 1) { sql.Where(s, values[s]); } else { sql.And(s, values[s]); } indexer++; } using (var cmd = sql.BuildCommand()) { result = cmd.ExecuteScalar(); } return(result != null); }
public static SqlStatement Insert(Dictionary<string,object> settings) { var sql=new SqlStatement("TailspinConnectionString"); StringBuilder sb=new StringBuilder(); sb.Append("INSERT INTO [dbo].[Addresses]("); int indexer=1; foreach(string key in settings.Keys){ sb.Append(key); if(indexer<settings.Count) sb.Append(","); sql.AddInsertValue(key,settings[key]); indexer++; } sb.AppendLine(")"); sql.Add(sb.ToString()); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[CustomerEvents]"); return sql; }
public static SqlStatement Update(Dictionary<string,object> settings) { var sql=new SqlStatement("TailspinConnectionString"); sql.Add(string.Format("UPDATE {0} SET ","[dbo].[CustomerBehaviors]")); foreach(string key in settings.Keys){ sql.AddSetting(key,settings[key]); } return sql; }
public static SqlStatement Select(params string[] columns) { //for jon galloway var sql=new SqlStatement("TailspinConnectionString"); StringBuilder sb=new StringBuilder(); sb.Append("SELECT "); int indexer=1; foreach(string s in columns){ string qualifiedName=s; if(QualifiedColumns.ContainsKey(s)) qualifiedName=QualifiedColumns[s]; sb.AppendFormat("{0}",qualifiedName); if(indexer<columns.Length) sb.Append(","); indexer++; } sb.AppendFormat("\r\nFROM {0} ",TABLE_NAME); sb.AppendLine(); sql.Add(sb.ToString()); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[Categories_Products]"); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[ShippingMethods]"); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[__RefactorLog]"); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[InventoryRecords]"); return sql; }
public static SqlStatement Delete() { var sql=new SqlStatement("TailspinConnectionString"); sql.Add("DELETE FROM [dbo].[ProductOptionDisplays]"); return sql; }
public static bool RecordExists( string connStringName,string tableName, Dictionary<string, object> values) { var sb = new StringBuilder(); SqlStatement sql = new SqlStatement(connStringName); sql.Add("SELECT " + values.First().Key); sql.Add(" FROM " + tableName); object result = null; int indexer = 1; foreach (string s in values.Keys) { if (indexer == 1) { sql.Where(s, values[s]); } else { sql.And(s, values[s]); } indexer++; } using (var cmd = sql.BuildCommand()) { result = cmd.ExecuteScalar(); } return result != null; }
public Customer GetCustomer(string userName) { var productRepo = new SimpleProductRepository(); Customer result = null; var batch = new BatchSql(); //see if there's a customer - if not create one object user = CustomersTable.Select(CustomersTable.Columns.UserName) .Where(CustomersTable.Columns.UserName, userName) .BuildCommand() .ExecuteScalar(); if (user == null) { CustomersTable.Insert( new Dictionary <string, object>() { { CustomersTable.Columns.UserName, userName }, { CustomersTable.Columns.LanguageCode, System.Globalization.CultureInfo.CurrentCulture.TwoLetterISOLanguageName } }) .BuildCommand() .ExecuteNonQuery(); } //customer batch.Append(CustomersTable.Select() .Where(CustomersTable.Columns.UserName, userName)); //addresses batch.Append(AddressesTable.Select() .Where(AddressesTable.Columns.UserName, userName)); //shopping cart Guid orderID = GetCartID(userName); batch.Append(OrdersTable.Select() .Where(OrdersTable.Columns.OrderID, orderID)); //items //avert your eyes if this bothers you var itemSql = new SqlStatement(connectionStringName); itemSql.Add(string.Format("SELECT {0}, {1} ", OrderItemsTable.COLUMN_LIST, ProductsTable.COLUMN_LIST)); itemSql.InnerJoin( OrderItemsTable.TABLE_NAME, OrderItemsTable.ColumnsQualified.SKU, ProductsTable.TABLE_NAME, ProductsTable.ColumnsQualified.SKU) .Where(OrderItemsTable.Columns.OrderID, orderID); batch.Append(itemSql); var cmd = batch.BuildCommand(connectionStringName); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //customer if (rdr.Read()) { result = new Customer(userName, CustomersTable.ReadEmail(rdr), CustomersTable.ReadFirst(rdr), CustomersTable.ReadLast(rdr)); } else { result = new Customer(); } //address result.AddressBook = new List <Address>(); if (rdr.NextResult()) { while (rdr.Read()) { result.AddressBook.Add(LoadAddress(rdr)); } } //cart result.Cart = new ShoppingCart(userName); if (rdr.NextResult()) { if (rdr.Read()) { result.Cart.ShippingAddress = result.AddressBook.SingleOrDefault(x => x.AddressID == OrdersTable.ReadShippingAddressID(rdr)); result.Cart.BillingAddress = result.AddressBook.SingleOrDefault(x => x.AddressID == OrdersTable.ReadBillingAddressID(rdr)); result.Cart.ShippingService = OrdersTable.ReadShippingService(rdr) ?? ""; result.Cart.ShippingAmount = OrdersTable.ReadShippingAmount(rdr); result.Cart.TaxAmount = OrdersTable.ReadTaxAmount(rdr); } } //items if (rdr.NextResult()) { while (rdr.Read()) { var product = productRepo.LoadProduct(rdr); result.Cart.Items.Add(new ShoppingCartItem(product, CartItemsTable.ReadQuantity(rdr), CartItemsTable.ReadDateAdded(rdr))); } } } return(result); }
public Customer GetCustomer(string userName) { var productRepo = new SimpleProductRepository(); Customer result = null; var batch = new BatchSql(); //see if there's a customer - if not create one object user = CustomersTable.Select(CustomersTable.Columns.UserName) .Where(CustomersTable.Columns.UserName, userName) .BuildCommand() .ExecuteScalar(); if (user == null) { CustomersTable.Insert( new Dictionary<string, object>() { {CustomersTable.Columns.UserName,userName}, {CustomersTable.Columns.LanguageCode,System.Globalization.CultureInfo.CurrentCulture.TwoLetterISOLanguageName} }) .BuildCommand() .ExecuteNonQuery(); } //customer batch.Append(CustomersTable.Select() .Where(CustomersTable.Columns.UserName, userName)); //addresses batch.Append(AddressesTable.Select() .Where(AddressesTable.Columns.UserName, userName)); //shopping cart Guid orderID = GetCartID(userName); batch.Append(OrdersTable.Select() .Where(OrdersTable.Columns.OrderID, orderID)); //items //avert your eyes if this bothers you var itemSql = new SqlStatement(connectionStringName); itemSql.Add(string.Format("SELECT {0}, {1} ", OrderItemsTable.COLUMN_LIST, ProductsTable.COLUMN_LIST)); itemSql.InnerJoin( OrderItemsTable.TABLE_NAME, OrderItemsTable.ColumnsQualified.SKU, ProductsTable.TABLE_NAME, ProductsTable.ColumnsQualified.SKU) .Where(OrderItemsTable.Columns.OrderID, orderID); batch.Append(itemSql); var cmd = batch.BuildCommand(connectionStringName); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //customer if (rdr.Read()) { result = new Customer(userName, CustomersTable.ReadEmail(rdr), CustomersTable.ReadFirst(rdr), CustomersTable.ReadLast(rdr)); } else { result = new Customer(); } //address result.AddressBook = new List<Address>(); if (rdr.NextResult()) { while (rdr.Read()) { result.AddressBook.Add(LoadAddress(rdr)); } } //cart result.Cart = new ShoppingCart(userName); if (rdr.NextResult()) { if (rdr.Read()) { result.Cart.ShippingAddress = result.AddressBook.SingleOrDefault(x => x.AddressID == OrdersTable.ReadShippingAddressID(rdr)); result.Cart.BillingAddress = result.AddressBook.SingleOrDefault(x => x.AddressID == OrdersTable.ReadBillingAddressID(rdr)); result.Cart.ShippingService = OrdersTable.ReadShippingService(rdr) ?? ""; result.Cart.ShippingAmount = OrdersTable.ReadShippingAmount(rdr); result.Cart.TaxAmount = OrdersTable.ReadTaxAmount(rdr); } } //items if (rdr.NextResult()) { while (rdr.Read()) { var product = productRepo.LoadProduct(rdr); result.Cart.Items.Add(new ShoppingCartItem(product, CartItemsTable.ReadQuantity(rdr), CartItemsTable.ReadDateAdded(rdr))); } } } return result; }
public Order GetOrder(Guid orderID) { SimpleProductRepository productRepository = new SimpleProductRepository(); SimpleCustomerRepository customerRepository = new SimpleCustomerRepository(); Order result = null; var batch = new BatchSql(); batch.Append(OrdersTable.Select().Where("OrderID", orderID)); //items //products for the items var sql = new SqlStatement(connectionStringName); sql.Add("SELECT "); sql.Add(ProductsTable.COLUMN_LIST); sql.Add(OrderItemsTable.COLUMN_LIST); sql.Add("FROM Products INNER JOIN OrderItems ON Products.SKU = OrderItems.SKU"); sql.Add("WHERE SKU IN (SELECT SKU FROM OrderItems WHERE OrderID=@OrderID)"); //transactions batch.Append(TransactionsTable.Select().Where("orderid", orderID)); int shippingAddressID = 0; int billingAddressID = 0; int shippingMethodID = 0; //pull it var cmd = sql.BuildCommand(); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (rdr.Read()) { result = new Order(OrdersTable.ReadOrderStatusID(rdr)) { DateCreated = OrdersTable.ReadCreatedOn(rdr), DateShipped = OrdersTable.ReadDateShipped(rdr), UserName = OrdersTable.ReadUserName(rdr), DiscountAmount = OrdersTable.ReadDiscountAmount(rdr), DiscountReason = OrdersTable.ReadDiscountReason(rdr), EstimatedDelivery = OrdersTable.ReadEstimatedDelivery(rdr), ID = orderID, OrderNumber = OrdersTable.ReadOrderNumber(rdr), ShippingAmount = OrdersTable.ReadShippingAmount(rdr), ShippingService = OrdersTable.ReadShippingService(rdr), TaxAmount = OrdersTable.ReadTaxAmount(rdr), }; billingAddressID = OrdersTable.ReadBillingAddressID(rdr); shippingAddressID = OrdersTable.ReadShippingAddressID(rdr); } //load the items result.Items = new List <OrderLine>(); if (rdr.NextResult()) { while (rdr.Read()) { var product = productRepository.LoadProduct(rdr); var item = new OrderLine(OrderItemsTable.ReadDateAdded(rdr), OrderItemsTable.ReadQuantity(rdr), product); result.Items.Add(item); } } //transactions result.Transactions = new List <Transaction>(); if (rdr.NextResult()) { while (rdr.Read()) { Transaction t = new Transaction( TransactionsTable.ReadTransactionID(rdr), orderID, TransactionsTable.ReadAmount(rdr), TransactionsTable.ReadTransactionDate(rdr), TransactionsTable.ReadAuthorizationCode(rdr), TransactionsTable.ReadNotes(rdr), TransactionsTable.ReadProcessor(rdr)); result.Transactions.Add(t); } } } sql = new SqlStatement(connectionStringName); //addresses batch.Append(AddressesTable.Select().Where("addressid", shippingAddressID)); batch.Append(AddressesTable.Select().Where("addressid", billingAddressID)); //shipping method batch.Append(ShippingMethodsTable.Select().Where("shippingmethodid", shippingMethodID)); cmd = batch.BuildCommand(connectionStringName); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //shipping address if (rdr.Read()) { //shipping result.ShippingAddress = customerRepository.LoadAddress(rdr); } //billing address if (rdr.NextResult()) { if (rdr.Read()) { result.BillingAddress = customerRepository.LoadAddress(rdr); } } //shipping method if (rdr.NextResult()) { if (rdr.Read()) { LoadShipping(rdr); } } } return(result); }
public Order GetOrder(Guid orderID) { SimpleProductRepository productRepository = new SimpleProductRepository(); SimpleCustomerRepository customerRepository = new SimpleCustomerRepository(); Order result = null; var batch = new BatchSql(); batch.Append(OrdersTable.Select().Where("OrderID", orderID)); //items //products for the items var sql = new SqlStatement(connectionStringName); sql.Add("SELECT "); sql.Add(ProductsTable.COLUMN_LIST); sql.Add(OrderItemsTable.COLUMN_LIST); sql.Add("FROM Products INNER JOIN OrderItems ON Products.SKU = OrderItems.SKU"); sql.Add("WHERE SKU IN (SELECT SKU FROM OrderItems WHERE OrderID=@OrderID)"); //transactions batch.Append(TransactionsTable.Select().Where("orderid", orderID)); int shippingAddressID = 0; int billingAddressID = 0; int shippingMethodID=0; //pull it var cmd = sql.BuildCommand(); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (rdr.Read()) { result = new Order(OrdersTable.ReadOrderStatusID(rdr)) { DateCreated =OrdersTable.ReadCreatedOn(rdr), DateShipped = OrdersTable.ReadDateShipped(rdr), UserName = OrdersTable.ReadUserName(rdr), DiscountAmount = OrdersTable.ReadDiscountAmount(rdr), DiscountReason =OrdersTable.ReadDiscountReason(rdr), EstimatedDelivery = OrdersTable.ReadEstimatedDelivery(rdr), ID = orderID, OrderNumber = OrdersTable.ReadOrderNumber(rdr), ShippingAmount = OrdersTable.ReadShippingAmount(rdr), ShippingService=OrdersTable.ReadShippingService(rdr), TaxAmount = OrdersTable.ReadTaxAmount(rdr), }; billingAddressID = OrdersTable.ReadBillingAddressID(rdr); shippingAddressID = OrdersTable.ReadShippingAddressID(rdr); } //load the items result.Items = new List<OrderLine>(); if (rdr.NextResult()) { while (rdr.Read()) { var product = productRepository.LoadProduct(rdr); var item = new OrderLine(OrderItemsTable.ReadDateAdded(rdr),OrderItemsTable.ReadQuantity(rdr),product); result.Items.Add(item); } } //transactions result.Transactions = new List<Transaction>(); if (rdr.NextResult()) { while (rdr.Read()) { Transaction t = new Transaction( TransactionsTable.ReadTransactionID(rdr), orderID, TransactionsTable.ReadAmount(rdr), TransactionsTable.ReadTransactionDate(rdr), TransactionsTable.ReadAuthorizationCode(rdr), TransactionsTable.ReadNotes(rdr), TransactionsTable.ReadProcessor(rdr)); result.Transactions.Add(t); } } } sql = new SqlStatement(connectionStringName); //addresses batch.Append(AddressesTable.Select().Where("addressid", shippingAddressID)); batch.Append(AddressesTable.Select().Where("addressid", billingAddressID)); //shipping method batch.Append(ShippingMethodsTable.Select().Where("shippingmethodid", shippingMethodID)); cmd = batch.BuildCommand(connectionStringName); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //shipping address if (rdr.Read()) { //shipping result.ShippingAddress = customerRepository.LoadAddress(rdr); } //billing address if (rdr.NextResult()) { if (rdr.Read()) { result.BillingAddress = customerRepository.LoadAddress(rdr); } } //shipping method if (rdr.NextResult()) { if (rdr.Read()) { LoadShipping(rdr); } } } return result; }