The server needs to connect to the database to interact with the client. In this case, Mysql is used to connect to the database

Development environment:

  • VS 2019
  • MySQL
  • .net 一, Connect to the MySQL environment configuration

To connect to MySQL, you need to configure the mysql.data. DLL file. If you use Vs2019, you can download it directly.

Use its method directly firstMySqlConnectionAfter an error message, Alt+Enter displays possible fixes, and then select InstallMySQL.Data, VS2019 will automatically download and install

You can also manually add the MySQL database. You can download it from the MySQL official website and install it manually

Connecting to a database requires variables

First create a class ConnDB to encapsulate database connection opening and closing and define the database connection variables you need to use:

  • Database address: the local islocalhost
  • Database port number: Default value3306
  • Login name: The administrator isroot
  • Login password: specifies the password of the user
  • Database name: The name of the database on which you need to operate

The specific definition is:

		// Define the connection address, port number, login user name, password, database name
        private string server;
        private string port;
        private string user;
        private string password;
        private string datename;
Copy the code

The constructor gets the corresponding parameters

The constructor gets the required parameters for instantiation of the class as follows:

 		public ConnDB(string _server,string _port,string _user,string _password,string _datename)
        {
            this.server = _server;
            this.port = _port;
            this.user = _user;
            this.password = _password;
            this.datename = _datename;
        } 
Copy the code

Connect and open the database

You need to connect to the database first, and you can connect to the database using MySqlConnection(), which takes a string of variables defined before the connection

Using the string.format () method, we can form these variables into strings:

		private MySqlConnection conn;
 		public MySqlConnection openDate()
        {
            try
            {
                string connStr = string.Format("server={0}; port={1}; user={2}; password={3}; database={4};", server, port, user, password, datename);
                // Connect to the database
                conn = new MySqlConnection(connStr);
                / / open
                conn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Database connection error due to:" + e.ToString());
            }
            return conn;            
        }
Copy the code

Close the database connection

Define a function to close the database:

public void closeDB(MySqlConnection conn)
        {
            // Close the database
            conn.Close();
        }
Copy the code

6. Invoke scripts through query cases

1. First instantiate the class and create a connection through openDate() in the class

2, then use the MySqlCommand() method to execute the SQL statement to complete the query

3, use MySqlDataReader () to process the query results

4. Output data in a certain way

	 	static void Main(string[] args)
        {
            // Test the database
            ConnDB connDB = new ConnDB("localhost"."3306"."root"."* * *"."* *");
            MySqlConnection conn= connDB.openDate();
            string sqlStr = "select * from users";
            // Execute the SQL statement and return the result
            MySqlCommand mycmd = new MySqlCommand(sqlStr,conn);
            // Extract data from the returned result
            MySqlDataReader re = mycmd.ExecuteReader();
            if (re.HasRows)
            {
                while (re.Read())
                {
                    // Read the second column
                    Console.WriteLine(re[1]);
                }
            }
            connDB.closeDB();
        }
Copy the code

Conclusion:

Post database connection class complete source code:

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace ServerDemo
{
    class ConnDB
    {
        // Define the connection address, port number, login user name, password, database name
        private string server;
        private string port;
        private string user;
        private string password;
        private string datename;

        private MySqlConnection conn;
        // The constructor takes arguments
        public ConnDB(string _server,string _port,string _user,string _password,string _datename)
        {
            this.server = _server;
            this.port = _port;
            this.user = _user;
            this.password = _password;
            this.datename = _datename;
        } 
       // 
       /// <summary>
       ///Connect to open database
       /// </summary>
        public MySqlConnection openDate()
        {
            try
            {
                string connStr = string.Format("server={0}; port={1}; user={2}; password={3}; database={4};", server, port, user, password, datename);
                // Connect to the database
                conn = new MySqlConnection(connStr);
                / / open
                conn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Database connection error due to:" + e.ToString());
            }
            return conn;            
        }
        public void closeDB()
        {
            // Close the databaseconn.Close(); }}}Copy the code

Test code:

using System;
using MySql.Data.MySqlClient;

namespace ServerDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Test the database
            ConnDB connDB = new ConnDB("localhost"."3306"."root"."* * * *"."* * *");
            MySqlConnection conn= connDB.openDate();
            string sqlStr = "select * from users";
            // Execute the SQL statement and return the result
            MySqlCommand mycmd = new MySqlCommand(sqlStr,conn);
            // Extract data from the returned result
            MySqlDataReader re = mycmd.ExecuteReader();
            if (re.HasRows)
            {
                while (re.Read())
                {
                    // Read three columns of data
                    Console.WriteLine(re[1]); } } connDB.closeDB(); }}}Copy the code