C# 增 @ 删 @ 改 @ 查

最新推荐文章于 2020-09-29 16:28:39 发布

曾經的小馮同學

最新推荐文章于 2020-09-29 16:28:39 发布

阅读量776

点赞数

分类专栏： C# ASP ASP.NET 文章标签： c# listview string dataset textbox sql

本文链接：https://blog.csdn.net/sxfenglei/article/details/5431264

版权

C# ASP ASP.NET 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

唉~~~！终于把增删改查这几步做完了。。。

休息下

这是各版块的代码，配有我写的注释：

/// <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=ASUS//SQLEXPRESS;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=ASUS//SQLEXPRESS;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=ASUS//SQLEXPRESS;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=ASUS//SQLEXPRESS;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=ASUS//SQLEXPRESS;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=ASUS//SQLEXPRESS;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;