SQL Editor for Database Developers

/*
SqlBuilder - an intelligent database tool
 
This file is part of SqlBuilder.
www.netcult.ch/elmue

This program is free software; you can redistribute it and/or modify it 
under the terms of the GNU General Public License as published by the 
Free Software Foundation; either version 2 of the License, or 
(at your option) any later version. 
 
This program is distributed in the hope that it will be useful, 
but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 
GNU General Public License for more details. 
 
You find the GNU General Public License in the subfolder GNU
if not, write to the Free Software Foundation, 
Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
*/


using System;
using System.IO;
using System.Text;
using System.Data;
using System.Drawing;
using System.Collections;
using System.Diagnostics;
using System.ComponentModel;
using System.Windows.Forms;
using SqlBuilder.Controls;

using FindCallback = SqlBuilder.Forms.frmFind.delFind;

namespace SqlBuilder.Forms
{
	public class frmDataGrid : frmBaseForm
	{
		string       ms_QueryHtml;
		DataSet      mi_DataSet;
		string       ms_ExecuteTime;
		ArrayList    mi_ScrollPos   = new ArrayList();
		bool         mb_Reload      = true;
		string       ms_LastFind    = "";

		public frmDataGrid(string s_QueryHtml, DataSet i_DataSet, string s_ExecuteTime)
		{
			InitializeComponent();
			
			StoreWindowPos = true; // must be set in Constructor AFTER InitializeComponent() !!
			ms_QueryHtml   = s_QueryHtml;
			mi_DataSet     = i_DataSet;
			mi_DataSet.DataSetName = "Query Results";
			ms_ExecuteTime = s_ExecuteTime;

			dataGrid.ReadOnly = true;
			dataGrid.SelectionBackColor = Color.MediumAquamarine;
		}

		protected override void OnLoad(EventArgs e)
		{
			base.OnLoad (e);

			for (int T=0; T<mi_DataSet.Tables.Count; T++)
			{
				mi_ScrollPos.Add(new Point(0,0));

				DataTable i_Table = mi_DataSet.Tables[T];

				string s_Name = string.Format("Table {0}", T+1);
				i_Table.TableName = s_Name;
				comboTables.Items.Add(s_Name);

				object o_Value;
				if (SQL.IsTableScalarOrEmpty(i_Table, out o_Value) && 
					i_Table.Columns[0].ColumnName == "Column1")
					i_Table.Columns[0].ColumnName  = "Scalar Value";
			}
			comboTables.SelectedIndex = 0;
			
			if (mi_DataSet.Tables.Count == 1)
				comboTables.Enabled = false;
		}

		protected override void OnClosed(EventArgs e)
		{
			dataGrid.DataSource = null; // IMPORTANT free memory !!
			mi_DataSet.Clear();         // IMPORTANT free memory !!
			mi_DataSet = null;          // IMPORTANT free memory !!
			base.OnClosed (e);
		}

		private void OnComboTablesSelectedIndexChanged(object sender, EventArgs e)
		{
			mb_Reload = true;
			dataGrid.DataBindings.Clear();
			dataGrid.TableStyles. Clear();
			dataGrid.DataSource = null;

			DataGridTableStyle i_Style = new DataGridTableStyle();
			dataGrid.TableStyles.Add(i_Style);

			DataTable i_Table   = mi_DataSet.Tables[comboTables.SelectedIndex];
			i_Style.MappingName = i_Table.TableName;

			foreach (DataColumn i_Col in i_Table.Columns)
			{
				string s_Type = Functions.CutBeginAt(i_Col.DataType.FullName, "System.");

				DataGridColumnStyle i_ColStyle;
				if (s_Type == "Boolean")
					i_ColStyle = new DataGridBoolColumn();
				else
					i_ColStyle = new CustomTextColumn(i_Col, dataGrid);

				// "ColumnName (Int32)"
				i_ColStyle.HeaderText  = string.Format("{0} ({1})", i_Col.Caption, s_Type);
				i_ColStyle.MappingName = i_Col.ColumnName;
				i_ColStyle.NullText    = Defaults.NullText; // also for checkboxes

				i_Style.GridColumnStyles.Add(i_ColStyle);
			}

			dataGrid.DataSource = i_Table;

			// restore the scroll position after changing the table
			Point Pos = (Point) mi_ScrollPos[comboTables.SelectedIndex];
			dataGrid.HorScrollPos  = Pos.X;
			dataGrid.VertScrollPos = Pos.Y;

			lblTableInfo.Text = string.Format("{0} columns,  {1} rows,  Total SQL Execution time: {2} seconds", 
			                                  i_Table.Columns.Count, i_Table.Rows.Count, ms_ExecuteTime);
			mb_Reload = false;
		}

