CRUD 生成器 - (存储过程生成)

• 下载源代码 - 16.4 KB

引言

我找不到任何简单的 CRUD 生成工具，所以我就快速编写了这个。这部分基于某人找到的一个 SQL 脚本，但对其进行了修改。

注意：虽然你可能会觉得这个很有用，但请务必查看我的新文章，生成类和 CRUD 过程，它使这个版本过时了。

背景

重要提示：这要求用户具有对数据库的 Windows 身份验证访问权限，能够创建存储过程，并从系统表中读取数据。如果你需要，可以修改它以使用用户名/密码。

使用代码

应该更改的一个部分是作者的自动填充。目前，它基于你的用户名格式为：DOMAIN\joe.doe。

我用来获取列的 SQL 代码如下所示。如果有人知道更好的获取数据类型的方法，请告诉我。这是我从别人的代码中获取的部分。

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

这是我生成的存储过程的格式

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

-- 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