/// <summary> /// 获取记录总数 /// </summary> public int GetRecordCount(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) FROM Customer "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } object obj = DbHelperMySql.GetSingle(strSql.ToString()); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 分页获取数据列表 /// </summary> public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { strSql.Append("order by T." + orderby); } else { strSql.Append("order by T.Id desc"); } strSql.Append(")AS Row, T.* from CustomerJPush T "); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(" WHERE " + strWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return(DbHelperMySql.Query(strSql.ToString())); }
/// <summary> /// 得到一个对象实体 /// </summary> public CustomerJPush GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,CustomerId,RegId,Platform,Created from CustomerJPush "); strSql.Append(" where Id=@Id"); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) }; parameters[0].Value = id; DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from CustomerJPush "); strSql.Append(" where Id=@Id"); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) }; parameters[0].Value = id; int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/// <summary> /// 得到一个对象实体 /// </summary> public Customer GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,Account,Password,Sign,NickName,Name,Phone,Email,OpenId,CardType,CardNo,Photo,ReferenceId,IsGrantLibrary,Count,Platform,RegisterIP,RegisterAddredss,Deleted,Created,Modified,RedeemPassword,EnabledFingerprint,HadaCount,IsAutoRedeem,IsSetPassword from Customer "); strSql.Append(" where Id=@Id"); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) }; parameters[0].Value = id; DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public SystemConfig GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,Category,Key,Value,Name,Deleted,Created,Modified from SystemConfig "); strSql.Append(" where Id=@Id"); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32) }; parameters[0].Value = id; DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 增加一条数据 /// </summary> public bool Add(SystemConfig model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into SystemConfig("); strSql.Append("Category,Key,Value,Name,Deleted,Created,Modified)"); strSql.Append(" values ("); strSql.Append("@Category,@Key,@Value,@Name,@Deleted,@Created,@Modified)"); MySqlParameter[] parameters = { new MySqlParameter("@Category", MySqlDbType.VarChar, 2), new MySqlParameter("@Key", MySqlDbType.VarChar, 100), new MySqlParameter("@Value", MySqlDbType.VarChar, 1000), new MySqlParameter("@Name", MySqlDbType.VarChar, 50), new MySqlParameter("@Deleted", MySqlDbType.Bit), new MySqlParameter("@Created", MySqlDbType.DateTime), new MySqlParameter("@Modified", MySqlDbType.DateTime) }; parameters[0].Value = model.Category; parameters[1].Value = model.Key; parameters[2].Value = model.Value; parameters[3].Value = model.Name; parameters[4].Value = model.Deleted; parameters[5].Value = model.Created; parameters[6].Value = model.Modified; int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/* * /// <summary> * /// 分页获取数据列表 * /// </summary> * public DataSet GetList(int PageSize,int PageIndex,string strWhere) * { * MySqlParameter[] parameters = { * new MySqlParameter("@tblName", MySqlDbType.VarChar, 255), * new MySqlParameter("@fldName", MySqlDbType.VarChar, 255), * new MySqlParameter("@PageSize", MySqlDbType.Int32), * new MySqlParameter("@PageIndex", MySqlDbType.Int32), * new MySqlParameter("@IsReCount", MySqlDbType.Bit), * new MySqlParameter("@OrderType", MySqlDbType.Bit), * new MySqlParameter("@strWhere", MySqlDbType.VarChar,1000), * }; * parameters[0].Value = "SystemConfig"; * parameters[1].Value = "Id"; * parameters[2].Value = PageSize; * parameters[3].Value = PageIndex; * parameters[4].Value = 0; * parameters[5].Value = 0; * parameters[6].Value = strWhere; * return DbHelperMySql.RunProcedure("UP_GetRecordByPage",parameters,"ds"); * }*/ #endregion BasicMethod #region ExtensionMethod /// <summary> /// 得到一个对象实体 /// </summary> public SystemConfig GetModel(string key) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT Id,Category,`Key`,`Value`,`Name`,Deleted,Created,Modified FROM SystemConfig "); strSql.Append(" where `Key`=@Key"); MySqlParameter[] parameters = { new MySqlParameter("@Key", MySqlDbType.String) }; parameters[0].Value = key; SystemConfig model = new SystemConfig(); DataSet ds = DbHelperMySql.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 增加一条数据 /// </summary> public bool Add(Customer model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into Customer("); strSql.Append("Account,Password,Sign,NickName,Name,Phone,Email,OpenId,CardType,CardNo,Photo,ReferenceId,IsGrantLibrary,Count,Platform,RegisterIP,RegisterAddredss,Deleted,Created,Modified,RedeemPassword,EnabledFingerprint,HadaCount,IsAutoRedeem,IsSetPassword)"); strSql.Append(" values ("); strSql.Append("@Account,@Password,@Sign,@NickName,@Name,@Phone,@Email,@OpenId,@CardType,@CardNo,@Photo,@ReferenceId,@IsGrantLibrary,@Count,@Platform,@RegisterIP,@RegisterAddredss,@Deleted,@Created,@Modified,@RedeemPassword,@EnabledFingerprint,@HadaCount,@IsAutoRedeem,@IsSetPassword)"); MySqlParameter[] parameters = { new MySqlParameter("@Account", MySqlDbType.VarChar, 50), new MySqlParameter("@Password", MySqlDbType.VarChar, 100), new MySqlParameter("@Sign", MySqlDbType.VarChar, 50), new MySqlParameter("@NickName", MySqlDbType.VarChar, 50), new MySqlParameter("@Name", MySqlDbType.VarChar, 50), new MySqlParameter("@Phone", MySqlDbType.VarChar, 11), new MySqlParameter("@Email", MySqlDbType.VarChar, 100), new MySqlParameter("@OpenId", MySqlDbType.VarChar, 200), new MySqlParameter("@CardType", MySqlDbType.VarChar, 2), new MySqlParameter("@CardNo", MySqlDbType.VarChar, 50), new MySqlParameter("@Photo", MySqlDbType.VarChar, 500), new MySqlParameter("@ReferenceId", MySqlDbType.Int32, 11), new MySqlParameter("@IsGrantLibrary", MySqlDbType.Bit), new MySqlParameter("@Count", MySqlDbType.Int32, 11), new MySqlParameter("@Platform", MySqlDbType.VarChar, 50), new MySqlParameter("@RegisterIP", MySqlDbType.VarChar, 20), new MySqlParameter("@RegisterAddredss", MySqlDbType.VarChar, 20), new MySqlParameter("@Deleted", MySqlDbType.Bit), new MySqlParameter("@Created", MySqlDbType.DateTime), new MySqlParameter("@Modified", MySqlDbType.DateTime), new MySqlParameter("@RedeemPassword", MySqlDbType.VarChar, 200), new MySqlParameter("@EnabledFingerprint", MySqlDbType.Bit), new MySqlParameter("@HadaCount", MySqlDbType.Int32, 11), new MySqlParameter("@IsAutoRedeem", MySqlDbType.Bit), new MySqlParameter("@IsSetPassword", MySqlDbType.Bit) }; parameters[0].Value = model.Account; parameters[1].Value = model.Password; parameters[2].Value = model.Sign; parameters[3].Value = model.NickName; parameters[4].Value = model.Name; parameters[5].Value = model.Phone; parameters[6].Value = model.Email; parameters[7].Value = model.OpenId; parameters[8].Value = model.CardType; parameters[9].Value = model.CardNo; parameters[10].Value = model.Photo; parameters[11].Value = model.ReferenceId; parameters[12].Value = model.IsGrantLibrary; parameters[13].Value = model.Count; parameters[14].Value = model.Platform; parameters[15].Value = model.RegisterIP; parameters[16].Value = model.RegisterAddredss; parameters[17].Value = model.Deleted; parameters[18].Value = model.Created; parameters[19].Value = model.Modified; parameters[20].Value = model.RedeemPassword; parameters[21].Value = model.EnabledFingerprint; parameters[22].Value = model.HadaCount; parameters[23].Value = model.IsAutoRedeem; parameters[24].Value = model.IsSetPassword; int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Customer model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Customer set "); strSql.Append("Account=@Account,"); strSql.Append("Password=@Password,"); strSql.Append("Sign=@Sign,"); strSql.Append("NickName=@NickName,"); strSql.Append("Name=@Name,"); strSql.Append("Phone=@Phone,"); strSql.Append("Email=@Email,"); strSql.Append("OpenId=@OpenId,"); strSql.Append("CardType=@CardType,"); strSql.Append("CardNo=@CardNo,"); strSql.Append("Photo=@Photo,"); strSql.Append("ReferenceId=@ReferenceId,"); strSql.Append("IsGrantLibrary=@IsGrantLibrary,"); strSql.Append("Count=@Count,"); strSql.Append("Platform=@Platform,"); strSql.Append("RegisterIP=@RegisterIP,"); strSql.Append("RegisterAddredss=@RegisterAddredss,"); strSql.Append("Deleted=@Deleted,"); strSql.Append("Created=@Created,"); strSql.Append("Modified=@Modified,"); strSql.Append("RedeemPassword=@RedeemPassword,"); strSql.Append("EnabledFingerprint=@EnabledFingerprint,"); strSql.Append("HadaCount=@HadaCount,"); strSql.Append("IsAutoRedeem=@IsAutoRedeem,"); strSql.Append("IsSetPassword=@IsSetPassword"); strSql.Append(" where Id=@Id"); MySqlParameter[] parameters = { new MySqlParameter("@Account", MySqlDbType.VarChar, 50), new MySqlParameter("@Password", MySqlDbType.VarChar, 100), new MySqlParameter("@Sign", MySqlDbType.VarChar, 50), new MySqlParameter("@NickName", MySqlDbType.VarChar, 50), new MySqlParameter("@Name", MySqlDbType.VarChar, 50), new MySqlParameter("@Phone", MySqlDbType.VarChar, 11), new MySqlParameter("@Email", MySqlDbType.VarChar, 100), new MySqlParameter("@OpenId", MySqlDbType.VarChar, 200), new MySqlParameter("@CardType", MySqlDbType.VarChar, 2), new MySqlParameter("@CardNo", MySqlDbType.VarChar, 50), new MySqlParameter("@Photo", MySqlDbType.VarChar, 500), new MySqlParameter("@ReferenceId", MySqlDbType.Int32, 11), new MySqlParameter("@IsGrantLibrary", MySqlDbType.Bit), new MySqlParameter("@Count", MySqlDbType.Int32, 11), new MySqlParameter("@Platform", MySqlDbType.VarChar, 50), new MySqlParameter("@RegisterIP", MySqlDbType.VarChar, 20), new MySqlParameter("@RegisterAddredss", MySqlDbType.VarChar, 20), new MySqlParameter("@Deleted", MySqlDbType.Bit), new MySqlParameter("@Created", MySqlDbType.DateTime), new MySqlParameter("@Modified", MySqlDbType.DateTime), new MySqlParameter("@RedeemPassword", MySqlDbType.VarChar, 200), new MySqlParameter("@EnabledFingerprint", MySqlDbType.Bit), new MySqlParameter("@HadaCount", MySqlDbType.Int32, 11), new MySqlParameter("@IsAutoRedeem", MySqlDbType.Bit), new MySqlParameter("@IsSetPassword", MySqlDbType.Bit), new MySqlParameter("@Id", MySqlDbType.Int32, 11) }; parameters[0].Value = model.Account; parameters[1].Value = model.Password; parameters[2].Value = model.Sign; parameters[3].Value = model.NickName; parameters[4].Value = model.Name; parameters[5].Value = model.Phone; parameters[6].Value = model.Email; parameters[7].Value = model.OpenId; parameters[8].Value = model.CardType; parameters[9].Value = model.CardNo; parameters[10].Value = model.Photo; parameters[11].Value = model.ReferenceId; parameters[12].Value = model.IsGrantLibrary; parameters[13].Value = model.Count; parameters[14].Value = model.Platform; parameters[15].Value = model.RegisterIP; parameters[16].Value = model.RegisterAddredss; parameters[17].Value = model.Deleted; parameters[18].Value = model.Created; parameters[19].Value = model.Modified; parameters[20].Value = model.RedeemPassword; parameters[21].Value = model.EnabledFingerprint; parameters[22].Value = model.HadaCount; parameters[23].Value = model.IsAutoRedeem; parameters[24].Value = model.IsSetPassword; parameters[25].Value = model.Id; int rows = DbHelperMySql.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }