/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_amount_log model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "user_amount_log("); strSql.Append("user_id,user_name,type,order_no,trade_no,payment_id,value,remark,status,add_time,complete_time)"); strSql.Append(" values ("); strSql.Append("@user_id,@user_name,@type,@order_no,@trade_no,@payment_id,@value,@remark,@status,@add_time,@complete_time)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@user_id", SqlDbType.Int,4), new SqlParameter("@user_name", SqlDbType.NVarChar,100), new SqlParameter("@type", SqlDbType.NVarChar,50), new SqlParameter("@order_no", SqlDbType.NVarChar,100), new SqlParameter("@trade_no", SqlDbType.NVarChar,100), new SqlParameter("@payment_id", SqlDbType.Int,4), new SqlParameter("@value", SqlDbType.Decimal,5), new SqlParameter("@remark", SqlDbType.NVarChar,500), new SqlParameter("@status", SqlDbType.TinyInt,1), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@complete_time", SqlDbType.DateTime), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.user_id; parameters[1].Value = model.user_name; parameters[2].Value = model.type; parameters[3].Value = model.order_no; parameters[4].Value = model.trade_no; parameters[5].Value = model.payment_id; parameters[6].Value = model.value; parameters[7].Value = model.remark; parameters[8].Value = model.status; parameters[9].Value = model.add_time; parameters[10].Value = model.complete_time; parameters[11].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); if (model.status > 0) { StringBuilder strSql2 = new StringBuilder(); strSql2.Append("update " + databaseprefix + "users set amount=amount+" + model.value); strSql2.Append(" where id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters2[0].Value = model.user_id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); } DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[11].Value; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.manager_role model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "manager_role("); strSql.Append("role_name,role_type,is_sys)"); strSql.Append(" values ("); strSql.Append("@role_name,@role_type,@is_sys)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@role_name", SqlDbType.NVarChar,100), new SqlParameter("@role_type", SqlDbType.TinyInt,1), new SqlParameter("@is_sys", SqlDbType.TinyInt,1), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.role_name; parameters[1].Value = model.role_type; parameters[2].Value = model.is_sys; parameters[3].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); StringBuilder strSql2; if (model.manager_role_values != null) { foreach (Model.manager_role_value modelt in model.manager_role_values) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "manager_role_value("); strSql2.Append("role_id,nav_name,action_type)"); strSql2.Append(" values ("); strSql2.Append("@role_id,@nav_name,@action_type)"); SqlParameter[] parameters2 = { new SqlParameter("@role_id", SqlDbType.Int,4), new SqlParameter("@nav_name", SqlDbType.NVarChar,100), new SqlParameter("@action_type", SqlDbType.NVarChar,50)}; parameters2[0].Direction = ParameterDirection.InputOutput; parameters2[1].Value = modelt.nav_name; parameters2[2].Value = modelt.action_type; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); } } DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[3].Value; }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.user_point_log model, bool is_upgrade) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "user_point_log("); strSql.Append("user_id,user_name,value,remark,add_time)"); strSql.Append(" values ("); strSql.Append("@user_id,@user_name,@value,@remark,@add_time)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@user_id", SqlDbType.Int,4), new SqlParameter("@user_name", SqlDbType.NVarChar,100), new SqlParameter("@value", SqlDbType.Int,4), new SqlParameter("@remark", SqlDbType.NVarChar,500), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.user_id; parameters[1].Value = model.user_name; parameters[2].Value = model.value; parameters[3].Value = model.remark; parameters[4].Value = model.add_time; parameters[5].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); StringBuilder strSql2 = new StringBuilder(); strSql2.Append("update " + databaseprefix + "users set point=point+" + model.value); if (model.value > 0 && is_upgrade) { strSql2.Append(",exp=exp+" + model.value); } strSql2.Append(" where id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters2[0].Value = model.user_id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[5].Value; }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //删除商品的属性值 StringBuilder strSql1 = new StringBuilder(); strSql1.Append("delete from wx_shop_productAttr_value "); strSql1.Append(" where productId=@productId "); SqlParameter[] parameters1 = { new SqlParameter("@productId", SqlDbType.Int,4)}; parameters1[0].Value = id; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1); sqllist.Add(cmd); //删除商品配件 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from wx_shop_sku "); strSql2.Append(" where productId=@productId "); SqlParameter[] parameters2 = { new SqlParameter("@productId", SqlDbType.Int,4)}; parameters2[0].Value = id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //删除图片相册 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("delete from wx_shop_albums "); strSql3.Append(" where productId=@productId "); SqlParameter[] parameters3 = { new SqlParameter("@productId", SqlDbType.Int,4)}; parameters3[0].Value = id; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); ////删除会员组价格 //StringBuilder strSql4 = new StringBuilder(); //strSql4.Append("delete from " + databaseprefix + "user_group_price "); //strSql4.Append(" where article_id=@article_id "); //SqlParameter[] parameters4 = { // new SqlParameter("@article_id", SqlDbType.Int,4)}; //parameters4[0].Value = id; //cmd = new CommandInfo(strSql4.ToString(), parameters4); //sqllist.Add(cmd); //删除评论 StringBuilder strSql5 = new StringBuilder(); strSql5.Append("delete from wx_shop_comment "); strSql5.Append(" where productId=@productId "); SqlParameter[] parameters5 = { new SqlParameter("@productId", SqlDbType.Int,4)}; parameters5[0].Value = id; cmd = new CommandInfo(strSql5.ToString(), parameters5); sqllist.Add(cmd); //删除购物车 StringBuilder strSql6 = new StringBuilder(); strSql6.Append("delete from wx_shop_cart "); strSql6.Append(" where productId=@productId "); SqlParameter[] parameters6= { new SqlParameter("@productId", SqlDbType.Int,4)}; parameters6[0].Value = id; cmd = new CommandInfo(strSql6.ToString(), parameters6); sqllist.Add(cmd); //删除主表 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from wx_shop_product "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.wx_shop_product model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into wx_shop_product("); strSql.Append("wid,categoryId,brandId,sku,productName,shortDesc,unit,weight,description,seo_title,seo_keywords,seo_description,focusImgUrl,thumbnailsUrll,recommended,latest,hotsale,specialOffer,costPrice,marketPrice,salePrice,upselling,stock,addDate,vistiCounts,sort_id,productionDate,ExpiryEndDate,updateDate,catalogId)"); strSql.Append(" values ("); strSql.Append("@wid,@categoryId,@brandId,@sku,@productName,@shortDesc,@unit,@weight,@description,@seo_title,@seo_keywords,@seo_description,@focusImgUrl,@thumbnailsUrll,@recommended,@latest,@hotsale,@specialOffer,@costPrice,@marketPrice,@salePrice,@upselling,@stock,@addDate,@vistiCounts,@sort_id,@productionDate,@ExpiryEndDate,@updateDate,@catalogId)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@wid", SqlDbType.Int,4), new SqlParameter("@categoryId", SqlDbType.Int,4), new SqlParameter("@brandId", SqlDbType.Int,4), new SqlParameter("@sku", SqlDbType.VarChar,20), new SqlParameter("@productName", SqlDbType.VarChar,500), new SqlParameter("@shortDesc", SqlDbType.VarChar,500), new SqlParameter("@unit", SqlDbType.VarChar,10), new SqlParameter("@weight", SqlDbType.Float,8), new SqlParameter("@description", SqlDbType.VarChar,2000), new SqlParameter("@seo_title", SqlDbType.VarChar,200), new SqlParameter("@seo_keywords", SqlDbType.VarChar,300), new SqlParameter("@seo_description", SqlDbType.VarChar,500), new SqlParameter("@focusImgUrl", SqlDbType.VarChar,800), new SqlParameter("@thumbnailsUrll", SqlDbType.VarChar,800), new SqlParameter("@recommended", SqlDbType.Bit,1), new SqlParameter("@latest", SqlDbType.Bit,1), new SqlParameter("@hotsale", SqlDbType.Bit,1), new SqlParameter("@specialOffer", SqlDbType.Bit,1), new SqlParameter("@costPrice", SqlDbType.Float,8), new SqlParameter("@marketPrice", SqlDbType.Float,8), new SqlParameter("@salePrice", SqlDbType.Float,8), new SqlParameter("@upselling", SqlDbType.Bit,1), new SqlParameter("@stock", SqlDbType.Int,4), new SqlParameter("@addDate", SqlDbType.DateTime), new SqlParameter("@vistiCounts", SqlDbType.Int,4), new SqlParameter("@sort_id", SqlDbType.Int,4), new SqlParameter("@productionDate", SqlDbType.DateTime), new SqlParameter("@ExpiryEndDate", SqlDbType.DateTime), new SqlParameter("@updateDate", SqlDbType.DateTime), new SqlParameter("@catalogId", SqlDbType.Int,4), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.wid; parameters[1].Value = model.categoryId; parameters[2].Value = model.brandId; parameters[3].Value = model.sku; parameters[4].Value = model.productName; parameters[5].Value = model.shortDesc; parameters[6].Value = model.unit; parameters[7].Value = model.weight; parameters[8].Value = model.description; parameters[9].Value = model.seo_title; parameters[10].Value = model.seo_keywords; parameters[11].Value = model.seo_description; parameters[12].Value = model.focusImgUrl; parameters[13].Value = model.thumbnailsUrll; parameters[14].Value = model.recommended; parameters[15].Value = model.latest; parameters[16].Value = model.hotsale; parameters[17].Value = model.specialOffer; parameters[18].Value = model.costPrice; parameters[19].Value = model.marketPrice; parameters[20].Value = model.salePrice; parameters[21].Value = model.upselling; parameters[22].Value = model.stock; parameters[23].Value = model.addDate; parameters[24].Value = model.vistiCounts; parameters[25].Value = model.sort_id; parameters[26].Value = model.productionDate; parameters[27].Value = model.ExpiryEndDate; parameters[28].Value = model.updateDate; parameters[29].Value = model.catalogId; parameters[30].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); #region //图片相册 if (model.albums != null) { StringBuilder strSql2; foreach (Model.wx_shop_albums modelt in model.albums) { strSql2 = new StringBuilder(); strSql2.Append("insert into wx_shop_albums("); strSql2.Append("productId,thumb_path,original_path,remark)"); strSql2.Append(" values ("); strSql2.Append("@productId,@thumb_path,@original_path,@remark)"); SqlParameter[] parameters2 = { new SqlParameter("@productId", SqlDbType.Int,4), new SqlParameter("@thumb_path", SqlDbType.NVarChar,255), new SqlParameter("@original_path", SqlDbType.NVarChar,255), new SqlParameter("@remark", SqlDbType.NVarChar,500)}; parameters2[0].Direction = ParameterDirection.InputOutput; parameters2[1].Value = modelt.thumb_path; parameters2[2].Value = modelt.original_path; parameters2[3].Value = modelt.remark; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); } } #endregion #region //商品属性 if (model.attrs != null) { StringBuilder strSql3; foreach (Model.wx_shop_productAttr_value modelt in model.attrs) { strSql3 = new StringBuilder(); strSql3.Append("insert into wx_shop_productAttr_value("); strSql3.Append("productId,attributeId,paValue)"); strSql3.Append(" values ("); strSql3.Append("@productId,@attributeId,@paValue)"); SqlParameter[] parameters3= { new SqlParameter("@productId", SqlDbType.Int,4), new SqlParameter("@attributeId", SqlDbType.Int,4), new SqlParameter("@paValue", SqlDbType.NVarChar,300)}; parameters3[0].Direction = ParameterDirection.InputOutput; parameters3[1].Value = modelt.attributeId; parameters3[2].Value = modelt.paValue; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); } } #endregion #region //商品配件sku|规格 if (model.skulist != null) { StringBuilder strSql4; foreach (Model.wx_shop_sku modelt in model.skulist) { strSql4 = new StringBuilder(); strSql4.Append("insert into wx_shop_sku("); strSql4.Append("productId,attributeId,attributeValue,price)"); strSql4.Append(" values ("); strSql4.Append("@productId,@attributeId,@attributeValue,@price)"); SqlParameter[] parameters4 = { new SqlParameter("@productId", SqlDbType.Int,4), new SqlParameter("@attributeId", SqlDbType.Int,4), new SqlParameter("@attributeValue", SqlDbType.NVarChar,300), new SqlParameter("@price", SqlDbType.Float,10), }; parameters4[0].Direction = ParameterDirection.InputOutput; parameters4[1].Value = modelt.attributeId; parameters4[2].Value = modelt.attributeValue; parameters4[3].Value = modelt.price; cmd = new CommandInfo(strSql4.ToString(), parameters4); sqllist.Add(cmd); } } #endregion DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[30].Value; }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //获取用户旧数据 Model.users model = GetModel(id); if (model == null) { return false; } List<CommandInfo> sqllist = new List<CommandInfo>(); //删除积分记录 StringBuilder strSql1 = new StringBuilder(); strSql1.Append("delete from " + databaseprefix + "user_point_log "); strSql1.Append(" where user_id=@id"); SqlParameter[] parameters1 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters1[0].Value = id; CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1); sqllist.Add(cmd); //删除金额记录 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "user_amount_log "); strSql2.Append(" where user_id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters2[0].Value = id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //删除短消息 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("delete from " + databaseprefix + "user_message "); strSql3.Append(" where post_user_name=@post_user_name or accept_user_name=@accept_user_name"); SqlParameter[] parameters3 = { new SqlParameter("@post_user_name", SqlDbType.NVarChar,100), new SqlParameter("@accept_user_name", SqlDbType.NVarChar,100)}; parameters3[0].Value = model.user_name; parameters3[1].Value = model.user_name; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); //删除申请码 StringBuilder strSql4 = new StringBuilder(); strSql4.Append("delete from " + databaseprefix + "user_code "); strSql4.Append(" where user_id=@id"); SqlParameter[] parameters4 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters4[0].Value = id; cmd = new CommandInfo(strSql4.ToString(), parameters4); sqllist.Add(cmd); //删除登录日志 StringBuilder strSql5 = new StringBuilder(); strSql5.Append("delete from " + databaseprefix + "user_login_log "); strSql5.Append(" where user_id=@id"); SqlParameter[] parameters5 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters5[0].Value = id; cmd = new CommandInfo(strSql5.ToString(), parameters5); sqllist.Add(cmd); //删除用户记录 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "users "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 删除一条数据,及子表所有相关数据 /// </summary> public bool Delete(int id) { List<CommandInfo> sqllist = new List<CommandInfo>(); //删除管理角色权限 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "manager_role_value "); strSql.Append(" where role_id=@role_id"); SqlParameter[] parameters = { new SqlParameter("@role_id", SqlDbType.Int,4)}; parameters[0].Value = id; CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); //删除管理角色 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "manager_role "); strSql2.Append(" where id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters2[0].Value = id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.user_amount_log model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "user_amount_log set "); strSql.Append("user_id=@user_id,"); strSql.Append("user_name=@user_name,"); strSql.Append("type=@type,"); strSql.Append("order_no=@order_no,"); strSql.Append("trade_no=@trade_no,"); strSql.Append("payment_id=@payment_id,"); strSql.Append("value=@value,"); strSql.Append("remark=@remark,"); strSql.Append("status=@status,"); strSql.Append("add_time=@add_time,"); strSql.Append("complete_time=@complete_time"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@user_id", SqlDbType.Int,4), new SqlParameter("@user_name", SqlDbType.NVarChar,100), new SqlParameter("@type", SqlDbType.NVarChar,50), new SqlParameter("@order_no", SqlDbType.NVarChar,100), new SqlParameter("@trade_no", SqlDbType.NVarChar,100), new SqlParameter("@payment_id", SqlDbType.Int,4), new SqlParameter("@value", SqlDbType.Decimal,5), new SqlParameter("@remark", SqlDbType.NVarChar,500), new SqlParameter("@status", SqlDbType.TinyInt,1), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@complete_time", SqlDbType.DateTime), new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = model.user_id; parameters[1].Value = model.user_name; parameters[2].Value = model.type; parameters[3].Value = model.order_no; parameters[4].Value = model.trade_no; parameters[5].Value = model.payment_id; parameters[6].Value = model.value; parameters[7].Value = model.remark; parameters[8].Value = model.status; parameters[9].Value = model.add_time; parameters[10].Value = model.complete_time; parameters[11].Value = model.id; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); if (model.status > 0) { StringBuilder strSql2 = new StringBuilder(); strSql2.Append("update " + databaseprefix + "users set amount=amount+" + model.value); strSql2.Append(" where id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters2[0].Value = model.user_id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); } int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //取得相册MODEL List<Model.article_albums> albumsList = new DAL.article_albums(databaseprefix).GetList(id); //取得附件MODEL List<Model.article_attach> attachList = new DAL.article_attach(databaseprefix).GetList(id); //删除扩展字段表 StringBuilder strSql1 = new StringBuilder(); strSql1.Append("delete from " + databaseprefix + "article_attribute_value "); strSql1.Append(" where article_id=@article_id "); SqlParameter[] parameters1 = { new SqlParameter("@article_id", SqlDbType.Int,4)}; parameters1[0].Value = id; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1); sqllist.Add(cmd); //删除图片相册 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "article_albums "); strSql2.Append(" where article_id=@article_id "); SqlParameter[] parameters2 = { new SqlParameter("@article_id", SqlDbType.Int,4)}; parameters2[0].Value = id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //删除附件 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("delete from " + databaseprefix + "article_attach "); strSql3.Append(" where article_id=@article_id "); SqlParameter[] parameters3 = { new SqlParameter("@article_id", SqlDbType.Int,4)}; parameters3[0].Value = id; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); //删除会员组价格 StringBuilder strSql4 = new StringBuilder(); strSql4.Append("delete from " + databaseprefix + "user_group_price "); strSql4.Append(" where article_id=@article_id "); SqlParameter[] parameters4 = { new SqlParameter("@article_id", SqlDbType.Int,4)}; parameters4[0].Value = id; cmd = new CommandInfo(strSql4.ToString(), parameters4); sqllist.Add(cmd); //删除评论 StringBuilder strSql5 = new StringBuilder(); strSql5.Append("delete from " + databaseprefix + "article_comment "); strSql5.Append(" where article_id=@article_id "); SqlParameter[] parameters5 = { new SqlParameter("@article_id", SqlDbType.Int,4)}; parameters5[0].Value = id; cmd = new CommandInfo(strSql5.ToString(), parameters5); sqllist.Add(cmd); //删除主表 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "article "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { new DAL.article_albums(databaseprefix).DeleteFile(albumsList); //删除图片 new DAL.article_attach(databaseprefix).DeleteFile(attachList); //删除附件 return true; } else { return false; } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.article model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "article("); strSql.Append("channel_id,category_id,call_index,title,link_url,img_url,seo_title,seo_keywords,seo_description,zhaiyao,content,sort_id,click,status,groupids_view,vote_id,is_top,is_red,is_hot,is_slide,is_sys,is_msg,user_name,add_time,update_time,wid)"); strSql.Append(" values ("); strSql.Append("@channel_id,@category_id,@call_index,@title,@link_url,@img_url,@seo_title,@seo_keywords,@seo_description,@zhaiyao,@content,@sort_id,@click,@status,@groupids_view,@vote_id,@is_top,@is_red,@is_hot,@is_slide,@is_sys,@is_msg,@user_name,@add_time,@update_time,@wid)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@channel_id", SqlDbType.Int,4), new SqlParameter("@category_id", SqlDbType.Int,4), new SqlParameter("@call_index", SqlDbType.NVarChar,50), new SqlParameter("@title", SqlDbType.NVarChar,100), new SqlParameter("@link_url", SqlDbType.NVarChar,255), new SqlParameter("@img_url", SqlDbType.NVarChar,255), new SqlParameter("@seo_title", SqlDbType.NVarChar,255), new SqlParameter("@seo_keywords", SqlDbType.NVarChar,255), new SqlParameter("@seo_description", SqlDbType.NVarChar,255), new SqlParameter("@zhaiyao", SqlDbType.NVarChar,255), new SqlParameter("@content", SqlDbType.NText), new SqlParameter("@sort_id", SqlDbType.Int,4), new SqlParameter("@click", SqlDbType.Int,4), new SqlParameter("@status", SqlDbType.TinyInt,1), new SqlParameter("@groupids_view", SqlDbType.NVarChar,255), new SqlParameter("@vote_id", SqlDbType.Int,4), new SqlParameter("@is_top", SqlDbType.TinyInt,1), new SqlParameter("@is_red", SqlDbType.TinyInt,1), new SqlParameter("@is_hot", SqlDbType.TinyInt,1), new SqlParameter("@is_slide", SqlDbType.TinyInt,1), new SqlParameter("@is_sys", SqlDbType.TinyInt,1), new SqlParameter("@is_msg", SqlDbType.TinyInt,1), new SqlParameter("@user_name", SqlDbType.NVarChar,100), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@update_time", SqlDbType.DateTime), new SqlParameter("@wid", SqlDbType.Int,4), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.channel_id; parameters[1].Value = model.category_id; parameters[2].Value = model.call_index; parameters[3].Value = model.title; parameters[4].Value = model.link_url; parameters[5].Value = model.img_url; parameters[6].Value = model.seo_title; parameters[7].Value = model.seo_keywords; parameters[8].Value = model.seo_description; parameters[9].Value = model.zhaiyao; parameters[10].Value = model.content; parameters[11].Value = model.sort_id; parameters[12].Value = model.click; parameters[13].Value = model.status; parameters[14].Value = model.groupids_view; parameters[15].Value = model.vote_id; parameters[16].Value = model.is_top; parameters[17].Value = model.is_red; parameters[18].Value = model.is_hot; parameters[19].Value = model.is_slide; parameters[20].Value = model.is_sys; parameters[21].Value = model.is_msg; parameters[22].Value = model.user_name; parameters[23].Value = model.add_time; parameters[24].Value = model.update_time; parameters[25].Value = model.wid; parameters[26].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); //添加扩展字段 StringBuilder strSql2 = new StringBuilder(); StringBuilder strFieldName = new StringBuilder(); //字段列表 StringBuilder strFieldVar = new StringBuilder(); //字段声明 SqlParameter[] parameters2 = new SqlParameter[model.fields.Count + 1]; int i = 1; strFieldName.Append("article_id"); strFieldVar.Append("@article_id"); parameters2[0] = new SqlParameter("@article_id", SqlDbType.Int, 4); parameters2[0].Direction = ParameterDirection.InputOutput; foreach (KeyValuePair<string, string> kvp in model.fields) { strFieldName.Append("," + kvp.Key); strFieldVar.Append(",@" + kvp.Key); if (kvp.Value.Length <= 4000) { parameters2[i] = new SqlParameter("@" + kvp.Key, SqlDbType.NVarChar, kvp.Value.Length); } else { parameters2[i] = new SqlParameter("@" + kvp.Key, SqlDbType.NText); } parameters2[i].Value = kvp.Value; i++; } strSql2.Append("insert into " + databaseprefix + "article_attribute_value("); strSql2.Append(strFieldName.ToString() + ")"); strSql2.Append(" values ("); strSql2.Append(strFieldVar.ToString() + ")"); cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //图片相册 if (model.albums != null) { StringBuilder strSql3; foreach (Model.article_albums modelt in model.albums) { strSql3 = new StringBuilder(); strSql3.Append("insert into " + databaseprefix + "article_albums("); strSql3.Append("article_id,thumb_path,original_path,remark)"); strSql3.Append(" values ("); strSql3.Append("@article_id,@thumb_path,@original_path,@remark)"); SqlParameter[] parameters3 = { new SqlParameter("@article_id", SqlDbType.Int,4), new SqlParameter("@thumb_path", SqlDbType.NVarChar,255), new SqlParameter("@original_path", SqlDbType.NVarChar,255), new SqlParameter("@remark", SqlDbType.NVarChar,500)}; parameters3[0].Direction = ParameterDirection.InputOutput; parameters3[1].Value = modelt.thumb_path; parameters3[2].Value = modelt.original_path; parameters3[3].Value = modelt.remark; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); } } //文章附件 if (model.attach != null) { StringBuilder strSql4; foreach (Model.article_attach modelt in model.attach) { strSql4 = new StringBuilder(); strSql4.Append("insert into " + databaseprefix + "article_attach("); strSql4.Append("article_id,file_name,file_path,file_size,file_ext,down_num,point)"); strSql4.Append(" values ("); strSql4.Append("@article_id,@file_name,@file_path,@file_size,@file_ext,@down_num,@point)"); SqlParameter[] parameters4 = { new SqlParameter("@article_id", SqlDbType.Int,4), new SqlParameter("@file_name", SqlDbType.NVarChar,100), new SqlParameter("@file_path", SqlDbType.NVarChar,255), new SqlParameter("@file_size", SqlDbType.Int,4), new SqlParameter("@file_ext", SqlDbType.NVarChar,20), new SqlParameter("@down_num", SqlDbType.Int,4), new SqlParameter("@point", SqlDbType.Int,4)}; parameters4[0].Direction = ParameterDirection.InputOutput; parameters4[1].Value = modelt.file_name; parameters4[2].Value = modelt.file_path; parameters4[3].Value = modelt.file_size; parameters4[4].Value = modelt.file_ext; parameters4[5].Value = modelt.down_num; parameters4[6].Value = modelt.point; cmd = new CommandInfo(strSql4.ToString(), parameters4); sqllist.Add(cmd); } } //用户组价格 if (model.group_price != null) { StringBuilder strSql5; foreach (Model.user_group_price models in model.group_price) { strSql5 = new StringBuilder(); strSql5.Append("insert into " + databaseprefix + "user_group_price("); strSql5.Append("article_id,group_id,price)"); strSql5.Append(" values ("); strSql5.Append("@article_id,@group_id,@price)"); SqlParameter[] parameters5 = { new SqlParameter("@article_id", SqlDbType.Int,4), new SqlParameter("@group_id", SqlDbType.Int,4), new SqlParameter("@price", SqlDbType.Decimal,5)}; parameters5[0].Direction = ParameterDirection.InputOutput; parameters5[1].Value = models.group_id; parameters5[2].Value = models.price; cmd = new CommandInfo(strSql5.ToString(), parameters5); sqllist.Add(cmd); } } DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[26].Value; }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { //删除会员组价格 StringBuilder strSql1 = new StringBuilder(); strSql1.Append("delete from " + databaseprefix + "user_group_price "); strSql1.Append(" where group_id=@group_id "); SqlParameter[] parameters1 = { new SqlParameter("@group_id", SqlDbType.Int,4)}; parameters1[0].Value = id; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql1.ToString(), parameters1); sqllist.Add(cmd); //删除主表 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "user_groups "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 增加一条数据,及其子表数据 /// </summary> public int Add(Model.orders model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "orders("); strSql.Append("order_no,trade_no,user_id,user_name,payment_id,payment_fee,payment_status,payment_time,express_id,express_no,express_fee,express_status,express_time,accept_name,post_code,telphone,mobile,area,address,message,remark,payable_amount,real_amount,order_amount,point,status,add_time,confirm_time,complete_time,wid,openid,modelName,modelCode,modelActionName,modelActionId,orderSubject,city,district,notify_id,pay_info,isSubscribe,fahuoCode,fahuoMsg)"); strSql.Append(" values ("); strSql.Append("@order_no,@trade_no,@user_id,@user_name,@payment_id,@payment_fee,@payment_status,@payment_time,@express_id,@express_no,@express_fee,@express_status,@express_time,@accept_name,@post_code,@telphone,@mobile,@area,@address,@message,@remark,@payable_amount,@real_amount,@order_amount,@point,@status,@add_time,@confirm_time,@complete_time,@wid,@openid,@modelName,@modelCode,@modelActionName,@modelActionId,@orderSubject,@city,@district,@notify_id,@pay_info,@isSubscribe,@fahuoCode,@fahuoMsg)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@order_no", SqlDbType.NVarChar,100), new SqlParameter("@trade_no", SqlDbType.NVarChar,100), new SqlParameter("@user_id", SqlDbType.Int,4), new SqlParameter("@user_name", SqlDbType.NVarChar,100), new SqlParameter("@payment_id", SqlDbType.Int,4), new SqlParameter("@payment_fee", SqlDbType.Decimal,5), new SqlParameter("@payment_status", SqlDbType.TinyInt,1), new SqlParameter("@payment_time", SqlDbType.DateTime), new SqlParameter("@express_id", SqlDbType.Int,4), new SqlParameter("@express_no", SqlDbType.NVarChar,100), new SqlParameter("@express_fee", SqlDbType.Decimal,5), new SqlParameter("@express_status", SqlDbType.TinyInt,1), new SqlParameter("@express_time", SqlDbType.DateTime), new SqlParameter("@accept_name", SqlDbType.NVarChar,50), new SqlParameter("@post_code", SqlDbType.NVarChar,20), new SqlParameter("@telphone", SqlDbType.NVarChar,30), new SqlParameter("@mobile", SqlDbType.NVarChar,20), new SqlParameter("@area", SqlDbType.NVarChar,100), new SqlParameter("@address", SqlDbType.NVarChar,500), new SqlParameter("@message", SqlDbType.NVarChar,500), new SqlParameter("@remark", SqlDbType.NVarChar,500), new SqlParameter("@payable_amount", SqlDbType.Decimal,5), new SqlParameter("@real_amount", SqlDbType.Decimal,5), new SqlParameter("@order_amount", SqlDbType.Decimal,5), new SqlParameter("@point", SqlDbType.Int,4), new SqlParameter("@status", SqlDbType.TinyInt,1), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@confirm_time", SqlDbType.DateTime), new SqlParameter("@complete_time", SqlDbType.DateTime), new SqlParameter("@wid", SqlDbType.Int,4), new SqlParameter("@openid", SqlDbType.VarChar,100), new SqlParameter("@modelName", SqlDbType.VarChar,300), new SqlParameter("@modelCode", SqlDbType.VarChar,50), new SqlParameter("@modelActionName", SqlDbType.VarChar,500), new SqlParameter("@modelActionId", SqlDbType.Int,4), new SqlParameter("@orderSubject", SqlDbType.VarChar,500), new SqlParameter("@city", SqlDbType.VarChar,100), new SqlParameter("@district", SqlDbType.VarChar,100), new SqlParameter("@notify_id", SqlDbType.VarChar,128), new SqlParameter("@pay_info", SqlDbType.VarChar,200), new SqlParameter("@isSubscribe", SqlDbType.Bit,1), new SqlParameter("@fahuoCode", SqlDbType.VarChar,50), new SqlParameter("@fahuoMsg", SqlDbType.VarChar,500), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.order_no; parameters[1].Value = model.trade_no; parameters[2].Value = model.user_id; parameters[3].Value = model.user_name; parameters[4].Value = model.payment_id; parameters[5].Value = model.payment_fee; parameters[6].Value = model.payment_status; parameters[7].Value = model.payment_time; parameters[8].Value = model.express_id; parameters[9].Value = model.express_no; parameters[10].Value = model.express_fee; parameters[11].Value = model.express_status; parameters[12].Value = model.express_time; parameters[13].Value = model.accept_name; parameters[14].Value = model.post_code; parameters[15].Value = model.telphone; parameters[16].Value = model.mobile; parameters[17].Value = model.area; parameters[18].Value = model.address; parameters[19].Value = model.message; parameters[20].Value = model.remark; parameters[21].Value = model.payable_amount; parameters[22].Value = model.real_amount; parameters[23].Value = model.order_amount; parameters[24].Value = model.point; parameters[25].Value = model.status; parameters[26].Value = model.add_time; parameters[27].Value = model.confirm_time; parameters[28].Value = model.complete_time; parameters[29].Value = model.wid; parameters[30].Value = model.openid; parameters[31].Value = model.modelName; parameters[32].Value = model.modelCode; parameters[33].Value = model.modelActionName; parameters[34].Value = model.modelActionId; parameters[35].Value = model.orderSubject; parameters[36].Value = model.city; parameters[37].Value = model.district; parameters[38].Value = model.notify_id; parameters[39].Value = model.pay_info; parameters[40].Value = model.isSubscribe; parameters[41].Value = model.fahuoCode; parameters[42].Value = model.fahuoMsg; parameters[43].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); //订单商品列表 if (model.order_goods != null) { StringBuilder strSql2; foreach (Model.order_goods models in model.order_goods) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "order_goods("); strSql2.Append("order_id,goods_id,goods_title,goods_price,real_price,quantity,point)"); strSql2.Append(" values ("); strSql2.Append("@order_id,@goods_id,@goods_title,@goods_price,@real_price,@quantity,@point)"); SqlParameter[] parameters2 = { new SqlParameter("@order_id", SqlDbType.Int,4), new SqlParameter("@goods_id", SqlDbType.Int,4), new SqlParameter("@goods_title", SqlDbType.NVarChar,100), new SqlParameter("@goods_price", SqlDbType.Decimal,5), new SqlParameter("@real_price", SqlDbType.Decimal,5), new SqlParameter("@quantity", SqlDbType.Int,4), new SqlParameter("@point", SqlDbType.Int,4)}; parameters2[0].Direction = ParameterDirection.InputOutput; parameters2[1].Value = models.goods_id; parameters2[2].Value = models.goods_title; parameters2[3].Value = models.goods_price; parameters2[4].Value = models.real_price; parameters2[5].Value = models.quantity; parameters2[6].Value = models.point; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); } } DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[43].Value; }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { List<CommandInfo> sqllist = new List<CommandInfo>(); //分店 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from wx_ucard_store_fendian "); strSql2.Append(" where sId=@sId "); SqlParameter[] parameters2 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters2[0].Value = id; CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //删除等级 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("delete from wx_ucard_udegree "); strSql3.Append(" where sid=@sid "); SqlParameter[] parameters3 = { new SqlParameter("@sid", SqlDbType.Int,4)}; parameters3[0].Value = id; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); //卡面设置 StringBuilder strSql4 = new StringBuilder(); strSql4.Append("delete from wx_ucard_cardinfo "); strSql4.Append(" where sId=@sId "); SqlParameter[] parameters4 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters4[0].Value = id; cmd = new CommandInfo(strSql4.ToString(), parameters4); sqllist.Add(cmd); //通知 StringBuilder strSql5 = new StringBuilder(); strSql5.Append("delete from wx_ucard_notice "); strSql5.Append(" where sId=@sId "); SqlParameter[] parameters5 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters5[0].Value = id; cmd = new CommandInfo(strSql5.ToString(), parameters5); sqllist.Add(cmd); //特权 StringBuilder strSql6 = new StringBuilder(); strSql6.Append("delete from wx_ucard_privileges "); strSql6.Append(" where sId=@sId "); SqlParameter[] parameters6 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters6[0].Value = id; cmd = new CommandInfo(strSql6.ToString(), parameters6); sqllist.Add(cmd); //优惠券 StringBuilder strSql7 = new StringBuilder(); strSql7.Append("delete from wx_ucard_ticket "); strSql7.Append(" where sId=@sId "); SqlParameter[] parameters7 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters7[0].Value = id; cmd = new CommandInfo(strSql7.ToString(), parameters7); sqllist.Add(cmd); //礼品券 StringBuilder strSql8 = new StringBuilder(); strSql8.Append("delete from wx_ucard_gift "); strSql8.Append(" where sId=@sId "); SqlParameter[] parameters8 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters8[0].Value = id; cmd = new CommandInfo(strSql8.ToString(), parameters8); sqllist.Add(cmd); //会员 StringBuilder strSql9 = new StringBuilder(); strSql9.Append("delete from wx_ucard_users "); strSql9.Append(" where sId=@sId "); SqlParameter[] parameters9 = { new SqlParameter("@sId", SqlDbType.Int,4)}; parameters9[0].Value = id; cmd = new CommandInfo(strSql9.ToString(), parameters9); sqllist.Add(cmd); ////会员领卡信息 //StringBuilder strSql10 = new StringBuilder(); //strSql10.Append("delete from wx_ucard_usercard "); //strSql10.Append(" where sid=@sid "); //SqlParameter[] parameters10 = { // new SqlParameter("@sId", SqlDbType.Int,4)}; //parameters10[0].Value = id; //cmd = new CommandInfo(strSql10.ToString(), parameters10); //sqllist.Add(cmd); //主表 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from wx_ucard_store "); strSql.Append(" where id=@id "); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.article_attribute_field model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "article_attribute_field("); strSql.Append("name,title,control_type,data_type,data_length,data_place,item_option,default_value,is_required,is_password,is_html,editor_type,valid_tip_msg,valid_error_msg,valid_pattern,sort_id,is_sys)"); strSql.Append(" values ("); strSql.Append("@name,@title,@control_type,@data_type,@data_length,@data_place,@item_option,@default_value,@is_required,@is_password,@is_html,@editor_type,@valid_tip_msg,@valid_error_msg,@valid_pattern,@sort_id,@is_sys)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@name", SqlDbType.NVarChar,100), new SqlParameter("@title", SqlDbType.NVarChar,100), new SqlParameter("@control_type", SqlDbType.NVarChar,50), new SqlParameter("@data_type", SqlDbType.NVarChar,50), new SqlParameter("@data_length", SqlDbType.Int,4), new SqlParameter("@data_place", SqlDbType.TinyInt,1), new SqlParameter("@item_option", SqlDbType.NText), new SqlParameter("@default_value", SqlDbType.NText), new SqlParameter("@is_required", SqlDbType.TinyInt,1), new SqlParameter("@is_password", SqlDbType.TinyInt,1), new SqlParameter("@is_html", SqlDbType.TinyInt,1), new SqlParameter("@editor_type", SqlDbType.TinyInt,1), new SqlParameter("@valid_tip_msg", SqlDbType.NVarChar,255), new SqlParameter("@valid_error_msg", SqlDbType.NVarChar,255), new SqlParameter("@valid_pattern", SqlDbType.NVarChar,500), new SqlParameter("@sort_id", SqlDbType.Int,4), new SqlParameter("@is_sys", SqlDbType.TinyInt,1), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.name; parameters[1].Value = model.title; parameters[2].Value = model.control_type; parameters[3].Value = model.data_type; parameters[4].Value = model.data_length; parameters[5].Value = model.data_place; parameters[6].Value = model.item_option; parameters[7].Value = model.default_value; parameters[8].Value = model.is_required; parameters[9].Value = model.is_password; parameters[10].Value = model.is_html; parameters[11].Value = model.editor_type; parameters[12].Value = model.valid_tip_msg; parameters[13].Value = model.valid_error_msg; parameters[14].Value = model.valid_pattern; parameters[15].Value = model.sort_id; parameters[16].Value = model.is_sys; parameters[17].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); //增加扩展字段表中一列 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("alter table " + databaseprefix + "article_attribute_value add " + model.name + " " + model.data_type); SqlParameter[] parameters2 = { }; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[17].Value; }
/// <summary> /// 新增积分记录,比如签到 /// </summary> /// <param name="model"></param> /// <param name="isEW">额外的奖励,1为连续6天签到</param> /// <returns></returns> public int AddJiFen(Model.wx_ucard_users_consumeinfo model, int isEW) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into wx_ucard_users_consumeinfo("); strSql.Append("sId,uid,moduleType,moduleTypeId,moduleActionName,moduleActionId,cScoreType,score,cMoneyType,consumeMoney,cContent,remark,addTime,sort_id)"); strSql.Append(" values ("); strSql.Append("@sId,@uid,@moduleType,@moduleTypeId,@moduleActionName,@moduleActionId,@cScoreType,@score,@cMoneyType,@consumeMoney,@cContent,@remark,@addTime,@sort_id)"); strSql.Append(";set @ReturnValue= @@IDENTITY "); SqlParameter[] parameters = { new SqlParameter("@sId", SqlDbType.Int,4), new SqlParameter("@uid", SqlDbType.Int,4), new SqlParameter("@moduleType", SqlDbType.VarChar,80), new SqlParameter("@moduleTypeId", SqlDbType.Int,4), new SqlParameter("@moduleActionName", SqlDbType.VarChar,200), new SqlParameter("@moduleActionId", SqlDbType.Int,4), new SqlParameter("@cScoreType", SqlDbType.Int,4), new SqlParameter("@score", SqlDbType.Int,4), new SqlParameter("@cMoneyType", SqlDbType.Int,4), new SqlParameter("@consumeMoney", SqlDbType.Float,8), new SqlParameter("@cContent", SqlDbType.VarChar,800), new SqlParameter("@remark", SqlDbType.VarChar,300), new SqlParameter("@addTime", SqlDbType.DateTime), new SqlParameter("@sort_id", SqlDbType.Int,4), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.sId; parameters[1].Value = model.uid; parameters[2].Value = model.moduleType; parameters[3].Value = 1; if (isEW == 1) { parameters[4].Value = "连续6天签到奖励"; } else { parameters[4].Value = model.moduleActionName; } parameters[5].Value = 0; parameters[6].Value = 1; parameters[7].Value = model.score; parameters[8].Value = model.cMoneyType; parameters[9].Value = model.consumeMoney; parameters[10].Value = ""; parameters[11].Value = ""; parameters[12].Value = DateTime.Now; parameters[13].Value = 0; parameters[14].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); StringBuilder strSql2 = new StringBuilder(); strSql2.Append("update wx_ucard_users set qdScore=qdScore+@score,ttScore=ttScore+@score where id=@id"); SqlParameter[] parameters2 = { new SqlParameter("@score", SqlDbType.Int,4), new SqlParameter("@id", SqlDbType.Int,4), }; parameters2[0].Value = model.score; parameters2[1].Value = model.uid; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[14].Value; }
/// <summary> /// 增加一条数据 /// </summary> /// <param name="model"></param> /// <param name="isXiaoFei">是否为消费积分</param> /// <returns></returns> public int Add(WechatBuilder.Model.wx_ucard_users_consumeinfo model,bool isXiaoFei) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into wx_ucard_users_consumeinfo("); strSql.Append("sId,uid,moduleType,moduleTypeId,moduleActionName,moduleActionId,cScoreType,score,cMoneyType,consumeMoney,cContent,remark,addTime,sort_id,sn,operName,pwd)"); strSql.Append(" values ("); strSql.Append("@sId,@uid,@moduleType,@moduleTypeId,@moduleActionName,@moduleActionId,@cScoreType,@score,@cMoneyType,@consumeMoney,@cContent,@remark,@addTime,@sort_id,@sn,@operName,@pwd)"); strSql.Append(";set @ReturnValue= @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@sId", SqlDbType.Int,4), new SqlParameter("@uid", SqlDbType.Int,4), new SqlParameter("@moduleType", SqlDbType.VarChar,80), new SqlParameter("@moduleTypeId", SqlDbType.Int,4), new SqlParameter("@moduleActionName", SqlDbType.VarChar,200), new SqlParameter("@moduleActionId", SqlDbType.Int,4), new SqlParameter("@cScoreType", SqlDbType.Int,4), new SqlParameter("@score", SqlDbType.Int,4), new SqlParameter("@cMoneyType", SqlDbType.Int,4), new SqlParameter("@consumeMoney", SqlDbType.Float,8), new SqlParameter("@cContent", SqlDbType.VarChar,800), new SqlParameter("@remark", SqlDbType.VarChar,300), new SqlParameter("@addTime", SqlDbType.DateTime), new SqlParameter("@sort_id", SqlDbType.Int,4), new SqlParameter("@sn", SqlDbType.VarChar,50), new SqlParameter("@operName", SqlDbType.VarChar,50), new SqlParameter("@pwd", SqlDbType.VarChar,50), new SqlParameter("@ReturnValue",SqlDbType.Int)}; parameters[0].Value = model.sId; parameters[1].Value = model.uid; parameters[2].Value = model.moduleType; parameters[3].Value = model.moduleTypeId; parameters[4].Value = model.moduleActionName; parameters[5].Value = model.moduleActionId; parameters[6].Value = model.cScoreType; parameters[7].Value = model.score; parameters[8].Value = model.cMoneyType; parameters[9].Value = model.consumeMoney; parameters[10].Value = model.cContent; parameters[11].Value = model.remark; parameters[12].Value = model.addTime; parameters[13].Value = model.sort_id; parameters[14].Value = model.sn; parameters[15].Value = model.operName; parameters[16].Value = model.pwd; parameters[17].Direction = ParameterDirection.Output; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); StringBuilder strSql2 = new StringBuilder(); string xfStr = ""; if (isXiaoFei) { xfStr = ",consumeScore=consumeScore+" + (model.score == null ? 0 : model.score.Value); } strSql2.Append("update wx_ucard_users set ttScore=ttScore+@newscore , consumeMoney=consumeMoney+@newMoney" + xfStr + " where id=@uid"); SqlParameter[] parameters2 = { new SqlParameter("@newscore", SqlDbType.Int,4), new SqlParameter("@newMoney", SqlDbType.Decimal,4), new SqlParameter("@uid", SqlDbType.Int,4)}; parameters2[0].Value = MyCommFun.Obj2Int(model.score); parameters2[1].Value = MyCommFun.Obj2Decimal(model.consumeMoney, 0); parameters2[2].Value = model.uid; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); DbHelperSQL.ExecuteSqlTranWithIndentity(sqllist); return (int)parameters[17].Value; }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.manager_role model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "manager_role set "); strSql.Append("role_name=@role_name,"); strSql.Append("role_type=@role_type,"); strSql.Append("is_sys=@is_sys"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@role_name", SqlDbType.NVarChar,100), new SqlParameter("@role_type", SqlDbType.TinyInt,1), new SqlParameter("@is_sys", SqlDbType.TinyInt,1), new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = model.role_name; parameters[1].Value = model.role_type; parameters[2].Value = model.is_sys; parameters[3].Value = model.id; List<CommandInfo> sqllist = new List<CommandInfo>(); CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters); sqllist.Add(cmd); //先删除该角色所有权限 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "manager_role_value where role_id=@role_id "); SqlParameter[] parameters2 = { new SqlParameter("@role_id", SqlDbType.Int,4)}; parameters2[0].Value = model.id; cmd = new CommandInfo(strSql2.ToString(), parameters2); sqllist.Add(cmd); //添加权限 if (model.manager_role_values != null) { StringBuilder strSql3; foreach (Model.manager_role_value modelt in model.manager_role_values) { strSql3 = new StringBuilder(); strSql3.Append("insert into " + databaseprefix + "manager_role_value("); strSql3.Append("role_id,nav_name,action_type)"); strSql3.Append(" values ("); strSql3.Append("@role_id,@nav_name,@action_type)"); SqlParameter[] parameters3 = { new SqlParameter("@role_id", SqlDbType.Int,4), new SqlParameter("@nav_name", SqlDbType.NVarChar,100), new SqlParameter("@action_type", SqlDbType.NVarChar,50)}; parameters3[0].Value = model.id; parameters3[1].Value = modelt.nav_name; parameters3[2].Value = modelt.action_type; cmd = new CommandInfo(strSql3.ToString(), parameters3); sqllist.Add(cmd); } } int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist); if (rowsAffected > 0) { return true; } else { return false; } }