Winform中使用MySQL数据库

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 params
            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)
                );
        }
        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);

原文地址：https://blog.csdn.net/qq_27474555/article/details/142331267

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！