Experiment 6

Title Requirements

Based on ASP Net core carries out web page programming, reads students' reading data from the database and displays it on the web page. It is required to display the list first, and then click the specific item to display. Certain data summary can be carried out.

Environment settings

1. Operating system: Windows 10 x64
2. SDK: .NET Framework 4.7.2
3. IDE: Visual Studio 2019

Topic meaning analysis

As the saying goes, the shorter the topic, the more difficult it is. As in the past, let's disassemble the requirements. The specific requirements are as follows:

① Based on ASP Net core for web development
② Read data from database
③ Show data to web pages
④ First display the list, and then click the specific item to display
⑤ Certain data summary can be carried out

We can see that ① and ② are our biggest core functions, which are the contents of the front-end and back-end. Connecting ①, ② and ③ in series is the main body of the experiment, and ④ and ⑤ are additional requirements.
For front-end content ①, we need to master ASP In the first mock exam of Net Core, we have to grasp the basic structure and use of MVC mode (model view controller mode) and finish the operation of database and display of front page in this mode.
For the back-end content ②, we need to master the basic organization of the database and the basic use of SQL language. This experiment uses the open source MySQL database to establish the database and table and add data through mysql. The built data table is directly read by the web page. The specific mode will be expanded below.
From ASP Based on the nature of net core, this experiment uses the development mode without separation of front and rear ends, that is, through ASP Net core reads the database on site and displays it in the dynamic web page. At present, the popular and more efficient development mode is the development mode of separating the front and back ends, that is, the front and back ends only use a file such as json for interaction: the front end sends a request to the back end, obtains the file, and then parses it. Limited by space, this article will not repeat.

Back end · database

Database overview

Database, as the name suggests, is the place where data is stored. From the precise definition, database refers to a data set that is stored together in a certain way, can be shared by multiple users, has as little redundancy as possible, and is independent of the application program, which is used to store structured data. There are many kinds of data models in data organization. At present, the main data model is relational data model. The database based on relational model is relational database, which is also the most widely used database at present. Various popular databases, such as Oracle, Microsoft SQL Server, Access and MySQL, are relational databases. In short, relational model refers to two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships.

SQL

After having a database, you naturally think of how to operate it. Structured Query Language, or SQL, is a query language of relational database, which originated from IBM and later adopted as an international standard. It is easy to operate and supported by many databases, including the MySQL database used in our experiment. SQL language includes query, manipulation, definition and control, which are realized by command verbs. We are more familiar with CRUD (Create Retrieve Update Delete), which is also included in these commands. We will use SQL language to build and read our database in detail next.

SQL in C #

It is not so simple to use SQL to operate the database in c#. In other words, we can't directly use SQL in the C # console like entering instructions in the MySQL command window. Instead, we must connect the database through the program, use the string to represent the commands, call them through the C # statement, and then exit. Different databases use different prefix statements. For example, the corresponding statement of MySQL database is MySQL XXX, and mysql.com should be introduced Data. MySQL client package; The statements used by Microsoft SQL Service are OleDbxxx and so on. We will see how to use it in detail in the following process.

Front end · ASP NET Core

Front end basics · HTML and JavaScript

In order to display the data in the browser, we need to use HTML and JavaScript to interact with users.
Hypertext markup language (HTML) is a markup language, which uses markup symbols to mark various parts of the web page to be displayed. The web page file itself is a text file. By adding markers to the text file, you can tell the browser how to display its contents. The browser reads the web page files in order, and then interprets and displays the contents of its tags according to the tags. Simply put, we complete different functions by entering different tags. These tags are different from XML and have been specified. We use the combination of these tags to control the appearance of web pages like instructions. We will see how they are used next.
The HTML mentioned above can only complete the display part of the web page, but most of the web pages we can see can interact with users in a variety of ways. What about this? In order to complete these interactions, we need to use JavaScript, a scripting language. The characteristic of scripting language is "on call", that is, interpreted or just in time compiled language. It is different from a large number of back-end languages. It is compiled and then executed, but executed sentence by sentence in the process of web page running. Such features can help us realize various functions in the operation of web pages. In the actual development, JS and HTML combine to complete the basic functions of display and human-computer interaction.
In addition to HTML and JavaScript, * * CSS (Cascading Style Sheets) * * is often used in front-end development to complete a variety of gorgeous dynamic effects. The function of this experiment can't use it. Interested readers can find information by themselves.

