<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".cs" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Xml" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
ModelManager manager = new ModelManager();
string tableName = "tbOptAuthor";
DataTable table= manager.GetTableSchema(tableName);
#>
/* ****************************************************************************************
* 版权所有：西安xxx软件有限公司
* 用 途：数据传输对象(DTO)
* 结构组成：
* 作 者：xxx
* 创建日期：<#=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")#>
* 历史记录：
* ****************************************************************************************
* 修改人员：
* 修改日期：
* 修改说明：
* ****************************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace wgx.Model
{
[Serializable]
/// <summary>
/// <#= tableName.Replace("tb","DTO_") #>
/// </summary>
public class <#= tableName.Replace("tb","DTO_") #>
{
#region 属性
<#
foreach(DataRow row in table.Rows)
{
#>
/// <summary>
/// <#=row["Description"]#>
/// </summary>
public <#= manager.TransFromSqlType(row["DataType"].ToString(),row["IsNullable"].ToString())#> <#=row["ColumnName"]#>{ get; set; }
<#}
#>
#endregion
}
} <#+
public class ModelManager
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private const string CONNECTION_STRING="Data Source=.;Initial Catalog=dbserver;User ID=sa;pwd=000000";
/// <summary>
/// 用户信息表名
/// </summary>
private const string PERSONINFO_TABLE_NAME = "tbOptAuthor";
/// <summary>
/// 根据表名查询表结构信息
/// SQL Server 2005写法
/// </summary>
private const string SELECT_SCHEMA_BY_TABLE_NAMEs = @"SELECT
d.name AS TableName,
a.colorder AS ColumnID,
a.name AS ColumnName,
b.name AS DataType,
g.[value] AS Description,
CASE WHEN a.isnullable = 0 THEN 'false'
ELSE 'true'
END AS IsNullable
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
WHERE d.[name] ='{0}'
order by a.id,a.colorder"; /// <summary>
/// 根据表名查询表结构信息
/// SQL Server 2000写法
/// </summary>
private const string SELECT_SCHEMA_BY_TABLE_NAME = @"SELECT (
case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',a.name N'ColumnName',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(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) N'主键',b.name N'DataType',a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'IsNullable',
isnull(e.text,'') N'默认值',isnull(g.[value],'') AS N'Description'
--into ##tx
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name='{0}'
order by object_name(a.id),a.colorder"; /// <summary>
/// 获得数据连接
/// </summary>
/// <returns></returns>
private SqlConnection GetConnection()
{
return new SqlConnection(CONNECTION_STRING);
}
/// <summary>
/// 释放连接
/// </summary>
/// <param name="con"></param>
private void ReleaseConnection(SqlConnection con)
{
if (con != null)
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
} /// <summary>
///
/// </summary>
/// <param name="tableName"></param>
public DataTable GetTableSchema(string tableName)
{
DataTable dt;
using (SqlConnection con = GetConnection())
{
con.Open(); SqlCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format(SELECT_SCHEMA_BY_TABLE_NAME,tableName);
cmd.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
dt = ds.Tables[];
} return dt;
}
/// <summary>
///
/// </summary>
public void Generate()
{
DataTable table = GetTableSchema(PERSONINFO_TABLE_NAME); if (table != null && table.Rows.Count > )
{
foreach (DataRow row in table.Rows)
{
Console.WriteLine("public class {0}", row["TableName"]);
Console.WriteLine("public {0} {1}", TransFromSqlType(row["DataType"].ToString(),row["IsNullable"].ToString()), row["ColumnName"]);
}
}
}
/// <summary>
/// SQL
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public string TransFromSqlType(string type,string isNull)
{
string typeStr="";
switch (type) //数据库的数据类型转换为对应的C#的数据类型，不是很完整
{
case "datetime":
case "smalldatetime":
typeStr=isNull=="true"? "?":"";
return "DateTime"+typeStr;
break;
case "int":
case "smallint":
typeStr=isNull=="true"? "?":"";
return "int"+typeStr;
break;
case "bit":
typeStr=isNull=="true"? "?":"";
return "bool"+typeStr;
break;
case "float":
typeStr=isNull=="true"? "?":"";
return "double"+typeStr;
break;
case "decimal":
case "money":
case "smallmoney":
case "numeric":
typeStr=isNull=="true"? "?":"";
return "decimal"+typeStr;
break;
case "uniqueidentifier":
typeStr=isNull=="true"? "?":"";
return "Guid"+typeStr;
break;
default:
return "string"+typeStr;
break;
}
}
}
#>
生成后的实体：
/* ****************************************************************************************
* 版权所有：西安xxx软件有限公司
* 用 途：数据传输对象(DTO)
* 结构组成：
* 作 者：xxx
* 创建日期：2012-12-28 11:32:47
* 历史记录：
* ****************************************************************************************
* 修改人员：wgx
* 修改日期：2014-12-28 11:32:47
* 修改说明：新增一个针对sql 2000数据库的查询方法
* ****************************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace wgx.Model
{
[Serializable]
/// <summary>
/// DTO_OptAuthor
/// </summary>
public class DTO_OptAuthor
{
#region 属性
/// <summary>
/// 用户组代码
/// </summary>
public string strGroupCode{ get; set; }
/// <summary>
/// 功能模块代码
/// </summary>
public string strCaseCode{ get; set; }
/// <summary>
/// 权限级别
/// </summary>
public string strLevel{ get; set; }
/// <summary>
/// 组数据级别
/// </summary>
public string strGroupLevel{ get; set; }
#endregion
}
}
使用过程可能遇到的问题：
解决办法：