Creating a light-weight Data Access Layer in C# (1)

I wrote about generating Stored Procedure wrappers a couple of months ago, and experimented with Subsonic which is a powerful framework for several databases.

However Subsonic requires you to have a special Subsonic section in your app/web.config file. So I thought: what is necessary to create a lightweight data access layer if I don’t need the full frameworks.

First, get the list of tables and columns you need in your DAL.

In SQL Server 2000, this is done with the two queries:

SELECT sysobjects.id, sysobjects.name
FROM sysobjects
WHERE    xtype='U' AND name<>'dtproperties'
AND    name in ('list', 'of', 'table', 'names')
ORDER BY name

SELECT dbo.syscolumns.name AS ID, dbo.systypes.name AS DataType,
    CASE WHEN systypes.status = 0 THEN NULL
         WHEN dbo.systypes.name = 'nvarchar' THEN dbo.syscolumns.length / 2
         ELSE dbo.syscolumns.length END AS Length,
    syscolumns.isnullable AS IsNullable,
    COLUMNPROPERTY (syscolumns.id, syscolumns.name, 'IsIdentity') as IsIdentity,
    COLUMNPROPERTY (syscolumns.id, syscolumns.name, 'IsComputed') as IsComputed
FROM dbo.syscolumns
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE     syscolumns.id = @OID AND systypes.name <> 'sysname'
ORDER BY syscolumns.colid

The queries can be run in SQL 2005 too, but if you don’t need to support 2000, use the new sys.* schema:

SELECT sys.objects.object_id, sys.objects.name
FROM sys.objects
WHERE type='U' AND name<>'dtproperties'
AND name in ('list', 'of', 'table', 'names')
ORDER BY name

SELECT sys.columns.name AS ID, sys.types.name AS DataType,
    CASE WHEN sys.types.name = 'nvarchar' THEN sys.columns.max_length / 2
         ELSE sys.columns.max_length END AS Length,
    sys.columns.is_nullable AS IsNullable,
    sys.columns.is_identity as IsIdentity,
    sys.columns.is_computed as IsComputed
FROM sys.columns
INNER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id
WHERE sys.columns.object_id = @OID AND sys.types.name <> 'sysname'
ORDER BY sys.columns.column_id

The parameter @OID in the column query denotes the table’s id column.