Ado. NET notes--use DataAdapter to perform additions and deletions

Related knowledge:

1. Once the data in the dataset is downloaded from the database, it is no longer in contact with the database. If you modify the data in the dataset, you need to reestablish the connection and update the changes to the database with the SQL command
2. Writing SQL commands is often cumbersome and mechanized, ADO. NET provides a SqlCommandBuilder object that helps the DataAdapter object derive the required Update,delete and INSERT statements from the SELECT statement, and then DataAdapter can take advantage of these statements. Examine the data that was modified in the dataset and commit to the database
3. SqlCommandBuilder automatically generated SQL commands are convenient, but not flexible. In particular, when you need to write data to multiple tables, you often need to customize the Add and DELETE statements

code example:

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingSystem.Data;7 usingSystem.Data.SqlClient;8 9 namespaceConsoleApplication11Ten { One     class Program A     { -         Static voidMain (string[] args) -         { the             stringstrconn =@"Server=joe-pc;database=accountdbforsqlinjection;uid=sa;pwd=root"; -SqlConnection conn =NewSqlConnection (strconn); -  -             stringsql ="SELECT Accountid,accountname,password from account"; +SqlDataAdapter da =NewSqlDataAdapter (SQL, conn); -  +DataSet ds =NewDataSet (); A  atDa. Fill (DS," Account"); -  -Console.WriteLine ("existing data in Account table:"); -  -DataTable dt = ds. tables[" Account"]; -DataView DV =NewDataView (DT); inDv. Sort ="AccountID ASC"; -  to             foreach(DataRowView DRVinchDV) +             { -Console.WriteLine ("{0}:{1},{2}", drv[0], drv[1], drv[2]); the             } *  $Console.WriteLine ("");Panax Notoginseng  -             //add a row of data--insert theDataRow newRow = dt. NewRow ();//creates a new empty row based on the schema of the table +             //assign a value to a new row Anewrow["AccountID"] =4; thenewrow["AccountName"] ="New"; +newrow["Password"] ="123456"; -             //Adds a new row to the row collection of the table $ dt. Rows.Add (NewRow); $  -             //Modify a row of data--update -DataRow updateRow = dt. rows[0];//Modify the first row of data in a table theupdaterow["Password"] ="000000";//changed the password to 000000 - Wuyi             //Delete a row of data--delete theDataRow deleterow = dt. rows[1];//preparing to delete the second row of data from the original table - dt. Rows.remove (deleterow); Wu  -             //testing in-memory data AboutConsole.WriteLine ("once modified, the existing data in the account table"); $             foreach(DataRowView DRVinchDV) -             { -Console.WriteLine ("{0}:{1},{2}", drv[0], drv[1], drv[2]); -             } A  +             //The program pauses waiting for user input, checks the database, and NewRow is not inserted into the database at this time.  the             //At this point NewRow is simply created in the DataSet object memory and is updated to the database -Console.WriteLine (""); $Console.WriteLine ("Check to see if a new row has been created in the database, and then press ENTER to continue. "); the console.readline (); the  the             //Update to Database the             //creates a SqlCommandBuilder and passes the DataAdapter object in.  -SqlCommandBuilder Cmdbuilder =NewSqlCommandBuilder (DA); inConsole.WriteLine ("generated INSERT statement: {0}", Cmdbuilder.getinsertcommand (). CommandText); theConsole.WriteLine ("generated UPDATE statement: {0}", Cmdbuilder.getupdatecommand (). CommandText); the             //before the SQL statement that was built DataAdapter, the key column of select must contain the primary key, where the SqlCommandBuilder update succeeds AboutConsole.WriteLine ("generated DELETE statement: {0}", Cmdbuilder.getdeletecommand (). CommandText); the             //Perform the update theDa. Update (DT);//Update all modifications in the table to the database theConsole.WriteLine ("the data is updated to the database. Check whether the data has been updated in the database. "); +  -Console.WriteLine (""); the Bayi             //re-export the data in the database to detect whether the above operation was successful the             string_sql ="SELECT Accountid,accountname,password from account"; theSqlDataAdapter _da =NewSqlDataAdapter (_SQL, conn); -_da. Fill (DS,"NewAccount"); -Console.WriteLine ("existing data in Account table:"); the  theDataTable _dt = ds. tables["NewAccount"]; theDataView _DV =NewDataView (_DT); the_dv. Sort ="AccountID ASC"; -  the             foreach(DataRowView DRVinch_DV) the             { theConsole.WriteLine ("{0}:{1},{2}", drv[0], drv[1], drv[2]);94             } the  theConsole.WriteLine (""); the         }98     } About}

Program Analysis:

1. modifying data in a dataset only changes the data in the program's memory, and does not affect the database
2. Use SqlCommandBuilder to help build and modify SQL statements. By printing These statement commands, you can see that they are using arguments to execute SQL commands. When the data is formally updated, these parameter values are populated with the modified data from the dataset
3. Call the Update method of the SqlDataAdapter object to formally update the data to the database

Extension issues:

If the SQL statement when you Fill a dataset is the data that is being federated from multiple tables at the same time, how will the interpolated statement be generated when the data is updated?

Ado. NET notes--use DataAdapter to perform additions and deletions