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:
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
Create Procedure [dbo].[SafetyIncident_Delete]
@SafetyIncidentId int
AS
Begin
SET NOCOUNT ON
delete from SafetyIncident
where
SafetyIncidentId = @SafetyIncidentId
End
Create Procedure [dbo].[SafetyIncident_ReadAll]
AS
Begin
SET NOCOUNT ON
select
SafetyIncidentId, WeekId, Description
from SafetyIncident
End
Create Procedure [dbo].[SafetyIncident_ReadById]
@SafetyIncidentId int
AS
Begin
SET NOCOUNT ON
select
SafetyIncidentId, WeekId, Description
from SafetyIncident
where
SafetyIncidentId = @SafetyIncidentId
End
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