userstoredprocedure.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 IsExist(string pstrUserName)
 {
   string strSQL = "SELECT NewsUserID FROM NewsUser WHERE NewsUserName= @NewsUserName;";
   SqlParameter[] parms = new SqlParameter[1];
   parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar, 10);
   parms[0].Direction = ParameterDirection.Input;
   parms[0].Value = pstrUserName;
   DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //判断用户名密码是否符合
 public static void Login(string pstrUserName, string pstrPasword)
 {
   string strSQL = @"SELECT NewsUserID 
                   FROM NewsUser
                   WHERE NewsUserName=@NewsUserName AND NewsUserPassword=@NewsUserPassword;";
   SqlParameter[] parms = new SqlParameter[2];
   parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar, 10);
   parms[0].Direction = ParameterDirection.Input;
   parms[0].Value = pstrUserName;
   parms[1] = new SqlParameter("@NewsUserPassword", SqlDbType.NVarChar, 32);
   parms[1].Direction = ParameterDirection.Input;
   parms[1].Value = pstrPasword;
   DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //注册用户
 public static void Register(string pstrUserName, string pstrPassword,string pstrEmail)
 {
   string strSQL = @"INSERT INTO NewsUser (NewsUserName,NewsUserPassword,NewsUserEmail) 
                   VALUES(@NewsUserName,@NewsUserPassword,@NewsUserEmail)
                   SELECT @@IDENTITY;";
   SqlParameter[] parms = new SqlParameter[3];
   parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar, 10);
   parms[0].Direction = ParameterDirection.Input;
   parms[0].Value = pstrUserName;
   parms[1] = new SqlParameter("@NewsUserPassword", SqlDbType.NVarChar, 32);
   parms[1].Direction = ParameterDirection.Input;
   parms[1].Value = pstrPassword;
   parms[2] = new SqlParameter("@NewsUserEmail", SqlDbType.NVarChar, 60);
   parms[2].Direction = ParameterDirection.Input;
   parms[2].Value = pstrEmail;
   DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //获取用户基本信息
 public static void GetBasicInfo(int pintUserID)
 {
  string strSQL = @"SELECT NewsUserName,NewsUserActived,NewsUserCredit,NewsUserPrivilege,NewsUserCreateDate,NewsUserEmail  
                  FROM NewsUser 
                  WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //获取用户扩展信息
 public static void GetExtendInfo(int pintUserID)
 {
  string strSQL = @"SELECT NewsUserTrueName,NewsUserAddress,NewsUserZipcode,NewsUserTelephone,NewsUserMobile,NewsUserEmail
                FROM NewsUser
                WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //设置用户扩展信息
 public static void SetExtendInfo(int pintUserID, string pstrTrueName, string pstrAddress, string pstrZipcode, string pstrTelephone, string pstrMobile)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserTrueName=@NewsUserTrueName,
                  NewsUserAddress=@NewsUserAddress,NewsUserZipcode=@NewsUserZipcode
                  NewsUserTelephone=@NewsUserTelephone,NewsUserMobile=@NewsUserMobile
                  WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[6];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  parms[1] = new SqlParameter("@NewsUserTrueName", SqlDbType.NVarChar,8);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrTrueName;
  parms[2] = new SqlParameter("@NewsUserAddress", SqlDbType.NVarChar,100);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pstrAddress;
  parms[3] = new SqlParameter("@NewsUserZipcode", SqlDbType.NVarChar,20);
  parms[3].Direction = ParameterDirection.Input;
  parms[3].Value = pstrZipcode;
  parms[4] = new SqlParameter("@NewsUserTelephone", SqlDbType.NVarChar, 20);
  parms[4].Direction = ParameterDirection.Input;
  parms[4].Value = pstrTelephone;
  parms[5] = new SqlParameter("@NewsUserMobile", SqlDbType.NVarChar, 20);
  parms[5].Direction = ParameterDirection.Input;
  parms[5].Value = pstrMobile;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //修改密码
 public static void ChangePassword(string pstrUserName, string pstrPassword)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserPassword=@NewsUserPassword
                WHERE NewsUserName=@NewsUserName";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar,50);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrUserName;
  parms[1] = new SqlParameter("@NewsUserPassword", SqlDbType.NVarChar,32);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrPassword;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //设置用户权限
 public static void SetUserPrivilege(int pintUserID, int pintPrivilege)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserPrivilege=@NewsUserPrivilege
                WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  parms[1] = new SqlParameter("@NewsUserPrivilege", SqlDbType.TinyInt);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pintPrivilege;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //设置用是否激活
 public static void SetUserActived(int pintUserID, int pintActived)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserActived=@NewsUserActived
                WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  parms[1] = new SqlParameter("@NewsUserActived", SqlDbType.TinyInt);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pintActived;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //增加用户发文章数
 public static void ADDUserCredit(int pintUserID)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserCredit=NewsUserCredit+1
                WHERE NewsUserID=@NewsUserID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //获取用户密码保护问题
 public static void GetUserQueston(string pstrUserName)
 {
  string strSQL = @"SELECT NewsUserQuestion FROM NewsUser
                  WHERE NewsUserName=@NewUserName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewUserName", SqlDbType.NVarChar,10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrUserName;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //检查密码保护是否符合
 public static void CheckUserProtection(string pstrUserName, string pstrUserQuestion, string pstrUserAnswer)
 {
  string strSQL = @"SELECT NewsUserID FROM NewsUser
                  WHERE NewsUserName=@NewUserName AND NewsUserQuestion=@UserQuestion AND NewsUserAnswer=@UserAnswer";
  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@NewUserName", SqlDbType.NVarChar, 10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrUserName;
  parms[1] = new SqlParameter("@UserQuestion", SqlDbType.NVarChar, 50);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrUserQuestion;
  parms[2] = new SqlParameter("@UserAnswer", SqlDbType.NVarChar, 50);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pstrUserAnswer;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //设置用户密码保护资料
 public static void SetUserProtection(string pstrUserName, string pstrUserQuestion, string pstrUserAnswer)
 {
  string strSQL = @"UPDATE NewsUser SET NewsUserQuestion=@NewsUserQuestion,NewsUserAnswer=@NewsUserAnswer
                  WHERE NewsUserName=@NewUserName";
  SqlParameter[] parms = new SqlParameter[3];
  parms[0] = new SqlParameter("@NewUserName", SqlDbType.NVarChar, 10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrUserName;
  parms[1] = new SqlParameter("@NewsUserQuestion", SqlDbType.NVarChar, 50);
  parms[1].Direction = ParameterDirection.Input;
  parms[1].Value = pstrUserQuestion;
  parms[2] = new SqlParameter("@NewsUserAnswer", SqlDbType.NVarChar, 50);
  parms[2].Direction = ParameterDirection.Input;
  parms[2].Value = pstrUserAnswer;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //获取用户列表
 public static void GetAllUser()
 {
  string strSQL = @"SELECT * FROM NewsUser";
  DBTools.CreateStoredProcedure(strSQL);
 }
 [SqlProcedure]
 //删除指定用户
 public static void DeleteUser(int pintUserID)
 {
  string strSQL = "DELETE FROM NewsUser WHERE NewsUserID=@NewsUseID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUseID", SqlDbType.Int);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintUserID;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //获取未激活用户列表
 public static void GetUnActivedUserList()
 {
  string strSQL = "SELECT * FROM NewsUser WHERE NewsUserActived=0 ORDER BY NewsUserCreateDate DESC";
  DBTools.CreateStoredProcedure(strSQL);
 }
 [SqlProcedure]
 //根据用户名获取用户信息
 public static void GetUserByName(string pstrUserName)
 {
  string strSQL = "SELECT * FROM NewsUser WHERE NewsUserName=@NewsUserName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar,10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pstrUserName;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

 [SqlProcedure]
 //根据用户权限获取用户信息
 public static void SelectUserByPrivilege(int pintPrivilege)
 {
  string strSQL = "SELECT * FROM NewsUser WHERE NewsUserPrivilege=@NewsUserPrivilege";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserPrivilege", SqlDbType.SmallInt);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintPrivilege;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //根据用户权限获取用户信息
 public static void SelectUserByCredit(int pintCredit)
 {
  string strSQL = "SELECT * FROM NewsUser WHERE NewsUserCredit=@NewsUserCredit";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserCredit", SqlDbType.SmallInt);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = pintCredit;
  DBTools.CreateStoredProcedure(strSQL, parms);
 }
 [SqlProcedure]
 //根据用户名获取用户信息
 public static void SelectUserByName(string pstrUserName)
 {
  string strSQL = "SELECT * FROM NewsUser WHERE NewsUserName LIKE @NewsUserName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@NewsUserName", SqlDbType.NVarChar, 10);
  parms[0].Direction = ParameterDirection.Input;
  parms[0].Value = "%"+pstrUserName+"%";
  DBTools.CreateStoredProcedure(strSQL, parms);
 }

};