配置App.config
下图连接的Mysql数据库
<connectionStrings>
<add name="conStr" connectionString="server=localhost;user id=root;password=123456;database=studentmanage;Charset=utf8;"/>
</connectionStrings>
sqlservel无密码登录如下:
Data Source:连接名
Initial Catalog:数据库名称
string conStr = "Data Source=pc-34\\sqlexpress;Initial Catalog=studentmanage;Integrated Security=True";
数据库文件
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for studentinfo
-- ----------------------------
DROP TABLE IF EXISTS `studentinfo`;
CREATE TABLE `studentinfo` (
`num` int NOT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of studentinfo
-- ----------------------------
INSERT INTO `studentinfo` VALUES (1, '如花', '0');
INSERT INTO `studentinfo` VALUES (2, '如花', '1');
INSERT INTO `studentinfo` VALUES (3, '如花', '1');
INSERT INTO `studentinfo` VALUES (4, '如花', '0');
-- ----------------------------
-- Table structure for studentscore
-- ----------------------------
DROP TABLE IF EXISTS `studentscore`;
CREATE TABLE `studentscore` (
`num` int NOT NULL,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` int NULL DEFAULT NULL,
PRIMARY KEY (`num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of studentscore
-- ----------------------------
INSERT INTO `studentscore` VALUES (1, '如花', 100);
INSERT INTO `studentscore` VALUES (2, '如花', 99);
INSERT INTO `studentscore` VALUES (3, '如花', 98);
SET FOREIGN_KEY_CHECKS = 1;
窗体设计一:非断开式访问数据库
主体程序代码:
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;
using System.Configuration;
using MySql.Data.MySqlClient;
namespace WindowsFormsApp5
{
public partial class Form1 : Form
{
private int current = 1;
string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
public Form1()
{
InitializeComponent();
}
private void ShowCurrent()
{
string sql = "select * from studentInfo where num=" + current;
MySqlConnection con = new MySqlConnection(conStr);
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
txtNum.Text = reader.GetString(0).ToString();
txtName.Text = reader.GetString(1);
String sex = reader.GetString(2);
if (sex == "0")
{
male.Checked = true;
}
else
{
female.Checked = true;
}
}
reader.Close();
con.Close();
}
private void btnPrevious_Click(object sender, EventArgs e)
{
current--;
ShowCurrent();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection(conStr);
MySqlCommand cmd = new MySqlCommand("delete from studentinfo where num=@num",con);
cmd.Parameters.AddWithValue("@num", Convert.ToInt32(txtNum.Text));
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("成功删除一条数据!");
}
private void btnInsert_Click(object sender, EventArgs e)
{
String sex = "0";
if (female.Checked)
{
sex = "1";
}
MySqlConnection con = new MySqlConnection(conStr);
MySqlCommand cmd = new MySqlCommand("insert into studentinfo(num,name,sex) values(@num,@name,@sex)", con);
cmd.Parameters.AddWithValue("@num", null);
cmd.Parameters.AddWithValue("@name", txtName.Text);
cmd.Parameters.AddWithValue("@sex", sex);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("成功添加一条数据!");
}
private void Form1_Load(object sender, EventArgs e)
{
ShowCurrent();
}
private void btnNext_Click(object sender, EventArgs e)
{
current++;
ShowCurrent();
}
}
}
在引用中导入System.Configuration
窗体设计二:断开式访问数据库
主体代码:
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;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
namespace WindowsFormsApp5
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
MySqlConnection con;
MySqlDataAdapter da;
DataSet ds;
private void btnSelect_Click(object sender, EventArgs e)
{
con = new MySqlConnection(conStr);
da = new MySqlDataAdapter("select * from studentscore", con);
ds = new DataSet();
da.Fill(ds, "studentscore");
dgvStudentScore.DataSource = ds.Tables["studentscore"];
}
private void btnInsert_Click(object sender, EventArgs e)
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(da);
DataRow r1 = ds.Tables["studentscore"].NewRow();
r1[0] = txtNum.Text;
r1[1] = txtName.Text;
r1[2] = txtScore.Text;
ds.Tables[0].Rows.Add(r1);
da.Update(ds, "studentscore");
dgvStudentScore.DataSource = ds.Tables["studentscore"];
}
private void dgvStudentScore_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
txtNum.Text = dgvStudentScore.CurrentRow.Cells["num"].Value.ToString();
txtNum.Text = dgvStudentScore.CurrentRow.Cells["name"].Value.ToString();
txtNum.Text = dgvStudentScore.CurrentRow.Cells["score"].Value.ToString();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(da);
DataRowCollection rows = ds.Tables["studentscore"].Rows;
DataRow row;
for (int i = 0; i < rows.Count; i++)
{
row = rows[i];
if (row["num"].ToString() == txtNum.Text)
{
row["score"] = Int32.Parse(txtScore.Text);
}
}
dgvStudentScore.DataSource = ds.Tables["studentscore"];
da.Update(ds, "studentscore");
}
private void btnDelete_Click(object sender, EventArgs e)
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(da);
DataRowCollection rows = ds.Tables["studentscore"].Rows;
DataRow row;
for (int i = 0; i < rows.Count; i++)
{
row = rows[i];
if (row["num"].ToString() == txtNum.Text)
{
row.Delete();
}
}
da.Update(ds, "studentscore");
dgvStudentScore.DataSource = ds.Tables["studentscore"];
}
}
}