网站开发html工具,江苏省公路与水路建设网站,北京网页游戏制作,网站标题大全Windows中在C#中使用Dapper和Mysql.Data库连接MySQL数据库
在Windows中使用C#连接Mysql数据库比较简单#xff0c;可以直接使用MySql.Data库#xff0c;目前最新版本为#xff1a;8.3.0。 当然也可以结合MySql.Data和Dapper库一起使用#xff0c;目前Dapper的最新版本为可以直接使用MySql.Data库目前最新版本为8.3.0。 当然也可以结合MySql.Data和Dapper库一起使用目前Dapper的最新版本为2.1.35。 Dapper是一款轻量级ORM工具是一个简单的.NET对象映射器在速度上几乎与使用原始ADO.NET数据读取器的速度一样快。ORM是一个对象关系映射器它负责数据库和编程语言之间的映射。Dapper通过扩展IDbConnection提供一些有用的扩展方法去查询数据库提供了一种简单、高效的方式来执行数据库查询、插入、更新和删除操作。
一、使用Mysql.Data和Dapper来操作Mysql数据库
准备条件 (1)、OSWindows 10或Windows11 (2)、Visual Stuidio 2022 (3)、MySQL安装包比如mysql-installer-web-community-8.0.36.0.msi
本文使用的Visual Studio版本是VS2022MySQL版本为8.0.36对应的下载地址为mysql-installer-web-community-8.0.36.0.msi并采用C# .Net WinForm窗体程序作为演示示例我们展示如何使用Mysql.Data和Dapper连接MySql数据库并查询MySql数据库中对应的people表然后在窗体程序中输入字段LastName来查询对应的数据鼠标按下search按钮在ListBox中展示从MySQL数据库中的查询结果另外我们在下方的三个输入框中分别输入用户的FirstName、LastName、EmailAddress字段然后鼠标点击insert按钮往数据库表中插入一条记录。最终的程序界面如下图所示 (1)、根据LastName查询记录 我们首先在输入框中输入LastName如蒋英文中LastName代表中文中的姓FirstName代表中文中的名然后鼠标点击【Seach】按钮查询的结果展示在下方中的ListBox中如下图所示
(2)、往数据库people表中插入一条记录 我们首先在下面的FirstName、LastName、EmailAddress三个文本框中输入对应的数据然后鼠标点击insert按钮即可插入一条记录。 注意由于Id是主键自增的所以不用管。
执行插入记录后原来输入的FistName、LastName、EmailAddress记录会被清空如下图所示 接下来我们输入在最上方根据刚刚输入的LastName蒋看FirstName为青青LastName为蒋EmailAddress为jiangqingqingchina.com的那条记录是否入库如下图所示
二、C#窗体程序源代码下载
完整的C#窗体程序代码我已经上传到github上面了
git clone https://github.com/ccf19881030/WindowsFormsMySqlDbApp.git或者直接下载https://github.com/ccf19881030/WindowsFormsMySqlDbApp/archive/refs/heads/main.zip源代码即可
三、创建C# MySQL数据库窗体示例程序
1、创建一个基于C# .Net的WinForm窗体程序
基于VS2022创建一个WinForm .Net窗体程序如下图所示
最终的程序代码结构如下图所示
2、通过NuGet包管理器安装Mysql.Data和Dapper库
创建项目名称为WindowsFormsMySqlDbApp的C#窗体程序之后通过项目中的引用-》管理 NuGet 程序包(N)-》浏览分别输入MySql.Data和Dapper然后点击安装 3、设计界面UI
WinForm界面如下图所示
WinForm界面和MFC一样很简单只由Label、TextBox、Button、ListBox这四种控件组成。
4、安装MySQL数据库并Navicat Premium 16或者MySQL命令行创建数据库ytdemo和people表
安装mysql-installer-web-community-8.0.36.0.msi数据库安装包之后我们root账号的初始密码设置为123456然后使用Navicat Premium 16连接并登录本地MySQL数据库然后先创建ytdemo数据库然后在该数据库中创建people表其字段结果如下图所示 people表结构的脚本如下 people.sql
/*Navicat Premium Data TransferSource Server : localhost_mysql_rootSource Server Type : MySQLSource Server Version : 80036Source Host : localhost:3306Source Schema : ytdemoTarget Server Type : MySQLTarget Server Version : 80036File Encoding : 65001Date: 09/03/2024 14:34:56
*/SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS 0;-- ----------------------------
-- Table structure for people
-- ----------------------------
DROP TABLE IF EXISTS people;
CREATE TABLE people (Id int NOT NULL AUTO_INCREMENT,FirstName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,LastName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,EmailAddress varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,PRIMARY KEY (Id) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 33 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ROW_FORMAT Dynamic;SET FOREIGN_KEY_CHECKS 1;然后往数据库表中插入一些测试数据如下图所示 people的测试数据如下
-- ----------------------------
-- Records of people
-- ----------------------------
INSERT INTO people VALUES (1, Timothy, Corey, TimCoreygmail.com);
INSERT INTO people VALUES (2, Jane, Simth, JSmithapple.com);
INSERT INTO people VALUES (3, Sue, Storm, SueStorm126.com);
INSERT INTO people VALUES (4, Bob, McClure, BobMClumicrosoft.com);
INSERT INTO people VALUES (5, Mary, Jones, maryJonesgoogle.com);
INSERT INTO people VALUES (6, Ray, Stone, rayStone163.com);
INSERT INTO people VALUES (7, 操, 曹, caocaoneusoft.com);
INSERT INTO people VALUES (8, 权, 孙, sunquanhuawei.com);
INSERT INTO people VALUES (9, 备, 刘, liubeiqq.com);
INSERT INTO people VALUES (10, 飞, 张, zhangfenqq.com);
INSERT INTO people VALUES (11, 羽, 关, guanyuqq.com);
INSERT INTO people VALUES (12, 亮, 诸葛, zhugeliangqq.com);
INSERT INTO people VALUES (13, 郎, 王, wanglang126.com);
INSERT INTO people VALUES (14, 肃, 鲁, lusu163.com);
INSERT INTO people VALUES (15, 云, 赵, zhaoyunqq.com);
INSERT INTO people VALUES (16, 超, 马, machaoqq.com);
INSERT INTO people VALUES (17, 纯, 马, machunalibaba.com);
INSERT INTO people VALUES (18, 万翼, 马, mawanyiqq.com);
INSERT INTO people VALUES (19, 禅, 刘, liushanqq.com);
INSERT INTO people VALUES (20, 懿, 司马, simayineusoft.com);
INSERT INTO people VALUES (21, 昭, 司马, simazhaoneusoft.com);
INSERT INTO people VALUES (22, 炎, 司马, simayanneusoft.com);
INSERT INTO people VALUES (23, 相如, 司马, simaxiangruhan.com);
INSERT INTO people VALUES (24, 德宗, 司马, simadezongneusoft.com);
INSERT INTO people VALUES (25, 峰, 欧阳, ouyangfengqq.com);
INSERT INTO people VALUES (26, 克, 欧阳, ouyangkeqq.com);
INSERT INTO people VALUES (27, 超风, 梅, machaofeng126.com);
INSERT INTO people VALUES (28, 志, 徐, xuzhigmail.com);
INSERT INTO people VALUES (29, 万宗, 毛, maowanzongchina.com);
INSERT INTO people VALUES (30, 人凤, 毛, maorenfengtaiwan.com);
INSERT INTO people VALUES (31, 爱天, 毛, maaitianchina.com);
INSERT INTO people VALUES (32, 于铭, 毛, mayuminchina.com);
INSERT INTO people VALUES (33, 科, 孙, sunkechina.com);
INSERT INTO people VALUES (34, 中山, 孙, sunzhongshanchina.com);
INSERT INTO people VALUES (35, 世凯, 袁, yuanshikaiqing.com);
INSERT INTO people VALUES (36, 袁, 立, yuanligmail.com);
INSERT INTO people VALUES (37, 国西, 李, liguoxiybu.com);
INSERT INTO people VALUES (38, 二, 李, lierdangdang.com);
INSERT INTO people VALUES (39, 丽丽, 叶, yeilihongkong.com);
INSERT INTO people VALUES (40, 婷, 叶, yetingqq.com);
INSERT INTO people VALUES (41, 耳父, 陈, chenerfuchina.com);
INSERT INTO people VALUES (42, 国富, 陈, chenguofutaiwan.com);
INSERT INTO people VALUES (43, 立夫, 陈, chenlifutaiwan.com);
INSERT INTO people VALUES (44, 诚, 陈, chenchentaiwan.com);
INSERT INTO people VALUES (45, 崇禧, 白, baichongxitaiwan.com);
INSERT INTO people VALUES (46, 果夫, 陈, chengefutaiwan.com);
INSERT INTO people VALUES (47, 美龄, 宋, songmeilingtaiwan.com);
INSERT INTO people VALUES (48, 庆龄, 宋, songqinglinchina.com);
INSERT INTO people VALUES (49, 蔼龄, 宋, songailingtaiwan.com);
INSERT INTO people VALUES (50, 祥熙, 孔, kongxiangxigmail.com);
INSERT INTO people VALUES (51, 仕杰, 宋, songshijiehuawei.com);
INSERT INTO people VALUES (52, 天赐, 张, zhangtiancihuawei.com);
INSERT INTO people VALUES (53, 峰, 乔, qiaofengxiaomi.com);
INSERT INTO people VALUES (54, 誉, 段, duanyugmail.com);
INSERT INTO people VALUES (55, 竹, 虚, xuzhugmail.com);
INSERT INTO people VALUES (56, 容复, 慕, morongfugmail.com);
INSERT INTO people VALUES (57, 植, 曹, caozhineusoft.com);
INSERT INTO people VALUES (58, 丕, 曹, caopineusoft.com);
INSERT INTO people VALUES (59, 冲, 曹, caochongneusoft.com);
INSERT INTO people VALUES (60, 爽, 曹, caoshuangneusoft.com);
INSERT INTO people VALUES (61, 真, 曹, caozhenneusoft.com);
INSERT INTO people VALUES (62, 睿, 曹, caoruineusoft.com);
INSERT INTO people VALUES (63, 伟庭, 曹, caoweitingneusoft.com);
INSERT INTO people VALUES (64, 值, 王, wangzhisony.com);
INSERT INTO people VALUES (65, 双双, 陈, chenshuangshuanggmail.com);
INSERT INTO people VALUES (66, 介石, 蒋, jiangzhongzhentaiwan.com);
INSERT INTO people VALUES (67, 经国, 蒋, jiangjingguotaiwan.com);
INSERT INTO people VALUES (68, 纬国, 蒋, jiangweiguotaiwan.com);
INSERT INTO people VALUES (69, 万安, 蒋, jiangwanantaiwan.com);
INSERT INTO people VALUES (70, 方良, 蒋, jiangfangliangtaiwan.com);
INSERT INTO people VALUES (71, 英, 蒋, jiangyingchina.com);
INSERT INTO people VALUES (72, 学森, 钱, qianxuesenchina.com);
INSERT INTO people VALUES (73, 雪, 林, linxuegmail.com);
INSERT INTO people VALUES (74, 国栋, 林, linguodonggmai.com);
INSERT INTO people VALUES (75, 万里, 蒋, jiangwanligmail.com);
INSERT INTO people VALUES (76, 勃, 周, zhoubohan.com);
INSERT INTO people VALUES (77, 彬彬, 周, zhoubinbinchina.com);
INSERT INTO people VALUES (78, 德旺, 朱, zhudewangchina.com);
INSERT INTO people VALUES (79, 正田, 朱, zhuzhengtiangmail.com);
INSERT INTO people VALUES (80, 元璋, 朱, zhuyuanzhangming.com);
INSERT INTO people VALUES (81, 允炆, 朱, zhuyunwenming,com);
INSERT INTO people VALUES (82, 标, 朱, zhubiaoming.com);
INSERT INTO people VALUES (83, 棣, 朱, zhuliming.com);
INSERT INTO people VALUES (84, 高炽, 朱, zhugaochiming.com);
INSERT INTO people VALUES (85, 高煦, 朱, zhugaoxuming.com);
INSERT INTO people VALUES (86, 瞻基, 朱, zhuzhanjiming.com);
INSERT INTO people VALUES (87, 厚照, 朱, zhuhouzhaoming.com);
INSERT INTO people VALUES (88, 秀, 刘, liuxiuhan.com);
INSERT INTO people VALUES (89, 璋, 刘, liuzhanghan.com);
INSERT INTO people VALUES (90, 莉莉, 刘, liulilihan.com);
INSERT INTO people VALUES (91, 志, 刘, liuzhihan.com);
INSERT INTO people VALUES (92, 屈氂, 刘, liuqumaohan.com);
INSERT INTO people VALUES (93, 梦, 刘, liumenghan.com);
INSERT INTO people VALUES (94, 凤凤, 张, zhangfengfeng126.com);
INSERT INTO people VALUES (95, 美丽, 刘, liumeili126.com);
INSERT INTO people VALUES (96, 靖, 郭, guojing163.com);
INSERT INTO people VALUES (97, 蓉, 黄, huangrong163.com);
INSERT INTO people VALUES (98, 芙, 郭, guofu163.com);
INSERT INTO people VALUES (99, 襄, 郭, guoxiang163.com);
INSERT INTO people VALUES (100, 美美, 郭, guomeimei163.com);
INSERT INTO people VALUES (101, 克, 欧阳, ouyangke126.com);
INSERT INTO people VALUES (102, 康, 杨, yangkang126.com);
INSERT INTO people VALUES (103, 佳伟, 沈, shenjiawei126.com);
INSERT INTO people VALUES (104, 逍遥, 李, lixiaoyaogmail.com);
INSERT INTO people VALUES (105, 正淳, 段, duanzhengchungmail.com);
INSERT INTO people VALUES (106, 星竹, 李, lixingzhugmail.com);
INSERT INTO people VALUES (107, 紫, 阿, azi163.com);
INSERT INTO people VALUES (108, 朱, 阿, azhu163.com);
INSERT INTO people VALUES (109, 延庆, 段, duanyanqinggmail.com);
INSERT INTO people VALUES (110, 微微, 曹, caoweiwei126.com);
INSERT INTO people VALUES (111, 乐天, 何, heletianybu.com);
INSERT INTO people VALUES (112, 晓婷, 何, hexiaotinggmail.com);5、创建Person实体对象
在C#窗体程序中创建一个Person实体类对应数据库表中的people结构具体代码如下图所示
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace WindowsFormsMySqlDbApp
{public class Person{public int Id { get; set; }public string FirstName { get; set; }public string LastName { get; set; }public string EmailAddress { get; set; }public string FullInfo{get{return $ {FirstName} {LastName} ({EmailAddress});}}}
}6、MySQL连接字符串
我们的MySQL链接字符串为Server127.0.0.1;Port3306;databaseytdemo;user idroot;password123456; 如果在云环境中我们需要将Server对应的本地数据库IP地址127.0.0.1修改为对应的云服务器IP地址user id对应用户名password对应我们的用户密码。 为了方便我们一般不要把MySQL数据库连接字符串硬编码在代码中写死一般可以放在App.Config配置文件中例如
?xml version1.0 encodingutf-8 ?
configurationstartup supportedRuntime versionv4.0 sku.NETFramework,Versionv4.8 //startupconnectionStringsadd nameSampleDB connectionStringServer127.0.0.1;Port3306;databaseytdemo;user idroot;password123456; //connectionStrings
/configuration7、创建MySQL数据库字符串访问类以及查询people表数据的接口
对应的MySQL连接字符串访问类Helper如下
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace WindowsFormsMySqlDbApp
{public static class Helper{public static string ConnectVal(string name){return ConfigurationManager.ConnectionStrings[name].ConnectionString;}}
}另外我们创建一个MySQLDataAccess类用于连接和访问MySQL数据库具体的实现代码如下
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using MySql.Data.MySqlClient;namespace WindowsFormsMySqlDbApp
{public class MySQLDataAccess{//public static string connStr Server127.0.0.1;Port3306;databaseytdemo;user idroot;password123456;; /// summary/// 根据lastName获取所有的人员信息列表/// /summary/// param namelastName姓/param/// returns/returnspublic ListPerson GetPersonList(string lastName){using (IDbConnection connection new MySqlConnection(Helper.ConnectVal(SampleDB))){//string searchSql select * from people where LastNameLastName;//return connection.QueryPerson(searchSql, new { LastName lastName }).ToList();var personList connection.QueryPerson($select * from people where LastName {lastName}).ToList();return personList;}}/// summary/// 往数据库表people中插入一条记录/// /summary/// param namefirstName/param/// param namelastName/param/// param nameemailAdress/parampublic void InsertPerson(string firstName, string lastName, string emailAddress){using (IDbConnection connection new MySqlConnection(Helper.ConnectVal(SampleDB))){//string insertSql $INSERT INTO people(FirstName, LastName, emailAddress) VALUES ({firstName}, {lastName}, {emailAdress});;//connection.Execute(insertSql);Person newPerson new Person { FirstName firstName, LastName lastName, EmailAddress emailAddress };string insertSql INSERT INTO people(FirstName, LastName, emailAddress) VALUES (FirstName, LastName, EmailAddress);;connection.Execute(insertSql, newPerson);}}}
}
8、在查询按钮实现函数中实现具体的查询逻辑
具体的界面和查询逻辑实现代码如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;namespace WindowsFormsMySqlDbApp
{public partial class DashBoardForm : Form{ListPerson people new ListPerson(); // 查询结果列表public DashBoardForm(){InitializeComponent();// 更新绑定内容UpdateBinding();}/// summary/// 刷新ListBox中的数据将peopleFoundListBox和peopl列表数据进行绑定并显示FullInfo字段内容/// /summarypublic void UpdateBinding(){peopleFoundListBox.DataSource people;peopleFoundListBox.DisplayMember FullInfo;}/// summary/// search按钮单击响应处理函数/// /summary/// param namesender/param/// param namee/paramprivate void searchButton_Click(object sender, EventArgs e){// 创建MySQLDataAccess类对象MySQLDataAccess dataAccess new MySQLDataAccess();// 根据UI窗体用户输入的LastName字段调用dataAccess对象的GetPersonList接口从people表查询数据people dataAccess.GetPersonList(lastNameTextBox.Text);// 更新绑定内容将people查询结果展示在peopleFoundListBox中UpdateBinding();}/// summary/// insert按钮响应处理函数/// 功能接收用户输入往people数据表中插入记录/// /summary/// param namesender/param/// param namee/paramprivate void insertDataButton_Click(object sender, EventArgs e){MySQLDataAccess dataAccess new MySQLDataAccess();// 插入一条数据记录到people表中dataAccess.InsertPerson(firstNameInsTextBox.Text, lastNameInsTextBox.Text, emailAddressInsTextBox.Text);// 插入数据之后将界面中的记录数据清空firstNameInsTextBox.Text ;lastNameInsTextBox.Text ;emailAddressInsTextBox.Text ;}}
}
9、运行程序
代码完成之后我们运行程序结果下图所示
四、参考资料
https://www.nuget.org/packages/MySql.Data/https://www.nuget.org/packages/Dapperhttps://github.com/mysql-net/MySqlConnectorhttps://github.com/DapperLib/Dapperhttps://github.com/ccf19881030/WindowsFormsMySqlDbApp
