根据数据库名创建实体类文件

最新推荐文章于 2024-06-13 09:20:45 发布

pt1314917

最新推荐文章于 2024-06-13 09:20:45 发布

阅读量751

点赞数 1

分类专栏： SQL 文章标签：数据库 sql file output image string

本文链接：https://blog.csdn.net/pt1314917/article/details/4586192

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

-- =============================================
-- Author:  <shipeng.wang>
-- Create date: <2009-09-14>
-- Description: <根据数据库名创建实体类>
-- =============================================
create proc [dbo].[p_db_wsp]
@dbname varchar(50),   --数据库名
@path varchar(100),    --实体类所在目录名，如D:/My/Models
@namespace varchar(50) --实体类命名空间,默认值为Models
as
--判断数据库是否存在
if(db_id(@dbname)is not null)
begin
  if(isnull(@namespace,'')='')
   set @namespace='Models'
  -- 允许配置高级选项
  EXEC sp_configure 'show advanced options', 1
  -- 重新配置
  RECONFIGURE
  -- 启用Ole Automation Procedures
  EXEC sp_configure 'Ole Automation Procedures', 1
  -- 启用xp_cmdshell,可以向磁盘中写入文件
  EXEC sp_configure 'xp_cmdshell', 1
  -- 重新配置
  RECONFIGURE
  declare @dbsql varchar(1000),@tablename varchar(100)
  set @dbsql='declare wsp cursor for select name from ['+@dbname+']..sysobjects where xtype=''u'' and name <>''sysdiagrams'''
  print @dbsql
  exec(@dbsql)
  open wsp
  fetch wsp into @tablename--使用游标循环遍历数据库中每个表
  while(@@fetch_status=0)
  begin
   --根据表中字段组合实体类中的字段和属性
   declare @nsql nvarchar(4000),@sql varchar(8000)
   set @nsql='select @s=isnull(@s+char(9)+''private '',''using System;'+char(13)+'using System.Collections.Generic;'
   +char(13)+'using System.Text;'+char(13)+'namespace '+@namespace+char(13)+
   '{'+char(13)+char(9)+'public class '+@tablename+char(13)+'{''+char(13)+char(9)+''private '')+
   case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string''
   when a.name in(''tinyint'',''smallint'',''int'') then ''int''
   when a.name = ''bigint'' then ''long''
   when a.name in(''datetime'',''smalldatetime'') then ''DateTime''
   when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal''
   when a.name =''bit'' then ''bool''
   else a.name end+'' ''+lower(''_''+b.name)+'';''+char(13)+char(9)+''public ''+
   case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string''
   when a.name in(''tinyint'',''smallint'',''int'') then ''int''
   when a.name=''bigint'' then ''long''
   when a.name in(''datetime'',''smalldatetime'') then ''DateTime''
   when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal''
   when a.name =''bit'' then ''bool''
   else a.name end
   +'' ''+b.name+char(13)+char(9)+''{''+char(13)+char(9)+char(9)+''get{return ''+lower(''_''+b.name)+'';}''+
   char(13)+char(9)+char(9)+''set{''+lower(''_''+b.name)+''=value;}''+char(13)+char(9)+''}''+char(13)
   from ['+@dbname+']..syscolumns b,
   (select distinct name,xtype from ['+@dbname+']..systypes where status=0) a
   where a.xtype=b.xtype and b.id=object_id(''['+@dbname+']..['+@tablename+']'')'
   exec sp_executesql @nsql,N'@s varchar(8000) output',@sql output
   set @sql=@sql+char(9)+'}'+char(13)+'}'
   --print @sql
   DECLARE @err INT,@fso INT,@fleExists BIT,@file VARCHAR(100)
   SET @file=@path+'/'+@tablename+'.cs'
   EXEC @err=sp_OACreate 'Scripting.FileSystemObject',@fso OUTPUT
   EXEC @err=sp_OAMethod @fso, 'FileExists',@fleExists OUTPUT,@file
   EXEC @err = sp_OADestroy @fso

   IF @fleExists!=0
    exec('exec xp_cmdshell ''del '+@file+'''') --存在则删除
   exec('exec xp_cmdshell ''echo '+@sql+' > '+@file+'''') --将文本写进文件中
   set @sql=null
   fetch wsp into @tablename
  end
  close wsp
  deallocate wsp
  print '生成成功！'
end
else
  print '数据库不存在！'