planeprojectdao.cs

民航订票管理系统

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.SessionState;

namespace PlaneProject
{  
   
	public class EmpLogin
	{
		public String username;
		public String password;
		public String status;
	}
	

	public class UserDetails 
	{
		public String UserName;
		public int Level;
		public String Password;
		public String Sex;
		public String Phone;
		public String Email ;
         public String Mail ;
         public String Address ;

		public int userid;
	}
	public class Querytickets
	{
           public DateTime datetimer;
		   public string week;
		  public string area;
		 public  string rank;
		public string flyno;
		public string ptype;
		public string seatno;
		public DataSet wtickets;

		////public Querytickets(string flyno,string ptype,string seatno,ref DataSet wtickets )
		//{
			///this.flyno = flyno;
			//this.ptype = ptype;
			//this.seatno = seatno;
		//	//this.wtickets = wtickets;
		//}
	}

	

	/// <summary>
	/// Planeprojectdao 的摘要说明。
	/// </summary>
	public class Planeprojectdao
	{
		public static int userID;
		public Planeprojectdao()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		public SqlDataReader  Tickets(DateTime datetimer,string weekly,string area,string rank) 
		{

			//创建数据库连接和命令的对象

			string sConnString = " data source =(local); initial catalog = plane project;"+ "user id=sa;password=sa;";
			SqlConnection myConnection = new SqlConnection(sConnString);
			//SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand  myCommand = new SqlCommand ("querytickets", myConnection);

			//指明Sql命令的操作类型是使用存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//给存储过程添加参数
			SqlParameter parameterdatetimer= new SqlParameter("@datetimer", SqlDbType.DateTime, 8);
			parameterdatetimer.Value = datetimer;
			myCommand.Parameters.Add(parameterdatetimer);

			SqlParameter parameterweek= new SqlParameter("@week", SqlDbType.VarChar, 50);
			parameterweek.Value =weekly;
			myCommand.Parameters.Add(parameterweek);

			SqlParameter parameterarea= new SqlParameter("@area", SqlDbType.VarChar, 50);
			parameterarea.Value =area;
			myCommand.Parameters.Add(parameterarea);

			SqlParameter parameterrank= new SqlParameter("@rank", SqlDbType.VarChar, 50);
			parameterrank.Value =rank;
			myCommand.Parameters.Add(parameterrank);


			SqlParameter parameterflyno = new SqlParameter("@flyno", SqlDbType.VarChar, 50);
			//指出该参数是存储过程的OUTPUT参数
			parameterflyno.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterflyno);

			SqlParameter parameterptype= new SqlParameter("@ptype", SqlDbType.VarChar, 50);
			parameterptype.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterptype);

			SqlParameter parameterseatno= new SqlParameter("@seatno", SqlDbType.VarChar,50);
			parameterseatno.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterseatno);
			
			

			//SqlParameter parameterLevel = new SqlParameter("@level", SqlDbType.Int, 4);
			//parameterLevel.Direction = ParameterDirection.Output;
			//myCommand.Parameters.Add(parameterLevel);

			//SqlParameter parameterFlyhistory= new SqlParameter("flyhistory", SqlDbType.NChar, 10);
			//parameterFlyhistory.Direction = ParameterDirection.Output;
			//myCommand.Parameters.Add(parameterFlyhistory);
			
			//Querytickets myUsertickets = new Querytickets();

			//打开数据库连接
			//myConnection.Open();
			//进行数据库操作
			//myCommand.SelectCommand.ExecuteNonQuery();
			//创建数据集
			//DataSet myDataSet = new DataSet();
			//往数据集里面填充数据
			//myCommand.Fill(myUsertickets.wtickets);
			//myCommand.Fill(ds);
			//关闭数据库连接
			//myConnection.Close();

			//ds = test();
			
			myConnection.Open();
			//进行数据库操作
			//myCommand.ExecuteNonQuery();
			//创建数据集
			//DataSet myDataSet = new DataSet();
			//往数据集里面填充数据
			//myUsertickets.wtickets = new DataSet();
			//myCommand.Fill(myUsertickets.wtickets);
			//myCommand.Fill(ds);
			//关闭数据库连接
			//myConnection.Close();

			SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

			//返回DataReader的结果
			return result;

			

			//产生UserDetails类的对象
			//Querytickets myUsertickets = new Querytickets(parameterflyno.Value.ToString(), parameterptype.Value.ToString(),parameterseatno.Value.ToString());



			//根据存储过程的输出参数的值对myUserDetails对象进行赋值

			
		//myUsertickets.ptype = parameterptype.Value.ToString();
		//myUsertickets.flyno = parameterflyno.Value.ToString();
		//myUserDetails.Level = (int)parameterLevel.Value;
			//myUsertickets.seatno = parameterseatno.Value.ToString();
