经过一段时间的努力,终于把.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。
![](https://www.cnblogs.com/images/cnblogs_com/leitwolf/11.jpg)
运行,界面如下:
![](https://www.cnblogs.com/images/cnblogs_com/leitwolf/12.jpg)
其中的Save按钮是用来把窗口内的东西保存到注册表中的,下次打开的时候就不用再修改了。其它的都是一眼就能看得懂的,这里不再讲述。
5、填写好参数后,点击OK按钮,生成代码。
![](https://www.cnblogs.com/images/cnblogs_com/leitwolf/13.jpg)
注:目前只支持sql server 2000,因为我对其它的数据库不熟。
生成的代码:
生成的代码默认为Model和DAL两个文件夹:
Model是用来放实体类文件的,而DAL是用来放操作类文件及数据库操作基础类和公共类。
实体类文件:
由私有变量、默认构造函数、属性三部分组成。其中属性支持外键的功能,当然了,只有1:N中的主表,如果也包含从表的话,就会造成资源的浪费和性能的下降。
namespace Coree.Model
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
public class Child
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
私有成员#region 私有成员
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private int _ChildID;
private Guid _ParentID;
private string _Name;
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
默认构造函数#region 默认构造函数
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public Child()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
_ChildID = 0;
_ParentID = Guid.Empty;
_Name = String.Empty;
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
公有属性#region 公有属性
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 主键
/// </summary>
public int ChildID
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return _ChildID; }
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{ _ChildID = value; }
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 父类
/// </summary>
public Guid ParentID
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return _ParentID; }
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{ _ParentID = value; }
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 姓名
/// </summary>
public string Name
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return _Name; }
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
set
{ _Name = value; }
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 父类
/// </summary>
public Parent Parent
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if(_ParentID != Guid.Empty)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return (new Coree.DAL.Parent()).GetModel(_ParentID);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return null;
}
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#endregion
}
}
数据操作类文件:
由一些常用的方法组成,有Exist,GetCount,GetModel,GetByID,GetList,Add,Update,Delete等,这些方法都有重载的方法。其中GetList返回的是DataSet,Delete方法用到了事务,具体可看生成的代码。
namespace Coree.DAL
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
public class Child
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------Exist-----------#region -----------Exist-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public bool Exist()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Exist("", null);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param>
public bool Exist(string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------数据统计-----------#region -----------数据统计-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int GetCount()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return GetCount("", null);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param>
public int GetCount(string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------得到一个对象实体---------#region -----------得到一个对象实体---------
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 得到一个对象实体
/// </summary>
public Coree.Model.Child GetModel(object ChildID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataSet ds = new DataSet();
ds = GetByID(ChildID);
Coree.Model.Child model = new Coree.Model.Child();
if (ds.Tables.Count != 0)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------GetByID-----------#region -----------GetByID-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// GetByID
/// </summary>
public DataSet GetByID(object ChildID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return GetList("where ChildID=?", ChildID);
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------数据(DataSet)------------#region -----------数据(DataSet)------------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet GetList()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return GetList("");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param>
public DataSet GetList(string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public DataSet GetList(int StartIndex, int PageSize)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return GetList(StartIndex, PageSize, "");
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <param name="query">格式如: where Name=? and Pwd=? order by Name desc</param>
public DataSet GetList(int StartIndex, int PageSize, string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------添加-----------#region -----------添加-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Add(Coree.Model.Child model)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!new Parent().Exist("where ParentID=?", model.ParentID))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Add(Guid ParentID,string Name)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!new Parent().Exist("where ParentID=?", ParentID))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------更新-----------#region -----------更新-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Update(Coree.Model.Child model)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!new Parent().Exist("where ParentID=?", model.ParentID))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Update(int ChildID,Guid ParentID,string Name)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (!new Parent().Exist("where ParentID=?", ParentID))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------删除-----------#region -----------删除-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Delete(Coree.Model.Child model)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Delete(model, null);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
internal int Delete(Coree.Model.Child model,Transaction t)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int count = 0;
if (t == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
using (t = new Transaction("Test"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
t.RollBack();
throw;
}
}
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Delete(object ChildID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Delete(ChildID, null);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
internal int Delete(object ChildID, Transaction t)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int count = 0;
if (t == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
using (t = new Transaction("Test"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
t.RollBack();
throw;
}
}
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public int Delete(IList ChildID)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return Delete(ChildID, null);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
internal int Delete(IList ChildID, Transaction t)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int count = 0;
if (t == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
using (t = new Transaction("Test"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < ChildID.Count; i++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
t.RollBack();
throw;
}
}
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < ChildID.Count; i++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------Child-----------#region -----------Child-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public partial struct Struct_child
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------Parent-----------#region -----------Parent-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public partial struct Struct_parent
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
public static System.Data.DbType parentid = DbType.Guid;
public static System.Data.DbType name = DbType.String;
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
CommonUtil.cs:
公共类,里面提供处理查询字符串、添加参数、创建分页查询语句的方法,供各个数据操作类使用。
namespace Coree.DAL
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
public class CommonUtil
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------处理查询字符串-----------#region -----------处理查询字符串-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static string ProcessQuery(string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (String.IsNullOrEmpty(query))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return " ";
}
if (!query.Contains("?"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return " " + query + " ";
}
string[] querys = query.Split('?');
if (parameterValues == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
parameterValues = new object[]
{ null };
}
if (querys.Length - 1 == parameterValues.Length)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder sb = new StringBuilder(" ");
for (int i = 0; i < querys.Length - 1; i++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string q = querys[i];
if (parameterValues[i] != null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(q + "@param" + i.ToString());
}
else if (q.Contains("="))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(q.Replace("=", " is null "));
}
else if (q.Contains("<>") || q.Contains("!="))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(q.Replace("<>", " is not null ").Replace("!=", " is not null "));
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception("下列语句中的谓词不与 null 组合查询:\r\n" + q);
}
}
return sb.Append(querys[querys.Length - 1]).Append(" ").ToString();
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception("提供的参数与语句中的参数个数不符\r\n" + query);
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------添加参数-----------#region -----------添加参数-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static void AddInParameter(DbHelper db, DbCommand dbCommand, string TableName, string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (parameterValues == null || String.IsNullOrEmpty(query)|| !query .Contains("?"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string q = querys[i];
if (parameterValues[i] != null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string ColumnName = q.Replace("and", "").Replace("between", "").Trim();
if (String.IsNullOrEmpty(ColumnName) && querys[i - 1].Contains("between"))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ColumnName = querys[i - 1].Replace("and", "").Replace("between", "").Trim();
}
db.AddInParameter(dbCommand, "@param" + i.ToString(), GetDbType(TableName, ColumnName), parameterValues[i]);
}
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------由列名获得列类型-----------#region -----------由列名获得列类型-----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static System.Data.DbType GetDbType(string TableName, string ColumnName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ColumnName = ColumnName.ToLower();
if (Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()) == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception("找不到表:" + TableName);
}
else if (Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()).GetField(ColumnName.ToLower()) == null)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw new Exception("找不到表:" + TableName + " 中的字段:" + ColumnName);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return (System.Data.DbType)Type.GetType("Coree.DAL.Struct_" + TableName.ToLower()).GetField(ColumnName.ToLower()).GetValue(0);
}
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
-----------创建分页查询语句----------#region -----------创建分页查询语句----------
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static string BuildPagerQuery(string TableName, string PK, string PKType, int StartIndex, int PageSize, string query, params object[] parameterValues)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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、这是一个自由的东东,大家可以任意修改和使用,如果大家有什么好的想法或建议也记得通知我哦,呵呵!
下载