C#中的 增 、删、 改、 查功能

作者:袖梨 2022-06-25

C#中的 增 、删、 改、 查功能
以下是各大板块的功能  有注释。。。

///

­

/// 对话框返回值 ­

/// ­

///这是一个退出按键的方法,用来确定用户是否真的要退出 ­

///

­

        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();//退出系统 ­

            } ­

        } ­

­

­

­

­

­

­

///

­

/// 创建 DBManager 类 ­

/// ­

/// 在 DBManager 类中创建数据库教程连接 ­

///

­

  class DBManager ­

   { ­

    //连接数据库字符串 和 实例化 connection 对象 ­

    private static string sqlconString = "Data Source=ASUSSQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True"; ­

    public static SqlConnection con = new SqlConnection(sqlconString); ­

   } ­

­

­

­

­

­

­

///

­

/// 创建 User 类 ­

/// ­

/// 在 User 类中创建 用于存储交换的变量 ­

///

­

  class User ­

   { ­

    public string userID = ""; ­

    public string userName = ""; ­

    public string userAge = ""; ­

    public string userGongZi = ""; ­

    public string userJiGuan = ""; ­

   } ­

­

///

­

        /// ExecuteScalar()方法 返回单个值 ­

         ­

        /// 核心 SELECT COUNT(*) FROM Student ­

        /// 核心 com.ExecuteScalar(); ­

///

­

//定义数据库连接字符串 和 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(); ­

­

­

­

­

­

///

­

        ///  将数据加载到 comboBox 控件中 ­

         ­

        /// 核心 select studentName from student ­

        /// 核心 SqlDataReader dataReader = com.ExecuteReader(); ­

        /// 核心 string studentName = ""; ­

        /// 核心 while (dataReader.Read()){ ­

        /// 核心  studentName = (string)dataReader[0]; ­

        /// 核心 comboBox1.Items.Add(studentName); ­

        /// 核心 } ­

///

­

            //定义数据库字符 和 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(); ­

­

­

­

­

­

///

­

        /// 将数据加载到 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 }); ­

        /// 核心 } ­

///

­

   ­

  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(); ­

        }     ­

­

­

­

­

­

­

///

­

  /// 单击 listView 时 将本行数据存入 user 类中的变量中 ­

        /// 核心 定义 user 类 ­

        /// 核心 使用 listView1.SelectedItems[0].SubItems[1].Text.ToString(); 拿到数据 并存入user 类中(需要实例化) ­

///

­

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

        } ­

­

­

­

­

­

­

///

­

        ///  增 ­

         ­

        /// 核心 string sql = string.Format("insert into admin (LoginId,LoginPwd) values ('{0}','{1}')",textBox1.Text,textBox2.Text); ­

        /// 核心 int num =com.ExecuteNonQuery(); ­

///

­

   //判断非空 ­

            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(); ­

­

­

­

­

­

///

­

        ///  删 ­

         ­

        /// 核心 if(listView1.SelectedItems.Count==0) ­

        /// 核心 string sql = string.Format("delete from admin where AdminId='{0}'",(int)listView1.SelectedItems[0].Tag); ­

        /// 核心 int test = com.ExecuteNonQuery(); ­

///

­

   //确保用户选择了一个学员才执行修改操作 ­

            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(); ­

­

­

///

­

  /// 右键 删除 ­

   ­

  /// 核心 listView1.SelectedItems[0].Tag.ToString() ­

///

­

   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(); ­

­

­

­

­

­

­

///

­

        ///  改 ­

         ­

        /// 核心 (int)listView1.SelectedItems[0].Tag ­

        /// 核心 update YuanGong set name='{0}',age='{1}',gongzi='{2}',jiguan='{3}' where id={4} ­

        /// 核心 int Run = com.ExecuteNonQuery(); ­

///

­

   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(); ­

   } ­

­

­

­

­

­

///

­

        ///  查 ­

         ­

        /// 核心 listView1.Items.Clear(); ­

        /// 核心 if (!datareader.HasRows) ­

        /// 核心 SqlDataReader datareader = com.ExecuteReader(); ­

///

­

   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);  //拿到第一列的单元格的值 ­

­

­

//============================================================================================ ­

­

­

///

­

/// 创建 全局变量 ­

///

­

        //实例化 dataSet 和 声明 dataAdapter ­

        DataSet dataSet = new DataSet(); ­

        SqlDataAdapter dataAdapter; ­

        //声明字符 ­

        string sql =""; ­

         ­

         ­

         ­

         ­

­

///

­

/// 创建 fillDataGridView方法 ­

///

­

        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"]; ­

        } ­

         ­

         ­

         ­

         ­

­

///

­

/// 筛选 数据 ­

///

­

            //刷新基本 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"]; ­

­

­

­

­

­

///

­

/// 更新 数据到数据源 ­

///

­

            // 确认修改 ­

            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;

 

相关文章

精彩推荐