Details of ADO. NET connectionless mode

Connectionless mode: Can not open the connection operation data in the memory, DataAdapter through management connection to provide services for connectionless mode, when to query data from the database, DataAdapter to open a connection, fill the specified DataSet, such as data read out automatically closes the connection, then can make changes to the data, use DataAdapter again to open the connection, persistent modify (update, delete, or update), finally close the connection automatically, use connectionless mode is one independent data, they will not change or rarely change, Because the actual data in the database may change between populating DataSet and updating the data, use the connection mode if you need to persist the data to the database immediately

Read data to DataSet: Connectionless means that a connection establishes a session with the database, the requested data is read into DataSet, and then the session is closed by disconnecting the database, when the session is closed due to disconnection from the database, DataSet becomes a connectionless database

/// <summary>
      ///  Query student information 
      /// </summary>
      /// <returns> Returns the populated student table 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");// call fill Method, SqlDataAdapter Automatically opens the connection, reads the data, and closes the connection 
          foreach (DataRow dr in ds.Tables["student"].Rows)
          {
              Console.WriteLine(dr["name"]);
          }
          return ds;
      }

Save the DataSet changes to the database
Insert data

/// <summary>
      ///  Insert student information, and return the inserted DataSet
      /// </summary>
      /// <param name="stu"> Student entity Class </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;

      }

Update the data

/// <summary>
      ///  According to the ID Update your name and age 
      /// </summary>
      /// <param name="name"> The name </param>
      /// <param name="age"> age </param>
      /// <param name="id"> students ID</param>
      /// <returns> Returns the updated version 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;
      }

Delete the data

/// <summary>
      ///  According to the ID delete 1 A student 
      /// </summary>
      /// <param name="id"> Returns the updated version 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;
      }