Dos.ORM使用教程

Dos.C#.Net使用

　　Dos.ORM（原Hxj.Data）于2009年发布，并发布实体生成工具。在开发过程参考了多个ORM框架，特别是NBear，MySoft、EF、Dapper等。吸取了他们的一些精华，加入自己的新思想。该组件已在上百个成熟企业项目中应用

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

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

  首先·在 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();



        }



    }
}

　　工具生成的实体类如下：

//------------------------------------------------------------------------------
// <auto-generated>
//     此代码由工具生成。
//     运行时版本:2.0.50727.5485
//     Support: http://www.cnblogs.com/huxj
//     对此文件的更改可能会导致不正确的行为，并且如果
//     重新生成代码，这些更改将会丢失。
// </auto-generated>
//------------------------------------------------------------------------------


using System;
using System.Data;
using System.Data.Common;
using Hxj.Data;
using Hxj.Data.Common;

namespace cn.School
{

    /// <summary>
    /// 实体类Student 。(属性说明自动提取数据库字段的描述信息)
    /// </summary>
    [Serializable]
    public class Student : Entity 
    {
        public Student():base("Student") {}

        #region Model
        private int _Stu_ID;
        private string _Stu_name;
        private int? _stu_Age;
        private string _stu_Sex;
        private string _stu_phon;
        private int _gr_id;
        /// <summary>
        /// 
        /// </summary>
        public int Stu_ID
        {
            get{ return _Stu_ID; }
            set
            {
                this.OnPropertyValueChange(_.Stu_ID,_Stu_ID,value);
                this._Stu_ID=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public string Stu_name
        {
            get{ return _Stu_name; }
            set
            {
                this.OnPropertyValueChange(_.Stu_name,_Stu_name,value);
                this._Stu_name=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public int? stu_Age
        {
            get{ return _stu_Age; }
            set
            {
                this.OnPropertyValueChange(_.stu_Age,_stu_Age,value);
                this._stu_Age=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public string stu_Sex
        {
            get{ return _stu_Sex; }
            set
            {
                this.OnPropertyValueChange(_.stu_Sex,_stu_Sex,value);
                this._stu_Sex=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public string stu_phon
        {
            get{ return _stu_phon; }
            set
            {
                this.OnPropertyValueChange(_.stu_phon,_stu_phon,value);
                this._stu_phon=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public int gr_id
        {
            get{ return _gr_id; }
            set
            {
                this.OnPropertyValueChange(_.gr_id,_gr_id,value);
                this._gr_id=value;
            }
        }
        #endregion

        #region Method
        /// <summary>
        /// 获取实体中的标识列
        /// </summary>
        public override Field GetIdentityField()
        {
            return _.Stu_ID;
        }
        /// <summary>
        /// 获取实体中的主键列
        /// </summary>
        public override Field[] GetPrimaryKeyFields()
        {
            return new Field[] {
                _.Stu_ID};
        }
        /// <summary>
        /// 获取列信息
        /// </summary>
        public override Field[] GetFields()
        {
            return new Field[] {
                _.Stu_ID,
                _.Stu_name,
                _.stu_Age,
                _.stu_Sex,
                _.stu_phon,
                _.gr_id};
        }
        /// <summary>
        /// 获取值信息
        /// </summary>
        public override object[] GetValues()
        {
            return new object[] {
                this._Stu_ID,
                this._Stu_name,
                this._stu_Age,
                this._stu_Sex,
                this._stu_phon,
                this._gr_id};
        }
        /// <summary>
        /// 给当前实体赋值
        /// </summary>
        public override void SetPropertyValues(IDataReader reader)
        {
            this._Stu_ID = DataUtils.ConvertValue<int>(reader["Stu_ID"]);
            this._Stu_name = DataUtils.ConvertValue<string>(reader["Stu_name"]);
            this._stu_Age = DataUtils.ConvertValue<int?>(reader["stu_Age"]);
            this._stu_Sex = DataUtils.ConvertValue<string>(reader["stu_Sex"]);
            this._stu_phon = DataUtils.ConvertValue<string>(reader["stu_phon"]);
            this._gr_id = DataUtils.ConvertValue<int>(reader["gr_id"]);
        }
        /// <summary>
        /// 给当前实体赋值
        /// </summary>
        public override void SetPropertyValues(DataRow row)
        {
            this._Stu_ID = DataUtils.ConvertValue<int>(row["Stu_ID"]);
            this._Stu_name = DataUtils.ConvertValue<string>(row["Stu_name"]);
            this._stu_Age = DataUtils.ConvertValue<int?>(row["stu_Age"]);
            this._stu_Sex = DataUtils.ConvertValue<string>(row["stu_Sex"]);
            this._stu_phon = DataUtils.ConvertValue<string>(row["stu_phon"]);
            this._gr_id = DataUtils.ConvertValue<int>(row["gr_id"]);
        }
        #endregion

        #region _Field
        /// <summary>
        /// 字段信息
        /// </summary>
        public class _
        {
            /// <summary>
            /// * 
            /// </summary>
            public readonly static Field All = new Field("*","Student");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field Stu_ID = new Field("Stu_ID","Student","Stu_ID");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field Stu_name = new Field("Stu_name","Student","Stu_name");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field stu_Age = new Field("stu_Age","Student","stu_Age");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field stu_Sex = new Field("stu_Sex","Student","stu_Sex");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field stu_phon = new Field("stu_phon","Student","stu_phon");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field gr_id = new Field("gr_id","Student","gr_id");
        }
        #endregion


    }
}




//------------------------------------------------------------------------------
// <auto-generated>
//     此代码由工具生成。
//     运行时版本:2.0.50727.5485
//     Support: http://www.cnblogs.com/huxj
//     对此文件的更改可能会导致不正确的行为，并且如果
//     重新生成代码，这些更改将会丢失。
// </auto-generated>
//------------------------------------------------------------------------------


using System;
using System.Data;
using System.Data.Common;
using Hxj.Data;
using Hxj.Data.Common;

namespace cn.School
{

    /// <summary>
    /// 实体类Gread 。(属性说明自动提取数据库字段的描述信息)
    /// </summary>
    [Serializable]
    public class Gread : Entity 
    {
        public Gread():base("Gread") {}

        #region Model
        private int _gr_id;
        private string _gr_name;
        /// <summary>
        /// 
        /// </summary>
        public int gr_id
        {
            get{ return _gr_id; }
            set
            {
                this.OnPropertyValueChange(_.gr_id,_gr_id,value);
                this._gr_id=value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public string gr_name
        {
            get{ return _gr_name; }
            set
            {
                this.OnPropertyValueChange(_.gr_name,_gr_name,value);
                this._gr_name=value;
            }
        }
        #endregion

        #region Method
        /// <summary>
        /// 获取实体中的标识列
        /// </summary>
        public override Field GetIdentityField()
        {
            return _.gr_id;
        }
        /// <summary>
        /// 获取实体中的主键列
        /// </summary>
        public override Field[] GetPrimaryKeyFields()
        {
            return new Field[] {
                _.gr_id};
        }
        /// <summary>
        /// 获取列信息
        /// </summary>
        public override Field[] GetFields()
        {
            return new Field[] {
                _.gr_id,
                _.gr_name};
        }
        /// <summary>
        /// 获取值信息
        /// </summary>
        public override object[] GetValues()
        {
            return new object[] {
                this._gr_id,
                this._gr_name};
        }
        /// <summary>
        /// 给当前实体赋值
        /// </summary>
        public override void SetPropertyValues(IDataReader reader)
        {
            this._gr_id = DataUtils.ConvertValue<int>(reader["gr_id"]);
            this._gr_name = DataUtils.ConvertValue<string>(reader["gr_name"]);
        }
        /// <summary>
        /// 给当前实体赋值
        /// </summary>
        public override void SetPropertyValues(DataRow row)
        {
            this._gr_id = DataUtils.ConvertValue<int>(row["gr_id"]);
            this._gr_name = DataUtils.ConvertValue<string>(row["gr_name"]);
        }
        #endregion

        #region _Field
        /// <summary>
        /// 字段信息
        /// </summary>
        public class _
        {
            /// <summary>
            /// * 
            /// </summary>
            public readonly static Field All = new Field("*","Gread");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field gr_id = new Field("gr_id","Gread","gr_id");
            /// <summary>
            /// 
            /// </summary>
            public readonly static Field gr_name = new Field("gr_name","Gread","gr_name");
        }
        #endregion


    }
}