Generating a good stored procedure CRUD Layer with CodeSmith

If you are not already using CodeSmith to avoid repetitive coding tasks, you should really take a look at it. One of the things I use it for frequently is to generate a clean stored procedure layer on top of my tables for doing standard CRUD (SELECT, INSERT, UPDATE, DELETE) operations on those tables.

Specifically, what you usually need for most tables in your database are:
– A SELECT proc that returns all rows
– A SELECT proc that takes a primary key value and returns the corresponding row
– An INSERT proc that adds a row to the table
– A DELETE proc that removes a row
– An UPDATE proc that modifies a row

I actually prefer to just have a single SELECT proc that takes a primary key parameter that defaults to NULL. If that parameter is NULL, it returns all row, otherwise it returns just the one row requested. That saves on the number of adapters/commands you have to create to do SELECTs.

In combination with these procs, you will want to add a column to your tables if at all possble that can be used for optimistic concurrency checking. You can use a datetime column that gets updated with every modification to a row, a timestamp column, or a uniqueidentifier with the rowguid property set to get it to auto-update.

If you use this pattern or want to, I wrote a CodeSmith template that will code generate all the stored procs for you. You feed it a table name and the name of the column that is used for optimistic concurrency checking. It will then generate the appropriate stored procs to ensure everything gets updated correctly based on the optimistic checking column type. You can download it here.
http://www.softinsight.com/downloads/StoredProcsForConcurrencyColumnTables.zip
Also in that zip is another template that will let you generate all the procs for all the tables in your database. It will skip any tables that do not have the concurrency column name specified, or that do not have a primary key.

This pattern also happens to work beautifully with typed data sets and table adapters in VS 2005.

As an example, if you add a Modified datetime column to the Employees table in Northwind, and set its default value to the getdate() function, you now have a good column that can be used for optimistic concurrency checking, as long as you wrap it in stored procs that update the Modified column on updates. The template I wrote generates the following code with the click of a button:

/* Object:Stored Procedure dbo.DeleteEmployeesScript Date: Wednesday, September 28, 2005 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteEmployees]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[DeleteEmployees]
GO

/* Object:Stored Procedure dbo.GetEmployeesScript Date: Wednesday, September 28, 2005 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectEmployees]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[SelectEmployees]
GO

/* Object:Stored Procedure dbo.InsertEmployeesScript Date: Wednesday, September 28, 2005 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertEmployees]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[InsertEmployees]
GO

/* Object:Stored Procedure dbo.UpdateEmployeesScript Date: Wednesday, September 28, 2005 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateEmployees]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[UpdateEmployees]
GO

SET QUOTEDIDENTIFIER ON
GO
SET ANSI
NULLS OFF
GO
————————————————————————————————————————
— Date Created: Wednesday, September 28, 2005
— Created By:Generated by CodeSmith
————————————————————————————————————————

CREATE PROCEDURE dbo.DeleteEmployees
@EmployeeID int,
@Modified datetime
AS

DELETE FROM [dbo].[Employees]
WHERE

[EmployeeID] = @EmployeeID
AND [Modified] = @Modified
GO

SET QUOTEDIDENTIFIER OFF
GO
SET ANSI
NULLS ON
GO

SET QUOTEDIDENTIFIER OFF
GO
SET ANSI
NULLS OFF
GO
————————————————————————————————————————
— Date Created: Wednesday, September 28, 2005
— Created By:Generated by CodeSmith
————————————————————————————————————————

CREATE PROCEDURE dbo.SelectEmployees
@EmployeeID int = NULL
AS

IF (@EmployeeID IS NOT NULL)
BEGIN
SELECT
[EmployeeID],
[LastName],
[FirstName],
[Title],
[TitleOfCourtesy],
[BirthDate],
[HireDate],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[HomePhone],
[Extension],
[Photo],
[Notes],
[ReportsTo],
[PhotoPath],
[Modified]
FROM
[dbo].[Employees]
WHERE
[EmployeeID] = @EmployeeID
END
ELSE
BEGIN
SELECT
[EmployeeID],
[LastName],
[FirstName],
[Title],
[TitleOfCourtesy],
[BirthDate],
[HireDate],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[HomePhone],
[Extension],
[Photo],
[Notes],
[ReportsTo],
[PhotoPath],
[Modified]
FROM
[dbo].[Employees]
END

GO

SET QUOTEDIDENTIFIER OFF
GO
SET ANSI
NULLS ON
GO

————————————————————————————————————————
— Date Created: Wednesday, September 28, 2005
— Created By:Generated by CodeSmith
————————————————————————————————————————

CREATE PROCEDURE dbo.InsertEmployees
@LastName nvarchar(20),
@FirstName nvarchar(10),
@Title nvarchar(30),
@TitleOfCourtesy nvarchar(25),
@BirthDate datetime,
@HireDate datetime,
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@HomePhone nvarchar(24),
@Extension nvarchar(4),
@Photo image,
@Notes ntext,
@ReportsTo int,
@PhotoPath nvarchar(255),
@Modified datetime OUTPUT,
@EmployeeID int OUTPUT
AS

SET @Modified=getdate()
INSERT INTO [dbo].[Employees] (
[LastName],
[FirstName],
[Title],
[TitleOfCourtesy],
[BirthDate],
[HireDate],
[Address],
[City],
[Region],
[PostalCode],
[Country],
[HomePhone],
[Extension],
[Photo],
[Notes],
[ReportsTo],
[PhotoPath],
[Modified]
) VALUES (
@LastName,
@FirstName,
@Title,
@TitleOfCourtesy,
@BirthDate,
@HireDate,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@HomePhone,
@Extension,
@Photo,
@Notes,
@ReportsTo,
@PhotoPath,
@Modified
)
SET @EmployeeID = @@IDENTITY



GO

SET QUOTEDIDENTIFIER OFF
GO
SET ANSI
NULLS ON
GO

SET QUOTEDIDENTIFIER ON
GO
SET ANSI
NULLS OFF
GO
————————————————————————————————————————
— Date Created: Wednesday, September 28, 2005
— Created By:Generated by CodeSmith
————————————————————————————————————————

CREATE PROCEDURE dbo.UpdateEmployees
@EmployeeID int,
@LastName nvarchar(20),
@FirstName nvarchar(10),
@Title nvarchar(30),
@TitleOfCourtesy nvarchar(25),
@BirthDate datetime,
@HireDate datetime,
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@HomePhone nvarchar(24),
@Extension nvarchar(4),
@Photo image,
@Notes ntext,
@ReportsTo int,
@PhotoPath nvarchar(255),
@Modified datetime OUTPUT
AS
DECLARE @CurrentModified DateTime
SET @CurrentModified = getdate()
UPDATE [dbo].[Employees] SET
[LastName] = @LastName,[FirstName] = @FirstName,[Title] = @Title,[TitleOfCourtesy] = @TitleOfCourtesy,[BirthDate] = @BirthDate,[HireDate] = @HireDate,[Address] = @Address,[City] = @City,[Region] = @Region,[PostalCode] = @PostalCode,[Country] = @Country,[HomePhone] = @HomePhone,[Extension] = @Extension,[Photo] = @Photo,[Notes] = @Notes,[ReportsTo] = @ReportsTo,[PhotoPath] = @PhotoPath,[Modified] = @CurrentModified
WHERE
[EmployeeID] = @EmployeeID
AND [Modified] = @Modified

SET @Modified = @CurrentModified
GO

SET QUOTEDIDENTIFIER OFF
GO
SET ANSI
NULLS ON
GO