Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version

FireBird SqlAssistant - A FireBird SQL Data Block

, 16 Jan 2007
A FireBird .NET data access block completely written in C#. Supports Embedded SQL, INI File SQL and Stored Procedure
firebird_sqlassistant_src.zip
Code
bin
FireBird.SqlAssistant.dll
Demo
bin
Debug
fbembed.dll
FirebirdSql.Data.FirebirdClient.dll
Data
EMPLOYEE.FDB
Demo.csproj.user
Properties
Settings.settings
Query
Query.ini
FireBird.SqlAssistant.csproj.user
FireBird.SqlAssistant.suo
Properties
Settings.settings
vssver.scc
/*--------------------------------------------------------------------------------------
 * 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
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Rafey
Web Developer
Pakistan Pakistan
Software engineer developing solutions using Microsoft technologies.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150414.1 | Last Updated 16 Jan 2007
Article Copyright 2007 by Rafey
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid