用存储过程生成实体类

下面是一个根据网上资料修改, 用来的生成实体类的SQL Server存储过程代码:

 

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
Sample Usage:
USE Northwind
EXEC procGenerateEntityClass 'Shippers'
Author:Leon C. Tayson

Modified By: Jack Tang
Modify Date: 2008-12-4
======================================================================*/
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 'double'
  WHEN @type = 'real'
   THEN 'float'
  WHEN @type LIKE '%money%'
   THEN 'decimal'
  WHEN @type = 'bit'
   THEN 'bool'
  WHEN @type = 'bigint'
   THEN 'long'
  WHEN @type LIKE '%int%'
   THEN 'int'
  WHEN @type = 'datetime'
   THEN 'DateTime'  
  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

 

参考资料地址: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1039&lngWId=5