The difference between datasets and SqlDataReader in ADO

overview:
One, SqlDataReader//connection-based, read-only access suitable for small amounts of data.
SqlDataAdapter//based on non-connected, suitable for large data volume, can be modified, and finally return the results of the changes to the database. Requires a little more resources.
Two, SqlDataAdapter the dataset into a dataset after reading the data, and the dataset has local customer service memory.
Three, SqlDataReader return is a data reader, only one of the reading, operation is not flexible, generally in read-only time to use.
SqlDataAdapter returns a dataset or a table that can be manipulated in any of its data
Four, in a different notation:
Sqldatreader must open the database before executing, and then generate a command object. The value is then assigned by the Command.ExecuteReader () method. You must close the join manually after completion.
SqlCommand cmd = new SqlCommand ("SELECT * from Stu", Conn);
Conn. Open ();
SqlDataReader rdr = cmd. ExecuteReader ();
.....
Conn.close (); When the
SqlDataAdapter executes, the database is automatically hit, and the ExecuteReader method without command is assigned, and the join is automatically disconnected when completed.
SqlDataAdapter adptr = new SqlDataAdapter (SQL, conn);
DataSet ds = new DataSet ();
Adptr. Fill (ds, "Stu");

instance:

using (SqlConnection conn = new SqlConnection ("Data source=.; Initial catalog=mytest; User Id=sa; password=123456 ")) {Conn.                Open (); using (SqlCommand cmd = conn. CreateCommand ()) {//input 1 ' or ' 1 ' = ' 1 will cause SQL injection vulnerability//CMD.                    CommandText = "Select age from T_student where Name= '" +txtname.text+ "'"; Cmd.                    CommandText = "Select age from T_student where [email protected] or age> @aaa"; Cmd.                    Parameters.Add (New SqlParameter ("@Name", txtName.Text)); Cmd.                    Parameters.Add (New SqlParameter ("@aaa", Convert.ToInt32 (Txtage.text))); Insert INTO .... VALUES (@Name, @Age)//delete .... where [email protected]// The update T1 set [email protected]//@ parameter cannot be used to replace//cmd such as table name, field name, select keyword, and so on.           CommandText = "Select age from @TableName";         Cmd.                    Parameters.Add (New SqlParameter ("@TableName", "t_student")); Cmd. Parameters.Add (New SqlParameter ("@Name", txtname));//beginners do not write the wrong way by using (SqlDataReader reader = cmd. ExecuteReader ()) {while (reader. Read ()) {//getint32 gets the int type//getint64 gets the L Ong type (in database is bigint) int age = Reader.                            GetInt32 (0); MessageBox.Show (age.                        ToString ()); }                    }                }            }

using (SqlConnection conn = new SqlConnection ("Data source=.; Initial catalog=mytest; User Id=sa; password=123456 ")) {Conn.                Open (); using (SqlCommand cmd = conn. CreateCommand ()) <span style= "White-space:pre" ></span> {cmd.                    CommandText = "Select * from t_student where age< @aaa"; Cmd.                    Parameters.Add (New SqlParameter ("@aaa", 60)); Cmd.                    ExecuteReader (//sqldataadapter is a class that fills us with SqlCommand query results into a dataset//                    SqlDataAdapter adapter = new SqlDataAdapter (cmd);                    The dataset is equivalent to a local complex set (list<int>) DataSet DataSet = new DataSet (); Adapter. Fill (DataSet),//execute cmd and populate the SqlCommand query result into the DataSet DataTable table = DataSet.                    Tables[0]; DataRowCollection rows = table.                    Rows; for (int i = 0; i < rows. Count;             i++)       {DataRow row = rows[i];                        int age = (int.) row["age";                        String name = (string) row["name"];                    MessageBox.Show (name + "," + age); }                }