FireBird SqlAssistant - A FireBird SQL Data Block

/*--------------------------------------------------------------------------------------
 * Author: Rafey
 * 
 * Comments: FireBird SqlAssistant with Embedded and Ini Query support
 * 
 * Email: syedrafey@gmail.com
 * 
 -------------------------------------------------------------------------------------*/

using System;
using System.Data;
using System.Text;
using System.IO;
using System.Collections;
using System.Runtime.InteropServices;
using System.Configuration;
using FirebirdSql.Data.FirebirdClient;

namespace FireBird.SqlAssistant
{
	#region enum
	public enum ParamType
	{
		Insert,
		Update,
		Filter
	} 
	#endregion

	#region SQLAssistant Class
	public class SQLAssistant
	{
		#region Data Members
		public const string EmptyQuery = "";
		#endregion

		#region Core

		#region ExecuteIni

		public static DataSet ExecuteIni(string queryName)
		{
			return SQLAssistant.ExecuteIni(SQLHelp.FileQueryIni, queryName);
		}

		public static DataSet ExecuteIni(string iniPath, string queryName)
		{
			IniFile file = new IniFile(iniPath);

			return SQLAssistant.Execute(SQLHelp.Connectionstring, file.GetKey("Query", queryName));
		}

		public static string GetIniQuery(string queryName)
		{
			return SQLAssistant.GetIniQuery(SQLHelp.FileQueryIni, queryName);
		}

		public static string GetIniQuery(string iniPath, string queryName)
		{
			IniFile f = new IniFile(iniPath);

			return f.GetKey("Query", queryName);
		}
		#endregion

		#region Execute
		public static DataSet Execute(string query)
		{
			return SQLAssistant.Execute(SQLHelp.Connectionstring, query);
		}

		public static DataSet Execute(string connectionstring, string commandText)
		{
			FbDataAdapter da = new FbDataAdapter(commandText, SQLHelp.Connectionstring);
			DataSet ds = new DataSet();
			da.Fill(ds);
			return ds;
		}

		#endregion

		#endregion

		#region Select

		public static DataSet SelectAll(string tableName)
		{
			return SQLAssistant.Execute("SELECT * FROM " + SQLHelp.DQuote(tableName));
		}

		public static DataSet Select(string tableName, params object[] filterColVals)
		{
			return SQLAssistant.Execute("SELECT * FROM " + SQLHelp.DQuote(tableName) + SQLHelp.ParamList(ParamType.Filter, filterColVals));
		}

		public static bool Exists(string tableName, params object[] filterColVals)
		{
			DataTable table = SQLAssistant.Select(tableName, filterColVals).Tables[0];

			return table.Rows.Count > 0;
		}
		#endregion

		#region Insert
		public static int Insert(string tableName, params object[] colVals)
		{
			SQLAssistant.Execute("INSERT INTO" + SQLHelp.DSQuote(tableName) + SQLHelp.ParamList(ParamType.Insert, colVals));

			return SQLAssistant.GetCount(tableName);
		}

		public static int GetCount(string tableName)
		{
			DataTable table = SQLAssistant.SelectAll(tableName).Tables[0];

			return SQLAssistant.GetCount(tableName, table);
		}

		public static int GetCount(string tableName, DataRow row)
		{
			return SQLAssistant.GetCount(tableName, row.Table);
		}

		public static int GetCount(string tableName, DataTable table)
		{
			DataTable t = SQLAssistant.Execute("SELECT COUNT(*) FROM" + SQLHelp.DSQuote(tableName)).Tables[0];

			return Convert.ToInt32(t.Rows[0][0]);
		}

		public static int Insert(string tableName, DataRow row)
		{
			SQLAssistant.Execute("INSERT INTO" + SQLHelp.DSQuote(tableName) + SQLHelp.ParamList(ParamType.Insert, row));

			return SQLAssistant.GetCount(tableName, row);
		}
		#endregion

		#region Update
		public static int Update(string tableName, DataRow row, params object[] filterColVals)
		{
			SQLAssistant.Execute("UPDATE" + SQLHelp.DSQuote(tableName) + SQLHelp.ParamList(ParamType.Update, row) + SQLHelp.ParamList(ParamType.Filter, filterColVals));

			return 0;
		}

		#endregion

		#region Delete
		public static int Delete(string tableName, params object[] filterColVals)
		{
			SQLAssistant.Execute("DELETE FROM" + SQLHelp.DSQuote(tableName) + SQLHelp.ParamList(ParamType.Filter, filterColVals));

			return 0;
		}

