你的位置:首页 > ASP.net教程

[ASP.net教程]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的
  • 众多成熟企业软件、互联网项目已应用此框架
  • 遵循MIT开源协议,除不允许改名,其它随意定制修改
  • Dos团队持续更新升级,任何Bug反馈都会立即得到解决

 

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

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

然后,进行增删改操作。如下:

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Threading.Tasks; 6 using Hxj.Data; 7 using Hxj.Data.Sqlite; 8 using System.Data; 9  10 namespace cn.School 11 { 12   class Test 13   { 14     static void Main(string[] args) 15     { 16  17       //  <connectionStrings> 18       //<add name="School" connectionString="Data Source=.;Initial Catalog=School;User ID=sa;Pwd=123;"></add> 19       //</connectionStrings> 20  21       //不同的数据库可构造不同的DbSession  DbSession(connectionStrings节点的name) 22       //DbSession dbs = new DbSession("School"); 23       DbSession dbs2 = new DbSession(DatabaseType.SqlServer, "Data Source=.;Initial Catalog=School;User ID=sa;Pwd=123;"); 24  25       //TestSelDB(); 26  27       //addTestDB(); 28  29       //Updata(); 30  31       //DelData(); 32  33       //sqlFrom(); 34  35       assistmethod(); 36     } 37  38     /// <summary> 39     /// 查询操作 40     /// </summary> 41     public static void TestSelDB() 42     { 43       //查询Student表中第一条数据并返回实体,代码如下。 44       Student st = DbSession.Default.From<Student>() 45         //.Select(Products._.ProductID) //查询返回ProductID字段 46         //.GroupBy(Products._.CategoryID.GroupBy && Products._.ProductName.GroupBy)//按照CategoryID,ProductName分组 47         //.InnerJoin<Suppliers>(Suppliers._.SupplierID == Products._.SupplierID)//关联Suppliers表  --CrossJoin  FullJoin LeftJoin RightJoin 同理 48         //.OrderBy(Products._.ProductID.Asc)//按照ProductID正序排序 49         //.Where((Products._.ProductName.Contain("apple") && Products._.UnitPrice > 1) || Products._.CategoryID == 2)//设置条件ProductName包含”apple”并且UnitPrice>1 或者CategoryID =2 50         //.UnionAll(DbSession.Default.From<Products>().Select(Products._.ProductID))//union all查询 51         //.Distinct() // Distinct 52         //.Top(5)  //读取前5条 53         //.Page(10, 2)//分页返回结果 每页10条返回第2页数据 54         //.ToDataSet();  //返回DataSet 55         //.ToDataReader(); //返回IDataReader 56         //.ToDataTable(); //返回DataTable 57         //.ToScalar(); //返回单个值   58       .ToFirst(); 59  60       //分字段查询 61       DbSession.Default.From<Student>() 62         .Select(Student._.Stu_ID, Student._.Stu_name) 63         .ToDataTable(); 64  65       //分字段查询取别名 66       DbSession.Default.From<Student>() 67         .Select(Student._.Stu_ID, Student._.Stu_name.As("pname")) 68         .ToDataTable(); 69  70       //排序倒叙排列 71       DataTable dt = DbSession.Default.From<Student>().OrderBy(Student._.Stu_ID.Desc).ToDataTable(); 72  73     } 74  75     /// <summary> 76     /// 模糊查询 77     /// 子查询 78     /// in 查询 79     /// not iN查询 80     /// </summary> 81     public static void demoSelet() 82     { 83  84       //Contain完全模糊查询 85       DbSession.Default.From<Student>().Where(Student._.Stu_ID.Contain(41500)); 86  87       //查找Stu_ID列中所有以41500开头的。 88       DbSession.Default.From<Student>().Where(Student._.Stu_ID.BeginWith(41500)); 89  90       //查找Stu_ID列中所有以41500结尾的。 91       DbSession.Default.From<Student>().Where(Student._.Stu_ID.EndWith(41500)); 92  93       //in 查询 94       DbSession.Default.From<Student>() 95         .Where(Student._.Stu_ID.SelectIn(1, 2, 3)) 96         .ToList(); 97  98       //not in查询 99       DbSession.Default.From<Student>()100         .Where(Student._.Stu_ID.SelectNotIn<int>(1, 2, 3))101         .ToList();102 103       //子查询104 105       //SubQueryEqual        =106       //SubQueryNotEqual      <>107       //SubQueryLess        <108       //SubQueryLessOrEqual    <=109       //SubQueryGreater      >110       //SubQueryGreaterOrEqual  >=111       //SubQueryIn        in112       //SubQueryNotIn        not in113       DbSession.Default.From<Student>()114         .Where(Student._.Stu_ID115         .SubQueryEqual(DbSession.Default.From<Student>().Where(Student._.Stu_ID == "Produce").Select(Student._.Stu_ID).Top(1)))116         .ToList();117     }118 119     /// <summary>120     /// 联合查询121     /// </summary>122     public static void likeSel()123     {124       //InnerJoin    inner join125       //LeftJoin    left join126       //RightJoin    right join127       //CrossJoin    cross join128       //FullJoin    full join129       //Union      union130       //UnionAll    union all131       DbSession.Default.From<Student>()132         .InnerJoin<Gread>(Student._.gr_id == Gread._.gr_id)133         .ToDataTable();134       //联合查询带条件135       DbSession.Default.From<Student>()136         .LeftJoin<Gread>(Student._.gr_id == Gread._.gr_id)137         .Where(Student._.gr_id == 1)138         .ToDataTable();139 140       //这两个是两个结果的合集,union会区分结果排除相同的,union all 则直接合并结果集合。141 142       DbSession.Default.From<Student>().Where(Student._.gr_id == 4522)143         .UnionAll(DbSession.Default.From<Gread>().Where(Gread._.gr_id == 1))144         .ToList();145     }146 147     /// <summary>148     /// 增加操作149     /// </summary>150     public static void addTestDB()151     {152       //新建一个实体153       Student stu = new Student();154       stu.Stu_name = "小黑";155       stu.stu_phon = "1254555";156       stu.stu_Sex = "男";157       stu.stu_Age = 25;158       stu.gr_id = 1;159 160       //开启修改 (开启修改后的添加操作将只insert赋值过的字段)161       stu.Attach();162 163       //返回值 如果有自增长字段,则返回自增长字段的值164       int result = DbSession.Default.Insert<Student>(stu);165 166       //将插入的数据查询出来167       List<Student> listStu = DbSession.Default.From<Student>().Where(Student._.Stu_ID == result).ToList();168     }169 170     /// <summary>171     /// 修改操作172     /// </summary>173     public static void Updata()174     {175       //先查询一个Student对象176       Student stu = DbSession.Default.From<Student>().Where(Student._.Stu_ID.Contain(41500)).ToFirst();177 178       //开启修改  (修改操作之前 必须执行此方法)179       stu.Attach();180 181       stu.Stu_name = "王五";182       List<ModifyField> list = stu.GetModifyFields();183       //清除修改记录  (清除后更新操作无效)184       //stu.ClearModifyFields();185       //返回0表示更新失败 组件有事务会自动回滚 186       //返回1表示更新成功  187       //更新成功返回值就是受影响的条数188       int num = DbSession.Default.Update<Student>(stu);189 190 191       //简单的修改方法,修改一个值的时候使用192       //int nums = DbSession.Default.Update<Student>(Student._.Stu_name, "九九", Student._.Stu_ID == 41501);193 194 195       //修改多个值的时候196       //Dictionary<Field, object> st = new Dictionary<Field, object>();197       //st.Add(Student._.stu_Sex, "男");198       //st.Add(Student._.Stu_name, "小徐");199       //int returnvalue = DbSession.Default.Update<Student>(st, Student._.Stu_ID == 41501);200     }201 202 203     /// <summary>204     /// 删除操作205     /// </summary>206     public static void DelData()207     {208 209       int returnValue = DbSession.Default.Delete<Student>(Student._.Stu_ID == 41504);210       //与上面等效的删除语句211       //int returnvalue = DbSession.Default.Delete<Student>(2);212 213 214       //删除一个对象215       //Student stu = DbSession.Default.From<Student>().ToFirst();216       //int returnvalue = DbSession.Default.Delete<Student>(stu);217     }218 219 220     /// <summary>221     /// 使用SQL语句查询222     /// </summary>223     public static void sqlFrom()224     {225 226       //直接使用SQL语句查询227       DataTable dt = DbSession.Default.FromSql("select * from Student").ToDataTable();228 229       //参数化SQL语句230       //DataTable dt1 = DbSession.Default.FromSql("select * from Student where stu_id=id").AddInParameter("id", DbType.Int32, 41500).ToDataTable();231 232       //多个参数查询233       //DataTable dt2 = DbSession.Default.FromSql("select * from Student where stu_id=id or stu_name=name")234       //  .AddInParameter("id", DbType.Int32, 41500)235       //  .AddInParameter("name", DbType.String, "张三")236       //  .ToDataTable();237     }238 239 240     /// <summary>241     /// 存储过程242     /// </summary>243     public static void ProcDemo()244     {245       //"ProcName"就是存储过程名称。246       DataTable dt = DbSession.Default.FromProc("ProcName").ToDataTable();247 248 249       //执行带参数的存储过程250       DataTable dt1 = DbSession.Default.FromProc("ProcName")251         .AddInParameter("parameterName", DbType.DateTime, "1995-01-01")252         .AddInParameter("parameterName1", DbType.DateTime, "1996-12-01")253         .ToDataTable();254 255 256 257       //AddInputOutputParameter 方法添加输入输出参数 258       //AddOutParameter 方法添加输出参数259       //AddReturnValueParameter 方法添加返回参数260 261       ProcSection proc = DbSession.Default.FromProc("testoutstore")262       .AddInParameter("in1", System.Data.DbType.Int32, 1)263       .AddOutParameter("out2", System.Data.DbType.String, 100);264       proc.ExecuteNonQuery();265 266       Dictionary<string, object> returnValue = proc.GetReturnValues();267 268       foreach (KeyValuePair<string, object> kv in returnValue)269       {270         Console.WriteLine("ParameterName:" + kv.Key + "  ;ReturnValue:" + Convert.ToString(kv.Value));271       }272     }273 274     /// <summary>275     /// 辅助方法276     /// </summary>277     public static void assistmethod()278     {279       //返回 Student._.Stu_name == "小黑" 的Student._.gr_id合计。280       int? sum = (int?)DbSession.Default.Sum<Student>(Student._.gr_id, Student._.Stu_name == "小黑");281 282       //返回 Student._.Stu_ID == 2 的Stu_ID平均值。283       DbSession.Default.Avg<Student>(Student._.Stu_ID, Student._.Stu_ID == 2);284 285       //返回 Student._.Stu_ID == 2 的Stu_ID个数。286       DbSession.Default.Count<Student>(Student._.Stu_ID, Student._.Stu_ID == 2);287 288       //返回 Student._.Stu_ID == 2 的Stu_ID最大值。289       DbSession.Default.Max<Student>(Student._.Stu_ID, Student._.Stu_ID == 2);290 291       //返回 Student._.Stu_ID == 2 的Stu_ID最小值。292       DbSession.Default.Min<Student>(Student._.Stu_ID, Student._.Stu_ID == 2);293 294     }295 296 297     /// <summary>298     /// 添加事务处理299     /// </summary>300     public static void TestTrans()301     {302 303       DbTrans trans = DbSession.Default.BeginTransaction();304       try305       {306         DbSession.Default.Update<Student>(Student._.Stu_name, "apple", Student._.Stu_ID == 1, trans);307         DbSession.Default.Update<Student>(Student._.Stu_name, "egg", Student._.Stu_ID == 2, trans);308         trans.Commit();309       }310       catch311       {312         trans.Rollback();313       }314       finally315       {316         trans.Close();317       }318 319       //存储过程中的事务  (ProcName表示存储过程名称)320       DbTrans trans1 = DbSession.Default.BeginTransaction();321       DbSession.Default.FromProc("ProcName").SetDbTransaction(trans);322 323     }324 325 326     /// <summary>327     /// 批处理328     /// </summary>329     public static void batingTest()330     {331       //默认是10条sql执行一次。也可以自定义。332       //DbBatch batch = DbSession.Default.BeginBatchConnection(20)333 334       using (DbBatch batch = DbSession.Default.BeginBatchConnection())335       {336         batch.Update<Student>(Student._.Stu_name, "apple", Student._.Stu_ID == 1);337         batch.Update<Student>(Student._.Stu_name, "pear", Student._.Stu_ID == 2);338         //执行batch.Execute(),就会将之前的sql脚本先提交。339         //batch.Execute();340         batch.Update<Student>(Student._.Stu_name, "orange", Student._.Stu_ID == 3);341       }342     }343 344 345     /// <summary>346     /// 缓存347     /// </summary>348     public static void SetCacheTimeOutDemo() {349 350       //SetCacheTimeOut设置查询的缓存为180秒351       DbSession.Default.From<Student>().Where(Student._.Stu_ID == 1).SetCacheTimeOut(180).ToFirst();352 353 354 355     }356 357 358 359   }360 }

  工具生成的实体类如下:

//------------------------------------------------------------------------------// <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  }}