ASP.NET University venue reservation system [source code + database]

This is my final homework when I am taking the professional course "Web Application Development Technology". The group is divided into groups of 5 people. As a result, I came back for repairs as a senior. Just do it alone. It is developed with asp.net technology, and some CSS frameworks are used to beautify the front end. The data interaction uses AJAX, and the database uses SQL Sever.

Code acquisition

Follow the official account [Program Ape Sound] and reply [WRB] in the background to get it.

1. Goals and application scenarios

Students usually need a certain amount of venue cooperation when performing various activities. If it is an outdoor venue, such as a playground, it can be used without a loan reservation. However, most activities need to be completed in indoor venues, such as group meetings, class meetings, class networking, etc. In addition, the teacher groups need to organize academic exchange activities and so on, but also need to be completed in indoor venues. Huazhong University of Science and Technology has ample classrooms and public spaces. Even on weekdays, there are plenty of free spaces left. The venues include Qiming College, teaching buildings, public rooms in student apartments, classrooms in the college, etc. In order to meet the needs of students for learning activities, promote communication among students, and make full use of the venues within the public classrooms, our team decided to design and implement the venue reservation system of Huazhong University of Science and Technology. The requirements of the system are as follows:

  • Users can log in to the system to make classroom reservations.
  • The system needs to support multi-user use, and the appointment time period of the same classroom between users cannot conflict.
  • If the classroom is no longer needed, users can choose to cancel their appointment.
  • Users can see their historical appointment information.

According to the above requirements, the system functions to be implemented are as follows:

  • Login and registration: New users can register through the registration page, and then use the registered account password to log in and use the system. The password is saved in the database in MD5 cipher text to ensure user privacy.
  • Venue display: The system reads the current venue information from the database, such as the type of venue, the name of the venue, whether it is free, and other information. And display it to the front desk of the page for users to view and choose.
  • Reservation: The user selects the appropriate venue according to the type of venue he needs, and the system displays the time period that the venue has been reserved to avoid conflicts. After the user selects the time period, the appointment can be made. The system detects whether the appointment conflicts, and if there is no conflict, the appointment is successful.
  • Cancel reservation: Show the venue and time slot that the user has reserved successfully, and the user can cancel the reservation if they don't need it.
  • Historical display: Display the user's historical appointment records.

The application groups of the system are mainly college students and university teachers. The scene faces a university campus. It aims to create a convenient venue reservation management system.

2. Design ideas

The design of the system is divided into three parts: front end, back end and database. The initially determined development platform is Microsoft's .net platform + SQL Sever database (of course it is also required by the course design). The front-end designs and layouts related pages, and can also use existing CSS frameworks for certain beautification. The backend can use the SqlHelper.cs packaged by the teacher to make some database requests. The front-end interaction mainly uses AJAX technology to achieve lightweight interaction. The main ideas about the front and back ends are as follows:

  • Front-end: Separate pages for development, which can make the structure clearer. The page can be divided into the login registration page and the main function page.
  • Back-end: The interactive technology uses AJAX for interaction. The back-end can use .ashx files to write APIs. The parameter action is used to control the type of request. For example, when action=”login”, it means that the request is a login function, so as to make judgments.
  • Database: Determine the function of the system, extract the corresponding data structure, and establish a database table.
    The functional structure design diagram of the system is as follows:
image

3. Key issues and implementation code

In this system, the key issues mainly include the following:

(1) AJAX interface design problem, the project is a lightweight project and does not require multiple back-end interface files (.ashx) to avoid management inconvenience. To use one .ashx to implement multiple requests, you need to add the function required by the request, that is, the action parameter. Therefore, the following framework is adopted:

