C#: mysql增删改查的封装类的简单实现

默认已安装mysql

首先要添加 Mysql.Data引用

先安装一个mysql-connector-net插件：
安装一切默认就行

在mysql中建一个表，加 utf8 防止中文乱码

create table if not exists users (
id int auto_increment primary key comment '用户ID',
username varchar(32) comment '用户名',
pwd varchar(32) comment '用户密码',
sex char(8) comment '用户性别',
age int comment '年龄',
email varchar(32) comment '邮箱',
dtime datetime DEFAULT CURRENT_TIMESTAMP comment '创建时间'
) charset=utf8;

之后在VS项目中添加引用Mysql.Data

表模板类

namespace ASP.App_Code.entity
{
    public class UserEntity
    {
        private int id;
        private string username;
        private string pwd;
        private string sex;
        private int age;
        private string email;
        private string dtime;

        public UserEntity(int id, string username, string pwd, string sex, int age, string email, string dtime) {
            this.id = id;
            this.username = username;
            this.pwd = pwd;
            this.sex = sex;
            this.age = age;
            this.email = email;
            this.dtime = dtime;
        }

        public UserEntity(string username, string pwd, string sex, int age, string email, string dtime) {
            this.username = username;
            this.pwd = pwd;
            this.sex = sex;
            this.age = age;
            this.email = email;
            this.dtime = dtime;
        }

        public int Id { get => id; set => id = value; }
        public string Username { get => username; set => username = value; }
        public string Pwd { get => pwd; set => pwd = value; }
        public string Sex { get => sex; set => sex = value; }
        public int Age { get => age; set => age = value; }
        public string Email { get => email; set => email = value; }
        public string Dtime { get => dtime; set => dtime = value; }

        public override string ToString() {
            return id +",'"+ username + "','" + pwd + "','" + sex + "'," + age + ",'" + email + "'";
        }
    }
}

数据库帮助类

using MySql.Data.MySqlClient;
using System.Data;

namespace ASP.App_Code.utils
{
    public class DbUtil
    {
        public const string CONN_STRING = "server=localhost;port=3306;database=dbname;" +
    "user=root;password=123456;charset=utf8";
        private DbUtil() { }
        private class Holder
        {
            internal static readonly MySqlConnection conn = new MySqlConnection(CONN_STRING);
        }
        public static MySqlConnection GetConn() {
            return Holder.conn;
        }
        public static void close() {
            GetConn().Close();
        }
        public static void close(MySqlDataReader reader) {
            reader.Close();
            close();
        }
        public static MySqlCommand GetCommand(string command) {
            if (GetConn().State != ConnectionState.Open) {
                GetConn().Open();
            }
            return new MySqlCommand(command, GetConn());
        }

        public static MySqlCommand GetCommand(string command, params object[] args) {
            MySqlCommand cmd = GetCommand(command);
            for (int i = 0; i < args.Length; i++) {
                cmd.Parameters.Add(new MySqlParameter("@" + i, args[i]));
            }
            return cmd;
        }
    }
}

dao层对象

using System.Collections.Generic;
using ASP.App_Code.entity;
using ASP.App_Code.utils;
using MySql.Data.MySqlClient;

namespace ASP.App_Code.dao
{
    public class UserDao
    {
        public static readonly string TABLE = "users";
        private static UserDao dao = new UserDao();
        private UserDao() { }
        public static UserDao Instance() {
            return dao;
        }
        public int Insert(UserEntity entity) {
            MySqlCommand cmd = DbUtil.GetCommand(
                   "insert into " + TABLE + " (id,username,pwd,sex,age,email) values (" + entity.ToString() + ")");
            int affectRow = cmd.ExecuteNonQuery();
            DbUtil.close();
            return affectRow;
        }
        public int RemoveById(int id) {
            MySqlCommand cmd = DbUtil.GetCommand(
                    "delete from " + TABLE + " where id=@0"
                , id);
            int affectRow = cmd.ExecuteNonQuery();
            DbUtil.close();
            return affectRow;
        }
        public UserEntity ReadById(int id) {
            MySqlCommand cmd = DbUtil.GetCommand("select * from " + TABLE + " where id=" + id);
            MySqlDataReader reader = cmd.ExecuteReader();
            UserEntity entity = null;
            if (reader.Read()) {
                entity = new UserEntity(reader.GetInt32("id"),
                    reader.GetString("username"),
                    reader.GetString("pwd"),
                    reader.GetString("sex"),
                    reader.GetInt32("age"),
                    reader.GetString("email"),
                    reader.GetDateTime("dtime").ToString("yyyy-MM-dd hh:mm:ss")
                    );
            }
            DbUtil.close(reader);
            return entity;
        }
        public UserEntity ReadByUsername(string username) {
            MySqlCommand cmd = DbUtil.GetCommand("select * from " + TABLE + " where username=@0", username);
            MySqlDataReader reader = cmd.ExecuteReader();
            UserEntity entity = null;
            if (reader.Read()) {
                entity = new UserEntity(reader.GetInt32("id"),
                    reader.GetString("username"),
                    reader.GetString("pwd"),
                    reader.GetString("sex"),
                    reader.GetInt32("age"),
                    reader.GetString("email"),
                    reader.GetDateTime("dtime").ToString("yyyy-MM-dd hh:mm:ss")
                    );
            }
            DbUtil.close(reader);
            return entity;
        }
        public List<UserEntity> ReadAll() {
            MySqlCommand cmd = DbUtil.GetCommand("select * from " + TABLE);
            MySqlDataReader reader = cmd.ExecuteReader();
            List<UserEntity> list = new List<UserEntity>();
            while (reader.Read()) {
                list.Add(new UserEntity(reader.GetInt32("id"),
                    reader.GetString("username"),
                    reader.GetString("pwd"),
                    reader.GetString("sex"),
                    reader.GetInt32("age"),
                    reader.GetString("email"),
                    reader.GetDateTime("dtime").ToString("yyyy-MM-dd hh:mm:ss")
                    ));
            }
            DbUtil.close(reader);
            list.Sort((o1, o2) => o2.Dtime.CompareTo(o1.Dtime));
            return list;
        }
        public int Update(UserEntity entity) {
            string setStr = "username='" + entity.Username + "'," +
                "pwd='" + entity.Pwd + "'," +
                "sex='" + entity.Sex + "'," +
                "age=" +entity.Age+"," +
                "email='" + entity.Email + "'";
            int affectRow = DbUtil.GetCommand(
                    "update " + TABLE + " set " + setStr + " where id=@0"
                , entity.Id).ExecuteNonQuery();
            DbUtil.close();
            return affectRow;
        }
    }
}

简单的功能完成！
如果要完善的话就是要在增改时将一些特定的字符全部给过滤掉，不然会报数据库错误