		public static int Delete(string tableName, DataRow row)
		{
			SQLAssistant.Execute("DELETE FROM " + SQLHelp.DQuote(tableName) + SQLHelp.ParamList(ParamType.Filter, row));

			return 0;
		}
		#endregion

		#region Stored Procedures

        #region Methods

		// adds and returns a stored procedure out parameter ReturnValue
		public static int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
		{
			int result;

			FbCommand command = BuildIntCommand(storedProcName, parameters);

			command.CommandType = CommandType.StoredProcedure;

			rowsAffected = command.ExecuteNonQuery();

			result = (int)(command.Parameters["ReturnValue"].Value);

			return result;
		}

		// return rows affected
		public static int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = BuildIntCommand(storedProcName, parameters);

            command.CommandType = CommandType.StoredProcedure;

            return command.ExecuteNonQuery();
        }

		public static FbDataReader ExecuteReader(string storedProcName, IDataParameter[] parameters)
        {
            FbDataReader dr;

            FbCommand command = BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;

            dr = command.ExecuteReader();
          
            return dr;
        }

		public static DataSet ExecuteDataSet(string storedProcName, IDataParameter[] parameters)
        {
            DataSet ds = new DataSet();

			FbDataAdapter da = new FbDataAdapter();
            da.SelectCommand = BuildQueryCommand(storedProcName, parameters);
            da.Fill(ds);

            return ds;
        }

        #endregion

        #region Private Helpers

		private static FbCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = BuildQueryCommand(storedProcName, parameters);

            command.Parameters.Add(new FbParameter(
                "ReturnValue",
                FbDbType.Integer,
                4, /* Size */
                ParameterDirection.ReturnValue,
                false, /* is nullable */
                0, /* byte precision */
                0, /* byte scale */
                string.Empty,
                DataRowVersion.Default,
                ""));

