/*
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.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
{
static string ms_NullColor = Functions.GetHtmlColor(Defaults.GridColor(typeof(DBNull)));
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)
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 (!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.VIEW: s_SQL += "xtype='V'"; break;
case eType.FUNCTION: s_SQL += "xtype='IF' OR xtype='FN' OR xtype='TF'"; break;
case eType.TRIGGER: s_SQL += "xtype='TR'"; 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 a Hashtable with
/// -- Key = "Triggers", "Procedures", Views", "Functions", "Tables" or "Dates"
/// -- Value = embedded hastable with
/// -- Key = name of procedure, table etc..
/// -- Value = content of Proc, View etc.. (strings) / table definitions (strings) / DateTimes
/// </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 \r\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", " SELECT obj.name AS name,"
+ " cols.name AS ColName,"
+ " typ.name AS ColType ,"
+ " cols.status & 8 As IsNullable,"
+ " cols.status & 128 As IsIdent,"
+ " ident_seed(obj.name) As Seed,"
+ " ident_incr(obj.name) As Increment"
+ " FROM syscolumns cols"
+ " INNER JOIN sysobjects obj ON obj.id = cols.id"
+ " INNER JOIN systypes typ ON typ.xusertype = cols.xusertype"
+ " WHERE obj.xtype = 'U'"
+ " ORDER BY obj.name, cols.colorder \r\n");
if (b_Date) i_List.Add("Dates", "SELECT name, crdate FROM sysobjects \r\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 i=0;
foreach (string s_Key in i_List.Keys) // NO for(...) here !!!! Same order as above !!!
{
Application.DoEvents();
DataTable i_Table = i_DataSet.Tables[i];
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":
{
for (int R=0; R<i_Table.Rows.Count; R++)
{
DataRow i_Row = i_Table.Rows[R];
string s_Name = ToStr(i_Row["name"]);
if (s_Content.Length == 0)
s_Content.AppendFormat("CREATE TABLE [{0}]\n(\n", s_Name);
s_Content.AppendFormat("[{0}] [{1}]", i_Row["ColName"], i_Row["ColType"]);
if ((int)i_Row["IsIdent"] > 0)
s_Content.AppendFormat(" IDENTITY( {0}, {1} )", i_Row["Seed"], i_Row["Increment"]);
if ((int)i_Row["IsNullable"] == 0)
s_Content.Append(" NOT");
s_Content.Append(" NULL, \n");
if (R+1 == i_Table.Rows.Count || s_Name != ToStr(i_Table.Rows[R+1]["name"]))
{
s_Content.Append(")");
i_Hash[s_Name] = s_Content.ToString();
s_Content.Length = 0;
}
}
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 = 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 != 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;
i++;
} // foreach
i_DataSet.Clear(); // Avoid memory leak (.NET bug)
return i_SysObjects;
}
// *************************************** 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)
return "";
return string.Format("IF EXISTS (SELECT * FROM sysobjects WHERE id= object_id('[{0}]'))\nBEGIN\n DROP {1} [{0}]\nEND\nGO\n\n",
s_SysObj, e_Type);
}
/// <summary>
/// If a table contains only one scalar value: returns true and this value
/// If a table is empty (0 rows) or null: retuns true and Value = null
/// </summary>
public static bool IsTableScalarOrEmpty(DataTable i_Table, out object o_Value)
{
o_Value = null;
if (i_Table == null || i_Table.Rows.Count == 0)
return true;
if (i_Table.Rows.Count == 1 && i_Table.Columns.Count == 1)
{
o_Value = i_Table.Rows[0][0];
return true; // scalar value
}
return false;
}
/// <summary>
/// Converts the content of a datatable cell into a string
/// </summary>
public static string DbObjectToString(object o_Value, bool b_ForHtml)
{
if (o_Value == null)
return "(NO QUERY RESULT)";
if (o_Value is DBNull)
{
if (b_ForHtml)
return string.Format("<font color={0}>{1}</font>", ms_NullColor, Defaults.NullText);
else
return Defaults.NullText;
}
if (o_Value is DateTime)
{
return ((DateTime)o_Value).ToString(Defaults.TimeFormat);
}
if (b_ForHtml)
return Functions.ReplaceHtml(o_Value.ToString());
else
return o_Value.ToString();
}
/// <summary>
/// Convert to string without exception if value is DBNull
/// </summary>
public static string ToStr(object o_Value)
{
if (o_Value == null || o_Value is DBNull)
return "";
return (string) o_Value;
}
/// <summary>
/// Calculate the MD5 for all cells of each row in a table and store it in an arraylist
/// ArrayList[Row] = MD5(CellContents)
/// This can be used to compare the contens of two tables
/// </summary>
public static ArrayList CalculateTableHash(DataTable i_Table)
{
ArrayList i_Hash = new ArrayList();
StringBuilder i_RowContent = new StringBuilder(5000);
for (int R=0; R<i_Table.Rows.Count; R++)
{
i_RowContent.Length = 0;
for (int C=0; C<i_Table.Columns.Count; C++)
{
i_RowContent.Append("{");
i_RowContent.Append(DbObjectToString(i_Table.Rows[R][C], false));
i_RowContent.Append("}");
}
string s_MD5 = Functions.CalcMD5(i_RowContent.ToString());
i_Hash.Add(s_MD5);
}
return i_Hash;
}
/// <summary>
/// Compares the contents of two tables by their MD5 which have been calculated with CalculateTableHash()
/// returns the count of rows which are equal and the count of rows which are different
/// </summary>
public static void CompareTableHashes(ArrayList i_Hash1, ArrayList i_Hash2, out int s32_Diff, out int s32_Equal)
{
s32_Diff = 0;
s32_Equal = 0;
ArrayList[] i_RowHash = { i_Hash1, i_Hash2 };
for (int T=0; T<2; T++)
{
for (int R=0; R<i_RowHash[T].Count; R++)
{
string s_RowHash = (string)i_RowHash[T][R];
// Check if an identical row exists in the other table
if (i_RowHash[1-T].Contains(s_RowHash))
s32_Equal ++;
else
s32_Diff ++;
}
}
}
/// <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 due to server bugs
// Use it only if greater than 1
if (s32_ErrLine == 0 && i == 0 && s32_ExcepLine > 1)
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;
}
}
}
Submit an article or tip