Database application development

Time:2021-1-29

Today’s database experiment is to “master the general method of database application development”, and the development environment is vs (I use version 19 here)

1、 Preparation

  • Operating environment: above win7
  • DBMS:MySQL 5.5
  • Development environment: vs19 (12 or above is OK)

2、 Establish engineering project

Default template after new project

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using MySql.Data.MySqlClient;

namespace LiwkerApp
{
    class Program
    {
        static void Main(string[] args)
        
        {
            
        }

        
    }
}

I won’t say much later. You must import the package first and then install it

image-20201207195740829

3、 Experiments

1. Query a student randomly

static void Main(string[] args)
        {
    		//After defining the function, call in main
            //show();
            //delOne();
            //Find();
            //showAll();
            //addOne1();
        }

code

//Show student functions
        static void show()
        {
            //Define a string to connect to the database
            //Here is the local server (localhost), login user name is root, there is a password to add
            string conStr = "server=localhost;user=root;database=Liwker;password=1110";

            //Define a database connection object con, which can be understood as the channel for the connection program to connect to the database
            //This shows that the above connection string is used to establish a connection with the target database
            MySqlConnection con = new MySqlConnection(conStr);

            //Connect to the database, which is usually called open database
            //Note that if the runtime reports an error here, either the server is not started
            //Or the connection information in the previous constr is wrong!
            con.Open();

            //Define an s0l command object to store the SQL commands and parameters sent to the server
            MySqlCommand cmd = new MySqlCommand();

            //The SQL command object connects to our database through con
            cmd.Connection = con;

            //The SQL command object will send the command string to the server. Of course, other types such as stored procedures can be used
            cmd.CommandType = System.Data.CommandType.Text;

            //Prepare the SQL command to be executed, where the function of the command is to randomly retrieve the name and gender of a student
            cmd.CommandText = "select * from student order by rand() limit 1;";

            //Define a data adapter based on CMD, which is specially used to receive the result after the execution of CMD command
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            //Define a data set, which is equivalent to the set of tables, and is used to receive the results of SQL command object execution
            DataSet ds = new DataSet();

            //Use the adapter to execute the command to fill the DS data set, that is to load the retrieved data into DS
            adapter.Fill (DS); // this method has a return value and returns the number of rows loaded

            //After the previous SQL command successfully fetches the students' information, it will be put into rows [0] row of tables [0] table of DS
            //If not, the rows number of tables [0] table is 0
            if (ds.Tables[0].Rows.Count > 0)
            {
                System.Console.WriteLine (student number: {0}), ds.Tables [0]. Rows [0] [student number]];
                //Show returned names
                System.Console.WriteLine ("Name: {0}"), ds.Tables [0]. Rows [0] [name]];
                //Displays the gender of the return
                System.Console.WriteLine (gender: {0}), ds.Tables [0]. Rows [0] [gender]];
                System.Console.WriteLine (age: {0}), ds.Tables [0]. Rows [0] [age]];
                System.Console.WriteLine (family: {0}), ds.Tables [0]. Rows [0] ["department"];
            }
            else 
            {
                System.Console.WriteLine ("no students yet! ");
            }

            //When you run out of commands, remember to close the connection so that the server can better serve other connections
            con.Close();
        }

result

image-20201207195923638

2. Delete students

code

