Introduction
This class library is a quick implementation to use custom attributes to marshal a .NET class to/from an SQL Server database table. It is fairly simple in that it assumes a one-to-one mapping between the class and the table.
Classes Involved
DatabaseTableAttribute
This class inherits Attribute
and is used to identify the table that the object gets read/written to.
DatabaseFieldAttribute
This class inherits Attribute
and identifies the field that the property gets read/written to.
SQLTableInterop
This class handles the interop between the object and the table.
SQLUtilities
Class that contains some utility functions for SQL Server.
Usage Example
Suppose we have a table [Currency
] defined thus:
CREATE TABLE Currency
(
[Currency Code] char(3),
[Currency Name] varchar(200)
)
Then we could create a .NET class that saves and reads from this table thus:
<DatabaseTableAttribute("CURRENCY")> _
Public Class Staff
Private _Code As String
Private _Name As String
Public Sub New(Byval Code As String)
_Code = Code
Dim SQLTableInterop As New SQLTableInterop(ConnectionString)
SQLTableInterop.GetObjectDataFromTable(Me)
End Sub
<DatabaseFieldAttribute("Currency Code",True)> _
Public ReadOnly Property CurrencyCode() As String
Get
Return _Code
End Get
End Property
<DatabaseFieldAttribute("Currency Name",False)> _
Public Property CurrencyName() As String
Get
Return _Name
End Get
Set(ByVal Value As String)
If Value <> _Name Then
_Name = Value
End If
End Set
End Property
Public Sub Save()
Dim SQLTableInterop As New SQLTableInterop(ConnectionString)
SQLTableInterop.SetObjectDataToTable(Me)
End Sub
Obviously, the usefulness of this utility DLL increases for larger tables.
History
- 1st November, 2005 - Changed the code to use a parameterised query rather than building the values in to prevent SQL injection type vulnerability