1、创建项目并添加引用MySql.Data；
2、在App.config文件添加connectionString
<?xml version="1.0" encoding="utf-8" ?>
<configuration><startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" /></startup><connectionStrings><add name="ConStr" connectionString="Server=localhost;User=root;Password=123456;Database=performance;" providerName="MySql.Data.MySqlClient"/></connectionStrings>
</configuration>
3、设计数据库
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(20) COLLATE utf8mb3_general_mysql500_ci NOT NULL,`sex` varchar(8) COLLATE utf8mb3_general_mysql500_ci NOT NULL,`password` varchar(20) COLLATE utf8mb3_general_mysql500_ci NOT NULL,`identity_id` int NOT NULL,`is_delete` varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_mysql500_ci NOT NULL DEFAULT '0',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`),UNIQUE KEY `user_id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_mysql500_ci COMMENT='用户表';--
-- Dumping data for table `user`
--LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES (2,'Alan','female','abcdef',2,'1','2024-09-18 11:48:22');
UNLOCK TABLES;
4、创建MySql操作类
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace PerformanceManagementApp.Utils
{public class SqlHelper{public static string ConStr { get; set; } = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;public static DataTable ExecuteTable(string cmdText,params MySqlParameter[] sqlParameters){using(MySqlConnection conn = new MySqlConnection(ConStr)){conn.Open();MySqlCommand cmd = new MySqlCommand(cmdText, conn);cmd.Parameters.AddRange(sqlParameters);MySqlDataAdapter sqlDataAdapter=new MySqlDataAdapter(cmd);DataSet ds = new DataSet();sqlDataAdapter.Fill(ds);return ds.Tables[0];}}public static int ExecuteNonQuery(string cmdText, params MySqlParameter[] sqlParameters){using(MySqlConnection con = new MySqlConnection(ConStr)){con.Open();MySqlCommand cmd = new MySqlCommand(cmdText, con);cmd.Parameters.AddRange(sqlParameters);int rows=cmd.ExecuteNonQuery();if(rows<=0){Console.WriteLine("数据库操作失败！");}return rows;}}}
}
5、建立用户类
using MySql.Data.MySqlClient;
using PerformanceManagementApp.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace PerformanceManagementApp.Models
{public class User{public int Id { get; set; }public string Name { get; set; }public string Sex { get; set; }public string Password { get; set; }public int IdentityId { get; set; }public string IsDelete { get; set; }="0";public DateTime CreateTime { get; set; }public static User ToModel(DataRow dr){User user = new User();user.Id = (int)dr["id"];user.Name = (string)dr["name"];user.Sex = dr["sex"].ToString();user.Password = dr["password"].ToString();user.IdentityId = (int)dr["identity_id"];user.IsDelete = dr["is_delete"].ToString();user.CreateTime = (DateTime)dr["create_time"];return user;} public static List<User> ListAll(){var list = new List<User>();string sql = "SELECT * FROM user u";DataTable dt=SqlHelper.ExecuteTable(sql);foreach (DataRow dr in dt.Rows){list.Add(ToModel(dr));}return list;}public static int Insert(User user){string sql = $"INSERT INTO User(name,password,sex,identity_id,is_delete) VALUES(@name,@password,@sex,@identity_id,@is_delete)";//using SqlParameter to set query paramsreturn SqlHelper.ExecuteNonQuery(sql,new MySqlParameter("@name", user.Name),new MySqlParameter("@password", user.Password),new MySqlParameter("@sex", user.Sex),new MySqlParameter("@identity_id", user.IdentityId),new MySqlParameter("@is_delete", user.IsDelete));}public static int Update(User user){string sql = $"UPDATE User SET name=@name,password=@password,sex=@sex,identity_id=@identity_id,is_delete=@is_delete WHERE id=@id";return SqlHelper.ExecuteNonQuery(sql,new MySqlParameter("@name", user.Name),new MySqlParameter("@password", user.Password),new MySqlParameter("@sex", user.Sex),new MySqlParameter("@identity_id", user.IdentityId),new MySqlParameter("@is_delete", user.IsDelete),new MySqlParameter("@id", user.Id));}}
}
6、在应用程序进行增、查、改操作
（1）、添加数据
User user = new User();
user.Name = "Tom";
user.Sex = "male";
user.Password = "123456";
user.IdentityId = 1;
User.Insert(user);
（2）、查询数据
User.ListAll();
（3）、修改记录
User user = new User();
user.Id = 2;
user.Name = "Alan";
user.Sex = "female";
user.Password = "abcdef";
user.IdentityId = 2;
user.IsDelete = "1";
User.Update(user);