Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

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

0.00/5 (No votes)
1 Nov 2005 1  
Shows how you can use custom attributes to save .NET classes directly to an SQL Server table

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

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here