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