酒店客房预订 - 仅显示特定日期的可用客房 [英] Hotel Room Booking - Show Only Available Rooms For Specific Dates

问题描述

我已经预订了一个酒店房间预订C＃桌面应用程序与microsoft localDB v11和visual studio 2013.



i有一个组合框，显示存在的所有房间我的房间桌。



这是我的组合框填充功能

i have made a hotel room booking C# desktop application with microsoft localDB v11 and visual studio 2013.

i have a combo box that shows all the rooms that exist in my room table.

and this is my combobox fill function

//fill room combobox with db values
        void fillComboBoxRoom()
        {
            SqlDataReader datareader = null;
            mycon.ConnectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\George\Documents\Visual Studio 2013\Projects\HotelManagement\HotelManagement\HotelDB.mdf;Integrated Security=True";
            cmd.Connection = mycon;
            cmd.CommandText = "select * from Room";


            try
            {
                mycon.Open();
                datareader = cmd.ExecuteReader();
                while (datareader.Read())
                {
                    roomtext.Items.Add((int)datareader["Αριθμός δωματίου"]);

                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            mycon.Close();

        }

和两个datetimepickers（checkindate，checkoutdate）



i需要正确的sql查询才能插入我的组合框功能，所以房间组合框只显示在两个日期时间选择器中选择的日期之间可用的房间。





这是我的预订表

and two datetimepickers (checkindate,checkoutdate)

i need the correct sql query to insert to my combobox function so the rooms combobox show only the rooms that are available between the dates that are picked in the two datetimepickers.


here is my reservations table

CREATE TABLE [dbo].[Reservation] (
    [Αριθμός Κράτησης]        INT           IDENTITY (1, 1) NOT NULL,
    [Δωμάτιο]                 INT           NULL,  <--this is the room number
    [Α.Φ.Μ. Πελάτη]           NVARCHAR (20) NULL,  --client id
    [Α.Φ.Μ. Πρακτορείου]      NVARCHAR (25) NULL,  --agency id
    [Ημερομηνία άφιξης]       DATE          NULL,  <--this is the checkin date
    [Ημερομηνία αναχώρησης]   DATE          NULL,  <--this is the checkout date
    [Ενήλικες]                INT           NULL,  --grown ups
    [Παιδιά]                  INT           NULL,  --kids
    [Πώς μας βρήκε]           NVARCHAR (50) NULL,  --how did he find us
    [Ποσό]                    MONEY         NULL,  -- amount
    [Προκαταβολή]             MONEY         NULL,  --advance
    [Ημερομηνία προκαταβολής] DATE          NULL,  --advance date
    [Υπόλοιπο]                MONEY         NULL,  --balance
    [Τράπεζα]                 NVARCHAR (30) NULL,  --bank
    [Τρόπος πληρωμής]         NVARCHAR (30) NULL,  --pay way
    PRIMARY KEY CLUSTERED ([Αριθμός Κράτησης] ASC),
    CONSTRAINT [FK_Reservation_ToRooms] FOREIGN KEY ([Δωμάτιο]) REFERENCES [dbo].[Room] ([Αριθμός δωματίου]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Reservation_ToAgencys] FOREIGN KEY ([Α.Φ.Μ. Πρακτορείου]) REFERENCES [dbo].[Agency] ([Α.Φ.Μ.]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Reservation_ToCustomers] FOREIGN KEY ([Α.Φ.Μ. Πελάτη]) REFERENCES [dbo].[Customer] ([Α.Φ.Μ]) ON DELETE CASCADE ON UPDATE CASCADE);

和我的房间桌子

and my room table

CREATE TABLE [dbo].[Room] (
    [Αριθμός δωματίου] INT           NOT NULL,    <----room number
    [Όροφος]           INT           NULL,  --floor
    [Τύπος Δωματίου]   NVARCHAR (30) NULL,  --room type
    [Τηλεόραση]        NVARCHAR (10) NULL,  -- tv
    [Κλιματισμός]      NVARCHAR (10) NULL,  --air condition
    [Mini Bar]         NVARCHAR (10) NULL,  --minibar
    [Θέα]              NVARCHAR (10) NULL,  --view
    CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([Αριθμός δωματίου] ASC),
    CONSTRAINT [FK_Room_ToRoomType] FOREIGN KEY ([Τύπος Δωματίου]) REFERENCES [dbo].[RoomType] ([Περιγραφή])
);

推荐答案

0）不要使用连接来形成SQL语句;使用参数化查询。

1）不要将数据访问代码直接放在表单代码中;创建一个带有数据访问层的分层应用程序。





你不应该检查 - 你应该得到一个列表 的房间在指定的时间内可用，并且仅提供这​​些房间。你能想象一个忙碌的1000间客房的酒店，用户必须继续尝试每个房间，直到找到可用的房间吗？





I也不是删除的粉丝，特别是删除的删除 - 有一个字段来表示状态/状态通常更好。

0) Do not use concatenation to form an SQL statement; use a parameterized query.
1) Do not put Data Access Code directly in the Form code; create a layered application with a Data Access Layer.


You shouldn't need to check that -- you should get a list of rooms that are available for the specified period and only present those. Can you imagine a busy 1000-room hotel and the users have to keep trying each room until they find one that's available?


I'm also not a fan of deletes, especially casaded deletes -- have a field to indicate state/status is generally better.

这篇关于酒店客房预订 - 仅显示特定日期的可用客房的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！