实验五
实验目的
熟悉掌握数据库的基本功能与SQL语言的使用,掌握数据库应用的基本方法
掌握将查询结果表与数据集绑定,并在相应控件上同步显示查询结果的方法
实验内容
①实验9-1:
为例9.3添加一项新的功能,单击“个人明细”按钮,弹出一个简单的个人信息浏览界面,它支持向前和向后翻页显示每一个学生的信息。
跟着学习
设计界面
①按照【例9.3】说明完成功能
【例9.3】利用数据集机制,使用DataAdapter对象填充DataSet的方法,离线访问XSCJDB数据库。
●程序完整代码如下
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;
namespace shiyan5
{
public partial class Form1 : Form
{
string strcon = @"Data Source=(localdb)\ProjectsV12;Initial Catalog=XSCJDB;Integrated Security=true";
DataSet myst = new DataSet();
SqlDataAdapter myda;
public Form1()
{
InitializeComponent();
btnSeach_Click(null, null);
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void btnInsert_Click(object sender, EventArgs e)
{
SqlConnection sqlcon = new SqlConnection(strcon);
try
{
string stuID = txtStuID.Text.Trim();
string stuName = txtName.Text.Trim();
string stuXB;
if(RbtnMale.Checked)
{
stuXB = "男";
}
else
{
stuXB = "女";
}
string stuBirthday = dateTimePicker1.Value.ToShortDateString().Split(' ')[0];
string stuMajor = txtMajor.Text.Trim();
string stuCredit = txtCredit.Text.Trim();
SqlCommand command = new SqlCommand();
command.CommandText="INSERT INTO XSB(XH,XM,XB,CSRQ,ZY,ZXF) VALUES('"+stuID+"','"+stuName+"','"+stuXB+"','"+stuBirthday+"','"+stuMajor+"','"+stuCredit+"')";
command.CommandType = CommandType.Text;
command.Connection = sqlcon;
sqlcon.Open();
myda = new SqlDataAdapter(command);
myda.Fill(myst, "XSB");
MessageBox.Show("插入成功!", "消息", MessageBoxButtons.OK);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
sqlcon.Close();
}
}
private void btnSeach_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcon = new SqlConnection(strcon))
{
sqlcon.Open();
string sql = "SELECT * FROM XSB";
SqlCommand command = new SqlCommand(sql, sqlcon);
myda = new SqlDataAdapter(command);
myst.Tables.Clear();
myda.Fill(myst, "XSB");
dgvAllStu.DataSource = myst.Tables["XSB"];
}
}
}
}
●运行截图如下
②在原来【例9.3】项目的主窗体中增加一个“个人明细”按钮。
右键单击项目,选择“添加”→“Windows窗体”,窗体名称Name属性设置为SelfDetail,设置新窗体的FormBorderStyle属性为FixedDialog,添加控件。
查询学生信息并绑定数据集
为“个人明细”按钮编写事件代码如下:
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcon = new SqlConnection(strcon))
{
sqlcon.Open();
string sql = "select XH as '学号' , XM as '姓名' , CSRQ as '生日' from XSB ";
SqlDataAdapter myda = new SqlDataAdapter(sql, sqlcon);
DataSet myst = new DataSet();
myda.Fill(myst, "SelfInfo");
frmSelf = new SelfDetail();
frmSelf.mDataSet = myst;
frmSelf.BindingContext = this.BindingContext;
frmSelf.DataBindings.Add("Text", myst, "SelfInfo.姓名");
frmSelf.lblStudentNo.DataBindings.Add("Text", myst, "SelfInfo.学号");
frmSelf.tbBirthday.DataBindings.Add("Text", myst, "SelfInfo.生日");
frmSelf.Show();
}
}
编写控制逻辑
为窗体类添加一个控制按钮状态的ButtonStateControl()方法:
private void SelfDetail_Load(object sender, EventArgs e)
{
ButtonStateControl();
}
private void ButtonStateControl()
{
btnFirst.Enabled = true;
btnNext.Enabled = true;
btnPrev.Enabled = true;
btnLast.Enabled = true;
if (this.BindingContext[mDataSet, "SelfInfo"].Position == 0)
{
btnPrev.Enabled = false;
btnLast.Enabled = false;
}
if (this.BindingContext[mDataSet, "SelfInfo"].Position == (this.BindingContext[mDataSet,"SelfInfo"].Count-1))
{
btnNext.Enabled = false;
btnFirst.Enabled = false;
}
}
对四个按钮进行编码,实现指针移动,代码如下:
private void btnFirst_Click(object sender, EventArgs e)
{
this.BindingContext[mDataSet, "SelfInfo"].Position = 0;
ButtonStateControl();
}
private void btnPrev_Click(object sender, EventArgs e)
{
this.BindingContext[mDataSet, "SelfInfo"].Position -= 1;
ButtonStateControl();
}
private void btnNext_Click(object sender, EventArgs e)
{
this.BindingContext[mDataSet, "SelfInfo"].Position += 1;
ButtonStateControl();
}
private void btnLast_Click(object sender, EventArgs e)
{
this.BindingContext[mDataSet, "SelfInfo"].Position = this.BindingContext[mDataSet, "SelfInfo"].Count - 1;
ButtonStateControl();
}
自己思考
修改程序,以编程实现DataSet和DataAdapter等数据控件的创建。
修改程序,实现当双击DATa Grid控件的每一行记录时,弹出该学生个人明细窗口的功能。
●在主窗体类中代码如下:
public static SelfDetail frmsd;
private void dgvAllStu_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
dgvAllStu objectstu =Service.GetSelfInfo(Convert.ToInt32(dgvAllStu.CurrentRow.Cells[0].Value));
actionFlag = 1;
if(frmsd ==null)
{
frmsd = new dgvAllStu(objectstu,actionFlag ) ;
frmsd.Show();
}
else
{
frmsd.Activate();
frmsd.WindowState = FormWindowState.Normal;
}
}
使用上面介绍的方法,在明细窗口中增加显示数据库中某学生的其他一些个人专项信息,如学生所在专业、总学分、备注信息等。
修改private void button1_Click(object sender, EventArgs e)方法,如下:
private void button1_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcon = new SqlConnection(strcon))
{
sqlcon.Open();
string sql = "select XH as '学号' , XM as '姓名' , CSRQ as '生日' ,ZY as '专业',ZXF as '学分' from XSB ";
SqlDataAdapter myda = new SqlDataAdapter(sql, sqlcon);
DataSet myst = new DataSet();
myda.Fill(myst, "SelfInfo");
frmSelf = new SelfDetail();
frmSelf.mDataSet = myst;
frmSelf.BindingContext = this.BindingContext;
frmSelf.DataBindings.Add("Text", myst, "SelfInfo.姓名");
frmSelf.lblStudentNo.DataBindings.Add("Text", myst, "SelfInfo.学号");
frmSelf.tbBirthday.DataBindings.Add("Text", myst, "SelfInfo.生日");
frmSelf.lbMajor.DataBindings.Add("Text", myst, "SelfInfo.专业");
frmSelf.xuefen.DataBindings.Add("Text", myst, "SelfInfo.学分");
frmSelf.Show();
}
}
实验结果如下:
实验总结
这次实验让我收益匪浅,掌握将查询结果表与数据集绑定,并在相应控件上同步显示查询结果的方法,学到了很多新知识,发现自己在理论知识上的不足,通过实验很好的锻炼了自己,提高了自身的实践能力和思考能力,并且很好的解决了自己对于理论知识的学习中存在的知识盲点。