从数据库生成实体属性Model & Map(SQL Server)

使用方法

— 生成实体属性

SELECT * FROM [bx_CodeGen_TableToModel] where TableName = ‘bx_Marketing_Invitation’

 

— 生成实体 Map

SELECT * FROM bx_CodeGen_TableToModelMap where TableName = ‘bx_Marketing_Invitation’

 

使用前先在数据库中执行如下 SQL

USE [yourDatabase]
 
GO
 
/****** Object:  UserDefinedFunction [dbo].[bx_CodeGen_GetColumnType]    Script Date: 2016/3/29 17:08:50 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author: aska li
-- Create date: 2015/6/4
-- Description: Get User's balance
-- =============================================
CREATE FUNCTION [dbo].[bx_CodeGen_GetColumnType]
(
@columnType nvarchar(50)
)
RETURNS nvarchar(50)
AS
BEGIN
 
IF @columnType = 'int' RETURN 'int';
IF @columnType = 'bigint' RETURN 'long';
IF @columnType = 'ntext' RETURN 'string';
IF @columnType = 'text' RETURN 'string';
IF @columnType = 'nchar' RETURN 'string';
IF @columnType = 'char' RETURN 'string';
IF @columnType = 'varchar' RETURN 'string';
IF @columnType = 'nvarchar' RETURN 'string';
IF @columnType = 'bigint' RETURN 'long';
IF @columnType = 'date' RETURN 'DateTime';
IF @columnType = 'datetime' RETURN 'DateTime';
 
RETURN @columnType;
 
END
 
GO
 
 
/****** Object:  View [dbo].[bx_CodeGen_TableMetadata]    Script Date: 2016/3/29 16:57:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[bx_CodeGen_TableMetadata]
AS
SELECT        TOP (100) PERCENT (CASE WHEN a.colorder = 1 THEN d .name ELSE d .name END) AS TableName, a.colorder AS ColumnSeq, a.name AS ColumnName, (CASE WHEN COLUMNPROPERTY(a.id, a.name,
                         'IsIdentity') = 1 THEN '√' ELSE '' END) AS 标识, (CASE WHEN
                             (SELECT        COUNT(*)
                               FROM            sysobjects
                               WHERE        (name IN
                                                             (SELECT        name
                                                               FROM            sysindexes
                                                               WHERE        (id = a.id) AND (indid IN
                                                                                             (SELECT        indid
                                                                                               FROM            sysindexkeys
                                                                                               WHERE        (id = a.id) AND (colid IN
                                                                                                                             (SELECT        colid
                                                                                                                               FROM            syscolumns
                                                                                                                               WHERE        (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN '√' ELSE '' END) AS 主键, b.name AS ColumnType, a.length AS 占用字节数,
                         COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, (CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END) AS 允许空, ISNULL(e.text, '')
                         AS 默认值, ISNULL(g.value, '') AS 字段说明
FROM            sys.syscolumns AS a LEFT OUTER JOIN
                         sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
                         sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
                         sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                         sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.major_id
ORDER BY a.id, ColumnSeq
 
 
 
 
 
 
GO
/****** Object:  View [dbo].[bx_CodeGen_TableToModel]    Script Date: 2016/3/29 16:57:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS  ******/
CREATE VIEW [dbo].[bx_CodeGen_TableToModel]
AS
SELECT        TableName, 'public virtual ' + dbo.bx_CodeGen_GetColumnType(ColumnType) + ' ' + ColumnName + '{get;set;}' AS PropertyDef
FROM            dbo.bx_CodeGen_TableMetadata
 
 
 
 
 
 
GO
/****** Object:  View [dbo].[bx_CodeGen_TableToModelMap]    Script Date: 2016/3/29 16:57:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[bx_CodeGen_TableToModelMap]
AS
SELECT        TableName, 'this.Map(m=>m.' + ColumnName + ');' AS MapExpr
FROM            dbo.bx_CodeGen_TableMetadata
 
 
GO
 

 

发表评论

电子邮件地址不会被公开。