 /*Copyright GBASE. 2009.  All rights reserved.                 */
 /*                                                                   */
 /*This sample program is owned by International Business Machines    */
 /*Corporation or one of its subsidiaries ("IBM") and is copyrighted  */
 /*and licensed, not sold.                                            */
 /*                                                                   */
 /*You may copy, modify, and distribute this sample program in any    */
 /*form without payment to IBM,  for any purpose including developing,*/
 /*using, marketing or distributing programs that include or are      */
 /*derivative works of the sample program. Licenses to IBM patents    */
 /*are expressly excluded from this license.                          */
 /*The sample program is provided to you on an "AS IS" basis, without */
 /*warranty of any kind.  IBM HEREBY  EXPRESSLY DISCLAIMS ALL         */
 /*WARRANTIES EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO*/
 /*THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTIC-*/
 /*ULAR PURPOSE. Some jurisdictions do not allow for the exclusion or */
 /*limitation of implied warranties, so the above limitations or      */
 /*exclusions may not apply to you.  IBM shall not be liable for any  */
 /*damages you suffer as a result of using, modifying or distributing */
 /*the sample program or its derivatives.                             */
 /*                                                                   */
 /*Each copy of any portion of this sample program or any derivative  */
 /*work,  must include the above copyright notice and disclaimer of   */
 /*warranty.                                                          */
 /*                                                                   */

using GBS.Data.GBasedbt;
using System;	
using System.Data;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Security.Cryptography; //remove
using System.Text;
using System.IO;


namespace DOTNETDEMO
{
	/// <summary>
	/// The IfxDemo Class acts as a provider of database services for updates, 
	/// deletes and inserts.It has different services for Command line demo app 
	/// and the Gui App. These are delineated through regions in the IDE editor s
	/// follows : #region OPERATIONS USED WITH COMMAND LINE EXAMPLE, 
	/// #region OPERATIONS USED BY GUI APP DEMO etc
	/// </summary>
	/// 

	public class IfxDemo
	{
		private DataSet _DemoDataSet = null;
		private static IfxDemo _IfxDemo = null;
		DemoUtils _Utils = null;
		IfxConnection _IfxCon = null;
		private IfxDataAdapter _IfxDaOrders = null;
		private IfxDataAdapter _IfxDaCustomers = null;
		private IfxCommandBuilder _OrderCmdBuilder = null;
		private IfxCommandBuilder _CustCmdBuilder = null;

		private IfxDemo()
		{
			if(_Utils == null)
			{
				_Utils = DemoUtils.GetInstance();
			}

			_IfxCon = new IfxConnection(_Utils.DBConnection);

			//creates the tables and loads the data for the demo
			_Utils.LoadData(_IfxCon);

			_DemoDataSet = FillDataSet(_IfxCon);
		}

		public static IfxDemo GetInstance()
		{
			if(_IfxDemo == null)
			{
				_IfxDemo = new IfxDemo();
			}
			return _IfxDemo;

		}

		#region OPERATIONS USED BY GUI APP DEMO
		/// <summary>
		/// Property: Returns the DataSet used by the Demo
		/// </summary>
		public DataSet DemoData
		{
			get
			{
				return _DemoDataSet;
			}

		}

		/// <summary>
		/// Property: Returns the DataTable Item in the Demo DataSet
		/// </summary>
		public DataTable DemoItems
		{
			get
			{
				return _DemoDataSet.Tables["item"];
			}

		}

		/// <summary>
		/// Property: Returns the DataTable orders in the Demo DataSet
		/// </summary>
		public DataTable DemoOrders
		{
			get
			{
				return _DemoDataSet.Tables["orders"];
			}
		}

		/// <summary>
		/// Property: Returns the DataTable customer in the Demo DataSet
		/// </summary>
		public DataTable DemoCustomers
		{
			get
			{
				return _DemoDataSet.Tables["customer"];
			}
		}

