using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace 通过控制台修改数据库文件
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//窗体加载事件
private void Form1_Load(object sender, EventArgs e)
{ //将TblClass1表中数据读取到一个List中
LoadData();
}
private void LoadData()
{
List list = new List();
string constr = "Data source=USER-20141030QL;initial catalog=ASP.net;integrated security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select *from Employees";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{ //判断是否查询到了数据
if (reader.HasRows)
{ //一条一条读取数据
while (reader.Read())
{
TblClass1 model = new TblClass1();
model.empID = reader.GetInt32(0);
model.empNumber = reader.GetString(1);
model.empName = reader.GetString(2);
model.empGender = reader.IsDBNull(3)?null:reader.GetString(3);
model.empJoinData = reader.GetString(4);
model.empAge = reader.GetInt32(5);
model.empAddress = reader.GetString(6);
model.empPhone = reader.GetString(7);
model.empDepartmentID=reader.GetInt32(8);
model.empEmail = reader.GetString(9);
list.Add(model);//把model对象加到List集合中
}
}
}
}
}//数据绑定的时候,只认"属性"不认"字段",内部通过反射来实现
this.dataGridView1.DataSource =list;//数据绑定
}
//增加一条数据
private void button1_Click(object sender, EventArgs e)
{ //采集用户的输入
string empNumber = textBox1.Text.Trim();
string empName = textBox2.Text.Trim();
string empGender = textBox3.Text.Trim();
string empJoinData = textBox4.Text.Trim();
string empAge = textBox5.Text.Trim();
string empAddress = textBox6.Text.Trim();
string empPhone = textBox7.Text.Trim();
string empDepartmentID = textBox8.Text.Trim();
string empEmail = textBox9.Text.Trim();
//执行插入操作
string constr = "data source=USER-20141030QL;initial catalog=ASP.net;integrated security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = string.Format(@"Insert into Employees values(N'{0}',N'{1}',N'{2}',N'{3}',{4},N'{5}',
N'{6}',{7},N'{8}')",empNumber,empName,empGender,empJoinData,empAge,
empAddress,empPhone,empDepartmentID,empEmail);
using (SqlCommand cmd = new SqlCommand(sql,con))
{
con.Open();
int r = cmd.ExecuteNonQuery();
if (r > 0)
{
this.Text = "插入成功!";
LoadData();
}
else
{
this.Text = "插入了"+r+"行!";
}
}
}
}
//获取焦点事件
private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
{ //获取当前选中的对象
DataGridViewRow currentrow=this.dataGridView1.Rows[e.RowIndex];
//获取当前航中绑定的TblClass数据对象
TblClass1 model=currentrow.DataBoundItem as TblClass1;
if (model != null)
{
label19.Text = model.empID.ToString();
textBox10.Text = model.empNumber;
textBox11.Text = model.empName;
textBox12.Text = model.empGender;
textBox13.Text = model.empJoinData;
textBox14.Text = model.empAge.ToString();
textBox15.Text = model.empAddress;
textBox16.Text = model.empPhone;
textBox17.Text = model.empDepartmentID.ToString();
textBox18.Text = model.empEmail;
}
}
//保存数据
private void button2_Click(object sender, EventArgs e)
{
//采集用户输入
TblClass1 model = new TblClass1();
model.empID = Convert.ToInt32(label19.Text);
model.empNumber =textBox10.Text.Trim();
model.empName = textBox11.Text.Trim();
model.empGender = textBox12.Text.Trim();
model.empJoinData = textBox13.Text.Trim();
model.empAge = Convert.ToInt32(textBox14.Text);
model.empAddress = textBox15.Text.Trim();
model.empPhone = textBox16.Text.Trim();
model.empDepartmentID = Convert.ToInt32(textBox17.Text);
model.empEmail = textBox18.Text.Trim();
//连接数据库,指向删除操作
string constr = "data source=USER-20141030QL;initial catalog=ASP.net;integrated security=true";
using (SqlConnection con= new SqlConnection(constr))
{
string sql = string.Format(@"update Employees set empNumber='{0}',empName='{1}',enpGender='{2}',empJoinData='{3}',
empAge={4},empAddress='{5}',empPhone='{6}',empDepartmentID={7},empEmail='{8}' where empID={9}",
model.empNumber,model.empName,model.empGender, model.empJoinData, model.empAge, model.empAddress,
model.empPhone, model.empDepartmentID, model.empEmail,model.empID);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
int r = cmd.ExecuteNonQuery();
this.Text = "更新了" + r + "行数据。";
//重新加载绑定控件
LoadData();
}
}
}
//删除数据
private void button3_Click(object sender, EventArgs e)
{
DialogResult result=MessageBox.Show("确定要删除吗","操作提示",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning);
if (result == System.Windows.Forms.DialogResult.OK)
{
int empID = Convert.ToInt32(label19.Text);
string constr = "data source=USER-20141030QL;initial catalog=ASP.net;integrated security=true ";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = string.Format("delete from Employees where empID={0}",empID);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
int r = cmd.ExecuteNonQuery();
this.Text="删除了"+r+"条数据";
//重新绑定数据
LoadData();
}
}
}
}
}
}
数据库设计截图:
运行界面截图: