C#中的 增 、删、 改、 查功能
以下是各大板块的功能 有注释。。。
/// <summary>
/// 对话框返回值
///
///这是一个退出按键的方法，用来确定用户是否真的要退出
/// </summary>
private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
{
//定义 DialogResult 变量 用以存储对话框返回值
DialogResult exitForm;
//对话框
exitForm = MessageBox.Show("冯磊老大您确定要离开我吗？","退出系统",MessageBoxButtons.OKCancel,MessageBoxIcon.Information);
//判断
if (exitForm == DialogResult.OK) //使用 DialogResult 属性判断该按钮的返回值是否为 "确定"
{
Application.Exit();//退出系统
}
}
/// <summary>
/// 创建 DBManager 类
///
/// 在 DBManager 类中创建数据库教程连接
/// </summary>
class DBManager
{
//连接数据库字符串 和 实例化 connection 对象
private static string sqlconString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True";
public static SqlConnection con = new SqlConnection(sqlconString);
}
/// <summary>
/// 创建 User 类
///
/// 在 User 类中创建 用于存储交换的变量
/// </summary>
class User
{
public string userID = "";
public string userName = "";
public string userAge = "";
public string userGongZi = "";
public string userJiGuan = "";
}
/// <summary>
/// ExecuteScalar()方法 返回单个值
/// 核心 SELECT COUNT(*) FROM Student
/// 核心 com.ExecuteScalar();
/// </summary>
//定义数据库连接字符串 和 connection 对象
string sqlString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MySchool;Integrated Security=True";
SqlConnection con = new SqlConnection(sqlString);
//打开数据库
con.Open();
//拼SQL语句 和 创建 command 对象
string sql = "SELECT COUNT(*) FROM Student ";
SqlCommand com = new SqlCommand(sql,con);
// 执行 SQL 查询 使用ExecuteScaler()方法 返回一个读取值
int num = (int)com.ExecuteScalar();
//将返回值格式化后存入变量中 后输出
string message = string.Format("Student表中共有{0}条学员信息！", num);
MessageBox.Show(message, "查询结果", MessageBoxButtons.OK, MessageBoxIcon.Information);//然后将该变量输出
//关闭数据库
con.Close();
/// <summary>
/// 将数据加载到 comboBox 控件中
/// 核心 select studentName from student
/// 核心 SqlDataReader dataReader = com.ExecuteReader();
/// 核心 string studentName = "";
/// 核心 while (dataReader.Read()){
/// 核心 studentName = (string)dataReader[0];
/// 核心 comboBox1.Items.Add(studentName);
/// 核心 }
/// </summary>
//定义数据库字符 和 connection 对象
string sqlString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MySchool;Integrated Security=True";
SqlConnection con = new SqlConnection(sqlString);
//定义sql语句 和 command 对象
string sql = "select studentName from student";
SqlCommand com = new SqlCommand(sql, con);
//打开数据库
con.Open();
//执行sql读取 使用dataReader对象
SqlDataReader dataReader = com.ExecuteReader();
//循环读取每一条数据 添加到comboBox1 中
string studentName = "";//将每条读取存入这个变量 用以每次想comboBox1中添加
while (dataReader.Read())
{
studentName = (string)dataReader[0];//从第0项开始读取
comboBox1.Items.Add(studentName);//添加每条读取到 comboBox1 中
}
//关闭读取 和 数据库
dataReader.Close();
con.Close();
/// <summary>
/// 将数据加载到 listView 控件中
/// 核心 SqlDataReader dataReader = com.ExecuteReader();
/// 核心 string id = "";
/// 核心 string adminUser = "";
/// 核心 string adminPwd = "";
/// 核心 while (dataReader.Read()){
/// 核心 id = dataReader["adminId"].ToString();
/// 核心 adminUser = dataReader["LoginId"].ToString();
/// 核心 adminPwd = dataReader["LoginPwd"].ToString();
/// 核心 ListViewItem liv = new ListViewItem(adminUser);
/// 核心 liv.Tag = (int)dataReader["adminId"];
/// 核心 listView1.Items.Add(liv);
/// 核心 liv.SubItems.AddRange(new string[] { adminPwd });
/// 核心 }
/// </summary>
private void FillListView()
{
//清空listview
listView1.Items.Clear();
//定义数据库字符 和 connection 对象
string sqlconString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MySchool;Integrated Security=True";
SqlConnection con = new SqlConnection(sqlconString);//上面定义过
//拼SQL 和 command 对象
string sql = "select * from admin";//上面定义过
SqlCommand com = new SqlCommand(sql, con);//上面定义过
con.Open();
//执行SQL语句
SqlDataReader dataReader = com.ExecuteReader();
//定义存储变量
string id = "";
string adminUser = "";
string adminPwd = "";
//循环读取数据库 数据 然后存入变量中
while (dataReader.Read())
{
//将数据库中的数据读取到变量中
id = dataReader["adminId"].ToString();//拿到ID
adminUser = dataReader["LoginId"].ToString();
adminPwd = dataReader["LoginPwd"].ToString();
//创建 ListView 项
ListViewItem liv = new ListViewItem(adminUser);//将 adminUser 加载到第一项中
liv.Tag = (int)dataReader["adminId"];
listView1.Items.Add(liv);//向listView1中添加一个新项
liv.SubItems.AddRange(new string[] { adminPwd });//将剩余项 adminPwd 添加到listView1中
}
dataReader.Close();
con.Close();
}
/// <summary>
/// 单击 listView 时 将本行数据存入 user 类中的变量中
/// 核心 定义 user 类
/// 核心 使用 listView1.SelectedItems[0].SubItems[1].Text.ToString(); 拿到数据 并存入user 类中(需要实例化)
/// </summary>
private void listView1_Click(object sender, EventArgs e)
{
//实例化 User 类
User us = new User();
//将鼠标点击的 listView 行的数据存入 user 类中的变量中
us.userName = listView1.SelectedItems[0].SubItems[1].Text.ToString();
us.userAge = listView1.SelectedItems[0].SubItems[2].Text.ToString();
us.userGongZi = listView1.SelectedItems[0].SubItems[3].Text.ToString();
us.userJiGuan = listView1.SelectedItems[0].SubItems[4].Text.ToString();
//将 user类中的数据在放到 textBox 控件中
txtName.Text = us.userName;
txtAge.Text = us.userAge;
txtGongZi.Text = us.userGongZi;
txtJiGuan.Text = us.userJiGuan;
}
/// <summary>
/// 增
/// 核心 string sql = string.Format("insert into admin (LoginId,LoginPwd) values ('{0}','{1}')",textBox1.Text,textBox2.Text);
/// 核心 int num =com.ExecuteNonQuery();
/// </summary>
//判断非空
if(textBox1.Text.Trim()=="")
{
MessageBox.Show("冯磊老大你叫啥？");
textBox1.Focus();//得到焦点
return;//如果为空则返回空 即结束
}else if(textBox2.Text.Trim()=="")
{
MessageBox.Show("冯磊老大告诉我密码？");
textBox2.Focus();
return;
}
//定义数据库连接 和 connection 对象
string sqlconString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MySchool;Integrated Security=True";
SqlConnection con = new SqlConnection(sqlconString );
//拼SQL 和 command 对象
string sql = string.Format("insert into admin (LoginId,LoginPwd) values ('{0}','{1}')",textBox1.Text,textBox2.Text);
SqlCommand com = new SqlCommand(sql,con );
con.Open();
//添加数据到数据库 使用ExecutenNonQuery方法
int num =com.ExecuteNonQuery();
//如果添加成功侧提示
if (num==1)
{
MessageBox.Show("冯磊老大添加成功！");
}
else {
MessageBox.Show("哦哦~~冯磊老大添加失败！","",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
con.Close();
//在加载 一遍 listView 可以将创建的listView 抽成方法 如 fillListView();
fillListView();
/// <summary>
/// 删
/// 核心 if(listView1.SelectedItems.Count==0)
/// 核心 string sql = string.Format("delete from admin where AdminId='{0}'",(int)listView1.SelectedItems[0].Tag);
/// 核心 int test = com.ExecuteNonQuery();
/// </summary>
//确保用户选择了一个学员才执行修改操作
if(listView1.SelectedItems.Count==0)
{
MessageBox.Show("冯磊老大你不告诉我删除那个我怎么删");
return;
}else{
//定义数据库连接字符串 和 connection 对象
string sqlconString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MySchool;Integrated Security=True";
SqlConnection con = new SqlConnection(sqlconString);
//拿ID
string sql = string.Format("delete from admin where AdminId='{0}'",(int)listView1.SelectedItems[0].Tag);
SqlCommand com = new SqlCommand(sql,con);
con.Open();
//执行SQL
int test = com.ExecuteNonQuery();
//提示删除成功与否
if (test > 0)
{
MessageBox.Show("冯磊老大我成功的给你灭了它！");
}
else {
MessageBox.Show("冯磊老大我灭它失败！");
}
con.Close();
}
/* listView */
FillListView();//加载FillListView();
/// <summary>
/// 右键 删除
/// 核心 listView1.SelectedItems[0].Tag.ToString()
/// </summary>
DBManager.con.Open();
string sql = string.Format("delete from yuangong where id='{0}'",listView1.SelectedItems[0].Tag.ToString());//listView1.SelectedItems[0].Tag.ToString(); //拿到ID
SqlCommand com = new SqlCommand(sql,DBManager.con);
int shanChu = com.ExecuteNonQuery();
DBManager.con.Close();
/* listView */
FillListView();//加载FillListView();
/// <summary>
/// 改
/// 核心 (int)listView1.SelectedItems[0].Tag
/// 核心 update YuanGong set name='{0}',age='{1}',gongzi='{2}',jiguan='{3}' where id={4}
/// 核心 int Run = com.ExecuteNonQuery();
/// </summary>
SqlConnection sql = string.Format("update YuanGong set name='{0}',age='{1}',gongzi='{2}',jiguan='{3}' where id={4}", txtName.Text, txtAge.Text, txtGongZi.Text, txtJiGuan.Text, (int)listView1.SelectedItems[0].Tag);
SqlCommand com = new SqlCommand(sql, DBManager.con);
DBManager.con.Open();
int Run = com.ExecuteNonQuery();
if (Run == 1)
{
DBManager.con.Close();
FillListView();
}
else
{
MessageBox.Show("冯磊老大修改失败！", "提示框", MessageBoxButtons.OK, MessageBoxIcon.Error);
DBManager.con.Close();
}
/// <summary>
/// 查
/// 核心 listView1.Items.Clear();
/// 核心 if (!datareader.HasRows)
/// 核心 SqlDataReader datareader = com.ExecuteReader();
/// </summary>
listView1.Items.Clear();
string id = "";
string name = "";
string age = "";
string gongzi = "";
string jiguan = "";
sql = string.Format("select * from yuangong where name='{0}' or age='{1}' or gongzi='{2}' or jiguan='{3}'", txtName.Text, txtAge.Text, txtGongZi.Text, txtJiGuan.Text);
com = new SqlCommand(sql, DBManager.con);
SqlDataReader datareader = com.ExecuteReader();
if (!datareader.HasRows)
{
MessageBox.Show("哦哦~~~冯磊老大没有在国防部查到你要的数据!");
datareader.Close();
DBManager.con.Close();
return;
}
else {
while(datareader.Read())
{
id=datareader["ID"].ToString();
name=datareader["name"].ToString();
age=datareader["age"].ToString();
gongzi=datareader["gongzi"].ToString();
jiguan=datareader["jiguan"].ToString();
ListViewItem liv = new ListViewItem(id);
liv.Tag=(int)datareader["ID"];
listView1.Items.Add(liv);
liv.SubItems.AddRange(new string []{name,age,gongzi,jiguan});
}
datareader.Close();
DBManager.con.Close();
}
//============================================================================================
MessageBox.Show(listView1.SelectedItems[0].Tag.ToString()); //拿到ID
MessageBox.Show(listView1.SelectedItems[0].SubItems[0].Text); //拿到第一列的单元格的值
//============================================================================================
/// <summary>
/// 创建 全局变量
/// </summary>
//实例化 dataSet 和 声明 dataAdapter
DataSet dataSet = new DataSet();
SqlDataAdapter dataAdapter;
//声明字符
string sql ="";
/// <summary>
/// 创建 fillDataGridView方法
/// </summary>
private void fillDataGridView()
{
//拼基本 SQL 语句
sql = "select id as 编号,name as 姓名,age as 年龄,gongzi as 工资,jiguan as 籍贯 from yuangong ";
//实例化 dataAdapter
dataAdapter = new SqlDataAdapter(sql, DBManager.con);
//填充 dataSet
dataAdapter.Fill(dataSet, "yuanGong");
//将数据绑定到 dataGridView中
dataGridView1.DataSource = dataSet.Tables["yuanGong"];
}
/// <summary>
/// 筛选 数据
/// </summary>
//刷新基本 SQL 语句
sql = "select id as 编号,name as 姓名,age as 年龄,gongzi as 工资,jiguan as 籍贯 from yuanGong ";
//拼接 条件SQL 语句
switch(comboBox1.Text)
{
case "全部":
break;
case "河南":
sql = sql + "where jiguan='河南'";
break;
case "浙江":
sql = sql + "where jiguan='浙江'";
break;
case "陕西":
sql = sql + "where jiguan='陕西'";
break;
case "河北":
sql = sql + "where jiguan='河北'";
break;
case "青岛":
sql = sql + "where jiguan='青岛'";
break;
}
//清空 dataSet
dataSet.Tables["yuanGong"].Clear();
//实例化 dataAdapter
dataAdapter = new SqlDataAdapter(sql,DBManager.con);
//填充 dataSet
dataAdapter.Fill(dataSet, "yuanGong");
//将数据绑定到 dataGridView中
dataGridView1.DataSource = dataSet.Tables["yuanGong"];
/// <summary>
/// 更新 数据到数据源
/// </summary>
// 确认修改
DialogResult result = MessageBox.Show("冯磊老大确定要保存修改吗？", "操作提示",MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.OK)
{
// 自动生成更新数据用的命令
SqlCommandBuilder comBui = new SqlCommandBuilder(dataAdapter);
// 将修改过的数据提交到数据库
dataAdapter.Update(dataSet, "yuanGong");
}
//清空 dataSet
dataSet.Tables["yuanGong"].Clear();
//重新加载数据到 dataGridView 中
fillDataGridView();
///窗体间传值
/*公共类*/
//创建类
public class readEmail
{
public static string userName="";
public static string emailName = "";
public static string emailContent = "";
}
/*from1*/
//将鼠标点击的 listView 行的数据存入 read 类中的变量中
readEmail.userName = listView1.SelectedItems[0].SubItems[0].Text.ToString();
readEmail.emailName = listView1.SelectedItems[0].SubItems[1].Text.ToString();
readEmail.emailContent = listView1.SelectedItems[0].SubItems[2].Text.ToString();
//打开新窗体
emailWinFrom emailwinfrom = new emailWinFrom();
emailwinfrom.ShowDialog();
/*from2*/
//将内容显示在新窗体的textbox 中
textBox2.Text = readEmail.userName;
textBox3.Text = readEmail.emailName;
textBox1.Text = readEmail.emailContent;