/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(BlogPostInfo model)
{
string strSQL = "INSERT INTO BlogPosts(UserId,Title,Content,ViewCount,CreateDateTime,SystemCategoryId,Tags,UserName,SystemCategoryName) VALUES(@UserId,@Title,@Content,0,GETDATE(),@SystemCategoryId,@Tags,@UserName,@SystemCategoryName);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("UserId", SqlDbType.Int),
new SqlParameter("Title", SqlDbType.NVarChar),
new SqlParameter("Content", SqlDbType.NVarChar),
new SqlParameter("SystemCategoryId", SqlDbType.Int),
new SqlParameter("Tags", SqlDbType.NVarChar),
new SqlParameter("UserName", SqlDbType.NVarChar),
new SqlParameter("SystemCategoryName", SqlDbType.NVarChar),
};
parms[0].Value = model.Id;
parms[1].Value = model.UserId;
parms[2].Value = string.IsNullOrEmpty(model.Title) ? string.Empty : model.Title;
parms[3].Value = string.IsNullOrEmpty(model.Content) ? string.Empty : model.Content;
parms[4].Value = model.SystemCategoryId;
parms[5].Value = string.IsNullOrEmpty(model.Tags) ? string.Empty : model.Tags;
parms[6].Value = model.UserName;
parms[7].Value = model.SystemCategoryName;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 广告位列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <AdPositionInfo> List(AdSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " CreateDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "AdPosition";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
IList <AdPositionInfo> list = new List <AdPositionInfo>();
AdPositionInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));
return(new PageOfList <AdPositionInfo>(list, setting.PageIndex, setting.PageSize, count));
}
public static int PostTopic(ForumTopicInfo model)
{
string strSQL = "INSERT INTO dbo.ForumTopics(ForumId,Title,Content,Poster,PosterId,Sticky,[Digest],IsDeleted,PostDateTime,LastPostDateTime) VALUES(@ForumId,@Title,@Content,@Poster,@PosterId,@Sticky,@Digest,@IsDeleted,GETDATE(),GETDATE());SELECT @@IDENTITY;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
public static int PostReply(ForumReplyInfo model)
{
string strSQL = "INSERT INTO ForumReplies(ForumId,TopicId,Content,Poster,PosterId,PostDateTime,IsDeleted,Floor) VALUES(@ForumId,@TopicId,@Content,@Poster,@PosterId,GETDATE(),0,@Floor);SELECT @@IDENTITY; ";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(CompanyNewsInfo model)
{
string strSQL = "DECLARE @MyId AS INT;INSERT INTO CompanyNews(CompanyId,Title,Content,PublishDateTime,ModifyDateTime,CreateDateTime,IsDeleted,Remark,ImageUrl,Tags,[Type]) VALUES(@CompanyId,@Title,@Content,@PublishDateTime,GETDATE(),GETDATE(),0,@Remark,@ImageUrl,@Tags,@Type);SELECT @MyId = @@IDENTITY;UPDATE CompanyNews SET Url = '/company/'+CAST(@CompanyId AS NVARCHAR(255))+'/newsdetail.html?id='+CAST(@MyId AS NVARCHAR(255)) WHERE Id = @MyId; SELECT @MyId;";
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),
new SqlParameter("Type", 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 <= DateTime.MinValue ? DateTime.Now : 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;
parms[8].Value = string.IsNullOrEmpty(model.Type) ? "news" : model.Type;
int id = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms));
//插入News2Tech表
InsertNews2Tech(id, model.TechIds);
return(id);
}
/// <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);
}
public static int Add(OrderInfo model)
{
string strSQL = "INSERT INTO Orders(OrderUserId,OrderNumber,ProductName,Amount,DeliveryDate,[Status],Remark,IsDeleted,CreateDateTime,OrderCompanyName) VALUES(@OrderUserId,@OrderNumber,@ProductName,@Amount,@DeliveryDate,@Status,@Remark,@IsDeleted,GETDATE(),@OrderCompanyName);SELECT @@IDENTITY;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
public static int Add(ForumInfo model)
{
string strSQL = "INSERT INTO Forums(GroupId,Name,Info,Sort,IsDeleted) VALUES(@GroupId,@Name,@Info,@Sort,@IsDeleted);SELECT @@IDENTITY;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 发表反馈
/// </summary>
/// <param name="model"></param>
/// <returns>返回ID</returns>
public static int Insert(CompanyFeedbackInfo model)
{
string strSQL = "INSERT INTO CompanyFeedback(UserId,RealName,CompanyName,Phone,Email,[Type],Content,ForCompanyId,IP) VALUES (@UserId,@RealName,@CompanyName,@Phone,@Email,@Type,@Content,@ForCompanyId,@IP);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("UserId", SqlDbType.Int),
new SqlParameter("RealName", SqlDbType.NVarChar),
new SqlParameter("CompanyName", SqlDbType.NVarChar),
new SqlParameter("Phone", SqlDbType.NVarChar),
new SqlParameter("Email", SqlDbType.NVarChar),
new SqlParameter("Type", SqlDbType.NVarChar),
new SqlParameter("Content", SqlDbType.NVarChar),
new SqlParameter("ForCompanyId", SqlDbType.Int),
new SqlParameter("IP", SqlDbType.NVarChar),
};
parms[0].Value = model.UserId;
parms[1].Value = model.RealName;
parms[2].Value = model.CompanyName;
parms[3].Value = model.Phone;
parms[4].Value = model.Email;
parms[5].Value = model.Type;
parms[6].Value = model.Content;
parms[7].Value = model.ForCompanyId;
parms[8].Value = model.IP;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Add(OrderAdminInfo model)
{
string strSQL = "INSERT INTO OrderAdmins(UserName,UserPwd,RoleType,IsDeleted,CreateDateTime) VALUES(@UserName,@UserPwd,@RoleType,0,GETDATE());SELECT @@IDENTITY;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
public static int AddGroup(ForumGroupInfo model)
{
string strSQL = "INSERT INTO dbo.ForumGroups(Name,Sort,IsDeleted,CreateDateTime) VALUES(@Name,@Sort,@IsDeleted,GETDATE());SELECT @@IDENTITY;";
SqlParameter[] parms = ParameterHelper.GetClassSqlParameters(model);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <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);
}
/// <summary>
/// 列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <MemberInfo> List(SearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Members";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " CreateDateTime DESC";
fp.WithOptions = " WITH(NOLOCK)";
IList <MemberInfo> list = new List <MemberInfo>();
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(GetByRow(dr));
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL()));;
return(new PageOfList <MemberInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// Email地址是否存在
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public static bool EmailExists(string email)
{
string strSQL = "SELECT COUNT(*) FROM Members WHERE Email = @Email";
SqlParameter param = new SqlParameter("Email", email);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, param)) > 0);
}
public static int Insert(CompanyProductInfo model)
{
string strSQL = "INSERT INTO dbo.CompanyProducts(CategoryId,CompanyId,Title,Content,ImageUrl,Remark,PublishDateTime,CreateDateTime,ModifyDateTime,IsDeleted,Tags,SystemCategoryId) VALUES(@CategoryId,@CompanyId,@Title,@Content,@ImageUrl,@Remark,@PublishDateTime,GETDATE(),GETDATE(),0,@Tags,@SystemCategoryId);SELECT @@IDENTITY;";
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(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 添加视频
/// </summary>
/// <param name="model"></param>
/// <returns>返回VideoId</returns>
public static int Insert(VideoInfo model)
{
string strSQL = "INSERT INTO Videos(CategoryId,Title,Remark,VideoUrl,ImageUrl,IsTop,Tags,PublishDateTime,IsDeleted) VALUES(@CategoryId,@Title,@Remark,@VideoUrl,@ImageUrl,@IsTop,@Tags,@PublishDateTime,@IsDeleted);SELECT @@IDENTITY;";
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(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
public static bool IsExistsUserName(string userName)
{
string strSQL = "SELECT COUNT(*) FROM OrderAdmins WITH(NOLOCK) WHERE UserName = @UserName";
SqlParameter parm = new SqlParameter("UserName", userName);
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parm)) > 0);
}
public static IPageOfList <CompanyInfo> CompanyList(MemberSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.OverOrderBy = " C.ApplyDateTime DESC";
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.QueryFields = "*";
fp.TableName = "Members";
fp.PrimaryKey = "Id";
fp.WithOptions = " WITH(NOLOCK)";
fp.TableReName = "M";
fp.JoinSQL = "INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId";
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" 1 = 1");
if (setting.CompanyStatus != CompanyStatus.None)
{
sbSQL.AppendFormat(" AND C.CompanyStatus = {0}", (int)setting.CompanyStatus);
}
if (!string.IsNullOrEmpty(setting.CompanyName))
{
//这块应该检查一下是否有危险字符，防止SQL注入
//目前没有加上 xingbaifang 2012-12-05
sbSQL.AppendFormat(" AND C.CompanyName LIKE '%{0}%'", setting.CompanyName);
}
fp.Condition = sbSQL.ToString();
//throw new Exception(fp.Build2005());
IList <CompanyInfo> list = new List <CompanyInfo>();
CompanyInfo model = null;
DataTable dt = SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetCompanyByDataRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
sbSQL = new StringBuilder();
sbSQL.Append("SELECT COUNT(*) FROM Members AS M WITH(NOLOCK) INNER JOIN Companies AS C WITH(NOLOCK) ON M.Id = C.UserId");
sbSQL.Append(" WHERE 1 = 1 ");
if (fp.Condition.Length > 0)
{
sbSQL.AppendFormat(" AND {0}", fp.Condition);
}
//throw new Exception(sbSQL.ToString());
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, sbSQL.ToString()));
return(new PageOfList <CompanyInfo>(list, setting.PageIndex, setting.PageSize, count));
}
private static int Count(SearchSetting settings)
{
StringBuilder sbCondition = new StringBuilder(" SELECT COUNT(*) FROM Admins WITH(NOLOCK) WHERE 1 = 1");
if (!settings.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 ");
}
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, sbCondition.ToString())));
}
public static int GetRoleID(string roleName)
{
string strSQL = "SELECT ID FROM dbo.SiteRole WHERE RoleName=@rolename UNION SELECT -1 ORDER BY ID DESC";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 产品列表
/// </summary>
/// <param name="setting"></param>
/// <returns></returns>
public static IPageOfList <ProductInfo> List(ProductSearchSetting setting)
{
FastPaging fp = new FastPaging();
fp.PageIndex = setting.PageIndex;
fp.PageSize = setting.PageSize;
fp.Ascending = false;
fp.TableName = "Products";
fp.TableReName = "p";
fp.PrimaryKey = "ID";
fp.QueryFields = "p.*";
fp.OverOrderBy = " Sort ASC,CreateDateTime ASC";
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" 1 = 1 ");
if (setting.CategoryId > 0)
{
sbCondition.Append(@" AND EXISTS(
SELECT * FROM Categories AS AC WITH(NOLOCK)
WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
AND p.CategoryId = AC.ID
)");
}
if (!setting.ShowDeleted)
{
sbCondition.Append(" AND IsDeleted = 0 /*获取未删除的*/");
}
SqlParameter[] parms =
{
new SqlParameter("CID", SqlDbType.Int),
};
parms[0].Value = setting.CategoryId;
fp.Condition = sbCondition.ToString();
IList <ProductInfo> list = new List <ProductInfo>();
ProductInfo model = null;
DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
model = GetByRow(dr);
if (model != null)
{
list.Add(model);
}
}
}
int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), parms));
return(new PageOfList <ProductInfo>(list, setting.PageIndex, setting.PageSize, count));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(EletterSubscribeInfo model)
{
string strSQL = "INSERT INTO EletterSubscribes(Email,[Subject],CreateDateTime) VALUES(@Email,@Subject,GETDATE());SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Email", SqlDbType.VarChar),
new SqlParameter("Subject", SqlDbType.VarChar),
};
parms[0].Value = model.Email;
parms[1].Value = model.Subject;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 验证用户（用户名和密码）
/// </summary>
/// <param name="userName"></param>
/// <param name="userPassword"></param>
/// <returns></returns>
public static bool Validate(string userName, string userPassword)
{
string strSQL = "SELECT COUNT(*) FROM Members WITH(NOLOCK) WHERE UserName = @UserName AND UserPassword = @UserPassword";
SqlParameter[] param =
{
new SqlParameter("UserName", SqlDbType.NVarChar),
new SqlParameter("UserPassword", SqlDbType.NVarChar),
};
param[0].Value = userName;
param[1].Value = userPassword;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, param)) > 0);
}
/// <summary>
/// 管理员验证
/// </summary>
/// <param name="userName"></param>
/// <param name="userPwd"></param>
/// <returns></returns>
public static bool AdminMemberValidator(string userName, string userPwd)
{
string strSQL = "SELECT COUNT(*) FROM AdminMembers WITH(NOLOCK) INNER JOIN Members WITH(NOLOCK) ON AdminMembers.UserName = Members.UserName WHERE Members.UserName = @UserName AND Members.[Password] = @Password";
SqlParameter[] parms =
{
new SqlParameter("UserName", SqlDbType.NVarChar),
new SqlParameter("Password", SqlDbType.NVarChar),
};
parms[0].Value = userName;
parms[1].Value = userPwd;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)) > 0);
}
public static bool ValidateForLogin(string userName, string userPwd)
{
string strSQL = "SELECT COUNT(*) FROM OrderAdmins WITH(NOLOCK) WHERE UserName = @UserName AND UserPwd = @UserPwd AND IsDeleted = 0";
SqlParameter[] parms =
{
new SqlParameter("@UserName", SqlDbType.NVarChar),
new SqlParameter("UserPwd", SqlDbType.NVarChar)
};
parms[0].Value = userName;
parms[1].Value = userPwd;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)) > 0);
}
/// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(ArticleInfo model)
{
string strSQL = "INSERT INTO Articles(CategoryId,Title,Content,Remark,ImageUrl,QuickLinkUrl,Tags,Author,Sort,IsTop,IsDeleted,PublishDateTime,TimeSpan,Url,CompanyId,AddUserName,LastModifyUserName,LastModifyDateTime,Copyright,SubTitle) VALUES(@CategoryId,@Title,@Content,@Remark,@ImageUrl,@QuickLinkUrl,@Tags,@Author,@Sort,@IsTop,@IsDeleted,@PublishDateTime,@TimeSpan,@Url,@CompanyId,@AddUserName,@AddUserName,GETDATE(),@Copyright,@SubTitle);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CategoryId", SqlDbType.Int),
new SqlParameter("Title", SqlDbType.NVarChar),
new SqlParameter("Content", SqlDbType.NVarChar),
new SqlParameter("Remark", SqlDbType.NVarChar),
new SqlParameter("ImageUrl", SqlDbType.VarChar),
new SqlParameter("QuickLinkUrl", SqlDbType.VarChar),
new SqlParameter("Tags", SqlDbType.VarChar),
new SqlParameter("Author", SqlDbType.VarChar),
new SqlParameter("Sort", SqlDbType.Int),
new SqlParameter("IsTop", SqlDbType.Bit),
new SqlParameter("IsDeleted", SqlDbType.Bit),
new SqlParameter("PublishDateTime", SqlDbType.DateTime),
new SqlParameter("TimeSpan", SqlDbType.VarChar),
new SqlParameter("Url", SqlDbType.VarChar),
new SqlParameter("CompanyId", SqlDbType.Int),
new SqlParameter("AddUserName", SqlDbType.NVarChar),
new SqlParameter("Copyright", SqlDbType.NVarChar),
new SqlParameter("SubTitle", SqlDbType.NVarChar),
};
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.Content) ? string.Empty : model.Content;
parms[4].Value = string.IsNullOrEmpty(model.Remark) ? string.Empty : model.Remark;
parms[5].Value = string.IsNullOrEmpty(model.ImageUrl) ? string.Empty : model.ImageUrl;
parms[6].Value = string.IsNullOrEmpty(model.QuickLinkUrl) ? string.Empty : model.QuickLinkUrl;
parms[7].Value = string.IsNullOrEmpty(model.Tags) ? string.Empty : model.Tags;
parms[8].Value = string.IsNullOrEmpty(model.Author) ? string.Empty : model.Author;
parms[9].Value = model.Sort;
parms[10].Value = model.IsTop;
parms[11].Value = model.IsDeleted;
parms[12].Value = model.PublishDateTime;
parms[13].Value = model.TimeSpan ?? DateTime.Now.ToString("yyyyMMddHHmmssffff");
parms[14].Value = model.Url;
parms[15].Value = model.CompanyId;
parms[16].Value = string.IsNullOrEmpty(model.AddUserName) ? string.Empty : model.AddUserName;
parms[17].Value = model.Copyright ?? string.Empty;
parms[18].Value = model.SubTitle ?? string.Empty;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(ProductPropInfo model)
{
string strSQL = "INSERT INTO ProductProps(CategoryId,Name,IsDeleted) VALUES(@CategoryId,@Name,0);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("CategoryId", SqlDbType.Int),
new SqlParameter("Name", SqlDbType.NVarChar),
};
parms[0].Value = model.Id;
parms[1].Value = model.CategoryId;
parms[2].Value = model.Name.Replace('"', '”');;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(CompanyProductCategoryInfo model)
{
string strSQL = "INSERT INTO CompanyProductCategories(Name,IsSystem,CompanyId,ProductCount,IsDeleted) VALUES(@Name,@IsSystem,@CompanyId,0,0);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Name", SqlDbType.NVarChar),
new SqlParameter("IsSystem", SqlDbType.Int),
new SqlParameter("CompanyId", SqlDbType.Int),
};
parms[0].Value = model.Name;
parms[1].Value = model.IsSystem;
parms[2].Value = model.CompanyId;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}
/// <summary>
/// 添加管理员
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static bool AddAdmin(int userId)
{
string strSQL = @"DECLARE @C AS INT;
SELECT @C = COUNT(*) FROM PagesAdmin WITH(NOLOCK) WHERE UserId = @UserId
IF(@C = 0)
BEGIN
INSERT INTO PagesAdmin(UserId,UserName,CreateDateTime)
SELECT Id,UserName,GETDATE() FROM Members WITH(NOLOCK) WHERE Id = @UserId
END
SELECT @C";
SqlParameter param = new SqlParameter("UserId", userId);
int flag = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, param));
return(flag == 0);
}
/// <summary>
/// 添加基本信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int Insert(MemberInfo model)
{
string strSQL = "INSERT INTO Members(UserName,[Password],Email,RealName,Mobile,Phone,[Type],CreateDateTime,LastLoginDateTime,ModifyDateTime,Address,Province,City,Industry,Position,Fax,NickName,Company,ZipCode,MagType,PurchaseProducts) VALUES(@UserName,@Password,@Email,@RealName,@Mobile,@Phone,@Type,GETDATE(),GETDATE(),GETDATE(),@Address,@Province,@City,@Industry,@Position,@Fax,@NickName,@Company,@ZipCode,@MagType,@PurchaseProducts);SELECT @@IDENTITY;";
SqlParameter[] parms =
{
new SqlParameter("Id", SqlDbType.Int),
new SqlParameter("UserName", SqlDbType.VarChar),
new SqlParameter("Password", SqlDbType.VarChar),
new SqlParameter("Email", SqlDbType.VarChar),
new SqlParameter("RealName", SqlDbType.NVarChar),
new SqlParameter("Mobile", SqlDbType.VarChar),
new SqlParameter("Phone", SqlDbType.VarChar),
new SqlParameter("Address", SqlDbType.NVarChar),
new SqlParameter("Province", SqlDbType.NVarChar),
new SqlParameter("City", SqlDbType.NVarChar),
new SqlParameter("Industry", SqlDbType.NVarChar),
new SqlParameter("Fax", SqlDbType.NVarChar),
new SqlParameter("Position", SqlDbType.NVarChar),
new SqlParameter("NickName", SqlDbType.NVarChar),
new SqlParameter("Type", SqlDbType.Int),
new SqlParameter("Company", SqlDbType.NVarChar),
new SqlParameter("ZipCode", SqlDbType.VarChar),
new SqlParameter("MagType", SqlDbType.VarChar),
new SqlParameter("PurchaseProducts", SqlDbType.VarChar),
};
parms[0].Value = model.Id;
parms[1].Value = model.UserName;
parms[2].Value = model.Password;
parms[3].Value = model.Email;
parms[4].Value = string.IsNullOrEmpty(model.RealName) ? string.Empty : model.RealName;
parms[5].Value = string.IsNullOrEmpty(model.Mobile) ? string.Empty : model.Mobile;
parms[6].Value = string.IsNullOrEmpty(model.Phone) ? string.Empty : model.Phone;
parms[7].Value = string.IsNullOrEmpty(model.Address) ? string.Empty : model.Address;
parms[8].Value = string.IsNullOrEmpty(model.Province) ? string.Empty : model.Province;
parms[9].Value = string.IsNullOrEmpty(model.City) ? string.Empty : model.City;
parms[10].Value = string.IsNullOrEmpty(model.Industry) ? string.Empty : model.Industry;
parms[11].Value = string.IsNullOrEmpty(model.Fax) ? string.Empty : model.Fax;
parms[12].Value = string.IsNullOrEmpty(model.Position) ? string.Empty : model.Position;
parms[13].Value = string.IsNullOrEmpty(model.NickName) ? model.UserName : model.NickName;
parms[14].Value = (int)model.Type;
parms[15].Value = model.Company ?? string.Empty;
parms[16].Value = model.ZipCode ?? string.Empty;
parms[17].Value = model.MagType ?? string.Empty;
parms[18].Value = model.PurchaseProducts ?? string.Empty;
return(Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL, parms)));
}