Accp7.0 .net三层开发S2项目酒店管理系统

@Accp7.0 .net三层开发S2项目酒店管理系统

Accp7.0 .net三层开发S2项目酒店管理系统

仅供参考
DAL内容

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelManager.Models;
using System.Data.SqlClient;

namespace HotelManager.DAL
{
public class RoomTypeService
{
///
/// 刷新
///
///
public static List GetAllType()
{
List allType = new List();
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “select * from RoomType”;
SqlCommand cmd = new SqlCommand(sql, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
RoomType type = new RoomType();
type.Id = (int)dr[“TypeId”];
type.TypeName = dr[“TypeName”].ToString();
type.TypePrice = (Decimal)dr[“TypePrice”];
allType.Add(type);
}
}
return allType;
}
}
///
/// 添加功能
///
///
///
///
public static int AddType(string typeName, Decimal price)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “insert RoomType values (@TypeName,@Price)”;
SqlCommand cmd = new SqlCommand(sql,conn);
SqlParameter[] para = {
new SqlParameter("@TypeName",typeName),
new SqlParameter("@Price",price)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 修改功能
///
///
///
///
///
public static int UpdateType(string typeName, Decimal price, int Id)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “update RoomType set TypeName=@TypeName,TypePrice=@Price where TypeId=@Id”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] para = {
new SqlParameter("@TypeName",typeName),
new SqlParameter("@Price",price),
new SqlParameter("@Id",Id)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 删除功能
///
///
///
public static int DeleteType(int Id)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “delete from RoomType where TypeId=@Id”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter para = new SqlParameter("@Id", Id);
cmd.Parameters.Add(para);
return cmd.ExecuteNonQuery();
}
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelManager.Models;
using System.Data.SqlClient;

namespace HotelManager.DAL
{
public class RoomExtensionService
{
///
/// 查询
///
///
public static List GetAllRoom()
{
List allRoom = new List();
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = @“select r.RoomId,r.BedNum,r.RoomTypeId,r.RoomStateID,rs.RoomStateName,rt.TypeName,r.Description,r.GuestNum from Room r
inner join RoomType rt on r.RoomTypeID=rt.TypeID
inner join RoomState rs on r.RoomStateID=rs.RoomStateID”;
SqlCommand cmd = new SqlCommand(sql, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
RoomExtension room = new RoomExtension();
room.RoomId =(int)dr[“RoomId”];
room.TypeName = dr[“TypeName”].ToString();
room.RoomStateId = (int)dr[“RoomStateID”];
room.RoomTypeId = (int)dr[“RoomTypeId”];
room.RoomStateName = dr[“RoomStateName”].ToString();
room.BedNum = (int)dr[“BedNum”];
room.Description = dr[“Description”].ToString();
room.GuestNum = (int)dr[“GuestNum”];
allRoom.Add(room);
}
}
return allRoom;
}
}
///
/// 添加
///
///
///
public static int AddRoom(Room room)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = @“insert Room (BedNum,RoomTypeID,Description) values(@BedNum,@RoomTypeID,@Description)”;
SqlCommand cmd = new SqlCommand(sql,conn);
SqlParameter[] para = {
new SqlParameter("@BedNum",room.BedNum),
new SqlParameter("@RoomTypeID",room.RoomTypeId),
new SqlParameter("@Description",room.Description)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 修改
///
///
///
public static int UpdateRoom(Room room)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “update Room set BedNum=@BedNum,RoomTypeID=@TypeId,Description=@Description where RoomId=@RoomId”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] para = {
new SqlParameter("@BedNum",room.BedNum),
new SqlParameter("@TypeId",room.RoomTypeId),
new SqlParameter("@Description",room.Description),
new SqlParameter("@RoomId",room.RoomId)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 删除
///
///
///
public static int DeleteRoom(int Id)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “delete from Room where RoomId=@Id”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter para = new SqlParameter("@Id", Id);
cmd.Parameters.Add(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 传值价格
///
///
///
public static Decimal GetPrice(int roomId)
{
Decimal price = 0;
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = @“select rt.TypePrice from RoomType rt
inner join Room r on rt.TypeID=r.RoomTypeID
where RoomId=@Id”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter para = new SqlParameter("@Id",roomId);
cmd.Parameters.Add(para);
price = (Decimal)cmd.ExecuteScalar();
}
return price;
}
///
/// 修改入住状态
///
///
///
///
///
public static int UpdateRoomInfo(int roomId,int stateId,int guestNum)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “update Room set RoomStateId=@StateId,GuestNum=@GuestNum where RoomId=@RoomId”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] para ={ new SqlParameter("@RoomId",roomId),
new SqlParameter("@StateId",stateId),
new SqlParameter("@GuestNum",guestNum)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelManager.Models;
using System.Data.SqlClient;

namespace HotelManager.DAL
{
public class ResideStateService
{
public static List GetAllState()
{
List allState = new List();
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = @“select * from ResideState”;
SqlCommand cmd = new SqlCommand(sql, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
ResideState state = new ResideState();
state.ResideId = (int)dr[“ResideId”];
state.ResideName = dr[“ResideName”].ToString();
allState.Add(state);
}
}
return allState;
}

    }
}

}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelManager.Models;
using System.Data.SqlClient;

