Member Avatar for shweta_babbar

In my website on trip planner,I want that user can check if room is available or not...b/w particular dates..
how can i do this?
also..how shud i create the database..??as in what atrributes shud i use?

  • 2 Contributors
  • 3 Replies
  • 112 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by wilch

Dani AI

Generated

Good call from to separate room data from reservations. For a correct availability check, store each booking as an explicit date range (start and end) instead of a single booked/unbooked flag, and treat the end date consistently (usually as the check-out date — i.e., an exclusive endpoint). asked for C# — below is a compact, practical pattern you can apply.

The standard overlap test (for half-open ranges: [Start, End)) is:

  • A new booking conflicts if requestedStart < existingEnd AND requestedEnd > existingStart.

Example SQL (parameterized) to find any conflict:

SELECT 1
FROM Bookings
WHERE RoomID = @roomId
  AND @requestedStart < EndDate
  AND @requestedEnd   > StartDate;

C# pattern (ADO.NET): check inside a serializable transaction, then insert if no conflict. This reduces race conditions under normal load:

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    using (var tran = conn.BeginTransaction(IsolationLevel.Serializable))
    {
        using (var check = new SqlCommand(
            "SELECT COUNT(1) FROM Bookings WHERE RoomID=@roomId AND @start < EndDate AND @end > StartDate",
            conn, tran))
        {
            check.Parameters.AddWithValue("@roomId", roomId);
            check.Parameters.AddWithValue("@start", start);
            check.Parameters.AddWithValue("@end", end);
            int conflicts = (int)check.ExecuteScalar();
            if (conflicts == 0)
            {
                using (var ins = new SqlCommand(
                    "INSERT INTO Bookings(RoomID, StartDate, EndDate, CustomerID) VALUES(@roomId,@start,@end,@cust)",
                    conn, tran))
                {
                    ins.Parameters.AddWithValue("@roomId", roomId);
                    ins.Parameters.AddWithValue("@start", start);
                    ins.Parameters.AddWithValue("@end", end);
                    ins.Parameters.AddWithValue("@cust", customerId);
                    ins.ExecuteNonQuery();
                }
                tran.Commit();
            }
            else tran.Rollback();
        }
    }
}

Practical tips: index the booking table by room and dates (e.g., RoomID, StartDate, EndDate). Use DATE for night-based bookings, or DATETIMEOFFSET if timezones matter. Always validate start < end, decide and document whether end is inclusive or exclusive, and store booking status (active/cancelled) rather than deleting rows. These steps keep checks fast and the logic reliable.

Recommended Answers

All 3 Replies

Member Avatar for wilch

Well for your database you can create a table called tblRooms, which contains the following fields:
- room_id (primary key)
- room_name (varchar)
- room_features (string/text ?!?)
- is_booked (boolean)
- date_booked (date)
- duration_booked (integer => num of days)

Now to check if the room is available between certain dates, you search for the range of dates from (date_booked) to the (date_booked + duration_booked) date.

I also think, you should have a table tblBookings, to store all of your bookings. This table must also have the foreign key room_id from the tblRooms.

The design, is not so perfect as i can perceive other things already, but it should give you an idea of the course to take.

Member Avatar for shweta_babbar

I didn't understand clearly ..
cn u gv me C# code..4 this trip planner..??

Edited by shweta_babbar because: n/a
Member Avatar for wilch

Sorry mate, but my c# is not so sharp, if you could write something out, and then i will aid you along the way ! :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.