hotelmanageda.cs

利用vs2008+sql开发的酒店管理系统

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




namespace HotelManageDAL
{
    public class HotelManageDA
    {
        //获取连接字符串
        private string conString = ConfigurationManager.ConnectionStrings["HotelConString"].ToString();
        DataSet ds =null;
        /// <summary>
        /// 通用查找方法
        /// </summary>
        /// <param name="strSel"></param>
        /// <returns>数据集</returns>
        public DataSet Select(string strSel)
        {
            ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(conString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(strSel,cn);
                sda.Fill(ds,"hotel");
            }
            return ds;
        }
        /// <summary>
        /// 通用增删改方法
        /// </summary>
        /// <param name="strExec"></param>
        /// <returns>是否执行成功的标记</returns>
        public bool Exec(string strExec)
        {
            using (SqlConnection cn = new SqlConnection(conString))
            {
                SqlCommand cm = new SqlCommand(strExec, cn);
                cn.Open();
                cm.ExecuteNonQuery();
            }
            return true;
        }
        /// <summary>
        /// 查找客房信息的方法
        /// </summary>
        /// <returns></returns>
        public DataSet SelectDA()
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(conString))
            {
                string sql = "select r.typeID,number,TypeName,TypePrice,GuestNumber,BedNumber,State,Description,roomID from room r inner join roomType rt on r.typeID=RT.TypeID ";
                SqlDataAdapter sda = new SqlDataAdapter(sql, cn);
                sda.Fill(ds,"RoomInfo");
                return ds;
            }
        }
        /// <summary>
        /// 插入的方法
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public bool InsertRoomDA(Room room)
        {
            DataSet ds = new DataSet();
            using (SqlConnection cn = new SqlConnection(conString))
            {
                string sql = "Insert into room values('" + room.Number
                                                + "'," + room.TypeID
                                                + "," + room.BedNumber
                                                + ",'" + room.Discription
                                                + "','" + room.State
                                                + "'," + room.GuestNumber + ")";
                SqlCommand cm = new SqlCommand(sql,cn);
                cn.Open();
                cm.ExecuteNonQuery();
                return true;
            }
        }

        /// <summary>
        /// 修改的方法
        /// </summary>
        /// <param name="rt"></param>
        /// <param name="r"></param>
        /// <returns></returns>
        public bool UpdateConnectionTable(RoomType rt,Room r)
        {
            string sql1 = "update room set Number='"+r.Number+"',GuestNumber='"+r.GuestNumber
                                                    +"',bedNumber='"+r.BedNumber+"',State='"+r.State
                                                    + "',Description='" + r.Discription
                                                    +"' where TypeID="+r.TypeID+" and RoomID="+r.RoomID+"";
            string sql2 = "update roomType set TypeName='"+rt.TypeName+"' where TypeID="+rt.TypeID+"";
            using (SqlConnection cn = new SqlConnection(conString))
            { 
                 SqlCommand cm1 = new SqlCommand(sql1, cn);
                cn.Open();
                SqlTransaction tran1 = null;
                SqlTransaction tran2 = null;
                try
                {
                    tran1 = cn.BeginTransaction();
                    cm1.Transaction = tran1;
                    cm1.ExecuteNonQuery();
                    tran1.Commit();

                    SqlCommand cm2 = new SqlCommand(sql2, cn);
                    tran2 = cn.BeginTransaction();
                    cm2.Transaction = tran2;
                    cm2.ExecuteNonQuery();
                    tran2.Commit();
                    return true;
                }
                catch
                {
                    tran1.Rollback();
                    tran2.Rollback();
                }      
            } return false;
        }
        
            
        }


    }