ADO.Net中DataTable的应用
1.思维导图
2.知识点描述
DataTable对象表示保存在本机内存中的表,它提供了对表中行列数据对象的各种操作。可以直接将数据从数据库填充到DataTable对象中,也可以将DataTable对象添加到现有的DataSet对象中。在断开连接的方式下,DataSet对象提供了和关系数据库一样的关系数据模型,代码中可以直接访问DataSet对象中的DataTable对象,也可以添加、删除、修改DataTable对象。可以通过dataGridView控件来实现。
(1)创建DataTable对象
①通过DataTable类的构造函数创建DataTable对象
DataTable table = new DataTable();
②通过DataSet的Tables对象的Add方法创建DataTable对象
DataSet dataset = new DataSet(); DataTable table = dataset.Tables.Add("表名");
(2)调用DataTable对象的Column中的Add方法添加列
(3)在DataTable对象中创建行
由于DataTable对象的每一行都是一个DataRow对象,所以创建行时可以利用DataTable对象的NewRow方法创建一个DataRow对象,并设置新行中各列的数据,然后利用Add方法将DataRow对象添加到表中。
(4)将SQL server数据库中的表填充到DataTable
通过DateAdapter对象的Fill方法将SQL server数据库中的表填充到DataTable对象中
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); sqlDataAdapter.SelectCommand = sqlCommand; this.表名(自己命名) = new DataTable(); sqlConnection.Open(); sqlDataAdapter.Fill(this.表名);
3.应用举例
数据表之分页
(1)设置每页大小和当前页
this.PageSize = 8; this.CurrentPageNo = 1;
(2)连接数据库,设置列
SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = "Server=(local);Database=DB_Equipment;Integrated Security=sspi"; SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = "select i.EquipmentNo,i.EquipmentName,i.Quantity,s.Name,i.Date from tb_inventory as i join tb_supplier as s on i.SupplierNo=s.No where 1=0;"; SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); sqlDataAdapter.SelectCommand = sqlCommand; this.inventoryTable = new DataTable(); this.inventoryTable.TableName = "Inventory"; sqlConnection.Open(); sqlDataAdapter.Fill(this.inventoryTable); DataColumn rowIdColumn = new DataColumn(); rowIdColumn.ColumnName = "RowID"; rowIdColumn.DataType = typeof(int); rowIdColumn.AutoIncrement = true; rowIdColumn.AutoIncrementSeed = 1; rowIdColumn.AutoIncrementStep = 1; this.inventoryTable.Columns.Add(rowIdColumn); sqlCommand.CommandText = "select i.EquipmentNo,i.EquipmentName,i.Quantity,s.Name,i.Date from tb_inventory as i join tb_supplier as s on i.SupplierNo=s.No"; sqlDataAdapter.Fill(this.inventoryTable); sqlConnection.Close();
(3)筛选行、筛选页
this.MaxPageNo = this.inventoryTable.Rows.Count / this.PageSize + 1; this.CurrentPageView = new DataView(); this.CurrentPageView.Table = this.inventoryTable; this.CurrentPageView.Sort = "RowID ASC"; this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize + " AND RowID <=" + this.CurrentPageNo * this.PageSize; this.dgv_inventory.DataSource = this.CurrentPageView; this.dgv_inventory.Columns["RowID"].Visible = false;
(4)上一页按钮单击事件
private void btn_PreviosPage_Click(object sender, EventArgs e) { if (this.CurrentPageNo > 1) { this.CurrentPageNo--; } this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize + " AND RowID <=" + this.CurrentPageNo * this.PageSize; }
(5)下一页按钮单击事件
private void btn_NextPage_Click(object sender, EventArgs e) { if (this.CurrentPageNo < this.MaxPageNo) { this.CurrentPageNo++; } this.CurrentPageView.RowFilter = "RowID >" + (this.CurrentPageNo - 1) * this.PageSize + " AND RowID <=" + this.CurrentPageNo * this.PageSize; }
(6)运行结果