using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
//using Oracle.DataAccess.Client;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.DirectoryServices.AccountManagement;


namespace DataAccess
{
    public static class DataAccessUtility
    {
        //public static string m_SP_UR_GET_USER_ACCESS = ConfigurationManager.AppSettings["SP_UR_GET_USER_ACCESS"];
        //public static string m_SP_UR_GET_USER_ACCESS = Properties.Settings.Default.SP_UR_GET_USER_ACCESS;

        private static string[] GetRoles
        {
            get
            {
                return ConfigurationManager.AppSettings["Roles"].Split('|');
            }
        }

        public static string GetCurrentUserRoles(string userName)
        {
            string[] roles = GetRoles;
            string userRoles = "";

            foreach (string roleName in roles)
            {
                string[] members = ConfigurationManager.AppSettings[roleName].Split(',');
                foreach (string memberName in members)
                {
                    if (memberName == userName)
                    {
                        userRoles = roleName;
                    }
                }
            }

            return userRoles;
        }

        public static string getUserRoles()
        {
            string all_Roles = "";
            try
            {

                using (PrincipalContext ctx = new PrincipalContext(ContextType.Domain))
                {
                    // find a user
                    UserPrincipal user = UserPrincipal.FindByIdentity(ctx, System.Web.HttpContext.Current.User.Identity.Name.ToUpper());
                    //UserPrincipal user = UserPrincipal.FindByIdentity(ctx, "LUTEST\\PINBROUT");

                    if (user != null)
                    {
                        // find the roles....
                        var roles = user.GetAuthorizationGroups();
                        // enumerate over them
                        foreach (Principal p in roles)
                        {
                            all_Roles = all_Roles + p + ",";
                        }
                        all_Roles = all_Roles.TrimEnd(',');
                    }

                }
            }
            catch (Exception Ex) { }
            finally
            {
            }

           // return "APP_VMS_VIEW,"+all_Roles;
           return "APP_VMS_EDIT," + all_Roles;
            //return "APP_VMS_SPRAY_VIEW," + all_Roles;
           //return "APP_VMS_SPRAY_EDIT," + all_Roles;
            //return "APP_VMS_ADMIN," + all_Roles;

        }

        public static string GetRole(string argStoredProcedureName, string pageTitle)
        {
            int result=0;
            string sarRoles = getUserRoles();
            try
            {
                List<OleDbParameter> sqlParams = new List<OleDbParameter>();
                sqlParams.Add(new OleDbParameter("V_USER_SAR_ROLES", sarRoles));
                sqlParams.Add(new OleDbParameter("V_PAGE_TITLE", pageTitle));
                result = DataAccessUtility.SelectSingleValueViaStoreProcedureParamWithOutPutParam(argStoredProcedureName, sqlParams);
            }
            catch
            {
            }
            return result.ToString();
        }

        public static void BindGridViewViaProcedure(string argStoredProcedureName, GridView myGrid)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);

            try
            {
                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;

               
                // We now add any available parameters to the command.



                // Open the connection.
                OracleConn.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(cmd);
                DataSet myDataSet = new DataSet();

                //Fill the DataTable through SqlDataAdapter
                myOleDbDataAdapter.Fill(myDataSet);

                //Bind the Datasource to Dropdownlist
                myGrid.DataSource = myDataSet;
                myGrid.DataBind();

                cmd.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }

