cartdb.vb

This is a book about vb.you could learn this from this book

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Collections

Namespace IBuyAdventure

   Public Class CartDB 

      Dim m_ConnectionString As String

      Public Sub New(dsn As String)
         m_ConnectionString = dsn
      End Sub

      Public Sub AddShoppingCartItem(customerName As String, productCode As String)

          Dim previousItem As DataSet = GetShoppingCartItem(customerName, productCode)
         
          If (previousItem.Tables(0).Rows.Count > 0)
            UpdateShoppingCartItem(previousItem.Tables(0).Rows(0)("ShoppingCartID"), previousItem.Tables(0).Rows(0)("Quantity") + 1)
          Else

            Dim products As IBuyAdventure.ProductsDB

            products = New IBuyAdventure.ProductsDB(m_ConnectionString)

            Dim productDetails As DataSet = products.GetProduct(productCode)

            Dim description As String = productDetails.Tables(0).Rows(0)("ProductDescription")
            Dim productName As String = productDetails.Tables(0).Rows(0)("ProductName")
            Dim unitPrice As Double = productDetails.Tables(0).Rows(0)("UnitPrice")
            Dim insertStatement As String = "INSERT INTO ShoppingCarts (ProductCode, ProductName, Description, UnitPrice, CustomerName, Quantity)" _
            		& "values ('" & productCode & "', @productName, @description, " _
            		& unitPrice & ", '" & customerName & "' , 1)"

            Dim sqlConnection As New SqlConnection(m_ConnectionString)
            Dim myCommand As 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()
          End If
          
      End Sub

      Public Sub UpdateShoppingCartItem(shoppingCartID As Integer, quantity As Integer)
      
          Dim updateStatement As String = "Update ShoppingCarts Set Quantity=" & quantity _
          	& " where shoppingcartID=" & shoppingCartID

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim myCommand As New SqlCommand(updateStatement, sqlConnection)

          myCommand.Connection.Open()
          myCommand.ExecuteNonQuery()
          myCommand.Connection.Close()
      
      End Sub

      Public Sub MigrateShoppingCartItems(currentShopperName As String, migrateShopperName As String)
      
          Dim updateStatement As String = "Update ShoppingCarts Set CustomerName='" & migrateShopperName _
          	& "' where CustomerName='" & currentShopperName & "'"

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim myCommand As New SqlCommand(updateStatement, sqlConnection)

          myCommand.Connection.Open()
          myCommand.ExecuteNonQuery()
          myCommand.Connection.Close()
      
      End Sub

      Public Sub DeleteShoppingCartItem(shoppingCartID As Integer)
      
          Dim updateStatement As String = "Delete ShoppingCarts where shoppingcartID=" & shoppingCartID

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim myCommand As New SqlCommand(updateStatement, sqlConnection)

          myCommand.Connection.Open()
          myCommand.ExecuteNonQuery()
          myCommand.Connection.Close()
      
      End Sub

      Public Sub ResetShoppingCart(currentShopperName As String)
      
          Dim updateStatement As String = "Delete ShoppingCarts where customername='" & currentShopperName & "'"

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim myCommand As New SqlCommand(updateStatement, sqlConnection)

          myCommand.Connection.Open()
          myCommand.ExecuteNonQuery()
          myCommand.Connection.Close()
      
      End Sub
      
      Public Function GetShoppingCartItem(customerName As String, productCode As String) As DataSet

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim sqlAdapter1 As New SqlDataAdapter("SELECT * FROM ShoppingCarts WHERE CustomerName='" _
          	& customerName & "' AND ProductCode='" & productCode & "'", sqlConnection)

          Dim products As New DataSet()
          sqlAdapter1.Fill(products, "shoppingcart")

          Return products
      
      End Function

      Public Function GetShoppingCartItems(customerName As String) As DataSet

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim sqlAdapter1 As New SqlDataAdapter("SELECT * FROM ShoppingCarts WHERE CustomerName='" _
          	& customerName & "'", sqlConnection)

          Dim products As New DataSet()
          sqlAdapter1.Fill(products, "shoppingcart")

          Return products
      
      End Function
      

      Public Function GetOrderValueForCart(customerName As String) As Double

          Dim sqlConnection As New SqlConnection(m_ConnectionString)
          Dim sqlAdapter1 As New SqlDataAdapter("select sum(unitprice*quantity) as totalvalue from shoppingcarts", sqlConnection)

          Dim products As New DataSet()
          sqlAdapter1.Fill(products, "shoppingcart")


          return products.Tables(0).Rows(0)("totalvalue")
      
      End Function

   End Class

End Namespace