		/// <summary>
		/// Inserts new orders into the Database and resyncs the DataSet
		/// </summary>
		/// <param name="Orders"> An Order instance</param>
		/// <returns></returns>
		public bool CreateOrders(ArrayList Orders)
		{
			bool Success = false;
			IfxConnection Conn = new IfxConnection(_Utils.DBConnection);

			Conn.Open();

			DataTable OrdersTable = _DemoDataSet.Tables["orders"];
			int rows = OrdersTable.Rows.Count;
			//Find the last order number and increment it for our new order.
			int order_num = (int)OrdersTable.Rows[rows-1]["order_num"];
			int Increment = order_num + 1;

//			DataSet Ds = GetData();
			foreach(Order NewOrder in Orders)
			{
				DataRow NewRow = OrdersTable.NewRow();
				NewRow["order_num"] = Increment; //equivalent to rows+1
				NewRow["cust_num"] = NewOrder.CustomerID ;
				NewRow["item_num"] = NewOrder.ItemID;
				NewRow["order_date"] = NewOrder.Date.Date;
				NewRow["quantity"] = NewOrder.Quantity;

				_DemoDataSet.Tables["orders"].Rows.Add(NewRow);
				Increment++;
				
				//create a new row in the datatable orders
			}
			IfxTransaction Trans = Conn.BeginTransaction();
			int Inserts  = 0;

			//event handlers to let the user know what happened or is 
			//happening against the GBasedbt DB
			_IfxDaOrders.RowUpdating += 
				new IfxRowUpdatingEventHandler( OnRowUpdating );

			_IfxDaOrders.RowUpdated += 
				new IfxRowUpdatedEventHandler( OnRowUpdated );

			try
			{
				_OrderCmdBuilder.GetInsertCommand().Connection = Conn;
				_OrderCmdBuilder.GetUpdateCommand().Connection = Conn;
				_OrderCmdBuilder.GetInsertCommand().Transaction = Trans;
				_OrderCmdBuilder.GetUpdateCommand().Transaction  = Trans;
				
				Inserts = _IfxDaOrders.Update(_DemoDataSet,"orders");			
				Trans.Commit();
			}
			catch(Exception excp)
			{
				Console.WriteLine("Exception:" + excp.Message);
			}

			//let's commit the changes in the DataSet also
			if(Inserts > 0)
			{
				_DemoDataSet.AcceptChanges();
				FillDataSet(Conn);
				Success = true;
			}
			_IfxDaOrders.RowUpdating -= 
				new IfxRowUpdatingEventHandler( OnRowUpdating );
			_IfxDaOrders.RowUpdated-= 
				new IfxRowUpdatedEventHandler( OnRowUpdated );

			Conn.Close();
			return Success;
		}

		/// <summary>
		/// Removes an order from the Order table in the db and resyncs DataSet
		/// </summary>
		/// <param name="Orders">An Order instance</param>
		/// <returns></returns>
		public bool DeleteOrders(ArrayList Orders)
		{
			bool Success = false;
			if(_IfxCon.State == ConnectionState.Open)
			{
				_IfxCon.Close();
			}
			_IfxCon.Open();
			foreach (Order CurrOrder in Orders)
			{
				string Del = "Delete from orders where order_num = ?";
				IfxCommand DelCmd = new IfxCommand(Del,_IfxCon);
				IfxParameter Param = DelCmd.CreateParameter();
				Param.Value = CurrOrder.OrderID;
				DelCmd.Parameters.Add(Param);
				DelCmd.ExecuteNonQuery();
			}

			FillDataSet(_IfxCon);
			_IfxCon.Close();
			
			return Success;
		}

