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 StoredProcs
{
	/// <summary>
	/// Summary description for StoredProcedures.
	/// </summary>
	public class StoredProcedures : System.Windows.Forms.Form
	{
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Button Start;
		private System.Windows.Forms.Button Done;
		private System.Windows.Forms.RichTextBox ResultSet;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public StoredProcedures()
		{
			//
			// 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.label1 = new System.Windows.Forms.Label();
            this.Start = new System.Windows.Forms.Button();
            this.Done = new System.Windows.Forms.Button();
            this.ResultSet = new System.Windows.Forms.RichTextBox();
            this.SuspendLayout();
            // 
            // label1
            // 
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, ((System.Drawing.FontStyle)((System.Drawing.FontStyle.Bold | System.Drawing.FontStyle.Underline))), System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.Location = new System.Drawing.Point(104, 16);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(200, 23);
            this.label1.TabIndex = 0;
            this.label1.Text = "StoredProcedure Demo";
            this.label1.Click += new System.EventHandler(this.label1_Click);
            // 
            // Start
            // 
            this.Start.Location = new System.Drawing.Point(157, 75);
            this.Start.Name = "Start";
            this.Start.Size = new System.Drawing.Size(75, 23);
            this.Start.TabIndex = 1;
            this.Start.Text = "Start";
            this.Start.Click += new System.EventHandler(this.Start_Click);
            // 
            // Done
            // 
            this.Done.Location = new System.Drawing.Point(157, 310);
            this.Done.Name = "Done";
            this.Done.Size = new System.Drawing.Size(75, 23);
            this.Done.TabIndex = 2;
            this.Done.Text = "Done";
            this.Done.Click += new System.EventHandler(this.Done_Click);
            // 
            // ResultSet
            // 
            this.ResultSet.Location = new System.Drawing.Point(16, 104);
            this.ResultSet.Name = "ResultSet";
            this.ResultSet.ReadOnly = true;
            this.ResultSet.Size = new System.Drawing.Size(384, 200);
            this.ResultSet.TabIndex = 3;
            this.ResultSet.Text = "";
            this.ResultSet.TextChanged += new System.EventHandler(this.ResultSet_TextChanged);
            // 
            // StoredProcedures
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(408, 342);
            this.Controls.Add(this.ResultSet);
            this.Controls.Add(this.Done);
            this.Controls.Add(this.Start);
            this.Controls.Add(this.label1);
            this.Name = "StoredProcedures";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "StoredProcedures";
            this.Load += new System.EventHandler(this.StoredProcedures_Load);
            this.ResumeLayout(false);

		}
		#endregion

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

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

		private void Start_Click(object sender, System.EventArgs e)
		{
			StringWriter writer = new StringWriter();
			// Change the connection string to match your 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);
				return;
			}

			// DataSet
			DataSet ds = new DataSet("Demo");
			// PsqlDataAdapter
			PsqlDataAdapter dataAdapter = new PsqlDataAdapter();
			// PsqlCommand
			PsqlCommand DBCmd;

			// Make a command object for the stored procedure
			// You must set the CommandType of the Command object to StoredProcedure 
			for (int i = 0; i < 2; i++)
			{
				if ( i == 0 )
				{
					// Calling stored procedure as defined by the .NET specs
					writer.WriteLine("Procedure called using the .NET syntax");
					DBCmd = new PsqlCommand("GetEmpSalary", Conn);
					DBCmd.CommandType = CommandType.StoredProcedure;
				}
				else
				{
					// Calling stored procudure using escape syntax
					writer.WriteLine("Procedure called using escape syntax");
					DBCmd = new PsqlCommand("{call GetEmpSalary (?,?)}", Conn);
					DBCmd.CommandType = CommandType.StoredProcedure;
				}

				// The stored procedure expects one input and one output parameter
				// Define the parameters for the stored procedure
				// We don't need to specify the direction of the parameter, since the default is INPUT
				DBCmd.Parameters.Add("@empno", PsqlDbType.Integer, 10).Value = 5;
				// Output parameter
				DBCmd.Parameters.Add("@sal", PsqlDbType.Numeric, 10).Direction = ParameterDirection.Output;
				try
				{
					dataAdapter.SelectCommand = DBCmd;
					dataAdapter.Fill(ds);
					writer.WriteLine("Procedure Executed Successfully!");
					writer.WriteLine("Output Parameter Value Returned (Salary of employee 5): " + DBCmd.Parameters["@sal"].Value.ToString());
				}
				catch (Exception ex) 
				{
					// Display any exceptions 
					writer.WriteLine (ex.Message);
				}
			}

			try
			{
				writer.WriteLine("Calling Stored procedure returning Multiple Result Sets");
				// Call a stored procedure with multiple result set
				DBCmd = new PsqlCommand("{call MultipleResultSet (?)}", Conn);
				// Alternataly, the CommandType of the Command Object can be set to TEXT when
				// calling the stored procedure using escape syntax.
				DBCmd.CommandType = CommandType.Text;
				// Set the input parameter expected by the stored procedure (dept number)
				DBCmd.Parameters.Add("@dept", PsqlDbType.Integer, 10).Value = 2;

				// Make the Command object a SelectCommand for DataAdapter
				dataAdapter.SelectCommand = DBCmd;
				dataAdapter.Fill(ds);
				writer.WriteLine("Procedure Executed Successfully!");
				// Print the result sets
				printDataSet(ds, writer);
			}
			catch (Exception ex)
			{
				// Display the exception
				writer.WriteLine(ex.Message);
			}

			// Dump the contents of the writer to the Result window
			flush(writer);
			// Close the connection
			Conn.Close();
		}

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

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

		// This method call dumps the StringWriter contents on to the ResultSet window
		private void flush (StringWriter writer)
		{
			ResultSet.Clear();
			ResultSet.AppendText (writer.ToString ());
			ResultSet.Refresh();
			writer.GetStringBuilder ().Length = 0;
		}

		// This method call prints the result set in the DataSet onto the ResultSet window
		protected void printDataSet(DataSet ds, StringWriter writer)
		{
			foreach (DataTable table in ds.Tables)
			{
				bool fFirst = true;
				foreach(DataColumn col in table.Columns)
				{
					if (!fFirst) 
					{
						writer.Write(", ");
					}
					fFirst = false;
					writer.Write(col.ColumnName);
				}
				writer.WriteLine ("");
				foreach(DataRow row in table.Rows)
				{
					fFirst = true;
					foreach(DataColumn col in table.Columns)
					{
						if (!fFirst) 
						{
							writer.Write(", ");
						}
						fFirst = false;
						if ( row[col].ToString().Equals(""))
						{
							writer.Write("<null>");
						}
						else
						{
							object obj =row[col];
							string	str = obj.ToString();
							writer.Write(str);
						}
					}
					writer.WriteLine ("");
				}
			}
		}

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