namespace HotelManager.DAL
{
public class GuestService
{
///
/// 添加
///
///
///
public static int GuestRecord(Guest guest)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = “insert GuestRecord (GuestName,IdentityID,RoomID,ResideDate,deposit) values(@GuestName,@IdentityID,@RoomID,@ResideDate,@deposit)”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] para = {
new SqlParameter("@GuestName",guest.Name),
new SqlParameter("@IdentityID",guest.IdCard),
new SqlParameter("@RoomID",guest.RoomId),
new SqlParameter("@ResideDate",guest.ResideDate),
new SqlParameter("@deposit",guest.Deposit)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
///
/// 查询
///
///
public static List GetAllGuest()
{
List allGuest = new List();
using (SqlConnection conn = DBHelper.GetConn())
{
string sql =@“select g.GuestId,g.GuestName,g.IdentityID,g.RoomId,g.ResideDate,g.deposit,
r.ResideId,r.ResideName,g.LeaveDate from GuestRecord g
inner join ResideState r on g.ResideID=r.ResideID”;
SqlCommand cmd = new SqlCommand(sql, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Guest guest = new Guest();
guest.GuestId = (int)dr[“GuestId”];
guest.Name = dr[“GuestName”].ToString();
guest.IdCard = dr[“IdentityID”].ToString();
guest.RoomId=(int)dr[“RoomId”];
guest.ResideDate = Convert.ToDateTime(dr[“ResideDate”]);
guest.Deposit = Convert.ToDecimal(dr[“deposit”]);
guest.ResideId = (int)dr[“ResideId”];
guest.ResideName = dr[“ResideName”].ToString();
allGuest.Add(guest);
}
}
return allGuest;
}
}
///
/// 修改
///
///
///
///
///
public static int UpdateGuestInfo(int guestId,Decimal toatalMoney,DateTime leaveDate)
{
using (SqlConnection conn = DBHelper.GetConn())
{
string sql = @“update GuestRecord set ResideID=2,TotalMoney=@TotalMoney,
LeaveDate=@LeaveDate where GuestID=@GuestID”;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter[] para = {
new SqlParameter("@TotalMoney",toatalMoney),
new SqlParameter("@LeaveDate",leaveDate),
new SqlParameter("@GuestID",guestId)
};
cmd.Parameters.AddRange(para);
return cmd.ExecuteNonQuery();
}
}
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace HotelManager.DAL
{
public class DBHelper
{
public static readonly string strConn = ConfigurationManager.ConnectionStrings[“HotelManagerSql”].ToString();
public static SqlConnection GetConn()
{
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
return conn;
}
}
}