		/// <summary>
		/// Deletes orders from Db based on changes to the DataSet
		/// </summary>
		/// <returns></returns>
		public bool DeleteOrders()
		{
			bool Success = false;

			DataTable DelTable = 
					_DemoDataSet.Tables["orders"].GetChanges(DataRowState.Deleted);
			if(DelTable != null)
			{
				_OrderCmdBuilder.GetDeleteCommand().Connection = _IfxCon;
				try
				{
					int Deletes = _IfxDaOrders.Update(_DemoDataSet, "orders");
					int x = Deletes;
				}
				catch(Exception excp)
				{
					Console.WriteLine(excp.Message);
				}
			}

			return Success;

		}

		/// <summary>
		/// Changes Customer address values based on updates to the database
		/// </summary>
		public void UpdateCustomers()
		{
			DataTable CustTable = _DemoDataSet.Tables["customer"];
			DataTable Changes = CustTable.GetChanges(DataRowState.Modified);
			
			try
			{

				_IfxDaCustomers.Update(_DemoDataSet,"customer");
			}
			catch(Exception excp)
			{
				Console.WriteLine(excp.Message);
			}
		}

		public DataSet GetActiveCustomers()
		{
			IfxConnection Conn = new IfxConnection(_Utils.DBConnection);
			Conn.Open();

			DataSet Ds = new DataSet();

			IfxDataAdapter IfxDaCusts = new IfxDataAdapter(
			"select UNIQUE C.fname, C.lname from customer C, orders O where C.cust_num = O.cust_num;",Conn);
			
			IfxDaCusts.Fill(Ds,"Customers");
			Conn.Close();
			return Ds;
		}
		#endregion

		#region OPERATIONS USED WITH COMMAND LINE EXAMPLE
		public void UpdateWithCommand()
		{

		}

		/// <summary>
		/// Demonstrates using the IfxCommand object to build an insert query and 
		/// send changes to the Database. Illustrates the use of ExecuteScalar to 
		/// retrieve the last record id in the table. Illustrates the use of 
		/// IfxParameter to bind parameters. Illustrates the use of ExecuteNonQuery 
		/// to perform an Insert Command. 
		/// </summary>
		public void InsertWithCommand()
		{
			if(_IfxCon.State == ConnectionState.Open)
			{
				_IfxCon.Close();
			}
			_IfxCon.Open();

			IfxCommand SelCmd =  new IfxCommand("select max(item_num) from item",_IfxCon);
			SelCmd.CommandType = CommandType.Text;
			//obtain the last item number as  integer.
			Int32 last_num = (Int32)SelCmd.ExecuteScalar();
			_IfxCon.Close();
			
            String InsItems = 
                "INSERT INTO item (item_num,description,stock,ship_unit, unit_price,advert)" + 
                " VALUES(?, ?, ?, ?, ?, ?)";

			_IfxCon.Open();
			IfxCommand Cmd = new IfxCommand(InsItems,_IfxCon);
			
			Cmd.Parameters.Add("item_num",last_num+1);
			Cmd.Parameters.Add("Description","Soccer Net");
			Cmd.Parameters.Add("stock",25);
			Cmd.Parameters.Add("Shipping_unit","Each");
			Cmd.Parameters.Add("Unit_Price",30.50);
            Cmd.Parameters.Add("advert", "Super strong nylon threaded nets. All weather durable.");
					
			//perform the insert
			try 
			{
				int ret = Cmd.ExecuteNonQuery();
				if(ret > 0)
				{
					Console.WriteLine("InsertSucceeded");
				}
			} catch (IfxException ie)
			{
				Console.WriteLine("Error: " + ie.Message);
				Console.WriteLine(ie.StackTrace);
			}			
			_IfxCon.Close();

		}