		private void OnDataGridScroll(object sender, EventArgs e)
		{
			if (!mb_Reload)
				mi_ScrollPos[comboTables.SelectedIndex] = new Point(dataGrid.HorScrollPos, dataGrid.VertScrollPos);
		}

		private void OnButtonExcel(object sender, System.EventArgs e)
		{
			this.Cursor = Cursors.WaitCursor;
			StringBuilder s_Excel = new StringBuilder(50000);
			
			s_Excel.Append("<html><body>\r\n");
			for (int T=0; T<mi_DataSet.Tables.Count; T++)
			{
				TableToHtml(s_Excel, mi_DataSet.Tables[T], true);
			}
			s_Excel.Append("</body></html>");

			this.Cursor = Cursors.Arrow;		
			string s_File = Functions.Terminate(Path.GetTempPath()) + "QueryResult.xls";
			Functions.SaveAndOpenFile(this, s_File, s_Excel.ToString(), Encoding.UTF8, ProcessWindowStyle.Maximized);
		}

		private void OnButtonHtml(object sender, System.EventArgs e)
		{
			this.Cursor = Cursors.WaitCursor;
			StringBuilder s_Html = new StringBuilder(50000);

			s_Html.Append("<html>\r\n<head>\r\n<title>SqlBuilder Html Export</title>\r\n");
			s_Html.Append("<style>div {font-size:13px; font-family:Arial; font-weight:bold;}</style>\r\n</head>\r\n<body>\r\n");
			s_Html.Append(ms_QueryHtml);
			s_Html.Append("\r\n<p><hr size=3 color=#AAAAAA><p>\r\n");

			for (int T=0; T<mi_DataSet.Tables.Count; T++)
			{
				TableToHtml(s_Html, mi_DataSet.Tables[T], false);
				s_Html.Append("<p>&nbsp;<p>\r\n");
			}
			s_Html.Append("</body></html>");

			this.Cursor = Cursors.Arrow;
			string s_File = Functions.Terminate(Path.GetTempPath()) + "QueryResult.htm";
			Functions.SaveAndOpenFile(this, s_File, s_Html.ToString(), Encoding.UTF8, ProcessWindowStyle.Maximized);
		}

		private void TableToHtml(StringBuilder s_Data, DataTable i_Table, bool b_ForExcel)
		{
			if (b_ForExcel)
				s_Data.AppendFormat("<table><tr><td><font size=4 color=blue>{0}</font> &nbsp; {1} rows, {2} columns</td></tr></table>", i_Table.TableName, i_Table.Rows.Count, i_Table.Columns.Count);
			else
				s_Data.AppendFormat("<font size=5 color=blue><b>{0}</b></font> &nbsp; {1} rows, {2} columns<p>\r\n", i_Table.TableName, i_Table.Rows.Count, i_Table.Columns.Count);

			s_Data.Append("<table cellspacing=0 cellpadding=3 bordercolor=black border=1 style='background-color:white; border-color:black; border-width:1px; border-style:solid; border-collapse:collapse; font-family:Arial; font-size:11px;'>\r\n");
			
			string[] s_Colors = { "#FFFFFF", "#EEEEEE" };

			// Rows
			for (int R=0; R<i_Table.Rows.Count; R++)
			{
				// print header in HTML every 25 lines
				int Rep = Defaults.HtmlHeaderRepeat;
				if (R==0 || (Rep > 0 && (R%Rep) == 0 && !b_ForExcel))
				{
					s_Data.Append("<tr bgcolor=#E0E090>");
					foreach (DataColumn i_Col in i_Table.Columns)
					{
						string s_Head = string.Format("{0} ({1})", i_Col.ColumnName, Functions.CutBeginAt(i_Col.DataType.FullName, "System."));
						s_Data.Append("<th>");
						s_Data.Append(Functions.ReplaceHtml(s_Head));
						s_Data.Append("</th>");
					}
					s_Data.Append("</tr>\r\n");
				}

				DataRow i_Row = i_Table.Rows[R];

				if (b_ForExcel) 
					s_Data.Append("<tr valign=top>");
				else
					s_Data.AppendFormat("<tr valign=top bgcolor={0}>", s_Colors[R%2]);

				// Cells
				foreach (object o_Cell in i_Row.ItemArray)
				{
					string s_Color = Functions.GetHtmlColor(Defaults.GridColor(o_Cell.GetType()));
					string s_Text = SQL.DbObjectToString(o_Cell, true);
					s_Data.AppendFormat("<td><font color={0}>{1}</font></td>", s_Color, s_Text.Replace("  ", "&nbsp; "));
				}
				s_Data.Append("</tr>\r\n");
			}

			s_Data.Append("</table>\r\n");
		}

