itemstoreprocedure.cs

来自「ASP.NET 2.0动态网站设计实例源代码,本书介绍了ASP.NET2.0的基」· CS 代码 · 共 111 行

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 GetAllItem()
 {
  string strSQL = "SELECT * FROM NewsItem ORDER BY NewsItemCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 [SqlProcedure]
 //获取首页显示栏目
 public static void GetHomeItem()
 {
  string strSQL = "SELECT NewsItemID,NewsItemName FROM NewsItem WHERE NewsShowAtHome=1 ORDER BY NewsItemCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 [SqlProcedure]
 //获取指定ID栏目信息
 public static void GetItemInfo(int pintItemID)
 {
  string strSQL = "SELECT * FROM NewsItem WHERE NewsItemID=@NewsItemID";
  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);
 }
 [SqlProcedure]
 //修改栏目信息
 public static void SetItemInfo(int pintItemID,string pstrItemName,string pstrItemEName,int pintShowAtHome)
 {
  string strSQL = @"UPDATE NewsItem SET NewsItemName=@NewsItemName,NewsItemEName=@NewsItemEName,NewsShowAtHome=@NewsShowAtHome
                 WHERE NewsItemID=@NewsItemID";
  SqlParameter[] parms = new SqlParameter[4];
  parms[0] = new SqlParameter("@NewsItemID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintItemID;
  parms[1] = new SqlParameter("@NewsItemName", SqlDbType.NVarChar, 10);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrItemName;
  parms[2] = new SqlParameter("@NewsItemEName", SqlDbType.NVarChar, 10);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pstrItemEName;
  parms[3] = new SqlParameter("@NewsShowAtHome", SqlDbType.Int);
  parms[3].Direction = ParameterDirection.Input;
  parms[3].Value = pintShowAtHome;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //删除指定ID栏目
 public static void DeleteItem(int pintItemID)
 {
  string strSQL = "DELETE FROM NewsItem WHERE NewsItemID=@NewsItemID";
  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);
 }
 [SqlProcedure]
 //添加新栏目
 public static void AddItem(string pstrNewsItemName,string pstrNewsItemEName,int pintShowAtHome)
 {
  string strSQL = @"INSERT INTO NewsItem (NewsItemName,NewsItemEName,NewsShowAtHome) 
                  VALUES(@NewsItemName,@NewsItemEName,@NewsShowAtHome);SELECT @@IDENTITY";
  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@NewsItemName", SqlDbType.NVarChar, 10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrNewsItemName;
  parms[1] = new SqlParameter("@NewsItemEName", SqlDbType.NVarChar, 10);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrNewsItemEName;
  parms[2] = new SqlParameter("@NewsShowAtHome", SqlDbType.Int);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pintShowAtHome;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

 [SqlProcedure]
 //获取指定名称栏目信息
 public static void GetItemByItemName(string pstrItemName)
 {
  string strSQL = "SELECT NewsItemID FROM NewsItem WHERE NewsItemName=@NewsItemName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsItemName", SqlDbType.NVarChar,10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrItemName;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

 [SqlProcedure]
 //获取指定名称栏目信息
 public static void GetItemByItemEName(string pstrItemEName)
 {
  string strSQL = "SELECT NewsItemID FROM NewsItem WHERE NewsItemEName=@NewsItemEName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsItemEName", SqlDbType.NVarChar, 10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrItemEName;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
};