110,536
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.Text;
using RoomType.Model;
using RoomType.IDAL;
using System.Data;
using System.Data.SqlClient;
namespace RoomType.DAL.SQLServer
{
public class RoomTypeDAL:IRoomType
{
private static readonly string SELECT = "select TypeID,TypeName,TypePrice,AddBedPrice,IsAddBed,Remark from RoomType";
private static readonly string SELECT_NAME = SELECT + " where TypeName = @TypeName";
private static readonly string SELECT_LIKE_NAME = SELECT + " where TypeName like '%'+@TypeName+'%'";
private static readonly string UPDATE = "update RoomType set TypeName=@TypeName,TypePrice=@TypePrice,AddBedPrice=@AddBedPrice,IsAddBed=@IsAddBed,Remark=@Remark where TypeID = @TypeID";
private static readonly string DELETE = "delete from RoomType where TypeID = @TypeID";
private static readonly string INSERT = "insert into RoomType(TypeName,TypePrice,AddBedPrice,IsAddBed,Remark) values(@TypeName,@TypePrice,@AddBedPrice,@IsAddBed,@Remark)";
#region IRoomType 成员
//查询所有
public List<RoomTypes> SelectAll()
{
List<RoomTypes> roomTypess = new List<RoomTypes>();
SqlDataReader reader = DBHelper.ExecuteDataReader(SELECT,CommandType.Text);
FillRoomTypes(roomTypess, reader);
return roomTypess;
}
private static void FillRoomTypes(List<RoomTypes> roomTypess, SqlDataReader reader)
{
while (reader.Read())
{
RoomTypes room = new RoomTypes();
FillRoom(reader, room);
roomTypess.Add(room);
}
}
private static void FillRoom(SqlDataReader reader, RoomTypes room)
{
room.TypeID = reader.GetInt32(0);
room.TypeName = reader.GetString(1);
room.TypePrice = reader.GetDecimal(2);
room.AddBedPrice = reader.GetDecimal(3);
room.IsAddBed = reader.GetString(4);
room.Remark = reader[5] is DBNull ? string.Empty : reader.GetString(5);
}
//模糊查询
public List<RoomTypes> SelectByAll(string name)
{
List<RoomTypes> roomTypess = new List<RoomTypes>();
SqlParameter ps = new SqlParameter("@TypeName", name);
SqlDataReader reader = DBHelper.ExecuteDataReader(SELECT_LIKE_NAME, CommandType.Text,ps);
FillRoomTypes(roomTypess, reader);
return roomTypess;
}
public int Select(string name)
{
int i = 0;
SqlParameter ps = new SqlParameter("@TypeName", name);
SqlDataReader reader = DBHelper.ExecuteDataReader(SELECT_NAME,CommandType.Text,ps);
if (reader.Read())
{
i = reader.GetInt32(0);
}
return i;
}
//增加
public int Insert(RoomTypes roomType)
{
SqlParameter [] ps = new SqlParameter[5];
ps[0] = new SqlParameter("@TypeName",roomType.TypeName);
ps[1] = new SqlParameter("@TypePrice",roomType.TypePrice);
ps[2] = new SqlParameter("@AddBedPrice",roomType.AddBedPrice);
ps[3] = new SqlParameter("@IsAddBed",roomType.IsAddBed);
ps[4] = new SqlParameter("@Remark",roomType.Remark);
int i = DBHelper.ExecuteNonQuery(INSERT,CommandType.Text,ps);
return i;
}
//修改
public int Update(RoomTypes roomType)
{
SqlParameter[] ps = new SqlParameter[6];
ps[0] = new SqlParameter("@TypeName", roomType.TypeName);
ps[1] = new SqlParameter("@TypePrice", roomType.TypePrice);
ps[2] = new SqlParameter("@AddBedPrice", roomType.AddBedPrice);
ps[3] = new SqlParameter("@IsAddBed", roomType.IsAddBed);
ps[4] = new SqlParameter("@Remark", roomType.Remark);
ps[5] = new SqlParameter("@TypeID", roomType.TypeID);
int i = DBHelper.ExecuteNonQuery(UPDATE, CommandType.Text, ps);
return i;
}
//删除
public int Delete(RoomTypes roomType)
{
SqlParameter ps = new SqlParameter("@TypeID",roomType.TypeID);
int i = DBHelper.ExecuteNonQuery(DELETE,CommandType.Text,ps);
return i;
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace RoomType.DAL.SQLServer
{
internal class DBHelper
{
private static readonly string connString=
ConfigurationManager.ConnectionStrings["HotelManageConnectionString"].ConnectionString;
public static DataSet ExecuteDataSet(string sql)
{
SqlDataAdapter sda = new SqlDataAdapter(sql,connString);
DataSet ds = new DataSet();
sda.Fill(ds, "HotelManage");
return ds;
}
public static SqlDataReader ExecuteDataReader(string sql, CommandType type, params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql,connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
int i = command.ExecuteNonQuery();
connection.Close();
return i;
}
public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] ps)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand command = new SqlCommand(sql, connection);
command.CommandType = type;
if (ps != null)
command.Parameters.AddRange(ps);
connection.Open();
object obj = command.ExecuteScalar();
connection.Close();
return obj;
}
}
}
using System;
using System.Collections.Generic;
using System.Text;
using RoomType.Model;
namespace RoomType.IDAL
{
public interface IRoomType
{
List<RoomTypes> SelectAll();
List<RoomTypes> SelectByAll(string name);
int Select(string name);
int Insert(RoomTypes roomType);
int Update(RoomTypes roomType);
int Delete(RoomTypes roomType);
}
}
using System;
using System.Collections.Generic;
using System.Text;
namespace RoomType.Model
{
[Serializable]
public class RoomTypes
{
public RoomTypes() { }
//编号ID
protected int _TypeID;
//类型名称
protected string _TypeName;
//价格
protected decimal _TypePrice=0.0m;
//加床价格
protected decimal _AddBedPrice=0.0m;
//是否加床
protected string _IsAddBed;
//备注
protected string _Remark;
/// <summary>
/// 备注
/// </summary>
public string Remark
{
get { return _Remark; }
set { _Remark = value; }
}
/// <summary>
/// 是否加床
/// </summary>
public string IsAddBed
{
get { return _IsAddBed; }
set
{
if (value.Trim() == "是" || value.Trim() == "否")
{
_IsAddBed = value;
}
else
{
throw new Exception("是否加床必须为:(是/否)?");
}
}
}
/// <summary>
/// 加床价格
/// </summary>
public decimal AddBedPrice
{
get { return _AddBedPrice; }
set
{
if (value >= 0)
{
_AddBedPrice = value;
}
else
{
throw new Exception("加床价格不能小于0!");
}
}
}
/// <summary>
/// 价格
/// </summary>
public decimal TypePrice
{
get { return _TypePrice; }
set
{
if (value >= 0)
{
_TypePrice = value;
}
else
{
throw new Exception("价格不能小于0!");
}
}
}
/// <summary>
/// 类型名称
/// </summary>
public string TypeName
{
get { return _TypeName; }
set { _TypeName = value; }
}
/// <summary>
/// 编号ID
/// </summary>
public int TypeID
{
get { return _TypeID; }
set { _TypeID = value; }
}
}
}