ADO.NET無(wú)連接模式的詳細(xì)介紹

更新時(shí)間：2013年10月09日 16:00:38 作者：

這篇文章詳細(xì)介紹了ADO.NET無(wú)連接模式及操作實(shí)例，有需要的朋友可以參考一下

無(wú)連接模式：可以在沒(méi)有打開連接時(shí)在內(nèi)存中操作數(shù)據(jù)，DataAdapter通過(guò)管理連接為無(wú)連接模式提供服務(wù)，當(dāng)要從數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)時(shí)，DataAdapter打開一個(gè)連接，填充指定的DataSet,等數(shù)據(jù)讀取完馬上自動(dòng)關(guān)閉連接，然后可以對(duì)數(shù)據(jù)做修改，再次使用DataAdapter打開連接，持久化修改(無(wú)論是更新，刪除或是更新)，最后自動(dòng)關(guān)閉連接，使用無(wú)連接模式的情況是有一些獨(dú)立數(shù)據(jù)，它們不會(huì)發(fā)生改變或者很少改變，因?yàn)樵趯⑻畛銬ataSet和更新數(shù)據(jù)這段時(shí)間內(nèi)數(shù)據(jù)庫(kù)中的實(shí)際數(shù)據(jù)也許會(huì)發(fā)生改變，如果需要將數(shù)據(jù)立即持久化到數(shù)據(jù)庫(kù)請(qǐng)使用連接模式

讀取數(shù)據(jù)到DataSet:無(wú)連接意味著一個(gè)連接建立了一個(gè)與數(shù)據(jù)庫(kù)的會(huì)話，請(qǐng)求的數(shù)據(jù)讀入到DataSet中，然后通過(guò)斷開數(shù)據(jù)庫(kù)的連接關(guān)閉會(huì)話，這時(shí)會(huì)話因?yàn)榕c數(shù)據(jù)庫(kù)的斷開而關(guān)閉，DataSet成為一個(gè)無(wú)連接的數(shù)據(jù)庫(kù)

復(fù)制代碼代碼如下:

/// <summary>
      /// 查詢學(xué)生信息
      /// </summary>
      /// <returns>返回填充了學(xué)生表的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");//調(diào)用fill方法時(shí)，SqlDataAdapter會(huì)自動(dòng)打開連接，讀取數(shù)據(jù)然后關(guān)閉連接
          foreach (DataRow dr in ds.Tables["student"].Rows)
          {
              Console.WriteLine(dr["name"]);
          }
          return ds;
      }

將DataSet的修改保存到數(shù)據(jù)庫(kù)
插入數(shù)據(jù)

復(fù)制代碼代碼如下:

/// <summary>
      /// 插入學(xué)生信息，并返回插入后的DataSet
      /// </summary>
      /// <param name="stu">學(xué)生實(shí)體類</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;

      }

更新數(shù)據(jù)

復(fù)制代碼代碼如下:

/// <summary>
      /// 根據(jù)ID更新姓名和年齡
      /// </summary>
      /// <param name="name">姓名</param>
      /// <param name="age">年齡</param>
      /// <param name="id">學(xué)生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;
      }

刪除數(shù)據(jù)

復(fù)制代碼代碼如下:

/// <summary>
      /// 根據(jù)ID刪除一個(gè)學(xué)生
      /// </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;
      }