Ado. NET connectionless Mode Detailed Introduction _ Practical skills

connectionless mode : The data can be manipulated in memory when no connection is opened, DataAdapter provides services through administrative connections for connectionless mode, and when querying data from the database, DataAdapter opens a connection to populate the specified dataset. After the data read, automatically close the connection, and then you can make changes to the data, use DataAdapter to open the connection again, persistent changes (whether it is update, delete or update), and finally automatically shut down the connection, the use of connectionless mode is a number of independent data, they will not change or rarely change, Because the actual data in the database may change during the time that the dataset is populated and the data is updated, use connection mode if you need to immediately persist the data to the database

reading data to DataSet: connectionless means that a connection establishes a session with the database, the requested data is read into the dataset, and then the session is closed by disconnecting from the database, and the session is closed due to a disconnect from the database. DataSet becomes a connectionless database

Copy Code code as follows:

<summary>
Query Student Information
</summary>
<returns> returns the dataset</returns> that fills the student table
Public DataSet getuserinfor ()
{
String str = "Data source=.;i Nitial 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 the data, and then closes the connection
foreach (DataRow Dr in DS. tables["Student"]. Rows)
{
Console.WriteLine (dr["name"]);
}
return DS;
}

Save a dataset's modifications to a database
Inserting data

Copy Code code 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=.;i Nitial 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 Code code as follows:

<summary>
Update name and age by ID
</summary>
<param name= "name" > Name </param>
<param name= "Age" > Ages </param>
<param name= "id" > Student id</param>
<returns> return to updated dataset</returns>
Public DataSet updatestudent (Student stu,int ID)
{
DataSet ds = Getuserinfor ();
String str = "Data source=.;i Nitial 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 Code code as follows:

<summary>
Delete a student based on ID
</summary>
<param name= "id" > Return to Updated dataset</param>
Public DataSet deletestudent (int id)
{
DataSet ds = Getuserinfor ();
String str = "Data source=.;i Nitial 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;
}