本文共 4924 字,大约阅读时间需要 16 分钟。
VS项目中使用ORM Entity Framework(EF) 方式对数据库进行相关操作也是相当方便.
第一种:用 Add(),Remove(),结合 Linq, EF的DbSet 本身具有 enumberable实例.1.查询
例:
using (SomeEntities someEntity = new SomeEntities ()) { txtCustomerName.Text = someEntity .Customer.Where(o => o.CusetomerName== "Cusetomer name").Select(o => o.CustomerName).FirstOrDefault());//查询得到某一个字段值 dgvRt.DataSource = someEntity .Customer.Where(o => o.CustomerName == "Cusetomer name ").ToList(); //查询得到集合 }2)用Find(主键字段值),注:只能是等于var query = someEntity .Customer.Find(1);3)////// 分页查询 /// ///要操作的数据类型 /// Where条件语句/// 按什么条件排序 /// 每页都少条数据/// 要查询第几页 ///返回一个泛型集合 // 注意:分页查询的时候,一定要先排序,因为其内部是做了一个row_number()的操作。 static ListGetPageList (Func whereLambda, Func orderLambda, int pageSize, int pageIndex) where T : class { MyDBEntities context = new MyDBEntities(); var list = context.Set ().Where(whereLambda).OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize); return list.ToList(); }
2.修改
var query = someEntity .Customer.Find(1);if (query.any()){ query.CustomerName = "new customername"; someEntity .SaveChanges();}//按条件批量修改 someEntity .Customer.Where(o => o.CustomerName.Contains("shenzhen") .ToList().ForEach(o => { o.CustomerName = "New cusotmername"; }); if (someEntity .SaveChanges() > 0) { MessageBox.Show("Update sucessful.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Fail to update.", "Fail", MessageBoxButtons.OK, MessageBoxIcon.Error); } //注意:如查记录的内容没有修改,EF是不会去执行update语句,, SaveChanges()返回的记录数为0
3.新增
someEntity .Customer.Add(New Customer(){ CustomerId = "kkkh"; CustomerName = "this is a test"; .....});someEntity .SaveChanges();
4.删除:可以用 Remove()单条删除或 RemoveRange()批量删除
var rows = someEntity .Customer.Where(o=>o.Id < 10);someEntity.RemoveRange(rows);someEntity.SaveChanges();
第二种:用 .Database.SqlQuery(),SqlQuery,ExecuteSqlCommand()使用 SQL 语句.注:这些命令会自动管理SqlConnection,不用编写连接的代码.
**1.查询:用SqlQuery(),SqlQuery**//Sql var queryBySql = someEntity.Database.SqlQuery ("SELECT * FROM Customer WHERE CustomerId= @id",New SqlParameter("@id","10"));\//stored procedure var queryByStorePro = someEntity.Database.SqlQuery ("dbo.sp_query_customer @id",New SqlParameter("@id","10"));**2. 增加,修改,删除:用ExecuteSqlCommand()**如果是insert ,update,delete操作,可以使用ExecuteSqlCommand()例如:var exRtn = someEntity.Database.ExecuteSqlCommand((TranscationBehavior.EnsureTransaction,"Insert into t1(@t1);Delete From t2 WHERE Id = @t2",New SqlParameter("@t1","value1"),New SqlParameter("@t2","value2"));if (exRtn > 0){ }else { }//支持使用Transaction--------------------------------- var exeRt = admenti.Database.ExecuteSqlCommand("sp_copy_JobRegMasterDetail @masterRefNo,@JobNo,@JobNoType", new SqlParameter[] { new SqlParameter("@masterRefNo", jobMast_CopyFrom.RefNo), new SqlParameter("@JobNo",jobMast_CopyFrom.JobNo), new SqlParameter("@JobNoType","JS")}); if (exeRt > 0) { MessageBox.Show("Copied."); btnSearch.PerformClick(); EditJob(jobMast_CopyFrom.JobNo); } else MessageBox.Show("Failed to copy.");
第三种:用 ado.net 的ExecuteNoQuery(), ExecuteScalar(),ExecuteReader()等.(这个很多人有熟悉的感觉吧)
例如:
var sqlCmd = someEntity.Database.Connection.CreateCommand();sqlCmd.CommandTex = "sp_query_customer";sqlCmd.CommandType=CommandType.StoredProcedure;sqlCmd.Parameters.add(New SqlParameter("@Id",10));sqlCmd.Connection.Open();var reader = sqlCmd.ExecuteReader();while (reader.Read()) Console.WriteLine($"CustomerName:{ reader.GetValue(0)});sqlCmd.Connection.Close();
另外也可以利用 entity framework data model的connection 很方便的使用ado.net的各种方法.
例如:SqlDataAdapter ,SqlCommand等var result = new DataTable(); using (SqlDataAdapter da = new SqlDataAdapter("sp_copy_JobRegMasterDetail", admenti.Database.Connection.ConnectionString)) { da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@masterRefNo", jobMast_CopyFrom.RefNo), new SqlParameter("@JobNo",jobMast_CopyFrom.JobNo), new SqlParameter("@JobNoType",strJobNoType)}); da.Fill(result); }
转载地址:http://osmdi.baihongyu.com/