Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "how to understand the ADO.NET connectionless mode". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Connectionless mode: you can manipulate data in memory when no connection is open. DataAdapter provides services for connectionless mode through administrative connections. When querying data from the database, DataAdapter opens a connection, fills the specified DataSet, and automatically closes the connection as soon as the data is read. Then you can modify the data, open the connection again using DataAdapter, and persist changes (whether update, delete or update) Finally, automatically close the connection, and in the case of connectionless mode, there is some independent data, which will not change or rarely change, because the actual data in the database may change during the period of filling DataSet and updating the data. 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 then the session is closed by disconnecting the database, when the session is closed because it is disconnected from the database, and the DataSet becomes a connectionless database

The copy code is as follows:

/ / /

/ / query student information

/ / /

/ / return the DataSet populated with the student table

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 you call the fill method, 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 changes to DataSet to the database

Insert data

The copy code is as follows:

/ / /

/ / insert student information and return the inserted DataSet

/ / /

/ / Student entity class

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

The copy code is as follows:

/ / /

/ / update name and age according to ID

/ / /

/ name

/ Age

/ Student ID

/ / return the updated DataSet

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

The copy code is as follows:

/ / /

/ / Delete a student according to ID

/ / /

/ / return the updated DataSet

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

}

This is the end of "how to understand ADO.NET Connectionless Mode". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.