Detailed introduction to the non-connection mode of ADO. NET

No connection mode: You can operate on data in the memory when the connection is not enabled. DataAdapter provides services for the connectionless mode through the Management connection. When you want to query data from the database, DataAdapter opens a connection, fill in the specified DataSet and close the connection immediately after the data is read. Then, you can modify the data and use DataAdapter to open the connection again for persistent modification (whether update, delete, or update ), finally, the connection is automatically closed. When no connection mode is used, some independent data will not be changed or rarely changed, because the actual data in the database may change during the time when the DataSet is filled and the data is updated. If you need to persist the data to the database immediately, use the connection mode.

Read data to DataSet: No connection means that a connection establishes a session with the database. The requested data is read into the DataSet, and the session is closed by disconnecting the database, in this case, the session is closed because it is disconnected from the database, and DataSet becomes a connectionless database.

Copy codeThe Code is as follows:
/// <Summary>
/// Query Student Information
/// </Summary>
/// <Returns> return the DataSet filled with the student table </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"); // when the fill method is called, SqlDataAdapter automatically opens the connection, reads data, and closes the connection.
Foreach (DataRow dr in ds. Tables ["student"]. Rows)
{
Console. WriteLine (dr ["name"]);
}
Return ds;
}

Save modifications to DataSet to the database
Insert data

Copy codeThe Code is as follows:
/// <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 Data

Copy codeThe Code is as follows:
/// <Summary>
/// Update the name and age based on the ID
/// </Summary>
/// <Param name = "name"> name </param>
/// <Param name = "age"> age </param>
/// <Param name = "id"> Student ID </param>
/// <Returns> return the updated 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 data

Copy codeThe Code is as follows:
/// <Summary>
/// Delete a student by ID
/// </Summary>
/// <Param name = "id"> return the updated 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;
}