// ______________________________________________________________________________________________

//                                      USING ADO IN CODE STEP 2
/*                                                 Ron Kessler
                                    Created 4/22/2007 for C# 2005

 NOTES:
 This demonstrates how to update our DB using code. In the save event they can edit/delete/insert records

 I added a menu strip control and added images/short-cut keys so check out the properties of Save & Exit.
 * 
 * 
 * FEATURES:
 *  You can select different records from a menu.
 *  You can only save changes if all records are retrieved and there were changes made to the dataset.
 *  The ADO code is inside its on class called ADOMethods.cs
 *  I changed the DB property to tell VS DO NOT COPY DB to output directory.
 * 
*/




using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;      //remember this



namespace ADO_MSAccess_In_Code
{
    public partial class frmADO2 : Form
    {
        public frmADO2()
        {
            InitializeComponent();
        }


        //---class-level stuff      
        DataSet myDataSet = new DataSet();    //declare once, use many!
        

        private void frmADO2_Load(object sender, EventArgs e)
        {
            string tableName = "customers";     //show all records upon startup
            string sqlString = "SELECT * from Customers ORDER BY LastName";
            showRecords(sqlString, tableName);
        }


        private void showRecords(string sqlString, string tableName)
        {
            //---fills the dataset & binds the datagrid control
            ADO_Methods.FillTheGrid(myDataSet, sqlString, tableName);
            DataGrid1.DataSource = myDataSet;
            DataGrid1.DataMember = tableName;
        }

        #region SQL Select Statements
        /// <summary>
        /// This method evaluates which menu item they select from the SQL_Menu.
        /// </summary>
        /// <param name="sender">I convert the object they selected to a ToolStripMenuItem</param>
        /// <param name="e"></param>
        private void SQL_Menu_Click(object sender, EventArgs e)
        {
            //---no updating unless they are viewing all records!
            if (mnuFileSave.Enabled == true)
                mnuFileSave.Enabled = false;
                        
            ToolStripMenuItem whichQuery = (ToolStripMenuItem) sender; //convert sender to TSMI type
            string sqlString = "";
            string tableName = "";

            //---create a new SQL query based on the menu item they chose. Each query goes to its own table
            switch (whichQuery.Name)    //which menu item?
            {
                case "LastFirstTelephoneToolStripMenuItem":

                    sqlString = "SELECT LastName as [Last Name], FirstName as [First Name]," +
                        "Tele as [Telephone] FROM Customers ORDER BY LastName";
                    tableName = "sql1";
                    break;

                case "LastFirstAddressZipToolStripMenuItem":

                    sqlString = "SELECT Lastname AS [Last Name], FirstName AS [First Name]," +
                        "StreetAddress AS [Street Address], Zip AS [Zip Code] from Customers ORDER BY Tele";
                    tableName = "sql2";
                    break;
                case "LastFirstTeleFaxToolStripMenuItem":

                    sqlString = "SELECT Customers.Lastname AS [Last Name], " +
                        "Customers.FirstName AS [First Name], Tele AS [Telephone], " +
                        "Fax AS [Fax #] from Customers ORDER BY Tele";
                    tableName = "sql3";
                    break;
                case "SelectALLToolStripMenuItem":
                    sqlString = "SELECT * from Customers ORDER BY LastName";
                    tableName = "sql4";
                    mnuFileSave.Enabled = true;
                    break;
            }
                       
            showRecords(sqlString, tableName);
        
        }
        #endregion

        
        //---save changes
        private void mnuFileSave_Click(object sender, EventArgs e)
        {
            string sqlString = "SELECT * from Customers ORDER BY LastName";
            string tableName = "customers";
            ADO_Methods.DoUpdate(myDataSet, sqlString, tableName);
        
        }
        


        private void mnuFileExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

       
      

    }
}





/*_____________________________________________________________
 *                               A D O   M E T H O D S   C L A S S
 *                                            Ron Kessler
 *                                              4/27/07
 *  
 *____________________________________________________________
*/
//This class holds the major ADO methods.



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;      //remember this


namespace ADO_MSAccess_In_Code
{
    class ADO_Methods
    {

        #region DB connection
        /// <summary>
        /// This static method creates a new connection object.
        /// </summary>
        /// <returns>OLEDB Connection object.</returns>
       public static OleDbConnection MakeConnection()
        {
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + "..\\..\\customers.mdb";
            OleDbConnection conn = new OleDbConnection(connString);
            return conn;
        }

        #endregion



        #region Data Adapter Command builder
        /// <summary>
        /// Static method that uses the command builder class to add the insert/edit/update commands to the data adapter.
        /// </summary>
        /// <param name="dataAdapter"></param>
        /// <param name="tableName"></param>
        /// <returns>OLE DB command builder.</returns>
        public static OleDbCommandBuilder MakeDACommands(OleDbDataAdapter dataAdapter, string tableName)
        {

            OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(dataAdapter);
            dataAdapter.TableMappings.Add("Table", tableName);
            return myCommandBuilder;
        }
        #endregion


        #region Fill the Grid
        /// <summary>
        /// A Static method that fills the dataset for the calling program.
        /// </summary>
        /// <param name="myDataSet">This is the name of the dataset to be filled.</param>
        /// <param name="sqlString">The SQL SELECT query string.</param>
        /// <param name="tableName">The name of the table to hold the returned records.</param>
        public static void FillTheGrid(DataSet myDataSet, string sqlString, string tableName)
        {

            OleDbConnection conn = ADO_Methods.MakeConnection();

            try
            {

                //---be sure to declare these in this order
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString, conn))
                {
                   OleDbCommandBuilder DACommands = ADO_Methods.MakeDACommands(dataAdapter, tableName); //add update/insert/delete to DA
                   dataAdapter.Fill(myDataSet, tableName);
                   
                }
            }
            catch (OleDbException ex)         //trap any DB errors
            {
                MessageBox.Show("I could not read the database." + ex.Message, "System Message",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
                
            }

            finally
            {
                conn.Close();                      ///just to be sure
            }
        }

        #endregion

        #region Update DS
        /// <summary>
        /// This static method updates the DB after any changes. If the DS has changes.
        /// </summary>
        /// <param name="myDataSet">This is the name of the dataset to be filled.</param>
        /// <param name="sqlString">The SQL SELECT query string.</param>
        /// <param name="tableName">The name of the table to hold the returned records.</param>
        public static void DoUpdate(DataSet myDataSet, string sqlString, string tableName)
        {

            OleDbConnection conn = ADO_Methods.MakeConnection();
            if (myDataSet.HasChanges())
            {
                try
                {
                    conn.Open();
                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sqlString,conn))
                    {

                        OleDbCommandBuilder DACommands = ADO_Methods.MakeDACommands(dataAdapter , tableName);
                        dataAdapter.Update(myDataSet);
                        MessageBox.Show("Your data has been updated.", "System Message",
                             MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                catch (OleDbException ex)         //trap any DB errors
                {
                    MessageBox.Show("I could not read the database." + ex.Message, "System Message",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);

                }
                catch (DataException ex)              //any errors with the data?
                {
                    MessageBox.Show("An error occurred." + ex.Message, "Database Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conn.Close();

                }

            }
        #endregion

        }

       
       
    }
}