cartdb.cs

东软内部材料（四）asp等相关的教学案例

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;

namespace IBuyAdventure
{
   public class CartDB 
   {

      string m_ConnectionString;

      public CartDB( string dsn ) {
         m_ConnectionString = dsn;
      }

      public void AddShoppingCartItem(string customerName, string productCode) {

          DataSet previousItem = GetShoppingCartItem(customerName, productCode);
          
          if (previousItem.Tables[0].Rows.Count > 0) {
            UpdateShoppingCartItem((int) previousItem.Tables[0].Rows[0]["ShoppingCartID"], ((int)previousItem.Tables[0].Rows[0]["Quantity"]) + 1);
          }
          else {

            IBuyAdventure.ProductsDB products;

            products = new IBuyAdventure.ProductsDB(m_ConnectionString);

            DataSet productDetails = products.GetProduct(productCode);
          
            String description = (String) productDetails.Tables[0].Rows[0]["ProductDescription"];
            String productName = (String) productDetails.Tables[0].Rows[0]["ProductName"];
            double unitPrice = (double) productDetails.Tables[0].Rows[0]["UnitPrice"];
            String insertStatement = "INSERT INTO ShoppingCarts (ProductCode, ProductName, Description, UnitPrice, CustomerName, Quantity) values ('" + productCode + "', @productName, @description, " + unitPrice + ", '" + customerName + "' , 1)";

            SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
            SqlCommand myCommand = new SqlCommand(insertStatement, sqlConnection);

            myCommand.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar, 50));
            myCommand.Parameters["@ProductName"].Value = productName ;

            myCommand.Parameters.Add(new SqlParameter("@description", SqlDbType.VarChar, 255));
            myCommand.Parameters["@description"].Value = description;

            myCommand.Connection.Open();
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();
          }
      }

      public void UpdateShoppingCartItem(int shoppingCartID, int quantity) {
      
          String updateStatement = "Update ShoppingCarts Set Quantity=" + quantity + " where shoppingcartID=" + shoppingCartID;

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlCommand myCommand = new SqlCommand(updateStatement, sqlConnection);

          myCommand.Connection.Open();
          myCommand.ExecuteNonQuery();
          myCommand.Connection.Close();
      }

      public void MigrateShoppingCartItems(String currentShopperName, String migrateShopperName) {
      
          String updateStatement = "Update ShoppingCarts Set CustomerName='" + migrateShopperName + "' where CustomerName='" + currentShopperName +"'";

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlCommand myCommand = new SqlCommand(updateStatement, sqlConnection);

          myCommand.Connection.Open();
          myCommand.ExecuteNonQuery();
          myCommand.Connection.Close();
      }

      public void DeleteShoppingCartItem(int shoppingCartID) {
      
          String updateStatement = "Delete ShoppingCarts where shoppingcartID=" + shoppingCartID;

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlCommand myCommand = new SqlCommand(updateStatement, sqlConnection);

          myCommand.Connection.Open();
          myCommand.ExecuteNonQuery();
          myCommand.Connection.Close();
      }

      public void ResetShoppingCart(String currentShopperName) {
      
          String updateStatement = "Delete ShoppingCarts where customername='" + currentShopperName + "'";

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlCommand myCommand = new SqlCommand(updateStatement, sqlConnection);

          myCommand.Connection.Open();
          myCommand.ExecuteNonQuery();
          myCommand.Connection.Close();
      }
      public DataSet GetShoppingCartItem(string customerName, string productCode) {

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT * FROM ShoppingCarts WHERE CustomerName='"+customerName+"' AND ProductCode='" + productCode + "'", sqlConnection);

          DataSet products = new DataSet();
          sqlAdapter1.Fill(products, "shoppingcart");

          return products;
      }

      public DataSet GetShoppingCartItems(string customerName) {

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT * FROM ShoppingCarts WHERE CustomerName='"+customerName+"'", sqlConnection);

          DataSet products = new DataSet();
          sqlAdapter1.Fill(products, "shoppingcart");

          return products;
      }
      

      public double GetOrderValueForCart( string customerName ) {

          SqlConnection sqlConnection = new SqlConnection(m_ConnectionString);
          SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("select sum(unitprice*quantity) as totalvalue from shoppingcarts", sqlConnection);

          DataSet products = new DataSet();
          sqlAdapter1.Fill(products, "shoppingcart");

          return (double) products.Tables[0].Rows[0]["totalvalue"];
      }

         
   }

}