© 2004 Microsoft Corporation. All rights reserved.

Figure 4 LoadData
'//——————————————————————————————————
'// Private Method
'// Overloaded:    No
'// Parameters:    None
'// Return Value:  None
'// Purpose:       Retrieves and then loads the data into the form.
'//——————————————————————————————————
Private Sub LoadData()
    Dim oTree As BusinessServices.CustomerOrder = New _
        BusinessServices.CustomerOrder()
    grdData.DataBindings.Clear()
    m_oDS = oTree.GetData()
    grdData.DataSource = Nothing
    grdData.DataSource = m_oDS
    oTree = Nothing
End Sub
Figure 5 The SaveData Method
Private Sub SaveData()
    Dim lRetVal As Long
    Dim oTree As BusinessServices.CustomerOrder = New _
        BusinessServices.CustomerOrder()
    Dim oDS_Delta As DataSet
    Dim sMsg As String

    '//— Check for changes with the HasChanges method first.
    If m_oDS Is Nothing Then Exit Sub
    If Not m_oDS.HasChanges() Then Exit Sub

    '//— Grab all changed rows
    oDS_Delta = m_oDS.GetChanges()
    sMsg = "Are you sure you want to save these " & _
        oDS_Delta.Tables(0).Rows.Count() & " rows to the database?"
        lRetVal = MsgBox(sMsg, Microsoft.VisualBasic.MsgBoxStyle.Question + 
        Microsoft.VisualBasic.MsgBoxStyle.YesNo, "Save Records")
    Select Case lRetVal
        Case vbYes
            Try
                '//— Save all changes
                sMsg = oTree.SaveData(oDS_Delta)
                LoadData()
            Catch e As Exception
                sMsg = "Error saving data." & vbCrLf & vbCrLf & _
                       e.Message.ToString()
            Finally
                MsgBox(sMsg, _
                    Microsoft.VisualBasic.MsgBoxStyle.Information, _
                    "Save Records")
            End Try
        Case vbNo
            '//— Do nothing
    End Select
    oDS_Delta = Nothing
    oTree = Nothing
End Sub
Figure 6 GetData Method of the Base Class
//———————————————————————————————————
// Public Method
// Overloaded:     No
// Parameters:     None
// Return Value:   DataSet
// Purpose:        Retrieves all items.
//———————————————————————————————————
public virtual DataSet GetData()
{
    //— Create a new DataSet
    m_oDS = new DataSet();

    //— Fill the DataSet with the Customers
    m_oDA.Fill(m_oDS, m_sClassName);

    //— Return the DataSet
    return m_oDS;
}
Figure 7 SaveData Method of the Base Class
//———————————————————————————————————
// Public Method
// Overloaded:    Yes
// Parameters:    DataSet
// Return Value:  Status Message
// Purpose:       Saves all items that changed, and 
//                uses the SqlConnection passed in.
//———————————————————————————————————
public virtual string SaveData(DataSet oDS, SqlConnection oCn)
{
    string sMsg = "";
    //————————————————————————————
    //— Save the data
    //————————————————————————————
    try
    {
        if (oCn.State != ConnectionState.Open) 
        {
            oCn.Open();
        }
        //— Make all database changes
        m_oDA.Update(oDS, m_sClassName);
    }
    catch (Exception e)
    {
        //— An error occurred, so we roll the transaction back
        sMsg = "Records were not updated. " + e.Message.ToString();
    }
    finally
    {
        //— Do nothing
    }
    return sMsg;
}
Figure 8 The Customer Class and its Constructor
//———————————————————————————————————
// Public Class
// Purpose:   The Customer class 
// Inherits:  The Base_BusinessServices class 
//———————————————————————————————————
public class Customer : Base_BusinessServices 
{

