用存储过程生成实体类

发表于   |   分类于

其实网上已经有有很多实体类生成工具的存在,但多了解点知识总是没错的.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'procGenerateEntityClass') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE procGenerateEntityClass
GO
/*======================================================================
Business Entity Class Generator
This SP accepts a DATABASE object (table, view) name's parameter AND
generates (C# code) custom entity class based ON the object's fields
======================================================================*/
CREATE PROCEDURE procGenerateEntityClass
@ObjectName varchar(100)
AS
DECLARE @name varchar(20),
  @type varchar(20)
DECLARE objCursor CURSOR
FOR
SELECT sc.name, st.name type FROM syscolumns sc
INNER JOIN systypes st
ON st.xusertype = sc.xusertype
WHERE Id=OBJECT_ID(@ObjectName)
DECLARE @propertyCodes varchar(8000)
SET @propertyCodes = ''
OPEN objCursor
FETCH NEXT FROM objCursor
INTO @name, @type
DECLARE @cType varchar(20)-- C# type
IF @@FETCH_STATUS <> 0
    BEGIN
  CLOSE objCursor
  DEALLOCATE objCursor
  PRINT 'Error... Please CHECK passed parameter'
  RETURN
 END
WHILE @@FETCH_STATUS = 0
    BEGIN
  -- 将SQL Server数据库数据类型转换为C#数据类型
  SET @cType =
  CASE
  WHEN @type LIKE '%char%' OR @type LIKE '%text%'
   THEN 'string'
  WHEN @type IN ('decimal', 'numeric')
   THEN 'decimal'
  WHEN @type = 'real'
   THEN 'float'
  WHEN @type LIKE '%money%'
   THEN 'decimal'
  WHEN @type = 'bit'
   THEN 'bool'
  WHEN @type IN ('bigint', 'timestamp')  
   THEN 'long'
  WHEN @type LIKE '%int%'
   THEN 'int'
  WHEN @type = 'datetime'
   THEN 'DateTime' 
   WHEN @type = 'uniqueidentifier'
   THEN 'Guid'  
  ELSE
   @type
  END
  SET @propertyCodes = @propertyCodes + CHAR(9) + 'public ' + @ctype + ' ' + @name + '{ get; set; }' + CHAR(13) + CHAR(13)
  FETCH NEXT FROM objCursor
  INTO @name, @type
 END
--PRINT '[Serializable]'
PRINT 'public class ' + @ObjectName + 'Info'
PRINT '{'
PRINT ''
PRINT CHAR(9) + 'public ' + @ObjectName + 'Info()'
PRINT CHAR(9) + '{'
PRINT CHAR(9) + '}'
PRINT ''
PRINT @propertyCodes
PRINT '}'
CLOSE objCursor
DEALLOCATE objCursor

使用方法:

1
EXEC procGenerateEntityClass 'tabname'

参考地址: Jack Tang的博客