SQL Editor for Database Developers

// -------------------------------------------------------
// SqlBuilder by Elm�Soft
// www.netcult.ch/elmue
// www.codeproject.com/KB/database/SqlBuilder.aspx
// -------------------------------------------------------

using System;
using System.Xml;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Threading;
using System.Windows.Forms;
using SqlBuilder.Forms;

using eType  = SqlBuilder.Controls.ListViewEx.eType;

namespace SqlBuilder
{
	/// <summary>
	/// Access to Microsoft SQL server
	/// </summary>
	public class SQL
	{
		string        ms_Server;
		string        ms_DataBase;
		string        ms_User;
		string        ms_Password;
		string        ms_Sql;
		// data for thread
		Form          mi_Owner;
		frmWait       mi_WaitForm;
		Thread        mi_Thread;
		SqlConnection mi_Connect;
		SqlCommand    mi_Command;
		DataSet       mi_DataSet;
		string        ms_ErrorMsg;
		string        ms_ExecTime;
		bool          mb_Aborting;
		int         ms32_FirstLine;

		/// <summary>
		/// Constructor
		/// Set s_User and s_Password = null for a trusted connection
		/// </summary>
		public SQL(Form i_Owner, string s_Server, string s_DataBase, string s_User, string s_Password)
		{
			mi_Owner    = i_Owner;
			ms_Server   = s_Server;
			ms_DataBase = s_DataBase;
			ms_User     = s_User;
			ms_Password = s_Password;
		}

		/// <summary>
		/// returns the SQL execution time in seconds of the last SQL command
		/// </summary>
		public string ExecuteTime
		{
			get { return ms_ExecTime; }
		}

		/// <summary>
		/// returns a DataSet or null on error
		/// This function should be called from a GUI thread
		/// s32_FirstLine = -1 -> don't print line numbers in error messages
		/// otherwise it is added to the linenumber in the error message
		/// </summary>
		public DataSet ExecuteSQL(string s_Sql, int s32_FirstLine)
		{
			if (mi_Thread != null)
				// Due to heavy bugs in .NET framework you cannot abort a thread without problems
				// So if you want a Cancel functionality you must create a new thread each time
				// and let it run until it terminates alone !!!!
				// So for each thread a new SQL class instance is required
				throw new Exception("Application design error: Create a new instance of the SQL class for each query!");

			if (ms_User != null && ms_User.Length == 0) { frmMsgBox.Err(mi_Owner, "You must specify a user!"); return null; }

			if (ms_Server.   Length == 0) { frmMsgBox.Err(mi_Owner, "You must specify a SQL server!"); return null; }
			if (ms_DataBase. Length == 0) { frmMsgBox.Err(mi_Owner, "You must specify a database!");   return null; }
			if (s_Sql.Trim().Length == 0) { frmMsgBox.Err(mi_Owner, "No SQL code specified!");         return null; }

			ms_Sql         = s_Sql;
			mi_DataSet     = null;
			ms_ErrorMsg    = null;
			mb_Aborting    = false;
			ms32_FirstLine = s32_FirstLine;
			ms_ExecTime    = "0.000";

			mi_WaitForm = new frmWait(); // ORDER FIRST!
			mi_WaitForm.evUserAbort += new frmWait.UserAbort(OnUserAbort);

			mi_Thread = new Thread(new ThreadStart(WorkThread));
			mi_Thread.Start(); // ORDER AFTER!

			// ShowDialog() blocks until the thread or the user closes the frmWait window
			mi_WaitForm.ShowDialog(mi_Owner);

			if (ms_ErrorMsg != null && !mb_Aborting)
				frmMsgBox.Err(mi_Owner, ms_ErrorMsg);

			DataSet i_Set = mi_DataSet;  // this avoids a memory leak !

			mi_Connect  = null; // this avoids a memory leak !
			mi_Command  = null; // this avoids a memory leak !
			mi_DataSet  = null; // this avoids a memory leak !
			mi_WaitForm = null;

			return i_Set;
		}

