OracleCommandBuilder.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ Net / Net / 3.5.50727.3053 / DEVDIV / depot / DevDiv / releases / whidbey / netfxsp / ndp / fx / src / DataOracleClient / System / Data / OracleClient / OracleCommandBuilder.cs / 2 / OracleCommandBuilder.cs

                            //------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient { 

    using System; 
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Data.Common; 
    using System.Data.ProviderBase;
    using System.Diagnostics; 
    using System.Globalization; 
    using System.Text;
 
    sealed public class OracleCommandBuilder : DbCommandBuilder {

        private const char      _doubleQuoteChar = '\"';
        private const string    _doubleQuoteString = "\""; 
        private const string    _doubleQuoteEscapeString = "\"\"";
        private const char      _singleQuoteChar = '\''; 
        private const string    _singleQuoteString = "\'"; 
        private const string    _singleQuoteEscapeString = "\'\'";
 
        // Command to resolve names into their individual components
        private const string ResolveNameCommand_Part1 =
            "begin dbms_utility.name_resolve(";
 
        private const string ResolveNameCommand_Part2 =
            ",1,:schema,:part1,:part2,:dblink,:part1type,:objectnum); end;"; 
 
        // Command to derive parameters for a specific stored procedure
        static private readonly string DeriveParameterCommand_Part1 = 
            "select"
                +" overload,"
                +" decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name,"
                +" decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1) direction,"      // default to input parameter if unknown. 
                +" decode(data_type,"
                    +" 'BFILE',"    + ((int)OracleType.BFile).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'BLOB',"     + ((int)OracleType.Blob).ToString(CultureInfo.CurrentCulture)       + "," 
                    +" 'CHAR',"     + ((int)OracleType.Char).ToString(CultureInfo.CurrentCulture)       + ","
                    +" 'CLOB',"     + ((int)OracleType.Clob).ToString(CultureInfo.CurrentCulture)       + "," 
                    +" 'DATE',"     + ((int)OracleType.DateTime).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'FLOAT',"    + ((int)OracleType.Number).ToString(CultureInfo.CurrentCulture)     + ","
                    +" 'INTERVAL YEAR TO MONTH',"+ ((int)OracleType.IntervalYearToMonth).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'INTERVAL DAY TO SECOND',"+ ((int)OracleType.IntervalDayToSecond).ToString(CultureInfo.CurrentCulture)   + "," 
                    +" 'LONG',"     + ((int)OracleType.LongVarChar).ToString(CultureInfo.CurrentCulture)+ ","
                    +" 'LONG RAW'," + ((int)OracleType.LongRaw).ToString(CultureInfo.CurrentCulture)    + "," 
                    +" 'NCHAR',"    + ((int)OracleType.NChar).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'NCLOB',"    + ((int)OracleType.NClob).ToString(CultureInfo.CurrentCulture)      + ","
                    +" 'NUMBER',"   + ((int)OracleType.Number).ToString(CultureInfo.CurrentCulture)     + "," 
                    +" 'NVARCHAR2',"+ ((int)OracleType.NVarChar).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'RAW',"      + ((int)OracleType.Raw).ToString(CultureInfo.CurrentCulture)        + ","
                    +" 'REF CURSOR',"+((int)OracleType.Cursor).ToString(CultureInfo.CurrentCulture)     + ","
                    +" 'ROWID',"    + ((int)OracleType.RowId).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'TIMESTAMP',"+ ((int)OracleType.Timestamp).ToString(CultureInfo.CurrentCulture)  + ","
                    +" 'TIMESTAMP WITH LOCAL TIME ZONE',"+ ((int)OracleType.TimestampLocal).ToString(CultureInfo.CurrentCulture)+ "," 
                    +" 'TIMESTAMP WITH TIME ZONE',"+ ((int)OracleType.TimestampWithTZ).ToString(CultureInfo.CurrentCulture)     + "," 
                    +" 'VARCHAR2'," + ((int)OracleType.VarChar).ToString(CultureInfo.CurrentCulture)    + ","
                    + ((int)OracleType.VarChar).ToString(CultureInfo.CurrentCulture) + ") oracletype,"      // Default to Varchar if unknown. 
                +" decode(data_type,"
                    +" 'CHAR',"     + 2000      + ","
                    +" 'LONG',"     + Int32.MaxValue    + ","
                    +" 'LONG RAW'," + Int32.MaxValue    + "," 
                    +" 'NCHAR',"    + 4000      + ","
                    +" 'NVARCHAR2',"+ 4000      + "," 
                    +" 'RAW',"      + 2000      + "," 
                    +" 'VARCHAR2'," + 2000      + ","
                    +"0) length," 
                +" nvl(data_precision, 255) precision,"
                +" nvl(data_scale, 255) scale "
                +"from all_arguments "
                +"where data_level = 0" 
                +" and data_type is not null"
                +" and owner = " 
                ; 
        private const string DeriveParameterCommand_Part2 =
            " and package_name"; 

        private const string DeriveParameterCommand_Part3 =
            " and object_name = ";
 
        private const string DeriveParameterCommand_Part4 =
            "  order by overload, position"; 
 
        public OracleCommandBuilder() : base() {
            GC.SuppressFinalize(this); 
        }

        public OracleCommandBuilder(OracleDataAdapter adapter) : this() {
            DataAdapter = adapter; 
        }
 
        /// Oracle only supports CatalogLocation.End 
        [
        Browsable(false), 
        EditorBrowsableAttribute(EditorBrowsableState.Never) ,
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
        ]
        public override CatalogLocation CatalogLocation { 
            get {
                return CatalogLocation.End; 
            } 
            set {
                if (CatalogLocation.End != value) { 
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value..
                }
            }
        } 

        /// Oracle only supports '@' 
        [ 
        Browsable(false),
        EditorBrowsableAttribute(EditorBrowsableState.Never), 
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
        ]
        public override string CatalogSeparator {
            get { 
                return "@";
            } 
            set { 
                if ("@" != value) {
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value. 
                }
            }
        }
 
        [
        DefaultValue(null), 
        ResCategoryAttribute(Res.OracleCategory_Update), 
        ResDescriptionAttribute(Res.OracleCommandBuilder_DataAdapter)
        ] 
        new public OracleDataAdapter DataAdapter {
            get {
                return (OracleDataAdapter)base.DataAdapter;
            } 
            set {
                base.DataAdapter = value; 
            } 
        }
 
        [
        Browsable(false),
        EditorBrowsableAttribute(EditorBrowsableState.Never),
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden), 
        ]
        public override string SchemaSeparator { 
            get { 
                return ".";
            } 
            set {
                if ("." != value) {
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value.
                } 
            }
        } 
 
        override protected void ApplyParameterInfo(DbParameter parameter, DataRow datarow, StatementType statementType, bool whereClause) {
            OracleParameter p = (OracleParameter) parameter; 
            object valueType = datarow["ProviderType", DataRowVersion.Default];
            OracleType oracleTypeOfColumn = (OracleType) valueType;
            switch (oracleTypeOfColumn) {
            case OracleType.LongVarChar: 
                oracleTypeOfColumn = OracleType.VarChar;     // We'll promote this automatically in the binding, and it saves headaches
                break; 
            } 
            p.OracleType = (OracleType) oracleTypeOfColumn;
            p.Offset     = 0; 
        }

        static public void DeriveParameters(OracleCommand command) {
            OracleConnection.ExecutePermission.Demand(); 

            if (null == command) 
                throw ADP.ArgumentNull("command"); 

            switch (command.CommandType) { 
                case System.Data.CommandType.StoredProcedure:
                    break;

                case System.Data.CommandType.Text: 
                case System.Data.CommandType.TableDirect:
                    throw ADP.DeriveParametersNotSupported(command); 
 
                default:
                    throw ADP.InvalidCommandType(command.CommandType); 
            }
            if (ADP.IsEmpty(command.CommandText))
                throw ADP.CommandTextRequired("DeriveParameters");
 
            OracleConnection connection = command.Connection;
            if (null == connection) 
                throw ADP.ConnectionRequired("DeriveParameters"); 

            ConnectionState state = connection.State; 

            if (ConnectionState.Open != state)
                throw ADP.OpenConnectionRequired("DeriveParameters", state);
 
            ArrayList list = DeriveParametersFromStoredProcedure(connection, command);
 
            OracleParameterCollection parameters = command.Parameters; 
            parameters.Clear();
 
            int count = list.Count;
            for(int i = 0; i < count; ++i) {
                parameters.Add((OracleParameter)list[i]);
            } 
        }
 
        static private ArrayList DeriveParametersFromStoredProcedure( 
            OracleConnection    connection,
            OracleCommand       command 
            ) {
            ArrayList       parameterList = new ArrayList();
            OracleCommand   tempCommand = connection.CreateCommand();
            string          schema; 
            string          packageName;
            string          objectName; 
            string          dblink; 

            tempCommand.Transaction = command.Transaction;  // must set the transaction context to be the same as the command, or we'll throw when we execute. 

            if (0 != ResolveName(tempCommand, command.CommandText,
                                    out schema,
                                    out packageName, 
                                    out objectName,
                                    out dblink)) { 
                StringBuilder builder = new StringBuilder(); 

                builder.Append(DeriveParameterCommand_Part1); 
                builder.Append(QuoteIdentifier(schema, _singleQuoteString, _singleQuoteEscapeString));

                builder.Append(DeriveParameterCommand_Part2);
                if (!ADP.IsNull(packageName)) { 
                    builder.Append(" = ");
                    builder.Append(QuoteIdentifier(packageName, _singleQuoteString, _singleQuoteEscapeString)); 
                } 
                else {
                    // fix RFC 50002452 - set package name to null to avoid duplicate entries 
                    // when object with same name exists inside some package
                    builder.Append(" is null");
                }
 
                builder.Append(DeriveParameterCommand_Part3);
                builder.Append(QuoteIdentifier(objectName, _singleQuoteString, _singleQuoteEscapeString)); 
                builder.Append(DeriveParameterCommand_Part4); 

                tempCommand.Parameters.Clear(); 
                tempCommand.CommandText = builder.ToString();

//              Console.WriteLine(tempCommand.CommandText);
 
                using(OracleDataReader rdr = tempCommand.ExecuteReader()) {
                    while (rdr.Read()) { 
                        if (!ADP.IsNull(rdr.GetValue(0))) 
                            throw ADP.CannotDeriveOverloaded();
 
                        string              parameterName   = rdr.GetString (1);
                        ParameterDirection  direction       = (ParameterDirection)(int)rdr.GetDecimal(2);
                        OracleType          oracleType      = (OracleType)(int)rdr.GetDecimal(3);
                        int                 size            = (int)rdr.GetDecimal(4); 
                        byte                precision       = (byte)rdr.GetDecimal(5);
                        int                 signedScale     = (int)rdr.GetDecimal(6); 
                        byte                scale; 

                        // FLOAT data types are really just NUMBERs in drag, but 
                        // they have a negative scale, so we just convert them to
                        // scale zero.  This also impacts negative scaled NUMBER
                        // parameters, but since the CLS doesn't really support
                        // negative scale, we are better off this way. 
                        if (signedScale < 0) {
                            scale = 0; 
                        } 
                        else {
                            scale = (byte)signedScale; 
                        }

                        OracleParameter parameter = new OracleParameter(
                                                            parameterName, 
                                                            oracleType,
                                                            size, 
                                                            direction, 
                                                            true,   // isNullable
                                                            precision, 
                                                            scale,
                                                            "",
                                                            DataRowVersion.Current,
                                                            null); 

                        parameterList.Add(parameter); 
                    } 
                }
            } 

            return parameterList;
        }
 
        new public OracleCommand GetInsertCommand() {
            return (OracleCommand)base.GetInsertCommand(); 
        } 
        new public OracleCommand GetInsertCommand(bool useColumnsForParameterNames) {
            return (OracleCommand)base.GetInsertCommand(useColumnsForParameterNames); 
        }

        new public OracleCommand GetUpdateCommand() {
            return (OracleCommand)base.GetUpdateCommand(); 
        }
        new public OracleCommand GetUpdateCommand(bool useColumnsForParameterNames) { 
            return (OracleCommand)base.GetUpdateCommand(useColumnsForParameterNames); 
        }
 
        new public OracleCommand GetDeleteCommand() {
            return (OracleCommand)base.GetDeleteCommand();
        }
        new public OracleCommand GetDeleteCommand(bool useColumnsForParameterNames) { 
            return (OracleCommand)base.GetDeleteCommand(useColumnsForParameterNames);
        } 
 
        override protected string GetParameterName( int parameterOrdinal ) {
            return  "p" + parameterOrdinal.ToString(CultureInfo.CurrentCulture); 
        }
        override protected string GetParameterName( string parameterName ) {
            return  parameterName;
        } 

        override protected string GetParameterPlaceholder( int parameterOrdinal ) { 
            return  ":" + GetParameterName(parameterOrdinal); 
        }
 
        public override string QuoteIdentifier(string unquotedIdentifier){
            return QuoteIdentifier( unquotedIdentifier, _doubleQuoteString, _doubleQuoteEscapeString);
        }
 
        private static string QuoteIdentifier(string unquotedIdentifier, string quoteString, string quoteEscapeString){
            ADP.CheckArgumentNull(unquotedIdentifier, "unquotedIdentifier"); 
 
            StringBuilder resultString = new StringBuilder();
            resultString.Append(quoteString); 
            resultString.Append(unquotedIdentifier.Replace(quoteString, quoteEscapeString));
            resultString.Append(quoteString);

            return resultString.ToString(); 
        }
 
        static uint ResolveName( 
            OracleCommand   command,         // command object to use
            string          nameToResolve, 
            out string      schema,          // schema part of name
            out string      packageName,     // package part of name
            out string      objectName,      // procedure/function part of name
            out string      dblink           // database link part of name 
            ) {
            //  Ask the server for the component parts of the name 
 
            StringBuilder   builder  = new StringBuilder();
 
            builder.Append(ResolveNameCommand_Part1);
            // NOTE: This might seem to create an issue because we will be quoting
            // a user supplied string, causing the comparison to be case-sensitive
            // where it would be case-insenstive otherwise.  That is not the case 
            // (no pun intended) here because we are simply using single quotes to
            // ensure that this *VALUE* is not creating a SQL injection attack. 
            builder.Append(QuoteIdentifier(nameToResolve, _singleQuoteString, _singleQuoteEscapeString)); 
            builder.Append(ResolveNameCommand_Part2);
 
            command.CommandText = builder.ToString();
            command.Parameters.Add(new OracleParameter("schema",    OracleType.VarChar, 30)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part1",     OracleType.VarChar, 30)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part2",     OracleType.VarChar, 30)).Direction = ParameterDirection.Output; 
            command.Parameters.Add(new OracleParameter("dblink",    OracleType.VarChar, 128)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part1type", OracleType.UInt32)).Direction = ParameterDirection.Output; 
            command.Parameters.Add(new OracleParameter("objectnum", OracleType.UInt32)).Direction = ParameterDirection.Output; 

            command.ExecuteNonQuery(); 

            object oracleObjectNumber = command.Parameters["objectnum"].Value;
            if (ADP.IsNull(oracleObjectNumber)) {
                schema = string.Empty; 
                packageName = string.Empty;
                objectName = string.Empty; 
                dblink = string.Empty; 
                return 0;
            } 

            schema  = (ADP.IsNull(command.Parameters["schema"].Value)) ? null : (string)command.Parameters["schema"].Value;
            packageName   = (ADP.IsNull(command.Parameters["part1"].Value )) ? null : (string)command.Parameters["part1"].Value;
            objectName   = (ADP.IsNull(command.Parameters["part2"].Value )) ? null : (string)command.Parameters["part2"].Value; 
            dblink  = (ADP.IsNull(command.Parameters["dblink"].Value)) ? null : (string)command.Parameters["dblink"].Value;
 
            return (uint)command.Parameters["part1type"].Value; 
        }
 
        private void RowUpdatingHandler(object sender, OracleRowUpdatingEventArgs ruevent) {
            base.RowUpdatingHandler(ruevent);
        }
 
        override protected void SetRowUpdatingHandler(DbDataAdapter adapter) {
            Debug.Assert(adapter is OracleDataAdapter, "!OracleDataAdapter"); 
            if (adapter == base.DataAdapter) { // removal case 
                ((OracleDataAdapter)adapter).RowUpdating -= RowUpdatingHandler;
            } 
            else { // adding case
                ((OracleDataAdapter)adapter).RowUpdating += RowUpdatingHandler;
            }
        } 

        public override string UnquoteIdentifier(string quotedIdentifier){ 
 
            ADP.CheckArgumentNull(quotedIdentifier, "quotedIdentifier");
 
            if ((quotedIdentifier.Length < 2) ||
                (quotedIdentifier[0] != _doubleQuoteChar) ||
                (quotedIdentifier[quotedIdentifier.Length-1] != _doubleQuoteChar)){
                throw ADP.IdentifierIsNotQuoted(); 
            }
 
            return quotedIdentifier.Substring(1,quotedIdentifier.Length-2).Replace(_doubleQuoteEscapeString,_doubleQuoteString); 

        } 
    }
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
//------------------------------------------------------------------------------ 
// 
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// 
// [....] 
//-----------------------------------------------------------------------------
 
