Example Data Class (VB.Net) - mpavey/sql-code-gen GitHub Wiki

Data.vb

Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports Sandbox.Utilities.Extensions
 
Namespace Sandbox
	Public Class Fields
		Public Function [Get](ByVal FieldID As Integer) As Model.Field
			'validate
			If FieldID < 1 Then
				Return New Model.Field()
			End If
 
			'lookup
			Dim x As List(Of Model.Field) = List(FieldID:=FieldID)
 
			'return
			If x.Count > 0 Then
				Return x.First()
			Else
				Return New Model.Field()
			End If
		End Function
 
		Public Function List(Optional ByVal FieldID As Int32 = 0) As List(Of Model.Field)
			'variables
			Dim DB As Database = DatabaseFactory.CreateDatabase()
			Dim Fields As New List(Of Model.Field)
 
			'command
			Using cmd As DbCommand = DB.GetStoredProcCommand("dbo.Fields_List")
				'parameters
				DB.AddInParameter(cmd, "@FieldID", DbType.Int32, FieldID)
 
				'execute query and get results
				Using DT As New DataTable()
					Using IDR As IDataReader = DB.ExecuteReader(cmd)
						If IDR IsNot Nothing Then
							DT.Load(IDR)
						End If
					End Using
 
					'convert to business object
					Fields = DT.ToList(Of Model.Field)()
				End Using
			End Using
 
			'return list
			Return Fields
		End Function
 
		Public Function Save(ByVal Field As Model.Field) As Integer
			'variables
			Dim DB As Database = DatabaseFactory.CreateDatabase()
			Dim ReturnValue As Integer = -1
 
			'command
			Using cmd As DbCommand = DB.GetStoredProcCommand("dbo.Fields_List")
				'parameters
				With Field
					DB.AddInParameter(cmd, "@FieldID", DbType.Int32, .FieldID)
				End With
 
				'return value parameter
				DB.AddParameter(cmd, "@ReturnValue", DbType.Int32, 4, ParameterDirection.ReturnValue, False, 0, 0, "@ReturnValue", DataRowVersion.Default, Nothing)
 
				'execute query
				DB.ExecuteNonQuery(cmd)
 
				'get return value
				ReturnValue = DB.GetParameterValue(cmd, "@ReturnValue")
			End Using
 
			'return value
			Return ReturnValue
		End Function
 
		Public Function Delete(ByVal FieldID As Integer) As Integer
			'variables
			Dim DB As Database = DatabaseFactory.CreateDatabase()
			Dim ReturnValue As Integer = -1
 
			'command
			Using cmd As DbCommand = DB.GetStoredProcCommand("dbo.Fields_Delete")
				'parameters
				DB.AddInParameter(cmd, "@FieldID", DbType.Int32, FieldID)
 
				'return value parameter
				DB.AddParameter(cmd, "@ReturnValue", DbType.Int32, 4, ParameterDirection.ReturnValue, False, 0, 0, "@ReturnValue", DataRowVersion.Default, Nothing)
 
				'execute query
				DB.ExecuteNonQuery(cmd)
 
				'get return value
				ReturnValue = DB.GetParameterValue(cmd, "@ReturnValue")
			End Using
 
			'return value
			Return ReturnValue
		End Function
	End Class
End Namespace