编写适合于自己的代码生成器 (zhuan)
在程序开发过程当中,程序员会经常做着重复性的工作,最常见的是访问数据库,程序员要经常编写增、删、改、分页之类的操作。为了避免这个问题,我们可以编写一个适合于自己使用的代码生成器。当然,像这样的代码生成器网上也已经有免费版本的了,功能也很强大,如Codematic,但并不一定适合你或你的团队。为了达到要求,我自己也写了一个代码生成器,实现起来也是很简单的,无非就是字符串的相加,和数据库的遍历。下面说一下如何遍历SQL数据库。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
获取SQL SERVER中的所有数据库:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
SQL数据库信息,存储在master 表中的,可执行SQL语句来获取数据库信息: SELECT * FROM sysdatabases用此查询获取的结果集中,name 字段为数据库的名字。下面是我使用的用来获取数据库信息的函数,供大家参考: /**//**//**//// <summary>
/// 获取指定SQL SERVER 中的所有数据库
/// </summary>
/// <returns></returns>
public DataSet GetDatabase()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
DataProvider.Custom.SqlQuery sqlQuery = new DataProvider.Custom.SqlQuery();
sqlQuery.Sql = "SELECT * FROM sysdatabases";
using (CustomDataProvider db = GetDatabaseConnection())
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return db.ExecuteDataSet(sqlQuery);
}
}上述代码中所使用的类SqlQuery 是为CustomDataProvider类执行查询使用的,这两个类封装了ADO.NET对数据库的操作,同时支持SQL SERVER和ACCESS,可实现两种数据库的相互转化,支持.NET 1.1。其中CustomDataProvider 类的ExecuteDataSet方法执行了一个SQL语句,并返回包含查询结果的数据集。这样的封装网上有很多,这里不再多讲。
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
获取数据库中的用户表,SQL语句: SELECT id,name FROM sysobjects WHERE xtype='U' AND name <> 'dtproperties' 函数: /**//**//**//// <summary>
/// 获取指定数据库中的所有表
/// </summary>
/// <param name="database">数据库的名字</param>
/// <returns></returns>
public DataSet GetTableList(string database)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
DataProvider.Custom.SqlQuery sqlQuery = new DataProvider.Custom.SqlQuery();
sqlQuery.Sql = "SELECT id,name FROM sysobjects WHERE xtype='U' AND name <> 'dtproperties'";
using (CustomDataProvider db = GetDatabaseConnection(database))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return db.ExecuteDataSet(sqlQuery);
}
} 获取表中的所有字段信息:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**//**//// <summary>
/// 获取指定表中的所有字段
/// </summary>
/// <param name="database">数据库的名字</param>
/// <param name="table">要获取字段的表</param>
/// <returns></returns>
public DataSet GetColumsList(string database, string table)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
DataProvider.Custom.SqlQuery sqlQuery = new DataProvider.Custom.SqlQuery();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
sqlQuery.Sql = @"SELECT a.colorder 列序号, a.name 列名, (CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END) 标识, (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) 主键, b.name 类型,
a.length 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度,
isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
(CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END) 允许空, isnull(e.text, '') 默认值,
isnull(g.[value], '') AS 字段说明
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 = @table
ORDER BY a.id, a.colorder";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
sqlQuery.AddParameter("@table", table);
using (CustomDataProvider db = GetDatabaseConnection(database))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return db.ExecuteDataSet(sqlQuery);
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
有了上述的介绍,相信读者可以自己编写出遍历数据库的程序了。剩下的事情就是根据自己的需要,通过上述获取的数据库信息生成代码(很长的字符串相加,看起来会很麻烦,没什么难度),并进行保存。下边是我编写的类中生成数据访问层接口的函数: public string CreateIDALCode()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
string className = _calssName;
string nameSpace = _nameSpace;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
string strTime; //获取系统时间#region string strTime; //获取系统时间
string strTime;
strTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
StringBuilder result = new StringBuilder();
result.Append(
@"using System;
using System.Data;
using System.Collections;
using DBUtility.Generic;
using " + nameSpace + @".Model;
using " + nameSpace + @".Collection;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
namespace " + nameSpace + @".IDAL
{
/// <summary>
/// I" + className + @" 的摘要说明
/// </summary>
/// <remarks>
/// 作者:" + _author + @"
/// 时间:" + strTime + @"
/// </remarks>
public interface I" + className + @" : IDisposable
{
/// <summary>
/// 执行命令的 DbHelper 实例对象
/// </summary>
DbHelper DbHelper
{
get;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 增加一条数据
/// </summary>
/// <returns>新增记录的自增 ID</returns>
int Add" + className + @"(" + className + " " + className.Substring(0, 1).ToLower() + className.Substring(1) + @");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 删除一条数据
/// </summary>
/// <returns>删除记录的行数/returns>
int Delete" + className + @"(int id);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 修改记录数
/// </summary>
/// <returns>被修改的记录数/returns>
int Update" + className + @"(" + className + " " + className.Substring(0, 1).ToLower() + className.Substring(1) + @");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取给定 ID 的指定记录
/// </summary>
/// <param name=" + "\"id\"" + @">将要获取记录的 ID </param>
/// <returns>返回 " + className + @" 类的一个实例对象</returns>
" + className + @" Get" + className + @"(int id);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取所有的记录
/// </summary>
/// <returns>包含所有记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取记录的条数
/// </summary>
/// <returns>数据库中记录的条数</returns>
int GetCount();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取页面数量
/// </summary>
/// <param name=""pageSize"">页面大小</param>
/// <returns>页面数量</returns>
int GetPageCount(int pageSize);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取指定页面的记录
/// </summary>
/// <param name=" + "\"pageSize\"" + @">页面记录数量</param>
/// <param name=" + "\"pageIndex\"" + @">要获取的页面</param>
/// <returns>包含所有记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(int pageSize, int pageIndex);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录
/// </summary>
/// <param name=""filter"">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <returns>包含符合条件的记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取记录的条数
/// </summary>
/// <param name=""filter"">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <returns>数据库中记录的条数</returns>
int GetCount(SqlQuery filter);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取页面数量
/// </summary>
/// <param name=""filter"">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=""pageSize"">页面大小</param>
/// <returns>页面数量</returns>
int GetPageCount(SqlQuery filter,int pageSize);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录
/// </summary>
/// <param name=""filter"">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=""count"">查询结果包含的记录数</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter,int count);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取指定页面的记录
/// </summary>
/// <param name=""filter"">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"pageSize\"" + @">页面记录数量</param>
/// <param name=" + "\"pageIndex\"" + @">要获取的页面</param>
/// <returns>包含符合条件的记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter, int pageSize, int pageIndex);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录
/// </summary>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter, string order);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录
/// </summary>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <param name=" + "\"count\"" + @">查询结果的数量</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter, string order, int count);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录
/// </summary>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <param name=" + "\"pageSize\"" + @">页面记录数量</param>
/// <param name=" + "\"pageIndex\"" + @">要获取的页面</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(SqlQuery filter, string order, int pageSize, int pageIndex);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录。可以指定要获取的字段。
/// </summary>
/// <param name=" + "\"fields\"" + @">结果集中的字段</param>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(string fields, SqlQuery filter, string order);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录。可以指定要获取的字段。
/// </summary>
/// <param name=" + "\"fields\"" + @">结果集中的字段</param>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <param name=" + "\"count\"" + @">查询结果的数量</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(string fields, SqlQuery filter, string order, int count);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 获取满足指定条件的记录。可以指定分页和要获取的字段
/// </summary>
/// <param name=" + "\"fields\"" + @">结果集中的字段</param>
/// <param name=" + "\"filter\"" + @">filter 过滤参数( filter.Sql 标识 SQL 中 WHERE 后边的条件,filter.Parameters为相应的参数)</param>
/// <param name=" + "\"order\"" + @">排序字段( SQL 中 ORDER BY 后边的条件)</param>
/// <param name=" + "\"pageSize\"" + @">页面记录数量</param>
/// <param name=" + "\"pageIndex\"" + @">要获取的页面</param>
/// <returns>包含符合条件记录的 " + className + @"Collection 实例</returns>
" + className + @"Collection Get" + className + @"(string fields, SqlQuery filter, string order, int pageSize, int pageIndex);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 启动事务
/// </summary>
void BeginTrans();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 提交事务
/// </summary>
void Commit();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
/// <summary>
/// 事务回滚
/// </summary>
void Rollback();
}
}"
);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return result.ToString();
}
posted on
2007-03-20 18:21
蓝狐
阅读(
311)
评论()
编辑
收藏
举报