##|TYPE Template
##|UNIQUEID a4caa4ab-9ac2-4056-9eb2-004162ac676d
##|TITLE Roberto DAAB Template
##|NAMESPACE RobertoSistemas
##|SOURCE_TYPE Source
##|OUTPUT_LANGUAGE VB.Net
##|COMMENTS_BEGIN
From a Table
##|COMMENTS_END
##|GUI_ENGINE Microsoft Script
##|GUI_LANGUAGE VBScript
##|GUI_BEGIN
Dim cmbDatabases
Dim lstTables
Dim chk

Sub setup()

	If Not input.Contains("lstTables") Or Not input.Contains("txtPath") Then

		ui.Title = "Generate VB.NET Business Objects"
		ui.Width  = 400
		ui.Height = 500

		' Grab default output path
		Dim sOutputPath
		sOutputPath = ""
		If input.Contains("defaultOutputPath") Then 
			sOutputPath = input.Item("defaultOutputPath")
		End If
		
		Dim sNamespace
		sNamespace = ""
		ui.AddLabel "lblNamespace", "Namespace: ",  "Namespace."
		ui.AddTextBox "txtNamespace", sNamespace, "Type the Namespace."

		ui.AddLabel "lblPath", "Output file path: ",  "Select the output path."
		ui.AddTextBox "txtPath", sOutputPath, "Select the Output Path."
		ui.AddFilePicker "btnPath", "Select Path", "Select the Output Path.", "txtPath", true

		' List Databases in a ComboBox
		ui.AddLabel "lblDatabases", "Select a database:", "Select a database in the dropdown below."
		Set cmbDatabases = ui.AddComboBox("cmbDatabase", "Select a database.")

		' List Tables in a listbox
		ui.AddLabel "lblTables", "Select tables:", "Select tables from the listbox below."
		Set lstTables = ui.AddListBox ("lstTables", "Select tables:")
		'lstTables.IsMultiSelect = false
		lstTables.Height = 120
		
		Set prefix = ui.AddCheckBox("prefix", "Prefix class with underscore?", false, "If checked, the file on disk will begin with an underscore")
	
		If MyMeta.DriverString = "FIREBIRD" Then
			Set chk = ui.AddCheckBox("ckDialect3", "DIALECT 3", false, "If checked, the stored procedure will use dialect 3 syntax")
			chk.forecolor = "red"
		End If		

		' Attach the onchange event to the cmbDatabases control.
		setupDatabaseDropdown cmbDatabases
		cmbDatabases.AttachEvent "onchange", "cmbDatabases_onchange"
		
		ui.ShowGUI = true
	Else 
		ui.ShowGUI = false
	End if

End Sub

Sub setupDatabaseDropdown(cmbDatabases)
	cmbDatabases.BindData MyMeta.Databases

	If Not MyMeta.DefaultDatabase Is Nothing Then
		cmbDatabases.SelectedValue = MyMeta.DefaultDatabase.Name
		bindTables cmbDatabases.SelectedValue
	End If
End Sub

Sub bindTables(sDatabase)
	Set db = MyMeta.Databases.Item(sDatabase)
	lstTables.BindData(db.Tables)
End Sub

' Event Handler
Sub cmbDatabases_onchange(control)
	Set cmbDatabases = ui.item("cmbDatabase")
	bindTables cmbDatabases.SelectedText
End Sub
##|GUI_END
##|BODY_MODE Markup
##|BODY_ENGINE Microsoft Script
##|BODY_LANGUAGE VBScript
##|BODY_TAG_START <%
##|BODY_TAG_END %>
##|BODY_BEGIN
<%
'-----------------------------------------------------------------------------
' Developer: Roberto Carlos
' E-Mail: robertosistemas@gmail.com
'-----------------------------------------------------------------------------
Dim bFirst
Dim name
Dim pname
Dim objTable
Dim objColumn
Dim tableNames
Dim language
Dim databaseName
Dim database
Dim namespace
Dim props
Dim bOtherColumns

Dim IDbCommand
Dim IDataParameter
Dim ParameterPrefix

Dim prefix
Dim dialect
Dim LevelCode

Dim exportPath
Dim exportfileName

LevelCode = 0

' Grab the namespace
namespace = input.Item("txtNamespace")

prefix = input.Item("prefix")

dialect = 1
If input.Item("ckDialect3") Then
	dialect = 3
End If


' Set the Language for our column data types

' Grab the choices the user made in our UI Script (see Interface Code tab)
Set tableNames = input.Item("lstTables")

databaseName  = input.Item("cmbDatabase")
Set database = MyMeta.Databases(databaseName)
	
