itemsp.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
{
 //获取根栏目
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetRootItem()
 {
  string SQL = @"SELECT [ItemID]
                     ,[ItemName]
                     ,[ItemIntro]
                 FROM [Item]
                 WHERE IsNull([ParentID],-1)=-1";
  DBTools.CreateStoredProcedure(SQL);
 }
 //根据父栏目ID获取栏目
 [SqlProcedure]
 public static void GetItemByParentID(int pParentID)
 {
  string SQL = @"SELECT [ItemID]
                     ,[ItemName]
                     ,[ItemIntro]
                 FROM [Item]
                 WHERE [ParentID]=@ParentID";
  SqlParameter[] parms=new SqlParameter[1];
  parms[0] = new SqlParameter("@ParentID", SqlDbType.Int);
  parms[0].Value = pParentID;
  DBTools.CreateStoredProcedure(SQL,parms);
 }
 //根据栏目ID获取栏目内容
 [SqlProcedure]
 public static void GetItemByID(int pItemID)
 {
  string SQL = @"SELECT [ItemID]
                     ,[ItemName]
                     ,[ItemIntro]
                     ,[ParentID]
                 FROM [Item]
                 WHERE [ItemID]=@ItemID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@ItemID", SqlDbType.Int);
  parms[0].Value = pItemID;
  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //添加栏目
 [SqlProcedure]
 public static void AddItem(string pItemName,string pItemIntro,int pParentID)
 {
  string SQL = @"INSERT INTO [Item]
                  ([ItemName],[ItemIntro],[ParentID])
                 VALUES
                   (@ItemName,@ItemIntro,@ParentID)";
  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@ParentID", SqlDbType.Int);
  parms[1] = new SqlParameter("@ItemName", SqlDbType.NVarChar,50);
  parms[2] = new SqlParameter("@ItemIntro", SqlDbType.NVarChar,250);
  if (pParentID != -1)
  {
   parms[0].Value = pParentID;
  }
  parms[0].IsNullable = true;
  parms[1].Value = pItemName;
  parms[2].Value = pItemIntro;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID修改栏目
  [SqlProcedure]
 public static void ModifyItemByID(string pItemName,string pItemIntro,int pItemID)
 {
  string SQL = @" UPDATE [Item]
                  SET [ItemName] = @ItemName,[ItemIntro] = @ItemIntro
                  WHERE [ItemID]=@ItemID";

  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@ItemName", SqlDbType.NVarChar,50);
  parms[1] = new SqlParameter("@ItemIntro", SqlDbType.NVarChar,250);
  parms[2] = new SqlParameter("@ItemID", SqlDbType.Int);

  parms[0].Value = pItemName;
  parms[1].Value = pItemIntro;
  parms[2].Value = pItemID;
  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID删除栏目
 [SqlProcedure]
 public static void DeleteItemByID(int pItemID)
 {
  string SQL = @"DELETE FROM [Item]
                 WHERE [ItemID]=@ItemID";

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

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //判断指定ID是否根栏目
 [SqlProcedure]
 public static void IsRootItem(int pItemID)
 {
  string SQL = @"SELECT [ItemID] FROM [Item]
                 WHERE [ItemID]=@ItemID
                 AND IsNull([ParentID],-1)=-1";

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

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