思维导图
1.实现类似下拉框的功能
首先需创建两个连接,以连接两张表。此处以连接科室\部门表和调用表为例,代码如下:
SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = "Server=(local);Database=DB_Equipment;Integrated Security=sspi"; SqlCommand sqlCommand = new SqlCommand(); SqlCommand sqlCommand2 = new SqlCommand(); sqlCommand.Connection = sqlConnection; sqlCommand2.Connection = sqlConnection; sqlCommand.CommandText = "SELECT * FROM tb_department;"; sqlCommand2.CommandText = "SELECT tb_use.No,tb_inventory.EquipmentName,tb_use.Quantity,tb_user.UserName,tb_use.DepartmentNo,tb_use.Date FROM tb_use,tb_inventory,tb_user WHERE tb_inventory.EquipmentNo=tb_use.EquipmentNo AND tb_user.UserNo=tb_use.UserNo;"; SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(); sqlDataAdapter.SelectCommand = sqlCommand; DataTable departmentTable = new DataTable(); SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter(); sqlDataAdapter2.SelectCommand = sqlCommand2; DataTable useTable = new DataTable(); sqlConnection.Open(); sqlDataAdapter.Fill(departmentTable); sqlDataAdapter2.Fill(useTable); sqlConnection.Close();
由于此处连接的tb_use的列都是编号,但是需显示名称比较容易查看,所以得利用sql语句以查询其他表编号所对应的名。
由于显示在数据表的列得转换成中文,所以可以直接在数据表的编辑列中设置,也可编写下列代码:
this.dataGridView1.DataSource = useTable; this.dataGridView1.Columns["No"].HeaderText = "编号"; this.dataGridView1.Columns["EquipmentName"].HeaderText = "器材名"; this.dataGridView1.Columns["Quantity"].HeaderText = "调用数量"; this.dataGridView1.Columns["UserName"].HeaderText = "调用人姓名"; this.dataGridView1.Columns["Date"].HeaderText = "调用日期";
实现下拉框功能,首先得设置下拉框的数据源为科室表,然后设置下拉框列的显示成员为(科室数据表的)名称(列),然后设置下拉框列的值成员为(科室数据表的)编号(列),代码如下:
departmentColumn.DataSource = departmentTable;
departmentColumn.DisplayMember = "Name"; departmentColumn.ValueMember = "No";
由于数据表中不需要显示科室编号,所以需将科室编号列隐藏,接着设置下拉框列的数据属性名称为(调用数据表的)科室编号(列),显示顺序,自动调整列宽模式为填充,然后将下拉框列加入数据网格视图的列集合,代码如下:
this.dataGridView1.Columns["DepartmentNo"].Visible = false; departmentColumn.DataPropertyName = "DepartmentNo"; departmentColumn.DisplayIndex = 4; departmentColumn.AutoSizeMode=DataGridViewAutoSizeColumnMode.Fill; this.dataGridView1.Columns.Add(departmentColumn);
运行结果如下:
2.查询搜索
(1)为窗体类定义如下的实例变量:
SqlDataAdapter da;
DataSet ds;
(2)编写自定义方法Fill(),向DataGridView控件中填充数据。同上库存表中保存的是供应商编号,而窗体上要显示的是供应商名称,所以同样用sql语句连接两张表。为了实现根据选择的查询条件,拼接产生sql语句。然后通过SqlDataAdapter的Fill方法填充数据集,获取数据,最后设置DataGridView控件的DataSourse属性。具体代码如下:
private void Fill() { SqlConnection sqlConnection = new SqlConnection(); sqlConnection.ConnectionString = "Server=(local);Database=DB_Equipment;Integrated Security=sspi"; string sql = "select EquipmentNo,EquipmentName,Quantity,Name,Date from tb_inventory,tb_supplier where tb_inventory.SupplierNo=tb_supplier.No"; if (cbo_condition.Text == "器材编号") sql += " and EquipmentNo like '%"+ txb_search.Text.Trim()+"%'"; else if(cbo_condition.Text=="器材名") sql += " and EquipmentName like '%"+ txb_search.Text.Trim()+"%'"; sql += " order by tb_inventory.EquipmentNo desc"; using (SqlConnection conn = new SqlConnection(sqlConnection.ConnectionString)) { da = new SqlDataAdapter(sql, conn); ds = new DataSet(); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } }
(3)在窗体的Load事件中调用Fill方法,即可在进入此窗体时就显示数据集。
(4)在“查询”按钮的单击事件中调用Fill方法,即可按照选择的查询条件查询所需要的信息。
以查询编号为例,运行结果如下: