C# SQL Server生成C#类

步骤一：创建函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[f_split](@s VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @str VARCHAR(1000),@split VARCHAR(100)
SELECT @s=@s+'_',@str=''
WHILE CHARINDEX('_',@s)>0
BEGIN
SET @split=left(@s,CHARINDEX('_',@s))
SET @str=@str+UPPER(left(@split,1))+right(@split,LEN(@split)-1)
SET @s=STUFF(@s,1,CHARINDEX('_',@s),'')
END
RETURN REPLACE(LEFT(@str,LEN(@str)-1),'_','')
END

　　步骤二：创建存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[sp_csharp](@ VARCHAR(100))
AS
DECLARE @i INT,@j INT, @a VARCHAR(20), @s VARCHAR(8000),@g VARCHAR(30)
DECLARE @t TABLE(id INT,[name] VARCHAR(100),[type] VARCHAR(20),[value] VARCHAR(200))
SET @i=1
SET @a='/// </summary>'
SET @g=' { get; set; }'
SET @s=@a+CHAR(10)+'/// '
INSERT INTO @t
SELECT id=ROW_NUMBER() OVER(ORDER BY c.[name]), 
CAST(c.[name] AS VARCHAR(100)) AS [name] ,
CAST(t.[name] AS VARCHAR(30)) AS [type] , 
CAST(ISNULL(e.[value],'') AS VARCHAR(200)) AS [value]--字段说明
FROM SYS.COLUMNS  c 
LEFT JOIN  SYS.TYPES t
ON c.user_type_id=t.user_type_id
LEFT JOIN SYS.EXTENDED_PROPERTIES e 
ON e.class=1 AND c.[object_id]=e.major_id AND c.column_id=e.minor_id 
WHERE c.[object_id]= OBJECT_ID(''+@+'')
SELECT @j=MAX(id) FROM @t
WHILE @i<=@j
BEGIN 
  SELECT @s=@s + [value] FROM @t WHERE id=@i
    SET @s=@s+CHAR(10)+@a+CHAR(10)
  SELECT @s=@s+'public'+CHAR(32)+CASE [type] WHEN 'datetime' THEN 'DateTime'
                         WHEN 'varchar' THEN 'String'
                         WHEN 'int' THEN 'Int32'
                    WHEN 'bit' THEN 'Boolean'
                         ELSE 'String' END--常用的几个数据类型，根据需要可扩充
       +CHAR(32)    FROM @t WHERE id=@i
   SELECT @s=@s+ dbo.F_split([name]) --调用函数
          FROM @t WHERE id=@i
  SET @s=@s+@g+CHAR(10)+CHAR(10)
  SET @s=@s+@a+CHAR(10)++'/// '
    SET @i=@i+1
END
print @S