ADO.Net中DataTable的应用

最新推荐文章于 2024-02-27 09:01:04 发布

我不叫草稿纸

最新推荐文章于 2024-02-27 09:01:04 发布

阅读量372

点赞数

本文链接：https://blog.csdn.net/lqb15060120720/article/details/83592817

版权

一、知识点描述

1、DataTable 表示一个内存内关系数据的表，可以独立创建和使用，也可以由其他 .NET Framework 对象使用，最常见的情况是作为 DataSet 的成员使用。

2、创建DataTable
①使用相应的DataTable构造函数创建DataTable对象。
②使用Add方法将其添加到DataTable对象的Tables集合中，将其添加到DataSet中。
③使用DataAdapter对象的Fill方法方法在DataSet中创建。

3、按名称引用表中的列、关系和约束是区分大小写的。因此，一个表中可以存在两个或两个以上名称相同(但大小写不同)的列、关系或约束。

4、在DataSet中创建DataTable之后，可以对数据表中的数据进行增、删、改、查等操作。
二、思维导图
在这里插入图片描述

三、运行截图
在这里插入图片描述
四、示例代码
1.载入

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;
            this.dgv_Score.Columns[this.dgv_Score.Columns.Count - 1].AutoSizeMode =                         
               DataGridViewAutoSizeColumnMode.Fill;
            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);

2.更新

 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 equipTable = (DataTable)this.dgv_Score.DataSource;                                  
            sqlConnection.Open();                                                                           
            int rowAffected = sqlDataAdapter.Update(equipTable);                                          
            sqlConnection.Close();                                                                          
            MessageBox.Show("更新" + rowAffected.ToString() + "行。");

3.查询

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