首页 新闻 搜索 专区 学院

跪求 用 msssql 语句或储存过程的生成asp.net 实体类工具或源码

0
悬赏园豆:50 [已解决问题] 解决于 2014-01-19 14:15

跪求 用 msssql 语句或储存过程的生成asp.net 实体类工具!

用动软件代码生成,能不能做到? 请问那位大牛帮一下!

< >
分享
最佳答案
0

用 sql 语句生成实体类

CREATE PROCEDURE [dbo].[Helper_CreatePocoFromTableName]    
    @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 ALL
    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 = '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 'short'
            WHEN DATA_TYPE = 'smallint' AND IS_NULLABLE = 'YES' THEN 'short?'

            WHEN DATA_TYPE = 'float' AND IS_NULLABLE = 'NO' THEN 'double'
            WHEN DATA_TYPE = 'float' AND IS_NULLABLE = 'YES' THEN 'double?'

            WHEN DATA_TYPE = 'real' AND IS_NULLABLE = 'NO' THEN 'float'
            WHEN DATA_TYPE = 'real' AND IS_NULLABLE = 'YES' THEN 'float?'

            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 = 'binary' 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'
            WHEN DATA_TYPE = 'uniqueidentifier' THEN 'Guid'              

        END AS NewType
        FROM INFORMATION_SCHEMA .COLUMNS
        WHERE TABLE_NAME = @tableName
        ORDER BY ORDINAL_POSITION
        ) AS a1
    UNION ALL
    SELECT 3 as rowNr ,  '} // class ' + @tableName
) as t Order By rowNr asc
END

 

调用只要  

exec Helper_CreatePocoFromTableName 'tablename'  就好了
 
收获园豆:50
gunsmoke | 老鸟四级 |园豆:3592 | 2014-01-08 11:47

谢谢你这位帅哥!

不过遇到复杂sql 语句,你提供一个只是针这个表名生成实体类

比如下面的sql 语句怎么生成实体类,你这边有什么好的方法! 谢谢!!

select top 1 a.ID,a.subject,a.typea,a.city,a.views,a.userName,a.sort,(select top 1 Namea from Class where ID=a.ClassID) as ClassName,(select count(id) from comDataReply where fid=a.ID and typea='vote') as replyNum,a.WebSite,a.isNew,a.isTop,a.isWeb,a.isHot,a.addTime from ForumsData a where a.site like '%BBP%' and typea='vote' order by a.addTime desc

 

 

 

海上村民 | 园豆:157 (初学一级) | 2014-01-10 10:39

@海上村民:  你可以用你的sql语句新创建一个view。   然后调用以上的stored procedure 传入新建的view名字 就好了。

gunsmoke | 园豆:3592 (老鸟四级) | 2014-01-10 18:06

@gunsmoke: 

谢谢这位哥们,以后有问题多请教你!!

海上村民 | 园豆:157 (初学一级) | 2014-01-19 14:16
其他回答(2)
0

http://www.cnblogs.com/baiboy/archive/2013/03/16/2963489.html 动软代码生成器的使用过程参考。

aehyok | 园豆:1012 (小虾三级) | 2014-01-06 18:25

谢谢这位兄弟!!

动软件代码 我也用过,只想找一个 用 msssql 语句储存过程的生成asp.net 实体类工具?

支持(0) 反对(0) 海上村民 | 园豆:157 (初学一级) | 2014-01-06 21:31

@海上村民: 我虽然没看过动软代码生成工具的代码,不过感觉里面肯定是用了mssql或者存储过程进行实现的吧。

支持(0) 反对(0) aehyok | 园豆:1012 (小虾三级) | 2014-01-06 22:48

@aehyok: 帅哥,有没有教程,渴望您的佳作! 兄弟不胜感激!!

支持(0) 反对(0) 海上村民 | 园豆:157 (初学一级) | 2014-01-06 22:52
支持(0) 反对(0) aehyok | 园豆:1012 (小虾三级) | 2014-01-06 22:56
0

需要请联系 QQ245506181

落幕残情 | 园豆:9 (初学一级) | 2014-01-13 16:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册