月度归档： 2014 年 12 月

SQL SERVER更改字段名

--把Account表的Pwd字段改名为Pwd2
exec sp_rename 'Account.[Pwd]' ,'Pwd2'

SQL SERVER 生成数据库表对应的实体类的存储过程

CREATE PROCEDURE [dbo].[CreateModel]   
    @tableName varchar(100 )
AS
BEGIN
SET NOCOUNT ON;

-- Subquery to return only the copy paste text
Select PropertyColumn from (
    SELECT 1 as rowNr, 'public class ' + @tableName + ' {' as PropertyColumn
    UNION
    SELECT 2 as rowNr, 'public ' + a1. NewType + ' ' + a1.COLUMN_NAME + ' {get;set;}' as PropertyColumn
    -- ,* comment added so that i get copy pasteable output
     FROM
    (
        /*using top because i'm putting an order by ordinal_position on it.
        putting a top on it is the only way for a subquery to be ordered*/
        SELECT TOP 100 PERCENT
        COLUMN_NAME ,
        DATA_TYPE ,
        IS_NULLABLE ,
        CASE
            WHEN DATA_TYPE = 'varchar' THEN 'string'
            WHEN DATA_TYPE = 'nvarchar' THEN 'string'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'NO' THEN 'DateTime'
            WHEN DATA_TYPE = 'smalldatetime' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'datetime2' AND IS_NULLABLE = 'YES' THEN 'DateTime?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'YES' THEN 'int?'
            WHEN DATA_TYPE = 'int' AND IS_NULLABLE = 'NO' THEN 'int'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'NO' THEN 'Int16'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'Int16?'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'decimal' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'numeric' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'NO' THEN 'decimal'
            WHEN DATA_TYPE = 'money' AND IS_NULLABLE = 'YES' THEN 'decimal?'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'NO' THEN 'long'
            WHEN DATA_TYPE = 'bigint' AND IS_NULLABLE = 'YES' THEN 'long?'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' THEN 'byte'
            WHEN DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'YES' THEN 'byte?'
            WHEN DATA_TYPE = 'char' THEN 'string'                       
            WHEN DATA_TYPE = 'timestamp' THEN 'byte[]'
            WHEN DATA_TYPE = 'varbinary' THEN 'byte[]'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'NO' THEN 'bool'
            WHEN DATA_TYPE = 'bit' AND IS_NULLABLE = 'YES' THEN 'bool?'
            WHEN DATA_TYPE = 'xml' THEN 'string'
        END AS NewType
        FROM INFORMATION_SCHEMA .COLUMNS
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1
    UNION
    SELECT 3 as rowNr,   '} // class ' + @tableName
    ) as t Order By rowNr asc
END

--调用下面的代码生对指定表的实体类
Helper_CreatePocoFromTableName 'Account'

--调用下面的代码生成所有实体实
USE Linshu
GO
    DECLARE @field1 nvarchar(400 )
    DECLARE cur CURSOR LOCAL for

    SELECT TABLE_NAME FROM information_schema .tables
    OPEN cur
    FETCH NEXT FROM cur INTO @field1 --, @field2
    WHILE @@FETCH_STATUS = 0 BEGIN         
        exec Helper_CreatePocoFromTableName @field1 -- , @field2           
        fetch next from cur into @field1 -- , @field2
    END

close cur
deallocate cur