Ado. NET Learning Notes (III.)

Ado| notes (because the book's code is written in C #, conversion to vb.net is really troublesome, the future will not be converted, forgive)
V. Manipulation of the DataSet
The DataRow in the dataset is the basic storage location for all its data, consisting primarily of an array of values representing a single row of the DataTable.
DataRow mainly includes several kinds of information: 1, the current value of each column in the row, 2, the original value of each column in the row, 3, row state, 4, link between parent row and child line

Initialize a DataRow:
DataTable Datatable=dataset.tables[0];
DataRow Newrow=datatable.newrow (); Using a DataTable to generate DataRow can use the patterns inside the DataTable
DATATABLE.ROWS.ADD (NewRow);

Delete Row:
DataTable.Rows.Remove (row instance);
DataTable.Rows.RemoveAt (line number);
Datarow.delete (); Line itself removed

Read and write DataRow values:
row["column name"],row[column number] can refer to one of the properties
DataColumn a=datatable.columns ("column name"); You can get a column

To batch changes to a row:
BeginEdit () starts the change, EndEdit () ends the change, writes the result of the change to Dataset,canceledit (), cancels the change
For example:
Row. BeginEdit ();
Make changes to row
Row. EndEdit ();

Bulk loading of data into a DataTable
Datatable.beginloaddata ();
Datatable.loaddatarow (Row1,false); When the second argument is true, the change is accepted when datatable.acceptchanges () is invoked, and false is added directly
......
Datatable.endloaddata ();
Using this data loading method can block all data constraints during data loading, the index will not be maintained, greatly speeding up the data loading speed

Version of row:
Current: Present Value
Default: Determines the default value of a row based on the different actions
Original: The value after the last call to AcceptChanges ()
Proposed: The value that was changed before calling AcceptChanges ()
For example, to get the original value of a row:
String Oldstring=row ("FirstName", datarowversion.original);

The status of the row:
Row. RowState gets the state of the row, for example, after the deletion becomes deleted, and the data store is updated and changed to unchanged

VI. DataSet navigation
Each table in Ado.net retains its relative independence, allowing navigation of related rows between different tables at the row level (navigating down to a child row, and the parent row up)
such as datarow[] Invoicerows=custrow.getchildrows ("Customer_invoice"); Navigating through a relationship to a child row

Seven, DataView
The DataView Data View provides the implementation of the external schema for the database structure.
DataView can also provide data binding for form controls and Web controls, and a DataView is built into each DataTable: Datatable.defaultview ();

Create DataView
DataView sortedview=new DataView (dataTable);

Sort the DataView
Datatable.defaultview.sort= "LastName";
datatable.defaultview.sort= "Lastname,firstname DESC";

To filter a DataView:
1, through the RowFilter property settings can be implemented to filter
datatable.defaultview.rowfilter= "vendor= ' Rawlings '";
However, the filter expression can only be set to a simpler expression with limited functionality, but it satisfies the basic requirements.
Also in the DataTable can be a simple search, return a DataRow array, example:
Datarow[] Compoundrows=datatable.select ("Vendor= ' Wilson ' and price>20.00)
2, through the RowState to filter
Datatable.defaultview.rowstatefilter= "Dataviewrowstate.originalrows" can filter out row with the required status

To search for DataView:
Relative to DataView using RowFilter to filter to get a rectangular dataset, using Find, findrows can more accurately locate the line matching the specific key
When searching, you must first set the DataView sort attribute:
int Found=datatable.defaultview.find ("Wilson"); Get the location of the row
Datarowview[] Rows=datatable.defaultview.findrows ("Rawlings")//passed a row array

Eight, update db
In a DataSet, when each DataTable corresponds to a dataadapter,dataadapter.update (), the DataTable is automatically updated.
Updates can be updated using CommandBuilder automatically generates updated SQL commands based on the changes in the dataset
SqlCommandBuilder bldr=new SqlCommandBuilder (dataAdapter);
DataAdapter.Update (custTable);
However, update accepts that the dataset parameter does not update the dataset but updates a table named "Table" in the dataset
When using CommandBuilder to update, pay attention to a few points:
1, SelectCommand must be effective
2, must have the main code
3, if the SelectCommand fill the DataTable after the schema changes, should be called before update () Commandbuilder.refreshschema ();
4, update DB is not affected by relationships, constraints or other tables in the dataset
Although it is convenient to use CommandBuilder, do not write the update command yourself, but the automatically generated command performance is not high, you can consider writing stored procedures or directly using the SQL statements with parameters, for example:
String insqry= "Insert into Customer (CustomerID) Values (@Customer)";
SqlCommand Inscmd=conn. CreateCommand ();
Inscmd.commandtext=insqry;
SqlParameterCollection insparams=inscmd.parameters;
Insparams.add ("@CustomerID", sqldbtype.uniqueidentifier,0, "CustomerID");
Dataadapter.insertcommand=inscmd;
DataAdapter.Update ();
You can also control the scope of updates when DataAdapter.Update () is updated:
DataAdapter.Update (Invtable.getchanges (datarowstate.deleted);//update only the deleted part

Ix. Affairs
Tx=conn. BeginTransaction (isolationlevel.serializable);
INVDA.SELECTCOMMAND.TRANSACTION=TX;
Transaction operations
Tx.commit (); Submit//tx. Rollback (); transaction rollback

X. Data binding
Simple version: (for text boxes, labels, etc.)
{Controls}. Databindings.add ("{property}", {DataSource}, "{dataMember}");
Where the property is to be bound, DataSource is DataView or Datatable,datamember is datasource one of the properties

Complex version: (For ListBox, ComboBox, etc.)
To set each property to implement the binding individually:
Datasource= an object that supports IList (DataTable or DataView)
DisplayMember: An attribute in the DataSource to be displayed
ValueMember: Determines which data row is referenced in the DataSource, that is, the implementation corresponds to the name value of DisplayMember

DataGrid bindings:
1, you can set the DataSource property to achieve static binding
2, you can use the SetDataBinding function to implement dynamic binding

The DataGrid also supports master/detail table display
Mastergrid.setdatabinding (Customertable, "");
Detailgrid.setdatabinding (customertable, "customer_invoices")//second property to be set as a relationship constraint
This selects one row in the primary table and finds the corresponding row in the child table based on the foreign code of the main table row in the child table

After binding, there is a CurrencyManager property in the binding entry that implements the cursor functionality
Bindingcontext[customertable] Returns a CurrencyManager object in which the position property can be changed to implement cursor movement.