exportPath = input.item("txtPath")

If InStrRev(exportPath, "\") <> Len(exportPath) Then
	exportPath = exportPath & "\"
End If
%>Option Explicit On
Option Strict On

Imports System.Collections.Generic
<%if namespace <> "" then
	output.autoTabLn "Namespace " & namespace
	LevelCode = 1
	output.tabLevel = LevelCode
end if
%>Partial Public Class MethodResult

	Private _Messages As List(Of String)

	Public Sub New()
		_Messages = New List(Of String)
	End Sub

	Public Property Messages() As List(Of String)
		Get
			Return _Messages
		End Get
		Set(ByVal value As List(Of String))
			_Messages = value
		End Set
	End Property

End Class
<%
if namespace <> "" then
	LevelCode = 0
	output.tabLevel = LevelCode
	output.autoTabLn "End Namespace"
end if
exportfileName = "MethodResult.vb"
output.save exportPath & exportfileName, false
buffer = buffer & output.text
output.clear

'-----------------------------------------------------------------------------

' Loop through the tables the user selected and generate the business entities
For intLp = 0 To tableNames.Count - 1

	Set objTable = database.Tables(tablenames.item(intLp))
	Set props = objTable.Properties
	
	bOtherColumns = (objTable.Columns.Count > objTable.PrimaryKeys.Count)

	LevelCode = 0
	output.tabLevel = LevelCode

%>Option Explicit On
Option Strict On

Imports System.ComponentModel
<%
if namespace <> "" then
	output.autoTabLn "Namespace " & namespace
	LevelCode = 1
	output.tabLevel = LevelCode
end if

if prefix then
	output.autoTabLn "Partial Public Class _" & GetObjectName(objTable)
else
	output.autoTabLn "Partial Public Class " & GetObjectName(objTable)
End If

%>#Region "Column Names"
<% 
	For Each objColumn in objTable.Columns		
		output.autoTabLn "Public Const c" & GetDbName(objColumn) & " As String = """ & GetDbName(objColumn) & """"
	Next
%>#End Region

#Region "Private Members"
<% 
	output.tabLevel = LevelCode + 1
	For Each objColumn in objTable.Columns		

		if objColumn.IsNullable then
			if objColumn.LanguageType = "String" then
				output.autoTabLn "Private " & GetPrivateName(objColumn) & " As " & objColumn.LanguageType
			Elseif objColumn.LanguageType = "Byte()" then
				output.autoTabLn "Private " & GetPrivateName(objColumn) & " As " & objColumn.LanguageType
			else
				output.autoTabLn "Private " & GetPrivateName(objColumn) & " As Nullable(Of " & objColumn.LanguageType & ")"
			End If
		else
			output.autoTabLn "Private " & GetPrivateName(objColumn) & " As " & objColumn.LanguageType
		end if

	Next
%>#End Region

#Region "Public Properties"
<%
		For Each objColumn in objTable.Columns

			output.tabLevel = LevelCode + 1
			output.autoTabLn ""
			output.autoTab "<DataObjectField("
			if objColumn.IsInPrimaryKey then
				output.write "True, True"
			Else
				output.write "False, False"
			End If
			if objColumn.IsNullable then
				output.write ", True"
			Else
				output.write ", False"
			End If
			if objColumn.CharacterMaxLength > 0 then
				output.write ", " & objColumn.CharacterMaxLength
			End If
			output.write ")> _" & vbcrlf
			output.autoTab "Public "
			If objColumn.IsComputed OR (lcase(objColumn.Name) = "createdate") OR (lcase(objColumn.Name) = "modifydate") Then
				output.write "ReadOnly "
			End If
			output.write "Property " & GetPublicName(objColumn) & "() As "
			if objColumn.IsNullable then
				if objColumn.LanguageType = "String" then
					output.writeln objColumn.LanguageType
				Elseif objColumn.LanguageType = "Byte()" then
					output.writeln objColumn.LanguageType
				else
					output.writeln "Nullable(Of " & objColumn.LanguageType & ")"
				end if
			else
				output.writeln objColumn.LanguageType
			end if
			output.tabLevel = LevelCode + 2
			output.autoTabLn "Get"
			output.tabLevel = LevelCode + 3
			output.autoTabLn "Return " & GetPrivateName(objColumn)
			output.tabLevel = LevelCode + 2
			output.autoTabLn "End Get"

			If NOT ((objColumn.IsComputed) OR ((lcase(objColumn.Name) = "createdate")) OR ((lcase(objColumn.Name) = "modifydate"))) Then
				output.tabLevel = LevelCode + 2
				if objColumn.IsNullable then
					if objColumn.LanguageType = "String" then
						output.autoTabLn "Set(ByVal Value As " & objColumn.LanguageType & ")"
					Elseif objColumn.LanguageType = "Byte()" then
						output.autoTabLn "Set(ByVal Value As " & objColumn.LanguageType & ")"
					else
						output.autoTabLn "Set(ByVal Value As " & "Nullable(Of " & objColumn.LanguageType & ")" & ")"
					end if
				else
					output.autoTabLn "Set(ByVal Value As " & objColumn.LanguageType & ")"
				end if
				output.tabLevel = LevelCode + 3
				output.autoTabLn GetPrivateName(objColumn) & " = Value"
				output.tabLevel = LevelCode + 2
				output.autoTabLn "End Set"
			End If

			output.tabLevel = LevelCode + 1
			output.autoTabLn "End Property"

		Next
%>#End Region		
<%
' finaliazar entidade

output.tabLevel = LevelCode
output.autoTabLn "End Class"

if namespace <> "" then
	LevelCode = 0
	output.tabLevel = LevelCode
	output.autoTabLn "End Namespace"
end if

If prefix = True Then 
	exportfileName = "_" & GetObjectName(objTable) & ".vb"
Else 
	exportfileName = GetObjectName(objTable) & ".vb"
End If
	
output.save exportPath & exportfileName, false
buffer = buffer & output.text
output.clear
'-----------------------------------------------------------------------------	

%>Option Explicit On
Option Strict On

Imports System.ComponentModel
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
<%

if namespace <> "" then

	LevelCode = LevelCode + 1

	output.autoTabLn "Namespace " & namespace

end if

output.tabLevel = LevelCode
output.autoTabLn "<DataObject(True)> _"
if prefix then
	output.autoTabLn "Partial Public Class _" & GetObjectName(objTable) & "DAL"
else
	output.autoTabLn "Partial Public Class " & GetObjectName(objTable) & "DAL"
End If

%>
#Region "Public Methods"
<%

	output.tabLevel = LevelCode + 1
	output.autoTabLn ""
	output.autoTabLn "Private mCountRecords As Integer = 0"

	output.tabLevel = LevelCode + 1
	output.autoTabLn ""
	output.autoTabLn "Public Sub CountRecords(ByVal pageSize As Integer, _"
	output.autoTabLn "                        ByRef totalRecords As Integer, _"
	output.autoTabLn "                        ByRef totalPages As Integer, _"
	output.autoTabLn "                        Optional ByVal filterExpression As String = """")"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "Dim _Mod As Double = 0"
    output.autoTabLn "Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()"
	output.autoTabLn "Dim msql As New StringBuilder"
	output.autoTabLn "msql.AppendLine(""Select count(*) From " & objTable.Alias & """)"
	output.autoTabLn "If Not String.IsNullOrEmpty(filterExpression) Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "msql.AppendLine(String.Concat("" WHERE "", filterExpression))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
    output.autoTabLn "Using dbCmd As DbCommand = db.GetSqlStringCommand(msql.ToString)"
	output.tabLevel = LevelCode + 3
    output.autoTabLn "totalRecords = CInt(db.ExecuteScalar(dbCmd))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End Using"
    output.autoTabLn "_Mod = (totalRecords Mod pageSize)"
	output.autoTabLn "If _Mod > 0 Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "totalPages = CInt((((totalRecords - _Mod) / pageSize) + 1))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "Else"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "totalPages = CInt((totalRecords / pageSize))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
	output.tabLevel = LevelCode + 1
	output.autoTabLn "End Sub"
	output.autoTabLn ""
	
	output.tabLevel = LevelCode + 1
	output.autoTabLn ""
	output.autoTabLn "Public Function GetCount(ByVal startRowIndex As Integer, _"
	output.autoTabLn "                         ByVal maximumRows As Integer, _"
	output.autoTabLn "                         Optional ByVal filterExpression As String = """", _"
	output.autoTabLn "                         Optional ByVal sortExpression As String = """") As Integer"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "If mCountRecords <= 0 Then"
	output.tabLevel = LevelCode + 3
    output.autoTabLn "Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()"
	output.autoTabLn "Dim msql As New StringBuilder"
	output.autoTabLn "msql.AppendLine(""Select count(*) From " & objTable.Alias & """)"
	output.autoTabLn "If Not String.IsNullOrEmpty(filterExpression) Then"
	output.tabLevel = LevelCode + 4
	output.autoTabLn "msql.AppendLine(String.Concat("" WHERE "", filterExpression))"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "End If"
    output.autoTabLn "Using dbCmd As DbCommand = db.GetSqlStringCommand(msql.ToString)"
	output.tabLevel = LevelCode + 4
    output.autoTabLn "mCountRecords = CInt(db.ExecuteScalar(dbCmd))"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "End Using"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
    output.autoTabLn "Return mCountRecords"
	output.tabLevel = LevelCode + 1
    output.autoTabLn "End Function"
	output.autoTabLn ""

