public DataTable GetTable(int id)
{
DataTable table = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.* ");
strSql.Append(" FROM M_Exhibition_Activity AS a ");
strSql.Append(" WHERE a.ActivityID =@ActivityID ");
SqlParameter[] parameters =
{
new SqlParameter("@ActivityID", SqlDbType.Int, 4)
};
parameters[0].Value = id;
DataSet ds = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), parameters);
if (ds != null && ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
table = ds.Tables[0];
}
}
return(table);
}
/// <summary>
/// 文章点赞数与评论数
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <ReviewCommonEntity> ReviewCommon_Get(EventItemCommentEntity entity)
{
try
{
ReviewCommonEntity rcEntity = new ReviewCommonEntity();
SqlParameter[] commPrms =
{
new SqlParameter("@EventItemID", entity.EventItemID),
new SqlParameter("@UserID", entity.UserID)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReviewCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity.ReviewCount = table.Rows[0]["ReviewCount"].ToInt();
rcEntity.ArticleLikeCount = table.Rows[0]["ArticleLikeCount"].ToInt();
rcEntity.IsArticleLike = table.Rows[0]["IsArticleLike"].ToInt() == 0 ? false : true;
}
return(new OperationResult <ReviewCommonEntity>(OperationResultType.Success, "评论和点赞数抓取完成！", rcEntity));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <ReviewCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 获取单条订阅明细
/// </summary>
/// <param name="userID"></param>
/// <param name="subID"></param>
/// <returns></returns>
public OperationResult <SubscriptionEntity> Subscription_Get(int userID, int subID)
{
try
{
string sql = string.Empty;
sql += " SELECT s.SubID,s.SubName,s.PinYin,s.[Description],s.SubShortName,fp.PicturePath ,fp.Domain,s.CreateTime ";
sql += " ,(SELECT CAST(COUNT(1) AS BIT) FROM [dbo].[M_User_Subscription] WHERE USERID={0} AND SubID =s.SubID) AS IsAlready ";
sql += " FROM M_Subscription AS s ";
sql += " LEFT JOIN [dbo].[M_V_Picture] AS fp ON s.PictureID= fp.PictureID ";
sql += " WHERE s.SubID={1} ";
sql = string.Format(sql, userID, subID);
List <SubscriptionEntity> entitys = new List <SubscriptionEntity>();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql).Tables[0];
entitys = ConvertDataTable <SubscriptionEntity> .ConvertToList(table);
if (entitys != null && entitys.Count == 1)
{
return(new OperationResult <SubscriptionEntity>(OperationResultType.Success, "订阅信息获取完成！", entitys[0]));
}
else
{
return(new OperationResult <SubscriptionEntity>(OperationResultType.NoChanged, "请求的订阅号不存在！"));
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <SubscriptionEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 判断分类是否可以添加
/// </summary>
/// <param name="pid"></param>
/// <param name="fname"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool IsUserable(int pid, string fname, int id)
{
bool flg = false;
if (pid == 0)
{
pid = -1;
}
string str = "select * from M_CalendarType where ParentCalendarTypeID=@ParentCalendarTypeID and CalendarTypeName=@CalendarTypeName and CalendarTypeID <> @CalendarTypeID";
SqlParameter[] pars = { new SqlParameter("@ParentCalendarTypeID", pid),
new SqlParameter("@CalendarTypeName", fname),
new SqlParameter("@CalendarTypeID", id) };
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
DataTable db = SQlHelper.ExecuteDataset(conn, CommandType.Text, str, pars).Tables[0];
if (db.Rows.Count > 0)
{
flg = false;
}
else
{
flg = true;
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
return(flg);
}
/// <summary>
/// 查询分页/不分页两种
/// </summary>
/// <param name="entity"></param>
/// <param name="totalcnt"></param>
/// <returns></returns>
public DataTable QueryAdminTable(AdminSearchEntity entity, out int totalcnt)
{
DataTable table = null;
totalcnt = 0;
try
{
SqlParameter[] prms = ParseToSqlParameters(entity).ToArray();
if (entity.UseDBPagination)
{
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
}
else
{
throw new NotImplementedException();
}
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
}
return(table);
}
/// <summary>
/// 登录判断
/// </summary>
/// <param name="username"></param>
/// <param name="pwd"></param>
/// <param name="userid"></param>
/// <returns></returns>
public bool TryLogin(string username, string pwd, out string userid)
{
userid = "0";
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT User_ID FROM M_System_User WHERE User_Name=@User_Name AND User_Pwd=@User_Pwd");
SqlParameter[] prms =
{
new SqlParameter("@User_Name", username),
new SqlParameter("@User_Pwd", pwd)
};
try
{
DataTable dt = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sb.ToString(), prms).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
userid = dt.Rows[0]["User_ID"].ToString();
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(false);
}
}
/// <summary>
/// 添加回复
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <AddReplyCommonEntity> ArticleReply_Insert(EventItemReplyEntity entity)
{
try
{
AddReplyCommonEntity acEntity = new AddReplyCommonEntity();
ReplyCommonEntity rcEntity = new ReplyCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
DateTime? nowDate = entity.ReplyTime == null ? DateTime.Now : entity.ReplyTime;
SqlParameter[] prms =
{
new SqlParameter("@ReplyID", entity.ReplyID),
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@IsAnonymous", entity.IsAnonymous),
new SqlParameter("@ReplyParentID", entity.ReplyParentID),
new SqlParameter("@ReplyContent", entity.ReplyContent),
new SqlParameter("@ReplyTime", nowDate),
new SqlParameter("@CheckTypeID", entity.CheckTypeID),
new SqlParameter("@ViewStateID", entity.ViewStateID)
};
SqlParameter[] commPrms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
};
prms[0].Direction = ParameterDirection.Output;
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_EventItemReply_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReplyCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity = ConvertDataTable <ReplyCommonEntity> .ConvertToList(table)[0];
}
acEntity.ReplyID = prms[0].Value.ToInt();
acEntity.ReplyTime = nowDate;
acEntity.ReviewLikeCount = rcEntity.ReviewLikeCount;
acEntity.IsReviewLike = rcEntity.IsReviewLike;
acEntity.ReplyCount = rcEntity.ReplyCount;
if (state > 0)
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.Success, "回复完成！", acEntity));
}
else
{
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoChanged, "评论已被删除,无法回复！", acEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <AddReplyCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 添加文章评论
/// </summary>
/// <returns></returns>
public OperationResult <ReviewCommonEntity> ArticleComments_Insert(EventItemCommentEntity entity)
{
try
{
ReviewCommonEntity rcEntity = new ReviewCommonEntity();
SqlParameter[] prms =
{
new SqlParameter("@EventItemID", entity.EventItemID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@IsAnonymous", entity.IsAnonymous),
new SqlParameter("@CommentContent", entity.CommentContent),
new SqlParameter("@CommentTime", entity.CommentTime == null ? DateTime.Now:entity.CommentTime),
new SqlParameter("@CheckTypeID", entity.CheckTypeID),
new SqlParameter("@ViewStateID", entity.ViewStateID)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_EventItemComments_Insert", prms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity.ReviewCount = table.Rows[0]["ReviewCount"].ToInt();
rcEntity.ArticleLikeCount = table.Rows[0]["ArticleLikeCount"].ToInt();
rcEntity.IsArticleLike = table.Rows[0]["IsArticleLike"].ToInt() == 0 ? false : true;
}
return(new OperationResult <ReviewCommonEntity>(OperationResultType.Success, "添加文章评论完成！", rcEntity));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <ReviewCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 根据展场id 查找属于这次展场的展商和活动的标题信息
/// </summary>
/// <param name="ExhibitionID">展场id</param>
/// <returns></returns>
public DataTable GetSearchKeyWordTable(int exhibitionID)
{
DataTable table = null;
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"SELECT ee.ExhibitorName AS SearchName,
ee.ExhibitorPinYin AS SearchPinYin,
1 AS SearchType
FROM
dbo.M_Exhibition_Exhibitors AS ee
INNER JOIN dbo.M_Exhibition_Main AS em ON em.ExhibitionID = ee.ExhibitionID
WHERE ee.StateTypeID=1 AND em.StateTypeID = 1 AND em.ExhibitionID = {0}
UNION
SELECT ea.ActivityTitle AS SearchName,
'' AS SearchPinYin,
2 AS SearchType
FROM dbo.M_Exhibition_Activity AS ea
LEFT JOIN dbo.M_Exhibition_Exhibitors AS ee ON ee.ExhibitorID = ea.ExhibitorID
INNER JOIN dbo.M_Exhibition_Main AS em ON em.ExhibitionID = ea.ExhibitionID
WHERE ea.StateTypeID = 1 AND ((ee.ExhibitorID IS NOT NULL AND ee.StateTypeID=1) OR ee.ExhibitorID IS NULL) AND em.StateTypeID =1 AND em.ExhibitionID = {0} AND ea.ParentID=0"
, exhibitionID.ToString());
DataSet ds = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
if (ds != null && ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
table = ds.Tables[0];
}
}
return(table);
}
/// <summary>
/// 分页查询所有已分配角色用户信息
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.13
/// </remarks>
/// <param name="PageSize">页面大小</param>
/// <param name="PageIndex">页码</param>
/// <param name="Records">返回总页数</param>
/// <returns></returns>
public DataSet GetUserWithHaveFunForPage(String sqlwhere, int PageSize, int PageIndex, out int Records)
{
SqlParameter[] prms =
{
new SqlParameter("@TableName", "M_V_System_User"),
new SqlParameter("@Fields", "*"),
new SqlParameter("@OrderField", "Role_ID"),
new SqlParameter("@sqlWhere", sqlwhere),
new SqlParameter("@pageSize", PageSize),
new SqlParameter("@pageIndex", PageIndex),
new SqlParameter("@Records", 0)
};
try
{
prms[6].Direction = ParameterDirection.Output;
DataSet ds = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "dbo.sp_pager06", prms);
Records = Convert.ToInt32(prms[6].Value.ToString());
return(ds);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
Records = 0;
return(new DataSet());
}
}
/// <summary>
/// 查询订阅结果集
/// </summary>
/// <param name="userID">用户id</param>
/// <param name="subID">末尾订阅ID</param>
/// <param name="strWhere">查询条件 用于查询是否订阅条件</param>
/// <param name="pageSize">页面大小</param>
/// <param name="pageIndex">起始页 默认为1</param>
/// <param name="type">YES 已订阅 NO 未订阅</param>
/// <returns></returns>
public OperationResult <List <SubscriptionEntity> > Subscription_GetList(int userID, int subID, int pageSize, string strWhere, int pageIndex, SubQueryType type)
{
try
{
List <SubscriptionEntity> entitys = new List <SubscriptionEntity>();
SqlParameter[] prms =
{
new SqlParameter("@UserID", userID),
new SqlParameter("@SubID", subID),
new SqlParameter("@StrWhere", strWhere),
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@PageSize", pageSize),
new SqlParameter("@QueryType", (int)type)
};
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_Subscription_GetList", prms).Tables[0];
entitys = ConvertDataTable <SubscriptionEntity> .ConvertToList(table);
return(new OperationResult <List <SubscriptionEntity> >(OperationResultType.Success, "订阅列表获取完成！", entitys));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <List <SubscriptionEntity> >(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
public UserSessionEntity TryLogin(string loginname, string pwd, out string msg)
{
try
{
UserSessionEntity info = new UserSessionEntity();
SqlParameter[] prms =
{
new SqlParameter("@LoginName", loginname)
};
msg = string.Empty;
string sql = "select UserID, PassWord,AreaID,Telphone,ThirdWxUserName,ThirdWbUserName,ThirdQqUserNameW,NickName,ThirdPictureUrl,p.PicturePath,p.Domain from M_User m left join M_V_Picture p on m.PictureID=p.PictureID where LoginName=@LoginName ";
DataTable dt = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql, prms).Tables[0];
if (dt.Rows.Count > 0)
{
if (dt.Rows.Count == 1)
{
if (dt.Rows[0]["PassWord"].ToString() == pwd)
{
msg = "登录成功";
msg = dt.Rows[0]["UserID"].ToString();
int userid = int.Parse(dt.Rows[0]["UserID"].ToString());
int areaid = dt.Rows[0]["AreaID"].ToString() == "" ? 0 : int.Parse(dt.Rows[0]["AreaID"].ToString());
info.Telphone = dt.Rows[0]["Telphone"].ToString();
info.ThirdWxUserName = dt.Rows[0]["ThirdWxUserName"].ToString();
info.ThirdWbUserName = dt.Rows[0]["ThirdWbUserName"].ToString();
info.ThirdQqUserNameW = dt.Rows[0]["ThirdQqUserNameW"].ToString();
info.NickName = dt.Rows[0]["NickName"].ToString();
info.ThirdPictureUrl = dt.Rows[0]["ThirdPictureUrl"].ToString();
info.PicturePath = dt.Rows[0]["PicturePath"].ToString();
info.Domain = dt.Rows[0]["Domain"].ToString();
info.UserID = userid;
info.ZoneID = areaid;
return(info);
}
else
{
msg = "用户名或密码错误！";
return(null);
}
}
else
{
msg = "您的账户异常(RepeatEvent)";
return(null);
}
}
else
{
msg = "用户名或者密码错误！";
return(null);
}
}
catch (Exception ex)
{
msg = ex.Message;
LogUtil.WriteLog(ex);
return(null);
}
}
public DataSet GetExhibitionDataSet(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@" SELECT * FROM dbo.M_Exhibition_Main WHERE ExhibitionID = {0};
SELECT * FROM dbo.M_Exhibition_Advertisement WHERE ExhibitionID = {0};"
, id.ToString());
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null));
}
/// <summary>
/// 查询所有角色
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.15
/// </remarks>
/// <returns></returns>
public DataSet GetAllRole()
{
try
{
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, "select * from M_System_Role"));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataSet());
}
}
/// <summary>
/// 查询所有部门信息
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.14
/// </remarks>
/// <returns></returns>
public DataSet GetAllDept()
{
try
{
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, "select * from Copy_DepartInfo"));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataSet());
}
}
public DataSet GetExhibitorEntityDataSet(int exhibitorID)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"SELECT e.*,0 as rowId FROM dbo.M_Exhibition_Exhibitors AS e
WHERE e.ExhibitorID={0};
SELECT el.* FROM dbo.M_Exhibition_Exhibitor_Location AS el
WHERE el.ExhibitorID={0}"
, exhibitorID.ToString());
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null));
}
/// <summary>
/// 添加日历信息
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public string InsertFestival(FestivalEntity entity)
{
string flg = "0";
string sql = "select * from M_Festival where FestivalName=@FestivalName and StartTime=@StartTime and EndTime=@EndTime and FestivalType=@FestivalType";
string sql1 = "insert into M_Festival(FestivalName,FestivalShortName,StartTime,EndTime,FestivalType,FestivalWeight) values (@FestivalName,@FestivalShortName,@StartTime,@EndTime,@FestivalType,@FestivalWeight)";
SqlParameter[] pars = { new SqlParameter("FestivalName", SqlDbType.NVarChar, 50),
new SqlParameter("FestivalShortName", SqlDbType.NVarChar, 50),
new SqlParameter("StartTime", SqlDbType.DateTime),
new SqlParameter("EndTime", SqlDbType.DateTime),
new SqlParameter("FestivalType", SqlDbType.Int),
new SqlParameter("FestivalWeight", SqlDbType.Int) };
pars[0].Value = entity.FestivalName;
pars[1].Value = entity.FestivalShortName;
pars[2].Value = entity.StartTime;
pars[3].Value = entity.EndTime;
pars[4].Value = entity.FestivalType;
pars[5].Value = entity.FestivalWeight;
SqlTransaction trans = null;
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
try
{
conn.Open();
trans = conn.BeginTransaction();
DataTable db = SQlHelper.ExecuteDataset(trans, CommandType.Text, sql, pars).Tables[0];
if (db.Rows.Count == 0)
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sql1, pars);
flg = "1";
trans.Commit();
}
else
{
flg = "2";
}
}
catch (Exception e)
{
LogUtil.WriteLog(e);
if (trans != null)
{
flg = "0"; //数据传输错误！
trans.Rollback();
}
}
}
return(flg);
}
public DataTable QueryStartPic()
{
try
{
string sql = @" SELECT TOP 1 p.PictureID,p.PicturePath,p.Domain,s.IsDefault,StartTime,s.EndTime,s.Url FROM [dbo].[M_StartPictures] s INNER JOIN dbo.M_V_Picture p";
sql += " ON s.PictureID=p.PictureID WHERE s.IsDefault=1 AND StartTime<=GETDATE() AND EndTime>=GETDATE() ";
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql).Tables[0]);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataTable());
}
}
/// <summary>
/// 根据用户编号查询信息维护员的所在维护员管理下的所有节点
/// </summary>
/// <param name="userid"></param>
/// <returns></returns>
public DataSet GetInfoManagerWithFunByID(String userid)
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid)
};
try
{
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, "select UFRel_FunctionID from M_System_User_Fun_Rel where UFRel_UserID=@userid and UFRel_FunctionID>=4000 and UFRel_FunctionID<5000", prms));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataSet());
}
}
/// <summary>
/// 根据角色编号查询默认权限
/// </summary>
/// <param name="roleid"></param>
/// <returns></returns>
public DataSet GetFunctionIDByRoleID(int roleid)
{
SqlParameter[] prms =
{
new SqlParameter("@roleid", roleid)
};
try
{
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, "select RFRel_FunctionID from M_System_Role_Fun_Rel where RFRel_RoleID=@roleid", prms));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataSet());
}
}
/// <summary>
/// 根据用户编号查询拥有权限的所有节点
/// </summary>
/// <remarks>
/// author: zp
/// createtime: 2010.07.14
/// </remarks>
/// <param name="userid">用户编号</param>
/// <returns></returns>
public DataSet GetFunctionRelUser(String userid)
{
SqlParameter[] prms =
{
new SqlParameter("@userid", userid)
};
try
{
return(SQlHelper.ExecuteDataset(new SqlConnection(SQlHelper.MyConnectStr), CommandType.Text, "select * from [M_V_FunctionRelUser] where User_ID=@userid and Function_isValid=1", prms));
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new DataSet());
}
}
/// <summary>
/// 评论点赞插入
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <ReplyCommonEntity> CommentsLike_Insert(CommentLikeEntity entity)
{
try
{
ReplyCommonEntity rcEntity = new ReplyCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
SqlParameter[] prms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@LikeCnt", entity.LikeCnt),
new SqlParameter("@LikeTime", entity.LikeTime == null ? DateTime.Now :entity.LikeTime)
};
SqlParameter[] commPrms =
{
new SqlParameter("@CommentID", entity.CommentID),
new SqlParameter("@UserID", entity.UserID)
};
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_Comments_Like_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReplyCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity = ConvertDataTable <ReplyCommonEntity> .ConvertToList(table)[0];
}
if (state > 0)
{
return(new OperationResult <ReplyCommonEntity>(OperationResultType.Success, "评论点赞成功！", rcEntity));
}
else
{
return(new OperationResult <ReplyCommonEntity>(OperationResultType.NoChanged, "您已点过赞了！", rcEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <ReplyCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
/// <summary>
/// 文章点赞插入
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public OperationResult <ReviewCommonEntity> ArticleLike_Insert(EventItemLikeEntity entity)
{
try
{
ReviewCommonEntity rcEntity = new ReviewCommonEntity();
using (SqlConnection con = new SqlConnection(SQlHelper.MyConnectStr))
{
SqlTransaction trans = null;
con.Open();
trans = con.BeginTransaction();
SqlParameter[] prms =
{
new SqlParameter("@EventItemID", entity.EventItemID),
new SqlParameter("@UserID", entity.UserID),
new SqlParameter("@LikeCnt", entity.LikeCnt),
new SqlParameter("@LikeTime", entity.LikeTime == null ? DateTime.Now :entity.LikeTime)
};
SqlParameter[] commPrms =
{
new SqlParameter("@EventItemID", entity.EventItemID),
new SqlParameter("@UserID", entity.UserID)
};
int state = SQlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "M_EventItem_Like_Insert", prms);
trans.Commit();
DataTable table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "M_ReviewCommon_Get", commPrms).Tables[0];
if (table != null && table.Rows.Count != 0)
{
rcEntity.ReviewCount = table.Rows[0]["ReviewCount"].ToInt();
rcEntity.ArticleLikeCount = table.Rows[0]["ArticleLikeCount"].ToInt();
rcEntity.IsArticleLike = table.Rows[0]["IsArticleLike"].ToInt() == 0 ? false : true;
}
if (state == 1)
{
return(new OperationResult <ReviewCommonEntity>(OperationResultType.Success, "文章点赞成功！", rcEntity));
}
else
{
return(new OperationResult <ReviewCommonEntity>(OperationResultType.NoChanged, "您已点过赞了！", rcEntity));
}
}
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(new OperationResult <ReviewCommonEntity>(OperationResultType.NoConnection, Description.EnumDescription(OperationResultType.NoConnection)));
}
}
public DataTable QueryData(ExhibitionSearchEntity info, out int totalcnt)
{
try
{
SqlParameter[] prms = ParseToSqlParameters(info).ToArray();
DataTable dt = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager05", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
return(dt);
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
return(null);
}
}
public DataSet GetExhibitorDataSet(int exhibitionID)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"SELECT e.* FROM dbo.M_Exhibition_Exhibitors AS e
INNER JOIN dbo.M_Exhibition_Main AS m ON m.ExhibitionID = e.ExhibitionID
WHERE e.ExhibitionID={0} AND e.StateTypeID = 1 AND m.StateTypeID=1
ORDER BY e.ExhibitorPinYin ASC;
SELECT el.* FROM dbo.M_Exhibition_Exhibitor_Location AS el
INNER JOIN dbo.M_Exhibition_Exhibitors AS e ON e.ExhibitorID = el.ExhibitorID
INNER JOIN dbo.M_Exhibition_Main AS m ON m.ExhibitionID = e.ExhibitionID
WHERE el.StateTypeID=1 AND e.ExhibitionID ={0} AND e.StateTypeID = 1 AND m.StateTypeID=1
ORDER BY el.ExhibitiorLocationOrder ASC"
, exhibitionID.ToString());
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null));
}
public DataTable GetFuImageTable()
{
DataTable table = null;
StringBuilder strSql = new StringBuilder();
strSql.Append(@" SELECT * FROM NY_FuImages ");
DataSet ds = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
if (ds != null && ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
table = ds.Tables[0];
}
}
return(table);
}
public DataSet GetUserWithHaveFun()
{
DataSet ds = null;
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"SELECT * FROM M_V_System_User
WHERE Role_ID <> 100
ORDER BY Role_ID");
try
{
ds = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, strSql.ToString(), null);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
}
return(ds);
}
public DataTable QuerySysUserInfo(string uid)
{
SqlParameter[] prms =
{
new SqlParameter("@Uid", uid)
};
string sql = "select User_RoleID,c.User_Name, Role_Name from M_System_User_Role_Rel u inner join M_System_Role r on u.User_RoleID=r.Role_ID inner join M_System_User c on c.User_ID=u.User_ID where u.User_ID=@Uid";
try
{
return(SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql, prms).Tables[0]);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(null);
}
}
/// <summary>
/// 删除日历信息
/// </summary>
/// <param name="FestivalID"></param>
/// <returns></returns>
public string deleteFestival(FestivalEntity entity)
{
SqlTransaction trans = null;
string flg = "0";
string sql = "delete from M_Festival where FestivalID=@FestivalID";
string sql1 = "update M_EventItem set FestivalID=@Festivalnullid,Title2=@Title2,StartTime2=@StartTime2,EndTime2=@EndTime2,EventItemFlag=@EventItemFlag where FestivalID=@FestivalID";
string sql2 = "select EventItemID from M_EventItem where FestivalID=@FestivalID";
SqlParameter[] pars = { new SqlParameter("FestivalID", SqlDbType.UniqueIdentifier) };
pars[0].Value = entity.FestivalID;
SqlParameter[] pars1 = { new SqlParameter("FestivalID", SqlDbType.UniqueIdentifier),
new SqlParameter("Festivalnullid", SqlDbType.UniqueIdentifier),
new SqlParameter("Title2", SqlDbType.NVarChar,100),
new SqlParameter("StartTime2", SqlDbType.DateTime),
new SqlParameter("EndTime2", SqlDbType.DateTime),
new SqlParameter("EventItemFlag", SqlDbType.Int) };
pars1[0].Value = entity.FestivalID;
try
{
using (SqlConnection conn = new SqlConnection(SQlHelper.MyConnectStr))
{
conn.Open();
trans = conn.BeginTransaction();
DataTable db = SQlHelper.ExecuteDataset(trans, CommandType.Text, sql2, pars).Tables[0];
if (db.Rows.Count > 0)
{
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sql1, pars1);
}
SQlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, pars);
trans.Commit();
flg = "1";
}
}
catch (Exception e)
{
flg = "0";
if (trans != null)
{
trans.Rollback();
}
LogUtil.WriteLog(e);
}
return(flg);
}
public DataTable QueryRoleTableByPage(SysRoleSearchEntity entity, out int totalcnt)
{
totalcnt = 0;
DataTable table = null;
try
{
if (entity.UseDBPagination)
{
SqlParameter[] prms = ParseToSqlParameters(entity).ToArray();
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
}
else
{
List <SqlParameter> prmsList = new List <SqlParameter>();
string sql = "SELECT Role_ID,Role_Name,Convert(nvarchar(19), CreateTime,121) AS CreateTime FROM M_System_Role ";
string sqlWhere = " WHERE 1=1 ";
string sqlOrder = " ORDER BY Role_ID Asc";
if (!string.IsNullOrEmpty(entity.Role_Name))
{
prmsList.Add(new SqlParameter("@Role_Name", entity.Role_Name));
sqlWhere += " AND Role_Name like @Role_Name + '%' ";
}
if (entity.OrderfieldType == OrderFieldType.Desc)
{
sqlOrder = " ORDER BY Role_ID Desc";
}
sql = sql + sqlWhere + sqlOrder;
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql, prmsList.ToArray()).Tables[0];
totalcnt = table != null ? table.Rows.Count : 0;
}
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
}
return(table);
}