Unit 15 – Software Development

This page describes creating a basic website that links to a database, allowing the user to book tables at a restaurant for a specific date. It does not cover aesthetic concerns. These can be found in the unit on website development.

It filters the list of tables offered to users so that two people can’t book the same table at the same time, and offers a ‘reservations’ view, so that a user can view their reservations.

It also implements a user/password scheme; without this, it would not be possible to log on to the site and record who was making the booking.

A couple of keywords that are important to this:

Session variables – a website is different to an application, in that variables cease to exist once the page is served to a user. This makes tracking things between pages difficult. The solution is to use session variables: variables that are stored on the web server, and kept between successive page requests. A separate copy of the variables is stored for each user of the website.
OleDb – a technology that allows any data source to be queried as if it was a database. In this example we are using an Access database as the datastore, and using OleDb to connect to it.
Database/persistence – you will need to use some form of database to ensure data persistence. Without it, booking details would be lost.

Project type

The information below applies to an ASP.NET WebForms project, using C#.

Structure of database

The database contains a table for each entity being tracked. For the restaurant, we are interested in: customers, tables, and bookings. Therefore we have three tables to store our data, each with a primary key. See the access file for exact details.

Default page

This is the first page that appears when the website is visited. It contains two text boxes for the username and password, and a button which the user can click in order to validate their credentials.

protected void Button1_Click(object sender, EventArgs e)
        {
            //Create a connection to the database, located at the location specified (Data Source = )
            using (OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\Restaurant.accdb"))
            {
                //Open the connection. 
                oConn.Open();
                //Create a SQL command to look for a match for the username and password
                OleDbCommand oCommand = new OleDbCommand("Select * from Customers c where c.[CustomerUsername]=@username and c.[CustomerPassword]=@password", oConn);
                oCommand.Parameters.Add(new OleDbParameter("@username", TextBox1.Text));
                oCommand.Parameters.Add(new OleDbParameter("@password", TextBox2.Text));
                OleDbDataReader dr = oCommand.ExecuteReader();
                //if dr.Read == did it find an entry?
                if (dr.Read()){
                    //Yes, store user ID and username as SESSION VARIABLES
                    Session["userID"] = dr.GetInt32(0);
                    Session["userName"] = TextBox1.Text;
                    dr.Close();
                    oConn.Close();
                    //Close DB, redirect user to the MainPage
                    Response.Redirect("MainPage.aspx");
                }

                //Still need to close the connection if we didn't find details. You may wish to show an error message here
                dr.Close();
                oConn.Close();
            }
        }

Main Page

This page shows the user a list of all bookings they have made, which is displayed as a table within a DIV element on the page. It also contains a button which when clicked directs the user to the MAKE BOOKING page.

        protected void Page_Load(object sender, EventArgs e)
        {
            using (OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\restaurant.accdb"))
            {
                oConn.Open();
                //SQL query that fetches the current user's bookings from the DB
                OleDbCommand oCommand = new OleDbCommand("Select "
                    + "t.TableDescription, b.BookingDate from Tables t, "
                    + "Bookings b where t.TableID = b.TableID "
                    + "and b.CustomerID=@customerID "
                    + "order by b.BookingDate asc", oConn);
                //Use Session variable to get userID. These are all stored as 'object' so needs casting back to int
                oCommand.Parameters.Add(new OleDbParameter("@customerID", (int)Session["userID"]));
                OleDbDataReader dr = oCommand.ExecuteReader();
                //Create a TABLE
                Table tbl = new Table();
                //Create a header row
                TableHeaderRow th = new TableHeaderRow();
                //Create header cells and add them to the header row
                TableHeaderCell thc = new TableHeaderCell();
                thc.Text = "Table booked";
                th.Cells.Add(thc);
                thc = new TableHeaderCell();
                thc.Text = "Date of booking";
                th.Cells.Add(thc);
                tbl.Rows.Add(th);
                TableRow tr;
                TableCell tc;
                //Loop through all records
                while (dr.Read())
                {
                    //For each record make a new table row, and add tablecells containing data
                    tr = new TableRow();
                    tc = new TableCell();
                    tc.Text = dr.GetString(0);  //Table description, column 0 of requested data
                    tr.Cells.Add(tc);
                    tc = new TableCell();
                    tc.Text = dr.GetDateTime(1).ToShortDateString();    //Date, column 1
                    tr.Cells.Add(tc);
                    tbl.Rows.Add(tr);
                }
                //Close connections
                dr.Close();
                oConn.Close();
                //CONTENTDIV is the id of a DIV element on the page. Put the table in that DIV so it can be seen
                CONTENTDIV.Controls.Add(tbl);//CONTENTDIV defined in HTML page
            }
        }

        //Button directs user to MakeBooking page
        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Redirect("MakeBooking.aspx");
        }

Make Booking

This is the most complex page; it allows the user to pick when they are looking to make a booking, and it then lists the tables available, which is the list of tables in the restaurant, but not including any that already have a booking for the current date. AutoPostback is enabled on the calendar (properties in VS), which allows the code to be run every time the date is changed, resulting in an up to date list of available tables.

The page contains a calendar item for selecting the date, and a dropdown list called DropDownList1 which is used to hold the details of the available tables.

 protected void Page_Load(object sender, EventArgs e)
        {
            //Selection changed event for calendar and index changed for drop downlist used, plus list is autopostback
           
        }

        protected void Calendar1_SelectionChanged(object sender, EventArgs e)
        {
            //Call function below; this is the event handler for date changed
            ReadAvailableTables(Calendar1.SelectedDate);
        }

        private void ReadAvailableTables(DateTime checkDate)
        {
            
            DropDownList1.Items.Clear();    //Clear list
            using (OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\restaurant.accdb"))
            {
                oConn.Open();
                OleDbCommand oCommand = new OleDbCommand("Select t.* from Tables t where t.TableID not in (select b.TableID from Bookings b where b.BookingDate=@date)", oConn);
                oCommand.Parameters.Add(new OleDbParameter("@date", checkDate));
                OleDbDataReader dr = oCommand.ExecuteReader();
                DropDownList1.Items.Add(new ListItem("Select a table", "0"));
                while (dr.Read())
                {
                    DropDownList1.Items.Add(new ListItem(dr.GetString(1), dr.GetInt32(0).ToString()));
                }
                dr.Close();
                oConn.Close();
            }
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Event handler for dropdown list item changed.
            //We don't want the 'save' button to be enabled if no choice has been made re table
            //Disable button if no table is selected (Index<=0). Enable otherwise
            if (DropDownList1.SelectedIndex > 0) Button1.Enabled = true; else Button1.Enabled = false;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            //Create the booking 
            int customerID = (int)Session["userID"];
            int tableID = Convert.ToInt32(DropDownList1.SelectedValue);
            DateTime bookingDate = Calendar1.SelectedDate;

            using (OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=N:\restaurant.accdb"))
            {
                oConn.Open();
                OleDbCommand oCommand = new OleDbCommand("insert into Bookings (BookingID, CustomerID, TableID, BookingDate) values ('', @customer, @table, @date)", oConn);
                oCommand.Parameters.Add(new OleDbParameter("@customer", customerID));
                oCommand.Parameters.Add(new OleDbParameter("@table", tableID));
                oCommand.Parameters.Add(new OleDbParameter("@date", bookingDate));
                oCommand.ExecuteNonQuery();
                oConn.Close();
            }
            //Redirect to the main page
            Response.Redirect("MainPage.aspx");
        }