Sqldataadapter dataset datatable

1. sqldataadapter calls the Stored Procedure

DataSet ds = new DataSet();SqlDataAdapter da = new SqlDataAdapter("GetAllUsers", "server=(local);database=DemoDB;Integrated Security=true;");da.SelectCommand.CommandType = CommandType.StoredProcedure;da.Fill(ds);this.dataGridView1.DataSource = ds.Tables[0];

 

2. Efficient dataset Filling

The fill () method executes selectcommand and fills the data table in the dataset with the content and structure of the query results.

You can use the following methods to improve performance:

A. dataset. enforceconstrains = false

B. Call the beginloaddata () method on the datatable object.

3. reasons why the beginloaddata () method improves performance:

Define the data structure, data tables, data columns, and data associations displayed before filling a dataset.

It has been determined that the data can be loaded more efficiently.

4. How to display and define the data structure of a dataset

A. Create a typed dataset class

B. Create data columns and data associations of data tables programmatically-or create objects such as data tables, data columns, and data associations programmatically

5. Each Data row object has a rowstate attribute.
-Identifies the status of each row of data in a dataset.
-Status type
• Datarowstate. added the row has been inserted into the dataset.
• Datarowstate. Deleted the row has been deleted from the dataset.
• Datarowstate. Detached the row has been created but not added to the datarowcollection of the dataset.
• The row datarowstate. Modified has been changed
• The row datarowstate. unchanged has not changed
6. How dataset tracks changes
Each dataset maintains two copies of each row of data.
-Current version: datarowversion. Current

If (row. rowstate = datarowstate. added)
Row ["fieldname", datarowversion. Current] row [fieldname, datarowversion. Current]
-Original datarowversion. Original
If (row rowstate = datarowstate deleted) if (row. rowstate = datarowstate. Deleted)
Row ["fieldname", datarowversion. original]
7. Data update command
• A sqldataadapter or oledbdataadapter object has some command objects that can be used to change the data of the data source.
-Insertcommand
-Updatecommand
-Deletecommand
• Syntax: The data adapters for SQL and oledb and the Q data of each command are identical to each command object
-Public sqlcommand insertcommand {g et; set;} p q {g ;;}
8. Use commandbuilder to generate commands use commandbuilder to generate commands
• Insertcommand
-Insert a row for all rowstate added rows in the table at the data source. Insert values of all updatable Columns

(But does not include the identification expression or timestamp column) all values of updatable columns (but does not include the identification, expression, timestamp, and other columns ).
• Updatecommand
-Update all rows in the table whose rowstate is modified at the data source. Update all rows whose rowstate is modified in the table updated at the data source.

Update the values of all columns, except for columns that cannot be updated, such as the ID column or expression column.
-Update all rows that meet the following conditions: the column values in the data source match the primary key column values of the row, and the remaining columns in the data source match the original values of the row.
• Deletecommand
-Delete all rows in the table whose rowstate is deleted at the data source. Delete all rows that meet the following conditions: the column value matches the primary key column value of the row, and the remaining column in the data source matches the original value of the row.

9. Time to use the getchanges method of the DataSet object
• Call the getchanges () method when you need to pass data changes to another class used by another object
• Use the getchanges () method to get a copy of all the data changes in the dataset
-Starts when data is loaded.
-Starting from the last call of the acceptchanges () method
10. Merge changes to a DataSet object

• Merge two datasets using the Merge () method: one original dataset and one new dataset that only contains changes to the original dataset and one that only contains changes to the original dataset
Adataset Merge (anotherdataset) adataset. Merge (anotherdataset)
• The two merged datasets must have the same data structure
11. Use dataset to update the data source

• The update () method of the data adapter calls each updated row of the specified data table in an appropriate SQL statement.
• Sample Code
Adataadapter. Update (adataset, adatatable) adataadapter. Update (adataset, adatatable)
12. How dataset accepts data changes

• The A cceptchanges () method of the dataset submits data changes for all the datasets since the data is loaded or since this call.
• You can call the acceptchanges () method for the entire dataset, or call a data row object of a datatable object.

13. Conflict
• Open concurrency mechanism is used in non-connection Environments
-The database lock is released immediately after one step of data operation is completed.
-Non-connection environments use an open concurrency mechanism to ensure the synchronous access of other resources to the database
-Keep the database lock during the entire data operation through the concurrent mechanism
• Data conflicts occur when updating the database.
-Another application or service may have changed the data.
• Example
-Delete a row that does not exist.
-Update an updated Column
14. Conflict Detection
• The data adapter Configuration Wizard can generate SQL statements used to monitor conflicts
• When updating a database
-The data update command compares the current data and original values in the database.
-A conflict is thrown for any difference.
15. Conflict Resolution
• Use the haserrors attribute to test errors
-Dataset. haserrors
-Datatable. haserrors
-Datarow. haserrors
• Select one of the following policies to resolve conflicts
-Overwrite previous data operations with values in the dataset
• Applicable to the administrator system that forces data to overwrite data in the data source
-Maintain conflicting rows in the dataset so that the database can be updated later.
• Store conflicting data in a dataset for retry
• Default policy for "use open concurrency" option
• Reject conflicting rows and roll back to the initial value in the dataset
-Reject conflicting data in the local data set, roll back the conflicting data in the local data set, and roll back the data to the initial value loaded from the database.
-Call conflicting data rows of a dataset data table-call the rejectchanges () method for conflicting data sets, data tables, and data rows
• Reject conflicting rows and obtain the latest data from the database
-Call the clear () method of the dataset to load data from the database again.