| Author: |
admiller97 |
| Created: |
8/27/2008 7:45:16 AM |
| Last Updated: |
8/27/2008 7:52:32 AM |
Template uses Microsoft DAAB 4.0, using inline, parametrized SQL so NO stored procedures are necessary. You'll need a MethodResult class similar to below.
'''
Public Class MethodResult
#Region "Members"
Private _messages As List(Of String) = New List(Of String)
#End Region
#Region "Properties"
Public ReadOnly Property Success() As Boolean
Get
If _messages.Count > 0 Then
Return False
Else
Return True
End If
End Get
End Property
Public ReadOnly Property Messages() As List(Of String)
Get
Return _messages
End Get
End Property
#End Region
#Region "Shared Methods"
Public Shared Operator &(ByVal inMethodResult1 As MethodResult, ByVal inMethodResult2 As MethodResult) As MethodResult
Dim oResult As New MethodResult()
oResult.Messages.AddRange(inMethodResult1.Messages)
oResult.Messages.AddRange(inMethodResult2.Messages)
Return oResult
End Operator
#End Region
End Class Sample output: Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Partial Public Class Product
#Region "Column Names"
Public Const cProductId As String = "ProductId"
Public Const cTitle As String = "Title"
Public Const cImageUrl As String = "ImageUrl"
Public Const cCreateDate As String = "CreateDate"
Public Const cNextRefreshDate As String = "NextRefreshDate"
Public Const cProductUrl As String = "ProductUrl"
Public Const cStatus As String = "Status"
Public Const cProductType As String = "ProductType"
Public Const cProductIdentifier As String = "ProductIdentifier"
Public Const cProcessor As String = "Processor"
Public Const cFailCount As String = "FailCount"
Public Const cCurrentLowPrice As String = "CurrentLowPrice"
Public Const cLowPriceChangeDate As String = "LowPriceChangeDate"
#End Region
#Region "Private Members"
Private _productId As Integer
Private _title As String
Private _imageUrl As String
Private _createDate As DateTime
Private _nextRefreshDate As DateTime
Private _productUrl As String
Private _status As Integer
Private _productType As Integer
Private _productIdentifier As String
Private _processor As String
Private _failCount As Integer
Private _currentLowPrice As Decimal
Private _lowPriceChangeDate As DateTime
#End Region
#Region "Public Properties "
Public Property ProductId As Integer
Get
Return _productId
End Get
Set(ByVal Value As Integer)
_productId = Value
End Set
End Property
Public Property Title As String
Get
Return _title
End Get
Set(ByVal Value As String)
_title = Value
End Set
End Property
Public Property ImageUrl As String
Get
Return _imageUrl
End Get
Set(ByVal Value As String)
_imageUrl = Value
End Set
End Property
Public ReadOnly Property CreateDate As DateTime
Get
Return _createDate
End Get
End Property
Public Property NextRefreshDate As DateTime
Get
Return _nextRefreshDate
End Get
Set(ByVal Value As DateTime)
_nextRefreshDate = Value
End Set
End Property
Public Property ProductUrl As String
Get
Return _productUrl
End Get
Set(ByVal Value As String)
_productUrl = Value
End Set
End Property
Public Property Status As Integer
Get
Return _status
End Get
Set(ByVal Value As Integer)
_status = Value
End Set
End Property
Public Property ProductType As Integer
Get
Return _productType
End Get
Set(ByVal Value As Integer)
_productType = Value
End Set
End Property
Public Property ProductIdentifier As String
Get
Return _productIdentifier
End Get
Set(ByVal Value As String)
_productIdentifier = Value
End Set
End Property
Public Property Processor As String
Get
Return _processor
End Get
Set(ByVal Value As String)
_processor = Value
End Set
End Property
Public Property FailCount As Integer
Get
Return _failCount
End Get
Set(ByVal Value As Integer)
_failCount = Value
End Set
End Property
Public Property CurrentLowPrice As Decimal
Get
Return _currentLowPrice
End Get
Set(ByVal Value As Decimal)
_currentLowPrice = Value
End Set
End Property
Public Property LowPriceChangeDate As DateTime
Get
Return _lowPriceChangeDate
End Get
Set(ByVal Value As DateTime)
_lowPriceChangeDate = Value
End Set
End Property
#End Region
#Region "Public Methods"
Public Shared Function GetByKey(ByVal inProductId As Integer) As Product
Dim oResultList As List(Of Product)
Dim db As Database = DatabaseFactory.CreateDatabase()
Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Select * From Products Where ProductId = @ProductId ")
db.AddInParameter(dbCmd, "ProductId", DbType.Int32, inProductId)
Using rdr As IDataReader = db.ExecuteReader(dbCmd)
oResultList = LoadFromReader(rdr)
End Using
End Using
If oResultList.Count = 0 Then
Return Nothing
Else
Return oResultList.Item(0)
End If
End Function
Public Shared Function GetAll() As List(Of Product)
Dim oResult As New List(Of Product)
Dim db As Database = DatabaseFactory.CreateDatabase()
Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Select * From Products")
Using rdr As IDataReader = db.ExecuteReader(dbCmd)
oResult = LoadFromReader(rdr)
End Using
End Using
Return oResult
End Function
#End Region
#Region "Protected Methods"
Protected Function Insert() As MethodResult
Dim oResult As New MethodResult()
Dim db As Database = DatabaseFactory.CreateDatabase()
Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Insert Into Products (Title, ImageUrl, CreateDate, NextRefreshDate, ProductUrl, Status, ProductType, ProductIdentifier, Processor, FailCount, CurrentLowPrice, LowPriceChangeDate) Values (@Title, @ImageUrl, @CreateDate, @NextRefreshDate, @ProductUrl, @Status, @ProductType, @ProductIdentifier, @Processor, @FailCount, @CurrentLowPrice, @LowPriceChangeDate) Select SCOPE_IDENTITY()")
db.AddInParameter(dbCmd, "Title", DbType.String, _title)
db.AddInParameter(dbCmd, "ImageUrl", DbType.String, _imageUrl)
db.AddInParameter(dbCmd, "CreateDate", DbType.DateTime, DateTime.Now())
db.AddInParameter(dbCmd, "NextRefreshDate", DbType.DateTime, _nextRefreshDate)
db.AddInParameter(dbCmd, "ProductUrl", DbType.String, _productUrl)
db.AddInParameter(dbCmd, "Status", DbType.Int16, _status)
db.AddInParameter(dbCmd, "ProductType", DbType.Int32, _productType)
db.AddInParameter(dbCmd, "ProductIdentifier", DbType.String, _productIdentifier)
db.AddInParameter(dbCmd, "Processor", DbType.String, _processor)
db.AddInParameter(dbCmd, "FailCount", DbType.Int16, _failCount)
db.AddInParameter(dbCmd, "CurrentLowPrice", DbType.Decimal, _currentLowPrice)
db.AddInParameter(dbCmd, "LowPriceChangeDate", DbType.DateTime, _lowPriceChangeDate)
ProductId = CInt(db.ExecuteScalar(dbCmd))
If _ProductId.ToString() = "" Then
oResult.Messages.Add("Insert Failed")
End If
End Using
Return oResult
End Function
Protected Function Update() As MethodResult
Dim oResult As New MethodResult()
Dim db As Database = DatabaseFactory.CreateDatabase()
Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Update Products Set Title = @Title, ImageUrl = @ImageUrl, NextRefreshDate = @NextRefreshDate, ProductUrl = @ProductUrl, Status = @Status, ProductType = @ProductType, ProductIdentifier = @ProductIdentifier, Processor = @Processor, FailCount = @FailCount, CurrentLowPrice = @CurrentLowPrice, LowPriceChangeDate = @LowPriceChangeDate Where ProductId = @ProductId")
db.AddInParameter(dbCmd, "ProductId", DbType.Int32, _productId)
db.AddInParameter(dbCmd, "Title", DbType.String, _title)
db.AddInParameter(dbCmd, "ImageUrl", DbType.String, _imageUrl)
db.AddInParameter(dbCmd, "NextRefreshDate", DbType.DateTime, _nextRefreshDate)
db.AddInParameter(dbCmd, "ProductUrl", DbType.String, _productUrl)
db.AddInParameter(dbCmd, "Status", DbType.Int16, _status)
db.AddInParameter(dbCmd, "ProductType", DbType.Int32, _productType)
db.AddInParameter(dbCmd, "ProductIdentifier", DbType.String, _productIdentifier)
db.AddInParameter(dbCmd, "Processor", DbType.String, _processor)
db.AddInParameter(dbCmd, "FailCount", DbType.Int16, _failCount)
db.AddInParameter(dbCmd, "CurrentLowPrice", DbType.Decimal, _currentLowPrice)
db.AddInParameter(dbCmd, "LowPriceChangeDate", DbType.DateTime, _lowPriceChangeDate)
If CInt(db.ExecuteNonQuery(dbCmd)) <> 1 Then
oResult.Messages.Add("Update Failed")
End If
End Using
Return oResult
End Function
Protected Function Delete() As MethodResult
Dim oResult As New MethodResult()
Dim db As Database = DatabaseFactory.CreateDatabase()
Using dbCmd As Common.DbCommand = db.GetSqlStringCommand("Delete From Products Where ProductId = @ProductId")
db.AddInParameter(dbCmd, "ProductId", DbType.Int32, _productId)
If CInt(db.ExecuteScalar(dbCmd)) <> 1 Then
oResult.Messages.Add("Delete Failed")
End If
End Using
Return oResult
End Function
Protected Shared Function LoadFromReader(ByRef inDataReader As IDataReader) As List(Of Product)
Dim oResult As New List(Of Product)
If (Not inDataReader Is Nothing) And (Not inDataReader.IsClosed) Then
While inDataReader.Read()
Dim oProduct As New Product()
If Not IsDBNull(inDataReader("ProductId")) Then
oProduct._productId = CType(inDataReader("ProductId"), Int32)
End If
If Not IsDBNull(inDataReader("Title")) Then
oProduct._title = CType(inDataReader("Title"), String)
End If
If Not IsDBNull(inDataReader("ImageUrl")) Then
oProduct._imageUrl = CType(inDataReader("ImageUrl"), String)
End If
If Not IsDBNull(inDataReader("CreateDate")) Then
oProduct._createDate = CType(inDataReader("CreateDate"), DateTime)
End If
If Not IsDBNull(inDataReader("NextRefreshDate")) Then
oProduct._nextRefreshDate = CType(inDataReader("NextRefreshDate"), DateTime)
End If
If Not IsDBNull(inDataReader("ProductUrl")) Then
oProduct._productUrl = CType(inDataReader("ProductUrl"), String)
End If
If Not IsDBNull(inDataReader("Status")) Then
oProduct._status = CType(inDataReader("Status"), Int32)
End If
If Not IsDBNull(inDataReader("ProductType")) Then
oProduct._productType = CType(inDataReader("ProductType"), Int32)
End If
If Not IsDBNull(inDataReader("ProductIdentifier")) Then
oProduct._productIdentifier = CType(inDataReader("ProductIdentifier"), String)
End If
If Not IsDBNull(inDataReader("Processor")) Then
oProduct._processor = CType(inDataReader("Processor"), String)
End If
If Not IsDBNull(inDataReader("FailCount")) Then
oProduct._failCount = CType(inDataReader("FailCount"), Int32)
End If
If Not IsDBNull(inDataReader("CurrentLowPrice")) Then
oProduct._currentLowPrice = CType(inDataReader("CurrentLowPrice"), Decimal)
End If
If Not IsDBNull(inDataReader("LowPriceChangeDate")) Then
oProduct._lowPriceChangeDate = CType(inDataReader("LowPriceChangeDate"), DateTime)
End If
oResult.Add(oProduct)
End While
End If
Return oResult
End Function
#End Region
End Class
|