    //———————————————————————————————————
    // Class Constructor (zero arguments)
    // Overloaded:    No
    //———————————————————————————————————
    public Customer()
    {
        m_sClassName = "Customer";
        string sSQL = "";
        SqlCommand oSelCmd;
        SqlCommand oInsCmd;
        SqlCommand oUpdCmd;
        SqlCommand oDelCmd;

        //————————————————————————————
        //— Set up the Connection
        //————————————————————————————
        InitializeConnection();

        //————————————————————————————
        //— Set up the SELECT Command
        //————————————————————————————
        sSQL = "SELECT CustomerID, CompanyName, ContactName, City, 
               Region " + " FROM    Customers " + " ORDER BY CompanyName ";
        oSelCmd = null;
        oSelCmd = new SqlCommand(sSQL, m_oCn);
        oSelCmd.CommandType = CommandType.Text;
        //————————————————————————————
        //— Set up the UPDATE Command
        //————————————————————————————
        sSQL = "UPDATE Customers " +
            " SET CompanyName = @CompanyName , " +
            " ContactName = @ContactName, City = @City, Region = 
            @Region " +
            " WHERE CustomerID = @CustomerID ";
        oUpdCmd = null;
        oUpdCmd = new SqlCommand(sSQL, m_oCn);
        oUpdCmd.CommandType = CommandType.Text;
        oUpdCmd.Parameters.Add(new SqlParameter("@CompanyName", 
                               SqlDbType.NVarChar, 40, "CompanyName"));
        oUpdCmd.Parameters.Add(new SqlParameter("@ContactName", 
                               SqlDbType.NVarChar, 30, "ContactName"));
        oUpdCmd.Parameters.Add(new SqlParameter("@City", 
                               SqlDbType.NVarChar, 15, "City"));
        oUpdCmd.Parameters.Add(new SqlParameter("@Region", 
                               SqlDbType.NVarChar, 15, "Region"));
        oUpdCmd.Parameters.Add(new SqlParameter("@CustomerID", 
                               SqlDbType.NChar, 5, "CustomerID"));
        //————————————————————————————
        //— Set up the INSERT Command
        //————————————————————————————
        sSQL = "INSERT INTO Customers " +
            " (CompanyName, ContactName, City, Region, CustomerID)" +
            " VALUES (@CompanyName, @ContactName, " +
                      " @City, @Region, @CustomerID)";
        oInsCmd = null;
        oInsCmd = new SqlCommand(sSQL, m_oCn);
        oInsCmd.CommandType = CommandType.Text;
        oInsCmd.Parameters.Add(new SqlParameter("@CompanyName", 
                               SqlDbType.NVarChar, 40, "CompanyName"));
        oInsCmd.Parameters.Add(new SqlParameter("@ContactName", 
        SqlDbType.NVarChar, 30, "ContactName"));
        oInsCmd.Parameters.Add(new SqlParameter("@City", 
                               SqlDbType.NVarChar, 15, "City"));
        oInsCmd.Parameters.Add(new SqlParameter("@Region", 
                               SqlDbType.NVarChar, 15, "Region"));
        oInsCmd.Parameters.Add(new SqlParameter("@CustomerID", 
                               SqlDbType.NChar, 5, "CustomerID"));
        //————————————————————————————
        //— Set up the DELETE Command
        //————————————————————————————
        sSQL = "DELETE Customers " +
            " WHERE CustomerID = @CustomerID ";
        oDelCmd = null;
        oDelCmd = new SqlCommand(sSQL, m_oCn);
        oDelCmd.CommandType = CommandType.Text;
        oDelCmd.Parameters.Add(new SqlParameter("@CustomerID", 
                               SqlDbType.NChar, 5, "CustomerID"));
        //————————————————————————————
        //— Create and set up the DataAdapter
        //————————————————————————————
        m_oDA = new SqlDataAdapter();
        m_oDA.SelectCommand = oSelCmd;
        m_oDA.UpdateCommand = oUpdCmd;
        m_oDA.DeleteCommand = oDelCmd;
        m_oDA.InsertCommand = oInsCmd;

        //— Destroy connection object
        m_oCn = null;
    }

}
Figure 9 The CustomerOrder Class and its Constructor
//———————————————————————————————————
// Public Class
// Purpose:   To relate the Customer to their Orders to their Order 
// Details
// Inherits:  Base_BusinessServices class
//———————————————————————————————————
public class CustomerOrder : Base_BusinessServices 
{

