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