DbType ? to go with Oracle Provider Ref Cursor

11 Jan 2007 10:04 PM

Doyle

What DbType to I use so that when the provider is Oracle I will get back
the ole Ref Cursor ?

my Parameter Construction method is below:

public DbParameter GetParameter(string argParameterName, ParameterDirection
direction, DbType argDbType, int argSize, object argValue, bool
argSubNullForZero)
    {
        DbParameter param;

        param = databaseProvider.CreateParameter();
        param.DbType = argDbType;

        param.ParameterName = argParameterName;
        if (argSize > 0)
            param.Size = argSize;
        param.Direction = direction;
        param.IsNullable = true;

        if (direction == ParameterDirection.Input)
        {
            if (argDbType == DbType.Int64 || argDbType == DbType.Int32 ||
argDbType ==DbType.Int16 ||
                argDbType == DbType.Decimal || argDbType == DbType.Double)
            {
                if (argSubNullForZero && (int)argValue == 0)
                    argValue = System.DBNull.Value;
            }

            if (argDbType == DbType.Date)
            {
                if ((DateTime)argValue == DateTime.MinValue)
                    argValue = System.DBNull.Value;
            }

            if (argDbType == DbType.String || argDbType ==
DbType.StringFixedLength ||
                argDbType == DbType.AnsiString || argDbType ==
DbType.AnsiStringFixedLength)
            {
                if ((String)argValue == String.Empty)
                    argValue = System.DBNull.Value;
            }
        }

        param.Value = argValue;

        return param;

    }

12 Jan 2007 9:45 AM

