users.cs

图书馆管理信息系统设计 功能：图书的查询

using System;
using System.Data;
using System.Data.SqlClient;

namespace Library_BS
{
	/// <summary>
	/// Users 的摘要说明。
	/// </summary>
	public class Users
	{
		//用户注册
		public int UserRegister(string UserBarCode) 

		{

			// Create Instance of Connection and Command Object

			SqlConnection myConnection = new SqlConnection("server=.;Trusted_Connection=true;database=Library");

			SqlCommand myCommand = new SqlCommand("UserRegister", myConnection);

 

			// Mark the Command as a SPROC

			myCommand.CommandType = CommandType.StoredProcedure;

 

			// Add Parameters to SPROC

			SqlParameter parameterUserBarCode = new SqlParameter("@UserBarCode", SqlDbType.NVarChar, 20);

			parameterUserBarCode.Value = UserBarCode;

			myCommand.Parameters.Add(parameterUserBarCode);

 

			SqlParameter parameterErrorCode = new SqlParameter("@ErrorCode", SqlDbType.Int, 4);

			parameterErrorCode.Direction = ParameterDirection.Output;

			myCommand.Parameters.Add(parameterErrorCode);

 

			// Open the connection and execute the Command

			myConnection.Open();

			myCommand.ExecuteNonQuery();

			myConnection.Close();

 

			int ErrorCode = (int)(parameterErrorCode.Value);         

			return ErrorCode;           

		}
          //根据注册名和密码登录
		public int UsersLogin(string registerName, string userPassword) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT count(*) FROM [Users] WHERE (([Users].[RegisterName] = @RegisterName) AND" +
				" ([Users].[UserPassword] = @UserPassword))";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_registerName = new System.Data.SqlClient.SqlParameter();
			dbParam_registerName.ParameterName = "@RegisterName";
			dbParam_registerName.Value = registerName;
			dbParam_registerName.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_registerName);
			System.Data.IDataParameter dbParam_userPassword = new System.Data.SqlClient.SqlParameter();
			dbParam_userPassword.ParameterName = "@UserPassword";
			dbParam_userPassword.Value = userPassword;
			dbParam_userPassword.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userPassword);
        
			dbConnection.Open();
			 int count= (int)dbCommand.ExecuteScalar();
        
			return count;
		}
		

      //根据注册名获得读者证号
		public string GetBarCodeByRegisterName(string registerName) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [Users].[UserBarCode] FROM [Users] WHERE ([Users].[RegisterName] = @Regist" +
				"erName)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_registerName = new System.Data.SqlClient.SqlParameter();
			dbParam_registerName.ParameterName = "@RegisterName";
			dbParam_registerName.Value = registerName;
			dbParam_registerName.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_registerName);
        
			dbConnection.Open();
			 return (string) dbCommand.ExecuteScalar();
        
			
		}
       //根据证号获得读者真实姓名
		public string  getRealNameByUserBarCode(string userBarCode) 
		{
			string connectionString = "server=\'(local)\'; user id=\'sa\'; password=\'\'; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [Users].[UserRealName] FROM [Users] WHERE ([Users].[UserBarCode] = @UserBa" +
				"rCode)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
        
			dbConnection.Open();
			
        
			return (string) dbCommand.ExecuteScalar();
		}
          
       //根据证号获得读者类型
		public string  getUsersType(string userBarCode) 
		{
			string connectionString = "server=\'(local)\'; user id=\'sa\'; password=\'\'; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [UserTypes].[UserTypeName] FROM [UserTypes], [Users] WHERE (([UserTypes].[" +
				"UserTypeID] = [Users].[UserTypeID]) AND ([Users].[UserBarCode] = @UserBarCode))";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
        
			dbConnection.Open();
			//System.Data.IDataReader dataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        
			return (string) dbCommand.ExecuteScalar();
		}

           //更新注册     
	public	int MyUpdateRegesiter(string userBarCode, string registerName, string userPassword, string iDNo, string email, string sex, string question, string answer) 
		{
			string connectionString = "server=\'(local)\'; user id=\'sa\'; password=\'\'; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [Users] SET [RegisterName]=@RegisterName, [UserPassword]=@UserPassword, [I" +
				"DNo]=@IDNo, [Email]=@Email, [Sex]=@Sex, [Question]=@Question, [Answer]=@Answer W" +
				"HERE ([Users].[UserBarCode] = @UserBarCode)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
			System.Data.IDataParameter dbParam_registerName = new System.Data.SqlClient.SqlParameter();
			dbParam_registerName.ParameterName = "@RegisterName";
			dbParam_registerName.Value = registerName;
			dbParam_registerName.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_registerName);
			System.Data.IDataParameter dbParam_userPassword = new System.Data.SqlClient.SqlParameter();
			dbParam_userPassword.ParameterName = "@UserPassword";
			dbParam_userPassword.Value = userPassword;
			dbParam_userPassword.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userPassword);
			System.Data.IDataParameter dbParam_iDNo = new System.Data.SqlClient.SqlParameter();
			dbParam_iDNo.ParameterName = "@IDNo";
			dbParam_iDNo.Value = iDNo;
			dbParam_iDNo.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_iDNo);
			System.Data.IDataParameter dbParam_email = new System.Data.SqlClient.SqlParameter();
			dbParam_email.ParameterName = "@Email";
			dbParam_email.Value = email;
			dbParam_email.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_email);
			System.Data.IDataParameter dbParam_sex = new System.Data.SqlClient.SqlParameter();
			dbParam_sex.ParameterName = "@Sex";
			dbParam_sex.Value = sex;
			dbParam_sex.DbType = System.Data.DbType.StringFixedLength;
			dbCommand.Parameters.Add(dbParam_sex);
			System.Data.IDataParameter dbParam_question = new System.Data.SqlClient.SqlParameter();
			dbParam_question.ParameterName = "@Question";
			dbParam_question.Value = question;
			dbParam_question.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_question);
			System.Data.IDataParameter dbParam_answer = new System.Data.SqlClient.SqlParameter();
			dbParam_answer.ParameterName = "@Answer";
			dbParam_answer.Value = answer;
			dbParam_answer.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_answer);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;
		}
		//获得读者单位		    
		public string  getUserDepartByUserBarCode(string userBarCode) 
		{
			string connectionString = "server=\'(local)\'; user id=\'sa\'; password=\'\'; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [Department].[DepartName] FROM [Department], [Users] WHERE (([Department]." +
				"[DepartID] = [Users].[DepartID]) AND ([Users].[UserBarCode] = @UserBarCode))";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_userBarCode = new System.Data.SqlClient.SqlParameter();
			dbParam_userBarCode.ParameterName = "@UserBarCode";
			dbParam_userBarCode.Value = userBarCode;
			dbParam_userBarCode.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_userBarCode);
        
			dbConnection.Open();
			
        
			return (string) dbCommand.ExecuteScalar();
		}
		//检测注册名是否可用
		public string  TestName(string registerName) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "SELECT [Users].* FROM [Users] WHERE ([Users].[RegisterName] = @RegisterName)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_registerName = new System.Data.SqlClient.SqlParameter();
			dbParam_registerName.ParameterName = "@RegisterName";
			dbParam_registerName.Value = registerName;
			dbParam_registerName.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_registerName);
        
			dbConnection.Open();
			
			string count=(string)dbCommand.ExecuteScalar();
        
			return count;

		}
		

	}
}