C#之MySql新增

2019-11-14 09:30:27

字体：大中小

来源：转载

供稿：网友

1.新建一个项目，windows窗体应用程序

2.创建一张User表,包括字段有：编号、用户名、密码

这里写图片描述

插入一条数据

这里写图片描述

3.新建一个UserForm窗体,格式如下

这里写图片描述

4.新建一个类库（SqlHelper）

对常用操作进行封装，这样会减少工作量和代码量（别忘了添加引用，这里是MySQL.data）

using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;namespace SqlHelper{ public static class helper { public static string conStr = "Server=localhost;user=root;passWord=admin;database=test"; public static DataTable GetList(string sql) { using (MySqlConnection myCon = new MySqlConnection(conStr)) { MySqlDataAdapter adapter = new MySqlDataAdapter(sql, myCon); DataTable table = new DataTable(); adapter.Fill(table); return table; } } public static int Insert(string sql, params MySqlParameter[] ps) { using (MySqlConnection myCon = new MySqlConnection(conStr)) { MySqlCommand cmd = new MySqlCommand(sql, myCon); cmd.Parameters.AddRange(ps); myCon.Open(); return cmd.ExecuteNonQuery(); } } }}

5.窗体加载是显示数据，在右侧填写完数据后，点击【添加】按钮，将会新增一条数据，并刷新列表。

using MySql.Data.MySqlClient;using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;namespace WindowsFormsapplication1{ public partial class UserForm : Form { public UserForm() { InitializeComponent(); } PRivate void UserForm_Load(object sender, EventArgs e) { LoadUser(); } //刷新 private void LoadUser() { string sql = "select * from user"; DataTable dt = SqlHelper.helper.GetList(sql); List<User> UserList = new List<User>(); foreach (DataRow row in dt.Rows) { UserList.Add(new User() { user_no = Convert.ToInt32(row["user_no"]), user_name = Convert.ToString(row["user_name"]), user_pwd = Convert.ToString(row["user_pwd"]), type = Convert.ToInt32(row["type"]) }); } dataGridView1.DataSource = UserList; } //格式化 private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e) { if (e.ColumnIndex == 3) { switch (e.Value.ToString()) { case "0": e.Value="经理"; break; case"1": e.Value = "员工"; break; } } else if (e.ColumnIndex == 2) { e.Value = "******"; } } private void btnAdd_Click(object sender, EventArgs e) { User user = new User() { user_name = tbUserName.Text, user_pwd = tbUserPwd.Text, type = rbClerk.Checked ? 1 : 0 }; if (AddUser(user)) { LoadUser(); MessageBox.Show("新增成功"); } else { MessageBox.Show("新增失败，请重新尝试"); } this.tbUserName.Text = this.tbUserPwd.Text = string.Empty; this.rbClerk.Checked = this.rbManager.Checked = false; } //新增用户 private bool AddUser(User u) { string sql = "insert into user (user_name,user_pwd,type) values(@user_name,@user_pwd,@type)"; MySqlParameter[] ps ={ new MySqlParameter("@user_name",u.user_name), new MySqlParameter("@user_pwd",u.user_pwd), new MySqlParameter("@type",u.type) }; return SqlHelper.helper.Insert(sql, ps) > 0; } //清除操作 private void btnClear_Click(object sender, EventArgs e) { this.tbUserName.Text = this.tbUserPwd.Text = string.Empty; this.rbClerk.Checked = false; this.rbManager.Checked = false; } }}