Implementing Model-View-Presenter in ASP.NET

using System;
using System.Collections;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Data.Common;
using SubSonic.Utilities;

namespace SubSonic
{

    public class OracleDataProvider : DataProvider
    {
        private const string DELIMITER = "|";
        private const string TABLE_NAME_PARAMETER = ":tableName";
        private const string COLUMN_NAME_PARAMETER = ":columnName";
        private const string MAP_SUFFIX = ":mapSuffix";
        private const string OBJECT_NAME_PARAMETER = ":objectName";
        private const string START_PARAMETER = ":start";
        private const string END_PARAMETER = ":end";

        override internal DbConnection CreateConnection()
        {
            return CreateConnection(DefaultConnectionString);
        }

        override internal DbConnection CreateConnection(string newConnectionString)
        {
            OracleConnection retVal = new OracleConnection(newConnectionString);
            retVal.Open();
            return retVal;
        }

        static void AddParams(OracleCommand cmd, QueryCommand qry)
        {
            if (qry.Parameters != null)
            {
                foreach (QueryParameter param in qry.Parameters)
                {
                    OracleParameter sqlParam = new OracleParameter();
                    sqlParam.DbType = param.DataType;
                    sqlParam.OracleType = GetOracleType(param.DataType);
                    sqlParam.ParameterName = param.ParameterName;
                    sqlParam.Value = param.ParameterValue;
                    cmd.Parameters.Add(sqlParam);
                }
            }
        }

        public static OracleType GetOracleType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.AnsiString: return OracleType.VarChar;
                case DbType.AnsiStringFixedLength: return OracleType.Char;
                case DbType.Binary: return OracleType.Raw;
                case DbType.Boolean: return OracleType.Byte;
                case DbType.Byte: return OracleType.Byte;
                case DbType.Currency: return OracleType.Number;
                case DbType.Date: return OracleType.DateTime;
                case DbType.DateTime: return OracleType.DateTime;
                case DbType.Decimal: return OracleType.Number;
                case DbType.Double: return OracleType.Double;
                case DbType.Guid: return OracleType.Raw;
                case DbType.Int16: return OracleType.Int16;
                case DbType.Int32: return OracleType.Int32;
                case DbType.Int64: return OracleType.Number;
                case DbType.Object: return OracleType.Blob;
                case DbType.SByte: return OracleType.SByte;
                case DbType.Single: return OracleType.Float;
                case DbType.String: return OracleType.NVarChar;
                case DbType.StringFixedLength: return OracleType.NChar;
                case DbType.Time: return OracleType.DateTime;
                case DbType.UInt16: return OracleType.UInt16;
                case DbType.UInt32: return OracleType.UInt32;
                case DbType.UInt64: return OracleType.Number;
                case DbType.VarNumeric: return OracleType.Number;

