C#操作數(shù)據(jù)庫小結

1、C#操作數(shù)據(jù)庫總結開發(fā)工具：Microsoft Visual Studio 2008數(shù)據(jù)庫:Microsoft SQL Server 2008/ 建立數(shù)據(jù)庫 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('2004100001020

2、1','張揚');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

3、=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

4、str = "Select * from Student Where studentnum='" + snum + "'" command.Connection = connection; command.CommandText = str; connection.Open(); /數(shù)據(jù)庫連接打開 if (command.ExecuteScalar() = null) MessageBox.Show("你輸入的學號對應的學生不在！", "錯誤", MessageBoxButtons.OK, Mess

5、ageBoxIcon.Error); else /查詢數(shù)據(jù)庫中的數(shù)據(jù) 方法一（代碼簡潔：優(yōu)） SqlDataReader sdr = command.ExecuteReader(); /sdr打開 while (sdr.Read() tBstudentnum.Text = sdr"studentnum".ToString(); tBstudentname.Text = sdr"studentname".ToString(); sdr.Close(); /sdr關閉 connection.Close(); /數(shù)據(jù)庫連接關閉 private void btn

6、Query2_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

7、; command.CommandText = str; connection.Open(); /數(shù)據(jù)庫連接打開 if (command.ExecuteScalar() = null) MessageBox.Show("您輸入的學號對應的學生不存在！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); else /查詢數(shù)據(jù)庫中的數(shù)據(jù) 方法二 SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSe

8、t(); sda.Fill(ds, "Student"); DataTable dt = ds.Tables"Student" tBstudentnum.Text = dt.Rows0"studentnum".ToString(); tBstudentname.Text = dt.Rows0"studentname".ToString(); connection.Close(); /數(shù)據(jù)庫連接關閉 private void btnAdd1_Click(object sender, EventArgs e) SqlC

9、onnection 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("學生學號或姓名不能為空！", "錯誤", MessageBox

10、Buttons.OK, MessageBoxIcon.Error); else /向數(shù)據(jù)庫中添加數(shù)據(jù) 方法一（代碼簡潔：優(yōu)） string insertstr = "insert into Student values('" + snum + "','" + sname + "')" command.Connection = connection; command.CommandText = insertstr; connection.Open(); /數(shù)據(jù)庫連接打開 command.ExecuteNo

11、nQuery(); MessageBox.Show("學生添加成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /數(shù)據(jù)庫連接關閉 private void btnAdd2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); string snum = tBstudentnum.Text.Trim(); string s

12、name = tBstudentname.Text.Trim(); if (string.IsNullOrEmpty(snum) | string.IsNullOrEmpty(sname) MessageBox.Show("學生學號或姓名不能為空！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); else /向數(shù)據(jù)庫中添加數(shù)據(jù) 方法二 string str = "select * from Student" string insertstr = "insert int

13、o Student values('" + snum + "','" + sname + "')" SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSet(); sda.Fill(ds, "Student"); DataTable dt = ds.Tables"Student" DataRow dr = dt.NewRow(); dr"studentnum

14、" = snum; dr"studentname" = 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

15、 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("學生學號或姓名不能為空！",

16、"錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); else /修改數(shù)據(jù)庫中的數(shù)據(jù) 方法一（代碼簡潔：優(yōu)） string modifystr = "update Student set studentname='" + sname + "' where studentnum='" + snum + "'" command.Connection = connection; command.CommandText = modifystr;

17、 connection.Open(); /數(shù)據(jù)庫連接打開 command.ExecuteNonQuery(); MessageBox.Show("學生的姓名修改成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /數(shù)據(jù)庫連接關閉 private void btnChg2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr);

18、 string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (snum = "" | sname = "") MessageBox.Show("學生學號或姓名不能為空！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); else /修改數(shù)據(jù)庫中的數(shù)據(jù) 方法二 string str = "select * from Student where

19、 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, &q

20、uot;Student"); DataTable dt = ds.Tables"Student" dt.Rows0"studentname" = sname; sda.UpdateCommand = new SqlCommand(updatestr, connection); sda.Update(ds, "Student"); MessageBox.Show("學生姓名修改成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Informa

21、tion); 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.O

22、K, MessageBoxIcon.Error); else /刪除數(shù)據(jù)庫中的數(shù)據(jù) 方法一（代碼簡潔：優(yōu)） string str = "select * from Student where studentnum='" + snum + "'" string deletestr = "delete from Student where studentnum='" + snum + "'" command.Connection = connection; command.Command

23、Text = str; connection.Open(); /數(shù)據(jù)庫連接打開 if (command.ExecuteScalar() = null) MessageBox.Show("此學號對應的學生不存在！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error); else command.CommandText = deletestr; command.ExecuteNonQuery(); MessageBox.Show("學生的信息刪除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); /數(shù)據(jù)庫連接關閉 private void btnDel2_Click(object sender, EventArgs e) SqlConnection connection = new SqlConnection(constr); string snum = tBstudentnum.Text.Trim(); if (string.IsNullOrEmpty(snum) MessageBox.Show("學生學號不能為空！",