WPF上位机8——C#与MySQL

ADO.NET

数据库连接

数据插入、删除、更改

数据查询

带单个参数

带多个参数

      using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace WpfTest
{public class SqlHelper{private static string constr = ConfigurationManager.ConnectionStrings["db"].ConnectionString;/// <summary>/// 连接数据库/// </summary>/// <returns></returns>public MySqlConnection? ConnectDatabase(){MySqlConnection connection = new MySqlConnection(constr);connection.Open();if (connection != null && connection.State == System.Data.ConnectionState.Open){Console.WriteLine("数据库连接成功");}else{Console.WriteLine("数据库连接失败");}return connection;}/// <summary>/// 关闭数据库/// </summary>/// <param name="mySqlConnection"></param>public void CloseConnection(MySqlConnection mySqlConnection){if (mySqlConnection != null && mySqlConnection.State == System.Data.ConnectionState.Open){mySqlConnection.Close();mySqlConnection = null!;}}/// <summary>/// 执行数据DML(insert,update,delete)操作/// </summary>/// <param name="sql"></param>public void ExecuteUpdate(string? sql){var connection = ConnectDatabase();MySqlCommand cmd = connection!.CreateCommand();cmd.CommandText = sql;int row = cmd.ExecuteNonQuery();if (row > 0){Console.WriteLine("操作成功");}else{Console.WriteLine("操作失败");}CloseConnection(connection);}/// <summary>/// 数据查询的调用模板/// </summary>public void test(){MySqlParameter[] parameters = new MySqlParameter[]{new MySqlParameter("@height",30),new MySqlParameter("@width",50),};string sql = "select * from tb_name where height=@height and width=@width";ExecuteQuery(sql, parameters);}/// <summary>/// 数据查询/// </summary>/// <param name="sql"></param>public void ExecuteQuery(string sql, MySqlParameter[] mySqlParameters){var connection = ConnectDatabase();try{MySqlCommand mySqlCommand = new MySqlCommand(sql, connection);foreach (MySqlParameter param in mySqlParameters){mySqlCommand.Parameters.Add(param);}MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();while (mySqlDataReader.Read()){mySqlDataReader.GetInt32(0);     // 通过列的索引拿到数据mySqlDataReader.GetString("表字段名1");  //通过列的名称拿到数据mySqlDataReader.GetString("表字段名2");}mySqlDataReader.Close();}catch (Exception){throw;}finally{CloseConnection(connection!);}}}
}

基于特性+反射+DAO通用操作库

ORM对象关系映射

数据库中表映射为实体类

调用ORM框架中提供的新增方法：构建sql语句

创建枚举

根据枚举类型创建sql语句

调用

实体限定

生成参数列表

生成插入的所有方法

使用特性映射表名和字段名

通过特性获取真实的表名称

当字段名发生更改时会报错，所以给字段也添加特性

修改的方法