public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string action = context.Request["action"];
            if (!string.IsNullOrEmpty(action))
            {
                switch (action)
                {
                    case "bookRoom":

At the beginning, the action parameter of the Request is judged, the function required by the request is determined, and then the corresponding code is called for Response.

(2) Use the json data returned by the request to create the corresponding HTML code and display it on the page. Take the display of venue information as an example:

Background code:

case "flushRoom":
    DataTable dtRoom = SqlHelper.getDataTable("select * from RoomInfo");
    string sJson = JsonConvert.SerializeObject(dtRoom);
    context.Response.Write(sJson);
    break;

Use sql statement to get all records, convert them to json string and then return to the foreground. Front desk AJAX request code:

function flushRoom() {
        $.ajax({
            type: 'get',
            url: 'RoomBookHandler.ashx',
            async: true,
            data: {
                action: 'flushRoom',
            },
            success: function (result) {
                document.getElementById("roomInfo").innerHTML = creatRoomTable(result);
                var footerStr = '<footer id="bookTimeSpan" ></footer >';
                document.getElementById("bookTimeSpan").innerHTML = footerStr;
            },
            error: function () {
                alert('获取数据失败!');
            }
        });
    }

Use the creatRoomTable function to generate the HTML table with the results returned by AJAX, and set it on the elements of the page. The code of creatRoomTable is as follows:

function creatRoomTable(dataStr) {
        var dataList = JSON.parse(dataStr);
        var trStr = '<table class="primary" id="roomInfo" style="width: 100%"><tr>' +
            '<th> 教室号</th> <th>教室类型</th><th>容纳人数</th>' +
            '<th> 教室状态</th> <th>教室说明</th><th>是否预约</th></tr >';
        //循环遍历出json对象中的每一个数据并显示在对应的td中
        for (i = 0; i < dataList.length; i++) {
            trStr += '<tr>';
            trStr += '<td>' + dataList[i].RoomNumber + '</td>';
            trStr += '<td>' + dataList[i].RoomType + '</td>';
            trStr += '<td>' + dataList[i].RoomPeople + '</td>';
            trStr += '<td>' + dataList[i].RoomStatus + '</td>';
            trStr += '<td>' + dataList[i].Remarks + '</td>';
            trStr += '<td>';
            if (dataList[i].RoomStatus.toString().trim() === "空闲") {
                trStr += '<label><input type="radio" onClick="getRoomTimeSpan()" name="selectRoom" value="' + dataList[i].RoomNumber.toString() + '" /><span class="checkable">预定</span></label>';
            }
            else {
                trStr += "不可用";
            }
            trStr += '</td>';
            trStr += '</tr>';
        }
        trStr += '</table>'
        return trStr;
    }

First, you need to convert the string into a json object, then build the HTML code of the table, and traverse the json object to generate the table elements one by one.

(3) Check whether the appointment time period is repeated. The time period stored in the database is stored in the form of a string. In fact, the string can be directly compared to determine whether the interval is repeated. The idea is to first retrieve the reserved time period for the venue. Then compare them one by one, if all pass, there is no conflict. The idea of ​​detection is shown in the figure below:

image

It is only necessary to judge whether the new appointment is on the left or right side of the reserved time period. The specific code is as follows:

for (int i = 0; i < dtBookInfo.Rows.Count; i++)
{
    //大于已预约右边,小于已预约左边
    notOverlap &= ( (string.Compare(bookSt, dtBookInfo.Rows[i][1].ToString().Trim(), true) > 0) ||
                    (string.Compare(bookEt, dtBookInfo.Rows[i][0].ToString().Trim(), true) < 0) );
}
if (!notOverlap)
{
    context.Response.Write("该时间段已经有别人预约啦,请重新选择!");
}

4. Database structure

In the design of the database, three database tables are used for system data storage. They are:

  • RoomInfo: Record venue information, such as venue type, capacity, availability, etc.
  • BookInfo: Record scheduled information, such as scheduled users, scheduled venues, scheduled time periods, etc.
  • WebUser: Record system user information, such as user name, MD5 cipher text of password, mobile phone number, etc.

The structure of the RoomInfo table is as follows:

Column nametype of dataDescriptionInstance
RoomNumbernchar(10)Venue numberEast Kowloon A101
RoomTypenchar(10)Venue typeLecture hall
RoomPeoplenchar(10)The number of people that the venue can hold100
RoomStatusnchar(10)Venue statusidle
Remarksnchar(10)RemarksBeing renovated

The structure of the BookInfo table is as follows:

Column nametype of dataDescriptionInstance
IDintReservation number1
CustomerNamevarchar(255)usernameZhang San
MyRemarknvarchar(50)RemarksBook a classroom opening meeting
BookDatenchar(10)Scheduled date2021-06-01
BookStnchar(50)Scheduled start time09:30
BookEtnchar(50)Scheduled end time11:20
BookDurationfloatScheduled time2.5
RoomNumbernchar(10)Reserved venue numberEast Kowloon A101

The structure of the WebUser table is as follows:

Column nametype of dataDescriptionInstance
usernamevarchar(255)usernameZhang San
passwordvarchar(255)Password MD5202...
telephonevarchar(50)phone17798253366

The ER diagram of the database is as follows:

image

5. The main code of the program and its description

The project structure is as follows, css, js and other files are put into the corresponding folder. The front end is divided into a login registration page (login.aspx) and a reservation page (indextem.aspx). Some helper classes (SqlHelper.cs, etc.) are used:

image

5.1 Front end

In the front-end development, the JavaScript part uses the json3 and jQuery libraries, which need to be introduced at the beginning of the aspx file:

<script src="https://cdn.bootcss.com/json3/3.3.2/json3.js"></script>
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>

The front-end interface uses a small and stylish CSS library Picnic CSS (https://picnicss.com/) to beautify, and customize some CSS styles, you need to introduce related files in the head:

<link rel="stylesheet" href="css/templatemo-style.css" />
<link rel="stylesheet" href="css/picnic.css" />

登录页面

image

The login page needs to be redirected after success, so the information submitted by the form is used:

<div id="section1" class="section-w3ls">
    <input type="radio" name="sections" id="option1" checked>
    <label for="option1" class="icon-left-w3pvt"><span class="fa fa-user-circle" aria-hidden="true"></span>登录</label>
    <article>
        <form runat="server">
            <h3 class="legend">账号登录</h3>
            <div class="input">
                <span class="fa fa-user-o" aria-hidden="true"></span>
                <input type="text" placeholder="用户名" name="inputEmail" required />
            </div>
            <div class="input">
                <span class="fa fa-key" aria-hidden="true"></span>
                <input type="password" placeholder="密码" name="inputPassword" required />
            </div>
            <asp:Button ID="Button1" class="btn submit" runat="server" Text="登陆" OnClick="Button1_Click" />
            <a href="#" class="bottom-text-w3ls">忘记密码?</a>
        </form>
    </article>
</div>

Registered users perform user name detection:

image


image

预约页面

The reservation page needs to display more information, as shown in the figure below:

image

You can write elements in HTML pages, and then use js to dynamically generate them, for example:

<table class="primary" id="roomInfo" style="width: 100%"></table>

document.getElementById("roomInfo").innerHTML = creatRoomTable(result);

You can also use the C# script to generate directly in the aspx file:

<%
System.Data.DataSet ds2 = MyDBUtils.DBHelper.ExecuteQuery("select BookInfo.ID, BookInfo.RoomNumber, RoomType, RoomPeople, MyRemark,BookSt, " +
    "BookEt, BookDuration from BookInfo join RoomInfo on " +
    "BookInfo.RoomNumber = RoomInfo.RoomNumber where " +
    "BookDate > '" + DateTime.Now.ToString("yyyy-MM-dd") + "' and CustomerName='" + Request.Cookies["login_name"].Value + "'");
for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
{
    Context.Response.Write("<tr>");

    for (int j = 1; j < 8; j++)
    {
        Context.Response.Write("<td>");
        Context.Response.Write(ds2.Tables[0].Rows[i][j].ToString());
        Context.Response.Write("</td>");
    }
    Context.Response.Write("<td>");
    Context.Response.Write("<label><input type='checkbox' name='checkbokRoom' value='" + ds2.Tables[0].Rows[i][0].ToString()+"-"+ ds2.Tables[0].Rows[i][1].ToString() + "' /><span class='checkable'>退订</span></label>");
    Context.Response.Write("</td>");
    Context.Response.Write("</tr>");
}
%>

The radio button in the table needs to be bound to the click event. This part of the code gets the time period reserved by the selected venue and displays it in the box below the table. It is a partial update for AJAX. When the selected venue is changed (Change of radio button), the reservation time period of the venue will also be updated below:

function getRoomTimeSpan() {
  var roomNumber = getSelectedRadioValue();
  //发送请求获预约的时间段
  $.ajax({
      type: 'get',
      url: 'RoomBookHandler.ashx',
      async: true,
      data: {
          action: 'getBookTime',
          roomNo: roomNumber
      },
      success: function (result) {
          var dataList = JSON.parse(result);
          var footerStr = '<footer id="bookTimeSpan" >';
          for (var ind in dataList) {
              footerStr += '<span class="label warning" style="font-size: 110%">';
              footerStr += dataList[ind].BookSt.toString().trim().substring(0, 5);
              footerStr += ' - ';
              footerStr += dataList[ind].BookEt.toString().trim().substring(0, 5);
              footerStr += '</span >';
          }
          footerStr += '</footer >';
          document.getElementById("bookTimeSpan").innerHTML = footerStr;
      },
      error: function () {
          alert('获取数据失败!');
      }
  });
}

The time period selection uses a time selection control, and the effect is as follows:

image

When booking, obtain a series of data input by the user, and then use AJAX to send it to the background for processing:

function bookRoom() {
    var bookT = document.getElementById("timeArrange").value;
    if (bookT === "") {
        alert("必须选择要借用的时间范围!");
        return false;
    }
    var myR = document.getElementById("myRemarks").value;
    var roomNumber = getSelectedRadioValue();
    if (roomNumber === "") {
        alert("必须选择要借用的教室!");
        return false;
    }
    //要发送的数据,教室号,预定开始时间-结束时间,我的备注

    $.ajax({
        type: 'post',
        url: 'RoomBookHandler.ashx',
        async: true,
        data: {
            action: 'bookRoom',
            roomNo: roomNumber,
            bookTime: bookT,
            myRemark: myR
        },
        success: function (result) {
            alert(result);
            getRoomTimeSpan();
            updateBookedTable();
        },
        error: function () {
            alert('请求失败!');
        }
    });
}

Note that if the user input is illegal, for example, the time period is not selected, the classroom is not selected, or the time period conflicts, the reservation cannot be completed effectively.

Successfully scheduled to show the reserved classroom:

image

The form creation code is similar to the form creation code displayed in the venue. If you cancel an appointment, you need to send the cancelled booking number (the booking number is bound to the value of the checkbox) to the background to delete the record:

function cancelBook() {
    var checkList = [];
    var timeSpanUpList = [];
    var checkbokContext = document.getElementsByName("checkbokRoom");
    for (i = 0; i < checkbokContext.length; ++i) {
        if (checkbokContext[i].checked) {
            var dataStr = checkbokContext[i].value.split('-');
            checkList.push(dataStr[0]);
            timeSpanUpList.push(dataStr[1]);
        }
    }
    if (checkList.length == 0) {
        alert("请选择您需要取消预约的教室!");
        return false;
    }
    var cancelListStr = checkList.join(','); //转成1,3,4这种形式,后台再解析
    $.ajax({
        type: 'post',
        url: 'RoomBookHandler.ashx',
        async: true,
        data: {
            action: 'cancelBook',
            cancel: cancelListStr
        },
        success: function (result) {
            alert(result);
            //刷新本表
            updateBookedTable();
            //刷新foot
            if (timeSpanUpList.indexOf(getSelectedRadioValue()) != -1) {
                getRoomTimeSpan();
            }
        },
        error: function () {
            alert('连接失败!');
        }
    });
}

After success, update the table. However, it should be noted that, in addition, a small detail has been made. After a certain period of time is cancelled, if this classroom happens to be selected on the venue display page, then the following appointment period will also be updated simultaneously, using the same AJAX technology .

image
success: function (result) {
    alert(result);
    //刷新本表
    updateBookedTable();
    //刷新foot
    if (timeSpanUpList.indexOf(getSelectedRadioValue()) != -1) {
        getRoomTimeSpan();
    }
},

The historical appointment form is generated in the form of an embedded script in aspx:

image
<table class="primary"  style="width: 100%">
    <tr>
        <th>教室号</th>
        <th>教室类型</th>
        <th>容纳人数</th>
        <th>我的备注</th>
        <th>日期</th>
        <th>开始时间</th>
        <th>结束时间</th>
        <th>借用时长(小时)</th>
    </tr>
    <tbody>
    <%
        System.Data.DataSet ds3 = MyDBUtils.DBHelper.ExecuteQuery("select BookInfo.RoomNumber, RoomType, RoomPeople, MyRemark,BookDate,BookSt, " +
            "BookEt, BookDuration from BookInfo join RoomInfo on " +
            "BookInfo.RoomNumber = RoomInfo.RoomNumber " +
            "where BookDate < '" + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") +"' and CustomerName='" + Request.Cookies["login_name"].Value + "'");
        for (int i = 0; i < ds3.Tables[0].Rows.Count; i++)
        {
            Context.Response.Write("<tr>");

            for (int j = 0; j < 8; j++)
            {
                Context.Response.Write("<td>");
                Context.Response.Write(ds3.Tables[0].Rows[i][j].ToString());
                Context.Response.Write("</td>");
            }
            Context.Response.Write("</tr>");
        }
    %>
    </tbody>
</table>

When searching, the system will automatically retrieve the user's reservation information before today from the reservation table and display it.

5.2 Background

登录页面

To verify the background code, you need to first convert the password into MD5 ciphertext, and then perform string matching:

string username = Request.Params["inputEmail"].ToString();
string password = MD5Helper.ToMD5(Request.Params["inputPassword"].ToString());

if (DBHelper.ExecuteQuery("select * from WebUser where username='" + username + "' and password='" + password + "'").Tables[0].Rows.Count > 0)
{
    //放一个Cookie来指示是哪名用户登陆了
    HttpCookie cookie = new HttpCookie("login_name", username);
    Response.Cookies.Add(cookie);
    Response.Redirect("indextem.aspx");
}
else
{
    Response.Write("<script language=javascript>alert('用户名或密码错误');</script>");
}

The front-end of the registration page is similar, the back-end code is mainly database insertion, and it is necessary to determine whether the user name is duplicated, as follows:

var name = Request["regName"];
if (!string.IsNullOrEmpty(name))
{
    if (DBHelper.ExecuteQuery("select * from WebUser where username='" + name + "'").Tables[0].Rows.Count > 0)
    {
        //检测该用户名是否注册
        Response.Write("<script language=javascript>alert('注册失败,用户名已被使用!');</script>");
    }
    else
    {
        var passwd = Request["regPassword"];
        passwd = MD5Helper.ToMD5(passwd.ToString());
        var telephone = Request["regTelephone"];
        var sql = "INSERT INTO WebUser(username,password,telephone) VALUES ('{0}','{1}','{2}')";
        sql = string.Format(sql, name, passwd, telephone);
        if (SqlHelper.ExecuteSql(sql) > 0)
        {
            var str = "注册成功,您的用户名:" + name + " ,现在去登录试试吧~";
            Response.Write("<script language=javascript>alert('" + str + "');</script>");
        }
        else
        {
            Response.Write("<script language=javascript>alert('注册失败,数据库出错!');</script>");
        }
    }
}

预约页面

Scheduled background processing code, the background needs to do scheduled conflict detection:

string[] bookTime = context.Request["bookTime"].Split('-');
string bookSt = bookTime[0].Trim();
string bookEt = bookTime[1].Trim();
string bookDate = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
string roomNumber = context.Request["roomNo"];
//预定时间区间判断
var bookInfoSql = "select BookSt, BookSt from BookInfo where " +
    "BookDate='{0}' And RoomNumber='{1}'";
bookInfoSql = string.Format(bookInfoSql, bookDate, roomNumber);
DataTable dtBookInfo = SqlHelper.getDataTable(bookInfoSql);
Boolean notOverlap = true;
for (int i = 0; i < dtBookInfo.Rows.Count; i++)
{
    //大于已预约右边,小于已预约左边
    notOverlap &= ( (string.Compare(bookSt, dtBookInfo.Rows[i][1].ToString().Trim(), true) > 0) ||
                    (string.Compare(bookEt, dtBookInfo.Rows[i][0].ToString().Trim(), true) < 0) );
}
if (!notOverlap)
{
    context.Response.Write("该时间段已经有别人预约啦,请重新选择!");
}
else
{
    string customerName = context.Request.Cookies["login_name"].Value;
    string myRemark = context.Request["myRemark"];
    if (string.IsNullOrEmpty(myRemark))
    {
        myRemark = "无";
    }

    DateTime dt1 = DateTime.Parse(bookDate + " " + bookSt);
    DateTime dt2 = DateTime.Parse(bookDate + " " + bookEt);
    TimeSpan ts = dt2.Subtract(dt1);
    double bookDurationHours = Math.Round(ts.TotalHours, 2);

    DataTable dtLastID = SqlHelper.getDataTable("select top 1 ID from BookInfo order by ID DESC");
    string insIdStr = dtLastID.Rows[0][0].ToString();
    int insId = int.Parse(insIdStr) + 1;
    //插入到数据库中去
    var bookSql = "INSERT INTO BookInfo(ID, CustomerName,MyRemark,BookDate,BookSt,BookDuration,RoomNumber,BookEt) " +
        "VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
    bookSql = string.Format(bookSql, insId, customerName, myRemark, bookDate, bookSt, bookDurationHours,
        roomNumber, bookEt);
    if (SqlHelper.ExecuteSql(bookSql) > 0)
    {
        context.Response.Write("预定成功!");
    }
    else
    {
        context.Response.Write("后台数据插入出错!");
    }
}

Get the background processing code for the reserved time period:

string roomNum = context.Request["roomNo"];
var sqlBookSp = "select BookSt, BookEt from BookInfo " +
    "where BookDate = '" + DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") + "' and RoomNumber = '{0}' order by BookSt";
sqlBookSp = string.Format(sqlBookSp, roomNum);
DataTable dtTimeSp = SqlHelper.getDataTable(sqlBookSp);
string sJson2 = JsonConvert.SerializeObject(dtTimeSp);
context.Response.Write(sJson2);
break;

Cancel the appointment code:

//删除预订的数据
string[] delListStr = context.Request["cancel"].Split(',');
int totalCancel = 0;
foreach(var delRoom in delListStr)
{
  var delSql = "delete from BookInfo where ID = " + delRoom;
  if (SqlHelper.ExecuteSql(delSql) > 0)
  {
      
      totalCancel++;
  }
}
context.Response.Write("取消预订完成,共取消 " + totalCancel.ToString() + " 间教室!");

6. Running effect diagram

log in

image

registered:

image


image

Reservation (you can manually refresh the classroom information):

image


image


image


image

Time Conflict:

image

My reservation:

image

Cancel reservation (support multiple cancellation together):

image

After cancellation, automatically refresh the time period reserved for the venue:

image

History reservation && my information display:

image

7. Summary

The release of venue information should also require a management terminal, that is, the administrator can edit the venue information and then publish it. However, due to limited personal capabilities, the management terminal has not been designed. Or it is also possible to directly connect the system to the hub to retrieve free classroom spaces and so on for borrowing. However, school support is needed.

appendix

The database script [attached to the program directory] may need to modify the file storage directory when running, the default is the Database folder under the D drive:

image

Use VS2019 and above to open the project file .sln:

image

Just run login.aspx:

image