默认已安装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;
}
}
}
简单的功能完成!
如果要完善的话就是要在增改时将一些特定的字符全部给过滤掉,不然会报数据库错误