newsarticle.cs

ASP.NET 2.0动态网站设计实例源代码,本书介绍了ASP.NET2.0的基础知识

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using tsingjun.NewsSQL;

public partial class StoredProcedures
{
 [Microsoft.SqlServer.Server.SqlProcedure]
 //获取新闻列表
 public static void GetNewsArticle()
 {
  string strSQL = "SELECT * FROM NewsArticle ORDER BY NewsCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //获取未经审阅新闻列表
 [SqlProcedure]
 public static void GetNewsUnCheckedAritcle()
 {
  string strSQL = @"SELECT NewsID,NewsUserName,NewsTitle,NewsCreateDate 
                    FROM NewsArticle INNER JOIN NewsUser 
                    ON NewsArticle.NewsUserID=NewsUser.NewsUserID 
                    WHERE NewsChecked=0 ORDER BY NewsCreateDate DESC;";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //获取已经审核的新闻列表
 [SqlProcedure]
 public static void GetNewsCheckedAritcle()
 {
  string strSQL = "SELECT * FROM NewsArticle WHERE NewsChecked=1 ORDER BY NewsCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //获取指定ID的新闻的内容
 [SqlProcedure]
 public static void GetNewsInfo(int pintNewsID)
 {
  string strSQL = "SELECT * FROM NewsArticle WHERE NewsID=@NewsID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL,parms);
 }
  //获取指定栏目ID的新闻的内容
 [SqlProcedure]
 public static void GetNewsByItemID(int pintNewsItemID)
 {
  string strSQL = @"SELECT NewsTitle,NewsUserTrueName,NewsCreateDate,NewsFileURL,NewsID 
                    FROM NewsArticle 
                    INNER JOIN NewsUser ON NewsArticle.NewsUserID=NewsUser.NewsUserID
                    WHERE NewsItemID=@NewsItemID ";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsItemID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsItemID;
  DBTools.CreateStoredProcedure(strSQL,parms);
 }
 //获取首页新闻的内容
 [SqlProcedure]
 public static void GetNewsAtHome()
 {
  string strSQL = "SELECT TOP 1 NewsContent,NewsImageURL,NewsFileURL FROM NewsArticle WHERE NewsHomeNews=1 ORDER BY NewsCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //设置指定ID新闻为首页新闻
 [SqlProcedure]
 public static void SetNewsAtHome(int pintNewsID)
 {
  string strSQL = "UPDATE NewsArticle SET NewsHomeNews=1 WHERE NewsID=@NewsID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //获取指定ID新闻是否为图片新闻
 [SqlProcedure]
 public static void IsImageNews(int pintNewsID)
 {
  string strSQL = "SELECT NewsID FROM NewsArticle WHERE NewsID=@NewsID AND NewsImage=1";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //获取已设置为首页新闻的列表
 [SqlProcedure]
 public static void GetNewsAtHomeList()
 {
  string strSQL = "SELECT * FROM NewsArticle WHERE NewsHomeNews=1";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //根据新闻标题关键字查询新闻列表
 [SqlProcedure]
 public static void GetNewsByTitle(string pstrNewsTitle)
 {
  string strSQL = @"SELECT NewsTitle,NewsUserTrueName,NewsCreateDate,NewsFileURL,NewsID 
                    FROM NewsArticle 
                    INNER JOIN NewsUser ON NewsArticle.NewsUserID=NewsUser.NewsUserID
                    WHERE NewsTitle like @NewsTitle AND NewsChecked=1
                    ORDER BY NewsCreateDate DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsTitle", SqlDbType.NVarChar,50);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = "%"+pstrNewsTitle+"%";
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //根据新闻内容关键字查询新闻列表
 [SqlProcedure]
 public static void GetNewsByContent(string pstrNewsContent)
 {
  string strSQL = @"SELECT NewsTitle,NewsUserTrueName,NewsCreateDate,NewsFileURL,NewsID 
                    FROM NewsArticle 
                    INNER JOIN NewsUser ON NewsArticle.NewsUserID=NewsUser.NewsUserID
                    WHERE NewsContent like @NewsContent AND NewsChecked=1
                    ORDER BY NewsCreateDate DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsContent", SqlDbType.NText);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = "%"+pstrNewsContent+"%";
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //获取相关新闻
 [SqlProcedure]
 public static void GetRelativeNews(string pstrNewsKeyword,int pintNewsID)
 {
  string[] strarrNewsKeyword = pstrNewsKeyword.Split(new char[] { ',' });
  string strSQL = "SELECT TOP 10 NewsFileURL,NewsTitle,NewsCreateDate FROM NewsArticle WHERE NewsID<>@NewsID AND ";
  if (strarrNewsKeyword.Length == 1)
  {
   strSQL += " NewsKeyword LIKE '%" + strarrNewsKeyword[0] + "%' ";
  }
  else
  {
   for (int i = 0; i < strarrNewsKeyword.Length - 2; i++)
   {
    strSQL += " NewsKeyword LIKE '%" + strarrNewsKeyword[i] + "%' OR ";
   }
   strSQL += " NewsKeyword LIKE '%" + strarrNewsKeyword[strarrNewsKeyword.Length - 1] + "%' ";
  }
  strSQL += " AND NewsChecked=1 ORDER BY NewsCreateDate DESC ";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //获取推荐新闻列表
 [SqlProcedure]
 public static void GetNewsRecommendList()
 {
  string strSQL = "SELECT TOP 10 NewsFileURL,NewsCreateDate,NewsTitle FROM NewsArticle WHERE NewsRecommend=1 AND NewsChecked=1 ORDER BY NewsCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //获取最火热Top10新闻列表
 [SqlProcedure]
 public static void GetNewsHot()
 {
  string strSQL = "SELECT TOP 10 NewsFileURL,NewsClicked,NewsTitle FROM NewsArticle WHERE NewsChecked=1  ORDER BY NewsClicked DESC,NewsCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 //删除指定ID新闻
 [SqlProcedure]
 public static void DeleteNewsByID(int pintNewsID)
 {
  string strSQL = "DELETE FROM NewsArticle WHERE NewsID=@NewsID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //设置新闻为已审核新闻
 [SqlProcedure]
 public static void SetNewsChecked(int pintNewsID,string pstrFileURL,int pintItemID,int NewsCheckUserID,string pstrNewsKeyword,int pintNewsHomeNews,int pintNewsRecommend)
 {
  string strSQL = @"UPDATE NewsArticle SET NewsFileURL=@NewsFileURL,NewsItemID=@NewsItemID,NewsCheckUserID=@NewsCheckUserID,
                  NewsKeyword=@NewsKeyword,NewsHomeNews=@NewsHomeNews,NewsRecommend=@NewsRecommend,NewsChecked=1 WHERE NewsID=@NewsID;
                  UPDATE NewsUser SET NewsUserCredit=NewsUserCredit+1 WHERE NewsUserID=(SELECT NewsUserID FROM NewsArticle WHERE NewsID=@NewsID)";
  SqlParameter[] parms = new SqlParameter[7];
  parms[0] = new SqlParameter("@NewsFileURL", SqlDbType.NVarChar,30);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrFileURL;
  parms[1] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pintNewsID;
  parms[2] = new SqlParameter("@NewsItemID", SqlDbType.Int);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pintItemID;
  parms[3] = new SqlParameter("@NewsCheckUserID", SqlDbType.Int);
  parms[3].Direction = ParameterDirection.Input;
  parms[3].Value = NewsCheckUserID;
  parms[4] = new SqlParameter("@NewsKeyword", SqlDbType.NVarChar, 100);
  parms[4].Direction = ParameterDirection.Input;
  parms[4].Value = pstrNewsKeyword;
  parms[5] = new SqlParameter("@NewsHomeNews", SqlDbType.Int);
  parms[5].Direction = ParameterDirection.Input;
  parms[5].Value = pintNewsHomeNews;
  parms[6] = new SqlParameter("@NewsRecommend", SqlDbType.Int);
  parms[6].Direction = ParameterDirection.Input;
  parms[6].Value = pintNewsRecommend;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //根据用户名关键字获取新闻列表
 [SqlProcedure]
 public static void GetNewsByUserName(string pstrNewsUserName)
 {
  string strSQL = @"SELECT NewsTitle,NewsUserTrueName,NewsCreateDate,NewsFileURL,NewsID 
                    FROM NewsArticle 
                    INNER JOIN NewsUser ON NewsArticle.NewsUserID=NewsUser.NewsUserID
                    WHERE NewsUserTrueName=@NewUserName AND NewsChecked=1
                    ORDER BY NewsCreateDate DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewUserName", SqlDbType.NVarChar,10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrNewsUserName;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

 //确认指定ID新闻是否通过审核
 [SqlProcedure]
 public static void IsChecked(int pintNewsID)
 {
  string strSQL = "SELECT NewsID FROM NewsArticle WHERE NewsID=@NewsID AND NewsChecked=1";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //添加新闻
 [SqlProcedure]
 public static void AddNews(int pintNewsUserID,string pstrNewsTitle,string pstrNewsContent,int pintNewsImage,string pstrNewsImageURL)
 {
  string strSQL = @"INSERT INTO NewsArticle (NewsUserID,NewsTitle,NewsContent,NewsImage,NewsImageURL) VALUES(
                  @NewsUserID,@NewsTitle,@NewsContent,@NewsImage,@NewsImageURL);SELECT @@IDENTITY";
  SqlParameter[] parms = new SqlParameter[5];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsUserID;
  parms[1] = new SqlParameter("@NewsTitle", SqlDbType.NVarChar,50);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrNewsTitle;
  parms[2] = new SqlParameter("@NewsContent", SqlDbType.NText);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pstrNewsContent;
  parms[3] = new SqlParameter("@NewsImage", SqlDbType.Int);
  parms[3].Direction = ParameterDirection.Input;
  parms[3].Value = pintNewsImage;
  parms[4] = new SqlParameter("@NewsImageURL", SqlDbType.NVarChar, 30);
  parms[4].Direction = ParameterDirection.Input;
  parms[4].Value = pstrNewsImageURL;
  parms[4].IsNullable = true;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //根据关键字获取新闻
 [SqlProcedure]
 public static void GetNewsByKeyword(string pstrKeyword)
 {
  string strSQL = @"SELECT NewsTitle,NewsUserTrueName,NewsCreateDate,NewsFileURL,NewsID 
                    FROM NewsArticle 
                    INNER JOIN NewsUser ON NewsArticle.NewsUserID=NewsUser.NewsUserID
                    WHERE NewsKeyword=@NewsKeyword AND NewsChecked=1
                    ORDER BY NewsCreateDate DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsKeyword", SqlDbType.NVarChar, 100);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrKeyword;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

 //设置新闻点击率
 [SqlProcedure]
 public static void SetNewsClicked(int pintNewsID)
 {
  string strSQL = @"UPDATE NewsArticle 
                    SET NewsClicked=NewsClicked+1
                    WHERE NewsID=@NewsID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintNewsID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 //获取首页指定ID的栏目新闻列表
 [SqlProcedure]
 public static void GetHomeItemNews(int pintItemID)
 {
  string strSQL = @"SELECT NewsTitle,NewsFileURL,NewsCreateDate FROM NewsArticle
                    WHERE NewsItemID=@NewsItemID AND NewsChecked=1
                    ORDER BY NewsCreateDate DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsItemID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintItemID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 
};