'//————————————————————————
'// Public Class
'// Purpose:    The frmMain class 
'//————————————————————————
Public Class frmMain
    Inherits System.Windows.Forms.Form

    '//——————————————————————
    '// Declare Class-level variables
    '//——————————————————————
    Private m_oDS As DataSet

'//———————————————————————
'// Private Method
'// Overloaded:    No
'// Parameters:    None
'// Return Value:  None
'// Purpose:       Retrieves and then loads the data into the form.
'//———————————————————————
Private Sub LoadData()
    Dim oCustomer As BusinessServices.Customer = _
        New BusinessServices.Customer()

    grdData.DataBindings.Clear()
    m_oDS = oCustomer.GetData()
    grdData.DataSource = m_oDS.Tables("Customer")
    oCustomer = Nothing
End Sub

'//—————————————————————————————————
'// Private Method
'// Overloaded:    No
'// Parameters:    None
'// Return Value:  None
'// Purpose:       Sends the data changes to the business services.
'//—————————————————————————————————
Private Sub SaveData()
    Dim lRetVal As Long
    Dim oCustomer As BusinessServices.Customer = _
        New BusinessServices.Customer()
    Dim oDS_Delta As DataSet
    Dim sMsg As String

    If m_oDS Is Nothing Then Exit Sub
    '//— Check for changes with the HasChanges method first.
    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 = oCustomer.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
    oCustomer = Nothing
End Sub

namespace BusinessServices
{
    //———————————————————————————————
    // Filename:  BusinessServices.cs
    // Author:    Lancelot Web Solutions, LLC
    // Date:      09/1/2001
    // Purpose:   The BusinessServices component is an interface between
    //            the presentation services tier and the data services. 
    //———————————————————————————————

    //———————————————————————————————
    // Declare all the NameSpaces to be referenced
    //———————————————————————————————
    using System;
    using System.Data;
    using System.Data.SqlClient;

public class Customer
{

    //———————————————————————————————
    // Declare Class-level variables
    //———————————————————————————————
    //— The SQL Server instance
    private string m_sServer = "localhost";     
    //— The Database name
    private string m_sDatabase = "northwind";   
    //— The Database login User ID
    private string m_sUsername = "sa";          
    //— The Database login Password
    private string m_sPassword = "";                
    //— The DataSet to use 
    private DataSet m_oDS ;                     
    //— The Connection the database
    private SqlConnection m_oCn ;               
    //— The DataAdapter that links the DataSet to the Connection
    private SqlDataAdapter m_oDA;               
    //— The name of the class
    private string m_sClassName = "Customer";

//——————————————————————————————————
// Class Constructor (zero arguments)
// Overloaded:    No
//——————————————————————————————————
public 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;
}

//——————————————————————————————————
// public Method
// Overloaded:    No
// Parameters:    None
// Return Value:  DataSet
// Purpose:       Retrieves all customers.
//——————————————————————————————————
public 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;
}

//——————————————————————————————————
// public Method
// Overloaded:    No
// Parameters:    DataSet
// Return Value:  Status Message
// Purpose:       Saves all customers that changed.
//——————————————————————————————————
public string SaveData(DataSet oDS)
{
    string sMsg;
    long lRecsAffected;

    //————————————————————————————
    //— Save the data
    //————————————————————————————
    try
    {
        //— Set up the conection manually
        InitializeConnection();
        m_oCn.Open();
        //— Make all database changes
        lRecsAffected = m_oDA.Update(oDS, m_sClassName);
        //— Set the message for the user
        sMsg = lRecsAffected + " Customer Records Were Updated";
    }
    catch (Exception e)
    {
        sMsg = "Records were not updated" + e.Message.ToString();
    }
    finally
    {
        //— Close the connection that we manually opened
        m_oCn.Close();
        m_oCn = null;
    }
    return sMsg;
}