        public static void BindGridViewViaProcedureWithParameter(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter, GridView myGrid)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);

            try
            {
                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;


                // We now add any available parameters to the command.
                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }


                // Open the connection.
                OracleConn.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(cmd);
                DataSet myDataSet = new DataSet();

                //Fill the DataTable through SqlDataAdapter
                myOleDbDataAdapter.Fill(myDataSet);

                //Bind the Datasource to Dropdownlist
                myGrid.DataSource = myDataSet;
                myGrid.DataBind();

                cmd.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }

        public static void BindDropdownListWithoutConditionViaProcedure(string argStoredProcedureName, DropDownList ddl)
        {
            // Establish the connection object.
            OleDbConnection myOleDbConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new OleDb Connection using that connection string.
            myOleDbConnection = new OleDbConnection(myConnectionString);

            try
            {
                // Establish data objects.
                OleDbCommand myOleDbCommand;
                DataSet myDataSet = new DataSet();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                myOleDbCommand = new OleDbCommand(argStoredProcedureName, myOleDbConnection);

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                

                // Open the connection.
                myOleDbConnection.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through OleDbDataAdapter
                myOleDbDataAdapter.Fill(myDataTable);

                //Bind the Datasource to Dropdownlist
                ddl.DataSource = myDataTable;
               
                ddl.DataTextField = "NAME";
                
                ddl.DataValueField = "ID";

                ddl.DataBind();

                myOleDbCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                myOleDbConnection.Close();

            }
        }

        public static void BindDropdownListWithConditionViaProcedure(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter, DropDownList ddl)
        {
            // Establish the connection object.
            OleDbConnection myOleDbConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new OleDb Connection using that connection string.
            myOleDbConnection = new OleDbConnection(myConnectionString);

            try
            {
                // Establish data objects.
                OleDbCommand myOleDbCommand;
                DataSet myDataSet = new DataSet();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                myOleDbCommand = new OleDbCommand(argStoredProcedureName, myOleDbConnection);

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        myOleDbCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                myOleDbConnection.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through OleDbDataAdapter
                myOleDbDataAdapter.Fill(myDataTable);
                if (myDataTable.Rows.Count > 0)
                {
                    //Bind the Datasource to Dropdownlist
                    ddl.DataSource = myDataTable;
                    ddl.DataTextField = "NAME";
                    ddl.DataValueField = "ID";

                    ddl.DataBind();
                }
                else
                {
                    ddl.SelectedIndex = -1;
                    ddl.Items.Clear();
                }
                myOleDbCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                myOleDbConnection.Close();

            }
        }

        public static void BindListboxWithConditionViaProcedure(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter, ListBox ddl)
        {
            // Establish the connection object.
            OleDbConnection myOleDbConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new OleDb Connection using that connection string.
            myOleDbConnection = new OleDbConnection(myConnectionString);

            try
            {
                // Establish data objects.
                OleDbCommand myOleDbCommand;
                DataSet myDataSet = new DataSet();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                myOleDbCommand = new OleDbCommand(argStoredProcedureName, myOleDbConnection);

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        myOleDbCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                myOleDbConnection.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through OleDbDataAdapter
                myOleDbDataAdapter.Fill(myDataTable);

                //Bind the Datasource to Dropdownlist
                ddl.DataSource = myDataTable;

                ddl.DataTextField = "NAME";

                ddl.DataValueField = "ID";

                ddl.DataBind();

                myOleDbCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                myOleDbConnection.Close();

            }
        }

        public static void BindListboxWithoutConditionViaProcedure(string argStoredProcedureName,  ListBox ddl)
        {
            // Establish the connection object.
            OleDbConnection myOleDbConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new OleDb Connection using that connection string.
            myOleDbConnection = new OleDbConnection(myConnectionString);

            try
            {
                // Establish data objects.
                OleDbCommand myOleDbCommand;
                DataSet myDataSet = new DataSet();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                myOleDbCommand = new OleDbCommand(argStoredProcedureName, myOleDbConnection);

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

              

                // Open the connection.
                myOleDbConnection.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through OleDbDataAdapter
                myOleDbDataAdapter.Fill(myDataTable);

                //Bind the Datasource to Dropdownlist
                ddl.DataSource = myDataTable;

                ddl.DataTextField = "NAME";

                ddl.DataValueField = "ID";

                ddl.DataBind();

                myOleDbCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                myOleDbConnection.Close();

            }
        }

        public static void BindGridViewWithConditionViaProcedure(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter, GridView myGrid)
        {
            // Establish the connection object.
            OleDbConnection myOleDbConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new OleDb Connection using that connection string.
            myOleDbConnection = new OleDbConnection(myConnectionString);

            try
            {
                // Establish data objects.
                OleDbCommand myOleDbCommand;

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                myOleDbCommand = new OleDbCommand(argStoredProcedureName, myOleDbConnection);

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        myOleDbCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                myOleDbConnection.Open();

                //Establish the DataAdapter
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter(myOleDbCommand);
                DataSet myDataSet = new DataSet();

                //Fill the DataTable through OleDbDataAdapter
                myOleDbDataAdapter.Fill(myDataSet);

                //Bind the Datasource to Dropdownlist
                myGrid.DataSource = myDataSet;
                myGrid.DataBind();

                myOleDbCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                myOleDbConnection.Close();

            }
        }
        public static void BindDataListWithConditionViaProcedure(string argStoredProcedureName, List<SqlParameter> argSqlParameter, DataList myDataList)
        {
            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argSqlParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argSqlParameter)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                mySqlConnection.Open();

                //Establish the DataAdapter
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);
                DataSet myDataSet = new DataSet();

                //Fill the DataTable through SqlDataAdapter
                mySqlDataAdapter.Fill(myDataSet);

                //Bind the Datasource to Dropdownlist
                myDataList.DataSource = myDataSet;
                myDataList.DataBind();

                mySqlCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();

            }
        }
       
        public static void BindCheckBoxListWithConditionViaProcedureSETUP(string argSqlParameter, CheckBoxList chklist)
        {
            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;
                DataSet myDataSet = new DataSet();

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand("sp_setup_selectsystemparameter", mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argSqlParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    mySqlCommand.Parameters.Add("@ParameterName", SqlDbType.VarChar, 50).Value = argSqlParameter;
                }

                // Open the connection.
                mySqlConnection.Open();

                //Establish the DataAdapter
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through SqlDataAdapter
                mySqlDataAdapter.Fill(myDataTable);

                //Bind the Datasource to Dropdownlist
                chklist.DataSource = myDataTable;
                chklist.DataTextField = "pname";
                chklist.DataValueField = "pvalue";

                chklist.DataBind();
                chklist.Items.RemoveAt(0);
                mySqlCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();

            }
        }
       
        public static void BindCheckBoxListWithConditionViaProcedure(string argStoredProcedureName, List<SqlParameter> argSqlParameter, CheckBoxList chkBoxList)
        {
            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;
                DataSet myDataSet = new DataSet();

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argSqlParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argSqlParameter)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                mySqlConnection.Open();

                //Establish the DataAdapter
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);
                DataTable myDataTable = new DataTable();

                //Fill the DataTable through SqlDataAdapter
                mySqlDataAdapter.Fill(myDataTable);

                //Bind the Datasource to Dropdownlist
                chkBoxList.DataSource = myDataTable;
                chkBoxList.DataTextField = mySqlCommand.Parameters["@paramTextField"].Value.ToString();
                chkBoxList.DataValueField = mySqlCommand.Parameters["@paramValueField"].Value.ToString();

                chkBoxList.DataBind();

                mySqlCommand.Dispose();

            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();

            }
        }
        public static string SelectSingleValueViaStoreProcedureParam(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);
            OleDbCommand myOleDbCommand = new OleDbCommand(argStoredProcedureName, OracleConn);
            try
            {
                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;


                // We now add any available parameters to the command.
                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                OracleConn.Open();

                //bind to the dataset through DataAdapter
                string myValue = myOleDbCommand.ExecuteScalar().ToString();

                myOleDbCommand.Dispose();

                return myValue;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }

        public static int SelectSingleValueViaStoreProcedureParamWithOutPutParam(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);
            OleDbCommand myOleDbCommand = new OleDbCommand(argStoredProcedureName, OracleConn);
            try
            {
                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;


                // We now add any available parameters to the command.
                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                OracleConn.Open();
                cmd.Parameters.AddWithValue("V_RETURN_VALUE", DbType.Int32).Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                int resultQuery = 0;
                if (cmd.Parameters["V_RETURN_VALUE"].Value.ToString() != "0")
                {
                    resultQuery = Convert.ToInt32(cmd.Parameters["V_RETURN_VALUE"].Value.ToString());
                }

                cmd.Dispose();

                return resultQuery;
                //bind to the dataset through DataAdapter
                //string myValue = myOleDbCommand.ExecuteScalar().ToString();

                //myOleDbCommand.Dispose();

                //return myValue;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }

        public static DataSet SelectDataSetViaStoreProcedureParam(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter)
        {
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);


            try
            {
               
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter();
                DataSet myDataSet = new DataSet();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                OleDbCommand myOleDbCommand = new OleDbCommand(argStoredProcedureName, OracleConn);
               

                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        myOleDbCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                OracleConn.Open();

                //bind to the dataset through DataAdapter
                myOleDbDataAdapter.SelectCommand = myOleDbCommand;
                myOleDbDataAdapter.Fill(myDataSet);

                myOleDbCommand.Dispose();

                return myDataSet;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }
        public static DataTable SelectDataTableViaStoreProcedureParam(string argStoredProcedureName, List<OleDbParameter> argOleDbParameter)
        {
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);


            try
            {

                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter();
                DataTable myDataTable = new DataTable();

                // Create a new OleDb Command and give it the name of the stored procedure and the OleDb connection.
                OleDbCommand myOleDbCommand = new OleDbCommand(argStoredProcedureName, OracleConn);


                // Inform the command that it is a stored procedure.
                myOleDbCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argOleDbParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argOleDbParameter)
                    {
                        myOleDbCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                OracleConn.Open();

                //bind to the dataset through DataAdapter
                myOleDbDataAdapter.SelectCommand = myOleDbCommand;
                myOleDbDataAdapter.Fill(myDataTable);

                myOleDbCommand.Dispose();

                return myDataTable;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }
        public static int InsertUpdateDeleteViaStoreProcedureParam(string argStoredProcedureName, List<OleDbParameter> argSqlParameter)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);

            try
            {
                

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argSqlParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argSqlParameter)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                OracleConn.Open();
               
                //ExecuteNonQuery
                //int resultQuery = cmd.ExecuteNonQuery();
              
               // cmd.Dispose();


                cmd.Parameters.AddWithValue("V_RETURN_VALUE", DbType.Int32).Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                int resultQuery = 0;
                if (cmd.Parameters["V_RETURN_VALUE"].Value.ToString() != "0")
                {
                    resultQuery = Convert.ToInt32(cmd.Parameters["V_RETURN_VALUE"].Value.ToString()) ;
                }

                cmd.Dispose();
               
                return resultQuery;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }

        public static string InsUpdDelViaStoreProcedureParam(string argStoredProcedureName, List<OleDbParameter> argSqlParameter)
        {
            // Establish the connection object.
            OleDbConnection OracleConn;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            OracleConn = new OleDbConnection(myConnectionString);

            try
            {
                // Open the connection.
                OracleConn.Open();

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                OleDbCommand cmd = new OleDbCommand(argStoredProcedureName, OracleConn);

                // Inform the command that it is a stored procedure.
                cmd.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argSqlParameter == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (OleDbParameter sp in argSqlParameter)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }

                

                //ExecuteNonQuery
                //int resultQuery = cmd.ExecuteNonQuery();

                // cmd.Dispose();


                cmd.Parameters.Add("V_RETURN_VALUE", OleDbType.VarChar,200).Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                string resultQuery = string.Empty;
                if (cmd.Parameters["V_RETURN_VALUE"].Value.ToString() != "")
                {
                    resultQuery = cmd.Parameters["V_RETURN_VALUE"].Value.ToString();
                }

                cmd.Dispose();

                return resultQuery;
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                OracleConn.Close();

            }
        }
        public static SqlDataTable getDataTableViaStoredProcedure(string argStoredProcedureName)
        {
            // Another convienience call.
            return DataAccessUtility.getDataTableViaMultiParameterStoredProcedure(argStoredProcedureName, null);
        }

        public static SqlDataTable getDataTableViaSingleParameterStoredProcedure(string argStoredProcedureName, SqlParameter argSqlParameter)
        {
            // This is a convienience call for the more complicated multi-parameter call.

            // We are just reducing the work the caller needs to do.
            List<SqlParameter> tmpParameterList = new List<SqlParameter>();
            tmpParameterList.Add(argSqlParameter);

            return DataAccessUtility.getDataTableViaMultiParameterStoredProcedure(argStoredProcedureName, tmpParameterList);
        }

        public static DataTable GetStandardDataTableViaStoredProcedure(string argStoredProcedureName, List<SqlParameter> argParameters)
        {
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;
            SqlConnection mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);
            DataTable retVal = new DataTable();
            SqlCommand mySqlCommand;
            try
            {
                mySqlCommand = new SqlCommand(argStoredProcedureName, mySqlConnection);
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argParameters == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameters)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                SqlDataAdapter myData = new SqlDataAdapter(mySqlCommand);

                myData.Fill(retVal);
            }

            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();
            }
            return retVal;
        }

        public static SqlDataTable getDataTableViaMultiParameterStoredProcedure(string argStoredProcedureName, List<SqlParameter> argParameters)
        {
            // This method will read in the contents of any named stored procedure and then
            // place the column values into SqlDataCells* nested within SqlDataRows* nested
            // within a SqlDataTable*. This dataset can then be iterated over to recover
            // the data without worrying about the actual SQL connection.
            //
            // [*] The SqlDataTable, SqlDataRow, and SqlDataCell are datasets created for the
            // project. They are simple public structures for holding the related SQL query
            // data.

            SqlDataTable tmpTable = new SqlDataTable();
            tmpTable.DataRows = new List<SqlDataRow>();

            // Establishing data connection object outside of the inner try scope in order to close
            // it in the finally block in the event of an error.

            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;
                SqlDataReader mySqlDataReader;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // We now add any available parameters to the command.

                if (argParameters == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameters)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                mySqlConnection.Open();

                // Set up the DataReader as a function of the SQL Command.
                mySqlDataReader = mySqlCommand.ExecuteReader();

                // This will execute multiple times based on the number of rows.
                while (mySqlDataReader.Read())
                {
                    // Create a table row to insert into the list.
                    SqlDataRow tmpRow = new SqlDataRow();
                    tmpRow.DataCells = new List<SqlDataCell>();

                    // Iterator for traversing the columns in the table row.
                    int i = 0;

                    while (i < mySqlDataReader.FieldCount)
                    {
                        // Create a table cell to insert into the row.
                        SqlDataCell tmpCell = new SqlDataCell();

                        // Setting the name of the column, if any.
                        tmpCell.ColumnName = mySqlDataReader.GetName(i).ToString();

                        // Setting the data type of the cell.
                        tmpCell.DataType = mySqlDataReader.GetDataTypeName(i);

                        if (mySqlDataReader.IsDBNull(i))
                        {
                            // If the column is null then we set the cell to 0 or an empty string 
                            // depending on the data type
                            switch (tmpCell.DataType)
                            {

                                case "int": tmpCell.Value = "0"; break;
                                case "decimal": tmpCell.Value = "0"; break;
                                case "nvarchar": tmpCell.Value = ""; break;
                                default: tmpCell.Value = ""; break;
                            }
                        }
                        else
                        {
                            // Get's the column data in its native format then changes it to
                            // a string and inserts that into the cell.
                            tmpCell.Value = mySqlDataReader.GetValue(i).ToString().Trim();
                        }

                        // Adding the cell to the row.
                        tmpRow.DataCells.Add(tmpCell);

                        // Iterate.
                        i++;
                    }

                    // Adding the table row to the list.
                    tmpTable.DataRows.Add(tmpRow);
                }
                // End of datareader reads.

                // Close the Data Reader.
                mySqlDataReader.Close();
            }
            catch (Exception outerEx)
            {
                throw new Exception(outerEx.Message + " Possibly due to failed database read.");
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();
            }

            // Return the list of table rows.
            return tmpTable;
        }

        //---------------------------------------------------------------------------------------------------

        public static string performSingleParameterNonQueryStoredProcedureWithOutputParameter(string argStoredProcedureName, SqlParameter argSqlParameter)
        {
            // This is a convienience call for the more complicated multi-parameter call.

            // We are just reducing the work the caller needs to do.
            List<SqlParameter> tmpParameterList = new List<SqlParameter>();
            tmpParameterList.Add(argSqlParameter);

            return DataAccessUtility.performMultiParameterNonQueryStoredProcedureWithOutputParameter(argStoredProcedureName, tmpParameterList);
        }

        public static string performMultiParameterNonQueryStoredProcedureWithOutputParameter(string argStoredProcedureName, List<SqlParameter> argParameters)
        {
            // This method will perform a non-query stored procedure.

            // Establishing data connection object outside of the inner try scope in order to close
            // it in the finally block in the event of an error.

            // Establish Retrun value.
            string tmpReturnValue = string.Empty;

            SqlParameter tmpReturnValueParam = new SqlParameter("@return_value", SqlDbType.NVarChar);
            tmpReturnValueParam.Value = "";
            tmpReturnValueParam.Size = 2000;
            tmpReturnValueParam.Direction = ParameterDirection.Output;

            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // Inserting parameters if any.
                if (argParameters == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameters)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Add return parameter.
                mySqlCommand.Parameters.Add(tmpReturnValueParam);

                // Open the connection.
                mySqlConnection.Open();

                // Execute the command.
                mySqlCommand.ExecuteNonQuery();

                // Get retun value.
                tmpReturnValue = tmpReturnValueParam.Value.ToString().Trim();
            }
            catch (Exception outerEx)
            {
                throw outerEx;
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave them open.

                // Close the Connection.
                mySqlConnection.Close();
            }

            return tmpReturnValue;
        }

        public static string performSingleParameterNonQueryStoredProcedureWithReturnValue(string argStoredProcedureName, SqlParameter argSqlParameter)
        {
            // This is a convienience call for the more complicated multi-parameter call.

            // We are just reducing the work the caller needs to do.
            List<SqlParameter> tmpParameterList = new List<SqlParameter>();
            tmpParameterList.Add(argSqlParameter);

            return DataAccessUtility.performMultiParameterNonQueryStoredProcedureWithReturnValue(argStoredProcedureName, tmpParameterList);
        }

        public static string performMultiParameterNonQueryStoredProcedureWithReturnValue(string argStoredProcedureName, List<SqlParameter> argParameters)
        {
            // This method will perform a non-query stored procedure.

            // Establishing data connection object outside of the inner try scope in order to close
            // it in the finally block in the event of an error.

            // Establish Retrun value.
            string tmpReturnValue = string.Empty;

            SqlParameter tmpReturnValueParam = new SqlParameter("@return_value", SqlDbType.NVarChar);
            tmpReturnValueParam.Value = "";
            tmpReturnValueParam.Size = 2000;
            tmpReturnValueParam.Direction = ParameterDirection.ReturnValue;

            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // Inserting parameters if any.
                if (argParameters == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameters)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Add return parameter.
                mySqlCommand.Parameters.Add(tmpReturnValueParam);

                // Open the connection.
                mySqlConnection.Open();

                // Execute the command.
                mySqlCommand.ExecuteNonQuery();

                // Get retun value.
                tmpReturnValue = tmpReturnValueParam.Value.ToString().Trim();
            }
            catch (Exception outerEx)
            {
                throw outerEx;
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave them open.

                // Close the Connection.
                mySqlConnection.Close();
            }

            return tmpReturnValue;
        }

        public static void performSingleParameterNonQueryStoredProcedure(string argStoredProcedureName, SqlParameter argSqlParameter)
        {
            // This is a convienience call for the more complicated multi-parameter call.

            // We are just reducing the work the caller needs to do.
            List<SqlParameter> tmpParameterList = new List<SqlParameter>();
            tmpParameterList.Add(argSqlParameter);

            DataAccessUtility.performMultiParameterNonQueryStoredProcedure(argStoredProcedureName, tmpParameterList);
        }

        public static void performMultiParameterNonQueryStoredProcedure(string argStoredProcedureName, List<SqlParameter> argParameters)
        {
            // This method will perform a non-query stored procedure.

            // Establishing data connection object outside of the inner try scope in order to close
            // it in the finally block in the event of an error.

            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Get the connection string from the web.config file.
            string myConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(myConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argStoredProcedureName, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.StoredProcedure;

                // Inserting parameters if any.
                if (argParameters == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameters)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                mySqlConnection.Open();

                // Execute the command.
                mySqlCommand.ExecuteNonQuery();
            }
            catch (Exception outerEx)
            {
                throw outerEx;
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave them open.

                // Close the Connection.
                mySqlConnection.Close();
            }
        }


        //---------------------------------------------------------------------------------------------------

        public static List<TableRow> convertSqlDataTableToListOfTableRows(SqlDataTable argSqlDataTable)
        {
            // This method will convert an SqlDataTable directly into a list of table rows for use
            // on the webform as tabular data.

            List<TableRow> tmpListOftableRows = new List<TableRow>();

            foreach (SqlDataRow sdr in argSqlDataTable.DataRows)
            {
                // Temporary table row to be added to the list.
                TableRow tmpTableRow = new TableRow();

                foreach (SqlDataCell sdc in sdr.DataCells)
                {
                    // Temporary table cell to be added to the table row.
                    TableCell tmpTableCell = new TableCell();

                    // Setting the cell text.
                    tmpTableCell.Text = sdc.Value;

                    // Adding the cell to the row.
                    tmpTableRow.Cells.Add(tmpTableCell);
                }

                // Adding the row to the list.
                tmpListOftableRows.Add(tmpTableRow);
            }

            // return the filled list.
            return tmpListOftableRows;
        }

        public static SqlDataCell getFirstCellFromSqlDataTable(SqlDataTable argTable)
        {
            return argTable.DataRows[0].DataCells[0];
        }

        public static SqlDataCell getLastCellFromSqlDataTable(SqlDataTable argTable)
        {
            return argTable.DataRows[argTable.DataRows.Count - 1].DataCells[argTable.DataRows[0].DataCells.Count - 1];
        }

        //---------------------------------------------------------------------------------------------------

        public static bool testMultiParameterStoredProcedureSucess(string argStoredProceudreName, List<SqlParameter> argSqlParameters)
        {
            // This method will perform a stored procedure and guage the sucess/failure
            // of the return.
            // THIS SHOULD ONLY BE USED WHEN TESTING A SINGLE RETURN THAT WILL BE BOOLEAN.

            // return value.
            bool tmpSucessBool = false;

            // create the return parameter and set it to output.
            SqlParameter tmpReturnParameter = new SqlParameter("@returncode", SqlDbType.Bit);
            tmpReturnParameter.Direction = ParameterDirection.Output;

            // add it to the list.
            argSqlParameters.Add(tmpReturnParameter);

            // Perform the call.
            DataAccessUtility.performMultiParameterNonQueryStoredProcedureWithOutputParameter(argStoredProceudreName, argSqlParameters);

            // try to assign the return parameter value to a boolean via parst.
            // wrapped in try block due to risky attempt to parse.
            try
            {
                tmpSucessBool = Boolean.Parse(tmpReturnParameter.Value.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Return the final product.
            return tmpSucessBool;
        }

        public static bool testSingleParameterStoredProcedureSucess(string argStoredProcedureName, SqlParameter argSqlParameter)
        {
            // Convienience call to the multiple parameter method.

            List<SqlParameter> tmpListOfParameters = new List<SqlParameter>();
            tmpListOfParameters.Add(argSqlParameter);

            return testMultiParameterStoredProcedureSucess(argStoredProcedureName, tmpListOfParameters);
        }

        //---------------------------------------------------------------------------------------------------

        // CRC Commented out ORACLE related code.
        /*
        public static SqlDataTable getDataTableFromOracleDatabaseViaQuery(string argQuery)
        {
            SqlDataTable tmpTable = new SqlDataTable();
            tmpTable.DataRows = new List<SqlDataRow>();

            // Create the connection object
            OracleConnection tmpOracleConnectionObject = new OracleConnection();

            // Specify the connect string
            string tmpConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get("PowerPlantOracleConnectionString");
            tmpOracleConnectionObject.ConnectionString = tmpConnectionString;

            try
            {
                // Open the connection
                tmpOracleConnectionObject.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Create the OracleCommand object
            OracleCommand tmpOracleCommandObject = new OracleCommand(argQuery);
            tmpOracleCommandObject.Connection = tmpOracleConnectionObject;
            tmpOracleCommandObject.CommandType = CommandType.Text;

            try
            {
                // Execute command, create OracleDataReader object
                OracleDataReader tmpOracleReaderObject = tmpOracleCommandObject.ExecuteReader();
                while (tmpOracleReaderObject.Read())
                {
                    // Create a table row to insert into the table.
                    SqlDataRow tmpRow = new SqlDataRow();
                    tmpRow.DataCells = new List<SqlDataCell>();

                    // Iterator for traversing the columns in the table row.
                    int i = 0;

                    while (i < tmpOracleReaderObject.FieldCount)
                    {
                        // Create a table cell to insert into the row.
                        SqlDataCell tmpCell = new SqlDataCell();

                        // Setting the name of the column, if any.
                        tmpCell.ColumnName = tmpOracleReaderObject.GetName(i).ToString();

                        // Setting the data type of the cell.
                        tmpCell.DataType = tmpOracleReaderObject.GetDataTypeName(i);

                        if (tmpOracleReaderObject.IsDBNull(i))
                        {
                            // If the column is null then we set the cell to an empty string.
                            tmpCell.Value = string.Empty;
                        }
                        else
                        {
                            // Get's the column data in its native format then changes it to
                            // a string and inserts that into the cell.
                            tmpCell.Value = tmpOracleReaderObject.GetValue(i).ToString().Trim();
                        }

                        // Adding the cell to the row.
                        tmpRow.DataCells.Add(tmpCell);

                        // Iterate.
                        i++;
                    }
                    // Adding the row to the table.
                    tmpTable.DataRows.Add(tmpRow);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Dispose OracleCommand object
                tmpOracleCommandObject.Dispose();

                // Close and Dispose OracleConnection object
                tmpOracleConnectionObject.Close();
                tmpOracleConnectionObject.Dispose();
            }

            return tmpTable;
        }

        public static void getDataTableFromGISOracleDatabaseViaQuery(string argQuery,List<OracleParameter> argOracleParameters)
        {
            //SqlDataTable tmpTable = new SqlDataTable();
            //tmpTable.DataRows = new List<SqlDataRow>();

            // Create the connection object
            OracleConnection tmpOracleConnectionObject = new OracleConnection();
          
            // Specify the connect string
            string tmpConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get("GISOracleConnectionString");
            tmpOracleConnectionObject.ConnectionString = tmpConnectionString;

            try
            {
                // Open the connection
                tmpOracleConnectionObject.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Create the OracleCommand object
            OracleCommand tmpOracleCommandObject = new OracleCommand(argQuery);
            tmpOracleCommandObject.Connection = tmpOracleConnectionObject;
            tmpOracleCommandObject.CommandType = CommandType.StoredProcedure;

            try
            {
                foreach (OracleParameter op in argOracleParameters)
                {
                    tmpOracleCommandObject.Parameters.Add(op);
                }
                tmpOracleCommandObject.ExecuteNonQuery();
                // Execute command, create OracleDataReader object
                //OracleDataReader tmpOracleReaderObject = tmpOracleCommandObject.ExecuteReader();
                //while (tmpOracleReaderObject.Read())
                //{
                //    // Create a table row to insert into the table.
                //    SqlDataRow tmpRow = new SqlDataRow();
                //    tmpRow.DataCells = new List<SqlDataCell>();

                //    // Iterator for traversing the columns in the table row.
                //    int i = 0;

                //    while (i < tmpOracleReaderObject.FieldCount)
                //    {
                //        // Create a table cell to insert into the row.
                //        SqlDataCell tmpCell = new SqlDataCell();

                //        // Setting the name of the column, if any.
                //        tmpCell.ColumnName = tmpOracleReaderObject.GetName(i).ToString();

                //        // Setting the data type of the cell.
                //        tmpCell.DataType = tmpOracleReaderObject.GetDataTypeName(i);

                //        if (tmpOracleReaderObject.IsDBNull(i))
                //        {
                //            // If the column is null then we set the cell to an empty string.
                //            tmpCell.Value = string.Empty;
                //        }
                //        else
                //        {
                //            // Get's the column data in its native format then changes it to
                //            // a string and inserts that into the cell.
                //            tmpCell.Value = tmpOracleReaderObject.GetValue(i).ToString().Trim();
                //        }

                //        // Adding the cell to the row.
                //        tmpRow.DataCells.Add(tmpCell);

                //        // Iterate.
                //        i++;
                //    }
                //    // Adding the row to the table.
                //    tmpTable.DataRows.Add(tmpRow);
                //}
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                // Dispose OracleCommand object
                tmpOracleCommandObject.Dispose();

                // Close and Dispose OracleConnection object
                tmpOracleConnectionObject.Close();
                tmpOracleConnectionObject.Dispose();
            }

            
        }
        */

        public static SqlDataTable getDataTableFromDB2DatabaseViaQuery(string argQuery)
        {
            SqlDataTable tmpReturnValue = new SqlDataTable();



            return tmpReturnValue;
        }

        //---------------------------------------------------------------------------------------------------

        public static DataTable parseCsvFileToDataTableObject(string argFilePathToCsvFile)
        {
            if (!File.Exists(argFilePathToCsvFile))
                return null;

            string tmpFullPath = Path.GetFullPath(argFilePathToCsvFile);
            string tmpFileName = Path.GetFileName(tmpFullPath);
            string tmpDirectory = Path.GetDirectoryName(tmpFullPath);

            //create the "database" connection string 
            string tmpConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
              + "Data Source=\"" + tmpDirectory + "\\\";"
              + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

            //create the database query
            string tmpQueryString = "SELECT * FROM " + tmpFileName;

            //create a DataTable to hold the query results
            DataTable tmpDataTable = new DataTable();

            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter tmpOleDbAdapter = new OleDbDataAdapter(tmpQueryString, tmpConnectionString);

            try
            {
                //fill the DataTable
                tmpOleDbAdapter.Fill(tmpDataTable);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            tmpOleDbAdapter.Dispose();

            return tmpDataTable;
        }

        public static SqlDataTable performFreeFormQuery(string argConnectionString, string argQueryString, List<SqlParameter> argParameterList)
        {
            SqlDataTable tmpTable = new SqlDataTable();
            tmpTable.DataRows = new List<SqlDataRow>();

            // Establishing data connection object outside of the inner try scope in order to close
            // it in the finally block in the event of an error.

            // Establish the connection object.
            SqlConnection mySqlConnection;

            // Create a new SQL Connection using that connection string.
            mySqlConnection = new System.Data.SqlClient.SqlConnection(argConnectionString);

            try
            {
                // Establish data objects.
                SqlCommand mySqlCommand;
                SqlDataReader mySqlDataReader;

                // Create a new SQL Command and give it the name of the stored procedure and the SQL connection.
                mySqlCommand = new System.Data.SqlClient.SqlCommand(argQueryString, mySqlConnection);

                // Inform the command that it is a stored procedure.
                mySqlCommand.CommandType = CommandType.Text;

                // We now add any available parameters to the command.

                if (argParameterList == null)
                {
                    // Do nothing, it's null.
                }
                else
                {
                    // Else add the parameters!
                    foreach (SqlParameter sp in argParameterList)
                    {
                        mySqlCommand.Parameters.Add(sp);
                    }
                }

                // Open the connection.
                mySqlConnection.Open();

                // Set up the DataReader as a function of the SQL Command.
                mySqlDataReader = mySqlCommand.ExecuteReader();

                // This will execute multiple times based on the number of rows.
                while (mySqlDataReader.Read())
                {
                    // Create a table row to insert into the list.
                    SqlDataRow tmpRow = new SqlDataRow();
                    tmpRow.DataCells = new List<SqlDataCell>();

                    // Iterator for traversing the columns in the table row.
                    int i = 0;

                    while (i < mySqlDataReader.FieldCount)
                    {
                        // Create a table cell to insert into the row.
                        SqlDataCell tmpCell = new SqlDataCell();

                        // Setting the name of the column, if any.
                        tmpCell.ColumnName = mySqlDataReader.GetName(i).ToString();

                        // Setting the data type of the cell.
                        tmpCell.DataType = mySqlDataReader.GetDataTypeName(i);

                        if (mySqlDataReader.IsDBNull(i))
                        {
                            // If the column is null then we set the cell to an empty string.
                            tmpCell.Value = string.Empty;
                        }
                        else
                        {
                            // Get's the column data in its native format then changes it to
                            // a string and inserts that into the cell.
                            tmpCell.Value = mySqlDataReader.GetValue(i).ToString().Trim();
                        }

                        // Adding the cell to the row.
                        tmpRow.DataCells.Add(tmpCell);

                        // Iterate.
                        i++;
                    }

                    // Adding the table row to the list.
                    tmpTable.DataRows.Add(tmpRow);
                }
                // End of datareader reads.

                // Close the Data Reader.
                mySqlDataReader.Close();
            }
            catch (Exception outerEx)
            {
                throw outerEx;
            }
            finally
            {
                // The connection close must be in the finally block so that an error
                // will not leave it open.

                // Close the Connection.
                mySqlConnection.Close();
            }

            // Return the list of table rows.
            return tmpTable;
        }

        //---------------------------------------------------------------------------------------------------

        public static int parseDataCellValueToInt(string argCellValue)
        {
            int tmpReturnValue = 0;

            if (argCellValue.Trim() == string.Empty)
            {
                tmpReturnValue = 0;
            }
            else
            {
                try
                {
                    tmpReturnValue = Int32.Parse(argCellValue);
                }
                catch (Exception ex)
                {
                    try
                    {
                        tmpReturnValue = ((int)Decimal.Parse(argCellValue));
                    }
                    catch (Exception ex2)
                    {
                        tmpReturnValue = 0;
                    }
                }
            }

            return tmpReturnValue;
        }

        public static float parseDataCellValueToFloat(string argCellValue)
        {
            float tmpReturnValue = 0;

            if (argCellValue.Trim() == string.Empty)
            {
                tmpReturnValue = 0;
            }
            else
            {
                try
                {
                    tmpReturnValue = float.Parse(argCellValue);
                }
                catch (Exception ex)
                {
                    tmpReturnValue = 0;
                }
            }

            return tmpReturnValue;
        }

        public static decimal parseDataCellValueDecimal(string argCellValue)
        {
            decimal tmpReturnValue = 0;

            if (argCellValue.Trim() == string.Empty)
            {
                tmpReturnValue = 0;
            }
            else
            {
                try
                {
                    tmpReturnValue = decimal.Parse(argCellValue);
                    tmpReturnValue = Math.Round(tmpReturnValue, 2);
                }
                catch (Exception ex)
                {
                    tmpReturnValue = 0;
                }
            }

            return tmpReturnValue;
        }

        public static string CurrentUser
        {
            // Convienience call for the current user.
            get
            {
                return HttpContext.Current.User.Identity.Name.ToString().ToUpper();
            }
        }

        public static string TechExpertFullName()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertFullName");
        }

        public static string TechExpertPhone()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertPhone");
        }

        public static string TechExpertEmail()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertEmail");
        }

        public static string TechExpertCorpIDPrimary()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertCorpID");
        }

        public static string TechExpertCorpIDSecondary()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertCorpID2");
        }

        public static string TechExpertCorpIDTertiary()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("TechExpertCorpID3");
        }

        public static string TechExpertContactStatement()
        {
            return ("For assistance: Please contact this application's technical expert, " + DataAccessUtility.TechExpertFullName() + " either via email at " + DataAccessUtility.TechExpertEmail() + " or via phone at " + DataAccessUtility.TechExpertPhone() + ".");
        }

        public static string GisTargetEditLink()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("GisTargetEditLink");
        }

        public static string GisTargetViewLink()
        {
            return System.Configuration.ConfigurationManager.AppSettings.Get("GisTargetViewLink");
        }
    }
}
