/*************************************************************

(C) ISEP 2003, 2004
(C) Laboratório .net DEI/ISEP 2003, 2004

O ISEP, na qualidade de autor do presente software, garante 
o direito de utilização, alteração, evolução e distribuição 
do mesmo sem qualquer tipo de restrições, excepto:
	- esta mensagem não pode ser retirada
	- os nomes das classes não podem ser alterados

*************************************************************/

using System;
using System.Data;
using System.Data.OleDb;

namespace Isep.Util.Data
{
	/// <summary>
	/// Summary description for OleDbHelper.
	/// </summary>
	public class OleDbHelper
	{
		#region CreateXXXConection
		public static OleDbConnection CreateOpenConnection(string conn)
		{
			OleDbConnection cnx = new OleDbConnection(conn);
			cnx.Open();
			return cnx;
		}

		public static OleDbTransaction CreateTransactedConnection(string conn)
		{
			OleDbConnection cnx = new OleDbConnection(conn);
			cnx.Open();
			return cnx.BeginTransaction();
		}
		#endregion

		#region ExecuteDataSet

		public static DataSet ExecuteDataSet(string conn, string select)
		{
			OleDbConnection cnx = CreateOpenConnection(conn);
			DataSet ds = ExecuteDataSet(cnx, select, null, null, null);
			cnx.Close();
			return ds;
		}

		public static DataSet ExecuteDataSet(string conn, string select, object[] parms)
		{
			OleDbConnection cnx = CreateOpenConnection(conn);
			DataSet ds = ExecuteDataSet(cnx, select, parms, null, null);
			cnx.Close();
			return ds;
		}

		public static DataSet ExecuteDataSet(OleDbConnection conn, string select)
		{
			return ExecuteDataSet(conn, select, null, null, null);
		}

		public static DataSet ExecuteDataSet(OleDbTransaction tx, string select)
		{
			return ExecuteDataSet(tx.Connection, select, null, tx, null);
		}

		public static DataSet ExecuteDataSet(OleDbConnection conn, string select, string tableName)
		{
			return ExecuteDataSet(conn, select, null, null, tableName);
		}

		public static DataSet ExecuteDataSet(OleDbTransaction tx, string select, string tableName)
		{
			return ExecuteDataSet(tx.Connection, select, null, tx, tableName);
		}

		public static DataSet ExecuteDataSet(OleDbTransaction tx, string select, object[] parms, string tableName)
		{
			return ExecuteDataSet(tx.Connection, select, parms, tx, tableName);
		}

		public static DataSet ExecuteDataSet(OleDbConnection conn, string select, object[] parms, OleDbTransaction tx, string tableName)
		{
			DataSet ds = new DataSet();
			OleDbDataAdapter da = new OleDbDataAdapter(select, conn);
			da.SelectCommand.Transaction = tx;
			HandleObjectParameters(da.SelectCommand, parms);
			if (tableName != null)
				da.Fill(ds, tableName);
			else
				da.Fill(ds);
			return ds;
		}

		#endregion

		#region FillDataSet

		public static void FillDataSet(OleDbConnection conn, string select, DataSet ds, string tableName)
		{
			FillDataSet(conn, select, null, null, ds, tableName);
		}

		public static void FillDataSet(OleDbTransaction tx, string select, DataSet ds, string tableName)
		{
			FillDataSet(tx.Connection, select, null, tx, ds, tableName);
		}

		public static void FillDataSet(OleDbTransaction tx, string select, object[] parms, DataSet ds, string tableName)
		{
			FillDataSet(tx.Connection, select, parms, tx, ds, tableName);
		}

		public static void FillDataSet(OleDbConnection conn, string select, object[] parms, DataSet ds, string tableName)
		{
			FillDataSet(conn, select, parms, null, ds, tableName);
		}

		public static void FillDataSet(OleDbConnection conn, string select, object[] parms, OleDbTransaction tx, DataSet ds, string tableName)
		{
			OleDbDataAdapter da = new OleDbDataAdapter(select, conn);
			da.SelectCommand.Transaction = tx;
			HandleObjectParameters(da.SelectCommand, parms);
			da.Fill(ds, tableName);
		}
		#endregion

		#region ExecuteScalar

		public static object ExecuteScalar(string conn, string select)
		{
			OleDbConnection cnx = CreateOpenConnection(conn);
			object ret = ExecuteScalar(cnx, select);
			cnx.Close();
			return ret;
		}

		public static object ExecuteScalar(OleDbConnection conn, string select)
		{
			OleDbCommand cmd = new OleDbCommand(select, conn);
			return cmd.ExecuteScalar();
		}

		public static object ExecuteScalar(OleDbTransaction tx, string select)
		{
			OleDbCommand cmd = new OleDbCommand(select, tx.Connection);
			cmd.Transaction = tx;
			return cmd.ExecuteScalar();
		}

		#endregion

		#region ExecuteUpdate

		public static int ExecuteUpdate(string conn, string cmd)
		{
			OleDbConnection cnx = CreateOpenConnection(conn);
			int ret = ExecuteUpdate(cnx, cmd);
			cnx.Close();
			return ret;
		}

		public static int ExecuteUpdate(OleDbConnection conn, string cmdText)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, conn);
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbConnection conn, OleDbCommand cmd)
		{
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbTransaction tx, string cmdText)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, tx.Connection);
			cmd.Transaction = tx;
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbTransaction tx, string cmdText, OleDbParameterCollection parms)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, tx.Connection);
			cmd.Transaction = tx;
			foreach(OleDbParameter p in parms)
				cmd.Parameters.Add(p);
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbConnection conn, string cmdText, object[] parms)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, conn);
			HandleObjectParameters(cmd, parms);
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbTransaction tx, string cmdText, object[] parms)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, tx.Connection);
			cmd.Transaction = tx;
			HandleObjectParameters(cmd, parms);
			return cmd.ExecuteNonQuery();
		}

		public static int ExecuteUpdate(OleDbTransaction tx, OleDbCommand cmd)
		{
			cmd.Transaction = tx;
			return cmd.ExecuteNonQuery();
		}

		#endregion

		#region InsertAndGetID

		public static int InsertAndGetID(string conn, string cmd)
		{
			OleDbConnection cnx = CreateOpenConnection(conn);
			int ret = InsertAndGetID(cnx, cmd, null, null);
			cnx.Close();
			return ret;
		}

		public static int InsertAndGetID(OleDbConnection conn, string cmdText)
		{
			return InsertAndGetID(conn, cmdText, null, null);
		}

		public static int InsertAndGetID(OleDbTransaction tx, string cmdText)
		{
			return InsertAndGetID(tx.Connection, cmdText, null, tx);
		}

		public static int InsertAndGetID(OleDbTransaction tx, string cmdText, object[] parms)
		{
			return InsertAndGetID(tx.Connection, cmdText, parms, tx);
		}

		public static int InsertAndGetID(OleDbConnection conn, string cmdText, object[] parms)
		{
			return InsertAndGetID(conn, cmdText, parms, null);
		}

		public static int InsertAndGetID(OleDbConnection conn, string cmdText, object[] parms, OleDbTransaction tx)
		{
			OleDbCommand cmd = new OleDbCommand(cmdText, conn);
			cmd.Transaction = tx;
			HandleObjectParameters(cmd, parms);
			int i = cmd.ExecuteNonQuery();

			OleDbCommand cmdId = new OleDbCommand("SELECT @@IDENTITY", conn);
			cmdId.Transaction = tx;
			return (int)cmdId.ExecuteScalar();
		}
		#endregion

		#region Private Methods

		/*
		 * todos os métodos desta classe que recebem um argumentos do tipo object[] permitem
		 * utilizar parametros nos comandos SQL. para usar seguir o exemplo:
		 * 
		 *		object[] parms = new object[] {
		 * 					"@un", OleDbType.Char, "i111999",
		 * 					"@pwd", OledDbType.Char, "7GFe2a",
		 * 					"@e", OleDbType.Char, "i111999@dei.isep.ipp.pt",
		 * 					};
		 *		OleDbHelper.ExecuteUpdate("INSERT INTO utilizadores (user_name, pass_word, email) VALUES (@un, @pwd, @e)", conn, parms);
		 * 
		 */
		private static void HandleObjectParameters(OleDbCommand cmd, object[] parms)
		{
			if (parms == null)
				return;

			try
			{
				for (int i =0; i < parms.Length; i+=3)
				{
					OleDbParameter p = cmd.Parameters.Add((string)parms[i], (OleDbType)parms[i+1]);
					p.Value = parms[i+2];
				}
			}
			catch
			{
				throw new ArgumentException("invalid content", "parms");
			}
		}
		#endregion
	}
}