现在有很多开发ORM的工具,象本人自己使用Grove.但是有时候因为业务需要不得不定制一些自己的ORM类以满足需求.
下面是用SQL2005直接生成数据类的实例:输出方式改为:以文本形式显示结果.
EXEC CreateDorNetFile 'DP_SalesCounter'
--数据表字段说明直接作为类属性的说明
- public class DP_SalesCounter
- {
- private String _SalesCounterID;
- private String _SalesCounterCode;
- private String _SalesCounterName;
- private String _ZoneID;
- private String _SalesDepartmentID;
- private Boolean _IsAgent;
- private String _AgentID;
- private Boolean _IsControlCenter;
- /// <summary>
- ///柜台ID
- /// </summary>
- public String SalesCounterID
- {
- get { return _SalesCounterID; }
- set { SalesCounterID = value; }
- }
- /// <summary>
- ///柜台代码
- /// </summary>
- public String SalesCounterCode
- {
- get { return _SalesCounterCode; }
- set { SalesCounterCode = value; }
- }
- /// <summary>
- ///柜台名称
- /// </summary>
- public String SalesCounterName
- {
- get { return _SalesCounterName; }
- set { SalesCounterName = value; }
- }
- /// <summary>
- ///地区ID
- /// </summary>
- public String ZoneID
- {
- get { return _ZoneID; }
- set { ZoneID = value; }
- }
- /// <summary>
- ///所属部门ID
- /// </summary>
- public String SalesDepartmentID
- {
- get { return _SalesDepartmentID; }
- set { SalesDepartmentID = value; }
- }
- /// <summary>
- ///是否代理
- /// </summary>
- public Boolean IsAgent
- {
- get { return _IsAgent; }
- set { IsAgent = value; }
- }
- /// <summary>
- ///代理商ID
- /// </summary>
- public String AgentID
- {
- get { return _AgentID; }
- set { AgentID = value; }
- }
- /// <summary>
- ///是否中心
- /// </summary>
- public Boolean IsControlCenter
- {
- get { return _IsControlCenter; }
- set { IsControlCenter = value; }
- }
- }
需要用到的过程与函数
- CREATE FUNCTION [dbo].[ConvertSQLTypeToDorNetType](@SQLType VARCHAR(50))
- RETURNS VARCHAR(50)
- AS
- /*
- 把SQL类型转换为.NET数据类型
- */
- BEGIN
- DECLARE @DorNetTyp VARCHAR(50)
- SET @DorNetTyp=''
- SET @SQLType=RTRIM(LTRIM(@SQLType))
- IF @SQLType='bigint'
- SET @DorNetTyp='Int64'
- ELSE IF @SQLType='binary'
- SET @DorNetTyp='Byte[]'
- ELSE IF @SQLType='bit'
- SET @DorNetTyp='Boolean'
- ELSE IF @SQLType='char'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='datetime'
- SET @DorNetTyp='DateTime'
- ELSE IF @SQLType='decimal'
- SET @DorNetTyp='Decimal'
- ELSE IF @SQLType='float'
- SET @DorNetTyp='Double'
- ELSE IF @SQLType='image'
- SET @DorNetTyp='Byte[]'
- ELSE IF @SQLType='int'
- SET @DorNetTyp='Int32'
- ELSE IF @SQLType='money'
- SET @DorNetTyp='Decimal'
- ELSE IF @SQLType='nchar'
- SET @DorNetTyp='char'
- ELSE IF @SQLType='ntext'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='numeric'
- SET @DorNetTyp='Decimal'
- ELSE IF @SQLType='nvarchar'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='real'
- SET @DorNetTyp='Single'
- ELSE IF @SQLType='smalldatetime'
- SET @DorNetTyp='DateTime'
- ELSE IF @SQLType='smallint'
- SET @DorNetTyp='Int16'
- ELSE IF @SQLType='smallmoney'
- SET @DorNetTyp='Decimal'
- ELSE IF @SQLType='sql_variant'
- SET @DorNetTyp='Object'
- ELSE IF @SQLType='sysname'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='text'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='timestamp'
- SET @DorNetTyp='DateTime'
- ELSE IF @SQLType='tinyint'
- SET @DorNetTyp='Byte'
- ELSE IF @SQLType='uniqueidentifier'
- SET @DorNetTyp='Guid'
- ELSE IF @SQLType='varbinary'
- SET @DorNetTyp='Byte[]'
- ELSE IF @SQLType='varchar'
- SET @DorNetTyp='String'
- ELSE IF @SQLType='xml'
- SET @DorNetTyp='String'
- RETURN @DorNetTyp
- END
- CREATE PROC [dbo].[CreateDorNetFile]
- @TABLE VARCHAR(50)
- AS
- /*
- 根据表生成.NET数据类
- */
- SELECT 'public class '+@TABLE+dbo.SystemNewLine()+'{'
- UNION ALL
- SELECT 'private '+dbo.ConvertSQLTypeToDorNetType(systypes.name)+' _'+syscolumns.name +';' FROM syscolumns
- LEFT JOIN systypes ON syscolumns.xtype=systypes.xtype
- WHERE ID IN (SELECT ID FROM sysObjects WHERE Name=@TABLE)
- UNION ALL
- SELECT dbo.SystemNewTab()
- UNION ALL
- SELECT
- '/// <summary>'+dbo.SystemNewLine()+
- '///'+cast(isnull(sys.extended_properties.value,'') as varchar(50))+dbo.SystemNewLine()+
- '/// </summary>'+dbo.SystemNewLine()+
- 'public '+dbo.ConvertSQLTypeToDorNetType(systypes.name)+' '+syscolumns.name
- +dbo.SystemNewLine()+'{'+dbo.SystemNewLine()+
- +dbo.SystemNewTab()+'get{ return _'+syscolumns.name +' ; }'
- +dbo.SystemNewLine()+
- +dbo.SystemNewTab()+'set{ '+syscolumns.name +' = value ; }'
- +dbo.SystemNewLine()+'}'
- FROM syscolumns
- LEFT JOIN systypes ON syscolumns.xtype=systypes.xtype
- LEFT JOIN sys.extended_properties ON syscolumns.ID=sys.extended_properties.major_id and syscolumns.colorder=sys.extended_properties.minor_id
- WHERE ID IN (SELECT ID FROM sysObjects WHERE Name=@TABLE)
- UNION ALL
- SELECT '}'
两个字符函数
- create FUNCTION [dbo].[SystemNewTab]()
- RETURNS VARCHAR(50)
- AS
- BEGIN
- RETURN ' '
- END
- CREATE FUNCTION [dbo].[SystemNewLine]()
- RETURNS VARCHAR(50)
- AS
- BEGIN
- RETURN CHAR(13)
- END
希望对个位有所帮助