C#操作数据库小结.doc

C#操作数据库总结开发工具：Microsoft Visual Studio 2008数据库:Microsoft SQL Server 2008/ 建立数据库 Demo,有一个学生表 Student(studentnum,studentname)/ create database Demo;/ use Demo;/ create table Student/ (/ studentnum char(14) primary key,/ studentname varchar(30) not null/ );/ insert into Student values(20041000010201,张扬);using System;using System.Data;using System.Data.SqlClient;using System.Windows.Forms;namespace Demo public partial class Form1 : Form private String constr = server=37;uid=sa;pwd=123qwe.;database=Demo; /远程sa账号登录 /private String connectionstr = server=localhost;Integrade Security=SSPI;database=Demo; 本地管理员账号登录 public Form1() InitializeComponent(); private void btnQuery1_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); SqlCommand command = new SqlCommand(); string snum = tBstudentnum.Text.Trim(); string str = Select * from Student Where studentnum= + snum + ; command.Connection = connection; command.CommandText = str; connection.Open(); /数据库连接打开 if (command.ExecuteScalar() = null) MessageBox.Show(你输入的学号对应的学生不在！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /查询数据库中的数据 方法一（代码简洁：优） SqlDataReader sdr = command.ExecuteReader(); /sdr打开 while (sdr.Read() tBstudentnum.Text = sdrstudentnum.ToString(); tBstudentname.Text = sdrstudentname.ToString(); sdr.Close(); /sdr关闭 connection.Close(); /数据库连接关闭 private void btnQuery2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); SqlCommand command = new SqlCommand(); string snum=tBstudentnum.Text.Trim(); string str = select * from Student where studentnum= + snum + ; command.Connection = connection; command.CommandText = str; connection.Open(); /数据库连接打开 if (command.ExecuteScalar() = null) MessageBox.Show(您输入的学号对应的学生不存在！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /查询数据库中的数据 方法二 SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSet(); sda.Fill(ds, Student); DataTable dt = ds.TablesStudent; tBstudentnum.Text = dt.Rows0studentnum.ToString(); tBstudentname.Text = dt.Rows0studentname.ToString(); connection.Close(); /数据库连接关闭 private void btnAdd1_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); SqlCommand command = new SqlCommand(); string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (string.IsNullOrEmpty(snum) | string.IsNullOrEmpty(sname) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /向数据库中添加数据 方法一（代码简洁：优） string insertstr = insert into Student values( + snum + , + sname + ); command.Connection = connection; command.CommandText = insertstr; connection.Open(); /数据库连接打开 command.ExecuteNonQuery(); MessageBox.Show(学生添加成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /数据库连接关闭 private void btnAdd2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (string.IsNullOrEmpty(snum) | string.IsNullOrEmpty(sname) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /向数据库中添加数据 方法二 string str = select * from Student; string insertstr = insert into Student values( + snum + , + sname + ); SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSet(); sda.Fill(ds, Student); DataTable dt = ds.TablesStudent; DataRow dr = dt.NewRow(); drstudentnum = snum; drstudentname = sname; dt.Rows.Add(dr); sda.InsertCommand = new SqlCommand(insertstr, connection); sda.Update(ds, Student); MessageBox.Show(学生添加成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); private void btnChg1_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); SqlCommand command = new SqlCommand(); string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (string.IsNullOrEmpty(snum) | string.IsNullOrEmpty(sname) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /修改数据库中的数据 方法一（代码简洁：优） string modifystr = update Student set studentname= + sname + where studentnum= + snum + ; command.Connection = connection; command.CommandText = modifystr; connection.Open(); /数据库连接打开 command.ExecuteNonQuery(); MessageBox.Show(学生的姓名修改成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /数据库连接关闭 private void btnChg2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (snum = | sname = ) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /修改数据库中的数据 方法二 string str = select * from Student where studentnum= + snum + ; ; string updatestr = update Student set studentname= + sname + where studentnum= + snum + ; SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSet(); sda.Fill(ds, Student); DataTable dt = ds.TablesStudent; dt.Rows0studentname = sname; sda.UpdateCommand = new SqlCommand(updatestr, connection); sda.Update(ds, Student); MessageBox.Show(学生姓名修改成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); private void btnDel1_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); SqlCommand command = new SqlCommand(); string snum = tBstudentnum.Text.Trim(); if (string.IsNullOrEmpty(snum) MessageBox.Show(学生学号不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else /删除数据库中的数据 方法一（代码简洁：优） string str = select * from Student where studentnum= + snum + ; string deletestr = delete from Student where studentnum= + snum + ; command.Connection = connection; command.CommandText = str; connection.Open(); /数据库连接打开 if (command.ExecuteScalar() = null) MessageBox.Show(此学号对应的学生不存在！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else command.CommandText = deletestr; command.ExecuteNonQuery(); MessageBox.Show(学生的信息删除成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /数据库连接关闭 private void btnDel2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); string snum = tBstudentnum.Text.Trim(); if (s