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.DeleteEmployees    Script 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.GetEmployees    Script 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.InsertEmployees    Script 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.UpdateEmployees    Script 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 QUOTED_IDENTIFIER 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 QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER 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 QUOTED_IDENTIFIER 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 QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER 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 QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Community | Languages and Tools

Thursday, September 29, 2005 9:44:02 PM (GMT Standard Time, UTC+00:00)
Comments [5]  |  Related posts:
Silverlight - not just pretty graphics - Cross platform .NET Framework!
Great guidance on using Team Foundation Server for your projects
ClickOnce Community Resource Kit has found a new home
Hands on WPF - dnrTV - Part 1
NOVA / DC Area Code Camp
.NET 3.0 Adoption and the current relative importance of its pieces