QASharp V1.3 [Query Analyzer like tool for MSDE database]

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace DataLayer
{
	/// <summary>
	/// Summary description for SqlDBHandler.
	/// </summary>
	public class SqlDBHandler
	{
		public string ERR_MSG;
		private SqlConnection m_conn = null;

		private string m_ConnStr    = null;
		private string m_DataSource = null;
		private string m_Catalog    = null;
		private string m_UserName   = null;
		private string m_Password   = null;

		public SqlDBHandler (string DataSource, string Catalog, string UserName, string Password)
		{
			m_DataSource = DataSource;
			m_Catalog    = Catalog;
			m_UserName   = UserName;
			m_Password   = Password;

			m_ConnStr = "server=" + m_DataSource + ";database=" + m_Catalog + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";

			m_conn = new SqlConnection (m_ConnStr);
		}

		public SqlDBHandler (string connectionString)
		{
			if (string.Empty != connectionString)
				m_conn = new SqlConnection (connectionString);
		}

		~SqlDBHandler ()
		{
			this.Close ();
		}

		#region Public Properties
		public string ConnectionString
		{
			get
			{
				return m_ConnStr;
			}

			set
			{
				m_ConnStr = value;
				m_conn.ConnectionString = m_ConnStr;
			}
		}

		public string DataSource
		{
			get
			{
				return m_DataSource;
			}

			set
			{
				m_DataSource = value;
				m_ConnStr = "server=" + m_DataSource + ";database=" + m_Catalog + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
				m_conn.ConnectionString = m_ConnStr;
			}
		}

		public string Catalog
		{
			get
			{
				return m_Catalog;
			}

			set
			{
				m_Catalog = value;
				m_ConnStr = "server=" + m_DataSource + ";database=" + m_Catalog + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
				m_conn.ConnectionString = m_ConnStr;
			}
		}

		public string UserName
		{
			get
			{
				return m_UserName;
			}

			set
			{
				m_UserName = value;
				m_ConnStr = "server=" + m_DataSource + ";database=" + m_Catalog + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
				m_conn.ConnectionString = m_ConnStr;
			}
		}

		public string Password
		{
			set
			{
				m_Password = value;
				m_ConnStr = "server=" + m_DataSource + ";database=" + m_Catalog + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
				m_conn.ConnectionString = m_ConnStr;
			}
		}

		#endregion

		#region Public Methods
		public void Close ()
		{
			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					//m_conn.Open ();
				}
			}
			catch (SqlException ex)
			{
				System.Console.WriteLine(ex.Message);
			}
		}

		public SqlDataReader ExecReader (string sText)
		{
			SqlDataReader dr = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlCommand cmd = new SqlCommand (sText, m_conn);
				cmd.CommandType = CommandType.Text;
				dr = cmd.ExecuteReader ();
			}
			catch (Exception ex)
			{
				string s = ex.Message;
			}

			return dr;
		}

		public DataSet ExecDS (string sText)
		{
			DataSet ds = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);

				ds = new DataSet ();
				da.Fill (ds);
				
			}
			catch (Exception ex)
			{
				string s = ex.Message;
				ERR_MSG = ex.Message;
			}
			finally
			{
				m_conn.Close ();
			}

			return ds;
		}

		/// <summary>
		/// Update the changes from datatable to the datastore
		/// </summary>
		/// <author>
		/// Rajesh Pillai
		/// </author>
		/// <param name="dt"></param>
		public void UpdateDT(DataTable dt)
		{
			string sql;

			sql = "select * from " +  dt.TableName;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}
				
				SqlDataAdapter da = new SqlDataAdapter(sql,m_conn.ConnectionString);

				string field="";
				string values="";
				
				string sql1 = "";
				
				foreach(DataRow dtr in dt.Rows)
				{
					if (dtr.RowState == DataRowState.Added)
					{
						sql1 = " insert into " + dt.TableName + " (";
						field = "";
						values = "";

						foreach(DataColumn dc in dt.Columns)
						{
							if (dc.AutoIncrement == false)
							{
								field += dc.ColumnName + ",";
								values +=  "'" +  dtr[dc.ColumnName] + "',";
							}
						}
						field = field.Substring(0,field.Length-1);
						values = values.Substring(0,values.Length-1);
					
						sql1 += field  + ")" + "Values (" + values + ")" ;
					}
					else
					if (dtr.RowState == DataRowState.Modified)
					{
						sql1 = " update  " + dt.TableName + " set ";
						string stmt="";
						string where = " where ";
						foreach(DataColumn dc in dt.Columns)
						{
							field  = dc.ColumnName + " = ";
							values =  "'" +  dtr[dc.ColumnName].ToString().Trim() + "' ";

							stmt += field +  values + ",";
							where += field + "'" +  dtr[dc.ColumnName,DataRowVersion.Original].ToString().Trim() + "' and " ;
						}
						stmt = stmt.Substring(0,stmt.Length-1);
						where = where.Substring(0,where.Length-5);
						
						sql1 += stmt + where;

					}
					else
					if (dtr.RowState == DataRowState.Deleted)
					{
						sql1 = " delete from " + dt.TableName ;
						string stmt="";
						string where = " where ";
						foreach(DataColumn dc in dt.Columns)
						{
							field  = dc.ColumnName + " = ";
							where += field + "'" +  dtr[dc.ColumnName,DataRowVersion.Original].ToString().Trim() + "' and " ;
						}
						
						where = where.Substring(0,where.Length-5);
						
						sql1 += stmt + where;
					}

					if (sql1.Trim() != string.Empty)
						ExecNonQuery(sql1);
				}

			}
			catch (Exception ex)
			{
				System.Diagnostics.Debug.WriteLine(ex.Message);
			}
		}
 
		public DataTable ExecDT (string sText)
		{
			DataTable dt = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter (sText, m_conn);

				dt = new DataTable ();
				da.Fill (dt);
				try
				{
					da.FillSchema(dt,SchemaType.Source);
				}
				catch(Exception ex)
				{}

				foreach(DataColumn dc in dt.Columns)
				{
					if (dc.AutoIncrement == true)
					{
						dc.AutoIncrementSeed = -1;
						dc.AutoIncrementStep = -1;
					}
				}
			}
			catch (Exception ex)
			{
				string s = ex.Message;
				ERR_MSG = ex.Message;
				
			}
			finally
			{
				m_conn.Close ();
			}

			return dt;
		}

		public int ExecNonQuery (string sText)
		{
			int iRowsAffected = -1;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlCommand cmd = new SqlCommand (sText, m_conn);
				cmd.CommandType = CommandType.Text;
				iRowsAffected = cmd.ExecuteNonQuery ();
			}
			catch (Exception ex)
			{
				string s = ex.Message;
				ERR_MSG = ex.Message;
			}
			finally
			{
				m_conn.Close ();
			}

			return iRowsAffected;
		}

		public void ExecTran (string sText)
		{
			SqlTransaction trans = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlCommand cmd = new SqlCommand (sText, m_conn);
				cmd.CommandType = CommandType.Text;

				trans = m_conn.BeginTransaction ();

				cmd.Transaction = trans;

				cmd.ExecuteNonQuery ();
				trans.Commit ();
			}
			catch (Exception ex)
			{
				trans.Rollback ();
				throw (new Exception (ex.Message));
			}
			finally
			{
				m_conn.Close ();
			}
		}

		public void ExecDropObject (string sTextDrop, string sText)
		{
			SqlTransaction trans = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlCommand cmd = new SqlCommand (sTextDrop, m_conn);
				cmd.CommandType = CommandType.Text;

				trans = m_conn.BeginTransaction ();

				cmd.Transaction = trans;

				// run the drop text
				cmd.ExecuteNonQuery ();

				cmd.CommandText = sText;
				cmd.ExecuteNonQuery ();

				trans.Commit ();
			}
			catch (Exception ex)
			{
				trans.Rollback ();
				throw (new Exception (ex.Message));
			}
			finally
			{
				m_conn.Close ();
			}
		}

		public DataTable GetTables ()
		{
			DataTable dtTable = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter ("exec sp_tables", m_conn);

				DataTable dt = new DataTable ();
				da.Fill (dt);

				dtTable = new DataTable ("Tables");

				DataColumn dcCol;
				dcCol = dtTable.Columns.Add ("TABLE_NAME",  typeof (string));
				dcCol = dtTable.Columns.Add ("TABLE_OWNER", typeof (string));
				dcCol = dtTable.Columns.Add ("TABLE_TYPE",  typeof (string));

				// populate DataTable.
				foreach (DataRow row in dt.Rows)
				{
					if (String.Compare (row["TABLE_TYPE"].ToString (), "table", true) == 0 && String.Compare ("dtproperties", row["TABLE_NAME"].ToString (), true) != 0)
					{
						dtTable.Rows.Add (new object[] {row["TABLE_NAME"].ToString (),
														   row["TABLE_OWNER"].ToString (),
														   row["TABLE_TYPE"].ToString ()});
					}
				}
			}
			catch (SqlException ex)
			{
				System.Diagnostics.Trace.Write(ex.Message);
			}
			finally
			{
				m_conn.Close ();
			}
		
			return dtTable;
		}

		// get stored procedure text
		public DataTable GetSPText(string SPName)
		{
			//sp_helptext 'CustOrderHist'
			DataTable dtTable = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter ("exec sp_helptext " + SPName , m_conn);

				DataTable dt = new DataTable ();
				da.Fill (dt);

				dtTable = new DataTable ("SPText");

				DataColumn dcCol;
				dcCol = dtTable.Columns.Add ("TEXT",  typeof (string));
				
				// populate DataTable.
				foreach (DataRow row in dt.Rows)
				{
					dtTable.Rows.Add (new object[] {row["TEXT"].ToString ()});
				} 
			}
			catch (SqlException sx)
			{}
			finally
			{
				m_conn.Close ();
			}
		
			return dtTable;

		}

		// sp_stored_procedures
		public DataTable GetSP ()
		{
			DataTable dtTable = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter ("exec sp_stored_procedures", m_conn);

				DataTable dt = new DataTable ();
				da.Fill (dt);

				dtTable = new DataTable ("SP");

				DataColumn dcCol;
				dcCol = dtTable.Columns.Add ("PROCEDURE_NAME",  typeof (string));
				dcCol = dtTable.Columns.Add ("PROCEDURE_OWNER", typeof (string));
				dcCol = dtTable.Columns.Add ("PROCEDURE_TYPE",  typeof (string));

				// populate DataTable.
				foreach (DataRow row in dt.Rows)
				{
					dtTable.Rows.Add (new object[] {row["PROCEDURE_NAME"].ToString (),
													   row["PROCEDURE_OWNER"].ToString (),
													   row["PROCEDURE_TYPE"].ToString ()});
				}
			}
			catch (SqlException sx)
			{

			}
			finally
			{
				m_conn.Close ();
			}
		
			return dtTable;
		}



		public DataTable GetViews ()
		{
			DataTable dtTable = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				SqlDataAdapter da = new SqlDataAdapter ("exec sp_tables", m_conn);

				DataTable dt = new DataTable ();
				da.Fill (dt);

				dtTable = new DataTable ("Views");

				DataColumn dcCol;
				dcCol = dtTable.Columns.Add ("VIEW_NAME",  typeof (string));
				dcCol = dtTable.Columns.Add ("VIEW_OWNER", typeof (string));
				dcCol = dtTable.Columns.Add ("VIEW_TYPE",  typeof (string));

				// populate DataTable.
				foreach (DataRow row in dt.Rows)
				{
					if (String.Compare (row["TABLE_TYPE"].ToString (), "view", true) == 0)
					{
						dtTable.Rows.Add (new object[] {row["TABLE_NAME"].ToString (),
														   row["TABLE_OWNER"].ToString (),
														   row["TABLE_TYPE"].ToString ()});
					}
				}
			}
			finally
			{
				m_conn.Close ();
			}
		
			return dtTable;
		}

		public DataTable GetPermissions (string sUserName)
		{
			DataTable dt = null;

			try
			{
				if (m_conn.State == ConnectionState.Closed)
				{
					m_conn.Open ();
				}

				string sql = "select distinct ltrim (rtrim (O.xType)) as Type " +
					",o.name " +
					",user_name (objectproperty (p.id, 'ownerid')) as Owner " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 193 and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'Select' " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 195 and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'Insert' " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 197 and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'Update' " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 196 and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'Delete' " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 224 and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'Execute' " +
					",IsNull ((select case protecttype when 204 then 'GRANT_W_GRANT' when 205 then 'GRANT' when 206 then 'REVOKE' end from sysprotects p2 where action = 26  and id = o.id and  p2.uid = p.uid), 'EMPTY') as 'DRI' " +
					" from sysobjects o, sysprotects p " +
					" where o.id *= p.id " +
					" and user_name (p.uid) = '" + sUserName + "'" +
					" and o.xType in ('U', 'V', 'P') " +
					" order by O.name asc";

				SqlDataAdapter da = new SqlDataAdapter (sql, m_conn);

				dt = new DataTable ();
				da.Fill (dt);
			}
			finally
			{
				m_conn.Close ();
			}
		
			return dt;
		}

		public SqlDbType GetSqlDbType (string sDataType)
		{
			SqlDbType type;

			if (String.Compare (sDataType, "BigInt", true) == 0)
			{
				type = SqlDbType.BigInt;
			}
			else if (String.Compare (sDataType, "Binary", true) == 0)
			{
				type = SqlDbType.Binary;
			}
			else if (String.Compare (sDataType, "Bit", true) == 0)
			{
				type = SqlDbType.Bit;
			}
			else if (String.Compare (sDataType, "Char", true) == 0)
			{
				type = SqlDbType.Char;
			}
			else if (String.Compare (sDataType, "DateTime", true) == 0)
			{
				type = SqlDbType.DateTime;
			}
			else if (String.Compare (sDataType, "Decimal", true) == 0)
			{
				type = SqlDbType.Decimal;
			}
			else if (String.Compare (sDataType, "Float", true) == 0)
			{
				type = SqlDbType.Float;
			}
			else if (String.Compare (sDataType, "Image", true) == 0)
			{
				type = SqlDbType.Image;
			}
			else if (String.Compare (sDataType, "Int", true) == 0)
			{
				type = SqlDbType.Int;
			}
			else if (String.Compare (sDataType, "Money", true) == 0)
			{
				type = SqlDbType.Money;
			}
			else if (String.Compare (sDataType, "NChar", true) == 0)
			{
				type = SqlDbType.NChar;
			}
			else if (String.Compare (sDataType, "NText", true) == 0)
			{
				type = SqlDbType.NText;
			}
			else if (String.Compare (sDataType, "NVarChar", true) == 0)
			{
				type = SqlDbType.NVarChar;
			}
			else if (String.Compare (sDataType, "Real", true) == 0)
			{
				type = SqlDbType.Real;
			}
			else if (String.Compare (sDataType, "SmallDateTime", true) == 0)
			{
				type = SqlDbType.SmallDateTime;
			}
			else if (String.Compare (sDataType, "SmallInt", true) == 0)
			{
				type = SqlDbType.SmallInt;
			}
			else if (String.Compare (sDataType, "SmallMoney", true) == 0)
			{
				type = SqlDbType.SmallMoney;
			}
			else if (String.Compare (sDataType, "Text", true) == 0)
			{
				type = SqlDbType.Text;
			}
			else if (String.Compare (sDataType, "Timestamp", true) == 0)
			{
				type = SqlDbType.Timestamp;
			}
			else if (String.Compare (sDataType, "TinyInt", true) == 0)
			{
				type = SqlDbType.TinyInt;
			}
			else if (String.Compare (sDataType, "UniqueIdentifier", true) == 0)
			{
				type = SqlDbType.UniqueIdentifier;
			}
			else if (String.Compare (sDataType, "VarBinary", true) == 0)
			{
				type = SqlDbType.VarBinary;
			}
			else if (String.Compare (sDataType, "VarChar", true) == 0)
			{
				type = SqlDbType.VarChar;
			}
			else if (String.Compare (sDataType, "Variant", true) == 0)
			{
				type = SqlDbType.Variant;
			}
			else
			{
				type = SqlDbType.NVarChar;
			}

			return type;
		}

		#endregion
	}
}