aritclesp.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;


public partial class StoredProcedures
{
 //根据栏目ID获取主题列表
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetArticleByItemID(int pItemID)
 {
  string SQL = @"SELECT [PostID]
                       ,[UserName]
                       ,[Title]
                       ,[Content]
                       ,[AttachmentID]
                       ,[PostTime]
                       ,[LastModified]
                 FROM [Posts]
                 WHERE [ItemID]=@ItemID
                 AND IsNull(PostRePostID,-1)=-1
                 ORDER BY PostTime DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@ItemID", SqlDbType.Int);
  parms[0].Value = pItemID;
  DBTools.CreateStoredProcedure(SQL,parms);
 }
 //根据主题ID获取文章列表
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetArticleByPostID(int pPostID)
 {
  string SQL = @"SELECT [PostID]
                       ,[UserName]
                       ,[Title]
                       ,[Content]
                       ,[AttachmentID]
                       ,[PostTime]
                       ,[LastModified]
                       ,[ItemID]
                 FROM [Posts]
                 WHERE PostRePostID=@PostID
                 OR PostID=@PostID
                 ORDER BY PostTime ASC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@PostID", SqlDbType.Int);
  parms[0].Value = pPostID;
  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据主题ID获取文章内容
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetPostByPostID(int pPostID)
 {
  string SQL = @"SELECT [ItemID]
                       ,[UserName]
                       ,[Title]
                       ,[Content]
                       ,[AttachmentID]
                       ,[PostTime]
                       ,[LastModified]
                 FROM [Posts]
                 WHERE [PostID]=@PostID
                 ORDER BY PostTime DESC";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@PostID", SqlDbType.Int);
  parms[0].Value = pPostID;
  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //添加新主题
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void AddPost(int pItemID,string pUserName,string pTitle,string pContent,int pAttachmentID)
 {
  string SQL = @"INSERT INTO [Posts]
                       ([ItemID]
                       ,[UserName]
                       ,[Title]
                       ,[Content]
                       ,[AttachmentID])
                 VALUES
                       (@ItemID
                       ,@UserName
                       ,@Title
                       ,@Content
                       ,@AttachmentID);
                 UPDATE [UserInfo]
                 SET [Posted]=[Posted]+1
                 WHERE [UserName]=@UserName;
                 SELECT @@IDENTITY;";
  SqlParameter[] parms = new SqlParameter[5];
  parms[0] = new SqlParameter("@ItemID", SqlDbType.Int);
  parms[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
  parms[2] = new SqlParameter("@Title", SqlDbType.NVarChar, 150);
  parms[3] = new SqlParameter("@Content", SqlDbType.NText);
  parms[4] = new SqlParameter("@AttachmentID", SqlDbType.Int);

  parms[0].Value = pItemID;
  parms[1].Value=pUserName;
  parms[2].Value=pTitle;
  parms[3].Value=pContent;
  if(pAttachmentID!=-1)
  parms[4].Value = pAttachmentID;
  parms[4].IsNullable = true;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //添加新回复
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void AddRePost(int pItemID, string pUserName, string pTitle, 
  string pContent,int pRePostID)
 {
  string SQL = @"INSERT INTO [Posts]
                       ([ItemID]
                       ,[UserName]
                       ,[Title]
                       ,[Content]
                       ,[PostRePostID])
                 VALUES
                       (@ItemID
                       ,@UserName
                       ,@Title
                       ,@Content
                       ,@PostRePostID);
                 UPDATE [UserInfo]
                 SET [RePosted]=[RePosted]+1
                 WHERE [UserName]=@UserName;";

  SqlParameter[] parms = new SqlParameter[5];
  parms[0] = new SqlParameter("@ItemID", SqlDbType.Int);
  parms[1] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
  parms[2] = new SqlParameter("@Title", SqlDbType.NVarChar, 150);
  parms[3] = new SqlParameter("@Content", SqlDbType.NText);
  parms[4] = new SqlParameter("@PostRePostID", SqlDbType.Int);

  parms[0].Value = pItemID;
  parms[1].Value = pUserName;
  parms[2].Value = pTitle;
  parms[3].Value = pContent;
  parms[4].Value = pRePostID;
  DBTools.CreateStoredProcedure(SQL,parms);
 }
 //根据ID修改文章内容和标题
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void ModifyArticleByID(string pTitle, string pContent, int pPostID)
 {
  string SQL = @"UPDATE [Posts]
                 SET [Title] = @Title
                    ,[Content] = @Content
                    ,[LastModified] =getdate() 
                 WHERE [PostID]=@PostID";
  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@PostID", SqlDbType.Int);
  parms[1] = new SqlParameter("@Title", SqlDbType.NVarChar, 150);
  parms[2] = new SqlParameter("@Content", SqlDbType.NText);

  parms[0].Value = pPostID;
  parms[1].Value = pTitle;
  parms[2].Value = pContent;

  DBTools.CreateStoredProcedure(SQL,parms);
 }
 //根据ID删除文章（包括主题）
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void DeletePost(int pPostID)
 {
  string SQL = @"DELETE FROM [Posts]
                 WHERE [PostID]=@PostID";

  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@PostID", SqlDbType.Int);
  parms[0].Value = pPostID;

  DBTools.CreateStoredProcedure(SQL,parms);
 }
};