If objTable.PrimaryKeys.Count > 0 Then

		output.tabLevel = LevelCode + 1
		output.autoTabLn "<DataObjectMethod(DataObjectMethodType.Select, False)> _"
		output.autoTab "Public Function GetByKey("

		bFirst = true
		For Each objColumn in objTable.PrimaryKeys
			If Not bFirst Then
				output.write ", "
			End If
			output.write "ByVal in" & objColumn.Name & " As " & objColumn.LanguageType
			bFirst = false
		Next 
		output.write ") As "
		if prefix then
			output.writeln "_" & GetObjectName(objTable)
		else
			output.writeln GetObjectName(objTable)
		End If

		output.tabLevel = LevelCode + 2
		output.autoTab "Dim oResultList As List(Of "
		if prefix then
			output.writeln "_" & GetObjectName(objTable) & ")"
		else
			output.writeln GetObjectName(objTable) & ")"
		End If
		
		output.autoTabLn "Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()"
		output.autoTab "Using dbCmd As DbCommand = db.GetSqlStringCommand(""Select * From " & objTable.Alias & " Where "
		bFirst = true
		For Each objColumn in objTable.PrimaryKeys
			If Not bFirst Then
				output.write " AND "
			End If
			output.write objColumn.Name & " = @" & objColumn.Name
			bFirst = false
		Next 
		output.writeln """)"
		
		For Each objColumn in objTable.PrimaryKeys
			output.tabLevel = LevelCode + 3
			output.autoTabLn "db.AddInParameter(dbCmd, ""@" & GetDbName(objColumn) & """, " & GetDbType(objColumn) & ", in" & GetDbName(objColumn) & ")"
		Next
		
		output.autoTabLn "Using rdr As IDataReader = db.ExecuteReader(dbCmd)"
		output.tabLevel = LevelCode + 4
        output.autoTabLn "oResultList = LoadFromReader(rdr)"
		output.tabLevel = LevelCode + 3
        output.autoTabLn "End Using"
		
		output.tabLevel = LevelCode + 2
        output.autoTabLn "End Using"
        
		output.autoTabLn "If oResultList.Count = 0 Then"
		output.tabLevel = LevelCode + 3
        output.autoTabLn "Return Nothing"
		output.tabLevel = LevelCode + 2
        output.autoTabLn "Else"
		output.tabLevel = LevelCode + 3
		output.autoTabLn "Return oResultList.Item(0)"
		output.tabLevel = LevelCode + 2
        output.autoTabLn "End If"
		
		output.tabLevel = LevelCode + 1
		output.autoTabLn "End Function"
	
	End If
	
	output.tabLevel = LevelCode + 1
	output.autoTabLn ""
	output.autoTabLn "<DataObjectMethod(DataObjectMethodType.Select, True)> _"
	output.autoTabLn "Public Function GetAll(ByVal startRowIndex As Integer, _"
	output.autoTabLn "                       ByVal maximumRows As Integer, _"
	output.autoTabLn "                       Optional ByVal filterExpression As String = """", _"
	output.autoTab "                       Optional ByVal sortExpression As String = """") As List(Of "
	if prefix then
		output.writeln "_" & GetObjectName(objTable) & ")"
	else
		output.writeln GetObjectName(objTable) & ")"
	End If
	output.tabLevel = LevelCode + 2
	output.autoTab "Dim oResult As List(Of "
	if prefix then
		output.writeln "_" & GetObjectName(objTable) & ")"
	else
		output.writeln GetObjectName(objTable) & ")"
	End If

	output.autoTabLn "Dim msql As New StringBuilder"
	output.autoTabLn "msql.AppendLine("" Select "")"
	output.autoTabLn "If maximumRows > 0 Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "msql.AppendLine(String.Format("" first({0}) "", maximumRows))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
	output.autoTabLn "If startRowIndex > 0 Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "msql.AppendLine(String.Format("" skip({0}) "", startRowIndex))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
	output.autoTabLn "msql.AppendLine("" * From " & objTable.Alias & " "")"

	'output.autoTabLn "msql.AppendLine(String.Format(""Select first({0}) skip({1}) * From " & objTable.Alias & """, maximumRows, startRowIndex))"
	
	output.autoTabLn "If Not String.IsNullOrEmpty(filterExpression) Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "msql.AppendLine(String.Concat("" WHERE "", filterExpression))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"
	output.autoTabLn "If Not String.IsNullOrEmpty(sortExpression) Then"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "msql.AppendLine(String.Concat("" ORDER BY "", sortExpression))"
	output.tabLevel = LevelCode + 2
	output.autoTabLn "End If"

	output.autoTabLn "Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()"
    output.autoTabLn "Using dbCmd As DbCommand = db.GetSqlStringCommand(msql.ToString)"
	output.tabLevel = LevelCode + 3
	output.autoTabLn "Using rdr As IDataReader = db.ExecuteReader(dbCmd)"
	output.tabLevel = LevelCode + 4
    output.autoTabLn "oResult = LoadFromReader(rdr)"
	output.tabLevel = LevelCode + 3
    output.autoTabLn "End Using"
	output.tabLevel = LevelCode + 2
    output.autoTabLn "End Using"
    output.autoTabLn "Return oResult"
	output.tabLevel = LevelCode + 1
    output.autoTabLn "End Function" %>

	<DataObjectMethod(DataObjectMethodType.Insert, True)> _
	Public Function Insert(<%
	output.write "p" & GetObjectName(objTable) & " As "
	if prefix then
		output.write "_"
	End If
	output.write GetObjectName(objTable)
%>) As MethodResult
		Dim oResult As New MethodResult()
		Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
		Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Insert Into <%= objTable.Alias %> (<%		
		bFirst = True
		If TableHasIntegerPrimaryKey(objTable) Then
			For Each objColumn in objTable.Columns
				If Not objColumn.IsInPrimaryKey Then
					If Not bFirst Then
						output.write ", "
					End If
					output.write GetDbName(objColumn)
					bFirst = False
				End If
			Next
		Else
			For Each objColumn in objTable.Columns
				If Not bFirst Then
					output.write ", "
				End If
				output.write GetDbName(objColumn)
				bFirst = False
			Next
		End If
		%>) Values (<%
		bFirst = True
		If TableHasIntegerPrimaryKey(objTable) Then
			For Each objColumn in objTable.Columns
				If Not objColumn.IsInPrimaryKey Then
					If Not bFirst Then
						output.write ", "
					End If
					output.write "@" & GetDbName(objColumn)
					bFirst = False
				End If
			Next
		Else
			For Each objColumn in objTable.Columns
				If Not bFirst Then
					output.write ", "
				End If
				output.write "@" & GetDbName(objColumn)
				bFirst = False
			Next
		End If
		Output.Write ")"
		If TableHasIntegerPrimaryKey(objTable) Then
			'output.write " Select SCOPE_IDENTITY()"
			output.write " RETURNING "

			bFirst = true
			For Each objColumn in objTable.PrimaryKeys
				If Not bFirst Then
					output.write ", "
				End If
				output.write objColumn.Name
				bFirst = false
			Next 

		End If
		Output.Write """)"
		%>
<%
		For Each objColumn in objTable.Columns

			output.tabLevel = LevelCode + 2

			If objColumn.IsComputed Or objColumn.IsInPrimaryKey Then
				
			Else

				if objColumn.IsNullable then

					if (objColumn.LanguageType = "String") then
						output.autoTabLn "If Not String.IsNullOrEmpty(" & "p" & GetObjectName(objTable) & "." & objColumn.Name & ") Then"
					Elseif (objColumn.LanguageType = "Byte()") then
						output.autoTabLn "If Not p" & GetObjectName(objTable) & "." & objColumn.Name & " Is Nothing Then"
					Else
						output.autoTabLn "If p" & GetObjectName(objTable) & "." & objColumn.Name & ".HasValue Then"
					End If
					output.tabLevel = LevelCode + 3
					output.autoTab "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", " 
					If (lcase(objColumn.Name) = "createdate") Or (lcase(objColumn.Name) = "modifydate") Then
						output.write "DateTime.Now()"
					Else
						output.write "p" & GetObjectName(objTable) & "." & objColumn.Name
					End If
					output.write ")" & vbcrlf
					output.tabLevel = LevelCode + 2
					output.autoTabLn "Else"
					output.tabLevel = LevelCode + 3
					output.autoTabLn "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", System.DBNull.Value)" 
					output.tabLevel = LevelCode + 2
					output.autoTabLn "End If"
				
				else
					output.tabLevel = LevelCode + 2
					output.autoTab "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", " 
					If (lcase(objColumn.Name) = "createdate") Or (lcase(objColumn.Name) = "modifydate") Then
						output.write "DateTime.Now()"
					Else
						output.write "p" & GetObjectName(objTable) & "." & objColumn.Name
					End If
					output.write ")" & vbcrlf
				end if
			End If
		Next
 
		If TableHasIntegerPrimaryKey(objTable) Then%>
			<%= "p" & GetObjectName(objTable) & "." & GetDbName(objTable.PrimaryKeys(0))%> = CInt(db.ExecuteScalar(dbCmd))
            If <%= "p" & GetObjectName(objTable) & "." & GetDbName(objTable.PrimaryKeys(0))%> <= 0 Then
                oResult.Messages.Add("Insert Failed")
            End If<%
		Else%>
			db.ExecuteScalar(dbCmd)<%
		
		End If%>
        End Using
        Return oResult
    End Function<%
	If objTable.PrimaryKeys.Count > 0 Then%>
	
	<DataObjectMethod(DataObjectMethodType.Update, True)> _
	Public Function Update(<%
	output.write "p" & GetObjectName(objTable) & " As "
	if prefix then
		output.write "_"
	End If
	output.write GetObjectName(objTable)
%>) As MethodResult
		Dim oResult As New MethodResult()
		Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
		Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Update <%= objTable.Alias %> Set <%		
		bFirst = True
		For Each objColumn in objTable.Columns
			If ((Not objColumn.IsInPrimaryKey) And (Not (lcase(objColumn.Name) = "createdate"))) Then
				If Not bFirst Then
					output.write ", "
				End If
				output.write GetDbName(objColumn) & " = @" & GetDbName(objColumn)
				bFirst=false
			End if
		Next
		%> Where <%

		bFirst = true
		For Each objColumn in objTable.PrimaryKeys
	
			If Not bFirst Then
				output.write " AND "
			End If
	
			output.write GetDbName(objColumn) & " = @" & GetDbName(objColumn)
	
			bFirst = false
		Next %>")
			
<%
		For Each objColumn in objTable.Columns

			output.tabLevel = LevelCode + 2

			'need to include primary keys for updates because of the where clause
			
			If objColumn.IsComputed or objColumn.IsInPrimaryKey or (lcase(objColumn.Name) = "createdate") Then
				
			Else
			
				if objColumn.IsNullable then

					if (objColumn.LanguageType = "String") then
						output.autoTabLn "If Not String.IsNullOrEmpty(" & "p" & GetObjectName(objTable) & "." & objColumn.Name & ") Then"
					Elseif (objColumn.LanguageType = "Byte()") then
						output.autoTabLn "If Not p" & GetObjectName(objTable) & "." & objColumn.Name & " Is Nothing Then"
					Else
						output.autoTabLn "If p" & GetObjectName(objTable) & "." & objColumn.Name & ".HasValue Then"
					End If
					
					output.tabLevel = LevelCode + 3
					output.autoTab "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", " 
					If (lcase(objColumn.Name) = "createdate") Or (lcase(objColumn.Name) = "modifydate") Then
						output.write "DateTime.Now()"
					Else
						output.write "p" & GetObjectName(objTable) & "." & objColumn.Name
					End If
					output.write ")" & vbcrlf
					
					output.tabLevel = LevelCode + 2
					output.autoTabLn "Else"
					output.tabLevel = LevelCode + 3
					output.autoTabLn "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", System.DBNull.Value)" 
					output.tabLevel = LevelCode + 2
					output.autoTabLn "End If"
				
				else
					output.tabLevel = LevelCode + 2
					output.autoTab "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", " 
					If lcase(objColumn.Name) = "modifydate" Then
						output.write "DateTime.Now()"
					Else
						output.write "p" & GetObjectName(objTable) & "." & objColumn.Name
					End If
					output.write ")" & vbcrlf
				end if
			End If
		Next
		
		For Each objColumn in objTable.PrimaryKeys
			output.autoTab "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", " 
			output.write "p" & GetObjectName(objTable) & "." & objColumn.Name
			output.write ")" & vbcrlf
		Next 		
%>
			If CInt(db.ExecuteNonQuery(dbCmd)) <> 1 Then
                oResult.Messages.Add("Update Failed")
            End If
        End Using
        Return oResult
    End Function<%
	End If
	If objTable.PrimaryKeys.Count > 0 Then%>

	<DataObjectMethod(DataObjectMethodType.Delete, True)> _
	Public Function Delete(<% 
		bFirst = true
		For Each objColumn in objTable.PrimaryKeys
			If Not bFirst Then
				output.write ", "
			End If
			output.write "ByVal in" & objColumn.Name & " As " & objColumn.LanguageType
			bFirst = false
		Next 
	%>) As MethodResult
        Dim oResult As New MethodResult()
        Dim db As Microsoft.Practices.EnterpriseLibrary.Data.Database = Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
        Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Delete From <%= objTable.Alias %> Where <%
		bFirst = true
		For Each objColumn in objTable.PrimaryKeys

			If Not bFirst Then
				output.write " AND "
			End If

			output.write GetDbName(objColumn) & " = @" & GetDbName(objColumn)

			bFirst = false
		Next
		%>")
<%			
		If objTable.PrimaryKeys.Count > 0 Then
			For Each objColumn in objTable.PrimaryKeys
				output.autoTabLn "db.AddInParameter(dbCmd, ""@" & objColumn.Name & """, " & GetDbType(objColumn) & ", in" & objColumn.Name & ")"
			Next
		End If 	%>
            If CInt(db.ExecuteScalar(dbCmd)) <> 1 Then
                oResult.Messages.Add("Delete Failed")
            End If
        End Using
        Return oResult
    End Function<%
	End If%>
