Database Parameters - OleDBType

David

Hi all,

I have a framework that is used for connecting to the database. It works fine, however I want to introduce the technique of binding variables into it (=using database parameters). The framework uses the microsoft oledb driver (but i have the same issue with the oracle driver) on an oracle 10 database. In the framework there's a method that looks like:

public void SomeMethod(object[] parameters)

{

...

foreach (object param in parameters)

{

oledbcommand.Parameters.Add(param)

}

...

}

Suppose we have 1 parameter (type string) that will be used in "select * from country where id = ?" and the database field country.id has type varchar2(10), this works fine. (System.String is automatically adressed as a OleDbType.Varchar2 parameter by the .NET framework)

However if the database field country.id has type char(10), I don't receive database exceptions (ora faults) and I don't receive unexpected exceptions, however the existing database record will not be retrieved. A solution for this is using the command

oledbcommand.Parameters.Add(new OleDbParameter(name, OleDbType.Char).Value = ...);

This will retrieve the database record, however I can't implement this in my framework. The SomeMethod-method is used for executing every select in my applications and doesn't know the type of the database field (a string could be varchar2, char, nvarchar, clob, ....)

Somebody knows how to handle this issue? Thanx for your response

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Alex18y ago
Have you tried this?

OleDbParameter param = new OleDbParameter();
param.ParameterName = "whatever";
param.Value = whateverObject;
command.Parameters.Add(param);

The thing is, I don't think I've ever had an issue with Char database fields, but we use Sql at work.

If that doesn't work, I have a bit of a lame suggestion, but it should work. Have another method that queries the database like this:

select distinct DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where Column_NAME = 'whatever'

That should get you the data type from the DB. Then you can have a big list of switch/case or if/else to check for all possible database types. Then have this method return an OleDbType enum, and use that in your method.

I know it's not straight forward, but that should do the trick.
David18y ago
Thanx for your reply

Your suggestion was how the code originally was written. I've detected the error there. It seems that the .NET framework automatically converts a System.String type to OLEDBTYPE.Varchar2 => the reason why AddWithValue doesn't need to know the parameter type

Furthermore, the Parameters.AddWithValue() method only exists with the oledbdriver (I also have to modify the code that uses the oracle driver).

Is nobody using binding variables (database parameters) in a company framework that writes strings to char(...) fields?
Alex18y ago
The best way would be to pass in two arrays to your method. One array with the names of the parameters, and one for the values. That way, you can just iterate through both arrays and do something like this:

private void SomeMethod(string[] paramNames, object[] paramValues)
{
   if(paramNames.Length != paramValues.Length)
   return; //error

   for(int i =0; i{
   command.Parameters.AddWithValue(paramNames[i],paramValues[i]);
}
}

When using this approach, you don't need to worry about the datatypes. It gets taken care of for you.