		private void OnButtonCompare(object sender, System.EventArgs e)
		{
			string s_Info = "\nTo compare two tables you must execute two SQL commands at once which return two identical tables. Then you can compare the contents of the tables to check if both queries return the same data.";
			if (mi_DataSet.Tables.Count != 2)
			{
				frmMsgBox.Err(this, string.Format("The query result contains {0} tables.", mi_DataSet.Tables.Count) + s_Info);
				comboTables.Focus();
				return;
			}

			DataColumnCollection Cols1 = mi_DataSet.Tables[0].Columns;
			DataColumnCollection Cols2 = mi_DataSet.Tables[1].Columns;
			DataRowCollection    Rows1 = mi_DataSet.Tables[0].Rows;
			DataRowCollection    Rows2 = mi_DataSet.Tables[1].Rows;

			if (Cols1.Count != Cols2.Count)
			{
				frmMsgBox.Err(this, "The two tables don't have the same count of columns." + s_Info);
				comboTables.Focus();
				return;
			}

			for (int C=0; C<Cols1.Count; C++)
			{
				if (Cols1[C].DataType != Cols2[C].DataType)
				{
					frmMsgBox.Err(this, "The two tables don't have identical datatypes for each column." + s_Info);
					comboTables.Focus();
					return;
				}
			}

			if (Rows1.Count == 0 || Rows2.Count == 0)
			{
				frmMsgBox.Err(this, "You cannot compare an empty table!");
				comboTables.Focus();
				return;
			}

			// Calculate the MD5 for the content of all cells in each row and store them in an arraylist
			ArrayList[] i_Hash = new ArrayList[2];
			i_Hash[0] = SQL.CalculateTableHash(mi_DataSet.Tables[0]);
			i_Hash[1] = SQL.CalculateTableHash(mi_DataSet.Tables[1]);

			int s32_Diff, s32_Equal;
			SQL.CompareTableHashes(i_Hash[0], i_Hash[1], out s32_Diff, out s32_Equal);

			if (s32_Diff == 0)
			{
				frmMsgBox.Info(this, "The contents of the two tables are exactly identical.");
				comboTables.Focus();
				return;
			}
			if (s32_Equal == 0)
			{
				frmMsgBox.Err(this, "There is not even one row in the two tables which is identical.");
				comboTables.Focus();
				return;
			}

			bool b_DelDiff = false;
			if (frmMsgBox.Ask(this, string.Format("There are {0} rows which are different in the two tables.\nDo you want to delete all the identical rows to see only the different rows?", s32_Diff)))
                b_DelDiff = true;
			else if (!frmMsgBox.Ask(this, string.Format("There are {0} rows which are identical in the two tables.\nDo you want to delete all the different rows to see only the identical rows?", s32_Equal)))
				return;

			Cursor = Cursors.WaitCursor;
			for (int T=0; T<2; T++)
			{
				// delete in !! REVERSE !! order
				for (int R=i_Hash[T].Count-1; R>=0; R--)
				{
					string s_RowHash = (string)i_Hash[T][R];
					if (i_Hash[1-T].Contains(s_RowHash) == b_DelDiff)
					{
						mi_DataSet.Tables[T].Rows.RemoveAt(R);
					}
				}
			}

			OnComboTablesSelectedIndexChanged(null, null); // load into datagrid
			comboTables.Focus();
			Cursor = Cursors.Arrow;
		}

		private void OnButtonFind(object sender, System.EventArgs e)
		{
			Find(true, true);
		}

		/// <summary>
		/// Callback called from frmFind on button "Find"
		/// </summary>
		private string OnFindText(string s_Find)
		{
			ms_LastFind = s_Find;
			return Find(false, true);
		}

