12.2.2 实现CarRentalReservation组件

 using System;
namespace CarRentalReservation
{
[Serializable]
public class UserDetail
{
protected string userID;
protected string passWord;
protected string name;
protected string address;
protected string phone;
protected string city;
protected string state;
protected string zip;

    public string UserID
{
get { return userID; }
set { userID = value; }
}
public string PassWord
{
get { return passWord; }
set { passWord = value; }
}
public string Name
{
get { return name; }
set { name = value; }
}
public string Address
{
get { return address; }
set { address = value; }
}
public string Phone
{
get { return phone; }
set { phone = value; }
}
public string City
{
get { return city; }
set { city = value; }
}
public string State
{
get { return state; }
set { state = value; }
}
public string Zip
{
get { return zip; }
set { zip = value; }
}
}
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Xml;
namespace CarRentalReservation
{
public class UserDB
{
public bool IsValidUserLogIn(string userName, string passWord)
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString =
System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString; connection.Open();
SqlCommand command = new SqlCommand("IsValidUserLogIn", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter paramUserName = new SqlParameter("@UserID",
SqlDbType.VarChar, 20);
paramUserName.Value = userName;
command.Parameters.Add(paramUserName);
SqlParameter paramPassWord = new SqlParameter("@Password",
SqlDbType.VarChar, 10);
paramPassWord.Value = passWord;
command.Parameters.Add(paramPassWord);
SqlParameter paramRetValue = new SqlParameter
("@RetValue", SqlDbType.Int, 4);
paramRetValue.Direction = ParameterDirection.Output; 
command.Parameters.Add(paramRetValue); 
command.ExecuteNonQuery();
// 获取存储过程的返回值，接着将其赋值给一个变量
int retValue = (int)paramRetValue.Value;
if (retValue == -1)
{
throw new InvalidCredentialsException("Invalid Login");
}
else
{
return true;
}
}
}
catch (Exception ex)
{
throw ex;
}
}

    public bool AddUserInfo(UserDetail user)
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString =
System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString; connection.Open();
SqlCommand command = new SqlCommand("AddUser", connection);
command.CommandType = CommandType.StoredProcedure; 
SqlParameter paramUserName = new SqlParameter("@UserID",
SqlDbType.VarChar, 20);
paramUserName.Value = user.UserID;
command.Parameters.Add(paramUserName);
SqlParameter paramPassWord = new SqlParameter("@Password",
SqlDbType.VarChar, 10);
paramPassWord.Value = user.PassWord;
command.Parameters.Add(paramPassWord);
SqlParameter paramName = new SqlParameter("@Name",SqlDbType.VarChar, 128);
paramName.Value = user.Name;
command.Parameters.Add(paramName);
SqlParameter paramAddress = new SqlParameter("@Address",
SqlDbType.VarChar, 128);
paramAddress.Value = user.Address;
command.Parameters.Add(paramAddress);
SqlParameter paramPhone = new SqlParameter("@Phone", SqlDbType.Xml);
paramPhone.Value = new SqlXml(new XmlTextReader(user.Phone,
XmlNodeType.Document, null));
command.Parameters.Add(paramPhone);
SqlParameter paramCity = new SqlParameter("@City", SqlDbType.VarChar, 50);
paramCity.Value = user.City;
command.Parameters.Add(paramCity);
SqlParameter paramState = new SqlParameter("@State", SqlDbType.Char, 2);
paramState.Value = user.State;
command.Parameters.Add(paramState);
SqlParameter paramZip = new SqlParameter("@Zip", SqlDbType.Char, 5);
paramZip.Value = user.Zip; 
command.Parameters.Add(paramZip); 
command.ExecuteNonQuery();
return true;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
} 

public class InvalidCredentialsException : Exception
{
public InvalidCredentialsException(string exceptionMessage):
base(exceptionMessage)
{ }
}

