using System;
using System.Data;
using db = System.Data.OleDb;
// File: DataSetAddModRemoveRows.cs(.txt)
// (Re)written  By: Dan Garlen February 2004
//  as part of a C# ADO.NET demonstration series
//  Requires: ConsoleUtilityRoutines.cs & C:\TestData\Northwind.mdb


namespace ADOdotNET_Demos
{
   public class DataSetAddModRemoveRows
   {
      public static string strSQLchanges = "SELECT * FROM [Customers WHERE [Contact Name] 'ALFKI'";
      public static string strSQL = "SELECT * FROM [Customers]";
      public static string strMDB = "C:\\TestData\\Northwind.mdb";
      public static string strDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" + strMDB; 

      static void Main(string[] args)
      {
         cur.Title("ADO Lab");
         //ChangeData();
         //AddData();
         DeleteData();
         cur.Footer();
      }

      public enum CustomerFields
      {
         ID = 0,
         CompanyName = 1,
         ContactName
      };

      public static void ChangeData()
      {
         db.OleDbConnection cnChanges = new db.OleDbConnection(strDB); 
         db.OleDbDataAdapter daChanges = new db.OleDbDataAdapter(strSQL, cnChanges);
         db.OleDbCommandBuilder cb = new db.OleDbCommandBuilder(daChanges); 
         System.Data.DataSet dsChanges = new System.Data.DataSet("Changes"); 

         cnChanges.Open(); 
         daChanges.Fill(dsChanges,"Changes"); 

         DataRow drChange; 
         int Records = dsChanges.Tables["Changes"].Rows.Count;
         for(int i=0;i < Records;i++)
         {
            drChange = dsChanges.Tables["Changes"].Rows[i]; 
            string IDBefore = drChange.ItemArray[(int)CustomerFields.ID].ToString();
            Console.Write("ID = [" + IDBefore + "]   ");
            string CompanyBefore = drChange.ItemArray[(int)CustomerFields.CompanyName].ToString();
            cur.WL("Company = [" + CompanyBefore + "]");

            if(IDBefore == "ALFKI")
            {
                        drChange = dsChanges.Tables["Changes"].Rows[i]; 
                        drChange.BeginEdit(); 
                        string CompanyAfter = "New Co-04";
                        drChange[(int)CustomerFields.CompanyName] = CompanyAfter; 
                        drChange.EndEdit();
               daChanges.Update(dsChanges, "Changes");
               cur.Title("Changing: " + IDBefore);
               string NewCompany = drChange.ItemArray[(int)CustomerFields.CompanyName].ToString();
               cur.WL("New CompanyName = [" + NewCompany + "]");
               cur.Footer();
            }

         }
           
      }


      public static void DeleteData()
      {
         string DelID = "ABC03"; // must be 5 Chars
         db.OleDbConnection cnDel = new db.OleDbConnection(strDB); 
         db.OleDbDataAdapter daDel = new db.OleDbDataAdapter(strSQL, cnDel);
         db.OleDbCommandBuilder cb = new db.OleDbCommandBuilder(daDel); 
         DataSet dsDel = new System.Data.DataSet("Del"); 

         cnDel.Open(); 
         daDel.Fill(dsDel,"Del"); 

         DataRow drDel;
         bool Success = true;
         bool RecordFound = false;

         int Records = dsDel.Tables["Del"].Rows.Count;
         for(int i=0;i < Records;i++)
         {
            drDel = dsDel.Tables["Del"].Rows[i]; 
            string ID = drDel.ItemArray[(int)CustomerFields.ID].ToString();
            Console.Write("ID = [" + ID + "]   ");
            string Company = drDel.ItemArray[(int)CustomerFields.CompanyName].ToString();
            cur.WL("Company = [" + Company + "]");
            if (DelID==ID)
            {
               try
               {
                  drDel.Delete(); 
                  daDel.Update(dsDel, "Del");
                  cur.Success("Delete");
                  RecordFound = true;
               }
               catch (Exception ex) 
               {
                  Success = false;
                  cur.Failure("Delete");
                  cur.WL("DB Error", ex.Message.ToString());
               }
               
            }

         }
         if (RecordFound==false)
         {
            cur.Failure("Record Not Found.");
         }
           
      }




      public static void AddData()
      {

         string NewID = "ABC04"; // must be 5 Chars
         string NewCompanyName = "My Co3"; 
         string NewContactName = "Any Guy 3";
         db.OleDbConnection cnAdd = new db.OleDbConnection(strDB); 
         db.OleDbDataAdapter daAdd = new db.OleDbDataAdapter(strSQL, cnAdd);
         db.OleDbCommandBuilder cb = new db.OleDbCommandBuilder(daAdd); 
         DataSet dsAdd = new System.Data.DataSet("Add"); 

         cnAdd.Open(); 
         daAdd.Fill(dsAdd,"Add"); 
         DataRow drAdd;
         bool Success = true;
         try
         {
            drAdd = dsAdd.Tables["Add"].NewRow(); 
            dsAdd.Tables["Add"].Rows.Add(drAdd);
            drAdd.BeginEdit(); 
            drAdd[(int)CustomerFields.ID]=NewID; 
            drAdd[(int)CustomerFields.CompanyName]=NewCompanyName; 
            drAdd[(int)CustomerFields.ContactName]=NewContactName; 
            drAdd.EndEdit();
            daAdd.Update(dsAdd, "Add");
            cur.Success("Add");
         }
         catch 
            (
            Exception ex) 
            {
               Success = false;
               cur.Failure("Add");
               cur.WL("DB Error", ex.Message.ToString());
            }
         
         int Records = dsAdd.Tables["Add"].Rows.Count;
         for(int i=0;i < Records;i++)
         {
            drAdd = dsAdd.Tables["Add"].Rows[i]; 
            string ID = drAdd.ItemArray[(int)CustomerFields.ID].ToString();

            Console.Write("ID = [" + ID + "]   ");

            string Company = drAdd.ItemArray[(int)CustomerFields.CompanyName].ToString();
            cur.WL("Company = [" + Company + "]");
            if ((NewID==ID) && Success)
            {
               cur.Title("The above item has been added.");
            }

         }
           
      }

  
   }
}