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 first
MySqlConnection
After 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 is
localhost
- Database port number: Default value
3306
- Login name: The administrator is
root
- 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