DOS.ORM教程、学习笔记

为什么选择Dos.ORM（原Hxj.Data）?

[原地址](http://www.cnblogs.com/BookCode/p/5292859.html) 上手简单，0学习成本。使用方便，按照sql书写习惯编写C#.NET代码。功能强大 高性能（与Dapper媲美，接近手写Sql） 体积小（不到150kb，仅一个dll） 完美支持Sql Server(2000至最新版),MySql,Oracle,Access,Sqlite等数据库 支持大量Lambda表达式写法，国产ORM支持度最高，开源中国ORM排行前三 不需要像NHibernate的XML配置，不需要像EF的各种数据库连接驱动 众多成熟企业软件、互联网项目已应用此框架 遵循MIT开源协议，除不允许改名，其它随意定制修改 Dos团队持续更新升级，任何Bug反馈都会立即得到解决

1.首先·在 App.config文件中配置连接数据库字符串。或者在程序中指定

<connectionStrings> <add name="School" connectionString="Data Source=.;Initial Catalog=School;User ID=sa;Pwd=123;"></add> </connectionStrings>

直接上示例代码不废话

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Hxj.Data; using Hxj.Data.Sqlite; using System.Data; namespace cn.School { class Test { static void Main(string[] args) { // <connectionStrings> //<add name="School" connectionString="Data Source=.;Initial Catalog=School;User ID=sa;Pwd=123;"></add> //</connectionStrings> //不同的数据库可构造不同的DbSession DbSession(connectionStrings节点的name) //DbSession dbs = new DbSession("School"); DbSession dbs2 = new DbSession(DatabaseType.SqlServer, "Data Source=.;Initial Catalog=School;User ID=sa;Pwd=123;"); //TestSelDB(); //addTestDB(); //Updata(); //DelData(); //sqlFrom(); assistmethod(); } /// <summary> /// 查询操作 /// </summary> public static void TestSelDB() { //查询Student表中第一条数据并返回实体，代码如下。 Student st = DbSession.Default.From<Student>() //.Select(Products._.ProductID) //查询返回ProductID字段 //.GroupBy(Products._.CategoryID.GroupBy && Products._.ProductName.GroupBy)//按照CategoryID，ProductName分组 //.InnerJoin<Suppliers>(Suppliers._.SupplierID == Products._.SupplierID)//关联Suppliers表 --CrossJoin FullJoin LeftJoin RightJoin 同理 //.OrderBy(Products._.ProductID.Asc)//按照ProductID正序排序 //.Where((Products._.ProductName.Contain("apple") && Products._.UnitPrice > 1) || Products._.CategoryID == 2)//设置条件 ProductName包含”apple”并 且UnitPrice>1 或者CategoryID =2 //.UnionAll(DbSession.Default.From<Products> ().Select(Products._.ProductID))//union all查询 //.Distinct() // Distinct //.Top(5) //读取前5条 //.Page(10, 2)//分页返回结果 每页10条返回第2页数据 //.ToDataSet(); //返回DataSet //.ToDataReader(); //返回IDataReader //.ToDataTable(); //返回DataTable //.ToScalar(); //返回单个值 .ToFirst(); //分字段查询 DbSession.Default.From<Student>() .Select(Student._.Stu_ID, Student._.Stu_name) .ToDataTable(); //分字段查询取别名 DbSession.Default.From<Student>() .Select(Student._.Stu_ID, Student._.Stu_name.As("pname")) .ToDataTable(); //排序倒叙排列 DataTable dt = DbSession.Default.From<Student> ().OrderBy(Student._.Stu_ID.Desc).ToDataTable(); } /// <summary> /// 模糊查询 /// 子查询 /// in 查询 /// not iN查询 /// </summary> public static void demoSelet() { //Contain完全模糊查询 DbSession.Default.From<Student>().Where(Student._.Stu_ID.Contain(41500)); //查找Stu_ID列中所有以41500开头的。 DbSession.Default.From<Student>().Where(Student._.Stu_ID.BeginWith(41500)); //查找Stu_ID列中所有以41500结尾的。 DbSession.Default.From<Student>().Where(Student._.Stu_ID.EndWith(41500)); //in 查询 DbSession.Default.From<Student>() .Where(Student._.Stu_ID.SelectIn(1, 2, 3)) .ToList(); //not in查询 DbSession.Default.From<Student>() .Where(Student._.Stu_ID.SelectNotIn<int>(1, 2, 3)) .ToList(); //子查询 //SubQueryEqual = //SubQueryNotEqual <> //SubQueryLess < //SubQueryLessOrEqual <= //SubQueryGreater > //SubQueryGreaterOrEqual >= //SubQueryIn in //SubQueryNotIn not in DbSession.Default.From<Student>() .Where(Student._.Stu_ID .SubQueryEqual(DbSession.Default.From<Student>().Where(Student._.Stu_ID == "Produce").Select(Student._.Stu_ID).Top(1))) .ToList(); } /// <summary> /// 联合查询 /// </summary> public static void likeSel() { //InnerJoin inner join //LeftJoin left join //RightJoin right join //CrossJoin cross join //FullJoin full join //Union union //UnionAll union all DbSession.Default.From<Student>() .InnerJoin<Gread>(Student._.gr_id == Gread._.gr_id) .ToDataTable(); //联合查询带条件 DbSession.Default.From<Student>() .LeftJoin<Gread>(Student._.gr_id == Gread._.gr_id) .Where(Student._.gr_id == 1) .ToDataTable(); //这两个是两个结果的合集，union会区分结果排除相同的，union all 则直接合并结果集合。 DbSession.Default.From<Student>().Where(Student._.gr_id == 4522) .UnionAll(DbSession.Default.From<Gread>().Where(Gread._.gr_id == 1)) .ToList(); } /// <summary> /// 增加操作 /// </summary> public static void addTestDB() { //新建一个实体 Student stu = new Student(); stu.Stu_name = "小黑"; stu.stu_phon = "1254555"; stu.stu_Sex = "男"; stu.stu_Age = 25; stu.gr_id = 1; //开启修改 (开启修改后的添加操作将只insert赋值过的字段) stu.Attach(); //返回值 如果有自增长字段，则返回自增长字段的值 int result = DbSession.Default.Insert<Student>(stu); //将插入的数据查询出来 List<Student> listStu = DbSession.Default.From<Student> ().Where(Student._.Stu_ID == result).ToList(); } /// <summary> /// 修改操作 /// </summary> public static void Updata() { //先查询一个Student对象 Student stu = DbSession.Default.From<Student> ().Where(Student._.Stu_ID.Contain(41500)).ToFirst(); //开启修改 (修改操作之前 必须执行此方法) stu.Attach(); stu.Stu_name = "王五"; List<ModifyField> list = stu.GetModifyFields(); //清除修改记录 (清除后更新操作无效) //stu.ClearModifyFields(); //返回0表示更新失败 组件有事务会自动回滚 //返回1表示更新成功 //更新成功返回值就是受影响的条数 int num = DbSession.Default.Update<Student>(stu); //简单的修改方法，修改一个值的时候使用 //int nums = DbSession.Default.Update<Student>(Student._.Stu_name, "九九", Student._.Stu_ID == 41501); //修改多个值的时候 //Dictionary<Field, object> st = new Dictionary<Field, object>(); //st.Add(Student._.stu_Sex, "男"); //st.Add(Student._.Stu_name, "小徐"); //int returnvalue = DbSession.Default.Update<Student>(st, Student._.Stu_ID == 41501); } /// <summary> /// 删除操作 /// </summary> public static void DelData() { int returnValue = DbSession.Default.Delete<Student>(Student._.Stu_ID == 41504); //与上面等效的删除语句 //int returnvalue = DbSession.Default.Delete<Student>(2); //删除一个对象 //Student stu = DbSession.Default.From<Student>().ToFirst(); //int returnvalue = DbSession.Default.Delete<Student>(stu); } /// <summary> /// 使用SQL语句查询 /// </summary> public static void sqlFrom() { //直接使用SQL语句查询 DataTable dt = DbSession.Default.FromSql("select * from Student").ToDataTable(); //参数化SQL语句 //DataTable dt1 = DbSession.Default.FromSql("select * from Student where stu_id=id").AddInParameter("id", DbType.Int32, 41500).ToDataTable(); //多个参数查询 //DataTable dt2 = DbSession.Default.FromSql("select * from Student where stu_id=id or stu_name=name") // .AddInParameter("id", DbType.Int32, 41500) // .AddInParameter("name", DbType.String, "张三") // .ToDataTable(); } /// <summary> /// 存储过程 /// </summary> public static void ProcDemo() { //"ProcName"就是存储过程名称。 DataTable dt = DbSession.Default.FromProc("ProcName").ToDataTable(); //执行带参数的存储过程 DataTable dt1 = DbSession.Default.FromProc("ProcName") .AddInParameter("parameterName", DbType.DateTime, "1995-01-01") .AddInParameter("parameterName1", DbType.DateTime, "1996-12-01") .ToDataTable(); //AddInputOutputParameter 方法添加输入输出参数 //AddOutParameter 方法添加输出参数 //AddReturnValueParameter 方法添加返回参数 ProcSection proc = DbSession.Default.FromProc("testoutstore") .AddInParameter("in1", System.Data.DbType.Int32, 1) .AddOutParameter("out2", System.Data.DbType.String, 100); proc.ExecuteNonQuery(); Dictionary<string, object> returnValue = proc.GetReturnValues(); foreach (KeyValuePair<string, object> kv in returnValue) { Console.WriteLine("ParameterName:" + kv.Key + " ;ReturnValue:" + Convert.ToString(kv.Value)); } } /// <summary> /// 辅助方法 /// </summary> public static void assistmethod() { //返回 Student._.Stu_name == "小黑" 的Student._.gr_id合计。 int? sum = (int?)DbSession.Default.Sum<Student>(Student._.gr_id, Student._.Stu_name == "小黑"); //返回 Student._.Stu_ID == 2 的Stu_ID平均值。 DbSession.Default.Avg<Student>(Student._.Stu_ID, Student._.Stu_ID == 2); //返回 Student._.Stu_ID == 2 的Stu_ID个数。 DbSession.Default.Count<Student>(Student._.Stu_ID, Student._.Stu_ID == 2); //返回 Student._.Stu_ID == 2 的Stu_ID最大值。 DbSession.Default.Max<Student>(Student._.Stu_ID, Student._.Stu_ID == 2); //返回 Student._.Stu_ID == 2 的Stu_ID最小值。 DbSession.Default.Min<Student>(Student._.Stu_ID, Student._.Stu_ID == 2); } /// <summary> /// 添加事务处理 /// </summary> public static void TestTrans() { DbTrans trans = DbSession.Default.BeginTransaction(); try { DbSession.Default.Update<Student>(Student._.Stu_name, "apple", Student._.Stu_ID == 1, trans); DbSession.Default.Update<Student>(Student._.Stu_name, "egg", Student._.Stu_ID == 2, trans); trans.Commit(); } catch { trans.Rollback(); } finally { trans.Close(); } //存储过程中的事务 （ProcName表示存储过程名称） DbTrans trans1 = DbSession.Default.BeginTransaction(); DbSession.Default.FromProc("ProcName").SetDbTransaction(trans); } /// <summary> /// 批处理 /// </summary> public static void batingTest() { //默认是10条sql执行一次。也可以自定义。 //DbBatch batch = DbSession.Default.BeginBatchConnection(20) using (DbBatch batch = DbSession.Default.BeginBatchConnection()) { batch.Update<Student>(Student._.Stu_name, "apple", Student._.Stu_ID == 1); batch.Update<Student>(Student._.Stu_name, "pear", Student._.Stu_ID == 2); //执行batch.Execute()，就会将之前的sql脚本先提交。 //batch.Execute(); batch.Update<Student>(Student._.Stu_name, "orange", Student._.Stu_ID == 3); } } /// <summary> /// 缓存 /// </summary> public static void SetCacheTimeOutDemo() { //SetCacheTimeOut设置查询的缓存为180秒 DbSession.Default.From<Student>().Where(Student._.Stu_ID == 1).SetCacheTimeOut(180).ToFirst(); } } }