		void WorkThread()
		{
			try
			{
				// SetLabelText is threadsafe
				mi_WaitForm.SetLabelText("Connecting server...");

				// ####################################################################################
				// To see how to build a connection string look at: http://www.connectionstrings.com !
				// ####################################################################################

				string s_Connect = String.Format("Server={0}; Database={1}; ", ms_Server, ms_DataBase);

				if (ms_User == null && ms_Password == null)
					s_Connect += "Trusted_Connection=yes;";
				else
					s_Connect += String.Format("User ID={0}; Password={1}; Trusted_Connection=no;", ms_User, ms_Password);

				mi_Connect = new SqlConnection(s_Connect);
				mi_Connect.Open();

				if (mb_Aborting)
					goto _Exit; // User has canceled meanwhile
			}
			catch (Exception Ex)
			{
				mi_Connect  = null;
				ms_ErrorMsg = string.Format("Error while connecting server {0}.\n{1}", ms_Server, Ex.Message);
				goto _Exit;
			}

			int s32_FirstGoLine = 0;
			try
			{
				// SetLabelText is threadsafe
				mi_WaitForm.SetLabelText("Execute SQL...");

				DataSet i_DataSet = null;
				StringBuilder s_Cmd = new StringBuilder(50000);

				Measure i_Measure = new Measure();

				string[] s_Lines = ms_Sql.Replace("\r", "").Split('\n');
				for (int L=0; L<s_Lines.Length; L++)
				{
					bool b_GO = s_Lines[L].ToUpper().StartsWith("GO");
					// Check that it is not "GOTO"
					if (s_Lines[L].Length > 2 && s_Lines[L][2] != ' ')
						b_GO = false;

					if (!b_GO)
					{
						s_Cmd.Append(s_Lines[L]);
						s_Cmd.Append("\n");
					}
					
					if (b_GO || L == s_Lines.Length-1) // last line -> always execute
					{
						if (s_Cmd.Length > 0)
						{
							mi_Command = new SqlCommand(s_Cmd.ToString(), mi_Connect);
							mi_Command.CommandTimeout = Defaults.Timeout;
							SqlDataAdapter i_Adapter  = new SqlDataAdapter(mi_Command);
							i_DataSet = new DataSet();
							i_Adapter.Fill(i_DataSet); // here the SQL code is sent to the server
							s_Cmd.Length = 0;
						}
						s32_FirstGoLine = L+1; // store for the NEXT command
					}
				}

				ms_ExecTime = i_Measure.ElapsedTimeStr;
				mi_DataSet  = i_DataSet;
			}
			catch (Exception Ex)
			{
				ms_ErrorMsg = ParseErrorMessage(Ex, ms32_FirstLine + s32_FirstGoLine);
				mi_DataSet  = null;
			}

			mi_Command = null;
			try { mi_Connect.Close(); }
			catch {} // catch bugs in .NET framework

			_Exit:

			// Close() is threadsafe
			if (!mb_Aborting)
				mi_WaitForm.Close();
		}

		/// <summary>
		/// User clicked the Abort button in mi_WaitForm
		/// </summary>
		private void OnUserAbort()
		{
			if (mb_Aborting) // block multiple clicks on button "Abort"
				return;

			mb_Aborting = true;
			mi_WaitForm.SetLabelText("Aborting...");

			if (mi_Command != null)
			{
				mi_Command.Cancel();
				Thread.Sleep(500);
			}

			if (mi_Connect != null)
			{
				try { mi_Connect.Close(); }
				catch {} // Bugs in .NET framework !
			}

			// DO NOT ABORT THE THREAD HERE! (mi_Thread.Abort())
			// Due to a bad design in .NET framework this would cause multiple problems
		}

		/// <summary>
		/// returns a DataTable or null on error
		/// This function should be called from a GUI thread
		/// </summary>
		public DataTable ReadTable(string s_SQL, int s32_FirstLine)
		{
			DataSet i_DataSet = ExecuteSQL(s_SQL, s32_FirstLine);
			if (i_DataSet == null)
				return null;

			if (i_DataSet.Tables.Count != 1)
			{
				frmMsgBox.Err(mi_Owner, string.Format("The SQL command returned a recordset with {0} instead of 1 tables!", i_DataSet.Tables.Count));
				return null;
			}

			return i_DataSet.Tables[0];
		}

		/// <summary>
		/// returns null on error or if the query did not return any result!
		/// returns DBNull if a NULL in the database has been read!
		/// This function should be called from a GUI thread
		/// </summary>
		public object ReadScalar(string s_SQL, int s32_FirstLine)
		{
			DataTable i_Table = ReadTable(s_SQL, s32_FirstLine);
			if (i_Table == null)
				return null;

