C#之MySql新增

2019-11-14 08:54:25

字體：大中小

來源：轉載

供稿：網友

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; } }}