//		//myUserDetails.Prioridetail = (string)parameterPrioridetail.Value;
			//myUserDetails.Flyhistory = (string)parameterFlyhistory.Value;
//			myUsertickets.wtickets = myDataSet.Clone();
			//myUsertickets.wtickets = myDataSet.Tables[0].DataSet.Clone();




			//return myUsertickets;
		}

		public UserDetails GetUserDetails(String userID) 
		{

			//创建数据库连接和命令的对象

			string sConnString = " data source =(local); initial catalog = plane project;"+ "user id=sa;password=sa;";
			SqlConnection myConnection = new SqlConnection(sConnString);
			//SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("UserInfo", myConnection);

			//指明Sql命令的操作类型是使用存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//给存储过程添加参数
			SqlParameter parameterclientno= new SqlParameter("@UserID", SqlDbType.Int, 4);
			parameterclientno.Value = Int32.Parse(userID);
			myCommand.Parameters.Add(parameterclientno);

			SqlParameter parameterClientName = new SqlParameter("@UserName", SqlDbType.NVarChar, 50);
			//指出该参数是存储过程的OUTPUT参数
			parameterClientName.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterClientName);

			SqlParameter parameterClientCode = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
			parameterClientCode.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterClientCode);

			SqlParameter parameterSex = new SqlParameter("@sex", SqlDbType.NVarChar, 50);
			parameterSex.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterSex);
			
			SqlParameter parameterPhone = new SqlParameter("@phone", SqlDbType.NVarChar, 50);
			parameterPhone.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterPhone);

			SqlParameter parameterEmail = new SqlParameter("@email", SqlDbType.NVarChar, 50);
			parameterEmail.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterEmail);

			SqlParameter parameterAddress = new SqlParameter("@address", SqlDbType.NVarChar, 50);
			parameterAddress.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterAddress);

			SqlParameter parameterMail = new SqlParameter("@mail", SqlDbType.NVarChar, 50);
			parameterMail.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterMail);

			//SqlParameter parameterLevel = new SqlParameter("@level", SqlDbType.Int, 4);
			//parameterLevel.Direction = ParameterDirection.Output;
			//myCommand.Parameters.Add(parameterLevel);

			//SqlParameter parameterFlyhistory= new SqlParameter("flyhistory", SqlDbType.NChar, 10);
			//parameterFlyhistory.Direction = ParameterDirection.Output;
			//myCommand.Parameters.Add(parameterFlyhistory);
			
			//打开数据库连接
			myConnection.Open();
			//进行数据库操作
			myCommand.ExecuteNonQuery();
			//关闭数据库连接
			myConnection.Close();

			//产生UserDetails类的对象
			UserDetails myUserDetails = new UserDetails();

			//根据存储过程的输出参数的值对myUserDetails对象进行赋值
			myUserDetails.UserName = (string)parameterClientName.Value.ToString();
			myUserDetails.Password = (string)parameterClientCode.Value.ToString();
			//myUserDetails.Level = (int)parameterLevel.Value;
			myUserDetails.Sex = (string)parameterSex.Value.ToString();
			myUserDetails.Phone = (string)parameterPhone.Value.ToString();
			myUserDetails.Email= (string)parameterEmail.Value.ToString();
			myUserDetails.Address= (string)parameterAddress.Value.ToString();
myUserDetails.Mail = (string)parameterMail.Value.ToString();
			//myUserDetails.Flyhistory = (string)parameterFlyhistory.Value;

			return myUserDetails;
		}

		
			
			
		public String AddUser(string UserName, string password, string Sex, string Phone,string Email, string Address,string Mail) 
		{

			//创建数据库连接和命令的对象


			string sConnString = " data source =(local); initial catalog = plane project;"+ "user id=sa;password=sa;";
			SqlConnection myConnection = new SqlConnection(sConnString);
			//SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("AddClient", myConnection);

			//指明Sql命令的操作类型是使用存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//给存储过程添加参数
			SqlParameter parameterclientname = new SqlParameter("@clientname", SqlDbType.NVarChar, 50);
			parameterclientname.Value = UserName;
			myCommand.Parameters.Add(parameterclientname);

			SqlParameter parameterclientcode = new SqlParameter("@clientcode", SqlDbType.NVarChar, 50);
			parameterclientcode.Value = password;
			myCommand.Parameters.Add(parameterclientcode);

			SqlParameter parametersex = new SqlParameter("@sex", SqlDbType.NVarChar, 50);
			parametersex.Value = Sex;
			myCommand.Parameters.Add(parametersex);