    Customer m_oCustomer = null;
    Order m_oOrder = null;
    OrderDetail m_oOrderDetail = null;

    //———————————————————————————————————
    // Class Constructor (zero arguments)
    // Overloaded:    No
    //———————————————————————————————————
    public CustomerOrder()
    {
        //————————————————————————————
        //— Create Customer, Order and Order Detail objects
        //————————————————————————————
        m_oCustomer = new Customer();
        m_oOrder = new Order();
        m_oOrderDetail = new OrderDetail();
    }
Figure 10 GetData Method of the CustomerOrder Class
//—————————————————————————————————
// Public Method
// Overloaded:    No
// Overrides:     Yes
// Parameters:    None
// Return Value:  DataSet
// Purpose:       Gets all Customer, Order and Order Detail records.
//—————————————————————————————————
public override DataSet GetData()
{
    DataSet oDs = new DataSet();

    //————————————————————————————
    //— Retrieve Data for Customers, Order and Order Details
    //————————————————————————————
    DataTable oDt_Customer = null;
    DataTable oDt_Order = null;
    DataTable oDt_OrderDetail = null;

    oDt_Customer= m_oCustomer.GetData().Tables["Customer"].Copy();
    oDt_Order  = m_oOrder.GetData().Tables["Order"].Copy();
    oDt_OrderDetail = 
      m_oOrderDetail.GetData().Tables["OrderDetail"].Copy();

    //————————————————————————————
    //— Load all data into the DataSet
    //————————————————————————————
    oDs.Tables.Add(oDt_Customer);
    oDs.Tables.Add(oDt_Order);
    oDs.Tables.Add(oDt_OrderDetail);

    //————————————————————————————
    //— Relate the Customers to Orders to Order Details
    //————————————————————————————
    oDs.Relations.Add("CustomerToOrder", 
                      oDs.Tables["Customer"].Columns["CustomerID"],
                      oDs.Tables["Order"].Columns["CustomerID"]);
    oDs.Relations.Add("OrderToOrderDetail", 
                      oDs.Tables["Order"].Columns["OrderID"], 
                      oDs.Tables["OrderDetail"].Columns["OrderID"]);

    //————————————————————————————
    //— Return the DataSet
    //————————————————————————————
    return oDs;
}
Figure 11 SaveData Method of the CustomerOrder Class
//———————————————————————————————————
// Public Method
// Overloaded:    No
// Overrides:     Yes
// Parameters:    DataSet
// Return Value:  Status Message
// Purpose:       Saves all items that changed.
//———————————————————————————————————
public override string SaveData(DataSet oDS)
{
    string sMsg;
    SqlTransaction oTran = null;

    //————————————————————————————
    //— Save the data
    //————————————————————————————
    try
    {
        //— Set up the conection manually
        InitializeConnection();
        m_oCn.Open();
        //— Begin a transaction 
        oTran = m_oCn.BeginTransaction();
        //— Make all database changes
        sMsg = m_oCustomer.SaveData(oDS, m_oCn);
        if (sMsg.Length > 0) { throw (new Exception(sMsg)); }
        //— Make all database changes
        sMsg = m_oOrder.SaveData(oDS, m_oCn);
        if (sMsg.Length > 0) { throw (new Exception(sMsg)); }
        //— Make all database changes
        sMsg = m_oOrderDetail.SaveData(oDS, m_oCn);
        if (sMsg.Length > 0) { throw (new Exception(sMsg)); }
        //— Commit the changes
        oTran.Commit();
    }
    catch (Exception e)
    {
        //— An error occurred, so we roll the transaction back
        oTran.Rollback();
        sMsg = "Records were not updated" + e.Message.ToString();
    }
    finally
    {
        //— Close the connection that we manually opened
        oTran = null;
        m_oCn.Close();
        m_oCn = null;
        sMsg = "Records were updated successfully";
    }
    return sMsg;
}