db.cs

根据起降城市,查询到起降时间,以及航次的具体信息.

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

namespace DataAccess
{
	/// <summary>
	/// DB 的摘要说明。
	/// </summary>
	public sealed class DB
	{
		private const string CONNECTION_STRING = "SERVER=.;DATABASE=Flights;UID=sa;PWD=ok";

		public static FlightCollection GetFlights(City fromCity, City toCity, DateTime startTime, DateTime endTime)
		{
			SqlConnection con = new SqlConnection(CONNECTION_STRING);
			SqlCommand cmd = new SqlCommand("GetFlights", con);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@FromCity", SqlDbType.Int);
			cmd.Parameters.Add("@ToCity", SqlDbType.Int);
			cmd.Parameters.Add("@StartTime", SqlDbType.DateTime);
			cmd.Parameters.Add("@EndTime", SqlDbType.DateTime);

			cmd.Parameters["@FromCity"].Value = fromCity.Id;
			cmd.Parameters["@ToCity"].Value = toCity.Id;
			cmd.Parameters["@StartTime"].Value = startTime;
			cmd.Parameters["@EndTime"].Value = endTime;

			FlightCollection flights = new FlightCollection();
			con.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			if(dr.HasRows)
			{
				while(dr.Read())
				{
					Flight f = new Flight(
						dr.GetInt32(0),
						dr.GetString(1),
						fromCity,
						toCity,
						dr.GetDateTime(4),
						dr.GetDateTime(5));
					flights.Add(f);
				}
			}
			dr.Close();
			con.Close();

			return flights;
		}

		public static CabinCollection GetCabins(Flight flight)
		{
			SqlConnection con = new SqlConnection(CONNECTION_STRING);
			SqlCommand cmd = new SqlCommand("GetCabins", con);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@FlightId", SqlDbType.Int);

			cmd.Parameters["@FlightId"].Value = flight.Id;

			CabinCollection cabins = new CabinCollection();
			con.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			if(dr.HasRows)
			{
				while(dr.Read())
				{
					Cabin c = new Cabin(
						dr.GetInt32(0),
						dr.GetString(1),
						dr.GetString(2),
						dr.GetInt32(4),
						dr.GetDecimal(5),
						dr.GetDecimal(6));
					cabins.Add(c);
				}
			}

			dr.Close();
			con.Close();

			return cabins;
		}

		public static CityCollection GetAllCities()
		{
			SqlConnection con = new SqlConnection(CONNECTION_STRING);
			SqlCommand cmd = new SqlCommand("GetAllCity", con);
			cmd.CommandType = CommandType.StoredProcedure;

			CityCollection cities = new CityCollection();
			con.Open();
			SqlDataReader dr = cmd.ExecuteReader();
			if(dr.HasRows)
			{
				while(dr.Read())
				{
					City c = new City(dr.GetInt32(0), dr.GetString(1));
					cities.Add(c);
				}
			}
			dr.Close();
			con.Close();

			return cities;
		}
	}
}