ASP.NET Core overview

Active Server Pages, or ASP, is a server-side script environment developed by Microsoft. It can be used to create dynamic interactive web pages and build powerful web applications. When the server receives a request for an ASP file, it processes the server-side script code contained in the HTML web page file used to build and send to the browser. And NET platform, which is developed by Microsoft NET Core is NET Framework is the first application development framework developed by Microsoft with cross platform (Windows, MAC, OSX, Linux) capabilities. The combination of the two is ASP NET Core, which is based on NET Core is a web development framework for ASP NET. Interested readers can find and understand their history by themselves, which will not be repeated in this article.

MVC (model view controller)

MVC is a mode of building Web applications. In short, it is how this Web page or Web application is built and operated. Its application covers almost all Web frameworks, not just ASP Net core. Even mobile applications on iOS and Android are a variant of MVC. Taken apart, the three components of MVC have the following functions:

1. Model: responsible for the part of the application used to process the application data logic. Usually, the model object is responsible for accessing data in the database. In ASP Net core, the model can be divided into two types: the model named xxxItem is responsible for the recording part, which records the entries saved in the database; The model named after xxxViewModel is responsible for combining with the view and sending it to the browser for users to view.
2. View: the part of an application that processes the display of data. Typically, views are created from model data. Simply put, it is to present the saved data to users. In ASP In net core, the view is written in razor language and stored in the suffix cshtml in a special file. Razor language will be introduced below.
3. Controller: the part of an application that handles user interaction. Usually, the controller is responsible for reading data from the view, controlling user input, and sending data to the model. In other words, the controller is the "window" responsible for human-computer interaction. It processes the input of the user (terminal) and sends it to the corresponding code for processing.

Razor · a combination of HTML and C #

Razor is mentioned above. In fact, it is a hybrid version of HTML and C# (also VB). Razor uses the "@" symbol to distinguish HTML tags from C# code. Variables beginning with @ or marked with @ at the beginning and end are recognized as C# codes. HTML tags can also be nested in this C# code, such as in form elements (< td > < td >). Isn't it amazing? It can replace some functions of JS code to a certain extent. We can see the practical use of this thing below. It should be noted that this language is specially made by Microsoft for the development of ASP series web pages. It can not be used for pure front-end development.

Where does the data come from?

Read data from the database, of course. The requirements of this experiment are relatively simple, so we can directly hand over the task of reading the database to the Controller, that is, directly read the contents of the database in the Controller, and then save it into the Model (xxxItem). In a more professional, hierarchical and structured ASP Net core development, we will transform MVC into different levels to achieve the function of separating display and back-end communication. At this time, a presentation layer composed of Controller and view will appear to deal with user interaction; A service layer containing business logic and database code will be entrusted with the task of communicating with the database. Readers can find relevant information and understand by themselves. This experiment only exchanges the simplest development work with you.

Complete code

Back end code (Experiment 6, question 2, back end \ Program.cs): establish a database and write data

using System;
using MySql.Data.MySqlClient;

