Entity Framework对数据库表的CRUD操作

ＶＳ项目中使用ORM 　Entity Framework(EF) 方式对数据库进行相关操作也是相当方便．
第一种：用 Add(),Remove()，结合 Linq,　EF的DbSet 本身具有 enumberable实例.

１.查询

例：

 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();　//查询得到集合
            }

２）用Find(主键字段值)，注：只能是等于
var query =  someEntity .Customer.Find(1);

３）
/// <summary>
/// 分页查询
 /// </summary>
/// <typeparam name="T">要操作的数据类型</typeparam>
/// <param name="whereLambda">Where条件语句</param>
/// <param name="orderLambda">按什么条件排序</param>
 /// <param name="pageSize">每页都少条数据</param>
/// <param name="pageIndex">要查询第几页</param>
  /// <returns>返回一个泛型集合</returns>
//  注意：分页查询的时候，一定要先排序，因为其内部是做了一个row_number()的操作。 
 static List<T> GetPageList<T>(Func<T, bool> whereLambda, Func<T, object> orderLambda, int pageSize,
  int pageIndex) where T : class
   {
         MyDBEntities context = new MyDBEntities();  
         var list =  context.Set<T>().Where(whereLambda).OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
         return list.ToList();
 }

２.修改

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（）返回的记录数为０

３.新增

someEntity .Customer.Add(New Customer()
{
  CustomerId = "kkkh";
  CustomerName = "this is a test";
  .....
});
someEntity .SaveChanges();

４.删除：可以用 Remove()单条删除或 RemoveRange()批量删除

var rows = someEntity .Customer.Where(o=>o.Id < 10);
someEntity.RemoveRange(rows);
someEntity.SaveChanges();

第二种：用 .Database.SqlQuery(),SqlQuery，ExecuteSqlCommand()使用 SQL 语句．注：这些命令会自动管理SqlConnection,不用编写连接的代码．

**１.查询：用SqlQuery(),SqlQuery<T>**
//Sql 
var queryBySql = someEntity.Database.SqlQuery<Cusetomer>("SELECT * FROM Customer WHERE CustomerId= @id",New SqlParameter("@id","10"));\
//stored procedure
 var queryByStorePro = someEntity.Database.SqlQuery<Customer>("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);
                }