如果沒有EF可以用的話--如何從table schema產生類別(for MS SQL)

俗話說:由儉入奢易,由奢入儉難,
不過,程式設計師應該考慮的是一旦沒了電的人類文明之下,怎麼活下去吧,
(至少做吃的就應該沒這問題了)
 好了,扯太遠了,如果你用習慣了Entity Framework,結果把你派到一定只能用.net 2的公司去
你有兩個選擇--一個是不幹了,一個是用自己的方式來包
以下這個sql stored procedure ,用了參數(table name),執行後就會產出相對的Model class了
來源在此
=========MSSQL store procedure to genreate c# class====================
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE GenerateCsharpClass
(@tableName2Gen varchar(100))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @TableName  varchar(100)
 set @TableName=@tableName2Gen
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
            then '?'
            else ''
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result
END
GO

=============================================================
(說真的,我也是有點不想幹了,明明有web & MSSQL2012 ,居然還要用winform & .net 2.0)

同場加映:
用stored procedure的參數來產生類別的屬性欄位:
===================================================
USE [xxxDB]
GO
/****** Object:  StoredProcedure [dbo].[GenCsharpClassByStoredProc]    Script Date: 2015/7/13 下午 02:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GenCsharpClassByStoredProc]
(@storeProcName varchar(100))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @TableName  varchar(100)
 set @TableName=@storeProcName

declare @Result varchar(max) = 'public class SP_'+@TableName+'
{'
select @Result = @Result + '
    public ' + ColumnType + '' + ' ' + ColumnName + ' { get; set; }
'
from
(
    select
        replace(replace(p.name, ' ', '_'),'@','')  ColumnName,
        p.parameter_id  ColumnId,
        case TYPE_NAME(p.user_type_id)
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'char'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + p.name
        end ColumnType

    from sys.objects AS SO
INNER JOIN sys.parameters AS P
ON SO.OBJECT_ID = P.OBJECT_ID
WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE IN ('P','FN'))
and SO.Type_Desc='SQL_STORED_PROCEDURE'
and so.name=@TableName
--ORDER BY SCHEMA_NAME(SCHEMA_ID), SO.name, P.parameter_id
)t
order by ColumnId,ColumnName

--一定要order by 2個欄位,如果只有一個ColumnId欄位的話,只會顯示出最後一個

set @Result = @Result  + '
}'

print @Result
end

===============================================