CRUD Generator - (Stored Procedure Generation)

• Download source code - 16.4 KB

Introduction

I couldn't find any simple CRUD generation tools, so I wrote this one quickly. This is partly based on a SQL script that someone found, but modified from there.

Note: While you may find this handy, make sure you check out my new article, Generate Classes and CRUD Procedures, which makes this one obsolete.

Background

Important note: This requires that the user has Windows Authentication access to the database, can create Stored Procedures, and read from the system tables. I'm sure you can modify it to use a username/password, if desired.

Using the code

One part that should change is the author auto-population. Currently, this is working on the premise that your user name is in this format: DOMAIN\joe.doe.

The SQL code I used to get columns looks like this. If anyone knows a better way to get the data-type, let me know. That's the part I took from someone else's code:

select 
  o.name as TableName, 

  c.name as ColumnName,

  COLUMNPROPERTY(o.id,c.name,'IsIdentity' ) isIdentity,

  case when p.Column_Name is not null then 1 else 0 end as IsPrimaryKey,

  c.colorder as ColumnOrder,

  CASE WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN 
      ( CAST(t.name AS [varchar]) + ' (' + CAST(c.length AS [varchar]) + ')' )
  WHEN t.name IN ('numeric', 'decimal') THEN 
      ( CAST(t.name AS [varchar]) + ' (' + CAST(c.xprec AS [varchar]) + ',' 
    + CAST(c.xscale AS [varchar]) + ')' )    
  ELSE t.name END AS DataType 

from 
  syscolumns c 
  inner join sysobjects o on c.id=o.id
  INNER JOIN systypes t ON c.xtype = t.xtype  
  left join (
      SELECT c.Table_Name, k.Column_Name
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
      inner JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.table_name = c.table_name 
    AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog 
    AND k.constraint_catalog = c.constraint_catalog AND 
        k.constraint_name = c.constraint_name
      where constraint_type = 'PRIMARY KEY'
  ) p on o.Name = p.Table_Name and c.Name = p.Column_Name
where o.xtype='U' and o.name <> 'dtproperties'
order by o.name,c.colorder

Here's the format of the Stored Procedures that I'm having it produce:

-- =============================================

-- Author:        FirstName LastName 

-- Create date:   10/11/2007

-- Description:   

-- Revisions:     

-- =============================================

Create Procedure [dbo].[SafetyIncident_Create]
      @WeekId int,
      @Description varchar (2047),
      @SafetyIncidentId int OUTPUT
AS
Begin
      SET NOCOUNT ON
      insert into SafetyIncident
            ( WeekId, Description)
      values
            (@WeekId,@Description)
 
      select @SafetyIncidentId = SCOPE_IDENTITY()
End
 
-- =============================================

-- Author:        FirstName LastName 

-- Create date:   10/11/2007

-- Description:   

-- Revisions:     

-- =============================================

Create Procedure [dbo].[SafetyIncident_Delete]
      @SafetyIncidentId int
AS
Begin
      SET NOCOUNT ON
      delete from SafetyIncident
      where
            SafetyIncidentId = @SafetyIncidentId
End
 
-- =============================================

-- Author:        FirstName LastName 

-- Create date:   10/11/2007

-- Description:   

-- Revisions:     

-- =============================================

Create Procedure [dbo].[SafetyIncident_ReadAll]
 
AS
Begin
      SET NOCOUNT ON
      select
       SafetyIncidentId, WeekId, Description
      from SafetyIncident
End
 
-- =============================================

-- Author:        FirstName LastName 

-- Create date:   10/11/2007

-- Description:   

-- Revisions:     

-- =============================================

Create Procedure [dbo].[SafetyIncident_ReadById]
      @SafetyIncidentId int
AS
Begin
      SET NOCOUNT ON
      select
       SafetyIncidentId, WeekId, Description
      from SafetyIncident
      where
            SafetyIncidentId = @SafetyIncidentId
End
 
-- =============================================

-- Author:        FirstName LastName 

-- Create date:   10/11/2007

-- Description:   

-- Revisions:     

-- =============================================

Create Procedure [dbo].[SafetyIncident_Update]
      @SafetyIncidentId int,
      @WeekId int,
      @Description varchar (2047)
AS
Begin
      SET NOCOUNT ON
      update SafetyIncident
      set
            WeekId = @WeekId,
            Description = @Description
      where
            SafetyIncidentId = @SafetyIncidentId
End

--DEACTIVATE Function added. Similar to the above