            return command;
        }

		private static FbCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            FbCommand command = new FbCommand(storedProcName, new FbConnection(SQLHelp.Connectionstring));

            command.CommandType = CommandType.StoredProcedure;

			if (parameters != null)
			{
				foreach (FbParameter parameter in parameters)
				{
					command.Parameters.Add(parameter);
				}
			}

            return command;
}

        #endregion

		#endregion
	} 
	#endregion

	#region IniFile Class

	public class IniFile
	{
		public string Path;

		#region Win32
		[DllImport("kernel32")]
		private static extern long WritePrivateProfileString(string section,
			string key, string val, string filePath);

		[DllImport("kernel32")]
		private static extern int GetPrivateProfileString(string section,
				 string key, string def, StringBuilder retVal,
			int size, string filePath);
		#endregion

		#region Constructors
		public IniFile(string path)
		{
			Path = path;
		}
		#endregion

		#region Set Key
		public void SetKey(string section, string key, string value)
		{
			WritePrivateProfileString(section, key, value, this.Path);
		}
		#endregion

		#region Get Key
		public string GetKey(string section, string key)
		{
			StringBuilder temp = new StringBuilder(5000);

			int i = GetPrivateProfileString(section, key, "", temp, 5000, this.Path);

			return temp.ToString();
		}
		#endregion
	}

	#endregion

	#region SQLHelp Class
	public class SQLHelp
	{
		#region Helper Methods

		#region Common Methods

		public static void SetKey(string key, string value)
		{
			System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

			config.AppSettings.Settings[key].Value = value;

			config.Save(ConfigurationSaveMode.Modified);

			ConfigurationManager.RefreshSection("appSettings");
		}

		public static string GetKey(string key, string defaultValue)
		{
			try
			{
				string val = ConfigurationManager.AppSettings[key];

				if (val == null)
				{
					val = defaultValue;
				}

				return val;
			}
			catch (Exception)
			{
				return defaultValue;
			}
		}
		#endregion

		#region Application Properties

		#region Folder Path

		//e.g C:\App.WinUI\bin\Debug
		public static string FolderApp
		{
			get { return AppDomain.CurrentDomain.BaseDirectory; }
		}

		//e.g C:\App.WinUI
		public static string FolderRoot
		{
			get { return Path.GetFullPath(@"..\..\"); }
		}

		public static string FolderBin
		{
			get { return Path.GetFullPath(@".\"); }
		}

		public static string GetAppFolder(string name)
		{
			string path = SQLHelp.FolderApp + name + @"\";

			if (!Directory.Exists(path) || Directory.GetFiles(path).Length < 1)
			{
				path = SQLHelp.FolderRoot + name + @"\";
			}

			return path;
		}

		public static string FolderData
		{
			get { return SQLHelp.GetAppFolder("Data"); }
		}

		public static string FolderQuery
		{
			get { return SQLHelp.GetAppFolder("Query"); }
		}

		public static string FileFdb
		{
			get { return SQLHelp.FolderData + @"EMPLOYEE.FDB"; }
		}

		public static string FileQueryIni
		{
			get { return SQLHelp.FolderQuery + @"Query.ini"; }
		}
		#endregion

		#region Connectionstring

		public static string Connectionstring
		{
			get
			{
				return "ServerType=1;User=SYSDBA;Password=masterkey;Database=" + SQLHelp.FileFdb;
			}
		}

		#endregion

		#endregion

		#region string Helpers
		public static string Quote(string s)
		{
			return "\'" + s + "\'";
		}

		public static string SQuote(string s)
		{
			return " \'" + s + "\' ";
		}

		public static string DQuote(string s)
		{
			return "\"" + s + "\"";
		}

		public static string DSQuote(string s)
		{
			return " \"" + s + "\" ";
		}

		public static string Bracket(string s)
		{
			return "[" + s + "]";
		}

		public static string Parenthesis(string s)
		{
			return "(" + s + ")";
		}

		public static string Space(string s)
		{
			return " " + s + " ";
		}

		public static string Timestamp()
		{
			return DateTime.Now.ToString();
		}

		public static string BracketNonBlank(string s)
		{
			if (s.Trim() != "")
			{
				return SQLHelp.Bracket(s);
			}

			return s;
		}

		public static bool IsEmpty(string s)
		{
			return s == "";
		}

		public static bool IsAnyEmpty(params object[] vals)
		{
			for (int i = 0; i <= vals.Length; i++)
			{
				if (vals.GetValue(i).ToString() == "")
				{
					return true;
				}
			}

			return false;
		}

		public static bool IsNonEmpty(string s)
		{
			return s != "";
		}

		public static string LogStr(string s)
		{
			return "[" + s + "]";
		}

		public static string RemoveLast(string s)
		{
			return SQLHelp.RemoveLast(s, 1);
		}

		public static string RemoveLast(string s, int count)
		{
			return s.Remove(s.Length - count, count);
		}
		#endregion

		#region SQL Statement Helpers

		#region ParamList
		public static string ParamList(ParamType paramType, DataRow row)
		{
			return SQLHelp.ParamColl(paramType, SQLHelp.ToArray(row));
		}

		public static string ParamList(ParamType paramType, object[] colVals)
		{
			return SQLHelp.ParamColl(paramType, colVals);
		}

		public static string ParamColl(ParamType paramType, params object[] colVals)
		{
			string s = "";
			string f = "";
			string v = "";
			string fn = "";
			string fv = "";
			string op = "=";
			string suffix = "";

			if (colVals == null || colVals.Length < 2) // one col and one val is must!
			{
				return "";
			}

			switch (paramType)
			{
				case ParamType.Insert:
					op = "";
					suffix = ",";
					break;

				case ParamType.Update:
					s = " SET ";
					suffix = ",";
					break;

				case ParamType.Filter:
					s = " WHERE ";
					op = "=";
					suffix = "AND";
					break;
			}

			for (int i = 0; i < colVals.Length - 1; i = i + 2)
			{
				f = colVals.GetValue(i).ToString();
				v = colVals.GetValue(i + 1).ToString().Replace("'", "''");

				s += SQLHelp.DQuote(f);
				fn += SQLHelp.DQuote(f);
				fv += SQLHelp.Quote(v);

				if (op != "")
				{
					s += SQLHelp.Space(op) + SQLHelp.Quote(v);
				}

				if (suffix != "")
				{
					s += SQLHelp.Space(suffix);
					fn += SQLHelp.Space(suffix);
					fv += SQLHelp.Space(suffix);
				}
			}

			if (suffix != "")
			{
				s = SQLHelp.RemoveLast(s, (suffix.Length + 2)); // 2 is added because of speace we added above in suffix
				fn = SQLHelp.RemoveLast(fn, (suffix.Length + 2));
				fv = SQLHelp.RemoveLast(fv, (suffix.Length + 2));
			}

			switch (paramType)
			{
				case ParamType.Insert:
					s = SQLHelp.Parenthesis(fn) + SQLHelp.Space("VALUES") + SQLHelp.Parenthesis(fv);
					break;
			}

			return s;

		}

		#endregion

		private static object[] ToArray(DataRow row)
		{
			ArrayList list = new ArrayList();

			for (int i = 0; i < row.Table.Columns.Count; i++)
			{
				if (row[i] != System.DBNull.Value)
				{
					list.Add(row.Table.Columns[i]);
					list.Add(row[i]);
				}
			}

			return list.ToArray();
		}
		#endregion

		#endregion
	} 
	#endregion
}