第七周学习笔记--如何在前端界面操作数据表
如何在前端界面操作数据表
一、知识点描述
1、DataGridView（数据网格视图）：绑定数据源，连接数据表。
2、载入数据表
①普通载入数据表
②让数据表里的列以下拉框的形式表示出来
下拉框（ComboBox）：显示一个可编辑的文本框，其中包含一个允许值下拉列表。
3、更新数据表：直接在DataGridView载入的数据表中修改表的内容并实现更新。
4、从数据表里精确搜索某项内容
①根据Button的Click事件显示搜索内容
②根据TextBox的TextChanged事件显示搜索内容
5、配合excel表格插入数据表内容
二、思维导图
三、示例代码和效果截图
1、载入、更新数据表
①示例代码
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;
namespace 护士工作站
{
public partial class 病人信息 : Form
{
public 病人信息()
{
InitializeComponent();
//以下6行为数据网格视图的排版设置
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;
}
//botton1_Click事件下的代码为载入sql数据表代码
private void button1_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=EduBase1;Integrated Security=sspi";
SqlCommand sqlCommand = new SqlCommand();
SqlCommand sqlCommand2 = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand2.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM tb_Bed;"; //查询下拉框数据来源的表格
sqlCommand2.CommandText = "SELECT * FROM tb_Patient;"; //查询载入数据来源的表格
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
DataTable bedTable = new DataTable();
SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter();
sqlDataAdapter2.SelectCommand = sqlCommand2;
DataTable patientTable = new DataTable();
sqlConnection.Open();
sqlDataAdapter.Fill(bedTable);
sqlDataAdapter2.Fill(patientTable);
sqlConnection.Close();
this.dgv_Score.Columns.Clear();
this.dgv_Score.DataSource = patientTable;
//以下代码为指定每一列对应的表头文本
this.dgv_Score.Columns["No"].HeaderText = "病人编号";
this.dgv_Score.Columns["Name"].HeaderText = "姓名";
this.dgv_Score.Columns["Gender"].HeaderText = "性别";
this.dgv_Score.Columns["Address"].HeaderText = "地址";
this.dgv_Score.Columns["ID"].HeaderText = "身份证号码";
this.dgv_Score.Columns["Phone"].HeaderText = "电话号码";
this.dgv_Score.Columns["Time"].HeaderText = "入院日期";
this.dgv_Score.Columns["MedicalRecordNo"].HeaderText = "病案号";
this.dgv_Score.Columns["BedNo"].Visible = false; //将网格视图的指定病床号设为不可见
this.dgv_Score.Columns[this.dgv_Score.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
DataGridViewComboBoxColumn bedColumn = new DataGridViewComboBoxColumn(); //声明并实例化数据网格视图下拉框列，用于设置病人的病床；
//以下9行代码是数据网格视图下拉框的相关设置
bedColumn.Name = "Bed";
bedColumn.HeaderText = "床位";
bedColumn.DataSource = bedTable;
bedColumn.DisplayMember = "No";
bedColumn.ValueMember = "No";
bedColumn.DataPropertyName = "BedNo";
bedColumn.DisplayIndex = 9;
bedColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
this.dgv_Score.Columns.Add(bedColumn);
}
//botton2_Click事件下的代码为更新数据表代码
private void button2_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=EduBase1;Integrated Security=sspi";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText =
"UPDATE tb_Patient"
+ " SET Name=@Name,Gender=@Gender,Address=@Address,ID=@ID,Phone=@Phone,Time=@Time,MedicalRecordNo=@MedicalRecordNo,BedNo=@BedNo"
+ " WHERE No=@No;";
sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 0, "Name");
sqlCommand.Parameters.Add("@Gender", SqlDbType.Bit, 0, "Gender");
sqlCommand.Parameters.Add("@Address", SqlDbType.VarChar, 0, "Address");
sqlCommand.Parameters.Add("@ID", SqlDbType.Char, 0, "ID");
sqlCommand.Parameters.Add("@Phone", SqlDbType.Char, 0, "Phone");
sqlCommand.Parameters.Add("@Time", SqlDbType.Date, 0, "Time");
sqlCommand.Parameters.Add("@MedicalRecordNo", SqlDbType.Char, 0, "MedicalRecordNo");
sqlCommand.Parameters.Add("@BedNo", SqlDbType.Int, 0, "BedNo");
sqlCommand.Parameters.Add("@No", SqlDbType.Char, 10, "No");
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 button3_Click(object sender, EventArgs e)
{
护士工作站首页 首页 = new 护士工作站首页();
this.Hide();
首页.ShowDialog(this);
}
}
}
②效果截图
（1）点击载入，将tb_Patient载入。
（2）床位号以下拉列表显示。
（3）将张果的床位号由一号改为3号，点击更新，再次点击载入，这时张果的病床号已被修改。
2、从数据表里精确搜索某项内容
①示例代码
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;
namespace 护士工作站
{
public partial class 医嘱查询 : Form
{
private DataTable MedicalAdviceTable; //私有字段：医嘱数据表
private DataView MedicalAdviceViewByName; //私有字段：按名称排序的医嘱数据视图
public 医嘱查询()
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
this.dgv_MedicalAdvice.AllowUserToAddRows = false;
this.dgv_MedicalAdvice.RowHeadersVisible = false;
this.dgv_MedicalAdvice.BackgroundColor = Color.White;
this.dgv_MedicalAdvice.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells;
}
//botton1_Click事件下的代码为载入医嘱代码
private void button1_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=EduBase1;Integrated Security=sspi";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM tb_MedicalAdvice;";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
this.MedicalAdviceTable = new DataTable(); sqlConnection.Open();
sqlDataAdapter.Fill(this.MedicalAdviceTable);
sqlConnection.Close();
this.MedicalAdviceViewByName = new DataView();
this.MedicalAdviceViewByName.Table = this.MedicalAdviceTable;
this.MedicalAdviceViewByName.Sort = "PatientName ASC";
this.dgv_MedicalAdvice.Columns.Clear();
this.dgv_MedicalAdvice.DataSource = this.MedicalAdviceTable;
this.dgv_MedicalAdvice.Columns["No"].HeaderText = "编号";
this.dgv_MedicalAdvice.Columns["PatientName"].HeaderText = "病人姓名";
this.dgv_MedicalAdvice.Columns["DoctorName"].HeaderText = "主治医生姓名";
this.dgv_MedicalAdvice.Columns["Content"].HeaderText = "医嘱内容";
this.dgv_MedicalAdvice.Columns["PatientPinyin"].Visible = false;
this.dgv_MedicalAdvice.Columns[this.dgv_MedicalAdvice.Columns.Count - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
}
//botton2_Click事件下的代码为按医嘱编号搜索相应医嘱
private void button2_Click(object sender, EventArgs e)
{
DataRow searchResultRow = this.MedicalAdviceTable.Rows.Find(this.医嘱编号.Text.Trim());
DataTable searchResultTable = this.MedicalAdviceTable.Clone();
searchResultTable.ImportRow(searchResultRow);
this.dgv_MedicalAdvice.DataSource = searchResultTable;
}
//botton3_Click事件下的代码为按病人名字搜索相应医嘱
private void button3_Click(object sender, EventArgs e)
{
DataRowView[] searchResultRowViews =
this.MedicalAdviceViewByName.FindRows(this.病人名字.Text.Trim());
DataTable searchResultTable = this.MedicalAdviceTable.Clone();
foreach (DataRowView dataRowView1 in searchResultRowViews)
{
searchResultTable.ImportRow(dataRowView1.Row);
}
this.dgv_MedicalAdvice.DataSource = searchResultTable;
}
private void 医嘱查询_Load(object sender, EventArgs e)
{
}
//拼音_TextChanged事件下的代码为按TextBox框中病人名字拼音的输入搜索相应医嘱
private void 拼音_TextChanged(object sender, EventArgs e)
{
DataRow[] searchResultRows =
this.MedicalAdviceTable.Select("PatientPinyin LIKE '%" + this.拼音.Text.Trim() + "%'");
DataTable searchResultTable = this.MedicalAdviceTable.Clone();
foreach (DataRow row in searchResultRows)
{
searchResultTable.ImportRow(row);
}
this.dgv_MedicalAdvice.DataSource = searchResultTable;
}
}
}
②相关截图
（1）点击载入，将tb_MedicalAdvice载入。
（2）在根据编号搜索和根据病人名字搜索相应的TextBox框输入你要搜索的数据行，点击搜索即可找到你想要的数据。
（3）在根据病人拼音搜索下面的TextBox框输入相应的病人拼音，即可触发TextChanged事件，得到你想要的数据。
3、配合excel表格插入数据表内容
①首先在Excel表格里编辑内容
②把Excel转载成csv格式，即Excel另存为其他格式，保存类型为csv（若保存过程中出现阻碍一律点是）
③在sql建立tb_MedicalAdvice并输入BULK INSERT tb_MedicalAdvice语句，使excel数据表中的内容插入tb_MedicalAdvice。
CREATE TABLE tb_MedicalAdvice
(No Char(20) PRIMARY KEY,
PatientName Varchar(10),
PatientPinyin VARCHAR(50) NULL,
DoctorName Varchar(10),
Content Char(200),
);
BULK INSERT tb_MedicalAdvice
FROM 'C:\MedicalAdvice.csv'
WITH
(FIELDTERMINATOR=','
,ROWTERMINATOR='\n'
,FIRSTROW=2);
③在VS中输入相应语句即可引用数据表的内容（详细可见上一点从2、数据表里精确搜索某项内容）
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=EduBase1;Integrated Security=sspi";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM tb_MedicalAdvice;";
