Hi All,
"CodeWriter" is correct in that your function must compare the Type objects
against the .NET System types. However, using the string-formatted name is
probably not the best way to compare objects that inherit from System.Type.
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" parameters
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 declare
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 that
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 you
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 username,
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 each
table and column pair with which you plan on using this function. Using
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 plan
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 the
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 rights.
--------------------
From: "codewriter" <co**************@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" <cs******@hotmail.com> wrote in message
news:0b****************************@phx.gbl... 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
--
This posting is provided "AS IS" with no warranties, and confers no rights.
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.
