Public Class Customer
Private _CustomerFirstName As String
Private _CustomerLastName As String
Private _CustomerIDNumber As String
Public Sub New()
_CustomerFirstName = ""
_CustomerLastName = ""
_CustomerIDNumber = ""
End Sub
Public Property CustomerFirstName() As String
Get
Return _CustomerFirstName
End Get
Set(ByVal value As String)
_CustomerFirstName = value
End Set
End Property
Public Property CustomerLastName() As String
Get
Return _CustomerLastName
End Get
Set(ByVal value As String)
_CustomerLastName = value
End Set
End Property
Public Property CustomerIDNumber() As String
Get
Return _CustomerIDNumber
End Get
Set(ByVal value As String)
_CustomerIDNumber = value
End Set
End Property
End Class
Assume you have an existing customer object that you want to generate SQL statement from. All you need now is to add in the attributes to the Get function of each property. Why put it at the GET function? This is because we will be using Reflection to extract the value. We will come to the Reflection Part later.
Below are the codes, you noticed that I set the Primary Key to True for CustomerIDNumber. This means that I have determined that the Customer ID number in the database is the Primary Key. You can determine your own Primary Key(s) , it is advisable to follow the relevant Database Table that the object is representing. If there are no Primary Keys set for the Database Table, you can determine your own for this object.
Public Property CustomerFirstName() As String
<DAO("FirstName", GetType(String), False)> _
Get
Return _CustomerFirstName
End Get
Set(ByVal value As String)
_CustomerFirstName = value
End Set
End Property
Public Property CustomerLastName() As String
<DAO("LastName", GetType(String), False)> _
Get
Return _CustomerLastName
End Get
Set(ByVal value As String)
_CustomerLastName = value
End Set
End Property
Public Property CustomerIDNumber() As String
<DAO("CustID", GetType(String), True)> _
Get
Return _CustomerIDNumber
End Get
Set(ByVal value As String)
_CustomerIDNumber = value
End Set
End Property
Executing it with Reflection
To retrieve the attributes from the object, we have to go deeper into the .NET framework. We are going to use classes in the Reflection namespace to extract the attribute details from the Customer object. Below are the codes to create a generic DbCommand object with a Select Statement with Parameters in place.
Private Function CreateSelectCommand(ByVal dataObject As Object, ByVal factory As DbProviderFactory, ByVal TableName As String) As DbCommand
Dim t As Type = dataObject.[GetType]()
Dim dao As DAOAttribute
Dim cmd As DbCommand = factory.CreateCommand()
Dim param As DbParameter
Dim Fields As New StringCollection()
Dim sbWhere As New StringBuilder(" WHERE ")
Dim HasCondition As Boolean = False
'Indicates that there is a WHERE Condition
For Each mi As System.Reflection.MethodInfo In t.GetMethods()
'Go thru each method of the object
For Each att As Attribute In Attribute.GetCustomAttributes(mi)
'Go thru the attributes for the method
If GetType(DAOAttribute).IsAssignableFrom(att.[GetType]()) Then
'Checks that the Attribute is of the right type
dao = DirectCast(att, DAOAttribute)
Fields.Add(dao.FieldName)
'Append the Fields
If dao.PrimaryKey Then
'Append the Conditions
If HasCondition Then
sbWhere.Append(" AND ")
End If
sbWhere.AppendFormat("{0} = @{0}", dao.FieldName)
param = factory.CreateParameter()
param.ParameterName = "@" + dao.FieldName
If cmd.Parameters.IndexOf(param.ParameterName) = 0 Then
param.DbType = DirectCast([Enum].Parse(GetType(DbType), dao.ValueType.Name), DbType)
cmd.Parameters.Add(param)
param.Value = mi.Invoke(obj, Nothing)
End If
'Set the HasCondition flag to true
HasCondition = True
End If
End If
Next
Next
Dim arrField As String() = New String(Fields.Count - 1) {}
Fields.CopyTo(arrField, 0)
cmd.CommandText = "SELECT " + String.Join(",", arrField) + " FROM " + TableName + (IIf(HasCondition, sbWhere.ToString(), " "))
Return cmd
End Function
Beyond Select Statements
This is a very practical example of using Attributes and Reflection. You may even create your own Insert Statement Commands with these codes. And also you may want to extend the DAOAttribute object to cater to your requirements.
Besides generating SQL statements, there are a lot of cool stuff that you can do with Attributes and Reflection, you can even create your own XML Attributes and Formatter for the classes you created!!!
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# Corner (http://www.c-sharpcorner.com/).