Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
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:
VB
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)
        'Debug.Print(s)
        Dim hT As Hashtable = DAL.SCRFL.GetProperties(_classObject)
        Using cnn As IDbConnection = DataFactory.CreateConnection()
            Dim cmd As IDbCommand = DataFactory.CreateCommand(s, cnn)
            'cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            For Each k In hT
                Dim param As IDbDataParameter = cmd.CreateParameter()
                'param.DbType = DataFactory.ConvertToDbType(k.value.GetType)

                param.Value = k.value
                param.ParameterName = k.key
                'param.Direction = ParameterDirection.Input

                'Debug.Print("value:={0}, name:={1}", TypeName(k.value), TypeName(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).
SQL
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
Posted
Updated 24-Aug-12 10:56am
v4

If I interpreted the question correctly, could it be possible that when you loop through the field names and the values, you're not getting the corresponding elements?

So basically instead of using hash table, perhaps using sorted lists or a dictionary of strin, object (field, value) would perhaps help.

Also, personally I would create a separate class for a parameter which would contain all the relevant information such as field name, value, type and so on and include the instances of that class to a single list. that list would then again be used when constucting the statements. Since the class itself is agnostic to any tables etc, it wouldn't be necessary to use reflection and that would perhaps enhance the performance of the code blocks also.
 
Share this answer
 
Comments
Samoxin 25-Aug-12 3:59am    
Regarding your comments (1st & 2nd paragraphs):
Field names returned from 'DAL.SCRFL.GetParamStatement' (sql statement) match with names from hash table BUT! Hast table does not keep it content in any order. I can definitely tell that hash table list order is different than the field order in the sql statement. Does it make any difference? I'm not sure if IDbCommand internally matches parameter name order with the field order in the statement. If so, that will explain 'Data type mismatch....' I'm going to test this. I'll let you know. Thank you for mentioning the 'LIST ORDER' term.
Wendelius 25-Aug-12 4:04am    
I would guess that the problem is in the mismathcing order for columns and values. Hopefully it solves the question :)
OK, Mr. Mika, WE GOT IT!

I was missing two points here.

1. Order of sql statement fields and added parameter names ARE matter even though these two data set independently: e.g.
"INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)" must match IDbCommand.Parameters.Add(param) order, meaning
param.ParameterName = f1
param.Value = v1
cmd.Parameters.Add(param)

param.ParameterName = f2
param.Value = v2
cmd.Parameters.Add(param)

param.ParameterName = f3
param.Value = v3
cmd.Parameters.Add(param)

etc.

2. I still have to maintain 'param.DbType' that I had commented. So, I enabled it.

So, I made a 'Dictionary(of String, String)' with class property and corresponding field names then I constructed another 'Dictionary(of String, Object)' with property name and corresponding values (note that I loop the 1st dictionary to get property values and build the 2nd one, so I kept the list order). The 'DAL.SCRFL.GetParamStatement' builds the sql statement from my 1st dictionary and The 'DAL.SCRFL.GetProperties' returns the 2nd dictionary.

Works!
Thanks Mika

About your comment regarding parameters (making them as a separate class). I totally agree with you. I Actually had one already in progress but because I had this problem I had to make some working/testing sub then update the original class.

Thanks again,
Sam
 
Share this answer
 
Here are few methods from business class:


Public Shared Function GetProperties(Of T)(_classObject As T) As Dictionary(Of String, Object)
Dim cT As Type = GetType(T)
Dim aL As Dictionary(Of String, String) = GetPropertyList(Of T)()
Dim pL As New Dictionary(Of String, Object)
For Each s As String In aL.Keys
Dim v As Object = _
cT.InvokeMember(s, _
(Reflection.BindingFlags.GetField Or _
Reflection.BindingFlags.GetProperty), _
Nothing,
_classObject,
Nothing)
pL.Add(s, v)
Next
Return pL
End Function

Public Shared Function GetPropertyList(Of T)() As Dictionary(Of String, String)
Dim cT As Type = GetType(T)
Dim pL() As Reflection.PropertyInfo = cT.GetProperties()
If pL IsNot Nothing AndAlso pL.Count > 0 Then
Dim cL As New Dictionary(Of String, String)
For Each pI As Reflection.PropertyInfo In pL
Dim aL() As Attribute = pI.GetCustomAttributes(GetType(AttColumnName), True)
If aL IsNot Nothing AndAlso aL.Count = 1 Then
Dim cA As AttColumnName = DirectCast(aL(0), AttColumnName)
cL.Add(pI.Name, cA.ColumnName)
End If
Next
Return cL
End If
Return Nothing
End Function

an attribute that I use to furnish the property:
<attributeusage(attributetargets.property> _
Public Class AttColumnName
Inherits Attribute

Private m_ColumnName As String = String.Empty
Private m_IsPrimaryKey As Boolean = False

Public Property ColumnName() As String
Get
Return m_ColumnName
End Get
Set(ByVal value As String)
m_ColumnName = value
End Set
End Property
Public Property IsPrimaryKey() As Boolean
Get
Return m_IsPrimaryKey
End Get
Set(ByVal value As Boolean)
m_IsPrimaryKey = value
End Set
End Property

Public Sub New(_columnName As String)
Me.new(_columnName, False)
End Sub
Public Sub New(_columnName As String, _isPrimaryKey As Boolean)
Me.ColumnName = _columnName
Me.IsPrimaryKey = _isPrimaryKey
End Sub

End Class

a sample property from business object:
<attcolumnname("clinicid",> _
Public Property ClinicId() As String
Get
Return m_ClinicId
End Get
Set(value As String)
m_ClinicId = value
End Set
End Property


etc.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900