namespace System.Data.OracleClient { 

    using System; 
    using System.Collections;
    using System.ComponentModel;
    using System.Data;
    using System.Data.Common; 
    using System.Data.ProviderBase;
    using System.Diagnostics; 
    using System.Globalization; 
    using System.Text;
 
    sealed public class OracleCommandBuilder : DbCommandBuilder {

        private const char      _doubleQuoteChar = '\"';
        private const string    _doubleQuoteString = "\""; 
        private const string    _doubleQuoteEscapeString = "\"\"";
        private const char      _singleQuoteChar = '\''; 
        private const string    _singleQuoteString = "\'"; 
        private const string    _singleQuoteEscapeString = "\'\'";
 
        // Command to resolve names into their individual components
        private const string ResolveNameCommand_Part1 =
            "begin dbms_utility.name_resolve(";
 
        private const string ResolveNameCommand_Part2 =
            ",1,:schema,:part1,:part2,:dblink,:part1type,:objectnum); end;"; 
 
        // Command to derive parameters for a specific stored procedure
        static private readonly string DeriveParameterCommand_Part1 = 
            "select"
                +" overload,"
                +" decode(position,0,'RETURN_VALUE',nvl(argument_name,chr(0))) name,"
                +" decode(in_out,'IN',1,'IN/OUT',3,'OUT',decode(argument_name,null,6,2),1) direction,"      // default to input parameter if unknown. 
                +" decode(data_type,"
                    +" 'BFILE',"    + ((int)OracleType.BFile).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'BLOB',"     + ((int)OracleType.Blob).ToString(CultureInfo.CurrentCulture)       + "," 
                    +" 'CHAR',"     + ((int)OracleType.Char).ToString(CultureInfo.CurrentCulture)       + ","
                    +" 'CLOB',"     + ((int)OracleType.Clob).ToString(CultureInfo.CurrentCulture)       + "," 
                    +" 'DATE',"     + ((int)OracleType.DateTime).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'FLOAT',"    + ((int)OracleType.Number).ToString(CultureInfo.CurrentCulture)     + ","
                    +" 'INTERVAL YEAR TO MONTH',"+ ((int)OracleType.IntervalYearToMonth).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'INTERVAL DAY TO SECOND',"+ ((int)OracleType.IntervalDayToSecond).ToString(CultureInfo.CurrentCulture)   + "," 
                    +" 'LONG',"     + ((int)OracleType.LongVarChar).ToString(CultureInfo.CurrentCulture)+ ","
                    +" 'LONG RAW'," + ((int)OracleType.LongRaw).ToString(CultureInfo.CurrentCulture)    + "," 
                    +" 'NCHAR',"    + ((int)OracleType.NChar).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'NCLOB',"    + ((int)OracleType.NClob).ToString(CultureInfo.CurrentCulture)      + ","
                    +" 'NUMBER',"   + ((int)OracleType.Number).ToString(CultureInfo.CurrentCulture)     + "," 
                    +" 'NVARCHAR2',"+ ((int)OracleType.NVarChar).ToString(CultureInfo.CurrentCulture)   + ","
                    +" 'RAW',"      + ((int)OracleType.Raw).ToString(CultureInfo.CurrentCulture)        + ","
                    +" 'REF CURSOR',"+((int)OracleType.Cursor).ToString(CultureInfo.CurrentCulture)     + ","
                    +" 'ROWID',"    + ((int)OracleType.RowId).ToString(CultureInfo.CurrentCulture)      + "," 
                    +" 'TIMESTAMP',"+ ((int)OracleType.Timestamp).ToString(CultureInfo.CurrentCulture)  + ","
                    +" 'TIMESTAMP WITH LOCAL TIME ZONE',"+ ((int)OracleType.TimestampLocal).ToString(CultureInfo.CurrentCulture)+ "," 
                    +" 'TIMESTAMP WITH TIME ZONE',"+ ((int)OracleType.TimestampWithTZ).ToString(CultureInfo.CurrentCulture)     + "," 
                    +" 'VARCHAR2'," + ((int)OracleType.VarChar).ToString(CultureInfo.CurrentCulture)    + ","
                    + ((int)OracleType.VarChar).ToString(CultureInfo.CurrentCulture) + ") oracletype,"      // Default to Varchar if unknown. 
                +" decode(data_type,"
                    +" 'CHAR',"     + 2000      + ","
                    +" 'LONG',"     + Int32.MaxValue    + ","
                    +" 'LONG RAW'," + Int32.MaxValue    + "," 
                    +" 'NCHAR',"    + 4000      + ","
                    +" 'NVARCHAR2',"+ 4000      + "," 
                    +" 'RAW',"      + 2000      + "," 
                    +" 'VARCHAR2'," + 2000      + ","
                    +"0) length," 
                +" nvl(data_precision, 255) precision,"
                +" nvl(data_scale, 255) scale "
                +"from all_arguments "
                +"where data_level = 0" 
                +" and data_type is not null"
                +" and owner = " 
                ; 
        private const string DeriveParameterCommand_Part2 =
            " and package_name"; 

