Been a while since I blogged. I've been hellishly busy with a big project. I'm sure there's other templates out there, but here's a codesmith template that I use to generate a basic class and dictionary collection for a foreign key table.
What is a foreign key table? Maybe it's better called a lookup table? I don't know, I was having trouble finding the proper words to describe it. In brief, if you have a table called “Customers” and customers have many “Phone Numbers”, the Phone Numbers table is the foreign key table. The reason I make this distinction is because the template generates a getlist by customer id. I have a different template I use for the Customers table.
Anyways, the template also makes use of the Data Application Block by microsoft, and assumes the naming conventions used by the stored procedure generator that comes with codesmith. So here you go!
<%@ CodeTemplate Language="C#" TargetLanguage="VB" Description="Generates a collection and class from a database table" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="CollectionName" Type="String" Category="Context" Description="Name of collection to generate" %>
<%@ Property Name="ClassName" Type="String" Category="Context" Description="Name of class to generate" %>
<%@ Property Name="ClassPK" Type="String" Category="Context" Description="Primary key of class" %>
<%@ Property Name="LookupKey" Type="String" Category="Context" Description="Getlist key" %>
<%@ Property Name="DatabaseTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Name of database table to read from" %>
<%@ Property Name="Author" Type="String" Category="Context" Description="Name of author" %>
'****************************************************************************************
'* File: <%=ClassName%>.vb
'* Author: <%=Author%>
'* Created: <%= DateTime.Now.ToShortDateString()%>
'****************************************************************************************
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Public Class <%=CollectionName%>
Inherits DictionaryBase
Private _ConnectString As String
Public Sub New(ByVal connectString As String)
_ConnectString = connectString
End Sub
Public Sub New(ByVal connectString As String, ByVal <%=LookupKey%> As Integer)
_ConnectString = connectString
GetList(<%=LookupKey%>)
End Sub
Public ReadOnly Property Keys() As ICollection
Get
Return Dictionary.Keys
End Get
End Property
Public ReadOnly Property Values() As ICollection
Get
Return Dictionary.Values
End Get
End Property
Public Sub Add(ByVal new<%=ClassName%> As <%=ClassName%>)
Dictionary.Add(new<%=ClassName%>.<%=ClassPK%>, new<%=ClassName%>)
End Sub
Public Sub Remove(ByVal key As Integer)
Dictionary.Remove(key)
End Sub
Public Function Contains(ByVal key As Integer) As Boolean
Return Dictionary.Contains(key)
End Function
Default Public Property Item(ByVal key As Integer) As <%=ClassName%>
Get
Return CType(Dictionary(key), <%=ClassName%>)
End Get
Set(ByVal Value As <%=ClassName%>)
Dictionary(key) = Value
End Set
End Property
Public Sub GetList(ByVal <%=LookupKey%> As Integer)
Dim sqlParams(0) As SqlParameter
sqlParams(0) = New SqlParameter("@<%=LookupKey%>", SqlDbType.Int)
sqlParams(0).Value = <%=LookupKey%>
Dim dr As SqlDataReader = SqlHelper.ExecuteReader(_ConnectString, CommandType.StoredProcedure, "Get<%=DatabaseTable.Name%>List", sqlParams)
While dr.Read
Dim new<%=ClassName%> As New <%=ClassName%>(_ConnectString)
<% for (int i =0; i < DatabaseTable.Columns.Count; i++) { %>
If Not IsDBNull(dr("<%=DatabaseTable.Columns[i].Name%>")) Then new<%=ClassName%>.<%=DatabaseTable.Columns[i].Name%> = <%if(DatabaseTable.Columns[i].DataType.ToString() != "String") {%><%=DatabaseTable.Columns[i].DataType%>.Parse(dr("<%=DatabaseTable.Columns[i].Name%>"))<% } else { %>dr("<%=DatabaseTable.Columns[i].Name%>").ToString() <% } %>
<% } %>
Me.Add(new<%=ClassName%>)
End While
If Not dr.IsClosed Then dr.Close()
End Sub
End Class
Public Class <%=ClassName%>
#Region "Private Members"
Private _connectString As String
<% for (int i =0; i < DatabaseTable.Columns.Count; i++) { %>
Private _<%=DatabaseTable.Columns[i].Name%> As <%=DatabaseTable.Columns[i].DataType%> <%if(DatabaseTable.Columns[i].IsPrimaryKeyMember) { %> = -1 <% } %>
<% } %>
#End Region
#Region "Properties"
<% for (int i =0; i < DatabaseTable.Columns.Count; i++) { %>
Public Property <%=DatabaseTable.Columns[i].Name%> As <%=DatabaseTable.Columns[i].DataType%>
Get
Return _<%=DatabaseTable.Columns[i].Name%>
End Get
Set(ByVal Value As <%=DatabaseTable.Columns[i].DataType%>)
_<%=DatabaseTable.Columns[i].Name%> = Value
End Set
End Property
<% } %>
#End Region
Public Sub New(ByVal connectString As String)
_connectString = connectString
End Sub
Public Sub New(ByVal connectString As String, ByVal id As Integer)
Dim sqlParams(0) As SqlParameter
sqlParams(0) = New SqlParameter("@<%=ClassPK%>", SqlDbType.Int)
sqlParams(0).Value = id
Dim dr As SqlDataReader
Try
dr = SqlHelper.ExecuteReader(_connectString, CommandType.StoredProcedure, "Get<%=DatabaseTable.Name%>", sqlParams)
If dr.Read Then
<% for (int i =0; i < DatabaseTable.Columns.Count; i++) { %>
If Not IsDBNull(dr("<%=DatabaseTable.Columns[i].Name%>")) Then _<%=DatabaseTable.Columns[i].Name%> = <%if(DatabaseTable.Columns[i].DataType.ToString() != "String") {%><%=DatabaseTable.Columns[i].DataType%>.Parse(dr("<%=DatabaseTable.Columns[i].Name%>"))<% } else { %>dr("<%=DatabaseTable.Columns[i].Name%>").ToString() <% } %>
<% } %>
Else
Throw New Exception("<%=ClassPK%> not found!")
End If
Catch ex As Exception
Throw
Finally
If Not dr.IsClosed Then dr.Close()
End Try
End Sub
Public Sub Save()
Dim sqlParams(<%=DatabaseTable.Columns.Count%>) As SqlParameter
If _<%=ClassPK%> = -1 Then
'insert
<% for (int i = 1; i < DatabaseTable.Columns.Count; i++) { %>
sqlParams(<%=i-1%>) = New SqlParameter("@<%=DatabaseTable.Columns[i].Name%>", SqlDbType.<%=DatabaseTable.Columns[i].NativeType%>)
sqlParams(<%=i-1%>).Value = _<%=DatabaseTable.Columns[i].Name%>
<% } %>
sqlParams(<%=DatabaseTable.Columns.Count -1%>) = New SqlParameter("@<%=ClassPK%>", SqlDbType.Int)
sqlParams(<%=DatabaseTable.Columns.Count -1%>).Direction = ParameterDirection.Output
SqlHelper.ExecuteNonQuery(_connectString, CommandType.StoredProcedure, "Insert<%=DatabaseTable.Name%>", sqlParams)
_<%=ClassPK%> = sqlParams(<%=DatabaseTable.Columns.Count -1%>).Value
Else
'update
<% for (int i = 0; i < DatabaseTable.Columns.Count; i++) { %>
sqlParams(<%=i%>) = New SqlParameter("@<%=DatabaseTable.Columns[i].Name%>", SqlDbType.<%=DatabaseTable.Columns[i].NativeType%>)
sqlParams(<%=i%>).Value = _<%=DatabaseTable.Columns[i].Name%>
<% } %>
SqlHelper.ExecuteNonQuery(_connectString, CommandType.StoredProcedure, "Update<%=DatabaseTable.Name%>", sqlParams)
End If
End Sub
Public Sub Delete()
Dim sqlParams(0) As SqlParameter
sqlParams(0) = New SqlParameter("@<%=ClassPK%>", SqlDbType.Int)
sqlParams(0).Value = _<%=ClassPK%>
SqlHelper.ExecuteNonQuery(_connectString, CommandType.StoredProcedure, "Delete<%=DatabaseTable.Name%>", sqlParams)
End Sub
End Class