如何在前端操作数据表

一、思维导图

二、知识点描述
1、DataGridView（数据网格视图）：绑定数据源，连接数据表。
2、载入数据表：将绑定的数据源的数据以列表的形式加载出来，
3、下拉框（ComboBox）：显示一个可编辑的文本框，其中包含一个允许值下拉列表。
4、更新数据表：在DataGridView载入的数据表中修改表的内容并由代码实现更新。
5、从数据表里精确搜索某项内容
①根据Button的Click事件显示搜索内容
②根据TextBox的TextChanged事件显示搜索内容
6、运行效果

三、代码示例

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;
                                                                                                           
using System.Data.SqlClient;
using System.Configuration;                                                                              

namespace 医院设备管理
{
    
    public partial class frm_equip : Form
    {
        private DataTable equipTable;

        private DataTable DepartmentTable;

        private DataView equipViewByName;

        public frm_equip()
        {
            InitializeComponent();
            this.StartPosition = FormStartPosition.CenterScreen;                                            
            this.dgv_Score.AllowUserToAddRows = false;                                                      
            this.dgv_Score.RowHeadersVisible = false;                                                      
            this.dgv_Score.BackgroundColor = Color.White;                                                  
            this.dgv_Score.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;                                                   
        }

        private void btn_Load_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                           
            sqlConnection.ConnectionString =
                ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;                             
            SqlCommand sqlCommand1 = new SqlCommand();                                                       
            sqlCommand1.Connection = sqlConnection;                                                          
            sqlCommand1.CommandText = "SELECT * FROM tb_equip;";                                          
            SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();                                           
            sqlDataAdapter1.SelectCommand = sqlCommand1;                                                     
            SqlCommand sqlCommand2 = new SqlCommand();                                                    
            sqlCommand2.Connection = sqlConnection;                                                          
            sqlCommand2.CommandText = "SELECT * FROM tb_Department;";                                           
            SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter();                                          
            sqlDataAdapter2.SelectCommand = sqlCommand2;                                                      
            sqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;                         
            sqlDataAdapter2.MissingSchemaAction = MissingSchemaAction.AddWithKey;                                              
                                                                                                                 
            this.equipTable = new DataTable();                                                      
            sqlConnection.Open();                                                                           
            sqlDataAdapter1.Fill(this.equipTable);                                                          
            sqlConnection.Close();                                                                         

            this.DepartmentTable = new DataTable();                                                       
            sqlConnection.Open();                                                                          
            sqlDataAdapter2.Fill(this.DepartmentTable);                                                         
            sqlConnection.Close();
            this.equipViewByName = new DataView();                                                         
            this.equipViewByName.Table = this.equipTable;                                                 
            this.equipViewByName.Sort = "ENO ASC";                                                                          
            this.dgv_Score.Columns.Clear();                                                                
            this.dgv_Score.DataSource = equipTable;                                                       
            this.dgv_Score.Columns["ENO"].HeaderText = "设备编码";                                               
            this.dgv_Score.Columns["E_name"].HeaderText = "设备名称";
            this.dgv_Score.Columns["E_norm"].HeaderText = "规格";           
            
            this.dgv_Score.Columns["FNO"].HeaderText = "生产产商";
            this.dgv_Score.Columns["E_use"].Visible = false;
            this.dgv_Score.Columns["DNO"].Visible = false;
            this.dgv_Score.Columns["remark"].Visible = false;
          
            DataGridViewComboBoxColumn equipColumn = new DataGridViewComboBoxColumn();                      
            equipColumn.Name = "Department";                                                             
            equipColumn.HeaderText = " 所在科室 ";                                                        
            equipColumn.DataSource = this.DepartmentTable;                                               
            equipColumn.DisplayMember = "D_name";                                                         
            equipColumn.ValueMember = "DNO";                                                            
            equipColumn.DataPropertyName = "DNO";                                               
            equipColumn.DisplayIndex = 3;                                                               
            //equipColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;                            
            this.dgv_Score.Columns.Add(equipColumn);                                                   
        }
        

        /// 私有方法：点击更新按钮；
        private void btn_Update_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                              
            sqlConnection.ConnectionString =
                ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;                           
            SqlCommand sqlCommand = new SqlCommand();                                                     
            sqlCommand.Connection = sqlConnection;                                                          
            sqlCommand.CommandText =                                                                       
                "UPDATE tb_equip"
                + " SET E_name=@Name,E_norm=@Norm,DNO=@DNO,FNO=@FNO,E_use=@Use"
                + " WHERE ENO=@ENO;";
            sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 0, "E_name");                              
            sqlCommand.Parameters.Add("@Norm", SqlDbType.VarChar, 0, "E_norm");
            sqlCommand.Parameters.Add("@DNO", SqlDbType.VarChar, 0, "DNO");
            sqlCommand.Parameters.Add("@FNO", SqlDbType.VarChar, 0, "FNO");
            sqlCommand.Parameters.Add("@Use", SqlDbType.VarChar, 0, "E_use");
            sqlCommand.Parameters.Add("@ENO", SqlDbType.Char, 10, "ENO");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                        
            sqlDataAdapter.UpdateCommand = sqlCommand;                                                      
            DataTable studentTable = (DataTable)this.dgv_Score.DataSource;                              
            sqlConnection.Open();                                                                           
            int rowAffected = sqlDataAdapter.Update(studentTable);                                          
            sqlConnection.Close();                                                                        
            MessageBox.Show("更新" + rowAffected.ToString() + "行。");                                      
        }

        private void btn_Search_Click(object sender, EventArgs e)
        {
            if (cbo_condition.Text == "按编号查询")
            {
                DataRow searchResultRow = this.equipTable.Rows.Find(this.txb_Search.Text.Trim());         
                DataTable searchResultTable = this.equipTable.Clone();                                        
                searchResultTable.ImportRow(searchResultRow);                                                   
                this.dgv_Score.DataSource = searchResultTable;                                                 
            }
            else
            {
                if (cbo_condition.Text == "按名称查询")
                {
                    DataRow[] searchResultRows =
                    this.equipTable.Select("E_name LIKE '%" + this.txb_Search.Text.Trim() + "%'");             
                    DataTable searchResultTable = this.equipTable.Clone();                                       
                    foreach (DataRow row in searchResultRows)                                                       
                    {          
                        searchResultTable.ImportRow(row);                                                           
                    }
                    this.dgv_Score.DataSource = searchResultTable;                                               
                }
            }
        }
                                                                                                 
}