C#操作数据库总结.doc

开发工具：Microsoft Visual Studio 2005数据库:Microsoft SQL Server 2005 说明：这里建立的数据库名为Demo,有一个学生表Student,为操作方便起见,我只添加两个字段:studentnum和studentname.一、SQL语句:-create database Demouse Democreate table Student(studentnum char(14) primary key,studentname varchar(30) not null)insert into Student values(20041000010201,张扬)二、代码：1.引入名称空间:using System.Data.SqlClient;2.定义连接字符串,连接对象，命令对象: private String connectionstr; private SqlConnection connection; private SqlCommand command;3.在构造函数中初始化连接字符串，连接对象，命令对象 (1)初始化连接字符串： 方式 connectionstr=server=localhost;uid=sa;pwd=123456;database=Demo; 方式 connectionstr=server=;Integrade Security=SSPI;database=Demo; 其中，SIMS是我要连接的数据库名.(1)中的uid 和pwd是你登录数据库的登录名和密码 注：这种连接是连接本地的数据库，若要连接局域网内其它机子上的数据库，可将方式的server=localhost;改为server=数据库所在机子的IP; (2)初始化连接对象 connection = new SqlConnection(connectionstr); (3)初始化命令对象 command =new SqlCommand(); command .Connection =connection ;4.操作数据库中的数据 (1)查询数据库中的数据 方法一： string snum=tBstudentnum .Text .Trim (); string str = select * from Student where studentnum= + snum + ; command .CommandText =str; connection.Open(); if (command.ExecuteScalar() = null) MessageBox.Show(您输入的学号对应的学生不存在！, 错误, MessageBoxButtons.OK,MessageBoxIcon.Error); else SqlDataReader sdr = command.ExecuteReader(); while (sdr.Read() tBstudentnum .Text = sdrstudentnum.ToString(); tBstudentname.Text = sdrstudentname.ToString(); sdr.Close(); connection.Close(); 方法二： string snum=tBstudentnum .Text .Trim (); string str = select * from Student where studentnum= + snum + ; 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();(2)向数据库中添加数据 方法一: string snum = tBstudentnum.Text.Trim (); string sname = tBstudentname.Text.Trim(); if (snum = | sname = ) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else string insertstr=insert into Student values(+snum +,+sname +); command.CommandText = insertstr; connection.Open(); command.ExecuteNonQuery(); MessageBox.Show(学生添加成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information); connection.Close(); 方法二: 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);(3)修改数据库中的数据 方法一: string snum = tBstudentnum.Text.Trim(); string sname = tBstudentname.Text.Trim(); if (snum = | sname = ) MessageBox.Show(学生学号或姓名不能为空！, 错误, MessageBoxButtons.OK, MessageBoxIcon.Error); else string modifystr = update Student set studentname= + sname + where studentnum= + snum + ; command.CommandText = modifystr; connection.Open(); command.ExecuteNonQuery(); MessageBox.Show(学生的姓名修改成功！, 提示, MessageBoxButtons.OK, MessageBoxIcon.Information ); connection.Close(); 方法二: 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); (4)删除数据库中的数据 方法一: string snum = tBstudentnum.Text.Trim(); if (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.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(); 方二: string str = select * from Student where studentnum= + snum + ; string deletestr = delete from Student where studentnum= + snum + ; SqlDataAdapter sda = new SqlDataAdapter(str, connection); DataSet ds = new DataSet(); sda.Fill(ds, Student); DataTable dt = ds.TablesStudent; if (dt.Rows.Count 0) dt.Rows0