using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using Pervasive.Data.SqlClient;

namespace CreateDatabaseObjects
{
	/// <summary>
	/// Summary description for Create.
	/// </summary>
	public class Create : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Button CreateButton;
		private System.Windows.Forms.RichTextBox OutputWindow;
		private System.Windows.Forms.Button EndButton;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Create()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.CreateButton = new System.Windows.Forms.Button();
			this.OutputWindow = new System.Windows.Forms.RichTextBox();
			this.EndButton = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// CreateButton
			// 
			this.CreateButton.Location = new System.Drawing.Point(48, 32);
			this.CreateButton.Name = "CreateButton";
			this.CreateButton.Size = new System.Drawing.Size(208, 23);
			this.CreateButton.TabIndex = 0;
			this.CreateButton.Text = "Create Tables and Stored Procedures";
			this.CreateButton.Click += new System.EventHandler(this.CreateButton_Click);
			// 
			// OutputWindow
			// 
			this.OutputWindow.Location = new System.Drawing.Point(48, 64);
			this.OutputWindow.Name = "OutputWindow";
			this.OutputWindow.ReadOnly = true;
			this.OutputWindow.Size = new System.Drawing.Size(208, 96);
			this.OutputWindow.TabIndex = 1;
			this.OutputWindow.Text = "";
			this.OutputWindow.TextChanged += new System.EventHandler(this.OutputWindow_TextChanged);
			// 
			// EndButton
			// 
			this.EndButton.Location = new System.Drawing.Point(104, 168);
			this.EndButton.Name = "EndButton";
			this.EndButton.TabIndex = 2;
			this.EndButton.Text = "Done";
			this.EndButton.Click += new System.EventHandler(this.EndButton_Click);
			// 
			// Create
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(292, 214);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																		  this.EndButton,
																		  this.OutputWindow,
																		  this.CreateButton});
			this.Name = "Create";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Create";
			this.Load += new System.EventHandler(this.Create_Load);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Create());
		}

		/// <summary>
		/// Writes out all the information during 
		/// CREATE onto the OutputWindow text box.
		/// </summary>
		/// <param name="writer"></param>
		private void flush (StringWriter writer)
		{
			OutputWindow.AppendText (writer.ToString ());
			OutputWindow.Refresh();
			writer.GetStringBuilder ().Length = 0;
		}

		private void CreateButton_Click(object sender, System.EventArgs e)
		{
			// Clear the Output Window
			OutputWindow.Clear();

			// StringWriter to write all the CREATE info
			StringWriter writer = new StringWriter();

			// SQLServer Command
            PsqlCommand DBCmd;
			// Change the connection string to match y  our servers
            PsqlConnection Conn = new PsqlConnection("Host=localhost;Port=1583;Database=demodata;");

			try 
			{
				// Open the connection
				Conn.Open();
			}
			catch (PsqlException ex)
			{
				// Connection failed
				writer.WriteLine(ex.Message);
				flush(writer);
				return;
			}

			// Drop tables, if they already exist.
			string[] DropTableSQL = {"drop table myEmp", "drop table myDept", "drop table myLongdbtypes"};
			for (int x=0; x<3; x++)
			{
				try
				{
					// Drop the tables, don't care if they don't exist
					DBCmd = new PsqlCommand(DropTableSQL[x], Conn);
					DBCmd.ExecuteNonQuery();
				}
				catch 
				{
					// Error will be thrown if the table does not exist. Ingnore it.
				}
			}

			// Create the tables
			string CreateEmpTableSQL = "CREATE TABLE myEmp (empno INT PRIMARY KEY NOT NULL,"
				+"ename    VARCHAR(10) NOT NULL,"
				+"job      VARCHAR(9) NOT NULL,"
				+"mgr      INT,"
				+"hiredate DATETIME NOT NULL,"
				+"sal      NUMERIC(7,2) NOT NULL,"
				+"comm     NUMERIC(7,2),"
				+"dept     INT NOT NULL)";

			string CreateDeptTableSQL = "CREATE TABLE myDept ("
				+"deptno INT NOT NULL,"
				+"dname  VARCHAR(14),"
				+"loc    VARCHAR(13))";

            string LongDataTypesTableSQL = "CREATE TABLE myLongdbtypes ("
                + "id INT NOT NULL, "
                + "longvarcharCol NLONGVARCHAR)";
                //+ "longvarbinayCol LONGVARBINARY)";
			try 
			{
                DBCmd = new PsqlCommand(CreateEmpTableSQL, Conn);
				DBCmd.ExecuteNonQuery();
				writer.WriteLine ("Table myEmp created Successfully!");
				flush(writer);
				DBCmd.CommandText = CreateDeptTableSQL;
				DBCmd.ExecuteNonQuery();
				writer.WriteLine ("Table myDept created Successfully!");
				flush(writer);
				DBCmd.CommandText = LongDataTypesTableSQL;
				DBCmd.ExecuteNonQuery();
				writer.WriteLine ("Table myLongdbtypes created Successfully!");
				flush(writer);
			}
			catch (Exception ex)
			{
				// Create tables failed
				writer.WriteLine (ex.Message);
				flush(writer);
			}

			// Now insert the records
			string[] InsertEmpRecordsSQL = {
											   "insert into myEmp values (1,'JOHNSON','ADMIN',6,{d'1990-12-17'},18000,NULL,4)",
											   "insert into myEmp values (2,'HARDING','MANAGER',9,{d'1998-02-02'},52000,300,3)",
											   "insert into myEmp values (3,'TAFT','SALES I',2,{d'1996-02-01'},25000,500,3)",
											   "insert into myEmp values (4,'HOOVER','SALES I',2,{d'1990-04-02'},27000,NULL,3)",
											   "insert into myEmp values (5,'LINCOLN','TECH',6,{d'1994-06-23'},22500,1400,4)",
											   "insert into myEmp values (6,'GARFIELD','MANAGER',9,{d'1993-05-01'},54000,NULL,4)",
											   "insert into myEmp values (7,'POLK','TECH',6,{d'1997-09-22'},25000,NULL,4)",
											   "insert into myEmp values (8,'GRANT','ENGINEER',10,{d'1997-03-30'},32000,NULL,2)",
											   "insert into myEmp values (9,'JACKSON','CEO',NULL,{d'1990-01-01'},75000,NULL,4)",
											   "insert into myEmp values (10,'FILLMORE','MANAGER',9,{d'1994-08-09'},56000,NULL,2)",
											   "insert into myEmp values (11,'ADAMS','ENGINEER',10,{d'1996-03-15'},34000,NULL,2)",
											   "insert into myEmp values (12,'WASHINGTON','ADMIN',6,{d'1998-04-16'},18000,NULL,4)",
											   "insert into myEmp values (13,'MONROE','ENGINEER',10,{d'2000-12-03'},30000,NULL,2)",
											   "insert into myEmp values (14,'ROOSEVELT','CPA',9,{d'1995-10-12'},35000,NULL,1)"};

			// Insert myDept table records first
			// Perform bulk insert
			DBCmd = new PsqlCommand("INSERT INTO MYDEPT (DEPTNO, DNAME, LOC) VALUES (?, ?, ?)", Conn);
			DBCmd.ArrayBindCount = 4;
			PsqlParameter param1 = new PsqlParameter();
			param1 = DBCmd.Parameters.Add("@p1", PsqlDbType.Integer);
			int[] param1Array = new int[] {1,2,3,4};
			param1.Value = param1Array;

			PsqlParameter param2 = new PsqlParameter();
			param2 = DBCmd.Parameters.Add("@p2", PsqlDbType.VarChar);
			string[] param2Array = new string[] {"ACCOUNTING","RESEARCH","SALES","OPERATION"};
			param2.Value = param2Array;

			PsqlParameter param3 = new PsqlParameter();
			param3 = DBCmd.Parameters.Add("@p3", PsqlDbType.VarChar);
			string[] param3Array = new string[] {"ST LOUIS","NEW YORK","ATLANTA","SEATTLE"};
			param3.Value = param3Array;

			try
			{
				DBCmd.ExecuteNonQuery();
				writer.WriteLine("BULK Insert Successful.");
				flush(writer);
			}
			catch (Exception exc)
			{
				// Insert failed.
				writer.WriteLine(exc.Message);
				flush(writer);
			}

			// Now the myEmp table records
			for (int x = 0; x<InsertEmpRecordsSQL.Length; x++)
			{
				try 
				{
					DBCmd = new PsqlCommand(InsertEmpRecordsSQL[x], Conn);
					DBCmd.ExecuteNonQuery();
					writer.WriteLine ("Row " + (x+1) + " Insert Successful!");
					flush(writer);
				}
				catch (Exception ex) 
				{
					// Insert failed
					writer.WriteLine (ex.Message);
					flush(writer);
				}
			}

			// No data to be inserted in table myLongDBTypes. This table is intended to be kept empty.

			string dropSP = "DROP PROCEDURE GetEmpSalary";
			string dropMRSSP = "DROP PROCEDURE MultipleResultSet";
            string spCreate = "CREATE PROCEDURE GetEmpSalary(in :empno integer,out :sal numeric(7,2)) AS BEGIN SELECT sal into :sal from myEmp where empno = :empno;end;";
            string MRSSPCreate = "CREATE PROCEDURE MultipleResultSet(:dept INTEGER) AS SELECT sal from myEmp WHERE dept = :dept; SELECT dname from myDept WHERE deptno = :dept;";

			try
			{
                DBCmd = new PsqlCommand(dropSP, Conn);
				DBCmd.ExecuteNonQuery();
                DBCmd = new PsqlCommand(dropMRSSP, Conn);
				DBCmd.ExecuteNonQuery();
			}
			catch
			{
				// Drop failed. Ignore the exception since the procedure wasn't there
			}
			try
			{
				DBCmd=new PsqlCommand(spCreate, Conn);
				DBCmd.ExecuteNonQuery();
				writer.WriteLine ("Procedure GetEmpSalary Created Successfully!");
				flush(writer);
                DBCmd = new PsqlCommand(MRSSPCreate, Conn);
				DBCmd.ExecuteNonQuery();
				writer.WriteLine ("Procedure MultipleResultSet Created Successfully!");
				flush(writer);
			}
			catch (Exception ex)
			{
				// Create procedure failed
				writer.WriteLine (ex.Message);
				flush(writer);
			}

			// Close the connection
			Conn.Close();
		}

		private void OutputWindow_TextChanged(object sender, System.EventArgs e)
		{
		
		}

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

		private void Create_Load(object sender, System.EventArgs e)
		{
		
		}
	}
}