Frans Bouma [C# MVP]

Doyle wrote:

Show quote

> What DbType to I use so that when the provider is Oracle I will get
> back the ole Ref Cursor ?
>
> my Parameter Construction method is below:
>
> public DbParameter GetParameter(string argParameterName,
> ParameterDirection direction, DbType argDbType, int argSize, object
> argValue, bool argSubNullForZero)
>     {
>         DbParameter param;
>
>         param = databaseProvider.CreateParameter();
>         param.DbType = argDbType;
>
>         param.ParameterName = argParameterName;
>         if (argSize > 0)
>             param.Size = argSize;
>         param.Direction = direction;
>         param.IsNullable = true;
>
>         if (direction == ParameterDirection.Input)
>         {
>             if (argDbType == DbType.Int64 || argDbType ==
> DbType.Int32 || argDbType ==DbType.Int16 ||
>                 argDbType == DbType.Decimal || argDbType ==
> DbType.Double)             {
>                 if (argSubNullForZero && (int)argValue == 0)
>                     argValue = System.DBNull.Value;
>             }
>
>             if (argDbType == DbType.Date)
>             {
>                 if ((DateTime)argValue == DateTime.MinValue)
>                     argValue = System.DBNull.Value;
>             }
>
>             if (argDbType == DbType.String || argDbType ==
> DbType.StringFixedLength ||
>                 argDbType == DbType.AnsiString || argDbType ==
> DbType.AnsiStringFixedLength)
>             {
>                 if ((String)argValue == String.Empty)
>                     argValue = System.DBNull.Value;
>             }
>         }
>
>         param.Value = argValue;
>
>         return param;
>
>     }

Why don't you use OracleDbType instead? You need to create
OracleParameter objects anyway.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

12 Jan 2007 5:30 PM

Doyle

because the whole point is to use the superclass type to allow the factory
pattern to determine the appropriate subclass to instantiate via the
provider.CreateParameter(). This functionality is needed in order to use the
same code for different providers and thus the reason for my question.

Show quote

"Frans Bouma [C# MVP]" wrote:

> Doyle wrote:
>
> > What DbType to I use so that when the provider is Oracle I will get
> > back the ole Ref Cursor ?
> >
> > my Parameter Construction method is below:
> >
> > public DbParameter GetParameter(string argParameterName,
> > ParameterDirection direction, DbType argDbType, int argSize, object
> > argValue, bool argSubNullForZero)
> >     {
> >         DbParameter param;
> >
> >         param = databaseProvider.CreateParameter();
> >         param.DbType = argDbType;
> >
> >         param.ParameterName = argParameterName;
> >         if (argSize > 0)
> >             param.Size = argSize;
> >         param.Direction = direction;
> >         param.IsNullable = true;
> >
> >         if (direction == ParameterDirection.Input)
> >         {
> >             if (argDbType == DbType.Int64 || argDbType ==
> > DbType.Int32 || argDbType ==DbType.Int16 ||
> >                 argDbType == DbType.Decimal || argDbType ==
> > DbType.Double)             {
> >                 if (argSubNullForZero && (int)argValue == 0)
> >                     argValue = System.DBNull.Value;
> >             }
> >
> >             if (argDbType == DbType.Date)
> >             {
> >                 if ((DateTime)argValue == DateTime.MinValue)
> >                     argValue = System.DBNull.Value;
> >             }
> >
> >             if (argDbType == DbType.String || argDbType ==
> > DbType.StringFixedLength ||
> >                 argDbType == DbType.AnsiString || argDbType ==
> > DbType.AnsiStringFixedLength)
> >             {
> >                 if ((String)argValue == String.Empty)
> >                     argValue = System.DBNull.Value;
> >             }
> >         }
> >
> >         param.Value = argValue;
> >
> >         return param;
> >
> >     }
>
>     Why don't you use OracleDbType instead? You need to create
> OracleParameter objects anyway.
>
>         FB
>
> --
> ------------------------------------------------------------------------
> Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
> LLBLGen Pro website: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
>

13 Jan 2007 8:57 AM

Frans Bouma [C# MVP]

Doyle wrote:

> because the whole point is to use the superclass type to allow the
> factory pattern to determine the appropriate subclass to instantiate
> via the provider.CreateParameter(). This functionality is needed in
> order to use the same code for different providers and thus the
> reason for my question.

    Ok, well, then next you of course start reflector, load
Oracle.DataAccess.dll, and check OraDb_DbTypeTable.InsertTableEntries()
;), which says:

//...
     OraDb_DbTypeTable.s_table.Add(OracleDbType.Raw, DbType.Binary);
      OraDb_DbTypeTable.s_table.Add(OracleDbType.RefCursor,
DbType.Object);
      OraDb_DbTypeTable.s_table.Add(OracleDbType.Single, DbType.Single);
      OraDb_DbTypeTable.s_table.Add(OracleDbType.TimeStamp,
DbType.DateTime);
//...

    so it maps to DbType.Object.

        FB

Show quote

>
> "Frans Bouma [C# MVP]" wrote:
>
> > Doyle wrote:
> >
> > > What DbType to I use so that when the provider is Oracle I will
> > > get back the ole Ref Cursor ?
> > >
> > > my Parameter Construction method is below:
> > >
> > > public DbParameter GetParameter(string argParameterName,
> > > ParameterDirection direction, DbType argDbType, int argSize,
> > > object argValue, bool argSubNullForZero)
> > >     {
> > >         DbParameter param;
> > >
> > >         param = databaseProvider.CreateParameter();
> > >         param.DbType = argDbType;
> > >
> > >         param.ParameterName = argParameterName;
> > >         if (argSize > 0)
> > >             param.Size = argSize;
> > >         param.Direction = direction;
> > >         param.IsNullable = true;
> > >
> > >         if (direction == ParameterDirection.Input)
> > >         {
> > >             if (argDbType == DbType.Int64 || argDbType ==
> > > DbType.Int32 || argDbType ==DbType.Int16 ||
> > >                 argDbType == DbType.Decimal || argDbType ==
> > > DbType.Double)             {
> > >                 if (argSubNullForZero && (int)argValue == 0)
> > >                     argValue = System.DBNull.Value;
> > >             }
> > >
> > >             if (argDbType == DbType.Date)
> > >             {
> > >                 if ((DateTime)argValue == DateTime.MinValue)
> > >                     argValue = System.DBNull.Value;
> > >             }
> > >
> > >             if (argDbType == DbType.String || argDbType ==
> > > DbType.StringFixedLength ||
> > >                 argDbType == DbType.AnsiString || argDbType ==
> > > DbType.AnsiStringFixedLength)
> > >             {
> > >                 if ((String)argValue == String.Empty)
> > >                     argValue = System.DBNull.Value;
> > >             }
> > >         }
> > >
> > >         param.Value = argValue;
> > >
> > >         return param;
> > >
> > >     }
> >
> >     Why don't you use OracleDbType instead? You need to create
> > OracleParameter objects anyway.

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------