Click here to Skip to main content
14,302,209 members

CRUD Generator - (Stored Procedure Generation)

Rate this:
2.59 (9 votes)
Please Sign up or sign in to vote.
2.59 (9 votes)
12 Oct 2007CPOL
Simple code that can be updated. This generates CRUD procedures.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ColinBashBash
Software Developer
United States United States
likes boardgames, computer games, and enjoys his .net programming job.

Comments and Discussions

 
QuestionBuen articulo Pin
Cesitar Ps12-Dec-18 15:53
memberCesitar Ps12-Dec-18 15:53 
GeneralSQL 2008 Mods required Pin
Ray Causey5-May-09 6:49
memberRay Causey5-May-09 6:49 
GeneralRe: SQL 2008 Mods required Pin
ColinBashBash5-May-09 7:41
memberColinBashBash5-May-09 7:41 
GeneralNice tool - does exactly what it says it does Pin
DalePres6-Mar-08 3:58
memberDalePres6-Mar-08 3:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 12 Oct 2007

Stats

39.1K views
1K downloads
22 bookmarked