PropertyInfo SetValue Question

[Tony Tullemans]

I am trying to write a subroutine that will examine all
the properties of a form to determine which of those are
SqlCommand objects, and then set the Connection property
of those SqlCommands to a passed-in SqlCommand object. I
can find the properties OK but am unsuccessful in trying
to change their value due to the way I have identified
those SqlCommand objects (as a PropertyInfo). Can anyone
give me a hint (or suggest a better way) as I am totally
bamboolzed? I have tried PropertyInfo.SetValue and
MethodInfo.Invoke but all seem to get the same exception
"Object does not match target type".

I have included some sample code:

Public Sub SetDBConnections(ByVal TargetForm As Form,
ByVal DBConnection As SqlClient.SqlConnection)
Dim myType As Type = TargetForm.GetType
Dim myPropertyInfoList As
System.Reflection.PropertyInfo() = myType.GetProperties
((System.Reflection.BindingFlags.NonPublic Or
System.Reflection.BindingFlags.Public Or
System.Reflection.BindingFlags.Instance))
Dim myPropertyInfo As System.Reflection.PropertyInfo
Try
For Each myPropertyInfo In myPropertyInfoList
If myPropertyInfo.PropertyType Is GetType
(SqlClient.SqlCommand) Then
Dim mySQLCmdType As Type =
myPropertyInfo.PropertyType
Dim mySQLCmdPropertyInfoList As
System.Reflection.PropertyInfo() =
mySQLCmdType.GetProperties
((System.Reflection.BindingFlags.NonPublic Or
System.Reflection.BindingFlags.Public Or
System.Reflection.BindingFlags.Instance))
Dim mySQLCmdPropertyInfo As
System.Reflection.PropertyInfo
For Each mySQLCmdPropertyInfo In
mySQLCmdPropertyInfoList
If mySQLCmdPropertyInfo.PropertyType Is
GetType(SqlClient.SqlConnection) Then
Try
''This technique fails
'Dim min As System.Reflection.MethodInfo
= mySQLCmdPropertyInfo.GetSetMethod
'min.Invoke(Me, New
SqlClient.SqlConnection() {DBConnection})

'So does this technique
If mySQLCmdPropertyInfo.CanWrite Then
mySQLCmdPropertyInfo.SetValue
(myPropertyInfo, DBConnection, Nothing)
End If

Exit For
Catch ex As Exception
MessageBox.Show
(ex.Message, "SetDBConnections Exception",
MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit For
End Try
End If
Next
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message, "SetDBConnections",
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub

 

[Mattias Sjögren]

Tony,

mySQLCmdPropertyInfo.SetValue(myPropertyInfo, DBConnection, Nothing)

This should probably be

mySQLCmdPropertyInfo.SetValue(myPropertyInfo.GetValue(TargetForm,
Nothing), DBConnection, Nothing)

But since AFAICT SqlCommand only has one single property of type
SqlConnection (the Connection property) I'm not sure you you're
setting it in a late bound way in a loop. I'd do

Dim cmd As SqlCommand = DirectCast(myPropertyInfo.GetValue(TargetForm,
Nothing), SqlCommand)
cmd.Connection = DBConnection



Mattias

 

[Tony Tullemans]

Hi Mattias,

That worked perfectly - thank you very much!

The reason why I am setting it late bound is that my forms can contain
many SqlCommand objects that are part of the SqlDataAdapters that I have
drop into the component tray. As the command object's connections are
set to my design time values I need to change the values once deployed
to a client's site. On my main startup form I also have a connection
object whose values are changed depending on the site where the
application is being run. I then use this master connection object by
passing it as an argument to each form's Open method, which in turn runs
the subroutine I posted to set the Connection property of each
SqlCommand to work with the client's database. Is this explanation
clear enough?

There might be a better way of doing things but this works well as I use
a lot of form's inheritance and can create systems very quickly.

Anyway, thanks once again for your help.

Tony (Gold Coast, Australia)