namespace Experiment 6_Second question_back-end
{
    class Program
    {
        static void Main(string[] args)
        {
			//Prepare the data to be written (you can also input it by yourself through the console)
            string[] number = { "2938", "0001", "0002", "0003", "2938", "6666", "0001", "0003", "0003", "2938" };
            string[] student = { "Zhang chentao", "Yang Xiaogang", "Li Xiaofang", "Zhao Xiaoming", "Zhang chentao", "Lai Yongxuan (teacher, happy New Year!)", "Yang Xiaogang", "Zhao Xiaoming", "Zhao Xiaoming", "Zhang chentao" };
            string[] sex = { "male", "male", "female", "female", "male", "male", "male", "female", "female", "male", };
            string[] birth = { "2002.2.20", "2002.12.20", "2002.7.16", "2002.1.5", "2002.2.20", "~", "2002.12.20", "2002.1.5", "2002.1.5", "2002.2.20" };
            string[] book = { "Principle and application of database system MySql Application Tutorial", "Algorithm and data structure", "Probability theory and mathematical statistics", "discrete mathematics ", "C#Programming tutorial", "Taxi Demand Prediction with LSTM-based Combination Model.", "Assembly language programming", "C#Programming course "," computer network and Internet "," Unity3D game development practice ",};

			//Start database operation
            string connetStr = "server=localhost;port=3306;user=root;password=********; database=*******;";
            // server=127.0.0.1/localhost stands for the local machine. The port number port is 3306 by default. It can not be written
            MySqlConnection connect = new MySqlConnection(connetStr);
            try//CRUD
            {
                connect.Open();//Open channel and establish connection

                string cmd = "";
                MySqlCommand mycmd = new MySqlCommand(cmd, connect);

                //Empty (used during test)
                cmd = "delete from studentdata";
                mycmd.CommandText = cmd;
                mycmd.ExecuteNonQuery();

                //Build table
                cmd = @"create table if not exists studentdata 
                        (
                            number char(4),
                            student char(20),
                            sex char(3),
                            birth char(10),
                            book char(120)
                        )";
                mycmd.CommandText = cmd;
                mycmd.ExecuteNonQuery();//Use this instruction for non query statements

                //insert data
                for (int i = 0; i < number.Length; i++)
                {
                    //Write data in column order
                    cmd = string.Format("insert into studentdata(number, student, sex, birth, book) values(\"{0}\", \"{1}\", \"{2}\", \"{3}\", \"{4}\")", number[i], student[i], sex[i], birth[i], book[i]);
                    mycmd.CommandText = cmd;
                    mycmd.ExecuteNonQuery();
                }

                //Query data
                cmd = "select * from studentdata";
                mycmd.CommandText = cmd;
                MySqlDataReader reader = mycmd.ExecuteReader();
                while (reader.Read())//It's a bit like an iterator, reading until the last line returns false
                {
                    for (int i = 0; i < 5; i++)
                        Console.Write(reader[i].ToString() + " ");
                    Console.WriteLine();
                }

            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                connect.Close();
            }

            Console.WriteLine("Finish...");
            Console.ReadKey();
        }
    }
}

Front end code (Experiment 6 Topic 2 front end \ Controllers\HomeController.cs): the page at the time of initial creation

using System.Diagnostics;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using test01.Models;

