利用datagridview在前端界面操作数据表
一、知识点描述
DataGridViewRow row = new DataGridViewRow();是创建DataGridView的行对象，
DataGridViewTextBoxCell是单元格的内容是个TextBox，
DataGridViewComboBoxCell是单元格的内容是下拉列表框，
DataGridViewButtonCell是单元格的内容是个按钮，等等。
1、DataGridView 行的用户删除操作的自定义：
1）无条件的限制行删除操作。
默认时，DataGridView 是允许用户进行行的删除操作的。如果设置DataGridView对象的AllowUserToDeleteRows属性为 False 时，用户的行删除操作就被禁止了。
// 禁止DataGridView1的行删除操作。
DataGridView1.AllowUserToDeleteRows = false;
但是，通过 DataGridViewRowCollection.Remove 还是可以进行删除。
补足： 如果 DataGridView 绑定的是
DataView 的话，通过DataView.AllowDelete 也可以控制行的删除。
2）行删除时的条件判断处理。
用户在删除行的时候，将会引发 DataGridView.UserDeletingRow 事件。在这个事件里，可以判断条件并取消删除操作。
// DataGridView1 的 UserDeletingRow 事件
private void DataGridView1_UserDeletingRow( object sender,
DataGridViewRowCancelEventArgs e)
{
// 删除前的用户确认。
if (MessageBox.Show("确认要删除该行数据吗？", "删除确认",
MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
{
// 如果不是 OK，则取消。
e.Cancel = true;
}
}
2、DataGridView 取得或者修改当前单元格的内容：
当前单元格指的是 DataGridView 焦点所在的单元格，它可以通过 DataGridView 对象的 CurrentCell 属性取得。如果当前单元格不存在的时候，返回Nothing(C#是null)
// 取得当前单元格内容
Console.WriteLine(DataGridView1.CurrentCell.Value);
// 取得当前单元格的列 Index
Console.WriteLine(DataGridView1.CurrentCell.ColumnIndex);
// 取得当前单元格的行 Index
二、思维导图
三、示例代码
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 护士工作站2
{
public partial class 药品使用 : Form
{
private DataTable MedicineTable;
private DataTable SelectMedicineTable;
public 药品使用()
{
InitializeComponent();
this.StartPosition = FormStartPosition.CenterScreen;
this.dgv_Medicine.ReadOnly = true;
this.dgv_Medicine.AllowUserToAddRows = false;
//课程数据网格视图不允许用户添加行；
this.dgv_Medicine.RowHeadersVisible = false;
this.dgv_Medicine.BackgroundColor = Color.White;
this.dgv_Medicine.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells;
this.dgv_SelectMedicine.AllowUserToAddRows = false; //已选课程数据网格视图不允许用户添加行；
this.dgv_SelectMedicine.RowHeadersVisible = false; //已选课程数据网格视图的行标题不可见；
this.dgv_SelectMedicine.BackgroundColor = Color.LightBlue;
this.dgv_SelectMedicine.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.AllCells;
}
private void btn_Load_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=Edubase2018;Integrated Security=sspi"; SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText =
"SELECT Kind_no,Drug_name,Ind_price FROM tb_Medicine WHERE Kind_no NOT IN"
+ "(SELECT Kind_no FROM tb_SelectMedicine WHERE AdmissionNo=@AdmissionNo);"; sqlCommand.Parameters.AddWithValue("@AdmissionNo", this.txb_no.Text.Trim());
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand; ；
this.MedicineTable = new DataTable();
this.SelectMedicineTable = new DataTable();
sqlConnection.Open();
sqlDataAdapter.Fill(this.MedicineTable);
sqlCommand.CommandText =
"SELECT C.Kind_no,C.Drug_name,C.Ind_price,C.OrderMedicine"
+ " FROM tb_Medicine AS C JOIN tb_SelectMedicine AS CS ON C.Kind_no=CS.Kind_no"
+ " WHERE AdmissionNo=@AdmissionNo;";
sqlDataAdapter.Fill(this.SelectMedicineTable);
sqlConnection.Close();
this.dgv_Medicine.Columns.Clear();
this.dgv_Medicine.DataSource = this.MedicineTable;
this.dgv_Medicine.Columns["Kind_no"].HeaderText = "编号";
this.dgv_Medicine.Columns["Drug_name"].HeaderText = "名称";
this.dgv_Medicine.Columns["Ind_price"].HeaderText = "价格";
this.dgv_Medicine.Columns[this.dgv_Medicine.Columns.Count - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
this.dgv_SelectMedicine.Columns.Clear(); this.dgv_SelectMedicine.DataSource = this.SelectMedicineTable; this.dgv_SelectMedicine.Columns["Kind_no"].HeaderText = "编号";
this.dgv_SelectMedicine.Columns["Drug_name"].HeaderText = "名称";
this.dgv_SelectMedicine.Columns["Ind_price"].HeaderText = "价格";
this.dgv_SelectMedicine.Columns["OrderMedicine"].HeaderText = "使用药品";
this.dgv_SelectMedicine.Columns[this.dgv_SelectMedicine.Columns.Count - 1].AutoSizeMode =
DataGridViewAutoSizeColumnMode.Fill;
this.lbl_CreditSum.Text =
"共" + this.SelectMedicineTable.Compute("SUM(Ind_price)", "").ToString() + "元";
}
private void btn_Submit_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
"Server=(local);Database=Edubase2018;Integrated Security=sspi";
SqlCommand insertCommand = new SqlCommand(); insertCommand.Connection = sqlConnection;
insertCommand.CommandText =
"INSERT tb_SelectMedicine(AdmissionNo,Kind_no,OrderMedicine)"
+ "VALUES(@AdmissionNo,@Kind_no,@OrderMedicine);";
insertCommand.Parameters.AddWithValue("AdmissionNo", this.txb_no.Text.Trim());
insertCommand.Parameters.Add("@Kind_no", SqlDbType.Char, 10, "Kind_no");
insertCommand.Parameters.Add("@OrderMedicine", SqlDbType.Bit, 0, "OrderMedicine");
SqlCommand updateCommand = new SqlCommand();
updateCommand.Connection = sqlConnection;
updateCommand.CommandText =
"UPDATE tb_SelectMedicine"
+ " SET OrderMedicine=@OrderMedicine"
+ " WHERE AdmissionNo=@AdmissionNo AND Kind_no=@Kind_no;";
updateCommand.Parameters.AddWithValue("@AdmissionNo", this.txb_no.Text.Trim());
updateCommand.Parameters.Add("@Kind_no", SqlDbType.Char, 10, "Kind_no");
updateCommand.Parameters.Add("@OrderMedicine", SqlDbType.Bit, 0, "OrderMedicine");
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.InsertCommand = insertCommand;
sqlDataAdapter.UpdateCommand = updateCommand; sqlConnection.Open();
int rowAffected = sqlDataAdapter.Update(this.SelectMedicineTable);
sqlConnection.Close();
MessageBox.Show("插入" + rowAffected.ToString() + "行。");
}
private void btn_Add_Click(object sender, EventArgs e)
{
if (this.dgv_Medicine.RowCount > 0)
{
DataRow
currentMedicineRow = ((DataRowView)this.dgv_Medicine.CurrentRow.DataBoundItem).Row
, SelectMedicineRow = this.SelectMedicineTable.NewRow();
SelectMedicineRow["Kind_no"] = currentMedicineRow["Kind_no"]; ；
SelectMedicineRow["Drug_name"] = currentMedicineRow["Drug_name"];
SelectMedicineRow["Ind_price"] = currentMedicineRow["Ind_price"];
this.SelectMedicineTable.Rows.Add(SelectMedicineRow);
currentMedicineRow.Delete();
this.lbl_CreditSum.Text =
"共" + this.SelectMedicineTable.Compute("SUM(Ind_price)", "").ToString() + "元";
}
}
private void btn_Remove_Click(object sender, EventArgs e)
{
if (this.dgv_SelectMedicine.RowCount > 0)
{
DataRow selectMedicineRow =
((DataRowView)this.dgv_SelectMedicine.CurrentRow.DataBoundItem).Row;
if (selectMedicineRow.RowState == DataRowState.Added) {
string MedicineNo = selectMedicineRow["Kind_no"].ToString();
DataRow deletedMedicineRow =
this.MedicineTable.Select("Kind_no'" + MedicineNo + "'", "", DataViewRowState.Deleted)[0];
deletedMedicineRow.RejectChanges();
this.SelectMedicineTable.Rows.Remove(selectMedicineRow);
this.lbl_CreditSum.Text =
"共" + this.SelectMedicineTable.Compute("SUM(Ind_price)", "").ToString() + "价格";
}
}
}
四、效果截图