		/// <summary>
		/// Demonstrates using IfxDataAdapter,IfxTransaction,IfxCommandBuilder
		/// and DataSet to insert new rows into an GBasedbt Database.
		/// Illustrates the use of DataRelation to 
		/// build the relationship between customer and orders. 
		/// Uses IfxCommandBuilder to generate the Insert statement for the db.
		/// </summary>
		public void InsertDataSetInTrans()
		{
			DataSet Ds = new DataSet();
			if(_IfxCon.State == ConnectionState.Open)
			{
				_IfxCon.Close();
			}
			_IfxCon.Open();
			
			IfxDataAdapter IfxDaOrders = 
					new IfxDataAdapter("select * from orders",_IfxCon);
			//we will use the CommandBuilder to generate our insert command for us
			//otherwise we will have to build our own insert command and set it 
			IfxCommandBuilder CmdBuilder = new IfxCommandBuilder(IfxDaOrders);
			//NOTE: IfxCommandBuilder does not generate the required statements until
			//the Update is called. This makes it challenging to associate a transaction 
			//with the Command object used by the CommandBuilder. The call below forces 
			//CommandBuilder to generate the Command. 
			CmdBuilder.GetInsertCommand();
			CmdBuilder.GetUpdateCommand();
			CmdBuilder.GetDeleteCommand();

			IfxDaOrders.Fill(Ds, "orders");

			IfxDataAdapter IfxDaCustomers = new IfxDataAdapter("select * from customer",_IfxCon);
			IfxDaCustomers.Fill(Ds, "customer");

			IfxDataAdapter IfxDaItem = new IfxDataAdapter("select * from item",_IfxCon);
			IfxDaItem.Fill(Ds, "item");

			DataTable CustTable = Ds.Tables["customer"];
			DataRow CustRow = CustTable.Rows[0];

			//ADD A NEW ORDER
			//First establish the relationships 
			DataColumn Cust_Num  = Ds.Tables["customer"].Columns["cust_num"];
			DataColumn Order_Cust_Num = Ds.Tables["orders"].Columns["cust_num"];

			DataColumn[] ColArr = new DataColumn[1];
			ColArr[0] = Cust_Num;
			
			Ds.Tables["customer"].PrimaryKey = ColArr;
			Ds.Relations.Add("CustomerOrders",
				Ds.Tables["customer"].Columns["cust_num"],
				Ds.Tables["orders"].Columns["cust_num"]);

			ForeignKeyConstraint FKCust = new 
				ForeignKeyConstraint("CustomerOrder",Cust_Num,Order_Cust_Num);
			FKCust.DeleteRule = Rule.Cascade;
			Ds.EnforceConstraints = true;

			//insert three new rows into the datatable orders
			DataTable OrdersTable = Ds.Tables["orders"];
			int rows = OrdersTable.Rows.Count;
			//Find the last order number and increment it for our new order.
			Int32 order_num = (Int32)OrdersTable.Rows[rows-1]["order_num"];
			DataRow NewRow = OrdersTable.NewRow();
			NewRow["order_num"] = order_num+1; //equivalent to rows+1
			NewRow["cust_num"] = 103 ;
			NewRow["item_num"] = 1005;
			DateTime Current = DateTime.Today;
			NewRow["order_date"] = Current.Date;
			NewRow["quantity"] = 50;

			Ds.Tables["orders"].Rows.Add(NewRow);

			NewRow = OrdersTable.NewRow();
			NewRow["order_num"] = order_num+2; //equivalent to rows+1
			NewRow["cust_num"] = 101 ;
			NewRow["item_num"] = 1002;
			NewRow["order_date"] = DateTime.Parse("9/12/2001").Date;
			NewRow["quantity"] = 10;

			Ds.Tables["orders"].Rows.Add(NewRow);

			NewRow = OrdersTable.NewRow();
			NewRow["order_num"] = order_num+3; //equivalent to rows+1
			NewRow["cust_num"] = 102 ;
			NewRow["item_num"] = 1004;
			Current = DateTime.Today;
			NewRow["order_date"] = DateTime.Parse("10/08/2002").Date;
			NewRow["quantity"] = 10;

			Ds.Tables["orders"].Rows.Add(NewRow);

			//send changes to database through the dataadapter.
			//USE AN IfxTransaction
			IfxTransaction Trans = _IfxCon.BeginTransaction();
			CmdBuilder.GetInsertCommand().Transaction = Trans;
			CmdBuilder.GetUpdateCommand().Transaction  = Trans;
			
			try 
			{
				int Inserts = IfxDaOrders.Update(Ds,"orders");		
				Trans.Commit();

				//let's commit the changes in the DataSet also
				if(Inserts > 0)
				{
					Ds.AcceptChanges();
				}
				Console.WriteLine("Orders updated");	
				Console.Read();
			} 
			catch(IfxException ie)
			{
				Console.WriteLine("Error: " + ie.Message);
				Console.WriteLine(ie.StackTrace);
			}

		}
     /// <summary>
     /// Executes a select query using ExecuteReader() method,
     /// loads a datatable with the resultset and prints the output.
     /// Illustrates the use of DataRow and DataTable.
     /// Uses IfxDataReader to get the data from db.
     /// </summary>
     public void ExecuteQuery()
     {
         if (_IfxCon.State == ConnectionState.Open)
         {
             _IfxCon.Close();
         }
         _IfxCon.Open();

         IfxCommand cmd = new IfxCommand("select a.order_num, c.lname as Customer, b.description as Item, "
             + "a.quantity  from orders a, item b, customer c where a.cust_num = c.cust_num and a.item_num = b.item_num", _IfxCon);
         DataTable dt = new DataTable();
         try
         {
            dt.Load(cmd.ExecuteReader());
            foreach (DataRow thisRow in dt.Rows)
                Console.WriteLine(String.Format("{0} {1} {2} {3}", thisRow[0], thisRow[1], thisRow[2], thisRow[3]));
         }
         catch (IfxException ie)
         {
             Console.WriteLine("Error: " + ie.Message);
             Console.WriteLine(ie.StackTrace);
         }
         _IfxCon.Close();
     }

