Hi everyone.
I gave Nanda this idea about comparing the datatypes in a separate function
for using in his stored procedure simply because he wanted to do it this
way. My opinion is that this approach is not good at all. Database tables
always have types defined, i.e. Integer, VarChar, etc. Passing a parameter
to a stored procedure without knowing what type this parameter is will
create problems and bugs. I'd prefer always to specify the type of a
parameter without relying on the user's choice. The idea of creating mapping
for different datatypes for different tables does not really attract me.
Imagine having 100 tables with average 10 fields in them in your database
and map them to different datatypes. It will be very tidious, timeconsuming,
and eventually produce bugs.
"Theo Yaung [Microsoft]" <theo_from_visual_studio@online.microsoft.com>
wrote in message news:nm844x5WDHA.1536@cpmsftngxa06.phx.gbl...[color=blue]
> Hi All,
>
> "CodeWriter" is correct in that your function must compare the Type[/color]
objects[color=blue]
> against the .NET System types. However, using the string-formatted name[/color]
is[color=blue]
> probably not the best way to compare objects that inherit from[/color]
System.Type.[color=blue]
>
> In the .NET Framework docs, under System.Type, you can read that the
> Equals(...) method will return true for comparing types. Thus, you could
> write your function as:
>
> private void ConvertToOleDb(System.Type type,
> out OleDbType datatype) {
> if(type.Equals(typeof(System.String)))
> datatype=OleDbType.VarWChar;
> else if(type.Equals(typeof(System.Double)))
> datatype=OleDbType.Double;
> else if(type.Equals(typeof(System.DateTime)))
> datatype=OleDbType.DBDate;
> else if(type.Equals(typeof(System.Decimal)))
> datatype=OleDbType.Currency;
> else {
> // add "last ditch" catch-all, or error here
>
> // you MUST assign datatype in order for this to compile
>
> datatype = OleDbType.Error; // TODO: change this
> }
> }
>
> Notice that I also changed the declaration of datatype to an "out"
> parameter rather than a ref parameter. Ref parameters allow you to read
> the value, or write on the variable accordingly. However, "out"[/color]
parameters[color=blue]
> are even more specialized in that they must be written to somewhere in the
> function. Thus, you enter a contract with the compiler -- once you[/color]
declare[color=blue]
> a parameter as "out", the parameter will check for you that in any
> circumstance imaginable, the parameter is always written to. "Out"
> parameters (a.k.a. Output Parameters) are also different in that the
> variable that is passed into a function with an out parameter need not be
> initialized (it makes sense, since that function is guaranteed to write to
> that variable).
>
> If you use output parameters, you also have to write "out" in front of the
> variable before you pass it to the function, instead of "ref".
>
> Overall, this approach is okay for certain circumstances. However, it is
> pretty limiting because it makes some serious assumptions. It assumes[/color]
that[color=blue]
> your database has only one OleDbType that is mapping to each .NET system
> type. That is very rarely the case for large databases, and still seldom
> the case except for the very smallest of databases.
>
> In databases, each column has a type, based on that database's
> architecture. Each type has its benefits and limitations, and thus, it's
> worth it to break database types into many many different fundamental
> datatypes. However, these benefits and limitations are often very
> performance driven, and mostly applicable for optimizing storage. When[/color]
you[color=blue]
> access this data, it becomes converted to more convenient forms, and
> multiple database types get converted into the same .NET System types.
>
> Take for example a table that uses OleDb's char(8) to represent a[/color]
username,[color=blue]
> and a varchar field to represent the full name. When converted to .NET
> system types, they both become System.String objects. Thus, if you try to
> write function for this table to map from .NET system type to OleDb type,
> then you have an ambiguity when you're mapping for System.String. In
> either the char(8) or the varchar instance, something might break because
> the types won't line up.
>
> So, you need more information. The safest way is to make an entry for[/color]
each[color=blue]
> table and column pair with which you plan on using this function.[/color]
Using[color=blue]
> a hashtable, as suggested in Jon Skeet's post, is an excellent means of
> mapping data in this scenario.
>
> The most convenient way is to make a hashtable for each table that you[/color]
plan[color=blue]
> on using this "reverse lookup" (maybe you're only using it on one table,
> for a particular application or web form).
>
> Say I had the following table columns:
> varwchar full_name;
> char(8) user_name;
> dbdate birth_date;
> dbtimestamp last_login;
>
> This would be a pathological case for trying to "reverse lookup" the .NET
> system type, since both full_name and user_names would come back as
> System.String objects, and birth_date and last_login would be
> System.DateTime objects. Thus, knowing the column is critical to picking
> the right OleDb type in this instance.
>
> Let's say you used a Hashtable object named lookupTable. You can use the
> following code to initialize it:
>
> lookupTable = new System.Collections.Hashtable();
> lookupTable["full_name"] = OleDbType.VarWChar;
> lookupTable["user_name"] = OleDbType.Char;
> lookupTable["birth_date"] = OleDbType.DBDate;
> lookupTable["last_login"] = OleDbType.DBTimeStamp;
>
> Then you could write a lookup function, similar to yours, but taking in[/color]
the[color=blue]
> string column name:
>
> private void LookupOleDbType(string colName, out OleDbType datatype) {
> datatype = (OleDbType) lookupTable[colName];
> }
>
> You really don't need the additional lookup function, except it may be
> convenient to keep your cast to OleDbType in only one place. Also, Jon
> Skeet raises a good point that output parameters or reference parameters
> should probably be avoided in exchange for using a return value. This
> keeps the code clear, in that if you are going to write a new value to the
> variable, it should be done somewhere that it originally had scope.
> Though, output parameters and reference parameters are sometimes the only
> way to return more than one value, or to write swap functions, etc.
>
> Hope this helps,
>
> Theo Yaung
> Visual Studio .NET
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.[color=blue]
>
> --------------------[color=green]
> > From: "codewriter" <code_writer_2003@yahoo.com>
> > Newsgroups: microsoft.public.dotnet.languages.csharp
> > Subject: Re: Creating deletecommand parameters at runtime
> > Date: Tue, 5 Aug 2003 11:12:40 -0400
> > Organization: Bell Sympatico
> >
> > Nanda,
> >
> > Your function will not run as you expect it to run. Here is the correct
> > version that I sent you yesterday by email:
> >
> > private OleDbType ConvertToOleDb(Type type)
> >
> > {
> >
> > OleDbType datatype = new OleDbType();
> >
> > if(type.ToString()=="System.Int32")
> >
> > datatype=OleDbType.Integer;
> >
> > else if(type.ToString()=="System.Char")
> >
> > datatype=OleDbType.Char;
> >
> > else if(type.ToString()=="System.Decimal")
> >
> > datatype=OleDbType.Decimal;
> >
> > else if(type.ToString()=="System.Double")
> >
> > datatype=OleDbType.Double;
> >
> > else if(type.ToString()=="System.Single")
> >
> > datatype=OleDbType.Single;
> >
> > else if(type.ToString()=="System.String")
> >
> > datatype=OleDbType.VarChar;
> >
> > return datatype;
> >
> > }
> >
> > "Nanda" <csenanda@hotmail.com> wrote in message
> > news:0b8001c35b36$d1c5d230$a101280a@phx.gbl...[color=darkred]
> > > Hi Theo Yaung,
> > > Thanks for your clear explanation. I have managed
> > > to get rid of the problem, by using a function to map the
> > > System.Type to OleDbType.
> > >
> > > private void ConvertToOleDb(System.Type type,ref
> > > OleDbType datatype)
> > > {
> > > if(type.ToString().Equals("adVarWChar"))
> > > datatype=OleDbType.VarWChar;
> > > else if(type.ToString().Equals("adDouble"))
> > > datatype=OleDbType.Double;
> > > else if(type.ToString().Equals("adDBDate"))
> > > datatype=OleDbType.DBDate;
> > > else if(type.ToString().Equals("adCurrency"))
> > > datatype=OleDbType.Currency;
> > > }
> > >
> > > Can u comment on this way. I even expect some other
> > > better approach from u.
> > > Regds.
> > > Nanda[/color]
> >
> >
> >[/color]
>
>
> --
>
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.[color=blue]
> Use of included script samples are subject to the terms specified at
>
http://www.microsoft.com/info/cpyright.htm
>
> Note: For the benefit of the community-at-large, all responses to this
> message are best directed to the newsgroup/thread from which they
> originated.
>[/color]