        private const string DeriveParameterCommand_Part3 =
            " and object_name = ";
 
        private const string DeriveParameterCommand_Part4 =
            "  order by overload, position"; 
 
        public OracleCommandBuilder() : base() {
            GC.SuppressFinalize(this); 
        }

        public OracleCommandBuilder(OracleDataAdapter adapter) : this() {
            DataAdapter = adapter; 
        }
 
        /// Oracle only supports CatalogLocation.End 
        [
        Browsable(false), 
        EditorBrowsableAttribute(EditorBrowsableState.Never) ,
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
        ]
        public override CatalogLocation CatalogLocation { 
            get {
                return CatalogLocation.End; 
            } 
            set {
                if (CatalogLocation.End != value) { 
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value..
                }
            }
        } 

        /// Oracle only supports '@' 
        [ 
        Browsable(false),
        EditorBrowsableAttribute(EditorBrowsableState.Never), 
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden),
        ]
        public override string CatalogSeparator {
            get { 
                return "@";
            } 
            set { 
                if ("@" != value) {
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value. 
                }
            }
        }
 
        [
        DefaultValue(null), 
        ResCategoryAttribute(Res.OracleCategory_Update), 
        ResDescriptionAttribute(Res.OracleCommandBuilder_DataAdapter)
        ] 
        new public OracleDataAdapter DataAdapter {
            get {
                return (OracleDataAdapter)base.DataAdapter;
            } 
            set {
                base.DataAdapter = value; 
            } 
        }
 