		string Find(bool b_OpenForm, bool b_Foreward)
		{
			if (b_OpenForm || ms_LastFind.Length == 0)
			{
				frmFind i_Find = new frmFind(0, ms_LastFind, "", new FindCallback(OnFindText), null);
				i_Find.ShowDialog();
				return "";
			}

			DataTable i_Table = (DataTable)dataGrid.DataSource;

			int StartCol = dataGrid.CurrentCell.ColumnNumber;
			int StartRow = dataGrid.CurrentCell.RowNumber;
			CalcStartPos(i_Table, b_Foreward, ref StartCol, ref StartRow);

			if (b_Foreward)
			{
				for (int Loop = 0; Loop<2; Loop ++)
				{
					for (int R=StartRow; R<i_Table.Rows.Count; R++)
					{
						for (int C=StartCol; C<i_Table.Columns.Count; C++)
						{
							string s_Value = SQL.DbObjectToString(dataGrid[R,C], false);
							if (Functions.IndexOf(s_Value, ms_LastFind, 0) >= 0)
							{
								dataGrid.CurrentCell = new DataGridCell(R,C);
								return "";
							}
						}
						StartCol = 0;
					}
					StartRow = 0;
				}
			}
			else // Search reverse
			{
				for (int Loop = 0; Loop<2; Loop ++)
				{
					for (int R=StartRow; R>=0; R--)
					{
						for (int C=StartCol; C>=0; C--)
						{
							string s_Value = SQL.DbObjectToString(dataGrid[R,C], false);
							if (Functions.IndexOf(s_Value, ms_LastFind, 0) >= 0)
							{
								dataGrid.CurrentCell = new DataGridCell(R,C);
								return "";
							}
						}
						StartCol = i_Table.Columns.Count-1;
					}
					StartRow = i_Table.Rows.Count-1;
				}
			}

			lblStatus.SetTransientText("Not found!");
			return "Not found!";
		}

		private void CalcStartPos(DataTable i_Table, bool b_Foreward, ref int Col, ref int Row)
		{
			Col += (b_Foreward) ? 1 : -1;

			if (Col >= i_Table.Columns.Count)
			{
				Col = 0;
				Row ++;
				if (Row >= i_Table.Rows.Count)
				{
					Col = 0;
					Row = 0;
				}
			}
			if (Col < 0)
			{
				Col = i_Table.Columns.Count-1;
				Row --;
				if (Row < 0)
				{
					Col = i_Table.Columns.Count-1;
					Row = i_Table.Rows.Count-1;
				}
			}
		}

		/// <summary>
		/// Receives also forewarded key events from the TextBox when in edit mode
		/// </summary>
		private void OnDataGridKeyDown(object sender, KeyEventArgs e)
		{
			if (e.Control && !e.Shift && !e.Alt)
			{
				switch(e.KeyCode)
				{
					case Keys.F:
						e.Handled = true;
						Find(true, true);
						return;
				}
			}

			if (!e.Control && !e.Shift && !e.Alt)
			{
				switch(e.KeyCode)
				{
					case Keys.F3:
						e.Handled = true;
						Find(false, true);
						return;
						
					case Keys.F4:
						e.Handled = true;
						Find(false, false);
						return;
				}
			}
		}

		private void OnDataGridCurrentCellChanged(object sender, EventArgs e)
		{
			lblStatus.SetTransientText(string.Format("Row {0}  Column {1}", dataGrid.CurrentCell.RowNumber+1, dataGrid.CurrentCell.ColumnNumber+1));
		}

		#region Windows Form Designer generated code

