.net mysql增删改查_.net Core+Dapper MySQL增删改查

public partial classTuiUsersDal

{public string ConnStr { set; get; }public List GetListUser(stringstrWhere)

{

StringBuilder strSql= newStringBuilder();

strSql.Append("select *");

strSql.Append("FROM `tuiusers`");if (strWhere.Trim() != "")

{

strSql.Append("where" +strWhere);

}

List list = new List();using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{

list= connection.Query(strSql.ToString()).ToList();

}returnlist;

}///

///增加一条数据///

public boolAdd(Entities.TuiUsers model)

{

DynamicParameters Parameters= newDynamicParameters();int cnt = 0;string sQuery = "INSERT INTO tuiusers (userName,userPass,userType,company,detail,create_time,edit_time,pid,alipayUrl,taobaoUrl,linkMan,contactInfo,priceRatio)"

+ "VALUES(@userName,@userPass,@userType,@company,@detail,@create_time,@edit_time,@pid,@alipayUrl,@taobaoUrl,@linkMan,@contactInfo,@priceRatio)";

Parameters.Add("alipayUrl", model.alipayUrl);

Parameters.Add("company", model.company);

Parameters.Add("contactInfo", model.contactInfo);

Parameters.Add("create_time", model.create_time);

Parameters.Add("detail", model.detail);

Parameters.Add("edit_time", model.edit_time);

Parameters.Add("linkMan", model.linkMan);

Parameters.Add("pid", model.pid);

Parameters.Add("priceRatio", model.priceRatio);

Parameters.Add("stateFlag", model.stateFlag);

Parameters.Add("taobaoUrl", model.taobaoUrl);

Parameters.Add("userName", model.userName);

Parameters.Add("userPass", model.userPass);

Parameters.Add("userType", model.userType);using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{

cnt=connection.Execute(sQuery, Parameters);

}if (cnt > 0)

{return true;

}else{return false;

}

}///

///根据ID删除一条数据///

public bool Delete(intid)

{

DynamicParameters Parameters= newDynamicParameters();int cnt = 0;string sQuery = "Delete FROM tuiusers" + "";

Parameters.Add("Id", id);using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{

cnt=connection.Execute(sQuery, Parameters);

}if (cnt > 0)

{return true;

}else{return false;

}

}///

///更新一条数据///

public boolUpdate(Entities.TuiUsers model)

{

DynamicParameters Parameters= newDynamicParameters();string sQuery = "";

Parameters.Add("alipayUrl", model.alipayUrl);

Parameters.Add("company", model.company);

Parameters.Add("contactInfo", model.contactInfo);

Parameters.Add("create_time", model.create_time);

Parameters.Add("detail", model.detail);

Parameters.Add("edit_time", model.edit_time);

Parameters.Add("linkMan", model.linkMan);

Parameters.Add("pid", model.pid);

Parameters.Add("priceRatio", model.priceRatio);

Parameters.Add("stateFlag", model.stateFlag);

Parameters.Add("taobaoUrl", model.taobaoUrl);

Parameters.Add("userName", model.userName);

Parameters.Add("userPass", model.userPass);

Parameters.Add("userType", model.userType);int cnt = 0;using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{

cnt=connection.Execute(sQuery, Parameters);

}if (cnt > 0)

{return true;

}else{return false;

}

}///

///根据ID获取实体对象///

public Entities.TuiUsers GetModel(intid)

{

DynamicParameters Parameters= newDynamicParameters();string sQuery = "SELECT * FROM tuiusers" + "WHERE id = @id";

Parameters.Add("id", id);using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{return connection.Query(sQuery, Parameters).FirstOrDefault();

}

}///

///根据userName获取实体对象///

public Entities.TuiUsers GetModelByUserName(stringuserName)

{

DynamicParameters Parameters= newDynamicParameters();string sQuery = "SELECT * FROM tuiusers" + "WHERE userName = @userName";

Parameters.Add("userName", userName);using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{return connection.Query(sQuery, Parameters).FirstOrDefault();

}

}/// 分页获取数据列表///

///

public List GetListArray(string fileds, string orderstr, int PageSize, int PageIndex, stringstrWhere)

{string cond = string.IsNullOrEmpty(strWhere) ? "" : string.Format("where {0}", strWhere);string sql = string.Format("select {0} from `tuiusers` {1} order by {2} limit {3},{4}", fileds, cond, orderstr, (PageIndex - 1) *PageSize, PageSize);

List list = new List();using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{

list= connection.Query(sql).ToList();

}returnlist;

}/// 计算记录数///

///

///

///

public int CalcCount(string where)

{string sql = "select count(1) from `tuiusers`";if (!string.IsNullOrEmpty(where))

{

sql+= "where" + where;

}using (var connection =ConnectionFactory.GetOpenConnection(ConnStr))

{int i = connection.QuerySingle(sql);returni;

}

}

}