/// <summary> /// 得到一个对象实体 /// </summary> public tb_user GetInfo(tb_user model) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from tb_user"); strSql.Append(" where id=@id "); using (IDbConnection conn = DapperHelper.OpenConnection()) { model = conn.Query <tb_user>(strSql.ToString(), model)?.FirstOrDefault(); } return(model); }
/// <summary> /// 是否存在该记录 /// </summary> public bool Exists(tb_user model) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from tb_user"); strSql.Append(" where id=@id "); using (IDbConnection conn = DapperHelper.OpenConnection()) { int count = conn.Execute(strSql.ToString(), model); if (count > 0) { return(true); } else { return(false); } } }
/// <summary> /// 增加一条数据 /// </summary> public bool Add(tb_user model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into tb_user("); strSql.Append("id,code,nickname,phonenum,address,longitude,latitude,remark,createdate,updatedate,pwd,province,city,county,location,sex,job,age,constellation,hav,tall,schedule,sgin,hobby,birth,wx,issingle,company,companyaddress,realname,avatar,isdel,addtime)"); strSql.Append(" values ("); strSql.Append("@id,@code,@nickname,@phonenum,@address,@longitude,@latitude,@remark,@createdate,@updatedate,@pwd,@province,@city,@county,@location,@sex,@job,@age,@constellation,@hav,@tall,@schedule,@sgin,@hobby,@birth,@wx,@issingle,@company,@companyaddress,@realname,@avatar,@isdel,@addtime)"); using (IDbConnection conn = DapperHelper.OpenConnection()) { int count = conn.Execute(strSql.ToString(), model); if (count > 0) { return(true); } else { return(false); } } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(tb_user model) { StringBuilder strSql = new StringBuilder(); StringBuilder setSql = new StringBuilder(); strSql.Append("update tb_user set "); if (!String.IsNullOrEmpty(model.code)) { setSql.Append("code=@code,"); } if (!String.IsNullOrEmpty(model.nickname)) { setSql.Append("nickname=@nickname,"); } if (!String.IsNullOrEmpty(model.phonenum)) { setSql.Append("phonenum=@phonenum,"); } if (!String.IsNullOrEmpty(model.address)) { setSql.Append("address=@address,"); } if (model.longitude != null) { setSql.Append("longitude=@longitude,"); } if (model.latitude != null) { setSql.Append("latitude=@latitude,"); } if (!String.IsNullOrEmpty(model.remark)) { setSql.Append("remark=@remark,"); } if (model.createdate != null) { setSql.Append("createdate=@createdate,"); } if (model.updatedate != null) { setSql.Append("updatedate=@updatedate,"); } if (!String.IsNullOrEmpty(model.pwd)) { setSql.Append("pwd=@pwd,"); } if (!String.IsNullOrEmpty(model.province)) { setSql.Append("province=@province,"); } if (!String.IsNullOrEmpty(model.city)) { setSql.Append("city=@city,"); } if (!String.IsNullOrEmpty(model.county)) { setSql.Append("county=@county,"); } if (!String.IsNullOrEmpty(model.location)) { setSql.Append("location=@location,"); } if (!String.IsNullOrEmpty(model.sex)) { setSql.Append("sex=@sex,"); } if (!String.IsNullOrEmpty(model.job)) { setSql.Append("job=@job,"); } if (model.age != null) { setSql.Append("age=@age,"); } if (!String.IsNullOrEmpty(model.constellation)) { setSql.Append("constellation=@constellation,"); } if (!String.IsNullOrEmpty(model.hav)) { setSql.Append("hav=@hav,"); } if (model.tall != null) { setSql.Append("tall=@tall,"); } if (!String.IsNullOrEmpty(model.schedule)) { setSql.Append("schedule=@schedule,"); } if (!String.IsNullOrEmpty(model.sgin)) { setSql.Append("sgin=@sgin,"); } if (!String.IsNullOrEmpty(model.hobby)) { setSql.Append("hobby=@hobby,"); } if (model.birth != null) { setSql.Append("birth=@birth,"); } if (!String.IsNullOrEmpty(model.wx)) { setSql.Append("wx=@wx,"); } if (!String.IsNullOrEmpty(model.issingle)) { setSql.Append("issingle=@issingle,"); } if (!String.IsNullOrEmpty(model.company)) { setSql.Append("company=@company,"); } if (!String.IsNullOrEmpty(model.companyaddress)) { setSql.Append("companyaddress=@companyaddress,"); } if (!String.IsNullOrEmpty(model.realname)) { setSql.Append("realname=@realname,"); } if (!String.IsNullOrEmpty(model.avatar)) { setSql.Append("avatar=@avatar,"); } if (model.isdel) { setSql.Append("isdel=@isdel,"); } if (model.addtime != null) { setSql.Append("addtime=@addtime,"); } strSql.Append(setSql.ToString().TrimEnd(',')); strSql.Append(" where id=@id "); using (IDbConnection conn = DapperHelper.OpenConnection()) { int count = conn.Execute(strSql.ToString(), model); if (count > 0) { return(true); } else { return(false); } } }
/// <summary> /// 获得数据列表 /// </summary> public List <tb_user> GetList(tb_user model, ref int total) { List <tb_user> list; StringBuilder strSql = new StringBuilder(); StringBuilder whereSql = new StringBuilder(" where 1 = 1 "); strSql.Append(" select ROW_NUMBER() OVER(ORDER BY id desc) AS RID, * from tb_user "); if (!String.IsNullOrEmpty(model.code)) { whereSql.Append(" and code=@code"); } if (!String.IsNullOrEmpty(model.nickname)) { whereSql.Append(" and nickname=@nickname"); } if (!String.IsNullOrEmpty(model.phonenum)) { whereSql.Append(" and phonenum=@phonenum"); } if (!String.IsNullOrEmpty(model.address)) { whereSql.Append(" and address=@address"); } if (model.longitude != null) { whereSql.Append(" and longitude=@longitude"); } if (model.latitude != null) { whereSql.Append(" and latitude=@latitude"); } if (!String.IsNullOrEmpty(model.remark)) { whereSql.Append(" and remark=@remark"); } if (model.createdate != null) { whereSql.Append(" and createdate=@createdate"); } if (model.updatedate != null) { whereSql.Append(" and updatedate=@updatedate"); } if (!String.IsNullOrEmpty(model.pwd)) { whereSql.Append(" and pwd=@pwd"); } if (!String.IsNullOrEmpty(model.province)) { whereSql.Append(" and province=@province"); } if (!String.IsNullOrEmpty(model.city)) { whereSql.Append(" and city=@city"); } if (!String.IsNullOrEmpty(model.county)) { whereSql.Append(" and county=@county"); } if (!String.IsNullOrEmpty(model.location)) { whereSql.Append(" and location=@location"); } if (!String.IsNullOrEmpty(model.sex)) { whereSql.Append(" and sex=@sex"); } if (!String.IsNullOrEmpty(model.job)) { whereSql.Append(" and job=@job"); } if (model.age != null) { whereSql.Append(" and age=@age"); } if (!String.IsNullOrEmpty(model.constellation)) { whereSql.Append(" and constellation=@constellation"); } if (!String.IsNullOrEmpty(model.hav)) { whereSql.Append(" and hav=@hav"); } if (model.tall != null) { whereSql.Append(" and tall=@tall"); } if (!String.IsNullOrEmpty(model.schedule)) { whereSql.Append(" and schedule=@schedule"); } if (!String.IsNullOrEmpty(model.sgin)) { whereSql.Append(" and sgin=@sgin"); } if (!String.IsNullOrEmpty(model.hobby)) { whereSql.Append(" and hobby=@hobby"); } if (model.birth != null) { whereSql.Append(" and birth=@birth"); } if (!String.IsNullOrEmpty(model.wx)) { whereSql.Append(" and wx=@wx"); } if (!String.IsNullOrEmpty(model.issingle)) { whereSql.Append(" and issingle=@issingle"); } if (!String.IsNullOrEmpty(model.company)) { whereSql.Append(" and company=@company"); } if (!String.IsNullOrEmpty(model.companyaddress)) { whereSql.Append(" and companyaddress=@companyaddress"); } if (!String.IsNullOrEmpty(model.realname)) { whereSql.Append(" and realname=@realname"); } if (!String.IsNullOrEmpty(model.avatar)) { whereSql.Append(" and avatar=@avatar"); } if (model.isdel) { whereSql.Append(" and isdel=@isdel"); } if (model.addtime != null) { whereSql.Append(" and addtime=@addtime"); } strSql.Append(whereSql); string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList"; string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}"; pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString()); using (IDbConnection conn = DapperHelper.OpenConnection()) { list = conn.Query <tb_user>(pageSqlStr, model)?.ToList(); total = conn.ExecuteScalar <int>(CountSql, model); } return(list); }