PetaPoco 增删改查
1 查询单行
DBInstance.DB.SingleOrDefault<CompanyInfo11>(id);
/// <summary> /// 根据id获取公司信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static CompanyInfo11 GetCompanyById(int id) { return DBInstance.DB.SingleOrDefault<CompanyInfo11>(id); }
2 查询结果集
DBInstance.DB.Query<CompanyInfo11>("where 1=1 order by Cid desc").ToList()
/// <summary> /// 查询所有公司信息,按创建日期倒序 /// </summary> /// <returns>数据表格</returns> public List<CompanyInfo11> Load() { List<CompanyInfo11> companyList = null; companyList= DBInstance.DB.Query<CompanyInfo11>("where 1=1 order by Cid desc").ToList(); return companyList; }
3 根据条件查询结果集
/// <summary> /// 查询所有公司信息,根据参数 /// </summary> /// <returns>数据表格</returns> public List<CompanyInfo11> Load() { List<CompanyInfo11> companyList = null; companyList = DBInstance.DB.Query<CompanyInfo11>("where Cid>@0", 5).ToList();//第一个参数是@0,第二个参数是@1,等等 return companyList; }
4.1 Update 更新单表中一行
/// <summary> /// 更新公司信息 /// </summary> /// <param name="comp"></param> /// <returns></returns> public int Update(CompanyInfo11 comp) { if (comp != null) { try { int i= DBInstance.DB.Update(comp); return i; } catch { } } return 0; }
4.2 Update 更新单表中一行的某列
/// <summary> /// 更新公司信息 /// </summary> /// <param name="comp"></param> /// <returns></returns> public int Update(CompanyInfo11 comp) { List<string> columns = new List<string>(); columns.Add("CompanyName"); //需要更新的列 if (comp != null) { try { int i= DBInstance.DB.Update(comp,columns); return i; } catch { } } return 0; }
4.3 Update 根据SQL语句(灵活)
//更新Cid>1000的所有条目,让CompanyName 都为实体类中的CompanyName
public int Update(CompanyInfo11 comp) { int i = DBInstance.DB.Update<CompanyInfo11>("set CompanyName=@0 where Cid>@1",comp.CompanyName,1000); return i; }
5.1 删除一行
public int Delete(CompanyInfo11 comp) { int i = DBInstance.DB.Delete(comp); return i; }
5.2 带条件删除
public int Delete2() { int i = DBInstance.DB.Delete<CompanyInfo11>("where Cid>@0",1000); return i; }