.net代码生成模板(MyGeneration版)
经过一段时间的努力，终于把.net代码生成模板完成了。高兴！！
背景:
前段时间比较有空，就拿几个ORM框架来看看，试用之后发觉ActiveRecord和SubSonic还不错，于是就深入一点拿来开发，没想到在开发的过程中却遇到许多莫名其妙的问题，心里真是郁闷！后来想想既然这些东西都是不可控制的，那就找一个代码生成器吧，至少生成的代码自己是可以看到的，听朋友说动软的代码生成器还不错就Down了一个下来，不过生成代码之后多少有些令人遗憾，因为里面的方法太少了，而且软件的版本更新也太慢了，最后想想为什么自己不写一个出来呢，用代码生成器来写，只要把平时经常要操作的方法写出来就OK了。
代码生成器MyGeneration：
我用的代码生成器是MyGeneration，本人认为这是一个相当不错的东东，免费的（下个版本即将开源），安装文件也小，才2.21M，可用C#编写，更重要的是它提供了一个完整的操作数据库所用到的类库。有关MyGeneration的文档请参考DDLLY写的《强大的代码生成工具MyGeneration 》以及MyGeneration的官方网站：http://www.mygenerationsoftware.com/。
.net代码生成模板：
使用步骤：
1、把下载的压缩包（在文章的末尾处下载）解压之后，发现里面会有两个文件：ED.zeus和DbHelper.cs；
2、把DbHelper.cs复制到MyGeneration的安装目录下，如：c:\Program Files\MyGeneration；
3、在你工程中的配置文件Web.config里加上连接数据库的字符串，如：
<connectionStrings> <add name ="Test" connectionString ="server=(local);database=Test;uid=sa;pwd=" providerName ="System.Data.SqlClient"/></connectionStrings>记得加上providerName 这个参数哦。
4、打开ED.zeus，Edit-->Default Settings-->Connection选项卡-->Database TargetMapping框-->设DbTarget为DbType。
运行，界面如下：
其中的Save按钮是用来把窗口内的东西保存到注册表中的，下次打开的时候就不用再修改了。其它的都是一眼就能看得懂的，这里不再讲述。
5、填写好参数后，点击OK按钮，生成代码。
注：目前只支持sql server 2000，因为我对其它的数据库不熟。
生成的代码：
生成的代码默认为Model和DAL两个文件夹：
Model是用来放实体类文件的，而DAL是用来放操作类文件及数据库操作基础类和公共类。
实体类文件：
由私有变量、默认构造函数、属性三部分组成。其中属性支持外键的功能，当然了，只有1：N中的主表，如果也包含从表的话，就会造成资源的浪费和性能的下降。
namespace Coree.Model
{
public class Child
{ 私有成员#region 私有成员 private int _ChildID; private Guid _ParentID; private string _Name; 
#endregion 默认构造函数#region 默认构造函数 public Child() { _ChildID = 0; _ParentID = Guid.Empty; _Name = String.Empty; } #endregion 公有属性#region 公有属性 /**//// <summary> /// 主键 /// </summary> public int ChildID { get { return _ChildID; } set { _ChildID = value; } } /**//// <summary> /// 父类 /// </summary> public Guid ParentID { get { return _ParentID; } set { _ParentID = value; } } /**//// <summary> /// 姓名 /// </summary> public string Name { get { return _Name; } set { _Name = value; } } /**//// <summary> /// 父类 /// </summary> public Parent Parent { get { if(_ParentID != Guid.Empty) { return (new Coree.DAL.Parent()).GetModel(_ParentID); } else { return null; } } } #endregion }
}数据操作类文件：
由一些常用的方法组成，有Exist,GetCount,GetModel,GetByID,GetList,Add,Update,Delete等，这些方法都有重载的方法。其中GetList返回的是DataSet，Delete方法用到了事务，具体可看生成的代码。
namespace Coree.DAL{ public class Child { -----------Exist-----------#region -----------Exist----------- public bool Exist() { return Exist("", null); } /**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param> public bool Exist(string query, params object[] parameterValues) { DbHelper db = new DbHelper("Test"); string sqlQuery = "select count(*) from Child " + CommonUtil.ProcessQuery(query, parameterValues); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); CommonUtil.AddInParameter(db, dbCommand, "Child", query, parameterValues); return Convert.ToInt32(db.ExecuteScalar(dbCommand)) > 0 ? true : false; } #endregion -----------数据统计-----------#region -----------数据统计----------- public int GetCount() { return GetCount("", null); } /**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param> public int GetCount(string query, params object[] parameterValues) { DbHelper db = new DbHelper("Test"); string sqlQuery = "select count(*) from Child " + CommonUtil.ProcessQuery(query, parameterValues); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); CommonUtil.AddInParameter(db, dbCommand, "Child", query, parameterValues); return Convert.ToInt32(db.ExecuteScalar(dbCommand)); } #endregion -----------得到一个对象实体---------#region -----------得到一个对象实体--------- /**//// <summary> /// 得到一个对象实体 /// </summary> public Coree.Model.Child GetModel(object ChildID) { DataSet ds = new DataSet(); ds = GetByID(ChildID); Coree.Model.Child model = new Coree.Model.Child(); if (ds.Tables.Count != 0) { model.ChildID = (int)ds.Tables[0].Rows[0]["ChildID"]; model.ParentID = (Guid)ds.Tables[0].Rows[0]["ParentID"]; model.Name = (string)ds.Tables[0].Rows[0]["Name"]; } return model; } #endregion -----------GetByID-----------#region -----------GetByID----------- /**//// <summary> /// GetByID /// </summary> public DataSet GetByID(object ChildID) { return GetList("where ChildID=?", ChildID); } #endregion -----------数据(DataSet)------------#region -----------数据(DataSet)------------ public DataSet GetList() { return GetList(""); } /**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param> public DataSet GetList(string query, params object[] parameterValues) { DbHelper db = new DbHelper("Test"); string sqlQuery = "select * from Child" + CommonUtil.ProcessQuery(query, parameterValues); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); CommonUtil.AddInParameter(db, dbCommand, "Child", query, parameterValues); return db.ExecuteDataSet(dbCommand); } public DataSet GetList(int StartIndex, int PageSize) { return GetList(StartIndex, PageSize, ""); } /**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param> public DataSet GetList(int StartIndex, int PageSize, string query, params object[] parameterValues) { DbHelper db = new DbHelper("Test"); string sqlQuery = CommonUtil.BuildPagerQuery("Child", "ChildID", "int", StartIndex, PageSize, query, parameterValues); DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@StartIndex", DbType.Int32, StartIndex); db.AddInParameter(dbCommand, "@PageSize", DbType.Int32, PageSize); CommonUtil.AddInParameter(db, dbCommand, "Child", query, parameterValues); return db.ExecuteDataSet(dbCommand); } #endregion -----------添加-----------#region -----------添加----------- public int Add(Coree.Model.Child model) { if (!new Parent().Exist("where ParentID=?", model.ParentID)) { throw new Exception("父表Parent中不存在ParentID值为" + model.ParentID + "的列"); } DbHelper db = new DbHelper("Test"); string sqlQuery = "insert into Child(ParentID,Name) values(@ParentID,@Name)"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ParentID", CommonUtil.GetDbType("Child", "ParentID"), model.ParentID); db.AddInParameter(dbCommand, "@Name", CommonUtil.GetDbType("Child", "Name"), model.Name); return db.ExecuteNonQuery(dbCommand); } public int Add(Guid ParentID,string Name) { if (!new Parent().Exist("where ParentID=?", ParentID)) { throw new Exception("父表Parent中不存在ParentID值为" + ParentID + "的列"); } DbHelper db = new DbHelper("Test"); string sqlQuery = "insert into Child(ParentID,Name) values(@ParentID,@Name)"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ParentID", CommonUtil.GetDbType("Child", "ParentID"), ParentID); db.AddInParameter(dbCommand, "@Name", CommonUtil.GetDbType("Child", "Name"), Name); return db.ExecuteNonQuery(dbCommand); } #endregion -----------更新-----------#region -----------更新----------- public int Update(Coree.Model.Child model) { if (!new Parent().Exist("where ParentID=?", model.ParentID)) { throw new Exception("父表Parent中不存在ParentID值为" + model.ParentID + "的列"); } DbHelper db = new DbHelper("Test"); string sqlQuery = "update Child set ParentID = @ParentID,Name = @Name where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), model.ChildID); db.AddInParameter(dbCommand, "@ParentID", CommonUtil.GetDbType("Child", "ParentID"), model.ParentID); db.AddInParameter(dbCommand, "@Name", CommonUtil.GetDbType("Child", "Name"), model.Name); return db.ExecuteNonQuery(dbCommand); } public int Update(int ChildID,Guid ParentID,string Name) { if (!new Parent().Exist("where ParentID=?", ParentID)) { throw new Exception("父表Parent中不存在ParentID值为" + ParentID + "的列"); } DbHelper db = new DbHelper("Test"); string sqlQuery = "update Child set ParentID = @ParentID,Name = @Name where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), ChildID); db.AddInParameter(dbCommand, "@ParentID", CommonUtil.GetDbType("Child", "ParentID"), ParentID); db.AddInParameter(dbCommand, "@Name", CommonUtil.GetDbType("Child", "Name"), Name); return db.ExecuteNonQuery(dbCommand); } #endregion -----------删除-----------#region -----------删除----------- public int Delete(Coree.Model.Child model) { return Delete(model, null); } internal int Delete(Coree.Model.Child model,Transaction t) { int count = 0; if (t == null) { using (t = new Transaction("Test")) { try { DbHelper db = new DbHelper("Test"); string sqlQuery = "delete from Child where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), model.ChildID); count = db.ExecuteNonQuery(dbCommand, t); t.Commit(); } catch (Exception ex) { t.RollBack(); throw; } } } else { DbHelper db = new DbHelper("Test"); string sqlQuery = "delete from Child where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), model.ChildID); count = db.ExecuteNonQuery(dbCommand, t); } return count; } public int Delete(object ChildID) { return Delete(ChildID, null); } internal int Delete(object ChildID, Transaction t) { int count = 0; if (t == null) { using (t = new Transaction("Test")) { try { DbHelper db = new DbHelper("Test"); string sqlQuery = "delete from Child where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), ChildID); count = db.ExecuteNonQuery(dbCommand, t); t.Commit(); } catch (Exception ex) { t.RollBack(); throw; } } } else { DbHelper db = new DbHelper("Test"); string sqlQuery = "delete from Child where ChildID=@ChildID"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", CommonUtil.GetDbType("Child", "ChildID"), ChildID); count = db.ExecuteNonQuery(dbCommand, t); } return count; } public int Delete(IList ChildID) { return Delete(ChildID, null); } internal int Delete(IList ChildID, Transaction t) { int count = 0; if (t == null) { using (t = new Transaction("Test")) { try { StringBuilder sb = new StringBuilder(); for (int i = 0; i < ChildID.Count; i++) { sb.Append(ChildID[i].ToString()).Append(","); } string inStr = sb.ToString().Remove(sb.ToString().Length - 1); DbHelper db = new DbHelper("Test"); string sqlQuery = "declare @sql nvarchar(1000) set @sql='delete from Child where ChildID in ('+@ChildID+')' exec (@sql)"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", DbType.String, inStr); count = db.ExecuteNonQuery(dbCommand, t); t.Commit(); } catch (Exception ex) { t.RollBack(); throw; } } } else { StringBuilder sb = new StringBuilder(); for (int i = 0; i < ChildID.Count; i++) { sb.Append(ChildID[i].ToString()).Append(","); } string inStr = sb.ToString().Remove(sb.ToString().Length - 1); DbHelper db = new DbHelper("Test"); string sqlQuery = "declare @sql nvarchar(1000) set @sql='delete from Child where ChildID in ('+@ChildID+')' exec (@sql)"; DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery); db.AddInParameter(dbCommand, "@ChildID", DbType.String, inStr); count = db.ExecuteNonQuery(dbCommand, t); } return count; } #endregion }}DbTypeStructs.cs：
用来映射数据库字段的数据类型，在分析sql语句，添加参数的时候用到。
namespace Coree.DAL{ -----------Child-----------#region -----------Child----------- public partial struct Struct_child { public static System.Data.DbType childid = DbType.Int32; public static System.Data.DbType parentid = DbType.Guid; public static System.Data.DbType name = DbType.String; } #endregion -----------Parent-----------#region -----------Parent----------- public partial struct Struct_parent { public static System.Data.DbType parentid = DbType.Guid; public static System.Data.DbType name = DbType.String; } #endregion}CommonUtil.cs：
公共类，里面提供处理查询字符串、添加参数、创建分页查询语句的方法，供各个数据操作类使用。
namespace Coree.DAL{ public class CommonUtil { -----------处理查询字符串-----------#region -----------处理查询字符串----------- public static string ProcessQuery(string query, params object[] parameterValues) { if (String.IsNullOrEmpty(query)) { return " "; } if (!query.Contains("?")) { return " " + query + " "; } string[] querys = query.Split('?'); if (parameterValues == null) { parameterValues = new object[] { null }; } if (querys.Length - 1 == parameterValues.Length) { StringBuilder sb = new StringBuilder(" "); for (int i = 0; i < querys.Length - 1; i++) { string q = querys[i]; if (parameterValues[i] != null) { sb.Append(q + "@param" + i.ToString()); } else if (q.Contains("=")) { sb.Append(q.Replace("=", " is null ")); } else if (q.Contains("<>") || q.Contains("!=")) { sb.Append(q.Replace("<>", " is not null ").Replace("!=", " is not null ")); } else { throw new Exception("下列语句中的谓词不与 null 组合查询：\r\n" + q); } } return sb.Append(querys[querys.Length - 1]).Append(" ").ToString(); } else { throw new Exception("提供的参数与语句中的参数个数不符\r\n" + query); } } #endregion -----------添加参数-----------#region -----------添加参数----------- public static void AddInParameter(DbHelper db, DbCommand dbCommand, string TableName, string query, params object[] parameterValues) { if (parameterValues == null || String.IsNullOrEmpty(query)|| !query .Contains("?")) { return; } StringBuilder sb = new StringBuilder(query.ToLower()); sb = sb.Replace("where", "").Replace("like", "").Replace("%", "").Replace("<", "").Replace(">", "").Replace("=", ""); string[] querys = sb.ToString().Split('?'); for (int i = 0; i < querys.Length - 1; i++) { string q = querys[i]; if (parameterValues[i] != null) { string ColumnName = q.Replace("and", "").Replace("between", "").Trim(); if (String.IsNullOrEmpty(ColumnName) && querys[i - 1].Contains("between")) { ColumnName = querys[i - 1].Replace("and", "").Replace("between", "").Trim(); } db.AddInParameter(dbCommand, "@param" + i.ToString(), GetDbType(TableName, ColumnName), parameterValues[i]); } } } #endregion -----------由列名获得列类型-----------#region -----------由列名获得列类型----------- public static System.Data.DbType GetDbType(string TableName, string ColumnName) { ColumnName = ColumnName.ToLower(); if (Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()) == null) { throw new Exception("找不到表：" + TableName); } else if (Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()).GetField(ColumnName.ToLower()) == null) { throw new Exception("找不到表：" + TableName + " 中的字段：" + ColumnName); } else { return (System.Data.DbType)Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()).GetField(ColumnName.ToLower()).GetValue(0); } } #endregion -----------创建分页查询语句----------#region -----------创建分页查询语句---------- public static string BuildPagerQuery(string TableName, string PK, string PKType, int StartIndex, int PageSize, string query, params object[] parameterValues) { query = ProcessQuery(query, parameterValues); StringBuilder sb = new StringBuilder(); sb.Append("set nocount on "); sb.Append("declare @indextable table(ttid int identity(1,1),nid " + PKType + ") "); sb.Append("declare @PageUpperBound int "); sb.Append("set @PageUpperBound=@StartIndex+@PageSize-1 "); sb.Append("set rowcount @PageUpperBound "); sb.Append("insert into @indextable(nid) select " + PK + " from " + TableName + query + " "); sb.Append("select * from " + TableName + " a "); sb.Append("inner join @indextable t on t.nid=a." + PK + " "); sb.Append("where t.ttid between @StartIndex and @PageUpperBound order by t.ttid "); sb.Append("set nocount off "); return sb.ToString(); } #endregion }}DbHelper.cs:
顾名思义，就是访问数据库的帮助类，我前面的文章中也提到过。
就到此吧，有什么问题可以来问我，模板中有设计不当的地方还请多多指教！！或者技术上的东西我们也可以切磋切磋。
声明：
1、如果你把这个代码生成模板用到你的工程当中，遇到什么麻烦或引起什么损失的话，本人概不负责，我也没有义务为你提供技术支持。
2、这是一个自由的东东，大家可以任意修改和使用，如果大家有什么好的想法或建议也记得通知我哦，呵呵！
下载
