userdb.cs

来自「图书馆的书目查询」· CS 代码 · 共 309 行

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

namespace Library_BS_DongWeiwei
{
	/// <summary>
	/// UserDB 的摘要说明。
	/// </summary>
	public class UserDB
	{
		public UserDB()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		/// <summary>

		///验证用户是否为“未注册图书馆合法用户”

		/// </summary>

		public int UserRegister(string UserBarCode) 

		{

			// Create Instance of Connection and Command Object

			//SqlConnection myConnection = new SqlConnection("server=(local);Trusted_Connection=true;database=Library_Dww");

			SqlConnection myConnection = System.Configuration.ConfigurationSettings.AppSettings["connString"];
			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 System.Data.DataSet QueryReaderInfoByID(string iD) 
		{
		    string connStr = System.Configuration.ConfigurationSettings.AppSettings["connString"];
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connStr);
        
			string queryString = "SELECT * FROM [读者信息] JOIN [读者类型] ON ([读者信息].[类型号] = [读者类型].[类型号]) JOIN [工作单位] ON ([读者信息].[单位编号] = [工作单位].[单位编号])WHERE ([读者信息].[编号] = @ID)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_iD = new System.Data.SqlClient.SqlParameter();
			dbParam_iD.ParameterName = "@ID";
			dbParam_iD.Value = iD;
			dbParam_iD.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_iD);
        
			System.Data.IDbDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter();
			dataAdapter.SelectCommand = dbCommand;
			System.Data.DataSet dataSet = new System.Data.DataSet();
			dataAdapter.Fill(dataSet);
        