     /// <summary>
     /// Gets the catalog info of a procedure raise_prices,
     /// using CommandBuilder.DeriveParameters.
     /// </summary>
     public void CatalogInfo()
     {
         if (_IfxCon.State == ConnectionState.Open)
         {
             _IfxCon.Close();
         }
         _IfxCon.Open();
         IfxCommand cmd = new IfxCommand("drop procedure raise_prices", _IfxCon);
         try
         {
             cmd.ExecuteNonQuery();
         }
         catch
         {
             //ignore
         }
         cmd.CommandText = "CREATE PROCEDURE raise_prices(per_cent int)\n"
             + " UPDATE item\n"
             + " SET unit_price = unit_price + (unit_price * (per_cent/100));\n"
             + " END procedure;\n";
         cmd.ExecuteNonQuery();

         cmd.CommandText = "SELECT item_num, unit_price FROM item";
         Console.WriteLine("Item table contents before executing the raise_prices stored procedure. Observe unit_price values");
         IfxDataReader dr = cmd.ExecuteReader();
         while (dr.Read())
         {
             Console.WriteLine(String.Format("{0} ----- {1}", dr[0], dr[1]));
         }
         dr.Close();

         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "raise_prices";
         IfxCommandBuilder.DeriveParameters(cmd); //cmd.Parameters will be populated.
         cmd.Parameters[1].Value = 10;
         Console.WriteLine("DeriveParameters Output:");
         foreach (IfxParameter param in cmd.Parameters)
         {
             Console.WriteLine("Name: {0}  Direction: {1} Type: {2} Size: {3} param: {4} value: {5}", param.ParameterName, param.Direction, param.DbType, param.Size, param.ToString(), param.Value);
         }

         cmd.ExecuteNonQuery();
         cmd.CommandType = CommandType.Text;
         cmd.CommandText = "SELECT item_num, unit_price FROM item";
         Console.WriteLine("Item table contents after executing the raise_prices stored procedure. Observe unit_price values");
         dr = cmd.ExecuteReader();
         while (dr.Read())
         {
             Console.WriteLine(String.Format("{0} ----- {1}", dr[0], dr[1]));
         }
         dr.Close();
         _IfxCon.Close();
     }

