Article directory
- Preface
- 1. Preparation work
- 2. C# uses Sql
- code demo
Foreword
Because the work has been transferred from C++ to C#, here we record how C# connects to the database. We will consider database transaction issues and threading issues later. Currently, it is just a simple implementation, as long as it can be used.
1. Preparation
1. The first thing to do is to install VS and MySql.
2. Get Mysql.data.dll
This file can be found in the C:\Program Files (x86)\MySQL\MySQL Installer for Windows directory. If not, you can download it directly: download the address and extract it to a file.
3. Add a reference
Find the solution explorer, right-click References->Add Reference
Find the Mysql.data.dll file and check it.
At this time, there will be more information about MySql.data in the reference column.
At this point, the preparation work is over
2. C# uses Sql
1. Import namespace using MySql.Data.MySqlClient;
2. Configuration parameters:
Server= server IP address;Database= database name;UserID= database user name;Password= database password;Port= port number
3. Database connection string
General connection method:
string connetStr = "server=127.0.0.1;port=3306;user=root;password=database password; database=test;"; MySqlConnection conn = new MySqlConnection(connetStr);
But it is easy to make mistakes in the above, you can borrow SqlConnectionStringBuilder
SQLConnectionStringBuilder is a class used to build SQL Server connection strings. This class makes it easier to build and manage SQL Server connection strings.
The specific usage is as follows:
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.UserID = "root";
builder.Password = "e9t6q4";
builder.Server = "127.0.0.1";
builder.Port = 13306;
builder.Database = "prc_d";
MySqlConnection m_sqlConnect = new MySqlConnection(builder.ConnectionString);
Database connection string parameters
4. Determine whether the database connection is successful
try
{<!-- -->
m_sqlConnect.Open();
Console.WriteLine("Database connection successful");//Print information
}
catch (MySqlException ex)
{<!-- -->
Console.WriteLine(ex.Message);//Print error message
}
5. Database read and write operations
| Parameters | Description |
|---|---|
| Server, host, data source, datasource , address, addr, network address | Server IP address |
| Database, initial catalog | Database name |
| Port | socket port, default 3306 |
| ConnectionProtocol, protocol | Connection protocol, default Sockets |
| PipeName, pipe | Connection pipe, default MYSQL |
| UseCompression, compress | Whether the connection is compressed, the default is false |
| AllowBatch | Whether multiple SQL statements are allowed to be executed at one time, the default is true |
| Logging | Whether to enable logging, the default is false |
| SharedMemoryName | The name of the memory share, the default is MYSQL |
| UseOldSyntax, old syntax, oldsyntax | Whether it is compatible with the old syntax, the default is false |
| ConnectionTimeout, connection timeout | Connection timeout waiting time, default 15s |
| DefaultCommandTimeout, command timeout | MySqlCommand timeout, default 30s |
| UserID, uid, username, user name, user | Database login account |
| Password, pwd | Login password |
| PersistSecurityInfo | Whether to keep sensitive information, default false |
| Encrypt | Encrypt |
| CertificatePassword | Certificate Password |
| CertificateStoreLocation | Certificate storage location |
| CertificateThumbprint | Certificate thumbprint |
| AllowZeroDateTime | Whether the date and time can be zero, the default is false |
| ConvertZeroDateTime | Whether the date and time of zero can be converted into DateTime.MinValue, the default false |
| UseUsageAdvisor, usage advisor | Whether the assistant is enabled will affect database performance, the default is false |
| ProcedureCacheSize, procedure cache, procedurecache | Several stored procedures can be cached at the same time, 0 is prohibited, the default is 25 |
| UsePerformanceMonitor, userperfmon, perfmon | Whether to enable performance monitoring, the default is false |
| IgnorePrepare | Whether to ignore the Prepare() call, the default is true |
| UseProcedureBodies, procedure bodies | Whether to check the validity of stored procedure bodies and parameters, the default is true |
| AutoEnlist | Whether to automatically use active connections, the default is true |
| RespectBinaryFlags | Whether to respond to the binary flags of metadata on the column, the default is true |
| TreatTinyAsBoolean | Whether the TINYINT(1) column is treated as Boolean, default true |
| AllowUserVariables | Whether user variables are allowed to appear in SQL, the default is false |
| InteractiveSession, interactive | Whether the session allows interaction, the default is false |
| FunctionsReturnString | Whether all server functions are processed as return strings, the default is false |
| UseAffectedRows | Whether to use the number of affected rows instead of the number of found rows to return data, the default is false |
| OldGuids | Whether to use the binary(16) column as Guids, default false |
| Keepalive | The number of seconds to keep the TCP connection, default 0, not kept. |
| ConnectionLifeTime | The minimum time (seconds) that a connection remains in the connection pool before being destroyed. Default 0 |
| Pooling | Whether to use the thread pool, default true |
| MinimumPoolSize, min pool size | The minimum number of threads allowed in the thread pool, default 0 |
| MaximumPoolSize, max pool size | The maximum number allowed in the thread pool Number of threads, default 100 |
| ConnectionReset | Whether the connection will be automatically reset after expiration, default false |
| CharacterSet, charset | Request the character set used for connection to the server, default: none |
| TreatBlobsAsUTF8 | Whether binary blobs are utf8 Treated, the default is false |
| BlobAsUTF8IncludePattern | The matching pattern of the column, once matched, will be processed as utf8, default: none |
| SslMode | Whether to enable SSL connection mode, default: MySqlSslMode.None |
6. Database read and write operations
ExcuteReader: Execute multi-line query and return DataReader object
ExcuteScalar: Execute a single row query and return the first row of data in the query result
Code demo
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
namespace text
{<!-- -->
classDBManager
{<!-- -->
//SQL Server connection string class that makes it easier to build and manage SQL Server connection strings.
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
//Classes in the .NET client library for MySQL database connection
private MySqlConnection m_sqlConnect;
//Single instance of database operation
private static readonly DBManager m_instance = new DBManager();
public staticDBManagerInstance
{<!-- -->
get
{<!-- -->
return m_instance;
}
}
privateDBManager()
{<!-- -->
//Configure the data required to connect to the database
builder.UserID = "root";
builder.Password = "e9t6q4";
builder.Server = "127.0.0.1";
builder.Port = 13306;
builder.Database = "prc_d";
//Connect to the database
m_sqlConnect = new MySqlConnection(builder.ConnectionString);
Connect();
}
~DBManager()
{<!-- -->
Close();
}
//Judge whether the database is connected
private bool Connect()
{<!-- -->
try
{<!-- -->
m_sqlConnect.Open();
Console.WriteLine("Database connection successful");//Print information
}
catch (MySqlException ex)
{<!-- -->
Console.WriteLine(ex.Message);//Print error message
return false;
}
return true;
}
//Close the database
private bool Close()
{<!-- -->
if (m_sqlConnect.State == ConnectionState.Closed)
{<!-- -->
Console.WriteLine("No database being linked");//Print information
return false;
}
try
{<!-- -->
m_sqlConnect.Close();
Console.WriteLine("Database closed successfully");//Print information
return true;
}
catch (MySqlException ex)
{<!-- -->
Console.WriteLine(ex.Message);//Print error message
}
return false;
}
//Because reading and writing are different, they are written separately.
//Read data, the parameters are sql operation statements
public MySqlDataReader DBRead(string sqlStr)
{<!-- -->
//Class for reading query results from the MySQL database
MySqlDataReader reader = null;
try
{<!-- -->
if (m_sqlConnect.State != ConnectionState.Open)
{<!-- -->
Console.WriteLine("Database is not linked");
//Try to relink
Connect();
}
MySqlCommand command = new MySqlCommand(sqlStr, m_sqlConnect);
reader = command.ExecuteReader();
}
catch (MySqlException ex)
{<!-- -->
Console.WriteLine(ex.Message);//Print error message
}
return reader;
//-----------------------Write it after the return, instead of pulling a separate module to write---------- ---------------
// Usage of MySqlDataReader reader
while (reader.Read())
{<!-- -->
//Determine whether the field is null. If the field is null, the data conversion will fail.
if (!reader.IsDBNull(1))
{<!-- -->
// Output the first column field value
Console.WriteLine(reader.GetInt32(0));
// Console.WriteLine(reader.GetInt32("id"));
}
}
}
//Change database
public int DBWrite(string sqlStr)
{<!-- -->
if (m_sqlConnect.State != ConnectionState.Open)
{<!-- -->
Console.WriteLine("Database is not linked");
return -1;
}
try
{<!-- -->
MySqlCommand command = new MySqlCommand(sqlStr, m_sqlConnect);
return command.ExecuteNonQuery();//If it is not equal to 0, it means the change is successful, and the returned result is the number of changed data
}
catch (MySqlException ex)
{<!-- -->
Console.WriteLine(ex.Message);//Print error message
return -1;
}
}
}
}