asp.net C#操作数据库总结

时间：2022-06-25 07:32:42 编辑：袖梨来源：一聚教程网

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;"

代码如下

复制代码

//        连接字符串：String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb";
//        建立连接：OleDbConnection connection = new OleDbConnection(connectionString);
//        使用OleDbCommand类来执行Sql语句：
//        OleDbCommand cmd = new OleDbCommand(sql, connection);
//        connection.Open();
        //        cmd.ExecuteNonQuery();
        #endregion

        #region 连接字符串
        //string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:程序书籍软件c#程序代码access数据库操作addressList.mdb"; //绝对路径
    //    string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Environment.CurrentDirectory+"addressList.mdb";

//相对路径

   (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);
                 }

推荐专题

最新下载

热门教程

asp.net C#操作数据库总结

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程