I have created a data access layer class that allows me to create most of objects I needed to communicate with databases (Odbc, OleDb and SqlClient). I also have created a business object handling class with intensive use of reflection to handle lots of tasks with my business object(s). Among other things this class generates every single property/object I needed for my DAL handling (SQL stream, list of values, properties, set retrieve values etc.). Take a look the code below for further explanation:
Public Shared Function InvokeParam(Of T)(_classObject As T, _commandType As AdapterCommandType, _arguments As Object()) As Boolean
Dim s As String = DAL.SCRFL.GetParamStatement(_classObject, _commandType, _arguments)
Dim hT As Hashtable = DAL.SCRFL.GetProperties(_classObject)
Using cnn As IDbConnection = DataFactory.CreateConnection()
Dim cmd As IDbCommand = DataFactory.CreateCommand(s, cnn)
cmd.CommandText = s
For Each k In hT
Dim param As IDbDataParameter = cmd.CreateParameter()
param.Value = k.value
param.ParameterName = k.key
Debug.Print("typeMatch:={0}, value:={1}, name:={2}", TypeName(param.Value) = TypeName(k.value), param.Value, param.ParameterName)
cmd.Parameters.Add(param)
Next
If (cmd.ExecuteNonQuery > 0) Then
Return True
End If
End Using
Return False
End Function
So, the
DAL.SCRFL.GetParamStatement
returns string formatted as "
INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)
" etc., for insert (based on
_commandType
enum value this method will return appropriate strings for update, delete, select statements). All are done with reflection and I'm sure there is no syntax error here. I can manually execute returned values from this method through direct provider type commands through another method.
The
DAL.SCRFL.GetProperties
method returns a hashtable formatted as
key=property
(field),
value=field
value pairs.
Now, I need to create parameters for each these (
keys=values
) properties and add them to my command parameters collection, then execute the command (
ExecuteNonQuery
).
This attempt you will see in my code (I’m creating parameters from each propert/value pair by looping the hash table). However at the end of the method when I'm executing the command, I'm getting an exception with '
Data type mismatch in criteria expression.' description.
I've tried adding
type
property to parameters
object
,
size
, etc., all was unsuccessful (I commented them). I tried changing
param.Value = k.value
to
param.Value = If(IsDBNull(k.value), DBNull.Value, k.value)
thinking that this might be the problem, though
k.value
is from my business class and I intentionally prevent it from null values for this test. Here is the test returned values; business class returned value from
DAL.SCRFL.GetParamStatement
call: The test is done for OleDb/Access database and, as you can see, I enclosed the Memo field in single quotes so, I'm aware of that too. My reflection methods read class properties' attributes (which I set to be table field names) and
DAL.SCRFL.GetParamStatement
builds basic sql statements for insert, update, delete and select use.
AdapterCommandType
is a built in enum type for it).
INSERT INTO Clinics (ClinicId, ClinicName, Phone, Fax, FederalId, DateContracted, Address, City, State, Zip, Inactive, [Memo], DateEntered, EnteredBy, DateModified, ModifiedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
REMARK: I have another method similar to this that executes an sql statement (InvokeSql) where I thoroughly check value types for each property to construct
property=value
pairs in my sql statement. Using a "fully qualified sql statement" in this,
InvokeSql
, method works w/out a single warning (Rouphly:
cnn As IDbConnection = CreateConnection()
,
cmd = CreateCommand(_cmdText, cnn)
,
cmd.ExecuteNonQuery()
where
_cmdText
is the sql statement.
There is no parameters as you can see!).
I'm mentioning this to point out that the problem arises whenever I use parameters with generic
IDbCommands
. Even though inside my
DataFactory
the
IDbCommand
set to be provider specific command type (my
DataFactory.CreateCommand(s, cnn)
returns generic
IDbCommand
).
Prior to my new DAL project I was doing all above steps with explicitly declared to be provider specific type objects and I had no problem. Technically speaking I’m exercising exact same skeleton as I used to, but converting them to a generic type of objects (not provider specific). And I can’t make it work when there is a parameter and
IDbCommand
objects. There is somewhere, probably, I’m missing something.
If someone can direct me, please, where is my mistake?
Thanks in advance, Sam