Dos.ORM（原Hxj.Data）於2009年發布，並發布實體生成工具。在開發過程參考了多個ORM框架，特別是NBear，MySoft、EF、Dapper等。吸取了他們的一些精華，加入自己的新思想。該組件已在上百個成熟企業項目中應用
首先·在 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 in
112 //SubQueryNotIn not in
113 DbSession.Default.From<Student>()
114 .Where(Student._.Stu_ID
115 .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 join
125 //LeftJoin left join
126 //RightJoin right join
127 //CrossJoin cross join
128 //FullJoin full join
129 //Union union
130 //UnionAll union all
131 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 try
305 {
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 catch
311 {
312 trans.Rollback();
313 }
314 finally
315 {
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
}
}
