Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » General » Downloads
 
Add your own
alternative version

Implementing Model-View-Presenter in ASP.NET

, 17 Nov 2007 CPOL
Three implementations of Model-View-Presenter in ASP.NET 2.0.
MVPSampleApp.zip
MVP.SampleApp
Lib
Microsoft.Practices.EnterpriseLibrary.Common.dll
Microsoft.Practices.EnterpriseLibrary.Data.dll
MySql.Data.dll
nunit.framework.dll
Rhino.Mocks.dll
Model
Data
Interfaces
Properties
Presentation
Presentation.Tests
Properties
Interfaces
Properties
SubSonic
ActiveRecord
Builder
CodeGeneration
Templates
CodeLanguage
Configuration
Controls
Calendar
lang
skin
active-bg.gif
calendar.gif
dark-bg.gif
hover-bg.gif
menuarrow.gif
normal-bg.gif
rowhover-bg.gif
status-bg.gif
title-bg.gif
today-bg.gif
Resources
DataProviders
Properties
Sql Tools
SubSonic.snk
Sugar
WebApp
App_Data
Views
SQL2000SampleDb.zip
SQL2000SampleDb.msi
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";
        }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alex Mueller
Web Developer
United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 17 Nov 2007
Article Copyright 2007 by Alex Mueller
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid