C#操作数据库总结

开发工具：Microsoft Visual Studio 2005
数据库:Microsoft SQL Server 2005

说明：这里建立的数据库名为Demo,有一个学生表Student,为操作方便起见,我只添加两个字段:studentnum和studentname.
一、SQL语句:
--create database Demo
use Demo

create 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=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);                 
                }

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