		private SqlBuilder.Controls.DataGridEx dataGrid;
		private SqlBuilder.Controls.StatusInfo  lblStatus;
		private System.Windows.Forms.Button btnHtml;
		private System.Windows.Forms.Button btnExcel;
		private System.Windows.Forms.ComboBox comboTables;
		private System.Windows.Forms.Label lblTableInfo;
		private System.Windows.Forms.Button btnCompare;
		private System.Windows.Forms.Button btnFind;
		private System.ComponentModel.Container components = null;

		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(frmDataGrid));
			this.lblStatus = new StatusInfo();
			this.dataGrid = new SqlBuilder.Controls.DataGridEx();
			this.btnHtml = new System.Windows.Forms.Button();
			this.btnExcel = new System.Windows.Forms.Button();
			this.comboTables = new System.Windows.Forms.ComboBox();
			this.lblTableInfo = new System.Windows.Forms.Label();
			this.btnCompare = new System.Windows.Forms.Button();
			this.btnFind = new System.Windows.Forms.Button();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid)).BeginInit();
			this.SuspendLayout();
			// 
			// dataGrid
			// 
			this.dataGrid.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
				| System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.dataGrid.CaptionText = "Data Table";
			this.dataGrid.CaptionVisible = false;
			this.dataGrid.DataMember = "";
			this.dataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid.HorScrollPos = 0;
			this.dataGrid.Location = new System.Drawing.Point(12, 38);
			this.dataGrid.Name = "dataGrid";
			this.dataGrid.Size = new System.Drawing.Size(870, 508);
			this.dataGrid.TabIndex = 2;
			this.dataGrid.VertScrollPos = 0;
			this.dataGrid.KeyDown += new System.Windows.Forms.KeyEventHandler(this.OnDataGridKeyDown);
			this.dataGrid.Scroll += new System.EventHandler(this.OnDataGridScroll);
			this.dataGrid.CurrentCellChanged += new System.EventHandler(this.OnDataGridCurrentCellChanged);
			// 
			// btnHtml
			// 
			this.btnHtml.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
			this.btnHtml.Location = new System.Drawing.Point(796, 8);
			this.btnHtml.Name = "btnHtml";
			this.btnHtml.Size = new System.Drawing.Size(84, 23);
			this.btnHtml.TabIndex = 5;
			this.btnHtml.Text = "Open as Html";
			this.btnHtml.Click += new System.EventHandler(this.OnButtonHtml);
			// 
			// btnExcel
			// 
			this.btnExcel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
			this.btnExcel.Location = new System.Drawing.Point(704, 8);
			this.btnExcel.Name = "btnExcel";
			this.btnExcel.Size = new System.Drawing.Size(86, 23);
			this.btnExcel.TabIndex = 4;
			this.btnExcel.Text = "Open as Excel";
			this.btnExcel.Click += new System.EventHandler(this.OnButtonExcel);
			// 
			// comboTables
			// 
			this.comboTables.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.comboTables.Location = new System.Drawing.Point(14, 8);
			this.comboTables.MaxDropDownItems = 25;
			this.comboTables.Name = "comboTables";
			this.comboTables.Size = new System.Drawing.Size(168, 21);
			this.comboTables.TabIndex = 1;
			this.comboTables.SelectedIndexChanged += new System.EventHandler(this.OnComboTablesSelectedIndexChanged);
			// 
			// lblTableInfo
			// 
			this.lblTableInfo.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.lblTableInfo.Location = new System.Drawing.Point(194, 10);
			this.lblTableInfo.Name = "lblTableInfo";
			this.lblTableInfo.Size = new System.Drawing.Size(360, 16);
			this.lblTableInfo.TabIndex = 5;
			this.lblTableInfo.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
			// 
			// btnCompare
			// 
			this.btnCompare.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
			this.btnCompare.Location = new System.Drawing.Point(634, 8);
			this.btnCompare.Name = "btnCompare";
			this.btnCompare.Size = new System.Drawing.Size(64, 23);
			this.btnCompare.TabIndex = 3;
			this.btnCompare.Text = "Compare";
			this.btnCompare.Click += new System.EventHandler(this.OnButtonCompare);
			// 
			// btnFind
			// 
			this.btnFind.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
			this.btnFind.Location = new System.Drawing.Point(564, 8);
			this.btnFind.Name = "btnFind";
			this.btnFind.Size = new System.Drawing.Size(64, 23);
			this.btnFind.TabIndex = 6;
			this.btnFind.Text = "Find";
			this.btnFind.Click += new System.EventHandler(this.OnButtonFind);
			// 
			// lblStatus
			// 
			this.lblStatus.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.lblStatus.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D;
			this.lblStatus.Location = new System.Drawing.Point(12, 552);
			this.lblStatus.Name = "lblStatus";
			this.lblStatus.Size = new System.Drawing.Size(870, 20);
			this.lblStatus.TabIndex = 7;
			this.lblStatus.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
			// 
			// frmDataGrid
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(894, 577);
			this.Controls.Add(this.lblStatus);
			this.Controls.Add(this.btnFind);
			this.Controls.Add(this.btnCompare);
			this.Controls.Add(this.lblTableInfo);
			this.Controls.Add(this.comboTables);
			this.Controls.Add(this.btnExcel);
			this.Controls.Add(this.btnHtml);
			this.Controls.Add(this.dataGrid);
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.Name = "frmDataGrid";
			this.SizeGripStyle = System.Windows.Forms.SizeGripStyle.Show;
			this.Text = " SQL Result View";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion
	}
}