using System;
using System.Data;
using System.Data.SqlClient;
namespace CarRentalReservation
{
public class RentalDB
{
public DataSet SearchRentalCar(string rentalPickupLocation, DateTime pickupDate,
int carClassID)
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString = System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString;
DataSet car = new DataSet("Cars");
SqlDataAdapter adapter = new SqlDataAdapter("SearchRentalCar", connection);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter paramRentalPickupLocation = new
SqlParameter("@rentalPickupLocation", SqlDbType.VarChar, 50);
paramRentalPickupLocation.Value = rentalPickupLocation;
adapter.SelectCommand.Parameters.Add(paramRentalPickupLocation);
SqlParameter paramPickupDate = new SqlParameter("@pickupDate",
SqlDbType.DateTime);
paramPickupDate.Value = pickupDate;
adapter.SelectCommand.Parameters.Add(paramPickupDate);
SqlParameter paramCarClassID = new SqlParameter("@carClassID",
SqlDbType.Int);
paramCarClassID.Value = carClassID;
adapter.SelectCommand.Parameters.Add(paramCarClassID);
adapter.Fill(car, "Car");
return car;
}
}
catch (Exception ex)
{
throw ex;
}
}

    public int AddBooking(int carID, string customerID, int carClassID,
string rentalPickupLocation, string rentalDropoffLocation, DateTime pickupDate,
DateTime dropoffDate)
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString = System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString; connection.Open();
SqlCommand command = new SqlCommand("AddBooking", connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter paramCarID = new SqlParameter("@carID", SqlDbType.Int);
paramCarID.Value = carID;
command.Parameters.Add(paramCarID);
SqlParameter paramCustomerID = new SqlParameter("@customerID",
SqlDbType.VarChar, 20);
paramCustomerID.Value = customerID;
command.Parameters.Add(paramCustomerID);
SqlParameter paramCarClassID = new SqlParameter("@carClassID",
SqlDbType.Int);
paramCarClassID.Value = carClassID;
command.Parameters.Add(paramCarClassID);
SqlParameter paramRentalPickupLocation = new
SqlParameter("@rentalPickupLocation", SqlDbType.VarChar, 50);
paramRentalPickupLocation.Value = rentalPickupLocation;
command.Parameters.Add(paramRentalPickupLocation);
SqlParameter paramRentalDropoffLocation = new
SqlParameter("@rentalDropOffLocation", SqlDbType.VarChar, 50);
paramRentalDropoffLocation.Value = rentalDropoffLocation;
command.Parameters.Add(paramRentalDropoffLocation);
SqlParameter paramPickupDate = new SqlParameter("@pickupDate",
SqlDbType.DateTime);
paramPickupDate.Value = pickupDate;
command.Parameters.Add(paramPickupDate);
SqlParameter paramDropoffDate = new SqlParameter("@dropoffDate",
SqlDbType.DateTime);
paramDropoffDate.Value = dropoffDate;
command.Parameters.Add(paramDropoffDate);
SqlParameter paramBookingID = new SqlParameter("@bookingID", SqlDbType.Int);
//设置paaBookingID参数为输出参数 
paramBookingID.Direction = ParameterDirection.Output;
command.Parameters.Add(paramBookingID);
command.ExecuteNonQuery();
int bookingID = Convert.ToInt32(command.Parameters["@bookingID"].Value);
return bookingID;
}
}
catch (Exception ex)
{
throw ex;
}
}

    public DataTable GetCarClassCodes()
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString = System.Web.Configuration.WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString;
DataTable car = new DataTable("CarClassCodes");
SqlDataAdapter adapter = new SqlDataAdapter("GetCarClassCodes", connection);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
adapter.Fill(car);
return car;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
namespace CarRentalReservation
{
public class StockDB
{
public XmlDocument GetStockQuote(string name)
{
try
{
using (SqlConnection connection = new SqlConnection())
{
string connectionString = System.Web.Configuration.
WebConfigurationManager.
ConnectionStrings["rentalCarReservation"].ConnectionString;
connection.ConnectionString = connectionString; connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "GetStockQuote";
command.CommandType = CommandType.StoredProcedure;
SqlParameter paramName = new SqlParameter("@Name", SqlDbType.Char, 4);
paramName.Value = name;
command.Parameters.Add(paramName);
XmlReader reader = command.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
//将XmlReader加载到XmlDocument object
doc.Load(reader);
return doc;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}