usersp.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
{
 //判断用户名是否存在
 [SqlProcedure]
 public static void IsUserNameExist(string pUserName)
 {
  string SQL = @"SELECT UserID 
                 FROM UserInfo 
                 WHERE [UserName]=@UserName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
  parms[0].Value = pUserName;
  DBTools.CreateStoredProcedure(SQL,parms);  
 }
 //注册
 [SqlProcedure]
 public static void Register(string pUserName, string pPassword, int pSex, string pEmail,
  string pTrueName, string pAddress, string pQuestion, string pAnswer, int pShowEmail,int pRoleID)
 {
  string SQL = @"INSERT INTO [UserInfo]
           ([UserName]
           ,[Password]
           ,[Sex]
           ,[TrueName]
           ,[Address]
           ,[Email]
           ,[ProtectQuestion]
           ,[ProtectionAnswer]
           ,[ShowEmail]
           ,[RoleID])
     VALUES
           (@UserName
           ,@Password
           ,@Sex
           ,@TrueName
           ,@Address
           ,@Email
           ,@ProtectQuestion
           ,@ProtectionAnswer
           ,@ShowEmail
           ,@RoleID);
           SELECT @@IDENTITY;";
  SqlParameter[] parms = new SqlParameter[10];
  parms[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
  parms[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
  parms[2] = new SqlParameter("@Sex", SqlDbType.TinyInt);
  parms[3] = new SqlParameter("@TrueName", SqlDbType.NVarChar, 50);
  parms[4] = new SqlParameter("@Address", SqlDbType.NVarChar, 500);
  parms[5] = new SqlParameter("@Email", SqlDbType.NVarChar, 500);
  parms[6] = new SqlParameter("@ProtectQuestion", SqlDbType.NVarChar, 150);
  parms[7] = new SqlParameter("@ProtectionAnswer", SqlDbType.NVarChar, 150);
  parms[8] = new SqlParameter("@ShowEmail", SqlDbType.TinyInt);
  parms[9] = new SqlParameter("@RoleID", SqlDbType.Int);


  parms[0].Value = pUserName;

  parms[1].Value = pPassword;

  parms[2].Value = pSex;


  parms[3].Value = pTrueName;
  parms[3].IsNullable = true;


  parms[4].Value = pAddress;
  parms[4].IsNullable = true;

  parms[5].Value = pEmail;


  parms[6].Value = pQuestion;
  parms[6].IsNullable = true;

  parms[7].Value = pAnswer;
  parms[7].IsNullable = true;

  parms[8].Value = pShowEmail;

  parms[9].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //登录
 [SqlProcedure]
 public static void Login(string pUserName, string pPassword)
 {
  string SQL = @"SELECT [UserID]
                 FROM [UserInfo]
                 WHERE [UserName]=@UserName
                 AND [Password]=@Password";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
  parms[1] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);

  parms[0].Value = pUserName;
  parms[1].Value = pPassword;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //获取登录信息，并设置最后登录时间
 [SqlProcedure]
 public static void AfterLogin(string pUserName)
 {
  string SQL = @"SELECT *
                 FROM [UserInfo]
                 WHERE [UserName]=@UserName;
                 UPDATE [UserInfo]
                 SET [LastLogin]=(getdate())
                 WHERE [UserName]=@UserName;";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);

  parms[0].Value = pUserName;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID获取用户信息
 [SqlProcedure]
 public static void GetUserInfo(int pUserID)
 {
  string SQL = @"SELECT [UserID]
                       ,[UserName]
                       ,[Password]
                       ,[CreateDate]
                       ,[LastLogin]
                       ,[Sex]
                       ,[TrueName]
                       ,[Address]
                       ,[RoleID]
                       ,[Posted]
                       ,[Email]
                       ,[ProtectQuestion]
                       ,[ProtectionAnswer]
                       ,[ShowEmail]
                       ,[Actived]
                       ,[Reposted]
                FROM [UserInfo]
                WHERE [UserID]=@UserID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[0].Value = pUserID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //设置用户为激活状态，并删除激活纪录
 [SqlProcedure]
 public static void SetUserActived(int pUserID,int pActivationID)
 {
  string SQL = @"UPDATE [UserInfo]
               SET [Actived]=1
               WHERE [UserID]=@UserID;
               DELETE FROM [Activation]
               WHERE [ActivationID]=@ActivationID";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[1] = new SqlParameter("@ActivationID", SqlDbType.Int);
  parms[0].Value = pUserID;
  parms[1].Value = pActivationID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //判断指定ID用户是否已经激活
 [SqlProcedure]
 public static void IsUserActived(int pUserID)
 {
  string SQL = @"SELECT [UserID]
               FROM [UserInfo]
               WHERE [UserID]=@UserID
               AND [Actived]=1";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[0].Value = pUserID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //修改用户信息
 [SqlProcedure]
 public static void ModifyInfo(int pUserID,string pTrueName,string pAddress,
  string pEmail,string pQuestion,string pAnswer,int pShowEmail)
 {
  string SQL = @"UPDATE [UserInfo]
                 SET [TrueName] = @TrueName
                    ,[Address] = @Address
                    ,[Email] = @Email
                    ,[ProtectQuestion] = @ProtectQuestion
                    ,[ProtectionAnswer] = @ProtectionAnswer
                    ,[ShowEmail] = @ShowEmail
                 WHERE [UserID]=@UserID";
  SqlParameter[] parms = new SqlParameter[7];
  parms[0] = new SqlParameter("@TrueName", SqlDbType.NVarChar, 50);
  parms[1] = new SqlParameter("@Address", SqlDbType.NVarChar, 500);
  parms[2] = new SqlParameter("@Email", SqlDbType.NVarChar, 500);
  parms[3] = new SqlParameter("@ProtectQuestion", SqlDbType.NVarChar, 150);
  parms[4] = new SqlParameter("@ProtectionAnswer", SqlDbType.NVarChar, 150);
  parms[5] = new SqlParameter("@ShowEmail", SqlDbType.TinyInt);
  parms[6] = new SqlParameter("@UserID", SqlDbType.Int);

  parms[0].Value = pTrueName;
  parms[0].IsNullable = true;

  parms[1].Value = pAddress;
  parms[0].IsNullable = true;

  parms[2].Value = pEmail;

  parms[3].Value = pQuestion;
  parms[3].IsNullable = true;

  parms[4].Value = pAnswer;
  parms[4].IsNullable = true;

  parms[5].Value = pShowEmail;
  parms[6].Value = pUserID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据用户ID修改密码
 [SqlProcedure]
 public static void ModifyPassword(string pUserName, string pPassword)
 {
  string SQL = @"UPDATE [UserInfo]
                 SET [Password]=@Password
                 WHERE [UserName]=@UserName";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
  parms[1] = new SqlParameter("@UserName", SqlDbType.NVarChar,50);

  parms[0].Value = pPassword;
  parms[1].Value = pUserName;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID删除用户
 [SqlProcedure]
 public static void DeleteUserByID(int pUserID)
 {
  string SQL = @"DELETE FROM [UserInfo]
                 WHERE UserID=@UserID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[0].Value = pUserID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID修改用户角色
 [SqlProcedure]
 public static void ModifyRoleByID(int pUserID,int pRoleID)
 {
  string SQL = @"UPDATE [UserInfo]
                 SET RoleID=@RoleID
                 WHERE UserID=@UserID";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[1] = new SqlParameter("@RoleID", SqlDbType.Int);

  parms[0].Value = pUserID;
  parms[1].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //获取用户列表
 [SqlProcedure]
 public static void GetUserList()
 {
  string SQL = @"SELECT [UserID]
                      ,[UserName]
                      ,[CreateDate]
                      ,[LastLogin]
                      ,[TrueName]
                      ,[Posted]
                      ,[Actived]
                 FROM [UserInfo]
                 WHERE [RoleID]=(SELECT [RoleID] 
                                 FROM [Role]
                                 WHERE [RoleName]<>'管理员')
                 OR IsNull([RoleID],-1)=-1;";

  DBTools.CreateStoredProcedure(SQL);
 }
 //获取用户密码保护问题
 [SqlProcedure]
 public static void GetUserQuestion(int pUserID)
 {
  string SQL = @"SELECT [ProtectQuestion]
                 FROM [UserInfo]
                 WHERE [UserID]=@UserID";

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

  DBTools.CreateStoredProcedure(SQL,parms);
 }

 //检查用户答案和用户ID是否一致
 [SqlProcedure]
 public static void ChecUserAnswer(int pUserID,string pAnswer)
 {
  string SQL = @"SELECT [UserID]
                 FROM [UserInfo]
                 WHERE [UserID]=@UserID
                 AND [ProtectionAnswer]=@Answer";

  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@UserID", SqlDbType.Int);
  parms[1] = new SqlParameter("@Answer", SqlDbType.NVarChar, 150);

  parms[0].Value = pUserID;
  parms[1].Value = pAnswer;

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