学习笔记(2019.4.15)ADO.NET中的DataTable的应用
DataTable是一个临时保存数据的网格虚拟表(表示内存中数据的一个表)
主要属性
Clear:清除所有数据的 DataTable。
Copy:复制该 DataTable 的结构和数据。
Clone:克隆 DataTable 的结构,包括所有 DataTable 架构和约束。
ImportRow:将 DataRow 复制到 DataTable 中,保留任何属性设置以及初始值和当前值。
思维导图
如下:
示例代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace DatabaseHomework
{
public partial class Drugs_Form : Form
{
private DataTable DrugsTable;
private DataView DrugsViewByName;
public Drugs_Form()
{
InitializeComponent();
}
private void btn_Read_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("select* from tb_Drugs",conn);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = cmd;
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
this.DrugsTable = new DataTable();
sqlDataAdapter.Fill(DrugsTable);
conn.Close();
this.DrugsViewByName = new DataView();
DrugsViewByName.Table = DrugsTable;
DrugsViewByName.Sort = "Name ASC";
dgv_Drugs.Columns.Clear();
dgv_Drugs.DataSource = DrugsTable;
dgv_Drugs.Columns["No"].HeaderText = "编号";
dgv_Drugs.Columns["Name"].HeaderText = "姓名";
dgv_Drugs.Columns["Price"].HeaderText = "单价";
dgv_Drugs.Columns["Pinyin"].Visible = false;
DataGridViewComboBoxColumn preCourseColumn = new DataGridViewComboBoxColumn();
preCourseColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
this.dgv_Drugs.Columns[this.dgv_Drugs.Columns.Count - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
}
private void Drugs_Form_Load(object sender, EventArgs e)
{
this.dgv_Drugs.AllowUserToAddRows = false; //数据网格视图不允许用户添加行;
this.dgv_Drugs.RowHeadersVisible = false; //数据网格视图的行标题不可见;
this.dgv_Drugs.BackgroundColor = Color.White; //数据网格视图的背景色设为白色;
this.dgv_Drugs.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells;
}
private void txt_Pinyin_TextChanged(object sender, EventArgs e)
{
DataRow[] searchResultRows = this.DrugsTable.Select("Pinyin LIKE '%" + txt_Pinyin.Text.Trim() + "%'");
DataTable searchResultTable = this.DrugsTable.Clone();
foreach (DataRow row in searchResultRows)
{
searchResultTable.ImportRow(row);
}
dgv_Drugs.DataSource = searchResultTable;
}
private void btn_GNo_Click(object sender, EventArgs e)
{
DataRow searchResultRow = this.DrugsTable.Rows.Find(txt_GNo.Text.Trim());
DataTable searchResultTable = DrugsTable.Clone();
searchResultTable.ImportRow(searchResultRow);
dgv_Drugs.DataSource = searchResultTable;
}
private void btn_GName_Click(object sender, EventArgs e)
{
DataRowView[] searchResultRowViews =
this.DrugsViewByName.FindRows(txt_GName.Text.Trim()); //借助本窗体的按名称排序的课程数据视图的方法FindRows,根据排序列(即课程名称)快速查找相应课程;由于该列并非主键,可能返回多行查询结果,故返回数据行视图数组;数据行视图数组不能直接作为数据源,需转为列表后方可作为数据源;
DataTable searchResultTable = this.DrugsTable.Clone(); //借助本窗体的课程数据表的方法Clone,创建相同架构的空表,用于保存搜索结果所在数据行;
foreach (DataRowView dataRowView in searchResultRowViews) //遍历搜索结果所在数据行视图数组;
{
searchResultTable.ImportRow(dataRowView.Row); //通过每条数据行视图的属性Row获取相应的数据行,并导入数据表;
}
this.dgv_Drugs.DataSource = searchResultTable; //将数据网格视图的数据源设为搜索结果数据表;
}
}
}
首先在Gridview中导入数据,然后再用适配器SqlDataAdapter放Gridview的数据,接着创建一个表DataTable,然后SqlDataAdapter.Fill(DataTable);
按拼音,姓名,编号查询的时候都应该创建一个新表来存放数据,即Clone()出原来表的没有数据的框架,接着用foreach循环ImportRow一行行加入数据,最后然Gridview的DataSource=这个克隆出来的新表。
示例截图
如下: