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

namespace Municipalidad.Utils
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class UtilsDB
	{
		public UtilsDB()
		{
			//
			// TODO: Add constructor logic here
			//
		}
		public enum SqlError: int
		{
			c_succeeded_flag = 0,
			c_failed_flag = -1,
			c_notfound_flag = -2,
			c_changed_flag = -3,
			c_Exist_flag = -4,
			c_integrity_violations = -5,
			c_unknow_error = -6
		}

		#region ExecuteReader

		public static SqlDataReader ExecuteDataReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)

		{
			bool mustCloseConnection = false;
			if( connection == null ) throw new ArgumentNullException( "connection" );
			// Create a command and prepare it for execution
			try
			{
				SqlCommand cmd = new SqlCommand();
						 
				PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

				// Create a reader
				SqlDataReader dataReader; 
				dataReader = cmd.ExecuteReader();

				// Detach the SqlParameters from the command object, so they can be used again
				cmd.Parameters.Clear();
				return dataReader;
			}
			catch 
			{
				if (mustCloseConnection)
				{
					connection.Close();
					connection.Dispose();
				}
				throw;
			}
		}

		public static OleDbDataReader ExecuteDataReader(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)

		{
			bool mustCloseConnection = false;
			if( connection == null ) throw new ArgumentNullException( "connection" );
			// Create a command and prepare it for execution
			try
			{
				OleDbCommand cmd = new OleDbCommand();
						 
				PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

				// Create a reader
				OleDbDataReader dataReader; 
				dataReader = cmd.ExecuteReader();
				
				return dataReader;
			}
			catch 
			{
				if (mustCloseConnection)
				{
					connection.Close();
					connection.Dispose();
				}
				throw;
			}
		}
		#endregion ExecuteReader

		#region ExecuteDataset

		/// <summary>
		/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">A valid SqlConnection</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>A dataset containing the resultset generated by the command</returns>
		public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			if( connection == null ) throw new ArgumentNullException( "connection" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
    			
			// Create the DataAdapter & DataSet
			using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
			{
				DataSet ds = new DataSet();

				// Fill the DataSet using default values for DataTable names, etc
				da.Fill(ds);
				
				// Detach the SqlParameters from the command object, so they can be used again
				cmd.Parameters.Clear();

				if( mustCloseConnection )
					connection.Close();

				// Return the dataset
				return ds;
			}	
		}
		
		#endregion ExecuteDataset

		#region ExecuteDataTable
		/// <summary>
		/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  DataTable ds = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">A valid SqlConnection</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>A DataTable containing the resultset generated by the command</returns>
		public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			if( connection == null ) throw new ArgumentNullException( "Conexión" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
    			
			// Create the DataAdapter & DataSet
			using( SqlDataAdapter da = new SqlDataAdapter(cmd) )
			{
				DataSet ds = new DataSet();

				// Fill the DataSet using default values for DataTable names, etc
				da.Fill(ds);
				
				// Detach the SqlParameters from the command object, so they can be used again
				cmd.Parameters.Clear();

				if( mustCloseConnection )
					connection.Close();

				// Return the DataTable
				return ds.Tables.Count == 1 ? ds.Tables[0] : null;
			}	
		}

		#endregion ExecuteDataTable				

		#region ExecuteNonQuery

		/// <summary>
		/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">A valid SqlConnection</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>An int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{	
			if( connection == null ) throw new ArgumentNullException( "connection" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
    		
			// Finally, execute the command
			int retval = cmd.ExecuteNonQuery();
    		
			// Detach the SqlParameters from the command object, so they can be used again
			cmd.Parameters.Clear();
			if( mustCloseConnection )
				connection.Close();
			return retval;
		}

		/// <summary>
		/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="transaction">A valid SqlTransaction</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>An int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			if( transaction == null ) throw new ArgumentNullException( "transaction" );
			if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
    			
			// Finally, execute the command
			int retval = cmd.ExecuteNonQuery();
    			
			// Detach the SqlParameters from the command object, so they can be used again
			cmd.Parameters.Clear();
			return retval;
		}

		#endregion ExecuteNonQuery

		#region ExecuteNonQueryReturn

		/// <summary>
		/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="connection">A valid SqlConnection</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>An int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQueryReturn(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{	
			if( connection == null ) throw new ArgumentNullException( "connection" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );

			SqlParameter ReturnParam = new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4);
			ReturnParam.Direction = ParameterDirection.ReturnValue;
			cmd.Parameters.Add(ReturnParam);    		
			
			// Finally, execute the command
			int retval = (int)SqlError.c_unknow_error;
			try
			{
				cmd.ExecuteNonQuery();
				retval = (int)ReturnParam.Value;

				switch (retval)
				{
					case (int)UtilsDB.SqlError.c_failed_flag: 
						throw new SqlErrorException(CustomErrors.FailedSave_Errors);					
					case (int)UtilsDB.SqlError.c_changed_flag: 
						throw new SqlErrorException(CustomErrors.Changed_Errors);					
					case (int)UtilsDB.SqlError.c_notfound_flag: 
						throw new SqlErrorException(CustomErrors.NotFound_Errors);
					case (int)UtilsDB.SqlError.c_Exist_flag: 
						throw new SqlErrorException(CustomErrors.Exist_Errors);
					case (int)UtilsDB.SqlError.c_integrity_violations: 
						throw new SqlErrorException(CustomErrors.Integrity_violation);					
					default:
						retval = retval;
						break;
				}		

				// Detach the SqlParameters from the command object, so they can be used again
				cmd.Parameters.Clear();
					
				if( mustCloseConnection )
				{
					connection.Close();
					connection.Dispose();
				}

				return retval;
			}
			catch (SqlException e)
			{
				connection.Close();
				connection.Dispose();

				if (e.Number == 547) throw new SqlErrorException(CustomErrors.Integrity_violation);
				else throw e;
			}
			catch (SqlErrorException e)
			{
				throw new SqlErrorException(e.Message);
			}
		}

		/// <summary>
		/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
		/// </remarks>
		/// <param name="transaction">A valid SqlTransaction</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
		/// <returns>An int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQueryReturn(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
		{
			if( transaction == null ) throw new ArgumentNullException( "transaction" );
			if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );

			// Create a command and prepare it for execution
			SqlCommand cmd = new SqlCommand();
			bool mustCloseConnection = false;
			PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection );
    		
			SqlParameter ReturnParam = new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4);
			ReturnParam.Direction = ParameterDirection.ReturnValue;
			cmd.Parameters.Add(ReturnParam);

			/// Finally, execute the command
			int retval = (int)SqlError.c_unknow_error;
			try
			{
				cmd.ExecuteNonQuery();
				retval = (int)ReturnParam.Value;

				switch (retval)
				{
					case (int)UtilsDB.SqlError.c_failed_flag: 
						throw new SqlErrorException(CustomErrors.FailedSave_Errors);					
					case (int)UtilsDB.SqlError.c_changed_flag: 
						throw new SqlErrorException(CustomErrors.Changed_Errors);					
					case (int)UtilsDB.SqlError.c_notfound_flag: 
						throw new SqlErrorException(CustomErrors.NotFound_Errors);
					case (int)UtilsDB.SqlError.c_Exist_flag: 
						throw new SqlErrorException(CustomErrors.Exist_Errors);
					case (int)UtilsDB.SqlError.c_integrity_violations: 
						throw new SqlErrorException(CustomErrors.Integrity_violation);					
					default:
						retval = retval;
						break;
				}		
    			
				// Detach the SqlParameters from the command object, so they can be used again
				cmd.Parameters.Clear();

				return retval;
			}
			catch (SqlException e)
			{	
				if (e.Number == 547) throw new SqlErrorException(CustomErrors.Integrity_violation);
				else throw e;
			}
			catch (SqlErrorException e)
			{
				throw new SqlErrorException(e.Message);
			}
		}

		#endregion ExecuteNonQueryReturn

		#region private utility methods & constructors

		/// <summary>
		/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
		/// to the provided command
		/// </summary>
		/// <param name="command">The SqlCommand to be prepared</param>
		/// <param name="connection">A valid SqlConnection, on which to execute this command</param>
		/// <param name="transaction">A valid SqlTransaction, or 'null'</param>
		/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">The stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
		/// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
		private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
		{
			if( command == null ) throw new ArgumentNullException("command");
			if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

			
			// If the provided connection is not open, we will open it
			if (connection.State != ConnectionState.Open)
			{
				mustCloseConnection = true;
				connection.Open();
			}
			else
			{
				mustCloseConnection = false;
			}

			// Associate the connection with the command
			command.Connection = connection;
			command.CommandTimeout =10000000; 

			// Set the command text (stored procedure name or SQL statement)
			command.CommandText = commandText;

			// If we were provided a transaction, assign it
			if (transaction != null)
			{
				if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
				command.Transaction = transaction;
			}

			// Set the command type
			command.CommandType = commandType;

			// Attach the command parameters if they are provided
			if (commandParameters != null)
			{
				AttachParameters(command, commandParameters);
			}
			return;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="command"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		/// <param name="commandType"></param>
		/// <param name="commandText"></param>
		/// <param name="commandParameters"></param>
		/// <param name="mustCloseConnection"></param>
		private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, out bool mustCloseConnection )
		{
			if( command == null ) throw new ArgumentNullException("command");
			if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

			
			// If the provided connection is not open, we will open it
			if (connection.State != ConnectionState.Open)
			{
				mustCloseConnection = true;
				connection.Open();
			}
			else
			{
				mustCloseConnection = false;
			}

			// Associate the connection with the command
			command.Connection = connection;
			command.CommandTimeout = 0; 

			// Set the command text (stored procedure name or SQL statement)
			command.CommandText = commandText;

			// If we were provided a transaction, assign it
			if (transaction != null)
			{
				if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
				command.Transaction = transaction;
			}

			// Set the command type
			command.CommandType = commandType;

			// Attach the command parameters if they are provided
			if (commandParameters != null)
			{
				AttachParameters(command, commandParameters);
			}
			return;
		}		

		/// <summary>
		/// This method is used to attach array of SqlParameters to a SqlCommand.
		/// 
		/// This method will assign a value of DbNull to any parameter with a direction of
		/// InputOutput and a value of null.  
		/// 
		/// This behavior will prevent default values from being used, but
		/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
		/// where the user provided no input value.
		/// </summary>
		/// <param name="command">The command to which the parameters will be added</param>
		/// <param name="commandParameters">An array of SqlParameters to be added to command</param>
		private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
		{
			if( command == null ) throw new ArgumentNullException( "command" );
			if( commandParameters != null )
			{
				foreach (SqlParameter p in commandParameters)
				{
					if( p != null )
					{
						// Check for derived output value with no value assigned
						if ( ( p.Direction == ParameterDirection.InputOutput || 
							p.Direction == ParameterDirection.Input ) && 
							(p.Value == null))
						{
							p.Value = DBNull.Value;
						}
						command.Parameters.Add(p);
					}
				}
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="command"></param>
		/// <param name="commandParameters"></param>
		private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
		{
			if( command == null ) throw new ArgumentNullException( "command" );
			if( commandParameters != null )
			{
				foreach (OleDbParameter p in commandParameters)
				{					
					if( p != null )
					{
						// Check for derived output value with no value assigned
						if ( ( p.Direction == ParameterDirection.InputOutput || 
							p.Direction == ParameterDirection.Input ) && 
							(p.Value == null))
						{
							p.Value = DBNull.Value;
						}

						command.Parameters.Add(p);
					}
				}
			}
		}

		public static SqlConnection GetConnection(string connectionString)
		{
			try
			{
				SqlConnection connection = new SqlConnection(connectionString);
				connection.Open();
				return connection;
			}
			catch
			{		
				throw new SqlErrorException(CustomErrors.ConnectionDB_Errors);
			}
		}
		
		public static OleDbConnection GetConnectionOleDb(string connectionString)
		{
			try
			{
				OleDbConnection connection = new OleDbConnection(connectionString);
				connection.Open();				
				return connection;
			}
			catch 
			{				
				throw new OleDbErrorException (CustomErrors.ConnectionDB_Errors);
			}
		}
		
		public static SqlParameter MP(String parameterName, SqlDbType DbType, Int32 Size, Object Value) 
		{ 
			SqlParameter param; 

			if(Size > 0) 
			{ 
				param = new SqlParameter(parameterName, DbType, Size); 
			} 
			else 
			{ 
				param = new SqlParameter(parameterName, DbType); 
			} 
			param.Direction = ParameterDirection.Input; 
			
			if (!(param.Direction == ParameterDirection.Output && Value == null)) 
			{ 
				param.Value = Value;
			} 
			return param; 
		} 
		#endregion private utility methods & constructors
	}
}
