1 引言
目前大多数项目或产品都使用关系型数据库实现业务数据的存储,这样在开发过程中,常常有一些业务逻辑需要直接用写sql语句实现,但这样开发的结果是:遍地布满sql语句。这些藕合较高的sql语句给系统的改造和升级带来很多无法预计的障碍。或者说可以使用服务端存储子程序实现,但只是将这种耦合搬迁到后端,问题依然没有根本解决,且服务端驻留过多的存储子程序也影响服务器的性能并给多人合作维护和更新部署带来许多障碍。为了提高项目的灵活性,特别是快速开发,ORM是一个不错的选择。举个简单的例子:在使用ORM的系统中,当数据库模型改变时,不再需要理会逻辑代码和sql语句中涉及到该模型的所有改动,只需要将该模型映射的对象稍作改动,甚至不做改动就可以满足要求。
ORM的全称是Object Relational Mapping,即对象关系映射。它的实质就是将关系数据(库)中的业务数据用对象的形式表示出来,并通过面向对象(Object-Oriented)的方式将这些对象组织起来,实现系统业务逻辑的过程。在ORM过程中最重要的概念是映射(Mapping),通过这种映射可以使业务对象与数据库分离。从面向对象来说,数据库不应该和业务逻辑绑定到一起,ORM则起到这样的分离作用,使数据库层透明,开发人员真正的面向对象。下图简单说明了ORM在多层系统架构中的这个作用。
图1 ORM在多层系统架构中的作用
当然ORM并非是万能的,面对纷繁复杂的业务逻辑,当遇到特别复杂的数据处理及海量数据处理和弥补拙劣的设计不足时还应归结到sql或存储过程来实现,但它却很好的体现了“80/20(或90/10)法则”(也被称为“帕累托法则”),也就是说:花比较少(10%-20%)的力气就可以解决大部分(80%-90%)的问题,这样通过利用ORM框架,我们就仅需要付出极少数时间和精力来解决剩下的少部分问题了,这无疑缩短了整个项目开发的周期。因此快速开发、面向对象和性能优化等必须灵活兼顾才好,这些该工具都提供了很好解决方案,下文分别作一介绍。
2 内容
2.1 ORM的工具实现:VB/C#.Net实体代码生成工具(EntitysCodeGenerate)
好的ORM工具不仅可以帮助我们很好的理解对象及对象的关系,而且工具本身会帮助我们维护这些关系,并且帮助我们记住字段属性业务含义及提供辅助的应用等。基于这个理念,我于多年的项目实践和业余时间设计开发了一个基于.NET的ORM工具——VB/C#.Net实体代码生成工具(EntitysCodeGenerate),该工具运行于dotnetframework2.0框架上,希望多多交流并指正。
VB/C#.Net实体代码生成工具(EntitysCodeGenerate) 为ORM提供对象持久、简单对象查询、事务处理等功能。数据持久包括一些对象的Insert、Update、Save、Delete、Select等功能,简单对象查询则提供一些基于对象的简单对象查询GetEntity及构造函数获取对象信息等。该工具是基于VS.NET 2005的开发的应用程序,职责是从数据库中提取生成实体类代码并帮助开发人员快速映射关系数据库中的业务模型的映射实体类,目前直接提供从Oracle、sqlServer、Access、MysqL、Sybase、sqlite数据库生成C#代码的支持,可以生成实体及实体集的相关文件,并自动提取数据库表和字段的注释说明和对应的数据类型等。
另外所生成的代码文件只需修改数据库连接,即可用于目前市场上ado.net支持的各种类型的数据库,如Oracle、sqlServer、MysqL、Access、Sybase、sqlite、Excel、DB2等。所生成代码文件的类关系图如下所示:
工具3.x版本之后的实体层代码有个基类(BaseEntity),基类里其实也正是你项目的数据连接的配置位置,该工具的实际应用时配置其实也就在这里,默认为生成代码时所填写的数据库连接信息,并可手工扩展修改(如从config配置文件读取、实现对用户数据库连接信息的加密/解密等;大多数时候我们只需在GetConnectionString()修改数据连接字符串即可):
public class BaseEntity { public static string GetConnectionString() { return "User ID=scott;Password=tiger;Data Source=85";//数据库连接设置可修改从别处读取 } public static DatabaseType GetDatabaseType() { return DatabaseType.Oracle; //数据库连接类型设置也可修改从别处读取 } …… } |
namespace System.Database { { public int ExecuteNonQuery(DBCommandWrapper command){…} public DataSet ExecuteDataSet(DBCommandWrapper command){…} …… } |
public abstract class DBCommandWrapper {……} |
internal abstract class Database{……} |
internal sealed class DatabaseFactory{……} |
…… |
internal class OracleDatabase : Database{…} |
public class OracleCommandWrapper : DBCommandWrapper{…} |
public int Insert() { ORMap<BaseEntity> ormap = new ORMap<BaseEntity>(this); DEPT entity = new DEPT(); return ormap.Insert(entity); } …… public static System.Data.DbType GetDBTypeByFullName(string strTypeFullName) { switch (strTypeFullName) { case "System.Byte": return System.Data.DbType.Byte; case "System.Boolean": return System.Data.DbType.Boolean; …… } } |
[Serializable(),Description("Primary:DEPTNO")] public class DEPT:BaseEntity { …… /// <summary> /// 主键 /// </summary> [DataObjectField(true)] public int DEPTNO { set{ _deptno=value;} get{return _deptno;} } /// <summary> /// /// </summary> [DataObjectField(false)] public string DNAME { set{ _dname=value;} get{return _dname;} } …… } |
DEPT entity = new DEPT(); |
entity.DEPTNO = 50; entity = entity.GetEntity(); |
DEPT entity = new DEPT(50); //通过主键字段条件获取实体对象 DEPT entity = new DEPT(new string[] { "DEPTNO" },new object[] { 50 }); |
DEPTS entity = new DEPTS(true); //获取所有信息 EMPS entitys1 = new EMPS(new string[] { "DEPTNO" },new object[] { 50 }); |
entity.DEPTNO = 51; entity.DNAME = “DNAME1”; entity.LOC = “LOC1”; entity.Insert(); |
entity.DEPTNO = 51; entity.DNAME = “DNAME2”; entity.LOC = “LOC2”; entity.Update(); |
entity.DEPTNO = 51; entity.DNAME = “DNAME3”; entity.LOC = “LOC3”; entity.Save(); |
entity.DEPTNO = 51; entity.Delete(); |
int intID = entity.GetInt32MaxID(); |
Entitys.Common.LC_WORKTYPE entity = new Entitys.Common.LC_WORKTYPE(); entity.ID = 1; entity.TYPENAME = "TYPENAME"; string strConnection = "Password=newaqfx;User ID= newaqfx;Data Source=85"; DbCore dbCore = new DbCore(DatabaseType.Oracle,strConnection); dbCore.Open(); dbCore.BeginTransaction(); dbCore.Save(new Entitys.Common.LC_WORKTYPE(),entity); entity.DESCRIPTION = "类型描述"; dbCore.Save(new Entitys.Common.LC_WORKTYPE(),entity); entity.TYPENAME = "作业类型"; dbCore.Save(new Entitys.Common.LC_WORKTYPE(),entity); DataSet dst = dbCore.ExecuteDataSet("select * from lc_worktype");
entity.ID = 1; DataTable dt = dbCore.GetDataTableByEntityKey(entity); int intRecord = dbCore.Delete(entity); dt = dbCore.GetDataTableByEntityKey(entity);
dbCore.CommitTransaction(); dbCore.Close(); |
DbCore dbCore = null; try { EMP entity1 = new EMP(); DataSet dst = new DataSet(); entity1.EMPNO = 7369; //设置主键EMPNO为 entity1 = entity1.GetEntity(); //取得主键EMPNO为实体对象信息 //"User ID=scott;Password=tiger;Data Source=85"; dbCore = new DbCore(Entitys.Common.BaseEntity.GetConnectionString()); dbCore.Open(); dbCore.BeginTransaction(); //选择当前事务下的所有雇员EMP的信息 dst = dbCore.SelectAll().From(entity1).ExecuteDataSet(); dbCore.Delete(entity1);//删除主键EMPNO为7369的记录 dst = dbCore.SelectAll().From(entity1).ExecuteDataSet();//查看当前事务下记录,当前删除记录将不在此显示 dbCore.Insert(new EMP(),entity1);//插入刚才删除主键EMPNO为7369的记录=dbCore.Save(new EMP(),entity1); dst = dbCore.SelectAll().From(entity1).ExecuteDataSet();//查看当前事务下记录,可见刚刚插入的新记录 entity1.SAL = entity1.SAL + 100;//薪水加100 dbCore.Update(new EMP(),entity1);//更新=dbCore.Save(new EMP(),entity1); dst = dbCore.SelectAll().From(entity1).ExecuteDataSet();//查看当前事务下记录,对应薪水SAL已更新 entity1.SAL = entity1.SAL - 100;// 薪水减100 dbCore.Update(new EMP(),entity1); dst = dbCore.SelectAll().From(entity1).ExecuteDataSet();//查看当前事务下记录,对应薪水SAL已更新 dbCore.CommitTransaction(); dbCore.Close(); } catch (Exception ex) { if (dbCore != null) { if (dbCore.IsTransaction) { dbCore.RollbackTransaction();//如果已经开始事务,则回滚事务 } dbCore.Close(); } } |
DbCore dbCore = new DbCore(DatabaseType.Oracle,“OracleConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.sqlServer,“sqlServerConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.MysqL,“MysqLConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.Sybase,“SybaseConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.DB2,“DB2ConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.sqlite,“sqliteConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.Postgresql,“PostgresqlConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.Informix,“InformixConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.OleDb,“OleDbConnectionString”); |
DbCore dbCore = new DbCore(DatabaseType.Odbc,“OdbcConnectionString”); |
DbCore dbCore = PublicClass.GetNewDbCore(); string strParaToken = dbCore.GetCurrentParameterToken; string strsql = "INSERT INTO dept (deptno,dname,loc) VALUES (" + strParaToken + "deptno," + strParaToken + "dname," + strParaToken + "loc)"; dbCore.Open(); //打开数据库连接 dbCore.BeginTransaction(); //开始事务
DBCommandWrapper cmd = dbCore.GetsqlStringCommandWrapper(strsql); //cmd.AddParameter(..);//为命令增加一个参数实例 cmd.AddInParameter(strParaToken + "deptno",DbType.Int32,99); cmd.AddInParameter(strParaToken + "dname",DbType.String,"部门名称"); cmd.AddInParameter(strParaToken + "loc",DbType.AnsiString,"locTest");
int intMaxDeptId = dbCore.GetInt32MaxId("dept","deptno");//当前表的deptno最大值
dbCore.ExecuteNonQuery(cmd); intMaxDeptId = dbCore.GetInt32MaxId("dept","deptno");//插入数据deptno=99之后当前表的deptno最大值
strsql = "DELETE dept WHERE deptno = " + strParaToken + "deptno"; DBCommandWrapper cmd1 = dbCore.GetsqlStringCommandWrapper(strsql); cmd1.AddInParameter(strParaToken + "deptno",99); dbCore.ExecuteNonQuery(cmd1); intMaxDeptId = dbCore.GetInt32MaxId("dept","deptno");//删除数据deptno=99之后当前表的deptno最大值
dbCore.RollbackTransaction();//回滚撤销事务。等于该方法什么都没做,只是演示作用
intMaxDeptId = dbCore.GetInt32MaxId("dept","deptno");
|
OracleCommandWrapper cmd = dbCore.GetsqlStringCommandWrapper(strsql) as OracleCommandWrapper; |
sqlCommandWrapper cmd = dbCore.GetsqlStringCommandWrapper(strsql) as sqlCommandWrapper; |
…… |
catch (Exception ex) { if (dbCore != null) { if (dbCore.IsTransaction) { dbCore.RollbackTransaction();//如果已经开始事务,则回滚事务 } dbCore.Close(); } MessageBox.Show(ex.Message); } |
DataSet dst = new DataSet(); |
DataSet dst = dbCore.SelectAll().From("sales").ExecuteDataSet(); |
DataSet dst = dbCore.SelectAll().From(SALESColumn.TableName).ExecuteDataSet(); |
DataSet dst = |
DataSet dst = |
DataSet dst = dbCore.SelectAll().From(new SALES()).ExecuteDataSet(); |
DataSet dst = new DataSet();DataSet dst1 = new DataSet(); |
dst = dbCore.SelectAll().From().JoinInner("sales","stor_id ","stores","stor_id") |
dst = dbCore.SelectAll().From().JoinInner(SALESColumn.stor_id,STORESColumn.stor_id) |
dst = dbCore.SelectAll().From().JoinLeft("sales","stor_id") |
dst = dbCore.SelectAll().From().JoinLeft(SALESColumn.stor_id,STORESColumn.stor_id) |
dst = dbCore.SelectAll().From().JoinRight("sales","stor_id") |
dst = dbCore.SelectAll().From().JoinRight(SALESColumn.stor_id,STORESColumn.stor_id) |
dst = dbCore.SelectAll().From().JoinFull("sales","stor_id") |
dst = dbCore.SelectAll().From().JoinFull(SALESColumn.stor_id,STORESColumn.stor_id) |
DataSet dst = new DataSet(); |
dst1 = dbCore.SelectAll().From().JoinInner(SALESColumn.stor_id,STORESColumn.stor_id) |
|
dst = dbCore.SelectAll().From().JoinInner("sales","stor_id") |
dst1 = dbCore.SelectAll().From().JoinInner(SALESColumn.stor_id,STORESColumn.stor_id) |
|
dst = dbCore.SelectAll().From("sales").FromTable("stores") .Where().ConditionColumnAndEqual("sales","stor_id"). |
dst1 = dbCore.SelectAll().From(SALESColumn.TableName).FromTable(STORESColumn.TableName) |
DataSet dst = new DataSet(); |
dst = dbCore.SelectAll().From(entity) |
dst = dbCore.SelectAll().From(entity) |
dst = dbCore.SelectAll().From(entity) |
dst1 = dbCore.SelectAll().From(EMPColumn.TableName) |
DataSet dst = new DataSet(); |
dst = dbCore.Select().SelectColumnMaxValue(EMPColumn.EMPNO) |
dst1 = dbCore.Select().AddMax(EMPColumn.EMPNO) |
|
dst = dbCore.Select().SelectColumn(EMPColumn.DEPTNO).SelectColumn(EMPColumn.SAL) .GroupBy(EMPColumn.DEPTNO).Column(EMPColumn.SAL) |
dst1 = dbCore.Select().Add(EMPColumn.DEPTNO).Add(EMPColumn.SAL) .AddMax(EMPColumn.EMPNO).AddMin(EMPColumn.EMPNO).AddAvg(EMPColumn.EMPNO) |
|
dst = dbCore.Select().SelectColumn(EMPColumn.DEPTNO).SelectColumn(EMPColumn.SAL) |
dst1 = dbCore.Select().Add(EMPColumn.DEPTNO).Add(EMPColumn.SAL) |
|
dst = dbCore.Select().SelectColumn(EMPColumn.DEPTNO).SelectColumn(EMPColumn.SAL) |
dst = dbCore.Select().SelectColumn(EMPColumn.DEPTNO).SelectColumn(EMPColumn.SAL) |
dst = dbCore.Select().SelectColumn(EMPColumn.DEPTNO).SelectColumn(EMPColumn.SAL) |
SELECT emp.deptno,emp.sal, |
DataSet dst = new DataSet(); try { #region 使用事务 //--打开数据库连接 dbCore.Open(); dbCore.BeginTransaction();//开始事务 int intRecordCount = dbCore.DeleteFrom(EMPColumn.TableName).ExecuteNonQuery(); dst = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet(); dbCore.RollbackTransaction();//回滚撤销事务 dst1 = dbCore.SelectAll().From(new EMP()).ExecuteDataSet(); dbCore.Close(); #endregion } catch (Exception ex) { if (dbCore != null) { dbCore.Close(); } MessageBox.Show(ex.Message); } |
try { dst = dbCore.SelectAll().From(new EMP()).ExecuteDataSet(); //--打开数据库连接,开始使用事务 dbCore.BeginTransaction(); EMP entity = new EMP(); entity.EMPNO = 7782; intRecordCount = dbCore.Delete(entity); dst = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet(); intRecordCount = dbCore.DeleteFrom(EMPColumn.TableName) dst = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet(); intRecordCount = dbCore.DeleteFrom(new EMP()).ExecuteNonQuery(); dst = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet(); dbCore.RollbackTransaction(); dst = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet(); dbCore.Close(); } catch (Exception ex) { if (dbCore != null) dbCore.Close(); MessageBox.Show(ex.Message); } |
DbCore dbCore = null; try { dbCore = PublicClass.GetNewDbCore(); dbCore.Open(); dbCore.BeginTransaction(); EMP entity = new EMP(); entity.EMPNO = 7499; entity = entity.GetEntity(dbCore); dbCore.Update(EMPColumn.TableName).Set(EMPColumn.SAL,entity.SAL + 100) .Set(EMPColumn.COMM,entity.COMM + 100) .Set(EMPColumn.HIREDATE,DateTime.Today) .Where().ConditionAndEqual(EMPColumn.EMPNO,7499).ExecuteNonQuery(); DataSet dst = dbCore.SelectAll().From(EMPColumn.TableName) .Where().ConditionAndEqual(EMPColumn.EMPNO,7499).ExecuteDataSet();//查询 dbCore.Update(EMPColumn.TableName).Set(EMPColumn.SAL,entity.SAL) .Set(EMPColumn.COMM,entity.COMM) .Set(EMPColumn.HIREDATE,entity.HIREDATE) .Where().ConditionAndEqual(EMPColumn.EMPNO,7499).ExecuteNonQuery();//恢复原值 dst = dbCore.SelectAll().From(EMPColumn.TableName) .Where().ConditionAndEqual(EMPColumn.EMPNO,7499).ExecuteDataSet();//查询 dbCore.CommitTransaction();//提交事务 dbCore.Close(); } catch (Exception ex) { if (dbCore != null) { if (dbCore.IsTransaction) { dbCore.RollbackTransaction();//如果已经开始事务,则回滚事务 } dbCore.Close(); } throw ex; } |
DbCore dbCore = PublicClass.GetNewDbCore(); DataTable dt1 = dbCore.SelectAll().From(EMPColumn.TableName).ExecuteDataSet().Tables[0]; DataTable dt2 = dbCore.SelectAll().From(DEPTColumn.TableName).ExecuteDataSet().Tables[0]; DataTable dt3 = TableHelp.MergeTable(dt1,dt2,"DEPTNO");//按部门编号DEPTNO列将表dt2的数据合并到dt1 DataTable dt3_ = TableHelp.MergeTable(dt2,dt1,"DEPTNO");//按部门编号DEPTNO列将表dt1的数据合并到dt2,dt1中有多行数据对应,取首行的数据,没有对应的数据为空 DataTable dt4 = TableHelp.AddTableRowNumCol(dt3); //给dt3添加行号 DataTable dt5 = TableHelp.GetTableTopRows(dt4,5); //获取前5行 DataTable dt6 = TableHelp.GetTableSubRows(dt4,6,10); //获取dt4从第6行到第10行 DataTable dt7 = TableHelp.GetTableSubRows(dt4,11,20); //获取dt4从第11行到第20行,注:无20行取到最后一行 DataTable dt8 = TableHelp.GetTableBottomRows(dt4,5); //获取dt4后5行 dt8 = TableHelp.GetTableBottomRows(dt4,50); //获取dt4后50行;dt4没有后50行,从后面往前取到最前面存在行 DataTable dt9 = TableHelp.JoinInner(dt1,"DEPTNO"); //内连接 DataTable dt10 = TableHelp.JoinInner(dt1,"deptno"); //内连接 DataTable dt11 = TableHelp.JoinLeft(dt1,"DEPTNO"); //左外连接 DataTable dt12 = TableHelp.JoinRight(dt1,"DEPTNO"); //右外连接 DataTable dt13 = TableHelp.JoinLeft(dt2,"DEPTNO"); //左外连接 DataTable dt14 = TableHelp.JoinFull(dt1,"DEPTNO"); //完全外连接
DataTable dt15 = TableHelp.SortTable(dt1,"deptno",SortDirection.Asc); DataTable dt16 = TableHelp.SortTable(dt1,"deptno"); DataTable dt17 = TableHelp.SortTable(dt1,SortDirection.Asc,"sal",SortDirection.Asc); DataTable dt18 = TableHelp.SortTable(dt1,"sal"); DataTable dt19 = TableHelp.SortTable(dt1,SortDirection.Desc,SortDirection.Desc); DataTable dt20 = TableHelp.SortTableDesc(dt1,"sal"); DataTable dt21 = TableHelp.SortTable(dt1,SortDirection.Desc);
TableHelp.DataTableToExcel(dt1,@"C:/Documents and Settings/楚涛/桌面/temp1.xls"); DataSet dst = new DataSet(); DataTable dt22 = dt1.Copy(); //修改表名,DataTable默认TableName="Table",DataSet集合的DataTable.TableName不能同名 dt22.TableName = "EMP"; DataTable dt23 = dt2.Copy(); //修改表名,DataSet集合的DataTable.TableName不能同名 dt23.TableName = "DEPT"; dst.Tables.Add(dt22); dst.Tables.Add(dt23); TableHelp.DataSetToExcel(dst,@"C:/Documents and Settings/楚涛/桌面/temp2.xls"); |
DataTable dt24 = TableHelp.GetTableSelect(dt1,"deptno=10");//选取deptno=10的所有信息,并以DataTable的形式返回 DataTable dt25 = TableHelp.GetTableSelect(dt1,"deptno=20");//选取deptno=20的所有信息,并以DataTable的形式返回 DataTable dt26 = TableHelp.TableAppend(dt24,dt25);//将dt23数据按行附加到dt22,并以新的结果数据表的形式返回 string[,] strArray = new string[1,2]; strArray[0,0] = "SCOTT"; strArray[0,1] = "scott/tiger"; DataTable dt27 = TableHelp.ReplacleTableColValue(dt26,"ename",strArray); |
string str1 = CommonHelp.NumberToRMB(1); //"壹元整" str1 = CommonHelp.NumberToRMB(102); //"壹佰零贰元整" str1 = CommonHelp.NumberToRMB(1000234); //"壹佰万零贰佰叁拾肆元整" str1 = CommonHelp.NumberToRMB(1000023456); //"壹拾亿零贰万叁仟肆佰伍拾陆元整" str1 = CommonHelp.NumberToRMB(100000234567); //"壹仟亿零贰拾叁万肆仟伍佰陆拾柒元整" decimal dec = 1234007890123.45M; str1 = CommonHelp.NumberToRMB(dec); //"壹万贰仟叁佰肆拾亿零柒佰捌拾玖万零壹佰贰拾叁元肆角伍分" string str = string.Empty; for (int i = 0; i < 1000; i++) { str += CommonHelp.GetOnlyRandomID() + "/r/n";//唯一随机数字固定长度为20的数字字符串 } MessageBox.Show(str); string str1 = "abcdEFGH"; bool isHasChinese = CommonHelp.IsHasChineseWord(str1); //false不含有中文字符 str1 = "abcd啊EFGH"; isHasChinese = CommonHelp.IsHasChineseWord(str1); //true含有中文字符 string str2 = "<input type=/"button/" value=/"button/"><input type=/"image/" >"; string str3 = CommonHelp.HTML_CodeToString(str2);//Html代码和对应格式化的字符串的相互转化 string str4 = CommonHelp.StringToHTML_Code(str2);//Html代码和对应格式化的字符串的相互转化 string str5 = CommonHelp.HTML_CodeToString(str3);//Html代码和对应格式化的字符串的相互转化 |