public static void DeleteAdminInRolesByAdminId(int adminId)
{
string strSQL = "DELETE AdminInRoles WHERE AdminId = @AdminId";
SqlParameter parm = new SqlParameter("AdminId", adminId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
/// <summary>
/// 删除管理员
/// </summary>
/// <param name="userId"></param>
public static void DeleteAdmin(int userId)
{
string strSQL = "DELETE PagesAdmin WHERE UserId = @UserId";
SqlParameter param = new SqlParameter("UserId", userId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, param);
}
/// <summary>
/// 删除角色
/// </summary>
/// <param name="roleName"></param>
/// <param name="throwOnPopulatedRole">是否确认用户</param>
/// <returns></returns>
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
StringBuilder strSQL = new StringBuilder();
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
if (throwOnPopulatedRole == true)
{
#region - T-SQL -
strSQL.Append(" SELECT COUNT(u.ID) FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
#endregion
int i = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL.ToString(), parms));
if (i > 0)
{
throw new ProviderException("Role is not empty");
}
}
strSQL = new StringBuilder("DELETE FROM dbo.SiteRole WHERE RoleName = @rolename;");
int j = Convert.ToInt32(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString(), parms));
return(j == 1);
}
public static void Update(OrderInfo model)
{
string strSQL = "UPDATE Orders SET OrderUserId = @OrderUserId,OrderCompanyName = @OrderCompanyName,OrderNumber = @OrderNumber,ProductName = @ProductName,Amount = @Amount,DeliveryDate = @DeliveryDate,[Status] = @Status,Remark = @Remark,IsDeleted = @IsDeleted WHERE ID = @ID;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
public static int Update(CompanyProductInfo model)
{
string strSQL = "UPDATE CompanyProducts SET CategoryId = @CategoryId,Title = @Title ,Content = @Content,ImageUrl = @ImageUrl,Remark = @Remark,PublishDateTime = @PublishDatetime,ModifyDateTime = GETDATE(),Tags = @Tags,SystemCategoryId = @SystemCategoryId WHERE Id = @Id";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CategoryId", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
new SqlParameter("Title", SqlDbType.NVarChar),
new SqlParameter("Content", SqlDbType.NVarChar),
new SqlParameter("ImageUrl", SqlDbType.NVarChar),
new SqlParameter("Remark", SqlDbType.NVarChar),
new SqlParameter("PublishDateTime", SqlDbType.DateTime),
new SqlParameter("Tags", SqlDbType.NVarChar),
new SqlParameter("SystemCategoryId", SqlDbType.Int),
};
parms[0].Value = model.Id;
parms[1].Value = model.CategoryId;
parms[2].Value = model.CompanyId;
parms[3].Value = string.IsNullOrEmpty(model.Title) ? string.Empty : model.Title;
parms[4].Value = string.IsNullOrEmpty(model.Content) ? string.Empty : model.Content;
parms[5].Value = string.IsNullOrEmpty(model.ImageUrl) ? string.Empty : model.ImageUrl;
parms[6].Value = string.IsNullOrEmpty(model.Remark) ? string.Empty : model.Remark;
parms[7].Value = model.PublishDateTime <= DateTime.MinValue ? DateTime.Now : model.PublishDateTime;
parms[8].Value = string.IsNullOrEmpty(model.Tags) ? string.Empty : model.Tags;
parms[9].Value = model.SystemCategoryId;
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms));
}
/// <summary>
/// 更新视频
/// </summary>
/// <param name="model">返回影响的行数</param>
public static int Update(VideoInfo model)
{
string strSQL = "UPDATE Videos SET CategoryId = @CategoryId ,Title = @Title,Remark = @Remark,VideoUrl = @VideoUrl,ImageUrl = @ImageUrl,IsTop = @IsTop,Tags = @Tags ,PublishDateTime = @PublishDateTime ,IsDeleted = @IsDeleted WHERE Id = @Id";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CategoryId", SqlDbType.Int),
new SqlParameter("Title", SqlDbType.NVarChar),
new SqlParameter("Remark", SqlDbType.NVarChar),
new SqlParameter("VideoUrl", SqlDbType.NVarChar),
new SqlParameter("ImageUrl", SqlDbType.NVarChar),
new SqlParameter("IsTop", SqlDbType.Int),
new SqlParameter("Tags", SqlDbType.NVarChar),
new SqlParameter("PublishDateTime", SqlDbType.DateTime),
new SqlParameter("IsDeleted", SqlDbType.Int),
};
parms[0].Value = model.Id;
parms[1].Value = model.CategoryId;
parms[2].Value = string.IsNullOrEmpty(model.Title) ? string.Empty : model.Title;
parms[3].Value = string.IsNullOrEmpty(model.Remark) ? string.Empty : model.Remark;
parms[4].Value = string.IsNullOrEmpty(model.VideoUrl) ? string.Empty : model.VideoUrl;
parms[5].Value = string.IsNullOrEmpty(model.ImageUrl) ? string.Empty : model.ImageUrl;
parms[6].Value = model.IsTop ? 1 : 0;
parms[7].Value = string.IsNullOrEmpty(model.Tags) ? string.Empty : model.Tags;
parms[8].Value = model.PublishDateTime;
parms[9].Value = model.IsDeleted ? 1 : 0;
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms));
}
/// <summary>
/// 删除后台管理员
/// </summary>
/// <param name="userName"></param>
public static void DeleteAdminMember(string userName)
{
string strSQL = "DELETE AdminMembers WHERE UserName = @UserName";
SqlParameter parm = new SqlParameter("UserName", userName);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
public static void Add(WebLogVisitInfo model)
{
string strSQL = "INSERT INTO Visit(Url,Referrer,Querys,IP,UserAgent,VisitTime,SiteId,OS,Brower,UserName) VALUES(@Url,@Referrer,@Querys,@IP,@UserAgent,GETDATE(),@SiteId,@OS,@Brower,@UserName) ";
SqlParameter[] parms =
{
new SqlParameter("Url", SqlDbType.NVarChar),
new SqlParameter("Referrer", SqlDbType.NVarChar),
new SqlParameter("Querys", SqlDbType.NVarChar),
new SqlParameter("UserAgent", SqlDbType.NVarChar),
new SqlParameter("SiteId", SqlDbType.Int),
new SqlParameter("OS", SqlDbType.NVarChar),
new SqlParameter("Brower", SqlDbType.NVarChar),
new SqlParameter("UserName", SqlDbType.NVarChar),
new SqlParameter("IP", SqlDbType.NVarChar),
};
parms[0].Value = model.Url;
parms[1].Value = model.Referrer;
parms[2].Value = model.Querys;
parms[3].Value = model.UserAgent;
parms[4].Value = model.SiteId;
parms[5].Value = model.OS;
parms[6].Value = model.Brower;
parms[7].Value = model.UserName;
parms[8].Value = model.IP;
SQLPlus.ExecuteNonQuery("weblog", CommandType.Text, strSQL, parms);
}
public static void InsertArticleTagData(int articleId, string tags)
{
//首先删除
string strSQL = string.Format("DELETE ArticleTags WHERE ArticleId = {0}", articleId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL);
//插入
if (!string.IsNullOrEmpty(tags) && tags.Length > 0)
{
tags = tags.Replace("，", ",");
tags = Regex.Replace(tags, @"(\s+)", ",");
string[] keys = tags.Split(',');
if (keys.Length > 0)
{
foreach (string key in keys)
{
if (key.Length >= 2)
{
strSQL = string.Format("INSERT INTO ArticleTags(ArticleId,Tag) VALUES({0},'{1}')", articleId, key);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL);
}
}
}
}
}
public static void UpdateReplyDeleted(int replyId)
{
string strSQL = "UPDATE ForumReplies SET IsDeleted = ABS(IsDeleted - 1) WHERE Id = @Id";
SqlParameter parm = new SqlParameter("Id", replyId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
public static void UpdateReply(ForumReplyInfo model)
{
string strSQL = "UPDATE ForumReplies SET Content = @Content WHERE Id = @Id";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
public static void RestoreTopic(int topicId)
{
string strSQL = "UPDATE ForumTopics SET IsDeleted = 0 WHERE Id = @Id";
SqlParameter parm = new SqlParameter("Id", topicId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
public static void UpdateTopicViewsCount(int id)
{
string strSQL = "UPDATE ForumTopics SET [Views] = [Views] + 1 WHERE Id = @Id";
SqlParameter parm = new SqlParameter("Id", id);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
public static void UpdateTopic(ForumTopicInfo model)
{
string strSQL = "UPDATE ForumTopics SET ForumId = @ForumId,Title = @Title ,Content = @Content,Sticky = @Sticky,[Digest] = @Digest,IsDeleted = @IsDeleted WHERE Id = @Id";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
public static void UpdateGroup(ForumGroupInfo model)
{
string strSQL = "UPDATE ForumGroups SET Name = @Name,Sort = @Sort,IsDeleted = @IsDeleted WHERE Id = @ID";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
/// <summary>
/// 添加公司信息
/// </summary>
/// <param name="model"></param>
/// <returns>返回公司ID(CompanyID)</returns>
public static int InsertCompany(CompanyInfo model)
{
string strSQL = "INSERT INTO Companies(UserId,CompanyName,CompanyIntroduction,CompanyLogo,CompanySite,[CompanyStatus]) VALUES(@UserId,@CompanyName,@CompanyIntroduction,@CompanyLogo,@CompanySite,1);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("UserId", SqlDbType.Int),
new SqlParameter("CompanyName", SqlDbType.NVarChar),
new SqlParameter("CompanyIntroduction", SqlDbType.NVarChar),
new SqlParameter("CompanyLogo", SqlDbType.NVarChar),
new SqlParameter("CompanySite", SqlDbType.NVarChar)
};
parms[0].Value = model.UserId;
parms[1].Value = string.IsNullOrEmpty(model.CompanyName) ? string.Empty :model.CompanyName;
parms[2].Value = string.IsNullOrEmpty(model.CompanyIntroduction) ? string.Empty : model.CompanyIntroduction;
parms[3].Value = string.IsNullOrEmpty(model.CompanyLogo) ? string.Empty:model.CompanyLogo;
parms[4].Value = string.IsNullOrEmpty(model.CompanySite) ? string.Empty : model.CompanySite;;
int companyId = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms));
//插入【公司产品】表
if (companyId > 0)
{
foreach (int i in model.Categories)
{
strSQL = string.Format("INSERT INTO dbo.Company2Category(CompanyId,CategoryId) VALUES({0},{1})", companyId, i);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL);
}
}
return(companyId);
}
/// <summary>
/// 更新播放次数
/// </summary>
/// <param name="videoId"></param>
public static void UpdatePlayCount(int videoId)
{
string strSQL = "UPDATE Videos SET PlayCount = PlayCount + 1 WHERE Id = @Id";
SqlParameter parm = new SqlParameter("Id", videoId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
/// <summary>
/// 更新回复数
/// </summary>
/// <param name="id"></param>
/// <param name="plus">true:+,false:-</param>
public static void UpdateReplies(int id, bool plus)
{
string strSQL = string.Format("UPDATE Forums SET Replies = Replies {0} 1 WHERE Id = @Id", plus ? "+" : "-");
SqlParameter parm = new SqlParameter("Id", id);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
/// <summary>
/// 更新产品所使用的属性以及属性值
/// </summary>
/// <param name="model"></param>
private static void UpdateProduct2PropValues(ProductInfo model)
{
//首先删除
string strSQL = "DELETE dbo.Product2PropValues WHERE ProductId = @ProductId";
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, new SqlParameter("ProductId", SqlDbType.Int)
{
Value = model.Id
});
//在添加
strSQL = "INSERT INTO dbo.Product2PropValues(ProductId,PropId,Value) VALUES(@ProductId,@PropId,@Value)";
SqlParameter[] parms =
{
new SqlParameter("ProductId", SqlDbType.Int),
new SqlParameter("PropId", SqlDbType.Int),
new SqlParameter("Value", SqlDbType.NVarChar),
};
parms[0].Value = model.Id;
foreach (var item in model.Props)
{
parms[1].Value = item.Id;
if (!string.IsNullOrEmpty(item.Value))
{
parms[2].Value = item.Value.Replace('"', '”');
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
}
}
/// <summary>
/// 更新浏览数
/// </summary>
/// <param name="postId"></param>
public static void UpdateViewCount(int postId)
{
string strSQL = "UPDATE BlogPosts SET ViewCount = ViewCount + 1 WHERE Id = @Id";
SqlParameter parm = new SqlParameter("Id", postId);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Update(CompanyNewsInfo model)
{
string strSQL = "UPDATE CompanyNews SET Title = @Title ,Content = @Content,PublishDateTime =@PublishDateTime,ModifyDateTime = GETDATE(),Remark = @Remark,ImageUrl = @ImageUrl,Tags = @Tags WHERE Id = @Id AND CompanyId = @CompanyId";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
new SqlParameter("Title", SqlDbType.NVarChar),
new SqlParameter("Content", SqlDbType.NVarChar),
new SqlParameter("PublishDateTime", SqlDbType.DateTime),
new SqlParameter("Remark", SqlDbType.NVarChar),
new SqlParameter("ImageUrl", SqlDbType.NVarChar),
new SqlParameter("Tags", SqlDbType.NVarChar),
};
parms[0].Value = model.Id;
parms[1].Value = model.CompanyId;
parms[2].Value = model.Title;
parms[3].Value = model.Content;
parms[4].Value = model.PublishDateTime;
parms[5].Value = string.IsNullOrEmpty(model.Remark) ? string.Empty : model.Remark;
parms[6].Value = string.IsNullOrEmpty(model.ImageUrl) ? string.Empty : model.ImageUrl;
parms[7].Value = string.IsNullOrEmpty(model.Tags) ? string.Empty : model.Tags;
//插入News2Tech表
InsertNews2Tech(model.Id, model.TechIds);
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms));
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <param name="companyId"></param>
/// <returns></returns>
public static bool Delete(int id, int companyId)
{
if (id == 0 || companyId == 0)
{
return(false);
}
//如果ProductCount =0 可以删除，否则失败
int productCount = Get(id, companyId).ProductCount;
if (productCount > 0)
{
return(false);
}
string strSQL = "UPDATE CompanyProductCategories SET IsDeleted = 1,ProductCount = 0 WHERE Id = @Id AND CompanyId = @CompanyId";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
};
parms[0].Value = id;
parms[1].Value = companyId;
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
}
/// <summary>
/// 删除（逻辑删除）
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Delete(int id)
{
string strSQL = "UPDATE Resumes SET IsDeleted = 1 WHERE Id = @Id";
SqlParameter parm = new SqlParameter("id", id);
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm));
}
/// <summary>
/// 还原管理员
/// </summary>
/// <param name="id"></param>
public static void Restore(int id)
{
string strSQL = "UPDATE OrderAdmins SET IsDeleted = 0 WHERE ID = @ID";
SqlParameter parm = new SqlParameter("ID", id);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parm);
}
public static void Update(OrderUserInfo model)
{
//同时更新Orders表中OrderCompanyName字段
string strSQL = "UPDATE OrderUsers SET UserName = @UserName,UserPwd = @UserPwd,CompanyName = @CompanyName WHERE Id = @ID;UPDATE Orders SET OrderCompanyName = @CompanyName WHERE OrderUserId = @ID";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
/// <summary>
/// 删除反馈信息
/// </summary>
/// <param name="feedbackId"></param>
/// <param name="companyId"></param>
public static void Delete(int feedbackId, int companyId)
{
string strSQL = "UPDATE dbo.CompanyFeedback SET IsDeleted = 1 WHERE Id = @Id AND ForCompanyId = @CompanyId";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
};
parms[0].Value = feedbackId;
parms[1].Value = companyId;
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
/// <summary>
/// 回复反馈
/// </summary>
/// <param name="model"></param>
public static void ReplyFeedback(CompanyFeedbackReplyInfo model)
{
string strSQL = "INSERT INTO CompanyFeedbackReply(FeedbackId,Content) VALUES(@FeedbackId,@Content)";
SqlParameter[] parms =
{
new SqlParameter("FeedbackId", SqlDbType.Int),
new SqlParameter("Content", SqlDbType.NVarChar),
};
parms[0].Value = model.FeedbackId;
parms[1].Value = model.Content;
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="postId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public static int Delete(int postId, int userId)
{
string strSQL = "UPDATE BlogPosts SET IsDeleted = 1 WHERE Id = @Id AND UserId = @UserId";
SqlParameter[] parms =
{
new SqlParameter("Id", postId),
new SqlParameter("UserId", userId),
};
parms[0].Value = postId;
parms[1].Value = userId;
return(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms));
}
/// <summary>
/// 更新产品数
/// </summary>
/// <param name="id"></param>
/// <param name="companyId"></param>
/// <param name="plus"></param>
public static void UpdateProductCount(int id, int companyId, bool plus)
{
string strSQL = string.Format("UPDATE CompanyProductCategories SET ProductCount = ProductCount {0} 1 WHERE Id = @Id AND CompanyId = @CompanyId", plus ? "+" :"-");
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
};
parms[0].Value = id;
parms[1].Value = companyId;
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
public static void SetPwd(int userId, string newPwd)
{
string strSQL = "UPDATE OrderAdmins SET UserPwd = @UserPwd WHERE Id = @ID";
SqlParameter[] parms =
{
new SqlParameter("@ID", SqlDbType.Int),
new SqlParameter("@UserPwd", SqlDbType.NVarChar)
};
parms[0].Value = userId;
parms[1].Value = newPwd;
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}