SqlDatabaseUtilities - a data layer helper for SQL server

Basically this project is a set of classes that make it possible to read and write your .NET classes (and collections of same) to SQL Server tables.

For example suppose you have a table such as:

CREATE TABLE Address
(
Id uniqueidentifier NOT NULL,
[Line 1] varchar(255) NULL,
[Line 2] varchar(255) NULL,
[Line 3] varchar(255) NULL,
[Post Code] varchar(10) NULL,
[User Name] varchar(220) NULL,
Timestamp datetime NOT NULL
)


And you have an unique index on Id.

You can create a .NET class to mirror this table:-

<DatabaseTable("Address")> _
Public Class Address

Private _Id As Guid
Private _Line_1 As String = ""

#Region "Id"
<DatabaseField("Id", True)> _
Public Property Id() As Guid
Get
Return _Id
End Get
Set(ByVal value As Guid)
_Id = value
End Set
End Property
#End Region

#Region "Line 1"
<DatabaseField("Line 1")> _
Public Property Line1() As String
Get
Return _Line_1
End Get
Set(ByVal Value As String)
If Value <> _Line_1 Then
_Line_1 = Value
End If
End Set
End Property
#End Region

'--8<--------------------------------etc for other fields...

End Class


Because this class has been marked with attributes (DatabaseTable() and DatabaseField() ) there is now sufficient information to read it and write it to the database table "Address".

For example to load the class with data from the table:-

Public Function GetHomeAddress(Byval Id As Guid) As Address
Dim HomeAddress As New Address

HomeAddress.Id = Id
Dim SQLTableInterop As New SQLTableInterop(_connectionstring)
SQLTableInterop.GetObjectDataFromTable(HomeAddress)

Return HomeAddress

End Function


and when you have changed the class you can save it back to the table thus:-

Dim SQLTableInterop As New SQLTableInterop(_connectionstring)
SQLTableInterop.SetObjectDataToTable(HomeAddress)


This becomes more useful the more tables and classes your application involves...

New version (attached) now does an INSERT to the table if the key fields (as held in the object) are not already on the table when the SetObjectdataToTable method is called

New version for visual studio 2005 - now allows nullable types and serialises them as dbNull if no value is set.