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.

Advertisements

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

  1. […] a light-weight Data Access Layer in C# (2) The first part of this series dealt with the queries to retrieve the necessary information on tables and columns […]

  2. […] Creating a light-weight Data Access Layer in C# (3) Part 1: Retrieve information on tables and columns […]

  3. […] Creating a light-weight Data Access Layer in C# (5) Part 1: Retrieve information on tables and columns […]

  4. seesharpgears says:

    Hello, I can see now a nice approach you have in creating DAL. I especially find it innovative the way that you create the C# classes through stored procedures. Similarly, I have written a framework for fast data retrieval and immediate binding sql results to C# objects. Basically my classes are generic and can take anything that has default constructor and in order to bind the table columns with class attributes you only have to give them same names. Please check it out here http://seesharpgears.blogspot.com/ and tell me what do you think about my approach, its usability etc.

  5. […] Creating a light-weight Data Access Layer in C# (6) Part 1: Retrieve information on tables and columns […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: