One of things I cannot get over is the sheer power and capability that the SubSonic project brings to any application you include it in. Today I got the chance to put even more of it's capability into practice within my one of my new web applications.
The Scenario: I need to build a dynamic page that lists my database tables and/or views and spits them out on the page.
The Solution:
Using a little bit of reflection with SubSonic, we can actually accomplish this very easily.
1. Start with the following line of code:
Dim t As New SubSonic.TableSchema.Table(SubSonic.DataService.Providers("MyDataProviderName"))
Replace the
MyDataProviderName string with the name you provided in the web.config for your database provide. This can be found here:
<SubSonicService defaultProvider="MyDataProviderName">
<providers>
<clear/>
<add name="MyDataProviderName" type="SubSonic.SqlDataProvider, SubSonic" ....
2. Place the code line from step one into the following loop:
For Each fi As FieldInfo In GetType(MyDataClassLibrary.MyNamespace.Tables).GetFields
Select Case fi.Name.ToLower
Case "x", "tostring"
'--- This is used to escape unwanted table names or values.
Case Else
Dim tNode As New TreeNode(fi.Name)
Dim t As New SubSonic.TableSchema.Table(SubSonic.DataService.Providers("MyDataProviderName"))
AddColumnNodes(tNode, fi.GetValue(t))
dNode.Nodes.Add(tNode)
End Select
Next
In the above code, I am actually building a tree view with the data. The important lines of code are the ones in bold, which highlight how you get around SubSonic's "remapping" of the database names.
Take a look in your
Tables namespace and you will notice the following:
Public Partial Structure Tables
Dim x As Integer
Public Shared MyTableOne As String = "my_table_one"
Public Shared MyTableTwo As String = "my_table_two"
Public Shared MyTableThree As String = "my_table_three"
...
End Structure
This means we need an object to exctract the field value from, which is where the following line of code comes from:
AddColumnNodes(tNode, fi.GetValue(t))
3. Now we just need a function to add column nodes to the table node.
Considder the following code:
Private Sub LoadTableNode(ByRef rootNode As TreeNode, ByVal TableName As String)
Dim q As New SubSonic.Query(TableName)
For Each c As SubSonic.TableSchema.TableColumn In q.Schema.Columns
Dim strNodeName As String = c.ColumnName
strNodeName &= " " & c.DataType.ToString
strNodeName &= "(" & c.MaxLength & ")"
Dim cNode As New TreeNode(strNodeName)
rootNode.Nodes.Add(cNode)
Next
End Sub
I am sure there are better ways to get at this information but this should give you the idea.
