C# 操作数据库（1）

//开发工具：Microsoft Visual Studio 2005

//数据库:Microsoft SQL Server 2005

//说明：这里建立的数据库名为Demo,有一个学生表Student,为操作方便起见,我只添加两个字段:studentnum和studentname.
//二、代码：
//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=127.0.0.1";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 = sdr["studentnum"].ToString();
 tBstudentname.Text = sdr["studentname"].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.Tables["Student"];
 tBstudentnum.Text = dt.Rows[0]["studentnum"].ToString();
 tBstudentname.Text = dt.Rows[0]["studentname"].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.Tables["Student"];
 DataRow dr = dt.NewRow();
 dr["studentnum"] = snum;
 dr["studentname"] = 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.Tables["Student"];
 dt.Rows[0]["studentname"] = 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.Tables["Student"];
 if (dt.Rows.Count > 0)
 {
 dt.Rows[0].Delete();
 sda.DeleteCommand = new SqlCommand(deletestr, connection);
 sda.Update(ds, "Student");
 MessageBox.Show("学生信息删除成功！", "提示", MessageBoxButtons.OK,
 MessageBoxIcon.Information);
 }
 else
 {
 MessageBox.Show("此学号对应的学生不存在！", "错误",MessageBoxButtons.OK, MessageBoxIcon.Error);
 }

//注：‍以上对数据库的操作,大都从操作角度出发仅进行单一的操作,有的地方并未进行错误处理,如修改学生信息时,学号应不可能编辑等