Use of custom attributes to serialise objects to/from SQL Server tables

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.

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.

Suppose we have a table [Currency] defined thus:

CREATE TABLE Currency
(
  [Currency Code] char(3), --key
  [Currency Name] varchar(200)
)

Then we could create a .NET class that saves and reads from this table thus:

   %26lt;DatabaseTableAttribute("CURRENCY")%26gt; _
   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

   %26lt;DatabaseFieldAttribute("Currency Code",True)%26gt; _
        Public ReadOnly Property CurrencyCode() As String
            Get
                Return _Code
            End Get
        End Property

   %26lt;DatabaseFieldAttribute("Currency Name",False)%26gt;  _
        Public Property CurrencyName() As String
            Get
                Return _Name
            End Get
            Set(ByVal Value As String)
                If Value %26lt;%26gt; _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.

• 01 Nov 2005 - Changed the code to use a parameterised query rather than building the values in to prevent SQL injection type vulnerability.