查询与绑定
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 TopSportsManager
{
public partial class ListTeacher : Form
{
public ListTeacher()
{
InitializeComponent();
searchBind();
setGrid();
}
private void searchBind()
{
string sqlcmd = "select * from Teacher where Teachername Like @Teachername";
SqlParameter[] param = {
new SqlParameter("Teachername","%" + SearchTeacherName.Text + "%")
};
dataGridView1.DataSource = TopSportsManager.db.ExecuteDataview(sqlcmd, param);
清空绑定#region 清空绑定
this.Memo.DataBindings.Clear();
this.Appraisal.DataBindings.Clear();
this.Photo1.DataBindings.Clear();
this.Photo2.DataBindings.Clear();
#endregion
添加绑定#region 添加绑定
this.Memo.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataGridView1.DataSource, "Memo", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, ""));
this.Appraisal.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataGridView1.DataSource, "Appraisal", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, ""));
this.Photo1.DataBindings.Add(new System.Windows.Forms.Binding("Image", this.dataGridView1.DataSource, "Photo1", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, this.Photo1.ErrorImage));
this.Photo2.DataBindings.Add(new System.Windows.Forms.Binding("Image", this.dataGridView1.DataSource, "Photo2", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, this.Photo2.ErrorImage));
#endregion
}
private void setGrid()
{
初始化字段#region 初始化字段
System.Windows.Forms.DataGridViewTextBoxColumn ID = new DataGridViewTextBoxColumn();
ID.DataPropertyName = "ID";
ID.HeaderText = "ID";
ID.Name = "ID";
ID.Resizable = System.Windows.Forms.DataGridViewTriState.True;
ID.Visible = false;
System.Windows.Forms.DataGridViewTextBoxColumn Num = new DataGridViewTextBoxColumn();
Num.DataPropertyName = "Num";
Num.HeaderText = "编号";
Num.Name = "Num";
Num.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn TeacherName = new DataGridViewTextBoxColumn();
TeacherName.DataPropertyName = "TeacherName";
TeacherName.HeaderText = "教练姓名";
TeacherName.Name = "TeacherName";
TeacherName.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Sex = new DataGridViewTextBoxColumn();
Sex.DataPropertyName = "Sex";
Sex.HeaderText = "性别";
Sex.Name = "Sex";
Sex.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Birthday = new DataGridViewTextBoxColumn();
Birthday.DataPropertyName = "Birthday";
Birthday.HeaderText = "生日";
Birthday.Name = "Birthday";
Birthday.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Job = new DataGridViewTextBoxColumn();
Job.DataPropertyName = "Job";
Job.HeaderText = "身份";
Job.Name = "Job";
Job.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn SRecord = new DataGridViewTextBoxColumn();
SRecord.DataPropertyName = "SRecord";
SRecord.HeaderText = "学历";
SRecord.Name = "SRecord";
SRecord.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn MaterItem = new DataGridViewTextBoxColumn();
MaterItem.DataPropertyName = "MaterItem";
MaterItem.HeaderText = "主授项目";
MaterItem.Name = "MaterItem";
MaterItem.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn SlaveItem = new DataGridViewTextBoxColumn();
SlaveItem.DataPropertyName = "SlaveItem";
SlaveItem.HeaderText = "副授项目";
SlaveItem.Name = "SlaveItem";
SlaveItem.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Exp = new DataGridViewTextBoxColumn();
Exp.DataPropertyName = "Exp";
Exp.HeaderText = "经验值";
Exp.Name = "Exp";
Exp.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn TeacherLevel = new DataGridViewTextBoxColumn();
TeacherLevel.DataPropertyName = "TeacherLevel";
TeacherLevel.HeaderText = "等级";
TeacherLevel.Name = "TeacherLevel";
TeacherLevel.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn City = new DataGridViewTextBoxColumn();
City.DataPropertyName = "City";
City.HeaderText = "来自";
City.Name = "City";
City.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Telphone1 = new DataGridViewTextBoxColumn();
Telphone1.DataPropertyName = "Telphone1";
Telphone1.HeaderText = "手机";
Telphone1.Name = "Telphone1";
Telphone1.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Telphone2 = new DataGridViewTextBoxColumn();
Telphone2.DataPropertyName = "Telphone2";
Telphone2.HeaderText = "电话";
Telphone2.Name = "Telphone2";
Telphone2.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Email = new DataGridViewTextBoxColumn();
Email.DataPropertyName = "Email";
Email.HeaderText = "Email";
Email.Name = "Email";
Email.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn QQ = new DataGridViewTextBoxColumn();
QQ.DataPropertyName = "QQ";
QQ.HeaderText = "QQ";
QQ.Name = "QQ";
QQ.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn JobType = new DataGridViewTextBoxColumn();
JobType.DataPropertyName = "JobType";
JobType.HeaderText = "工作性质";
JobType.Name = "JobType";
JobType.Resizable = System.Windows.Forms.DataGridViewTriState.True;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns.Clear();//一定要先绑定数据后再清除列,不然没有效果
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { ID });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Num });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { TeacherName });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Sex });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Birthday });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Job });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { SRecord });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { MaterItem });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { SlaveItem });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Exp });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { TeacherLevel });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { City });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Telphone1 });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Telphone2 });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Email });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { QQ });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { JobType });
#endregion
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
// 创建此子窗体的一个新实例。
TopSportsManager.EditTeacher childForm = new TopSportsManager.EditTeacher(this.dataGridView1);
childForm.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
searchBind();
}
}
}
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 TopSportsManager
{
public partial class ListTeacher : Form
{
public ListTeacher()
{
InitializeComponent();
searchBind();
setGrid();
}
private void searchBind()
{
string sqlcmd = "select * from Teacher where Teachername Like @Teachername";
SqlParameter[] param = {
new SqlParameter("Teachername","%" + SearchTeacherName.Text + "%")
};
dataGridView1.DataSource = TopSportsManager.db.ExecuteDataview(sqlcmd, param);
清空绑定#region 清空绑定
this.Memo.DataBindings.Clear();
this.Appraisal.DataBindings.Clear();
this.Photo1.DataBindings.Clear();
this.Photo2.DataBindings.Clear();
#endregion
添加绑定#region 添加绑定
this.Memo.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataGridView1.DataSource, "Memo", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, ""));
this.Appraisal.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.dataGridView1.DataSource, "Appraisal", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, ""));
this.Photo1.DataBindings.Add(new System.Windows.Forms.Binding("Image", this.dataGridView1.DataSource, "Photo1", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, this.Photo1.ErrorImage));
this.Photo2.DataBindings.Add(new System.Windows.Forms.Binding("Image", this.dataGridView1.DataSource, "Photo2", true, System.Windows.Forms.DataSourceUpdateMode.OnValidation, this.Photo2.ErrorImage));
#endregion
}
private void setGrid()
{
初始化字段#region 初始化字段
System.Windows.Forms.DataGridViewTextBoxColumn ID = new DataGridViewTextBoxColumn();
ID.DataPropertyName = "ID";
ID.HeaderText = "ID";
ID.Name = "ID";
ID.Resizable = System.Windows.Forms.DataGridViewTriState.True;
ID.Visible = false;
System.Windows.Forms.DataGridViewTextBoxColumn Num = new DataGridViewTextBoxColumn();
Num.DataPropertyName = "Num";
Num.HeaderText = "编号";
Num.Name = "Num";
Num.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn TeacherName = new DataGridViewTextBoxColumn();
TeacherName.DataPropertyName = "TeacherName";
TeacherName.HeaderText = "教练姓名";
TeacherName.Name = "TeacherName";
TeacherName.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Sex = new DataGridViewTextBoxColumn();
Sex.DataPropertyName = "Sex";
Sex.HeaderText = "性别";
Sex.Name = "Sex";
Sex.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Birthday = new DataGridViewTextBoxColumn();
Birthday.DataPropertyName = "Birthday";
Birthday.HeaderText = "生日";
Birthday.Name = "Birthday";
Birthday.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Job = new DataGridViewTextBoxColumn();
Job.DataPropertyName = "Job";
Job.HeaderText = "身份";
Job.Name = "Job";
Job.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn SRecord = new DataGridViewTextBoxColumn();
SRecord.DataPropertyName = "SRecord";
SRecord.HeaderText = "学历";
SRecord.Name = "SRecord";
SRecord.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn MaterItem = new DataGridViewTextBoxColumn();
MaterItem.DataPropertyName = "MaterItem";
MaterItem.HeaderText = "主授项目";
MaterItem.Name = "MaterItem";
MaterItem.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn SlaveItem = new DataGridViewTextBoxColumn();
SlaveItem.DataPropertyName = "SlaveItem";
SlaveItem.HeaderText = "副授项目";
SlaveItem.Name = "SlaveItem";
SlaveItem.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Exp = new DataGridViewTextBoxColumn();
Exp.DataPropertyName = "Exp";
Exp.HeaderText = "经验值";
Exp.Name = "Exp";
Exp.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn TeacherLevel = new DataGridViewTextBoxColumn();
TeacherLevel.DataPropertyName = "TeacherLevel";
TeacherLevel.HeaderText = "等级";
TeacherLevel.Name = "TeacherLevel";
TeacherLevel.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn City = new DataGridViewTextBoxColumn();
City.DataPropertyName = "City";
City.HeaderText = "来自";
City.Name = "City";
City.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Telphone1 = new DataGridViewTextBoxColumn();
Telphone1.DataPropertyName = "Telphone1";
Telphone1.HeaderText = "手机";
Telphone1.Name = "Telphone1";
Telphone1.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Telphone2 = new DataGridViewTextBoxColumn();
Telphone2.DataPropertyName = "Telphone2";
Telphone2.HeaderText = "电话";
Telphone2.Name = "Telphone2";
Telphone2.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn Email = new DataGridViewTextBoxColumn();
Email.DataPropertyName = "Email";
Email.HeaderText = "Email";
Email.Name = "Email";
Email.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn QQ = new DataGridViewTextBoxColumn();
QQ.DataPropertyName = "QQ";
QQ.HeaderText = "QQ";
QQ.Name = "QQ";
QQ.Resizable = System.Windows.Forms.DataGridViewTriState.True;
System.Windows.Forms.DataGridViewTextBoxColumn JobType = new DataGridViewTextBoxColumn();
JobType.DataPropertyName = "JobType";
JobType.HeaderText = "工作性质";
JobType.Name = "JobType";
JobType.Resizable = System.Windows.Forms.DataGridViewTriState.True;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.Columns.Clear();//一定要先绑定数据后再清除列,不然没有效果
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { ID });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Num });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { TeacherName });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Sex });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Birthday });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Job });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { SRecord });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { MaterItem });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { SlaveItem });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Exp });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { TeacherLevel });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { City });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Telphone1 });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Telphone2 });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { Email });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { QQ });
dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] { JobType });
#endregion
}
private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
// 创建此子窗体的一个新实例。
TopSportsManager.EditTeacher childForm = new TopSportsManager.EditTeacher(this.dataGridView1);
childForm.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
searchBind();
}
}
}
修改代码
public EditTeacher(DataGridView dv)
{
_dv = dv;
var bs = dv.DataSource;
InitializeComponent();
string sqlcmd;
intNumber = dv.CurrentRow.Index;
selectUserID = dv[0, intNumber].Value.ToString().Trim();
// MessageBox.Show(selectUserID);
初始化课目#region 初始化课目
sqlcmd = "select * from Project";
SqlDataReader dr = TopSportsManager.db.ExecuteDataReader(sqlcmd);
this.MaterItem.Items.Clear();
this.SlaveItem.Items.Clear();
while (dr.Read())
{
this.MaterItem.Items.Add(dr["Name"].ToString());
this.SlaveItem.Items.Add(dr["Name"].ToString());
}
dr.Close();
#endregion
初始化编号#region 初始化编号
sqlcmd = "select Max(Num) As MaxNum from Teacher";
string MaxNum = (Convert.ToInt32(TopSportsManager.db.ExecuteScalar(sqlcmd)) + 1).ToString("D6");
Num.Text = MaxNum;
#endregion
初始化等级#region 初始化等级
sqlcmd = "select * from Level";
dr = TopSportsManager.db.ExecuteDataReader(sqlcmd);
this.TeacherLevel.Items.Clear();
while (dr.Read())
{
this.TeacherLevel.Items.Add(dr["Name"].ToString());
}
dr.Close();
#endregion
初始化输入框里的内容#region 初始化输入框里的内容
sqlcmd = "select * from Teacher where ID=@ID";
SqlParameter[] param = {
new SqlParameter("ID",selectUserID),
};
dr = TopSportsManager.db.ExecuteDataReader(sqlcmd,param);
if (dr.Read())
{
this.Num.Text = dr["Num"].ToString();
this.TeacherName.Text = dr["TeacherName"].ToString();
this.Birthday.Text = dr["Birthday"].ToString();
this.Job.Text = dr["Job"].ToString();
this.SRecord.Text = dr["SRecord"].ToString();
this.MaterItem.Text = dr["MaterItem"].ToString();
this.SlaveItem.Text = dr["SlaveItem"].ToString();
this.Exp.Text = dr["Exp"].ToString();
this.TeacherLevel.Text = dr["TeacherLevel"].ToString();
this.City.Text = dr["City"].ToString();
this.Memo.Text = dr["Memo"].ToString();
this.Appraisal.Text = dr["Appraisal"].ToString();
this.Telphone1.Text = dr["Telphone1"].ToString();
this.Telphone2.Text = dr["Telphone2"].ToString();
this.Email.Text = dr["Email"].ToString();
this.QQ.Text = dr["QQ"].ToString();
this.JobType.Text = dr["JobType"].ToString();
}
dr.Close();
#endregion
}
public EditTeacher(DataGridView dv)
{
_dv = dv;
var bs = dv.DataSource;
InitializeComponent();
string sqlcmd;
intNumber = dv.CurrentRow.Index;
selectUserID = dv[0, intNumber].Value.ToString().Trim();
// MessageBox.Show(selectUserID);
初始化课目#region 初始化课目
sqlcmd = "select * from Project";
SqlDataReader dr = TopSportsManager.db.ExecuteDataReader(sqlcmd);
this.MaterItem.Items.Clear();
this.SlaveItem.Items.Clear();
while (dr.Read())
{
this.MaterItem.Items.Add(dr["Name"].ToString());
this.SlaveItem.Items.Add(dr["Name"].ToString());
}
dr.Close();
#endregion
初始化编号#region 初始化编号
sqlcmd = "select Max(Num) As MaxNum from Teacher";
string MaxNum = (Convert.ToInt32(TopSportsManager.db.ExecuteScalar(sqlcmd)) + 1).ToString("D6");
Num.Text = MaxNum;
#endregion
初始化等级#region 初始化等级
sqlcmd = "select * from Level";
dr = TopSportsManager.db.ExecuteDataReader(sqlcmd);
this.TeacherLevel.Items.Clear();
while (dr.Read())
{
this.TeacherLevel.Items.Add(dr["Name"].ToString());
}
dr.Close();
#endregion
初始化输入框里的内容#region 初始化输入框里的内容
sqlcmd = "select * from Teacher where ID=@ID";
SqlParameter[] param = {
new SqlParameter("ID",selectUserID),
};
dr = TopSportsManager.db.ExecuteDataReader(sqlcmd,param);
if (dr.Read())
{
this.Num.Text = dr["Num"].ToString();
this.TeacherName.Text = dr["TeacherName"].ToString();
this.Birthday.Text = dr["Birthday"].ToString();
this.Job.Text = dr["Job"].ToString();
this.SRecord.Text = dr["SRecord"].ToString();
this.MaterItem.Text = dr["MaterItem"].ToString();
this.SlaveItem.Text = dr["SlaveItem"].ToString();
this.Exp.Text = dr["Exp"].ToString();
this.TeacherLevel.Text = dr["TeacherLevel"].ToString();
this.City.Text = dr["City"].ToString();
this.Memo.Text = dr["Memo"].ToString();
this.Appraisal.Text = dr["Appraisal"].ToString();
this.Telphone1.Text = dr["Telphone1"].ToString();
this.Telphone2.Text = dr["Telphone2"].ToString();
this.Email.Text = dr["Email"].ToString();
this.QQ.Text = dr["QQ"].ToString();
this.JobType.Text = dr["JobType"].ToString();
}
dr.Close();
#endregion
}