			object o_Value;
			if (!SqlTable.IsTableScalarOrEmpty(i_Table, out o_Value))
				frmMsgBox.Err(mi_Owner, string.Format("The SQL command returned a table with {0} rows and {1} columns instead of one scalar value.", i_Table.Rows.Count, i_Table.Columns.Count));

			i_Table.Clear();
			return o_Value;
		}

		/// <summary>
		/// returns a list of all databases or null on error
		/// </summary>
		public string[] ListAllDataBases()
		{
			ms_DataBase = "master";
			DataTable i_Table = ReadTable("SELECT name FROM sysdatabases ORDER BY name", -1);
			if (i_Table == null)
				return null;

			string[] s_Bases = new string[i_Table.Rows.Count];
			for (int R=0; R<i_Table.Rows.Count; R++)
			{
				s_Bases[R] = (string) i_Table.Rows[R]["name"];
			}
			i_Table.Clear();
			return s_Bases;
		}

		/// <summary>
		/// returns a list of all functions, procedures etc.. in a database
		/// returns null on error
		/// </summary>
		public string[] ListAllSysObjects(eType e_Type)
		{
			string  s_SQL = "SELECT name FROM sysobjects WHERE ";
			switch (e_Type)
			{
				case eType.PROCEDURE: s_SQL += "xtype='P'  OR xtype='X'"; break;
				case eType.FUNCTION:  s_SQL += "xtype='IF' OR xtype='FN' OR xtype='TF'"; break;
				case eType.VIEW:      s_SQL += "xtype='V'";  break;
				case eType.TRIGGER:   s_SQL += "xtype='TR'"; break;
				case eType.TABLE:     s_SQL += "xtype='U'";  break;
				default: return null;
			}
			s_SQL += "ORDER BY name";

			DataTable i_Table = ReadTable(s_SQL, -1);
			if (i_Table == null)
				return null;

			string[] s_SysObj = new string[i_Table.Rows.Count];
			for (int R=0; R<i_Table.Rows.Count; R++)
			{
				s_SysObj[R] = (string)i_Table.Rows[R]["name"];
			}
			i_Table.Clear();
			return s_SysObj;
		}

		/// <summary>
		/// Read a trigger, view, procedure, function
		/// This function should be called from a GUI thread
		/// returns null on error
		/// </summary>
		public string ReadSysObject(string s_SysObj, eType e_Type)
		{
			string s_SQL = string.Format("SELECT text, encrypted FROM syscomments WHERE id = object_id('[{0}]') ORDER BY number, colid", s_SysObj);
			DataTable i_Table = ReadTable(s_SQL, -1);
			if (i_Table == null)
				return null;

			if (i_Table.Rows.Count == 0)
			{
				frmMsgBox.Err(mi_Owner, string.Format("The {0} '{1}' does not exist on the SQL server.", e_Type.ToString().ToLower(), s_SysObj));
				return null;
			}

			if ((bool)i_Table.Rows[0]["encrypted"])
			{
				frmMsgBox.Err(mi_Owner, string.Format("The {0} '{1}' is encrypted. This is currently not supported.", e_Type.ToString().ToLower(), s_SysObj));
				return null;
			}

			string s_Content = "";
			for (int R=0; R<i_Table.Rows.Count; R++)
			{
				s_Content += i_Table.Rows[R]["text"];
			}

			// single LF -> CR + LF
			return Functions.ReplaceCRLF(s_Content.Trim());
		}

		/// <summary>
		/// returns true if a procedure, view or function exists in the database, false if not exists
		/// returns null on server error
		/// This function should be called from a GUI thread
		/// </summary>
		public object SysObjectExists(string s_SysObj)
		{
			string s_SQL = "SELECT COUNT(*) FROM sysobjects WHERE id= object_id('["+s_SysObj+"]')";
			object o_Exists = ReadScalar(s_SQL, -1);

			if (!(o_Exists is Int32))
				return null;

			return ((int)o_Exists > 0);
		}

