WinForm之窗体应用程序
基本简单数据库操作（增删改查）
using System;
using System.Collections.Generic;
using System.Windows.Forms; namespace DataBaseOperation
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new frmMain());
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms; namespace DataBaseOperation
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
} private void btnSelect_Click(object sender, EventArgs e)
{
frmSelect fs = new frmSelect();
fs.ShowDialog();
} private void btnInsert_Click(object sender, EventArgs e)
{
frmInsert fi = new frmInsert();
fi.ShowDialog();
} private void btnUpdate_Click(object sender, EventArgs e)
{
frmUpdate fu = new frmUpdate();
fu.ShowDialog();
} private void btnDelete_Click(object sender, EventArgs e)
{
frmDelete fd = new frmDelete();
fd.ShowDialog();
} private void frmMain_Load(object sender, EventArgs e)
{ this.Left = Screen.PrimaryScreen.WorkingArea.Width - this.Width;
this.Top = Screen.PrimaryScreen.WorkingArea.Height - this.Height;
}
}
}
frmMain
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;// namespace DataBaseOperation
{
public partial class frmDelete : Form
{
public frmDelete()
{
InitializeComponent();
} SqlDataAdapter sda;//
DataSet ds = new DataSet();// private void frmDelete_Load(object sender, EventArgs e)
{
//窗体加载时查询表中全部信息 //1.
string sql = "select sid,sname,ssex,saddress,semail from students"; //2.
sda = new SqlDataAdapter(sql, DBHelper.connection); int result = sda.Fill(ds); if (result > )
{
this.dataGridView1.DataSource = ds.Tables[];
}
else
{
MessageBox.Show("表中无信息");
} } private void 删除选中行ToolStripMenuItem_Click(object sender, EventArgs e)
{
if (this.dataGridView1.SelectedRows.Count > )
{
//1.
string id = this.dataGridView1.SelectedRows[].Cells["sid"].Value.ToString(); //2.
string sql = string.Format("delete from students where sid={0}", id); //3. try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
int result = command.ExecuteNonQuery();
if (result > )
{
MessageBox.Show("成功删除该行信息！");
}
else
{
MessageBox.Show("操作失败！");
}
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
else
{
MessageBox.Show("请选中要删除的行");
} //刷新控件中信息行
this.dataGridView1.Rows.Remove(this.dataGridView1.SelectedRows[]);
} private void btnSearchAll_Click(object sender, EventArgs e)
{
//1.清空ds中的表信息
ds.Tables.Clear(); //2.
int result = sda.Fill(ds);
if (result > )
{
this.dataGridView1.DataSource = ds.Tables[];
}
else
{
MessageBox.Show("表中无信息");
}
} private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{ } private void dataGridView1_MouseDoubleClick(object sender, MouseEventArgs e)
{ } private void contextMenuStrip1_Opening(object sender, CancelEventArgs e)
{ }
}
}
frmDelete
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;// namespace DataBaseOperation
{
public partial class frmInsert : Form
{
public frmInsert()
{
InitializeComponent();
} private void btnReset_Click(object sender, EventArgs e)
{
this.txtName.Text = "";
this.txtAddress.Text = "";
this.txtEmail.Text = "";
this.radMan.Checked = true;
this.txtName.Focus();
} private void btnInsert_Click(object sender, EventArgs e)
{
//1.获取控件中用户输入的学员信息
string name = this.txtName.Text.Trim();
string sex;
if (this.radMan.Checked)
{
sex = "";
}
else
{
sex = "";
}
MessageBox.Show(sex);
string address = this.txtAddress.Text.Trim();
string email= this.txtEmail.Text.Trim(); //2.
string sql=string.Format("insert into students (sname,ssex,saddress,semail) values('{0}',{1},'{2}','{3}')",name,sex,address,email); //3.
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
int result = command.ExecuteNonQuery();
if (result > )
{
MessageBox.Show("成功添加一条学员信息");
}
else
{
MessageBox.Show("操作失败！");
}
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
}
}
frmInsert
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;// namespace DataBaseOperation
{
public partial class frmSelect : Form
{
public frmSelect()
{
InitializeComponent();
} private void btnSearchName_Click(object sender, EventArgs e)
{
//根据学号查询学员姓名
string id = this.txtNum1.Text.Trim();
if (id != "")
{
string sql = string.Format("select sname from students where sid={0}", id);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
object name = command.ExecuteScalar();
if (name != null)
{
this.lblName.Text = "此学员的姓名为：" + name.ToString();
}
else
{
MessageBox.Show("查无此人！");
this.lblName.Text = "";
this.txtNum1.Text = "";
this.txtNum1.Focus();
} }
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
else
{
MessageBox.Show("请输入学号！");
}
} private void btnSearchStudentInfo_Click(object sender, EventArgs e)
{
//根据学号查询学员信息
string id = this.txtNum2.Text.Trim();
if (id != "")
{
string sql = string.Format("select sname,ssex,saddress,semail from students where sid={0}", id);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
this.txtName1.Text = sdr["sname"].ToString();
// MessageBox.Show("性别字段的值："+sdr["ssex"].ToString());
if (sdr["ssex"].ToString().ToLower() == "true")
{
this.radMan1.Checked = true;
}
else
{
this.radWoman1.Checked = true;
}
this.txtAddress.Text = sdr["saddress"].ToString();
this.txtEmail.Text = sdr["semail"].ToString();
}
else
{
MessageBox.Show("查无此人！");
this.txtNum2.Text = "";
this.txtNum2.Focus();
this.txtName1.Text = "";
this.radMan1.Checked = true;
this.txtAddress.Text = "";
this.txtEmail.Text = "";
} sdr.Close(); }
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
else
{
MessageBox.Show("请输入学号！");
}
} DataSet ds = new DataSet();//创建数据集对象
SqlDataAdapter sda;//声明数据适配器 private void btnSearchBySex1_Click(object sender, EventArgs e)
{
//清空数据集中表信息
ds.Tables.Clear(); //根据性别查询学员信息
string sex;
if (this.radMan2.Checked)
{
sex = this.radMan2.Tag.ToString();
}
else
{
sex = this.radWoman2.Tag.ToString();
}
MessageBox.Show("性别的值为：" + sex); string sql = string.Format("select sid,sname,ssex,saddress,semail from student where ssex={0}", sex);
//创建数据适配器对象
sda = new SqlDataAdapter(sql, DBHelper.connection);
int result = sda.Fill(ds);
if (result > )
{
this.dgvStudentInfo.DataSource = ds.Tables[];
}
else
{
MessageBox.Show("无查询结果");
} } private void btnSearchBySex2_Click(object sender, EventArgs e)
{
//清空数据集中表信息
ds.Tables.Clear(); //根据性别查询学员信息
string sex;
if (this.cboSex1.Text != "")
{
if (this.cboSex1.Text == "男")
{
sex = "";
}
else
{
sex = "";
}
MessageBox.Show("性别的值为：" + sex);
}
else
{
MessageBox.Show("请选择性别");
return;
} string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
//创建数据适配器对象
sda = new SqlDataAdapter(sql, DBHelper.connection);
int result = sda.Fill(ds);
if (result > )
{
this.dgvStudentInfo.DataSource = ds.Tables[];
}
else
{
MessageBox.Show("无查询结果");
}
} private void btnSearchByName1_Click(object sender, EventArgs e)
{
//清空数据集中表信息
ds.Tables.Clear(); //根据学员姓名查询学员信息（模糊查询）
string name = this.txtName2.Text.Trim();
string sql = string.Format("select sid,sname,ssex,saddress,semail from students where sname like '%{0}%'", name);
//创建数据适配器对象
sda = new SqlDataAdapter(sql, DBHelper.connection);
int result = sda.Fill(ds);
if (result > )
{
this.dgvStudentInfo.DataSource = ds.Tables[];
}
else
{
MessageBox.Show("无查询结果");
} } private void btnSearchBySex3_Click(object sender, EventArgs e)
{
//清空ListView中的项
this.lstStudentInfo.Items.Clear(); //根据性别查询学员信息
string sex;
if (this.radMan3.Checked)
{
sex = this.radMan3.Tag.ToString();
}
else
{
sex = this.radWoman3.Tag.ToString();
}
MessageBox.Show("性别的值为：" + sex); string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader sdr = command.ExecuteReader();
while (sdr.Read())
{
//1.
ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
//2.
if (sdr["ssex"].ToString().ToLower() == "true")
{
sex = "男";
}
else
{
sex = "女";
}
lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
//3.
this.lstStudentInfo.Items.Add(lvi);
}
//关闭sdr
sdr.Close();
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
} } private void btnSearchBySex4_Click(object sender, EventArgs e)
{
//清空ListView中的项
this.lstStudentInfo.Items.Clear(); //根据性别查询学员信息
string sex;
if (this.cboSex2.Text != "")
{
if (this.cboSex2.Text == "男")
{
sex = "";
}
else
{
sex = "";
}
MessageBox.Show("性别的值为：" + sex);
}
else
{
MessageBox.Show("请选择性别");
return;
} string sql = string.Format("select sid,sname,ssex,saddress,semail from students where ssex={0}", sex);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader sdr = command.ExecuteReader();
while (sdr.Read())
{
//1.
ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
//2.
if (sdr["ssex"].ToString().ToLower() == "true")
{
sex = "男";
}
else
{
sex = "女";
}
lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
//3.
this.lstStudentInfo.Items.Add(lvi);
}
//关闭sdr
sdr.Close();
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
} private void btnSearchByName2_Click(object sender, EventArgs e)
{
//清空ListView中的项
this.lstStudentInfo.Items.Clear(); //根据学员姓名查询学员信息（模糊查询）
string name = this.txtName3.Text.Trim();
string sql = string.Format("select sid,sname,ssex,saddress,semail from students where sname like '%{0}%'", name);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader sdr = command.ExecuteReader();
while (sdr.Read())
{
//1.
ListViewItem lvi = new ListViewItem(sdr["sid"].ToString());
//2.
string sex;
if (sdr["ssex"].ToString().ToLower() == "true")
{
sex = "男";
}
else
{
sex = "女";
}
lvi.SubItems.AddRange(new string[] { sdr["sname"].ToString(), sex, sdr["saddress"].ToString(), sdr["semail"].ToString() });
//3.将主键值写到lvi的Tag属性中
lvi.Tag = sdr["sid"].ToString(); //4.
this.lstStudentInfo.Items.Add(lvi);
}
//关闭sdr
sdr.Close();
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
} private void dgvStudentInfo_CellClick(object sender, DataGridViewCellEventArgs e)
{
//单元格点击事件
if (this.dgvStudentInfo.SelectedRows.Count > )
{
string id = this.dgvStudentInfo.SelectedRows[].Cells["sid"].Value.ToString();
MessageBox.Show(id);
}
} private void lstStudentInfo_MouseClick(object sender, MouseEventArgs e)
{
//ListView控件点击事件
if (this.lstStudentInfo.SelectedItems.Count > )
{
string id = this.lstStudentInfo.SelectedItems[].Tag.ToString();
MessageBox.Show(id);
}
}
}
}
frmSelect
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;// namespace DataBaseOperation
{
public partial class frmUpdate : Form
{
public frmUpdate()
{
InitializeComponent();
} private void btnSearchStudentInfo_Click(object sender, EventArgs e)
{
//根据学号查询学员信息
string id = this.txtNum.Text.Trim();
if (id != "")
{
string sql = string.Format("select sname,ssex,saddress,semail from students where sid={0}", id);
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
SqlDataReader sdr = command.ExecuteReader();
if (sdr.Read())
{
this.txtName.Text = sdr["sname"].ToString();
// MessageBox.Show("性别字段的值："+sdr["ssex"].ToString());
if (sdr["ssex"].ToString().ToLower() == "true")
{
this.radMan.Checked = true;
}
else
{
this.radWoman.Checked = true;
}
this.txtAddress.Text = sdr["saddress"].ToString();
this.txtEmail.Text = sdr["semail"].ToString(); //激活或屏蔽窗体中部分控件
this.txtNum.Enabled = false;
this.txtName.Enabled = true;
this.txtAddress.Enabled = true;
this.txtEmail.Enabled = true;
this.radMan.Enabled = true;
this.radWoman.Enabled = true;
}
else
{
MessageBox.Show("查无此人！");
this.txtNum.Text = "";
this.txtNum.Focus();
this.txtName.Text = "";
this.radMan.Checked = true;
this.txtAddress.Text = "";
this.txtEmail.Text = "";
} sdr.Close(); }
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
else
{
MessageBox.Show("请输入学号！");
}
} private void btnUpdate_Click(object sender, EventArgs e)
{
//1.
string id = this.txtNum.Text.Trim();
string name = this.txtName.Text.Trim();
string sex;
if (this.radMan.Checked)
{
sex = "";
}
else
{
sex = "";
}
string address = this.txtAddress.Text.Trim();
string email = this.txtEmail.Text.Trim(); //2.
string sql = string.Format("update students set sname='{0}',ssex={1},saddress='{2}',semail='{3}' where sid={4}", name, sex, address, email, id); //3.
try
{
SqlCommand command = new SqlCommand(sql, DBHelper.connection);
DBHelper.connection.Open();
int result = command.ExecuteNonQuery();
if (result > )
{
MessageBox.Show("更新完毕！");
//激活或屏蔽窗体中部分控件
this.txtNum.Enabled = true;
this.txtName.Enabled = false;
this.txtAddress.Enabled = false;
this.txtEmail.Enabled = false;
this.radMan.Enabled = false;
this.radWoman.Enabled = false; }
else
{
MessageBox.Show("更新操作失败！");
}
}
catch (Exception ex)
{ MessageBox.Show(ex.Message);
}
finally
{
DBHelper.connection.Close();
}
}
}
}
frmUpdate