//Delete a student by student number
        static void delOne()
        {
            String who; // stores the searched student number
            Console.Write ("deleted student No.:); // output prompt information
            who =  Console.ReadLine (); // accept keyboard input

            //Any database operation, its connection to the database and SQL command preparation are the same, so you can directly use the following
            string conStr = "server=localhost;user=root;database=Liwker;password=1110";
            MySqlConnection con = new MySqlConnection(conStr);
            con.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;

            //Edit the SQL command issued to the server to delete students, where @ XH is a parameter
            cmd.CommandText  ="Delete from student where" [email protected] ";

            //Provide the parameters required by the command, here provide the student number just entered
            cmd.Parameters.AddWithValue("@xh", who);

            //The following methods are used to execute the SQL command of non query class. 1 is success, 0 is failure
            int r = cmd.ExecuteNonQuery();
            if(r == 1)
            {
                System.Console.WriteLine "Delete successfully! ");
            }
            else
            {
                System.Console.WriteLine "Deletion failed, or there may be no such person at all! ");
            }

            //When you run out of commands, remember to close the connection so that the server can better serve other connections
            con.Close();
        }

        //Inquire the information of a student through the student number
        static void Find()
        {
            string who;
            Console.Write ("please input student number for inquiry");
            who = Console.ReadLine();

            String constr = "server = localhost; user = root; database = liwker; password = 1110"; // the string connecting to the database
            MySQL connection con = new MySQL connection (constr); // define a new database link
            con.Open (); // open database

            MySQL command CMD = new MySQL command(); // define a new SQL command
            cmd.Connection  =Con; // the SQL command links to the database
            cmd.CommandType  =  System.Data.CommandType . text; // send command string
            cmd.CommandText  ="Select * from student where" [email protected]; "; // write SQL command string
            cmd.Parameters.AddWithValue ("@ XH", who); // replace parameters

            MySQL DataAdapter adapter = new MySQL DataAdapter (CMD); // define a data adapter that accepts the execution result of the CMD object
            Dataset DS = new dataset(); // define a dataset that accepts the execution results of SQL objects
            adapter.Fill (DS); // load the data in the adapter into DS
            if (ds.Tables[0].Rows.Count > 0)
            {
                System.Console.WriteLine (student number: {0}), ds.Tables [0]. Rows [0] [student number]];
                System.Console.WriteLine ("Name: {0}"), ds.Tables [0]. Rows [0] [name]];
                System.Console.WriteLine (gender: {0}), ds.Tables [0]. Rows [0] [gender]];
                System.Console.WriteLine (age: {0}), ds.Tables [0]. Rows [0] [age]];
                System.Console.WriteLine (family: {0}), ds.Tables [0]. Rows [0] ["department"];
            }
            else
            {
                System.Console.WriteLine "No one! ");
            }
            con.Close();
        }

result

image-20201207202410347

3. Query all students’ information

code

//Query all students' information
        static void showAll()
        {
            string conStr = "server=localhost;user=root;database=Liwker;password=1110";
            MySqlConnection con = new MySqlConnection(conStr);
            con.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "select * from student;";
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            //Loop through all the lines (student)
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                System.Console.WriteLine (student number: {0}), ds.Tables [0]. Rows [i] [student number]];
                System.Console.WriteLine ("Name: {0}"), ds.Tables [0]. Rows [i] [name]];
                System.Console.WriteLine (gender: {0}), ds.Tables [0]. Rows [i] [gender]];
                System.Console.WriteLine (age: {0}), ds.Tables [0]. Rows [i] [age]];
                System.Console.WriteLine (family: {0}), ds.Tables [0]. Rows [i] ["department"];
                System.Console.WriteLine("\n");
            }

            con.Close();
        }

result

image-20201207202515284

4. Query single student

code

//Inquire the information of a student through the student number
        static void Find()
        {
            string who;
            Console.Write ("please input student number for inquiry");
            who = Console.ReadLine();

            String constr = "server = localhost; user = root; database = liwker; password = 1110"; // the string connecting to the database
            MySQL connection con = new MySQL connection (constr); // define a new database link
            con.Open (); // open database

            MySQL command CMD = new MySQL command(); // define a new SQL command
            cmd.Connection  =Con; // the SQL command links to the database
            cmd.CommandType  =  System.Data.CommandType . text; // send command string
            cmd.CommandText  ="Select * from student where" [email protected]; "; // write SQL command string
            cmd.Parameters.AddWithValue ("@ XH", who); // replace parameters

            MySQL DataAdapter adapter = new MySQL DataAdapter (CMD); // define a data adapter that accepts the execution result of the CMD object
            Dataset DS = new dataset(); // define a dataset that accepts the execution results of SQL objects
            adapter.Fill (DS); // load the data in the adapter into DS
            if (ds.Tables[0].Rows.Count > 0)
            {
                System.Console.WriteLine (student number: {0}), ds.Tables [0]. Rows [0] [student number]];
                System.Console.WriteLine ("Name: {0}"), ds.Tables [0]. Rows [0] [name]];
                System.Console.WriteLine (gender: {0}), ds.Tables [0]. Rows [0] [gender]];
                System.Console.WriteLine (age: {0}), ds.Tables [0]. Rows [0] [age]];
                System.Console.WriteLine (family: {0}), ds.Tables [0]. Rows [0] ["department"];
            }
            else
            {
                System.Console.WriteLine "No one! ");
            }
            con.Close();
        }

result

image-20201207202442361

5. Add a new student

Method 1

//New students (method 1)
        //It's a bit defective, because the student number attribute is fixed with 5 characters, here if is used to judge and compare (so the blank space is found)
        //For example, input "S02" is not found, you must input "S02"
        static void addOne()
        {
            String XH, name, sex, age, XB, SG; // declare different strings to receive student information

            Console.Write (new student number:);
            xh =  Console.ReadLine (); // input the student number first, so as to judge whether there is repetition

            //Connect to database
            string conStr = "server=localhost;user=root;database=Liwker;password=1110";
            MySqlConnection con = new MySqlConnection(conStr);
            con.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;

            //Get the student number of the existing student first
            cmd.CommandText  ="Select student number from student";
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            adapter.Fill(ds);

            //Repeated judgment of student number
            for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (xh == (string) ds.Tables [0]. Rows [i] [student number]] // add string cast
                {
                    Console.Write ("this student number already exists, please re-enter it):";
                    xh = Console.ReadLine();
                    i = -1;
                }
            }

            Console.Write ("name");
            name = Console.ReadLine();

            Console.Write (gender:);
            sex = Console.ReadLine();

            Console.Write (age:);
            age = Console.ReadLine();

            Console.Write (family:);
            xb = Console.ReadLine();

            Console.Write (height:);
            sg = Console.ReadLine();

            //Define SQL insert command
            cmd.CommandText = "insert into student values(@xh,@name,@sex,@age,@xb,@sg)";
            cmd.Parameters.AddWithValue("@xh", xh);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@sex", sex);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@xb", xb);
            cmd.Parameters.AddWithValue("@sg", sg);
            int r =  cmd.ExecuteNonQuery (); // return of non query SQL commands (0 or 1)
            if (r == 1)
            {
                System.Console.WriteLine "Add successfully! ");
            }
            else
            {
                System.Console.WriteLine "Add failed! ");
            }

            con.Close();
        }

Method 2

//New students (method 2)
        //This method has no space problem
        //Every time you enter the student number, you will run SQL to see if there are duplicate data
        static void addOne1()
        {
            string xh, name, sex, age, xb, sg;
            Int f = 0; // define a flag for later judgment of repetition

            Console.Write (new student number:);
            xh = Console.ReadLine();

            //Connect to database
            string conStr = "server=localhost;user=root;database=Liwker;password=1110";
            MySqlConnection con = new MySqlConnection(conStr);
            con.Open();

            //Repeated judgment of student number
            do
            {
                //Let's first judge whether there is repetition
                if (f != 0)
                {
                    Console.Write ("this student number already exists, please re-enter it):";
                    xh = Console.ReadLine();
                }
                //Define a new SQL command
                MySqlCommand cmd1 = new MySqlCommand();
                cmd1.Connection = con;
                cmd1.CommandType = System.Data.CommandType.Text;

                //Define the SQL command to query whether the student number is repeated
                CMD1. Commandtext = select * from student where [email protected] ";
                cmd1.Parameters.AddWithValue("@xh", xh);
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd1);
                DataSet ds = new DataSet();
                f =  adapter.Fill (DS); // F accepts the number of rows, 0 is none, n is with n rows of data
            }While (F > 0); // loop until there is no repetition


            Console.Write ("name");
            name = Console.ReadLine();

            Console.Write (gender:);
            sex = Console.ReadLine();

            Console.Write (age:);
            age = Console.ReadLine();

            Console.Write (family:);
            xb = Console.ReadLine();

            Console.Write (height:);
            sg = Console.ReadLine();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;
            //Define SQL insert command
            cmd.CommandText = "insert into student values(@xh,@name,@sex,@age,@xb,@sg)";
            cmd.Parameters.AddWithValue("@xh", xh);
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@sex", sex);
            cmd.Parameters.AddWithValue("@age", age);
            cmd.Parameters.AddWithValue("@xb", xb);
            cmd.Parameters.AddWithValue("@sg", sg);
            int r =  cmd.ExecuteNonQuery (); // return of non query SQL commands (0 or 1)
            if (r == 1)
            {
                System.Console.WriteLine ("- New successfully! ");
            }
            else
            {
                System.Console.WriteLine ("- add failed! ");
            }

            con.Close();
        }

result

image-20201207202612467