		/// <summary>
		/// returns column definitions like name, data type, primary key, indent, etc..
		/// s_TableName = null   --> read all tables
		/// s_TableName = "Name" --> read only the specified table
		/// returns a sorted ArrayList of TableCol definitions for each column in each table
		/// returns null on SQL error
		/// </summary>
		public TableDef LoadTableDefFromServer(string s_TableName)
		{
			string s_Sql = BuildSqlGetTableColumns(s_TableName); 
			DataTable i_DefintionsTable = ReadTable(s_Sql, -1);
			if (i_DefintionsTable == null)
				return null;

			return TableDef.FromDataTable(i_DefintionsTable);
		}

		/// <summary>
		/// See GetTableColumns()
		/// </summary>
		private string BuildSqlGetTableColumns(string s_TableName)
		{
			string s_Where = ""; 
			if (s_TableName != null) 
				s_Where += string.Format(" WHERE obj.name = '{0}'\n", s_TableName);

			return string.Format(Functions.ReadStringResource("GetTableColumns.sql"), s_Where) + "\n";
		}

		/// <summary>
		/// returns a Hashtable with
		/// -- Key = "Triggers", "Procedures", Views", "Functions", "Dates"
		/// -- Value = embedded hastable with
		///     --  Key   = Name of procedure, table etc..
		///     --  Value = Content of Proc, View etc.. (string) / TableDef instance / DateTime
		/// </summary>
		public Hashtable ReadAllSysObjects(bool b_Trig, bool b_Proc, bool b_View, bool b_Func, bool b_Tabl, bool b_Date)
		{
			Hashtable i_SysObjects = new Hashtable();

			const string s_Fmt = "SELECT object_name(id) AS name, text "
							   + "FROM syscomments "
							   + "WHERE encrypted=0 AND ({0}) "
							   + "ORDER BY id, colid \n"; // SORTING IMPORTANT!!!

			Hashtable i_List = new Hashtable();

			if (b_Trig) i_List.Add("Triggers",   string.Format(s_Fmt, "objectproperty(id, 'IsTrigger')=1"));
			if (b_Proc) i_List.Add("Procedures", string.Format(s_Fmt, "objectproperty(id, 'IsProcedure')=1 OR objectproperty(id, 'IsExtendedProc')=1"));
			if (b_View) i_List.Add("Views",      string.Format(s_Fmt, "objectproperty(id, 'IsView')=1"));
			if (b_Func) i_List.Add("Functions",  string.Format(s_Fmt, "objectproperty(id, 'IsTableFunction')=1 OR objectproperty(id, 'IsInlineFunction')=1 OR objectproperty(id, 'IsScalarFunction')=1"));
			if (b_Tabl) i_List.Add("Tables",     BuildSqlGetTableColumns(null));
			if (b_Date) i_List.Add("Dates",      "SELECT name, crdate FROM sysobjects\n");

			if (i_List.Count == 0)
				return null; // nothing to do

			string s_SQL = "";
			foreach (string s_Key in i_List.Keys)
			{
				s_SQL += (string)i_List[s_Key];
			}

			// Retrieve all tables in one dataset
			DataSet i_DataSet = ExecuteSQL(s_SQL, -1);
			if (i_DataSet == null)
				return null;

			// Copy from dataset
			StringBuilder s_Content = new StringBuilder(100000);
			int T=0;
			foreach (string s_Key in i_List.Keys) // NO for(...) here !!!! Same order as above !!!
			{
				Application.DoEvents();
				DataTable i_Table = i_DataSet.Tables[T++];
				Hashtable i_Hash  = new Hashtable();

				switch (s_Key)
				{
					case "Dates":
					{
						// Copy DataTable "Dates" into Hashtable
						foreach (DataRow i_Row in i_Table.Rows)
						{
							i_Hash[i_Row["name"]] = i_Row["crdate"];
						}
						break;
					}

					case "Tables":
					{
						// read all table definitions from the data table
						// with one TableDef (value) each table name (key)
						i_Hash = TableDef.TableFactory(i_Table);
						break;
					}

					default: // Proc, View, Func, Trig
					{
						for (int R=0; R<i_Table.Rows.Count; R++)
						{
							DataRow i_Row  = i_Table.Rows[R];
							string  s_Name = Functions.ToStr(i_Row["name"]);

							if (s_Name.Length == 0)
							{
								frmMsgBox.Err(mi_Owner, "FATAL SERVER ERROR: Sql Server returned invalid data!!");
								return null; // This may happen if the database is inconsistent -> re-install server!
							}

							s_Content.Append(i_Row["text"]);

							// Procedures, Functions > 8 kB Unicode data are split over multiple table rows !
							if (R+1 == i_Table.Rows.Count || s_Name != Functions.ToStr(i_Table.Rows[R+1]["name"]))
							{
								i_Hash[s_Name]   = s_Content.ToString();
								s_Content.Length = 0;
							}
						}
						break;
					}
				} // switch
				
				i_SysObjects[s_Key] = i_Hash;
			} // foreach

			i_DataSet.Clear(); // Avoid memory leak (.NET bug)
			return i_SysObjects;
		}

