Use of Custom Attributes to Serialise Objects to/from SQL Server Tables

• Download source files - 11.1 KB

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), --key
  [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

• 1^st November, 2005 - Changed the code to use a parameterised query rather than building the values in to prevent SQL injection type vulnerability