#End Region

#Region "Private Methods"
    Private Function LoadFromReader(ByRef inDataReader As IDataReader) As List(Of <%
		if prefix then
			output.write "_" & GetObjectName(objTable) & ")"
		else
			output.write GetObjectName(objTable) & ")"
		End If
%>
		Dim oResult As New List(Of <%
		if prefix then
			output.write "_" & GetObjectName(objTable) & ")"
		else
			output.write GetObjectName(objTable) & ")"
		End If
%>
        If (Not inDataReader Is Nothing) And (Not inDataReader.IsClosed) Then
            While inDataReader.Read()
                Dim o<%= GetObjectName(objTable) %> As New <%
		if prefix then
			output.write "_" & GetObjectName(objTable) & "()"
		else
			output.write GetObjectName(objTable) & "()"
		End If%>
				<% 
				For Each objColumn in objTable.Columns %>
				If Not IsDBNull(inDataReader("<%= GetDbName(objColumn)%>")) Then
					o<%= GetObjectName(objTable)%>.<%= objColumn.Name%> = CType(inDataReader("<%= GetDbName(objColumn)%>"), <%= objColumn.LanguageType %>)
				else<%
				If objColumn.LanguageType = "String" Then%>
					o<%= GetObjectName(objTable)%>.<%= objColumn.Name%> = String.Empty
				<%ElseIf objColumn.LanguageType = "Byte()" Then%>
					o<%= GetObjectName(objTable)%>.<%= objColumn.Name%> = Nothing
				<%else%>
					o<%= GetObjectName(objTable)%>.<%= objColumn.Name%> = New <%= objColumn.LanguageType %>()
				<%End If%>End If<%
				Next%>
                oResult.Add(o<%= GetObjectName(objTable) %>)
            End While
			inDataReader.Close()
        End If
        
<%
	output.tabLevel = LevelCode + 2
	output.autoTabLn "Return oResult"

	output.tabLevel = LevelCode + 1
	output.autoTabLn "End Function"
%>
#End Region

<%

output.tabLevel = LevelCode
output.autoTabLn "End Class"

if namespace <> "" then
	output.tabLevel = 0
	output.autoTabLn "End Namespace"
end if

%>

<% 
	' Save the output file for this Table
	Dim filename
	filename = input.item("txtPath")

	Dim length
	Dim pos
	lenth = Len(filename)
	pos = InStrRev(filename, "\") 

	If Not pos = lenth Then
		filename = filename & "\"
	End If

	If prefix = True Then 
		filename = filename & "_" & GetObjectName(objTable) & "DAL.vb"
	Else 
		filename = filename & GetObjectName(objTable) & "DAL.vb"	
	End If

	output.save filename, false
	buffer = buffer & output.text
	output.clear
Next '  tableName
	
	output.write buffer

'===========================================================================
' These are support routines called by the above script
'===========================================================================
Function GetPublicName(objColumn)
	Dim name
	name = objColumn.Name
	GetPublicName = UCase(Left(name, 1)) & Right(name, Len(name) -1)
End Function

Function GetPrivateName(objColumn)
	Dim name 
	name = objColumn.Name
	GetPrivateName = "_" & LCase(Left(name, 1)) & Right(name, Len(name) -1)
End Function

Function GetDbName(objColumn)
	GetDbName = objColumn.Name
End Function

Function GetObjectName(objTable)
	Dim Name
	If Right(objTable.Alias, 1) = "s" Then
		Name = Left(objTable.Alias, Len(objTable.Alias)-1)
	Else
		Name = objTable.Alias
	End If
	GetObjectName = Name
End Function

Function TableHasIntegerPrimaryKey(inTable)
	Dim result
	If inTable.PrimaryKeys.Count = 1 Then
		Select Case LCase(inTable.PrimaryKeys(0).LanguageType)
			Case "integer"
				result = True
			Case "long"
				result = True
			Case Else
				result = False
		End Select
	Else
		result = False
	End If	
	
	TableHasIntegerPrimaryKey = result
End Function

Function TrimSpaces(str)

	Dim tname
	Dim name
	Dim char
	Dim l

	name = ""
	tname = str
	l = Len(tname)

	For j = 1 To l
		char = Mid(tname, j, 1)
		If Not char = " " Then
			name = name & char
		End If
	Next

	TrimSpaces = name
End Function

Function GetFullType(objColumn)

	Select Case LCase(objColumn.LanguageType)
		Case "string"
			GetFullType = "String"
		Case "integer"
			GetFullType = "Int32"
		Case "short"
			GetFullType = "Int16"
		Case "decimal"
			GetFullType = "Decimal"
		Case "datetime"
			GetFullType = "DateTime"
		Case Else
			GetFullType = objColumn.LanguageType
	End Select
	
End Function

Function GetDbType(objColumn)

	Select Case objColumn.DbTargetType
		Case "FbDbType.Integer"
			GetDbType = "DbType.Int32"
		Case "FbDbType.BigInt"
			GetDbType = "DbType.Int64"
		Case "FbDbType.SmallInt"
			GetDbType = "DbType.Int16"
		Case "FbDbType.Float"
			GetDbType = "DbType.Single"
		Case "FbDbType.Double"
			GetDbType = "DbType.Double"
		Case "FbDbType.Numeric"
			GetDbType = "DbType.Decimal"
		Case "FbDbType.Date"
			GetDbType = "DbType.Date"
		Case "FbDbType.Time"
			GetDbType = "DbType.Time"
		Case "FbDbType.TimeStamp"
			GetDbType = "DbType.DateTime"
		Case "FbDbType.Char"
			GetDbType = "DbType.String"
		Case "FbDbType.VarChar"
			GetDbType = "DbType.String"
		Case "FbDbType.Text"
			GetDbType = "DbType.String"
		Case "FbDbType.Binary"
			GetDbType = "DbType.Binary"
		Case Else
			GetDbType = objColumn.DbTargetType
	End Select
	
End Function

Function GetEmptyValue(objColumn)

	Select Case objColumn.DbTargetType
		Case "DbType.Int16", "DbType.Int32", "DbType.Int64", "DbType.Decimal", "DbType.Double", "DbType.Currency", "DbType.Single"
			GetEmptyValue = "0"
		Case "DbType.Guid"
			GetEmptyValue = "Guid.Empty"
		Case "DbType.StringFixedLength", "DbType.AnsiStringFixedLength", "DbType.String", "DbType.AnsiString"
			GetEmptyValue = "String.Empty"
		Case "DbType.DateTime"
			GetEmptyValue = "DateTime.MinValue"
		Case Else
			GetEmptyValue = Replace(objColumn.DbTargetType, "DbType.", "")
	End Select

End Function

Function GetParameterSize(objColumn)

	Select Case objColumn.DbTargetType
		Case "DbType.Int32"
			GetParameterSize = "4"
		Case "DbType.Binary"
			GetParameterSize = "8"
		Case "DbType.Guid"
			GetParameterSize = "16"
		Case "DbType.StringFixedLength", "DbType.AnsiStringFixedLength", "DbType.String", "DbType.AnsiString"
			GetParameterSize = objColumn.CharacterMaxLength
		Case Else
			GetParameterSize = "0"
	End Select

End Function

Function GetNullValueDefault(objColumn)

	Select Case objColumn.DbTargetType
		Case "DbType.Int16", "DbType.Int32", "DbType.Int64", "DbType.Decimal", "DbType.Double", "DbType.Currency", "DbType.Single"
			GetNullValueDefault = "0"
		Case "DbType.Guid"
			GetNullValueDefault = "Guid.Empty"
		Case "DbType.StringFixedLength", "DbType.AnsiStringFixedLength", "DbType.String", "DbType.AnsiString"
			GetNullValueDefault = "String.Empty"
		Case "DbType.DateTime"
			GetNullValueDefault = "DateTime.Parse(""01/01/1900"")"
		Case "DbType.Boolean"
			GetNullValueDefault = "False"
		Case "DbType.Binary", "DbType.Byte"
			GetNullValueDefault = "New Byte() {}"
		Case "DbType.Object"
			GetNullValueDefault = "Nothing"
		Case Else
			GetNullValueDefault = objColumn.DbTargetType	'"string.Empty"
	End Select

End Function
%>
##|BODY_END