欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ADO.NET无连接模式的详细介绍

程序员文章站 2024-02-27 22:13:15
无连接模式:可以在没有打开连接时在内存中操作数据,dataadapter通过管理连接为无连接模式提供服务,当要从数据库中查询数据时,dataadapter打开一个连接,填充...

无连接模式:可以在没有打开连接时在内存中操作数据,dataadapter通过管理连接为无连接模式提供服务,当要从数据库中查询数据时,dataadapter打开一个连接,填充指定的dataset,等数据读取完马上自动关闭连接,然后可以对数据做修改,再次使用dataadapter打开连接,持久化修改(无论是更新,删除或是更新),最后自动关闭连接,使用无连接模式的情况是有一些独立数据,它们不会发生改变或者很少改变,因为在将填充dataset和更新数据这段时间内数据库中的实际数据也许会发生改变,如果需要将数据立即持久化到数据库请使用连接模式

读取数据到dataset:无连接意味着一个连接建立了一个与数据库的会话,请求的数据读入到dataset中,然后通过断开数据库的连接关闭会话,这时会话因为与数据库的断开而关闭,dataset成为一个无连接的数据库

复制代码 代码如下:

/// <summary>
      /// 查询学生信息
      /// </summary>
      /// <returns>返回填充了学生表的dataset</returns>
      public dataset getuserinfor()
      {
          string str = "data source=.;initial catalog=student;integrated security=true";
          var conn = new sqlconnection(str);
          dataset ds = new dataset();
          var sda = new sqldataadapter("select * from student",conn);
          sda.fill(ds, "student");//调用fill方法时,sqldataadapter会自动打开连接,读取数据然后关闭连接
          foreach (datarow dr in ds.tables["student"].rows)
          {
              console.writeline(dr["name"]);
          }
          return ds;
      }

将dataset的修改保存到数据库
插入数据

复制代码 代码如下:

/// <summary>
      /// 插入学生信息,并返回插入后的dataset
      /// </summary>
      /// <param name="stu">学生实体类</param>
      public dataset insertstudnt(student stu)
      {
          dataset ds = getuserinfor();
          string str = "data source=.;initial catalog=student;integrated security=true";
          string sql = "insert into student values (@name,@age)";
          var conn = new sqlconnection(str);
          var cmd = new sqlcommand(sql,conn);
          var sda = new sqldataadapter();
          sqlparameter sqlparam1 = new sqlparameter()
          {
              parametername = "@name",
              sourcecolumn = "name"
          };
          sqlparameter sqlparam2 = new sqlparameter()
          {
              parametername = "@age",
              sourcecolumn = "age"
          };
          sqlparameter[] sqlparamarray = new sqlparameter[] {sqlparam1,sqlparam2 };
          cmd.parameters.addrange(sqlparamarray);
          sda.insertcommand = cmd;
          datarow dr = ds.tables["student"].newrow();
          dr["name"] = stu.name;
          dr["age"] = stu.age;
          ds.tables["student"].rows.add(dr);
          sda.update(ds,"student");
          return ds;

      }

更新数据

复制代码 代码如下:

/// <summary>
      /// 根据id更新姓名和年龄
      /// </summary>
      /// <param name="name">姓名</param>
      /// <param name="age">年龄</param>
      /// <param name="id">学生id</param>
      /// <returns>返回更新后的dataset</returns>
      public dataset updatestudent(student stu,int id)
      {
          dataset ds = getuserinfor();
          string str = "data source=.;initial catalog=student;integrated security=true";
          string sql = "update student set name=@name,age=@age where id=@id";
          var conn = new sqlconnection(str);
          var cmd = new sqlcommand(sql, conn);
          var sda = new sqldataadapter();
          sqlparameter param1 = new sqlparameter()
          {
              parametername="@name",sourcecolumn="name"
          };
          sqlparameter param2 = new sqlparameter()
          {
              parametername = "@age",
              sourcecolumn = "age",
              sqldbtype=sqldbtype.int
          };
          sqlparameter param3 = new sqlparameter()
          {
              parametername = "@id",
              sourcecolumn = "id"
          };
          sqlparameter[] param = new sqlparameter[] {param1,param2,param3 };
          cmd.parameters.addrange(param);
          sda.updatecommand = cmd;
          datatable dt = ds.tables["student"];
          foreach (datarow dr in dt.rows)
          {
              int oldid=convert.toint32(dr["id"]);
              if (oldid == id)
              {
                  dr["name"] = stu.name;
                  dr["age"] = stu.age;
              }
          }
          sda.update(ds,"student");
          return ds;
      }

删除数据

复制代码 代码如下:

/// <summary>
      /// 根据id删除一个学生
      /// </summary>
      /// <param name="id">返回更新后的dataset</param>
      public dataset deletestudent(int id)
      {
          dataset ds = getuserinfor();
          string str = "data source=.;initial catalog=student;integrated security=true";
          string sql = "delete from student where id=@id";
          var conn = new sqlconnection(str);
          var cmd = new sqlcommand(sql, conn);
          var sda = new sqldataadapter();
          sqlparameter param = new sqlparameter()
          {
              parametername="@id",sourcecolumn="id",sqldbtype=sqldbtype.int
          };
          cmd.parameters.add(param);
          sda.deletecommand = cmd;
          datatable dt=ds.tables["student"];
          foreach (datarow dr in dt.rows)
          {
              int oldid = convert.toint32(dr["id"]);
              if (oldid == id)
                  dr.delete();
          }
          sda.update(ds,"student");
          return ds;
      }