			return dataSet;
		}

    
		public int UpdateRegister(string 编号, string 密码, string 密码问题, string 密码答案, string 注册名, string 性别, System.DateTime 出生日期, string 证件号码, string 电话, string 通讯地址, string 邮编, string e_mail) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library_Dww\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [读者信息] SET [电话]=@电话, [出生日期]=@出生日期, [证件号码]=@证件号码, [注册名]=@注册名, [邮编]=@邮编, [E_" +
				"mail]=@E_mail, [密码]=@密码, [密码答案]=@密码答案, [通讯地址]=@通讯地址, [密码问题]=@密码问题, [性别]=@性别 WHER" +
				"E ([读者信息].[编号] = @编号)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_编号 = new System.Data.SqlClient.SqlParameter();
			dbParam_编号.ParameterName = "@编号";
			dbParam_编号.Value = 编号;
			dbParam_编号.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_编号);
			System.Data.IDataParameter dbParam_密码 = new System.Data.SqlClient.SqlParameter();
			dbParam_密码.ParameterName = "@密码";
			dbParam_密码.Value = 密码;
			dbParam_密码.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_密码);
			System.Data.IDataParameter dbParam_密码问题 = new System.Data.SqlClient.SqlParameter();
			dbParam_密码问题.ParameterName = "@密码问题";
			dbParam_密码问题.Value = 密码问题;
			dbParam_密码问题.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_密码问题);
			System.Data.IDataParameter dbParam_密码答案 = new System.Data.SqlClient.SqlParameter();
			dbParam_密码答案.ParameterName = "@密码答案";
			dbParam_密码答案.Value = 密码答案;
			dbParam_密码答案.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_密码答案);
			System.Data.IDataParameter dbParam_注册名 = new System.Data.SqlClient.SqlParameter();
			dbParam_注册名.ParameterName = "@注册名";
			dbParam_注册名.Value = 注册名;
			dbParam_注册名.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_注册名);
			System.Data.IDataParameter dbParam_性别 = new System.Data.SqlClient.SqlParameter();
			dbParam_性别.ParameterName = "@性别";
			dbParam_性别.Value = 性别;
			dbParam_性别.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_性别);
			System.Data.IDataParameter dbParam_出生日期 = new System.Data.SqlClient.SqlParameter();
			dbParam_出生日期.ParameterName = "@出生日期";
			dbParam_出生日期.Value = 出生日期;
			dbParam_出生日期.DbType = System.Data.DbType.DateTime;
			dbCommand.Parameters.Add(dbParam_出生日期);
			System.Data.IDataParameter dbParam_证件号码 = new System.Data.SqlClient.SqlParameter();
			dbParam_证件号码.ParameterName = "@证件号码";
			dbParam_证件号码.Value = 证件号码;
			dbParam_证件号码.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_证件号码);
			System.Data.IDataParameter dbParam_电话 = new System.Data.SqlClient.SqlParameter();
			dbParam_电话.ParameterName = "@电话";
			dbParam_电话.Value = 电话;
			dbParam_电话.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_电话);
			System.Data.IDataParameter dbParam_通讯地址 = new System.Data.SqlClient.SqlParameter();
			dbParam_通讯地址.ParameterName = "@通讯地址";
			dbParam_通讯地址.Value = 通讯地址;
			dbParam_通讯地址.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_通讯地址);
			System.Data.IDataParameter dbParam_邮编 = new System.Data.SqlClient.SqlParameter();
			dbParam_邮编.ParameterName = "@邮编";
			dbParam_邮编.Value = 邮编;
			dbParam_邮编.DbType = System.Data.DbType.StringFixedLength;
			dbCommand.Parameters.Add(dbParam_邮编);
			System.Data.IDataParameter dbParam_e_mail = new System.Data.SqlClient.SqlParameter();
			dbParam_e_mail.ParameterName = "@E_mail";
			dbParam_e_mail.Value = e_mail;
			dbParam_e_mail.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_e_mail);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;
		}// Insert page code here
	
		    
		public int UpdateUserInfor(string 编号, string 密码问题, string 密码答案, string 电话, string 通讯地址, string 邮编, string e_mail) 
		{
			string connectionString = "server=\'(local)\'; trusted_connection=true; database=\'Library_Dww\'";
			System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
        
			string queryString = "UPDATE [读者信息] SET [密码问题]=@密码问题, [密码答案]=@密码答案, [E_mail]=@E_mail, [邮编]=@邮编, [电话]=@电" +
				"话, [通讯地址]=@通讯地址 WHERE ([读者信息].[编号] = @编号)";
			System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
			dbCommand.CommandText = queryString;
			dbCommand.Connection = dbConnection;
        
			System.Data.IDataParameter dbParam_编号 = new System.Data.SqlClient.SqlParameter();
			dbParam_编号.ParameterName = "@编号";
			dbParam_编号.Value = 编号;
			dbParam_编号.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_编号);
			System.Data.IDataParameter dbParam_密码问题 = new System.Data.SqlClient.SqlParameter();
			dbParam_密码问题.ParameterName = "@密码问题";
			dbParam_密码问题.Value = 密码问题;
			dbParam_密码问题.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_密码问题);
			System.Data.IDataParameter dbParam_密码答案 = new System.Data.SqlClient.SqlParameter();
			dbParam_密码答案.ParameterName = "@密码答案";
			dbParam_密码答案.Value = 密码答案;
			dbParam_密码答案.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_密码答案);
			System.Data.IDataParameter dbParam_电话 = new System.Data.SqlClient.SqlParameter();
			dbParam_电话.ParameterName = "@电话";
			dbParam_电话.Value = 电话;
			dbParam_电话.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_电话);
			System.Data.IDataParameter dbParam_通讯地址 = new System.Data.SqlClient.SqlParameter();
			dbParam_通讯地址.ParameterName = "@通讯地址";
			dbParam_通讯地址.Value = 通讯地址;
			dbParam_通讯地址.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_通讯地址);
			System.Data.IDataParameter dbParam_邮编 = new System.Data.SqlClient.SqlParameter();
			dbParam_邮编.ParameterName = "@邮编";
			dbParam_邮编.Value = 邮编;
			dbParam_邮编.DbType = System.Data.DbType.StringFixedLength;
			dbCommand.Parameters.Add(dbParam_邮编);
			System.Data.IDataParameter dbParam_e_mail = new System.Data.SqlClient.SqlParameter();
			dbParam_e_mail.ParameterName = "@E_mail";
			dbParam_e_mail.Value = e_mail;
			dbParam_e_mail.DbType = System.Data.DbType.String;
			dbCommand.Parameters.Add(dbParam_e_mail);
        
			int rowsAffected = 0;
			dbConnection.Open();
			try 
			{
				rowsAffected = dbCommand.ExecuteNonQuery();
			}
			finally 
			{
				dbConnection.Close();
			}
        
			return rowsAffected;
		}// Insert page code here
		//

		public string GetPwd(string readerId)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("GetPwd", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReaderID = new SqlParameter("@readerID", SqlDbType.VarChar, 20);
			parameterReaderID.Value = readerId;
			myCommand.Parameters.Add(parameterReaderID);

			SqlParameter parameterPwd = new SqlParameter("@pwd", SqlDbType.VarChar, 20);
			parameterPwd.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPwd);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			string pwd=(parameterPwd.Value).ToString();
			
			return pwd;
		}
		public void UpdatePwd(string readerId, string pwd)
		{
			
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("UpdatePwd", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter parameterReaderID = new SqlParameter("@readerID", SqlDbType.VarChar, 20);
			parameterReaderID.Value = readerId;
			myCommand.Parameters.Add(parameterReaderID);

			SqlParameter parameterPwd = new SqlParameter("@pwd", SqlDbType.VarChar, 20);
			parameterPwd.Value = pwd;
			myCommand.Parameters.Add(parameterPwd);

			// Open the connection and execute the Command
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

		}

	}
}