                default:
                    {
                        return OracleType.VarChar;
                    }
            }
        }

        public override void SetParameter(IDataReader rdr, StoredProcedure.Parameter par)
        {
            par.DBType = GetDbType(rdr[OracleSchemaVariable.DATA_TYPE].ToString());
            string sMode = rdr[OracleSchemaVariable.MODE].ToString();
            if (sMode == OracleSchemaVariable.MODE_INOUT) {
                par.Mode = ParameterDirection.InputOutput;
            }
            par.Name = rdr[OracleSchemaVariable.NAME].ToString();
        }

        public override string GetParameterPrefix()
        {
            return OracleSchemaVariable.PARAMETER_PREFIX;
        }

        protected override string AdjustUpdateSql(Query qry, TableSchema.Table table, string updateSql)
        {
            return updateSql;
        }

        public override IDataReader GetReader(QueryCommand qry)
        {
            AutomaticConnectionScope automaticConnectionScope = new AutomaticConnectionScope(this);
            OracleCommand cmd = new OracleCommand(qry.CommandSql);
            cmd.CommandType = qry.CommandType;
            cmd.CommandTimeout = qry.CommandTimeout;

            AddParams(cmd, qry);
            cmd.Connection = (OracleConnection)automaticConnectionScope.Connection;

            IDataReader rdr;

            try
            {
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (OracleException x)
            {
                automaticConnectionScope.Dispose();
                throw x;
            }

            return rdr;
        }

        public override T GetDataSet<T>(QueryCommand qry)
        {
            T ds = new T();
            OracleCommand cmd = new OracleCommand(qry.CommandSql);
            cmd.CommandType = qry.CommandType;
            cmd.CommandTimeout = qry.CommandTimeout;
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            using(AutomaticConnectionScope conn = new AutomaticConnectionScope(this))
            {
                cmd.Connection = (OracleConnection)conn.Connection;
                AddParams(cmd, qry);
                da.Fill(ds);

                cmd.Dispose();
                da.Dispose();

                return ds;
            }
        }

        public override object ExecuteScalar(QueryCommand qry)
        {
            using (AutomaticConnectionScope automaticConnectionScope = new AutomaticConnectionScope(this))
            {
                OracleCommand cmd = new OracleCommand(qry.CommandSql);
                cmd.CommandType = qry.CommandType;
                cmd.CommandTimeout = qry.CommandTimeout;
                AddParams(cmd, qry);
                cmd.Connection = (OracleConnection)automaticConnectionScope.Connection;
                return cmd.ExecuteScalar();
            }
        }

        public override int ExecuteQuery(QueryCommand qry)
        {
            using (AutomaticConnectionScope automaticConnectionScope = new AutomaticConnectionScope(this))
            {
                OracleCommand cmd = new OracleCommand(qry.CommandSql);
                cmd.CommandType = qry.CommandType;
                cmd.CommandTimeout = qry.CommandTimeout;
                AddParams(cmd, qry);
                cmd.Connection = (OracleConnection)automaticConnectionScope.Connection;      
                return cmd.ExecuteNonQuery();
            }
        }

        public override TableSchema.Table GetTableSchema(string tableName, TableType tableType)
        {
            TableSchema.TableColumnCollection columns = new TableSchema.TableColumnCollection();
            TableSchema.Table tbl = new TableSchema.Table(tableName, tableType, this);
            //tbl.ClassName = Convention.ClassName(tableName);
            string sql = TABLE_COLUMN_SQL;            
            QueryCommand cmd = new QueryCommand(sql, Name);
            cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);
            TableSchema.TableColumn column;
            string scale = "";
            string precision = "";

            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                //get information about both the table and it's columns                
                while (rdr.Read())
                {
                    column = new TableSchema.TableColumn(tbl);
                    column.ColumnName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString();

                    scale = rdr[OracleSchemaVariable.NUMBER_SCALE].ToString();
                    precision = rdr[OracleSchemaVariable.NUMBER_PRECISION].ToString();

                    column.NumberScale = 0;
                    column.NumberPrecision = 0;

                    if (!String.IsNullOrEmpty(scale) && scale != "0")
                    {
                        column.NumberScale = int.Parse(scale);
                    }

                    if (!String.IsNullOrEmpty(precision) && precision != "0")
                    {
                        column.NumberPrecision = int.Parse(precision);
                    }

                   // column.DataType = GetDbType(rdr[OracleSchemaVariable.DATA_TYPE].ToString().ToLower());
                    column.DataType = GetDbTypeOracle(rdr[OracleSchemaVariable.DATA_TYPE].ToString().ToLower(), column.NumberScale, column.NumberPrecision);
                    column.AutoIncrement = false;
                    int maxLength;
                    int.TryParse(rdr[OracleSchemaVariable.MAX_LENGTH].ToString(), out maxLength);
                    column.MaxLength = maxLength;
                    column.IsNullable = Utility.IsMatch(rdr[OracleSchemaVariable.IS_NULLABLE].ToString(), "Y");
                    column.IsReadOnly = false;
                    columns.Add(column);
                }
            }

            string sql2 = INDEX_SQL;
            cmd.CommandSql = sql2;
            //cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);

            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                while (rdr.Read())
                {
                    string colName;
                    colName = rdr[OracleSchemaVariable.COLUMN_NAME].ToString();
                    string constraintType;
                    constraintType = rdr[OracleSchemaVariable.CONSTRAINT_TYPE].ToString();
                    column = columns.GetColumn(colName);

                    if (constraintType == SqlSchemaVariable.PRIMARY_KEY)
                    {
                        column.IsPrimaryKey = true;
                    }
                    else if (constraintType == SqlSchemaVariable.FOREIGN_KEY)
                    {
                        column.IsForeignKey = true;
                    }
                    //HACK: Allow second pass naming adjust based on whether a column is keyed
                    column.ColumnName = column.ColumnName;
                }
                rdr.Close();
            }
            tbl.Columns = columns;
            return tbl;
        }

        public override IDataReader GetSPParams(string spName)
        {
            QueryCommand cmd = new QueryCommand(SP_PARAM_SQL, Name);
            cmd.AddParameter(OBJECT_NAME_PARAMETER, spName);
            return DataService.GetReader(cmd);
        }

        public override string[] GetSPList()
        {
            QueryCommand cmd = new QueryCommand(SP_SQL, Name);
            StringBuilder sList = new StringBuilder();
            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                bool isFirst = true;
                while (rdr.Read())
                {
                    if(!isFirst)
                    {
                        sList.Append(DELIMITER);
                    }
                    isFirst = false;
                    sList.Append(rdr[0]);
                }
                rdr.Close();
                return sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            }
        }

        public override string[] GetViewNameList()
        {
            QueryCommand cmd = new QueryCommand(VIEW_SQL, Name);
            StringBuilder sList = new StringBuilder();
            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                while (rdr.Read())
                {
                    string viewName = rdr[SqlSchemaVariable.NAME].ToString();

                    if (String.IsNullOrEmpty(ViewStartsWith) || viewName.StartsWith(ViewStartsWith))
                    {
                        sList.Append(rdr[SqlSchemaVariable.NAME]);
                        sList.Append("|");
                    }
                }
                rdr.Close();
                string[] strArray = sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                Array.Sort(strArray);
                return strArray;
            }
        }

        public override string[] GetTableNameList()
        {
            QueryCommand cmd = new QueryCommand(TABLE_SQL, Name);
            StringBuilder sList = new StringBuilder();
            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                bool isFirst = true;
                while (rdr.Read())
                {
                    if(!isFirst)
                    {
                        sList.Append(DELIMITER);
                    }
                    isFirst = false;
                    sList.Append(rdr[SqlSchemaVariable.NAME]);
                }
                rdr.Close();
                string[] strArray = sList.ToString().Split(DELIMITER.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                Array.Sort(strArray);
                return strArray;
            }
        }

        public override ArrayList GetPrimaryKeyTableNames(string tableName)
        {
            QueryCommand cmd = new QueryCommand(GET_PRIMARY_KEY_SQL, Name);
            cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);
            ArrayList list = new ArrayList();

            using (IDataReader rdr = DataService.GetReader(cmd))
            {
                while (rdr.Read())
                {
                    list.Add(new string[] { rdr[SqlSchemaVariable.TABLE_NAME].ToString(), rdr[SqlSchemaVariable.COLUMN_NAME].ToString() });
                }
                rdr.Close();
            }
            return list;
        }

        public override TableSchema.Table[] GetPrimaryKeyTables(string tableName)
        {
            QueryCommand cmd = new QueryCommand(GET_PRIMARY_KEY_SQL, Name);
            cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);
            ArrayList names = new ArrayList();
            //ArrayList list = new ArrayList();

            using (IDataReader rdr = GetReader(cmd))
            {
                while (rdr.Read())
                {
                    names.Add(rdr[SqlSchemaVariable.TABLE_NAME].ToString());
                }
                rdr.Close();
            }

            if (names.Count > 0)
            {
                TableSchema.Table[] tables = new TableSchema.Table[names.Count];

                for (int i = 0; i < names.Count; i++)
                {
                    tables[i] = DataService.GetSchema((string)names[i], Name, TableType.Table);
                }
                return tables;
            }
            return null;
        }

        public override string GetForeignKeyTableName(string fkColumnName, string tableName)
        {
            QueryCommand cmd = new QueryCommand(GET_FOREIGN_KEY_SQL, Name);
            cmd.AddParameter(COLUMN_NAME_PARAMETER, fkColumnName);
            cmd.AddParameter(TABLE_NAME_PARAMETER, tableName);

            object result = DataService.ExecuteScalar(cmd);
            if (result == null)
            {
                return null;
            }
            return result.ToString();
        }

        public override string GetForeignKeyTableName(string fkColumnName)
        {
            QueryCommand cmd = new QueryCommand(GET_TABLE_SQL, Name);
            cmd.AddParameter(COLUMN_NAME_PARAMETER, fkColumnName);

            object result = DataService.ExecuteScalar(cmd);
            if (result == null)
            {
                return null;
            }
            return result.ToString();
        }


        // os added to distinguish for ORACLE between float and integer
        // REM : to be accurate, we should test dataPrecision to determine what kind of int
        // we deal with 32, 64, while not extactly true cause, for instance 77000 is 5 of dataprecision
        // and doesn't fit an int16)
        // 19/03/07 : due to mismatch casting between integer and decimal data type (i.e 
        // what is the real type returned by the OracleClient assembly which is decimal and
        // returned type from this function, I decided to return always for number type,
        // decimal). Works better.
        public static DbType GetDbTypeOracle(string sqlType, int dataScale, int dataPrecision)
        {
            switch (sqlType)
            {
                case "varchar2":
                case "varchar":
                case "char":
                case "nchar":
                case "nvarchar2":
                case "rowid":
                case "nclob":
                case "clob":
                    return DbType.String;
                case "number":
                    return DbType.Decimal;
                //if (dataScale > 0)
                //{
                //    return DbType.Single;
                //}
                //else
                //{
                //    return DbType.Int32;
                //}
                case "float":
                    return DbType.Double;
                case "raw":
                case "long raw":
                case "blob":
                    return DbType.Binary;
                case "date":
                case "timestamp":
                    return DbType.DateTime;
                default:
                    return DbType.String;
            }

        }


        public override DbType GetDbType(string sqlType)
        {
            switch(sqlType)
            {
                case "number":
                    return DbType.Single;
                case "float":
                    return DbType.Double;
                case "varchar2":
                case "varchar":
                case "char":
                case "nchar":
                case "nvarchar2":
                case "rowid":
                case "nclob":
                case "blob":
                    return DbType.String;
                case "raw":
                case "long raw":
                    return DbType.Binary;
                case "date":
                case "timestamp":
                    return DbType.DateTime;
                default:
                    return DbType.String;
            }
        }


        public override IDbCommand GetCommand(QueryCommand qry)
        {
            OracleCommand cmd = new OracleCommand(qry.CommandSql);
            AddParams(cmd, qry);
            return cmd;
        }

        #region Schema Bits

        private const string MANY_TO_MANY_LIST = "SELECT b.table_name FROM user_constraints a, user_cons_columns b " +
                                                  "WHERE a.table_name = :tableName " + 
                                                  "AND a.constraint_type = 'R' " + 
                                                  "AND a.r_constraint_name = b.constraint_name " +
                                                  "AND b.table_name like '%' + :mapSuffix";

        const string TABLE_COLUMN_SQL = "SELECT user, a.table_name, a.column_name, a.column_id, a.data_default, " +
                                        "       a.nullable, a.data_type, a.char_length, a.data_precision, a.data_scale " +
                                        "  FROM user_tab_columns a " +
                                        " WHERE a.table_name = :tableName";


        const string SP_PARAM_SQL = @"SELECT a.object_name, a.object_type, b.position, b.in_out, 
                                    b.argument_name, b.data_type, b.char_length, b.data_precision, b.data_scale 
                                    FROM user_objects a, user_arguments b 
                                    WHERE a.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') 
                                    AND a.object_id = b.object_id 
                                    AND a.object_name = :objectName";


        const string SP_SQL = @"SELECT a.object_name, a.object_type, a.created, a.last_ddl_time 
    `                           FROM user_objects a 
                                WHERE a.object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') ";


        const string TABLE_SQL = "SELECT a.table_name AS Name FROM user_tables a";

        const string VIEW_SQL = "SELECT a.view_name AS Name FROM user_views a";

        const string INDEX_SQL =    "SELECT b.table_name, b.column_name, " +
                                    "       DECODE (a.constraint_type, " +
                                    "               'R', 'FOREIGN KEY', " +
                                    "               'P', 'PRIMARY KEY', " +
                                    "               'UNKNOWN' " +
                                    "              ) constraint_type " +
                                    "  FROM user_constraints a, user_cons_columns b " +
                                    " WHERE a.constraint_name = b.constraint_name " +
                                    "   AND a.constraint_type IN ('R', 'P') " +
                                    "   AND b.table_name = :tableName ";

        const string GET_TABLE_SQL =    "SELECT b.table_name " +
                                        "  FROM user_constraints a, user_cons_columns b " +
                                        " WHERE a.constraint_name = b.constraint_name " +
                                        "   AND a.constraint_type IN ('R', 'P') " +
                                        "   AND b.column_name = :columnName " +
                                        "   AND a.constraint_type = 'P' ";

        const string GET_FOREIGN_KEY_SQL =  "SELECT d.table_name " +
                                            "  FROM user_cons_columns c, user_cons_columns d, user_constraints e " +
                                            " WHERE d.constraint_name = e.r_constraint_name " +
                                            "   AND c.constraint_name = e.constraint_name " +
                                            "   AND d.column_name = :columnName " +
                                            "   AND e.table_name = :tableName ";
        
        const string GET_PRIMARY_KEY_SQL =  "SELECT e.table_name AS TableName, c.column_name AS ColumnName " +
                                            "  FROM user_cons_columns c, user_cons_columns d, user_constraints e " +
                                            " WHERE d.constraint_name = e.r_constraint_name " +
                                            "   AND c.constraint_name = e.constraint_name " +
                                            "   AND d.table_name = :tableName ";

        #endregion

        /// <summary>
        /// Executes a transaction using the passed-commands
        /// </summary>
        /// <param name="commands"></param>
        public override void ExecuteTransaction(QueryCommandCollection commands)
        {
            //make sure we have at least one
            if (commands.Count > 0)
            {
                OracleCommand cmd = null;

                //a using statement will make sure we close off the connection
                using (AutomaticConnectionScope conn = new AutomaticConnectionScope(this))
                {
                    //open up the connection and start the transaction

                    if (conn.Connection.State == ConnectionState.Closed)
                        conn.Connection.Open();

                    OracleTransaction trans = (OracleTransaction)conn.Connection.BeginTransaction();

                    foreach (QueryCommand qry in commands)
                    {
                        cmd = new OracleCommand(qry.CommandSql, (OracleConnection)conn.Connection);
                        cmd.CommandType = qry.CommandType;
                        
                        AddParams(cmd, qry);

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (OracleException x)
                        {
                            //if there's an error, roll everything back
                            trans.Rollback();

                            //clean up
                            conn.Connection.Close();
                            cmd.Dispose();

                            //throw the error retaining the stack.
                            throw new Exception(x.Message);
                        }
                    }
                    //if we get to this point, we're good to go
                    trans.Commit();

                    //close off the connection
                    conn.Connection.Close();
                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }
                }
            }
            else
            {
                throw new Exception("No commands present");
            }
        }

        #region SQL Builders

        public override string GetSql(Query qry)
        {
            string result = String.Empty;
            switch (qry.QueryType)
            {
                case QueryType.Select:
                    result = GetSelectSql(qry);
                    break;
                case QueryType.Update:
                    result = GetUpdateSql(qry);
                    break;
                case QueryType.Insert:
                    result = GetInsertSql(qry);
                    break;
                case QueryType.Delete:
                    result = GetDeleteSql(qry);
                    break;
            }
            return result;
        }


        /// <summary>
        /// Creates a SELECT statement based on the Query object settings
        /// </summary>
        /// <returns></returns>
        public override string GetSelectSql(Query qry)
        {
            TableSchema.Table table = qry.Schema;

            string select = SqlFragment.SELECT;
            select += qry.IsDistinct ? SqlFragment.DISTINCT : String.Empty;
            StringBuilder order = new StringBuilder();
            string query;
            string columns;

            if (!String.IsNullOrEmpty(qry.SelectList) && qry.SelectList.Trim().Length >= 2)
            {
                columns = qry.SelectList;
            }
            else
            {
                columns = GetQualifiedSelect(table);
            }
            string where = BuildWhere(qry);

            //Finally, do the orderby 
            if (qry.OrderByCollection.Count > 0)
            {
                for (int j = 0; j < qry.OrderByCollection.Count; j++)
                {
                    string orderString = qry.OrderByCollection[j].OrderString;
                    if (!String.IsNullOrEmpty(orderString))
                    {
                        order.Append(orderString);
                        if (j + 1 != qry.OrderByCollection.Count)
                        {
                            order.Append(", ");
                        }
                    }
                }
            }
            else
            {
                if(table.PrimaryKey != null)
                {
                    order.Append(OrderBy.Asc(table.PrimaryKey.ColumnName).OrderString);
                }
            }

            if(order.Length > 0)
            {
                order = order.Replace("[", String.Empty);
                order = order.Replace("]", String.Empty);
            }

            if (qry.PageIndex < 0)
            {
                query = select + columns + " FROM " + table.Name + where + order;
            }
            else
            {
                int start = qry.PageIndex * qry.PageSize;
                int end = (qry.PageIndex + 1) * qry.PageSize;
                string cteFormat = "with __PagedTable ({0}, __RowIndex) as (Select {1}, Row_Number() OVER ({2}) as '__RowIndex' FROM [{3}] {4})Select {0}, __RowIndex FROM __PagedTable where __RowIndex >= {5} and __RowIndex < {6} Order by __RowIndex";

                query = string.Format(cteFormat, columns.Replace("[" + table.Name + "].", String.Empty), columns, order, table.Name, where, start, end);
            }
            return query;
        }

        /// <summary>
        /// Returns a qualified list of columns ([Table].[Column])
        /// </summary>
        /// <returns></returns>
        protected static string GetQualifiedSelect(TableSchema.Table table)
        {
            StringBuilder sb = new StringBuilder();
            bool isFirst = true;
            foreach (TableSchema.TableColumn tc in table.Columns)
            {
                if(!isFirst)
                {
                    sb.Append(", ");
                }
                isFirst = false;
                sb.AppendFormat("{0}.{1}", table.Name, tc.ColumnName);
            }
            return sb.ToString();
        }

        /// <summary>
        /// Loops the TableColums[] array for the object, creating a SQL string
        /// for use as an INSERT statement
        /// </summary>
        /// <returns></returns>
        public override string GetInsertSql(Query qry)
        {
            TableSchema.Table table = qry.Schema;

            //split the TablNames and loop out the SQL
            StringBuilder insertSQL = new StringBuilder();
            insertSQL.Append("INSERT INTO ");
            insertSQL.Append(table.Name);
            //string client = DataService.GetClientType();

            StringBuilder cols = new StringBuilder();
            StringBuilder pars = new StringBuilder();
            //OS not used with ORACLE bool primaryKeyIsGuid = false;
            //string primaryKeyName = "";

            //if table columns are null toss an exception
            bool isFirst = true;

            foreach (TableSchema.TableColumn col in table.Columns)
            {
                if (!col.AutoIncrement && !col.IsReadOnly)
                {
                    if (!isFirst)
                    {
                        cols.Append(",");
                        pars.Append(",");
                    }
                    isFirst = false;
                    cols.Append(col.ColumnName);
                    pars.Append(MakeParam(col.ColumnName));
                }
            }
            insertSQL.Append("(");
            insertSQL.Append(cols);
            insertSQL.Append(") ");

            // OS : can't user for ORACLE string getInsertValue = " SELECT SCOPE_IDENTITY() as newID;";

            insertSQL.Append("VALUES(");
            insertSQL.Append(pars);
            insertSQL.Append(")");

            // OS : can't user for ORACLE insertSQL += " SELECT SCOPE_IDENTITY() as newID;";
            // OS : it seems this method is not used, however if used later, witth ORACLE one 
            // could use the RETURNING clause of and INSERT statement to get a new TRIGGER id generated
            // sequence or counter for primary key purpose
            // insertSQL += getInsertValue;

            return insertSQL.ToString();
        }


        #endregion

        #region Command Builders

        public override QueryCommand BuildCommand(Query qry)
        {
            QueryCommand cmd = null;
            switch (qry.QueryType)
            {
                case QueryType.Select:
                    cmd = BuildSelectCommand(qry);
                    break;
                case QueryType.Update:
                    cmd = BuildUpdateCommand(qry);
                    break;
                case QueryType.Insert:
                    cmd = null;
                    break;
                case QueryType.Delete:
                    cmd = BuildDeleteCommand(qry);
                    break;
            }
            if (cmd != null)
            {
                cmd.ProviderName = Name;
            }
            return cmd;
        }

        #endregion

        #region SQL Scripters
        public override string ScriptData(string tableName) {
            return ScriptData(tableName, DataService.Provider.Name);
        }
        public override string ScriptData(string tableName,string providerName)
        {
            StringBuilder fieldList = new StringBuilder();
            StringBuilder statements = new StringBuilder();
            StringBuilder result = new StringBuilder();
            StringBuilder insertStatement = new StringBuilder();

            insertStatement.Append("INSERT INTO ");
            insertStatement.Append(tableName);
            insertStatement.Append(" ");

            //pull the schema for this table
            TableSchema.Table table = Query.BuildTableSchema(tableName,providerName);

            //build the insert list.
            bool isFirst = true;
            foreach (TableSchema.TableColumn col in table.Columns)
            {
                if(!isFirst)
                {
                    fieldList.Append(",");
                }
                isFirst = false;
                fieldList.Append(col.ColumnName);
            }

            //complete the insert statement
            insertStatement.Append("(");
            insertStatement.Append(fieldList.ToString());
            insertStatement.AppendLine(")");

            //get the table data
            IDataReader rdr = new Query(table).ExecuteReader();
            //bool isNumeric = false;
            //TableSchema.TableColumn thisColumn=null;
            while (rdr.Read())
            {
                StringBuilder thisStatement = new StringBuilder();
                thisStatement.Append(insertStatement);
                thisStatement.Append("VALUES(");
                //loop the schema and pull out the values from the reader
                isFirst = true;
                foreach (TableSchema.TableColumn col in table.Columns)
                {
                    if(!isFirst)
                    {
                        thisStatement.Append(",");
                    }
                    isFirst = false;

                    if (Utility.IsNumeric(col))
                    {
                        thisStatement.Append(rdr[col.ColumnName]);                    }
                    else
                    {
                        thisStatement.Append("'");
                        thisStatement.Append(rdr[col.ColumnName].ToString().Replace("'", "''"));                    }
                }
                //add in a closing paren
                thisStatement.AppendLine(")");
                statements.Append(thisStatement.ToString());
            }
            rdr.Close();


            result.Append("PRINT 'Begin inserting data in ");
            result.Append(tableName);
            result.AppendLine("'");

            result.Append(statements.ToString());

            return result.ToString();
        }


        public override string ScriptSchema()
        {
            /*
            OracleConnection conn = new OracleConnection(this.connectionString);
            OracleConnectionStringBuilder cString = new OracleConnectionStringBuilder(this.connectionString);
            ServerConnection sconn = new ServerConnection(conn);
            Server server = new Server(sconn);
            Database db = server.Databases[cString.InitialCatalog];
            Transfer trans = new Transfer(db);

            //set the objects to copy
            trans.CopyAllTables = true;
            trans.CopyAllDefaults = true;
            trans.CopyAllUserDefinedFunctions = true;
            trans.CopyAllStoredProcedures = true;
            trans.CopyAllViews = true;
            trans.CopyData = true;
            trans.CopySchema = true;
            trans.CopyAllDefaults = true;
            trans.DropDestinationObjectsFirst = true;
            trans.UseDestinationTransaction = true;

            trans.Options.AnsiFile = true;
            trans.Options.ClusteredIndexes = true;
            trans.Options.DriAll = true;
            trans.Options.IncludeHeaders = true;
            trans.Options.IncludeIfNotExists = true;
            trans.Options.SchemaQualify = true;
            

            StringCollection script = trans.ScriptTransfer();
            
           
            foreach (string s in script) {
                result += s + "\r\n";
            }
             * */
            string result = "";
            return result + "\r\n\r\n";
        }

        #endregion

        public override DbCommand GetDbCommand(QueryCommand qry)
        {
            DbCommand cmd;
            AutomaticConnectionScope conn = new AutomaticConnectionScope(this);
            cmd = conn.Connection.CreateCommand();
          
            cmd.CommandText = qry.CommandSql;
            cmd.CommandType = qry.CommandType;

            foreach (QueryParameter par in qry.Parameters)
            {
                cmd.Parameters.Add(par);
            }

            return cmd;
        }

        public override string[] GetForeignKeyTables(string tableName) {
            return new string[] { "" };
        }

        public override string GetTableNameByPrimaryKey(string pkName, string providerName) {
            return string.Empty;
        }

        internal override string GetDatabaseVersion(string providerName)
        {
            return "Unknown";
        }
    }
}