		/// <summary>
		/// returns an Array with all available types like "int", "varchar", "image",... 
		/// </summary>
		public string[] ReadAllDataTypes()
		{
			string s_Sql = "SELECT name FROM systypes ORDER BY name";
			DataTable i_Table = ReadTable(s_Sql, -1);
			if (i_Table == null)
				return null;

			string[] i_Types = new string[i_Table.Rows.Count];
			for(int R=0; R<i_Table.Rows.Count; R++)
			{
				i_Types[R] = (string)i_Table.Rows[R]["name"];
			}
			return i_Types;
		}

		// *************************************** STATIC *******************************************
		// *************************************** STATIC *******************************************
		// *************************************** STATIC *******************************************

		/// <summary>
		/// returns a SQL command to delete a sysobject
		/// </summary>
		public static string BuildDeleteSysObjectCommand(string s_SysObj, eType e_Type)
		{
			if (e_Type == eType.SQL || e_Type == eType.TABLE)
				return "";

			return string.Format("IF NOT object_id('{0}') IS NULL\nBEGIN\n  DROP {1} [{0}]\nEND\nGO\n\n",
			                     s_SysObj, e_Type);
		}

		/// <summary>
		/// ***************************************************************
		/// ATTENTION: Ex.LineNumber may be wrong due to server bugs (if 1)
		/// ***************************************************************
		/// Ex.Message = "Line 3: Incorrect syntax near 'LIKE'.\r\nLine 5: Incorrect syntax near 'AND'."
		/// Correct line numbers corresponding to the first line which is selected
		/// If the entire SQL code is executed s32_FirstSelectedLine must be = 1
		/// If no display of line numbers is desired s32_FirstSelectedLine must be = -1
		/// </summary>
		public static string ParseErrorMessage(Exception Ex, int s32_FirstSelectedLine)
		{
			int s32_ExcepLine = 0;
			if (Ex is SqlException)
				s32_ExcepLine = ((SqlException)Ex).LineNumber;

			string[] s_Parts = Ex.Message.Replace("\r", "").Split('\n');
			string   s_Msg   = "";

			for (int i=0; i<s_Parts.Length; i++)
			{
				int s32_ErrLine = 0;
				if (s_Parts[i].StartsWith("Line ") || 
					s_Parts[i].StartsWith("L�nea ")|| 
					s_Parts[i].StartsWith("Zeile "))
				{
					int End = s_Parts[i].IndexOf(":");
					if (End > 5 && End < 10)
					{
						string s_Number = s_Parts[i].Substring(5, End-5);
						s32_ErrLine = int.Parse(s_Number);

						s_Parts[i] = s_Parts[i].Substring(End+1).Trim(); // cut "Line No:"
					}
				}

				// If not "Line 23:" specified in error message use SqlException.LineNumber for the first error
				// ATTENTION: Ex.LineNumber may be wrongly 1 or 65536 due to server bugs
				// Use it only if greater than 1 and smaller than 50000
				if (s32_ErrLine == 0 && i == 0 && s32_ExcepLine > 1 && s32_ExcepLine < 50000)
					s32_ErrLine = s32_ExcepLine;

				if (s32_FirstSelectedLine > 0 && s32_ErrLine > 0)
					s_Msg += string.Format("Line {0}: {1}\n", s32_ErrLine + s32_FirstSelectedLine -1, s_Parts[i]);
				else
					s_Msg += s_Parts[i] + "\n";
			}

			if (s_Msg.Trim().Length == 0) // This happens ! (it's from Microsoft)
				s_Msg = "The Sql server created an exception without a message!";

			return s_Msg;
		}
	}
}