Use datasets for data access

There are two different data access methods: connection, command, and datareader for data operations, and dataadapter for data operations, data is generally stored in the data set dataset in the memory. In this way, you can operate on the data in the memory, and then pass the modification to the database at the appropriate time.

 

 

1. Create a dataset:

1. Right-click solution to add a new item and find the dataset in the data:

2. Add a connection to the data connection in the server resource manager:

3. Find the table to be used in the database, select the table, and drag it to the right data set:

A dataset is created.

2. Add a new query to the dataset

1. Select the tableadapter of a table, right-click to configure advanced options, deselect the last two items, and remove concurrency.

2. Right-click to add a query, select SQL statement next, select the query type (to return a row or a single value or modify, delete, or add), and then write the SQL statement.

3. In this way, we can see the query we added in the dataset:

Iii. How to Use

1. Create an adapter object:

InfoTableAdapter adp = new InfoTableAdapter();

2. Create a memory table object

  Mydb.InfoDataTable table = new Mydb.InfoDataTable();

3. query return

  table = adp.GetData();

4. Modify

  InfoTableAdapter adp = new InfoTableAdapter();  adp.UpdateBycode(name, sex, nation, birthday, code);

 

Example: display the memory dataset (Dataset) in the datagridview, add, delete, and modify the dataset, and then submit all the modifications to the database.

The operations on the dataset are marked here. When the dataset is submitted to the database, the system automatically finds the row to be modified or deleted based on the tag and operates on it.

Code:

Private dataset _ DS = new dataset ();
Private void form2_load (Object sender, eventargs e) {// establish a connection sqlconnection conn = new sqlconnection ("Server = .; database = mydb; uid = sa; Pwd = 901004 "); // create the adapter sqldataadapter adapter = new sqldataadapter (); adapter. selectcommand = new sqlcommand (); adapter. selectcommand. commandtext = "select * from Info"; adapter. selectcommand. connection = conn; // execute the query and add the data to the memory dataset adapter. fill (_ DS); // display the memory dataset with the control datagridv Iew1.datasource = _ DS. tables [0];} private void button#click (Object sender, eventargs e) {// Add datarow ROW = _ DS. tables [0]. newrow (); row ["code"] = txtcode. text; row ["name"] = txtname. text; row ["sex"] = convert. toboolean (txtsex. text); row ["Nation"] = txtnation. text; row ["Birthday"] = convert. todatetime (txtbirthday. text); _ DS. tables [0]. rows. add (ROW);} private void button2_click (Object sender, EV Entargs e) {sqlconnection conn = new sqlconnection ("Server = .; database = mydb; uid = sa; Pwd = 901004 "); sqldataadapter adapter = new sqldataadapter (); // Add sqlcommand cmd = Conn. createcommand (); cmd. commandtext = "insert into info values (@ code, @ name, @ sex, @ nation, @ birthday)"; cmd. parameters. add ("@ code", sqldbtype. varchar, 50, "Code"); cmd. parameters. add ("@ name", sqldbtype. varchar, 50, "name"); cmd. parame TERs. add ("@ sex", sqldbtype. bit, 1, "sex"); cmd. parameters. add ("@ nation", sqldbtype. varchar, 50, "Nation"); cmd. parameters. add ("@ birthday", sqldbtype. datetime, 8, "Birthday"); adapter. insertcommand = cmd; // The modified sqlcommand cmd1 = Conn. createcommand (); fig = "Update info set [email protected], [email protected], [email protected], [email protected] Where [email protected]"; fig. A Dd ("@ code", sqldbtype. varchar, 50, "Code"); Parameters 1.parameters. add ("@ name", sqldbtype. varchar, 50, "name"); Parameters 1.parameters. add ("@ sex", sqldbtype. bit, 1, "sex"); Parameters 1.parameters. add ("@ nation", sqldbtype. varchar, 50, "Nation"); Parameters 1.parameters. add ("@ birthday", sqldbtype. datetime, 8, "Birthday"); adapter. updatecommand = cmd1; // Delete sqlcommand cmd2 = Conn. createcommand (); fig = "delete from Info where [email protected] "; Parameters 2.parameters. add ("@ code", sqldbtype. varchar, 50, "Code"); adapter. deletecommand = cmd2; // submit the adapter. update (_ DS); MessageBox. show ("OK");} private void button3_click (Object sender, eventargs e) {// Delete datarow ROW = NULL; // find a row foreach (datarow DR in _ DS. tables [0]. rows) {If (Dr ["code"]. tostring () = txtcode. text) {ROW = Dr ;}} if (row! = NULL) {// Delete row. Delete ();}}

 

 

Use datasets for data access