using System;
using System.Collections.Generic;
using System.Text;
using System.Data;  // ado.net stuff
using System.Data.OleDb;
 
namespace DbProxy
{
 
    public class OleDbProxy
    {
        private string dbConnString;
 
        public OleDbProxy(string db_conn_str_)
        {
            dbConnString = db_conn_str_;
        }
 
        /// <summary>
        /// purpose:
        ///     - performs the sql command 
        ///         - eg CREATE, DROP, INSERT, UPDATE, ...
        /// requirements:
        ///     - sql_ is  a valid, sql cmd
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the cmd
        ///     - result >= 0
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="sql_">the non query sql command</param>
        /// <returns></returns>
        public virtual int NonQuery(string sql_)
        {
            int result = 0;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql_, conn);
                result = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return Math.Abs(result);
        }
 
        /// <summary>
        /// purpose:
        ///     - performs the sql command 
        ///         - eg CREATE, DROP, INSERT, UPDATE, ...
        /// requirements:
        ///     - sql_ is  a valid, sql cmd
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the cmd
        ///     - result >= 0
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="sql_">the non query sql command</param>
        /// <returns></returns>
        public virtual object ExecScalar(string sql_)
        {
            object result = null;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql_, conn);
                result = cmd.ExecuteScalar();
                conn.Close();
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return result;
        }
 
        /// <summary>
        /// purpose:
        ///     - performs the sql query 'query_'
        /// requirements:
        ///     - query_ is  a valid, readonly query
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the query
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <returns></returns>
        public virtual DataSet ReadOnlyQuery(string query_)
        {
            bool read_query = false;
            string write_query_type = "none";
            try
            {
                string query2 = query_.ToLower().TrimStart();
                int select_pos = query2.IndexOf("select");
                if (select_pos >= 0)
                {
                    read_query = true;
                    int insert_pos = query2.IndexOf("insert");
                    int update_pos = query2.IndexOf("update");
                    int delete_pos = query2.IndexOf("delete");
 
                    if (insert_pos > 0 && select_pos > insert_pos)
                    {
                        write_query_type = "insert";
                        read_query = false;
                    }
                    if (update_pos > 0 && select_pos > update_pos)
                    {
                        write_query_type = "update";
                        read_query = false;
                    }
                    if (delete_pos > 0 && select_pos > delete_pos)
                    {
                        write_query_type = "delete";
                        read_query = false;
                    }
                }
 
                if (read_query)
                {
                    return Query(query_);
                }
                else
                {
                    throw new Exception("Only Read queries are allowed: " + write_query_type);
                }
            }
            catch (Exception ex_)
            {
                //throw new Exception(ex_.Message);
                throw ex_;
            }
        }
 
        /// <summary>
        /// purpose:
        ///     - performs the sql query 'query_'
        /// requirements:
        ///     - query_ is  a valid sql query
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the result of the query
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <returns></returns>
        public DataSet Query(string query_)
        {
            OleDbConnection conn = null;
            DataSet ds = new DataSet("QueryResults");
            try
            {
                conn = GetConnection();
                OleDbDataAdapter adapter = new OleDbDataAdapter(query_, conn);
                adapter.FillSchema(ds, SchemaType.Mapped, ResultTableName);
                rowsReturned = adapter.Fill(ds, ResultTableName);
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return ds;
        }
 
        /// <summary>
        /// purpose:
        ///     - updates the db from the contents of dt_
        /// requirements:
        ///     - query_ is  a valid sql query which for the db table coresponding to dt_
        ///     - eg:
        ///         - dt_ has name "Orders" and contains columns OrdNum, ClientId, Total etc
        ///         - query == "SELECT OrdNum, ClientId, Total FROM Orders"
        ///     - are able to use a 'CommandBuilder' to create the insert, update & delete commands
        ///     - the user has sufficient privileges to perform the operation
        /// promise:
        ///     - returns the no. of rows updated in the db
        ///     - if query_ is invalid, throws System.Exception
        ///     - if user is invalid, throws System.Exception 
        /// </summary>
        /// <param name="query_"></param>
        /// <param name="dt_"></param>
        /// <returns></returns>
        public int Update(string query_, DataTable dt_)
        {
            int rows = 0;
            OleDbConnection conn = null;
            try
            {
                conn = GetConnection();
                OleDbDataAdapter adapter = new OleDbDataAdapter(query_, conn);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
                rows = adapter.Update(dt_);
            }
            catch (Exception ex_)
            {
                throw new Exception(ex_.Message);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return rows;
        }
 
        /// <summary>
        /// returns a new connection to the underlying db
        /// user is responsible for opening & closing the connection
        /// </summary>
        /// <returns></returns>
        public OleDbConnection GetConnection()
        {
            OleDbConnection db_connection = null;
            try
            {
                db_connection = new System.Data.OleDb.OleDbConnection(dbConnString);
            }
            catch (Exception)
            {
            }
            return db_connection;
        }
 
 
 
        /// <summary>
        /// returns the name that the 'result table' will have
        /// ie the only table in the data set returned by a call to one of the query fns
        /// </summary>
        public string ResultTableName
        {
            get
            {
                return resultTablename;
            }
        }
 
        private string resultTablename = "ResultSet";
 
        /// <summary>
        /// no. of rows returned in last valid query
        /// </summary>
        /// <returns></returns>
        public string RowCount()
        {
            return rowsReturned.ToString();
        }
 
        private int rowsReturned = 0;
    }
 
}