		#endregion  

		#region HANDLERS COMMON TO COMMAND LINE AND GUI APP(USED IN GUI APP)
		// handler for RowUpdating event
		protected static void OnRowUpdating(object sender, IfxRowUpdatingEventArgs args) 
		{
			
			string UpdMsg = "Row Updating Event: " + args.Command + " ,Command Type: " + args.StatementType + ", Status: " + args.Status;
			 
			Console.WriteLine(UpdMsg);

		}

		// handler for RowUpdating event
		protected static void OnRowUpdated(object sender, IfxRowUpdatedEventArgs args) 
		{
			string UpdMsg = "Row Updated Event: " + args.Command + " ,Command Type: " + args.StatementType + ", Status: " + args.Status;
			Console.WriteLine(UpdMsg);

		}
		#endregion

		#region OPERATIONS COMMON TO COMMAND LINE AND GUI DEMO APPS


		/// <summary>
		/// Demonstrates using DataTables, IfxCommandBuilder and IfxAdapter
		/// </summary>
		/// <param name="Conn"></param>
		/// <returns></returns>
		private DataSet FillDataSet(IfxConnection Conn)
		{
			_DemoDataSet = new DataSet();
			_IfxDaOrders = 
				new IfxDataAdapter("select * from orders",Conn);
			//we will use the CommandBuilder to generate our insert command for us
			//otherwise we will have to build our own insert command and set it 
			//we will use the CommandBuilder to generate our insert command for us
			//otherwise we will have to build our own insert command and set it 
			_OrderCmdBuilder = new IfxCommandBuilder(_IfxDaOrders);
			//NOTE: IfxCommandBuilder does not generate the required statements 
			//until the Update is called. 
			//This makes it challenging to associate a transaction 
			//with the Command object used by the CommandBuilder. The call below forces
			//CommandBuilder to generate the Command. 
			_OrderCmdBuilder.GetInsertCommand();
			_OrderCmdBuilder.GetUpdateCommand();
			_OrderCmdBuilder.GetDeleteCommand();
			_IfxDaOrders.Fill(_DemoDataSet, "orders");

			_IfxDaCustomers = 
				new IfxDataAdapter("select * from customer",Conn);
			_CustCmdBuilder = new IfxCommandBuilder(_IfxDaCustomers);
			//generate ahead the update command
			_CustCmdBuilder.GetUpdateCommand();

			_IfxDaCustomers.Fill(_DemoDataSet, "customer");

			IfxDataAdapter IfxDaItem = 
				new IfxDataAdapter("select * from item",Conn);
			IfxDaItem.Fill(_DemoDataSet, "item");

			DataColumn Cust_Num  = _DemoDataSet.Tables["customer"].Columns["cust_num"];
			DataColumn Order_Cust_Num = _DemoDataSet.Tables["orders"].Columns["cust_num"];
			DataColumn Order_Num = _DemoDataSet.Tables["orders"].Columns["order_num"];

			DataColumn[] ColArr = new DataColumn[1];
			DataColumn[] ColArrOrder = new DataColumn[1];
			ColArr[0] = Cust_Num;
			ColArrOrder[0] = Order_Num;

			_DemoDataSet.Tables["customer"].PrimaryKey = ColArr;
			_DemoDataSet.Tables["orders"].PrimaryKey = ColArrOrder;

			_DemoDataSet.Relations.Add("CustomerOrders",
				_DemoDataSet.Tables["customer"].Columns["cust_num"],
				_DemoDataSet.Tables["orders"].Columns["cust_num"]);

			ForeignKeyConstraint FKCust = new 
				ForeignKeyConstraint("CustomerOrder",Cust_Num,Order_Cust_Num);
			FKCust.DeleteRule = Rule.Cascade;
			_DemoDataSet.EnforceConstraints = true;

			return _DemoDataSet;

		}
		#endregion


	}
}
