/// <summary>
/// 生成资讯类别信息实体
/// </summary>
/// <param name="xml"></param>
/// <param name="model"></param>
/// <returns></returns>
private void GetClassBySqlXml(string xml, ref Model.MTravelArticle model)
{
if (string.IsNullOrEmpty(xml)) return;
var xRoot = XElement.Parse(xml);
var xRows = Utils.GetXElements(xRoot, "row");
if (xRows == null || !xRows.Any()) return;
if (model == null) model = new Model.MTravelArticle();
foreach (var t in xRows)
{
model.ClassName = Utils.GetXAttributeValue(t, "ClassName");
model.ArticleType = (ArticleType)Utils.GetInt(Utils.GetXAttributeValue(t, "IsSystem"));
break;
}
}
/// <summary>
/// 获取首页底部的四个帮助信息
/// </summary>
/// <returns></returns>
public IList<Model.MTravelArticle> GetHeadZiXun()
{
IList<Model.MTravelArticle> resultList;
var strSql = new StringBuilder();
strSql.AppendFormat(
" SELECT top 3 ArticleID,Source,ArticleTitle,ImgPath,Description,ArticleText,ArticleTag,TitleColor,KeyWords,ClassId,(select top 1 ClassName,IsSystem from tbl_TravelArticleClass where ClassId=tbl_TravelArticle.ClassId for xml raw,root('Root')) as ClassName,IsFrontPage,IsHot,IssueTime,OperatorId,(select ContactName from tbl_User where UserID=tbl_TravelArticle.OperatorId) as OperatorName,LinkUrl,Click,SortRule FROM tbl_TravelArticle WHERE exists(select 1 from tbl_TravelArticleClass where ClassId = tbl_TravelArticle.ClassId and IsSystem = {0}) order by SortRule DESC,IssueTime DESC ; ",
(int)ArticleType.安全指南);
strSql.AppendFormat(
" SELECT top 3 ArticleID,Source,ArticleTitle,ImgPath,Description,ArticleText,ArticleTag,TitleColor,KeyWords,ClassId,(select top 1 ClassName,IsSystem from tbl_TravelArticleClass where ClassId=tbl_TravelArticle.ClassId for xml raw,root('Root')) as ClassName,IsFrontPage,IsHot,IssueTime,OperatorId,(select ContactName from tbl_User where UserID=tbl_TravelArticle.OperatorId) as OperatorName,LinkUrl,Click,SortRule FROM tbl_TravelArticle WHERE exists(select 1 from tbl_TravelArticleClass where ClassId = tbl_TravelArticle.ClassId and IsSystem = {0} ) order by SortRule DESC,IssueTime DESC ; ",
(int)ArticleType.订购指南);
strSql.AppendFormat(
" SELECT top 3 ArticleID,Source,ArticleTitle,ImgPath,Description,ArticleText,ArticleTag,TitleColor,KeyWords,ClassId,(select top 1 ClassName,IsSystem from tbl_TravelArticleClass where ClassId=tbl_TravelArticle.ClassId for xml raw,root('Root')) as ClassName,IsFrontPage,IsHot,IssueTime,OperatorId,(select ContactName from tbl_User where UserID=tbl_TravelArticle.OperatorId) as OperatorName,LinkUrl,Click,SortRule FROM tbl_TravelArticle WHERE exists(select 1 from tbl_TravelArticleClass where ClassId = tbl_TravelArticle.ClassId and IsSystem = {0} ) order by SortRule DESC,IssueTime DESC ; ",
(int)ArticleType.沟通与订阅);
strSql.AppendFormat(
" SELECT top 3 ArticleID,Source,ArticleTitle,ImgPath,Description,ArticleText,ArticleTag,TitleColor,KeyWords,ClassId,(select top 1 ClassName,IsSystem from tbl_TravelArticleClass where ClassId=tbl_TravelArticle.ClassId for xml raw,root('Root')) as ClassName,IsFrontPage,IsHot,IssueTime,OperatorId,(select ContactName from tbl_User where UserID=tbl_TravelArticle.OperatorId) as OperatorName,LinkUrl,Click,SortRule FROM tbl_TravelArticle WHERE exists(select 1 from tbl_TravelArticleClass where ClassId = tbl_TravelArticle.ClassId and IsSystem = {0} ) order by SortRule DESC,IssueTime DESC ; ",
(int)ArticleType.支付与取票);
DbCommand dc = this._db.GetSqlStringCommand(strSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
resultList = new List<Model.MTravelArticle>();
while (dr.Read())
{
var model = new Model.MTravelArticle
{
ArticleID = dr.GetString(dr.GetOrdinal("ArticleID")),
Source = dr.IsDBNull(dr.GetOrdinal("Source")) ? "" : dr.GetString(dr.GetOrdinal("Source")),
ArticleTitle =
dr.IsDBNull(dr.GetOrdinal("ArticleTitle"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleTitle")),
ImgPath = dr.IsDBNull(dr.GetOrdinal("ImgPath")) ? "" : dr.GetString(dr.GetOrdinal("ImgPath")),
Description =
dr.IsDBNull(dr.GetOrdinal("Description"))
? ""
: dr.GetString(dr.GetOrdinal("Description")),
ArticleText =
dr.IsDBNull(dr.GetOrdinal("ArticleText"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleText")),
ArticleTag =
dr.IsDBNull(dr.GetOrdinal("ArticleTag")) ? "" : dr.GetString(dr.GetOrdinal("ArticleTag")),
TitleColor =
dr.IsDBNull(dr.GetOrdinal("TitleColor")) ? "" : dr.GetString(dr.GetOrdinal("TitleColor")),
KeyWords =
dr.IsDBNull(dr.GetOrdinal("KeyWords")) ? "" : dr.GetString(dr.GetOrdinal("KeyWords")),
ClassId = dr.GetInt32(dr.GetOrdinal("ClassId")),
IsFrontPage = dr.GetString(dr.GetOrdinal("IsFrontPage")) == "1" ? true : false,
IsHot = dr.GetString(dr.GetOrdinal("IsHot")) == "1" ? true : false,
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
OperatorId =
dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId")),
OperatorName =
dr.IsDBNull(dr.GetOrdinal("OperatorName"))
? ""
: dr.GetString(dr.GetOrdinal("OperatorName")),
LinkUrl = dr.IsDBNull(dr.GetOrdinal("LinkUrl")) ? "" : dr.GetString(dr.GetOrdinal("LinkUrl")),
Click = dr.IsDBNull(dr.GetOrdinal("Click")) ? 0 : dr.GetInt32(dr.GetOrdinal("Click")),
SortRule = dr.IsDBNull(dr.GetOrdinal("SortRule")) ? 0 : dr.GetInt32(dr.GetOrdinal("SortRule"))
};
if (!dr.IsDBNull(dr.GetOrdinal("ClassName")))
{
this.GetClassBySqlXml(dr.GetString(dr.GetOrdinal("ClassName")), ref model);
}
resultList.Add(model);
}
dr.NextResult();
while (dr.Read())
{
var model = new Model.MTravelArticle
{
ArticleID = dr.GetString(dr.GetOrdinal("ArticleID")),
Source = dr.IsDBNull(dr.GetOrdinal("Source")) ? "" : dr.GetString(dr.GetOrdinal("Source")),
ArticleTitle =
dr.IsDBNull(dr.GetOrdinal("ArticleTitle"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleTitle")),
ImgPath = dr.IsDBNull(dr.GetOrdinal("ImgPath")) ? "" : dr.GetString(dr.GetOrdinal("ImgPath")),
Description =
dr.IsDBNull(dr.GetOrdinal("Description"))
? ""
: dr.GetString(dr.GetOrdinal("Description")),
ArticleText =
dr.IsDBNull(dr.GetOrdinal("ArticleText"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleText")),
ArticleTag =
dr.IsDBNull(dr.GetOrdinal("ArticleTag")) ? "" : dr.GetString(dr.GetOrdinal("ArticleTag")),
TitleColor =
dr.IsDBNull(dr.GetOrdinal("TitleColor")) ? "" : dr.GetString(dr.GetOrdinal("TitleColor")),
KeyWords =
dr.IsDBNull(dr.GetOrdinal("KeyWords")) ? "" : dr.GetString(dr.GetOrdinal("KeyWords")),
ClassId = dr.GetInt32(dr.GetOrdinal("ClassId")),
IsFrontPage = dr.GetString(dr.GetOrdinal("IsFrontPage")) == "1" ? true : false,
IsHot = dr.GetString(dr.GetOrdinal("IsHot")) == "1" ? true : false,
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
OperatorId =
dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId")),
OperatorName =
dr.IsDBNull(dr.GetOrdinal("OperatorName"))
? ""
: dr.GetString(dr.GetOrdinal("OperatorName")),
LinkUrl = dr.IsDBNull(dr.GetOrdinal("LinkUrl")) ? "" : dr.GetString(dr.GetOrdinal("LinkUrl")),
Click = dr.IsDBNull(dr.GetOrdinal("Click")) ? 0 : dr.GetInt32(dr.GetOrdinal("Click")),
SortRule = dr.IsDBNull(dr.GetOrdinal("SortRule")) ? 0 : dr.GetInt32(dr.GetOrdinal("SortRule"))
};
if (!dr.IsDBNull(dr.GetOrdinal("ClassName")))
{
this.GetClassBySqlXml(dr.GetString(dr.GetOrdinal("ClassName")), ref model);
}
resultList.Add(model);
}
dr.NextResult();
while (dr.Read())
{
var model = new Model.MTravelArticle
{
ArticleID = dr.GetString(dr.GetOrdinal("ArticleID")),
Source = dr.IsDBNull(dr.GetOrdinal("Source")) ? "" : dr.GetString(dr.GetOrdinal("Source")),
ArticleTitle =
dr.IsDBNull(dr.GetOrdinal("ArticleTitle"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleTitle")),
ImgPath = dr.IsDBNull(dr.GetOrdinal("ImgPath")) ? "" : dr.GetString(dr.GetOrdinal("ImgPath")),
Description =
dr.IsDBNull(dr.GetOrdinal("Description"))
? ""
: dr.GetString(dr.GetOrdinal("Description")),
ArticleText =
dr.IsDBNull(dr.GetOrdinal("ArticleText"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleText")),
ArticleTag =
dr.IsDBNull(dr.GetOrdinal("ArticleTag")) ? "" : dr.GetString(dr.GetOrdinal("ArticleTag")),
TitleColor =
dr.IsDBNull(dr.GetOrdinal("TitleColor")) ? "" : dr.GetString(dr.GetOrdinal("TitleColor")),
KeyWords =
dr.IsDBNull(dr.GetOrdinal("KeyWords")) ? "" : dr.GetString(dr.GetOrdinal("KeyWords")),
ClassId = dr.GetInt32(dr.GetOrdinal("ClassId")),
IsFrontPage = dr.GetString(dr.GetOrdinal("IsFrontPage")) == "1" ? true : false,
IsHot = dr.GetString(dr.GetOrdinal("IsHot")) == "1" ? true : false,
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
OperatorId =
dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId")),
OperatorName =
dr.IsDBNull(dr.GetOrdinal("OperatorName"))
? ""
: dr.GetString(dr.GetOrdinal("OperatorName")),
LinkUrl = dr.IsDBNull(dr.GetOrdinal("LinkUrl")) ? "" : dr.GetString(dr.GetOrdinal("LinkUrl")),
Click = dr.IsDBNull(dr.GetOrdinal("Click")) ? 0 : dr.GetInt32(dr.GetOrdinal("Click")),
SortRule = dr.IsDBNull(dr.GetOrdinal("SortRule")) ? 0 : dr.GetInt32(dr.GetOrdinal("SortRule"))
};
if (!dr.IsDBNull(dr.GetOrdinal("ClassName")))
{
this.GetClassBySqlXml(dr.GetString(dr.GetOrdinal("ClassName")), ref model);
}
resultList.Add(model);
}
dr.NextResult();
while (dr.Read())
{
var model = new Model.MTravelArticle
{
ArticleID = dr.GetString(dr.GetOrdinal("ArticleID")),
Source = dr.IsDBNull(dr.GetOrdinal("Source")) ? "" : dr.GetString(dr.GetOrdinal("Source")),
ArticleTitle =
dr.IsDBNull(dr.GetOrdinal("ArticleTitle"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleTitle")),
ImgPath = dr.IsDBNull(dr.GetOrdinal("ImgPath")) ? "" : dr.GetString(dr.GetOrdinal("ImgPath")),
Description =
dr.IsDBNull(dr.GetOrdinal("Description"))
? ""
: dr.GetString(dr.GetOrdinal("Description")),
ArticleText =
dr.IsDBNull(dr.GetOrdinal("ArticleText"))
? ""
: dr.GetString(dr.GetOrdinal("ArticleText")),
ArticleTag =
dr.IsDBNull(dr.GetOrdinal("ArticleTag")) ? "" : dr.GetString(dr.GetOrdinal("ArticleTag")),
TitleColor =
dr.IsDBNull(dr.GetOrdinal("TitleColor")) ? "" : dr.GetString(dr.GetOrdinal("TitleColor")),
KeyWords =
dr.IsDBNull(dr.GetOrdinal("KeyWords")) ? "" : dr.GetString(dr.GetOrdinal("KeyWords")),
ClassId = dr.GetInt32(dr.GetOrdinal("ClassId")),
IsFrontPage = dr.GetString(dr.GetOrdinal("IsFrontPage")) == "1" ? true : false,
IsHot = dr.GetString(dr.GetOrdinal("IsHot")) == "1" ? true : false,
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
OperatorId =
dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId")),
OperatorName =
dr.IsDBNull(dr.GetOrdinal("OperatorName"))
? ""
: dr.GetString(dr.GetOrdinal("OperatorName")),
LinkUrl = dr.IsDBNull(dr.GetOrdinal("LinkUrl")) ? "" : dr.GetString(dr.GetOrdinal("LinkUrl")),
Click = dr.IsDBNull(dr.GetOrdinal("Click")) ? 0 : dr.GetInt32(dr.GetOrdinal("Click")),
SortRule = dr.IsDBNull(dr.GetOrdinal("SortRule")) ? 0 : dr.GetInt32(dr.GetOrdinal("SortRule"))
};
if (!dr.IsDBNull(dr.GetOrdinal("ClassName")))
{
this.GetClassBySqlXml(dr.GetString(dr.GetOrdinal("ClassName")), ref model);
}
resultList.Add(model);
}
}
return resultList;
}