namespace test01.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;

        public HomeController(ILogger<HomeController> logger)
        {
            _logger = logger;
        }

        public IActionResult Index()
        {
            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

Front end code (front end \ Controllers\TestController.cs in the second question of Experiment 6): a self written controller used to read the database and store data

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using Microsoft.AspNetCore.Mvc;
using test01.Models;

namespace test01.Controllers
{
    public class TestController : Controller
    {
        public IActionResult Index()
        {
            var contents = new List<TestItem>();

            string connetStr = "server=localhost;port=3306;user=root;password=********; database=*******;";
            //server=127.0.0.1/localhost stands for the local machine. The port number port is 3306 by default. It can not be written
            //This is the same as the beginning of the back-end code
            MySqlConnection conn = new MySqlConnection(connetStr);
            try
            {
                conn.Open();//Open channel and establish connection

                //CRUD
                string cmd = "";
                MySqlCommand mycmd = new MySqlCommand(cmd, conn);

                //Query data
                cmd = "select * from studentdata";
                mycmd.CommandText = cmd;
                MySqlDataReader reader = mycmd.ExecuteReader();
                while (reader.Read())
                {
                    //The back-end code is transformed, and the records are stored in the array and finally in the model; Note the creation of new objects
                    contents.Add(new TestItem { number = reader[0].ToString(), student = reader[1].ToString(), sex = reader[2].ToString(), birth = reader[3].ToString(), book = reader[4].ToString() });
                }
            }
            catch (MySqlException ex)
            {
                //I haven't thought of a good error information processing method yet
                Console.WriteLine(ex);
            }
            finally
            {
                //Close connection
                conn.Close();
            }

            //Create a new object, store it in the, and return it to the model
            return View(new TestViewModel { Contents = contents });
        }
    }
}

Front end code (front end \ Models\TestItem.cs in the second question of Experiment 6): provides an interface for data access

namespace test01.Models
{
    // Content entity
    public class TestItem
    {
        public string number { get; set; }

        public string student { get; set; }
        
        public string sex { get; set; }

        public string birth { get; set; }

        public string book { get; set; }

    }
}

Front end code (Experiment 6 Topic 2 front end \ Models\TestViewModel.cs): combined with the view, it is provided to the user

using System.Collections.Generic;

namespace test01.Models
{
    public class TestViewModel
    {
        // Content list
        public List<TestItem> Contents { get; set; }
    }
}

Front end code (Experiment 6 Topic 2 front end \ Views\Home\Index.cshtml): the page at the time of initial creation

@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
</div>

Front end code (Experiment 6 Topic 2 front end \ Views\Home\Privacy.cshtml): the page at the time of initial creation

@{
    ViewData["Title"] = "Privacy Policy";
}
<h1>@ViewData["Title"]</h1>

<p>Use this page to detail your site's privacy policy.</p>

Front end code (front end of the second question of Experiment 6 \ Views\Test\Index.cshtml): a self written page that contains the screening and display of data in the model

@model TestViewModel
@{
    ViewData["Title"] = "Content list";
}

<div class="panel panel-default todo-panel">

    <div class="panel-heading">@ViewData["Title"]</div>

    <!--Making tables-->
    <table id = "student" border="1" cellspacing="0">
        <thead>
            <tr>
                <td>Student number
                    <!--onchang Function to filter and display data when switching options-->
                    <select id="selectNumber" οnchange="ChooseSelect()">
                        <option>*</option>
                    </select>
                </td>

                <td>full name
                    <select id="selectName" οnchange="ChooseSelect()">
                        <option>*</option>
                    </select>
                </td>

                <td>Gender
                    <select id="selectSex" οnchange="ChooseSelect()">
                        <option>*</option>
                        <option>male</option>
                        <option>female</option>
                    </select>
                </td>

                <td>birthday
                </td>

                <td>book
                </td>
            </tr>
        </thead>

        @foreach (var item in Model.Contents)//The contents in the table are basically immovable and need to be modified through subsequent functions
        {
            <tr>
                <!--Razor Direct use C#Variable -- >
                <td>@item.number</td>
                <td>@item.student</td>
                <td>@item.sex</td>
                <td>@item.birth</td>
                <td>@item.book</td>
            </tr>
        }
    </table>

    <!--JS script-->
    <script type="text/javascript">
        function DeleteForm() {
            var table = document.getElementById("student")
            var len = table.rows.length
            for (var i = len - 1; i > 0; i--)
                table.deleteRow(i)
            return
        }//Delete form for presentation of new form

        function ChooseCondition(number, name, sex) {
            var filteredArray = new Array()
            for (var i = 0; i < studentData.length; i++) {
                if ((studentData[i].number == number || number == "*") &&
                    (studentData[i].student == name || name == "*") &&
                    (studentData[i].sex == sex || sex == "*"))
                //"*" indicates a wildcard, so it is written into the judgment condition with the logic of or
                    filteredArray.push(studentData[i])
            }
            return filteredArray
        }//Filter the qualified records from the global array and return them to the display function for display

        function CreateForm(res) {
            var table = document.getElementById("student")
            for (var i = 0; i < res.length; i++) {
                var x = table.insertRow(i+1)
                //Add table element
                x.insertCell(0).innerText = res[i].number
                x.insertCell(1).innerText = res[i].student
                x.insertCell(2).innerText = res[i].sex
                x.insertCell(3).innerText = res[i].birth
                x.insertCell(4).innerText = res[i].book
            }
            return
        }//Display function

        function ChooseSelect()
        {
            //Filter, get string
            var onumber = document.getElementById("selectNumber")
            var number = onumber.options[onumber.selectedIndex].value

            var oname = document.getElementById("selectName")
            var name = oname.options[oname.selectedIndex].value

            var osex = document.getElementById("selectSex")
            var sex = osex.options[osex.selectedIndex].value

            var res = ChooseCondition(number, name, sex)

            //Empty table
            DeleteForm()

            //Display the filtered list
            CreateForm(res)

            return
        }//The total function to be called is filtered first, then deleted, and then displayed

        
        var studentData = new Array()//Global array
        var tableElement = document.getElementById("student")
        var tableLen = tableElement.rows.length//Get the total number of rows

        var sselectNumber = document.getElementById("selectNumber")
        var qNumber = new Array()
        var sselectName = document.getElementById("selectName")
        var qName = new Array()

        for (var i = 1; i < tableLen; i++) {
            var oneStudent = new Object()//Create a new object

            oneStudent.number = tableElement.rows[i].cells[0].innerText
			//Create a new option object to insert into the select. You must insert the drop-down menu with a new object every time, otherwise an error will be caused (it will only appear in one place)
            var opNumber = document.createElement("option")
            opNumber.innerText = oneStudent.number
            if (qNumber.indexOf(opNumber.innerText) < 0) { //No one was found
                qNumber.push(opNumber.innerText)//Insert into de duplication table
                sselectNumber.add(opNumber)//insert
            }

			//The principle is the same as above
            oneStudent.student = tableElement.rows[i].cells[1].innerText
            var opName = document.createElement("option")
            opName.innerText = oneStudent.student
            if (qName.indexOf(opName.innerText) < 0) {
                qName.push(opName.innerText)
                sselectName.add(opName)
            }
			
            oneStudent.sex = tableElement.rows[i].cells[2].innerText
            oneStudent.birth = tableElement.rows[i].cells[3].innerText
            oneStudent.book = tableElement.rows[i].cells[4].innerText
            
            studentData.push(oneStudent)//Insert into total global array
        }
    </script>

</div>

Code fragment analysis

Back end · database establishment

string connetStr = "server=localhost;port=3306;user=root;password=********; database=*******;";
// server=127.0.0.1/localhost stands for the local machine. The port number port is 3306 by default. It can not be written
MySqlConnection connect = new MySqlConnection(connetStr);

Since the database cannot be operated directly as in the console, we can only connect and operate the database through C# instructions in the form of string provided by the package. The following code is the same.

cmd = @"create table if not exists studentdata 
                        (
                            number char(4),
                            student char(20),
                            sex char(3),
                            birth char(10),
                            book char(120)
                        )";
mycmd.CommandText = cmd;
mycmd.ExecuteNonQuery();//Use this instruction for non query statements

For non query statements, the package provides the instruction ExecuteNonQuery() to operate. The SQL instruction of the operation is the string written in CommandText. Query statement is the most commonly used statement in database operation, and the query methods are rich and colorful, such as the following code:

//Query data
cmd = "select * from studentdata";
mycmd.CommandText = cmd;
MySqlDataReader reader = mycmd.ExecuteReader();
while (reader.Read())
{
    for (int i = 0; i < 5; i++)
        Console.Write(reader[i].ToString() + " ");
    Console.WriteLine();
}

MySQL provides two query statements in the package of C#, one of which is the ExecuteReader() provided above. The function of this statement is to create a reader object, which is like an iterator. It reads the data obtained by SQL command line by line and returns it in the form of array, so we can obtain the content of each field by subscript. Of course, the above code is only used when debugging the back end. It needs to be slightly modified when reading data at the front end, as we will see later.

front end

TestController. In CS

public IActionResult Index()
{
    var contents = new List<TestItem>();

    string connetStr = "server=localhost;port=3306;user=root;password=********; database=*******;";
    //server=127.0.0.1/localhost stands for the local machine. The port number port is 3306 by default. It can not be written
    //This is the same as the beginning of the back-end code
    MySqlConnection conn = new MySqlConnection(connetStr);
    try
    {
        conn.Open();//Open channel and establish connection

        //CRUD
        string cmd = "";
        MySqlCommand mycmd = new MySqlCommand(cmd, conn);

        //Query data
        cmd = "select * from studentdata";
        mycmd.CommandText = cmd;
        MySqlDataReader reader = mycmd.ExecuteReader();
        while (reader.Read())
        {
            //The back-end code is transformed, and the records are stored in the array and finally in the model; Note the creation of new objects
            contents.Add(new TestItem { number = reader[0].ToString(), student = reader[1].ToString(), sex = reader[2].ToString(), birth = reader[3].ToString(), book = reader[4].ToString() });
        }
    }
    catch (MySqlException ex)
    {
        //I haven't thought of a good error information processing method yet
        Console.WriteLine(ex);
    }
    finally
    {
        //Close connection
        conn.Close();
    }

    //Create a new object, store it in the, and return it to the model
    return View(new TestViewModel { Contents = contents });
}

This code is to slightly modify the content of the front end to read the database. The difference is that it first puts the data into a temporary object array, and then passes the array into the model for storage. In the actual test, return view (New testviewmodel {contents = contents}); This is necessary to create a new object array, otherwise it will lead to problems, and the cause of the problem is still unknown. The Index function is a function that can be read directly when the page is loaded without adding subdirectories.

Test\Index. In cshtml

<thead>
    <tr>
    	<td>Student number
    		<!--onchang Function to filter and display data when switching options-->
    		<select id="selectNumber" οnchange="ChooseSelect()">
    			<option>*</option>
    		</select>
    	</td>

    	<td>full name
    		<select id="selectName" οnchange="ChooseSelect()">
    			<option>*</option>
    		</select>
    	</td>

    	<td>Gender
            <!--Gender is fixed, so write it directly-->
    		<select id="selectSex" οnchange="ChooseSelect()">
    			<option>*</option>
    			<option>male</option>
    			<option>female</option>
    		</select>
    	</td>

    	<td>birthday
    	</td>

    	<td>book
    	</td>
    </tr>
</thead>

The above paragraph is pure HTML language, which is used to create the header, that is, row 0 of the table. It contains some select controls, that is, drop-down menus. Its element is option, that is, each option. The onchang function of the control is a function that will be triggered when the content of the control changes. The content of the function is written in the JavaScript script, which we will see next.
Here, the preliminary idea of data filtering, that is, requirement ④, has been seen: we will use JS script to filter the data like developing C# program, and then return it to the table for display. These data have been put into the buffer after being read, rather than calling the database on site.
At this time, we only need to add HTML to the table, which is not enough:

@foreach (var item in Model.Contents)//The contents in the table are basically immovable and need to be modified through subsequent functions
{
	<tr>
		<!--Razor Direct use C#Variable -- >
		<td>@item.number</td>
		<td>@item.student</td>
		<td>@item.sex</td>
		<td>@item.birth</td>
		<td>@item.book</td>
	</tr>
}

The syntax or variables preceded by the @ symbol are c#, while those with < > are HTML syntax. It can be seen that the variable of C # is embedded into the cell, and the cell is embedded into the foreach loop, which achieves the effect of dynamic table creation in disguise. Of course, the above functions can also be achieved through JavaScript, but it is a little troublesome to deal with JS and c# exported data, and Razor just fills the gap between them; Next, we will see the role of JS in pure HTML. Let's look down.

var studentData = new Array()//Global array
var tableElement = document.getElementById("student")
var tableLen = tableElement.rows.length//Get the total number of rows

var sselectNumber = document.getElementById("selectNumber")
var qNumber = new Array()
var sselectName = document.getElementById("selectName")
var qName = new Array()

for (var i = 1; i < tableLen; i++) {
    var oneStudent = new Object()//Create a new object

    oneStudent.number = tableElement.rows[i].cells[0].innerText
    //Create a new option object to insert into the select. You must insert the drop-down menu with a new object every time, otherwise an error will be caused (it will only appear in one place)
    var opNumber = document.createElement("option")
    opNumber.innerText = oneStudent.number
    if (qNumber.indexOf(opNumber.innerText) < 0) { //No one was found
        qNumber.push(opNumber.innerText)//Insert into de duplication table
        sselectNumber.add(opNumber)//insert
    }

    //The principle is the same as above
    oneStudent.student = tableElement.rows[i].cells[1].innerText
    var opName = document.createElement("option")
    opName.innerText = oneStudent.student
    if (qName.indexOf(opName.innerText) < 0) {
        qName.push(opName.innerText)
        sselectName.add(opName)
    }

    oneStudent.sex = tableElement.rows[i].cells[2].innerText
    oneStudent.birth = tableElement.rows[i].cells[3].innerText
    oneStudent.book = tableElement.rows[i].cells[4].innerText

    studentData.push(oneStudent)//Insert into total global array
}

JS script should be written in the < script > < script > tag. The order of the upper and lower codes in this paragraph is opposite to that written in the program, because logically, what should be realized first is to summarize all the data and then display it, and then click the drop-down list to filter. The former is run according to the script writing order when the web page is running, while the latter is realized by calling functions. A better way is to put the implementation of the function in the head of HTML, that is, the < head > < / head > tag.
Now let's analyze the role of this part of the script. In the previous code, the database contents have been put into the HTML table through Razor, but these contents do not enter the storage space of JS, so we need to read the data into a global array variable through the intermediary of HTML table, and then filter it. The data stored in the global array is a JS object, which is familiar to students who have contacted JSON(JavaScript Object Notation): a {} contains some key value pairs, in which the key is the attribute, and the value is the value corresponding to the attribute, which is just used to hold the student's field information. While finding the fields of each row from the table and saving them into the object (such as oneStudent.number = tableElement.rows[i].cells[0].innerText), we put these options into the drop-down box select. This includes the process of de duplication. In order to achieve the effect of de duplication, we open an auxiliary array for the fields to be filtered, such as var qNumber = new Array(), and insert the objects that have not been inserted into the select drop-down box into it and into the drop-down box at the same time. The indexOf function used in it actually plays the role of filtering. If it returns - 1, it means that the object is not in the array.
It is worth mentioning that JavaScript is a weakly typed language, so all variables are declared in var. This is mentioned because of the frequently used document Getelementbyid () is a very powerful function, which can grab different objects according to the Id value within the page range. This Id value is like the variable name in C# and can be used to identify different kinds of controls.
The next part is all about JS functions, which is also the final part of this experiment.

function DeleteForm() {
    var table = document.getElementById("student")
    var len = table.rows.length
    for (var i = len - 1; i > 0; i--)
        table.deleteRow(i)
    return
}//Delete form for presentation of new form

function ChooseCondition(number, name, sex) {
    var filteredArray = new Array()
    for (var i = 0; i < studentData.length; i++) {
        if ((studentData[i].number == number || number == "*") &&
            (studentData[i].student == name || name == "*") &&
            (studentData[i].sex == sex || sex == "*"))
            //"*" indicates a wildcard, so it is written into the judgment condition with the logic of or
            filteredArray.push(studentData[i])
    }
    return filteredArray
}//Filter the qualified records from the global array and return them to the display function for display

function CreateForm(res) {
    var table = document.getElementById("student")
    for (var i = 0; i < res.length; i++) {
        var x = table.insertRow(i+1)
        //Add table element
        x.insertCell(0).innerText = res[i].number
        x.insertCell(1).innerText = res[i].student
        x.insertCell(2).innerText = res[i].sex
        x.insertCell(3).innerText = res[i].birth
        x.insertCell(4).innerText = res[i].book
    }
    return
}//Display function

The above is the content of the three sub function functions. The first is to delete all the contents of the table. The reason why we can boldly delete the table here is that we have added all the contents to the global array according to objects. The principle of deletion is to obtain all rows of the table and delete them row by row. Here we should pay attention to two points: first, delete from the tail to prevent the change of cyclic variable subscript and line number from affecting the deletion order (imagine skipping deletion); Second, line 0 is the header. The real content starts from line 1. Pay attention to I > 0 when deleting.
The content of the second function is to get the content to be filtered from the outside and filter it, and return an array after filtering. The information of this decision is provided by the general function and comes from the selection of the select box. The extraction method can be seen in the following code. In the select drop-down box, there is a special symbol "*", which is a wildcard. Wildcard indicates that there is no filtering restriction on the content of the current field. So how does it behave in the judgment conditions? Obviously, it returns true for any condition passed in, and naturally thinks of using or conditions to implement it. Connecting these decision expressions with or logic with operators is the general decision condition, that is, the following parts of the code.

if((studentData[i].number == number || number == "*") &&
   (studentData[i].student == name || name == "*") &&
   (studentData[i].sex == sex || sex == "*"))

The third function creates a new table and inserts the returned filtered array into it. The insertion method is to insert row by row, and insert cell by cell in each row, that is, insert column by column. The functions of these table controls are relatively simple in form. I won't repeat them here. It's OK to master the use mode.
The next step is to assemble all these sub functions into a total function for calling:

function ChooseSelect()
{
    //Filter, get string
    var onumber = document.getElementById("selectNumber")
    var number = onumber.options[onumber.selectedIndex].value

    var oname = document.getElementById("selectName")
    var name = oname.options[oname.selectedIndex].value

    var osex = document.getElementById("selectSex")
    var sex = osex.options[osex.selectedIndex].value

    var res = ChooseCondition(number, name, sex)

    //Empty table
    DeleteForm()

    //Display the filtered list
    CreateForm(res)

    return
}//The total function to be called is filtered first, then deleted, and then displayed

In the previous two behavior examples, since select can only obtain the subscript of the selected item, we need to obtain the selected option from the options array through the subscript. Option is an object. The filtering method we want is to filter through the string, so we get their text through the value value. Note that this can only be used when the value value is not specified in the tag. If the value value is specified, you can use innerText to obtain it. After obtaining the filter conditions required by the user in each select box, pass them into the ChooseCondition function, and then run each sub function to achieve the desired goal.
So when will this function be called? Let's go back to the code when we first made the table:

<select id="selectNumber" οnchange="ChooseSelect()">

This onchange function is used to call the final ChooseSelect() function to change the contents of the whole table after changing any drop-down box.

So far. We analyzed layer by layer and worked step by step, and finally achieved the goal of the experiment. Next, we will enter this experiment, which is also the characteristic link of our last experiment:

demonstration

This is the characteristic part of this experiment. Most of this experiment lies in the front end, and we don't use forms directly for development as before, but need to display with the help of browser. This is what we will see from this experiment, that is, all the databases.
After running the back-end code, we go directly to the Database window (cmd) and view the inserted database (table) through the select * from studentdata command:

As you can see, the data has been written to the database. We return to the DOS interface, transfer the directory to the project folder, and enter the instruction dotnet watch run to run the whole project. Of course, you can also start it directly in VS and run it in IIS.

It can be seen that after running the web application, we directly enter the home interface. We add / test in the address bar to access the Index function of TestController, and then show it to the browser.

You can see that we have entered the page containing tables, which shows all the data. We can select the items we want by adjusting different filter conditions

If you choose mutually exclusive conditions

You can see that it has become an empty watch, which is reasonable.
So far, we have completed the display of all the contents of this experiment.

summary

Through this experiment, we understand the basic organization form of relational database, master the basic content and use mode of SQL language, and complete the function of adding data and searching data. In terms of front-end content, we learned the basic front-end knowledge such as HTML and JavaScript, and understood ASP Net core's basic organizational structure and MVC development mode. Through the above knowledge, the development of a simple Web application is completed.

Of course, this experiment is a very simple MVC development mode, which is difficult to catch up with the real professional development. For example, even if the front and back ends are not separated, there should be a way for the front end to send an instruction request to the back end, query the database and then return, rather than using a less efficient way to operate the data in the cache through JS. The advantage of database lies in its strong data organization ability. Secondly, this experiment can be completed in layers, that is, it is divided into presentation layer and service layer, which can make the program more modular. Finally, we can use ASP Net core provides other ways to achieve more efficient access to the database, rather than using mysql Data. MySQL client package.

review

Through this semester's C# study, we learned a lot from the initial console interface development, to form application development, to delegate event reflection, to multithreading and mutual exclusion, and finally to Web application development. The author also went all the way from a little white who knew nothing to a learner with a certain development foundation. Of course, there are far more ways of software development, and software development is by no means C# this way, but through the C# study this semester, we can appreciate the charm, basic mode and basic knowledge of software development. Soft engineering has a long way to go. I hope I can learn more profound, practical and interesting development contents in the future. At the end of the article, I would like to thank Professor Lai Yongxuan, who has been accompanying and guiding us, the dedicated teaching assistant, the dear students around us, and myself who have been climbing on the rugged road.
The above is the of this experimental report,
All content.
I wish you all a happy 2022!

reference

Li Hui et al Database system principle and MySQL application course: China Machine Press, 2015

Li Chunbao, Zeng Ping, Yu Dandan C# programming course (3rd Edition): Tsinghua University Press, 2015

Lai Yongxuan Mobile Computing & data analysis laboratory, Xiamen University

Copyright @ 2021, CSDN: ForeverMeteor, all rights reserved.