        [
        Browsable(false),
        EditorBrowsableAttribute(EditorBrowsableState.Never),
        DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden), 
        ]
        public override string SchemaSeparator { 
            get { 
                return ".";
            } 
            set {
                if ("." != value) {
                    throw ADP.NotSupported(); // we have to have this property setter, but we can't allow you to change it's value.
                } 
            }
        } 
 
        override protected void ApplyParameterInfo(DbParameter parameter, DataRow datarow, StatementType statementType, bool whereClause) {
            OracleParameter p = (OracleParameter) parameter; 
            object valueType = datarow["ProviderType", DataRowVersion.Default];
            OracleType oracleTypeOfColumn = (OracleType) valueType;
            switch (oracleTypeOfColumn) {
            case OracleType.LongVarChar: 
                oracleTypeOfColumn = OracleType.VarChar;     // We'll promote this automatically in the binding, and it saves headaches
                break; 
            } 
            p.OracleType = (OracleType) oracleTypeOfColumn;
            p.Offset     = 0; 
        }

        static public void DeriveParameters(OracleCommand command) {
            OracleConnection.ExecutePermission.Demand(); 

            if (null == command) 
                throw ADP.ArgumentNull("command"); 

            switch (command.CommandType) { 
                case System.Data.CommandType.StoredProcedure:
                    break;

                case System.Data.CommandType.Text: 
                case System.Data.CommandType.TableDirect:
                    throw ADP.DeriveParametersNotSupported(command); 
 
                default:
                    throw ADP.InvalidCommandType(command.CommandType); 
            }
            if (ADP.IsEmpty(command.CommandText))
                throw ADP.CommandTextRequired("DeriveParameters");
 
            OracleConnection connection = command.Connection;
            if (null == connection) 
                throw ADP.ConnectionRequired("DeriveParameters"); 

            ConnectionState state = connection.State; 

            if (ConnectionState.Open != state)
                throw ADP.OpenConnectionRequired("DeriveParameters", state);
 
            ArrayList list = DeriveParametersFromStoredProcedure(connection, command);
 
            OracleParameterCollection parameters = command.Parameters; 
            parameters.Clear();
 
            int count = list.Count;
            for(int i = 0; i < count; ++i) {
                parameters.Add((OracleParameter)list[i]);
            } 
        }
 
        static private ArrayList DeriveParametersFromStoredProcedure( 
            OracleConnection    connection,
            OracleCommand       command 
            ) {
            ArrayList       parameterList = new ArrayList();
            OracleCommand   tempCommand = connection.CreateCommand();
            string          schema; 
            string          packageName;
            string          objectName; 
            string          dblink; 

            tempCommand.Transaction = command.Transaction;  // must set the transaction context to be the same as the command, or we'll throw when we execute. 

            if (0 != ResolveName(tempCommand, command.CommandText,
                                    out schema,
                                    out packageName, 
                                    out objectName,
                                    out dblink)) { 
                StringBuilder builder = new StringBuilder(); 

                builder.Append(DeriveParameterCommand_Part1); 
                builder.Append(QuoteIdentifier(schema, _singleQuoteString, _singleQuoteEscapeString));

                builder.Append(DeriveParameterCommand_Part2);
                if (!ADP.IsNull(packageName)) { 
                    builder.Append(" = ");
                    builder.Append(QuoteIdentifier(packageName, _singleQuoteString, _singleQuoteEscapeString)); 
                } 
                else {
                    // fix RFC 50002452 - set package name to null to avoid duplicate entries 
                    // when object with same name exists inside some package
                    builder.Append(" is null");
                }
 
                builder.Append(DeriveParameterCommand_Part3);
                builder.Append(QuoteIdentifier(objectName, _singleQuoteString, _singleQuoteEscapeString)); 
                builder.Append(DeriveParameterCommand_Part4); 

                tempCommand.Parameters.Clear(); 
                tempCommand.CommandText = builder.ToString();

//              Console.WriteLine(tempCommand.CommandText);
 
                using(OracleDataReader rdr = tempCommand.ExecuteReader()) {
                    while (rdr.Read()) { 
                        if (!ADP.IsNull(rdr.GetValue(0))) 
                            throw ADP.CannotDeriveOverloaded();
 
                        string              parameterName   = rdr.GetString (1);
                        ParameterDirection  direction       = (ParameterDirection)(int)rdr.GetDecimal(2);
                        OracleType          oracleType      = (OracleType)(int)rdr.GetDecimal(3);
                        int                 size            = (int)rdr.GetDecimal(4); 
                        byte                precision       = (byte)rdr.GetDecimal(5);
                        int                 signedScale     = (int)rdr.GetDecimal(6); 
                        byte                scale; 

                        // FLOAT data types are really just NUMBERs in drag, but 
                        // they have a negative scale, so we just convert them to
                        // scale zero.  This also impacts negative scaled NUMBER
                        // parameters, but since the CLS doesn't really support
                        // negative scale, we are better off this way. 
                        if (signedScale < 0) {
                            scale = 0; 
                        } 
                        else {
                            scale = (byte)signedScale; 
                        }

                        OracleParameter parameter = new OracleParameter(
                                                            parameterName, 
                                                            oracleType,
                                                            size, 
                                                            direction, 
                                                            true,   // isNullable
                                                            precision, 
                                                            scale,
                                                            "",
                                                            DataRowVersion.Current,
                                                            null); 

                        parameterList.Add(parameter); 
                    } 
                }
            } 

            return parameterList;
        }
 
        new public OracleCommand GetInsertCommand() {
            return (OracleCommand)base.GetInsertCommand(); 
        } 
        new public OracleCommand GetInsertCommand(bool useColumnsForParameterNames) {
            return (OracleCommand)base.GetInsertCommand(useColumnsForParameterNames); 
        }

        new public OracleCommand GetUpdateCommand() {
            return (OracleCommand)base.GetUpdateCommand(); 
        }
        new public OracleCommand GetUpdateCommand(bool useColumnsForParameterNames) { 
            return (OracleCommand)base.GetUpdateCommand(useColumnsForParameterNames); 
        }
 
        new public OracleCommand GetDeleteCommand() {
            return (OracleCommand)base.GetDeleteCommand();
        }
        new public OracleCommand GetDeleteCommand(bool useColumnsForParameterNames) { 
            return (OracleCommand)base.GetDeleteCommand(useColumnsForParameterNames);
        } 
 
        override protected string GetParameterName( int parameterOrdinal ) {
            return  "p" + parameterOrdinal.ToString(CultureInfo.CurrentCulture); 
        }
        override protected string GetParameterName( string parameterName ) {
            return  parameterName;
        } 

        override protected string GetParameterPlaceholder( int parameterOrdinal ) { 
            return  ":" + GetParameterName(parameterOrdinal); 
        }
 
        public override string QuoteIdentifier(string unquotedIdentifier){
            return QuoteIdentifier( unquotedIdentifier, _doubleQuoteString, _doubleQuoteEscapeString);
        }
 
        private static string QuoteIdentifier(string unquotedIdentifier, string quoteString, string quoteEscapeString){
            ADP.CheckArgumentNull(unquotedIdentifier, "unquotedIdentifier"); 
 
            StringBuilder resultString = new StringBuilder();
            resultString.Append(quoteString); 
            resultString.Append(unquotedIdentifier.Replace(quoteString, quoteEscapeString));
            resultString.Append(quoteString);

            return resultString.ToString(); 
        }
 
        static uint ResolveName( 
            OracleCommand   command,         // command object to use
            string          nameToResolve, 
            out string      schema,          // schema part of name
            out string      packageName,     // package part of name
            out string      objectName,      // procedure/function part of name
            out string      dblink           // database link part of name 
            ) {
            //  Ask the server for the component parts of the name 
 
            StringBuilder   builder  = new StringBuilder();
 
            builder.Append(ResolveNameCommand_Part1);
            // NOTE: This might seem to create an issue because we will be quoting
            // a user supplied string, causing the comparison to be case-sensitive
            // where it would be case-insenstive otherwise.  That is not the case 
            // (no pun intended) here because we are simply using single quotes to
            // ensure that this *VALUE* is not creating a SQL injection attack. 
            builder.Append(QuoteIdentifier(nameToResolve, _singleQuoteString, _singleQuoteEscapeString)); 
            builder.Append(ResolveNameCommand_Part2);
 
            command.CommandText = builder.ToString();
            command.Parameters.Add(new OracleParameter("schema",    OracleType.VarChar, 30)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part1",     OracleType.VarChar, 30)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part2",     OracleType.VarChar, 30)).Direction = ParameterDirection.Output; 
            command.Parameters.Add(new OracleParameter("dblink",    OracleType.VarChar, 128)).Direction = ParameterDirection.Output;
            command.Parameters.Add(new OracleParameter("part1type", OracleType.UInt32)).Direction = ParameterDirection.Output; 
            command.Parameters.Add(new OracleParameter("objectnum", OracleType.UInt32)).Direction = ParameterDirection.Output; 

            command.ExecuteNonQuery(); 

            object oracleObjectNumber = command.Parameters["objectnum"].Value;
            if (ADP.IsNull(oracleObjectNumber)) {
                schema = string.Empty; 
                packageName = string.Empty;
                objectName = string.Empty; 
                dblink = string.Empty; 
                return 0;
            } 

            schema  = (ADP.IsNull(command.Parameters["schema"].Value)) ? null : (string)command.Parameters["schema"].Value;
            packageName   = (ADP.IsNull(command.Parameters["part1"].Value )) ? null : (string)command.Parameters["part1"].Value;
            objectName   = (ADP.IsNull(command.Parameters["part2"].Value )) ? null : (string)command.Parameters["part2"].Value; 
            dblink  = (ADP.IsNull(command.Parameters["dblink"].Value)) ? null : (string)command.Parameters["dblink"].Value;
 
            return (uint)command.Parameters["part1type"].Value; 
        }
 
        private void RowUpdatingHandler(object sender, OracleRowUpdatingEventArgs ruevent) {
            base.RowUpdatingHandler(ruevent);
        }
 
        override protected void SetRowUpdatingHandler(DbDataAdapter adapter) {
            Debug.Assert(adapter is OracleDataAdapter, "!OracleDataAdapter"); 
            if (adapter == base.DataAdapter) { // removal case 
                ((OracleDataAdapter)adapter).RowUpdating -= RowUpdatingHandler;
            } 
            else { // adding case
                ((OracleDataAdapter)adapter).RowUpdating += RowUpdatingHandler;
            }
        } 

        public override string UnquoteIdentifier(string quotedIdentifier){ 
 
            ADP.CheckArgumentNull(quotedIdentifier, "quotedIdentifier");
 
            if ((quotedIdentifier.Length < 2) ||
                (quotedIdentifier[0] != _doubleQuoteChar) ||
                (quotedIdentifier[quotedIdentifier.Length-1] != _doubleQuoteChar)){
                throw ADP.IdentifierIsNotQuoted(); 
            }
 
            return quotedIdentifier.Substring(1,quotedIdentifier.Length-2).Replace(_doubleQuoteEscapeString,_doubleQuoteString); 

        } 
    }
}

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK