dos.orm mysql_Dos.ORM使用教程

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingHxj.Data;7 usingHxj.Data.Sqlite;8 usingSystem.Data;9

10 namespacecn.School11 {12 classTest13 {14 static void Main(string[] args)15 {16

17 //18 //19 //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 ///

39 ///查询操作40 ///

41 public static voidTestSelDB()42 {43 //查询Student表中第一条数据并返回实体，代码如下。

44 Student st = DbSession.Default.From()45 //.Select(Products._.ProductID)//查询返回ProductID字段46 //.GroupBy(Products._.CategoryID.GroupBy && Products._.ProductName.GroupBy)//按照CategoryID，ProductName分组47 //.InnerJoin(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 =250 //.UnionAll(DbSession.Default.From().Select(Products._.ProductID))//union all查询51 //.Distinct()//Distinct52 //.Top(5)//读取前5条53 //.Page(10, 2)//分页返回结果 每页10条返回第2页数据54 //.ToDataSet();//返回DataSet55 //.ToDataReader();//返回IDataReader56 //.ToDataTable();//返回DataTable57 //.ToScalar();//返回单个值

58 .ToFirst();59

60 //分字段查询

61 DbSession.Default.From()62 .Select(Student._.Stu_ID, Student._.Stu_name)63 .ToDataTable();64

65 //分字段查询取别名

66 DbSession.Default.From()67 .Select(Student._.Stu_ID, Student._.Stu_name.As("pname"))68 .ToDataTable();69

70 //排序倒叙排列

71 DataTable dt = DbSession.Default.From().OrderBy(Student._.Stu_ID.Desc).ToDataTable();72

73 }74

75 ///

76 ///模糊查询77 ///子查询78 ///in 查询79 ///not iN查询80 ///

81 public static voiddemoSelet()82 {83

84 //Contain完全模糊查询

85 DbSession.Default.From().Where(Student._.Stu_ID.Contain(41500));86

87 //查找Stu_ID列中所有以41500开头的。

88 DbSession.Default.From().Where(Student._.Stu_ID.BeginWith(41500));89

90 //查找Stu_ID列中所有以41500结尾的。

91 DbSession.Default.From().Where(Student._.Stu_ID.EndWith(41500));92

93 //in 查询

94 DbSession.Default.From()95 .Where(Student._.Stu_ID.SelectIn(1, 2, 3))96 .ToList();97

98 //not in查询

99 DbSession.Default.From()100 .Where(Student._.Stu_ID.SelectNotIn(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 in

113 DbSession.Default.From()114 .Where(Student._.Stu_ID115 .SubQueryEqual(DbSession.Default.From().Where(Student._.Stu_ID == "Produce").Select(Student._.Stu_ID).Top(1)))116 .ToList();117 }118

119 ///

120 ///联合查询121 ///

122 public static voidlikeSel()123 {124 //InnerJoin inner join125 //LeftJoin left join126 //RightJoin right join127 //CrossJoin cross join128 //FullJoin full join129 //Union union130 //UnionAll union all

131 DbSession.Default.From()132 .InnerJoin(Student._.gr_id ==Gread._.gr_id)133 .ToDataTable();134 //联合查询带条件

135 DbSession.Default.From()136 .LeftJoin(Student._.gr_id ==Gread._.gr_id)137 .Where(Student._.gr_id == 1)138 .ToDataTable();139

140 //这两个是两个结果的合集，union会区分结果排除相同的，union all 则直接合并结果集合。

141

142 DbSession.Default.From().Where(Student._.gr_id == 4522)143 .UnionAll(DbSession.Default.From().Where(Gread._.gr_id == 1))144 .ToList();145 }146

147 ///

148 ///增加操作149 ///

150 public static voidaddTestDB()151 {152 //新建一个实体

153 Student stu = newStudent();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(stu);165

166 //将插入的数据查询出来

167 List listStu = DbSession.Default.From().Where(Student._.Stu_ID ==result).ToList();168 }169

170 ///

171 ///修改操作172 ///

173 public static voidUpdata()174 {175 //先查询一个Student对象

176 Student stu = DbSession.Default.From().Where(Student._.Stu_ID.Contain(41500)).ToFirst();177

178 //开启修改 (修改操作之前 必须执行此方法)

179 stu.Attach();180

181 stu.Stu_name = "王五";182 List list =stu.GetModifyFields();183 //清除修改记录 (清除后更新操作无效)184 //stu.ClearModifyFields();185 //返回0表示更新失败 组件有事务会自动回滚186 //返回1表示更新成功187 //更新成功返回值就是受影响的条数

188 int num = DbSession.Default.Update(stu);189

190

191 //简单的修改方法，修改一个值的时候使用192 //int nums = DbSession.Default.Update(Student._.Stu_name, "九九", Student._.Stu_ID == 41501);193

194

195 //修改多个值的时候196 //Dictionary st = new Dictionary();197 //st.Add(Student._.stu_Sex, "男");198 //st.Add(Student._.Stu_name, "小徐");199 //int returnvalue = DbSession.Default.Update(st, Student._.Stu_ID == 41501);

200 }201

202

203 ///

204 ///删除操作205 ///

206 public static voidDelData()207 {208

209 int returnValue = DbSession.Default.Delete(Student._.Stu_ID == 41504);210 //与上面等效的删除语句211 //int returnvalue = DbSession.Default.Delete(2);212

213

214 //删除一个对象215 //Student stu = DbSession.Default.From().ToFirst();216 //int returnvalue = DbSession.Default.Delete(stu);

217 }218

219

220 ///

221 ///使用SQL语句查询222 ///

223 public static voidsqlFrom()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 ///

241 ///存储过程242 ///

243 public static voidProcDemo()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 returnValue =proc.GetReturnValues();267

268 foreach (KeyValuePair kv inreturnValue)269 {270 Console.WriteLine("ParameterName:" + kv.Key + ";ReturnValue:" +Convert.ToString(kv.Value));271 }272 }273

274 ///

275 ///辅助方法276 ///

277 public static voidassistmethod()278 {279 //返回 Student._.Stu_name == "小黑" 的Student._.gr_id合计。

280 int? sum = (int?)DbSession.Default.Sum(Student._.gr_id, Student._.Stu_name == "小黑");281

282 //返回 Student._.Stu_ID == 2 的Stu_ID平均值。

283 DbSession.Default.Avg(Student._.Stu_ID, Student._.Stu_ID == 2);284

285 //返回 Student._.Stu_ID == 2 的Stu_ID个数。

286 DbSession.Default.Count(Student._.Stu_ID, Student._.Stu_ID == 2);287

288 //返回 Student._.Stu_ID == 2 的Stu_ID最大值。

289 DbSession.Default.Max(Student._.Stu_ID, Student._.Stu_ID == 2);290

291 //返回 Student._.Stu_ID == 2 的Stu_ID最小值。

292 DbSession.Default.Min(Student._.Stu_ID, Student._.Stu_ID == 2);293

294 }295

296

297 ///

298 ///添加事务处理299 ///

300 public static voidTestTrans()301 {302

303 DbTrans trans =DbSession.Default.BeginTransaction();304 try

305 {306 DbSession.Default.Update(Student._.Stu_name, "apple", Student._.Stu_ID == 1, trans);307 DbSession.Default.Update(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 ///

327 ///批处理328 ///

329 public static voidbatingTest()330 {331 //默认是10条sql执行一次。也可以自定义。332 //DbBatch batch = DbSession.Default.BeginBatchConnection(20)

333

334 using (DbBatch batch =DbSession.Default.BeginBatchConnection())335 {336 batch.Update(Student._.Stu_name, "apple", Student._.Stu_ID == 1);337 batch.Update(Student._.Stu_name, "pear", Student._.Stu_ID == 2);338 //执行batch.Execute()，就会将之前的sql脚本先提交。339 //batch.Execute();

340 batch.Update(Student._.Stu_name, "orange", Student._.Stu_ID == 3);341 }342 }343

344

345 ///

346 ///缓存347 ///

348 public static voidSetCacheTimeOutDemo() {349

350 //SetCacheTimeOut设置查询的缓存为180秒

351 DbSession.Default.From().Where(Student._.Stu_ID == 1).SetCacheTimeOut(180).ToFirst();352

353

354

355 }356

357

358

359 }360 }