Ado. Net learning Summary

1. Connection
ADO. the biggest feature of net is that it supports operations on the content in the database when the connection is disconnected, which can greatly save the consumption caused by excessive connections, the previous article has provided an example to illustrate ADO. net. We can open the connection when getting data from the database, disconnect the connection after obtaining the data, and operate the data in the dataset, then open the connection when updating the content in dataset to the database. Datareader must be connected all the time.
Pay attention to the following points when using this feature:
(1) The connection must be closed when the connection attribute is changed.
(2) Select conn. changedatabase (dbname) when switching the database to reduce the consumption caused by disconnection and new connection round-trip
Ado. Net also supports built-in database connection pools. After a connection is closed, the connection remains in the pool for a period of time before it is actually closed. If someone requests to establish the same connection before the timeout, the opened connection is allocated to the requester, which can reduce the consumption of frequently opened and disconnected connections. However, in SQL Server 2000, connections with integrated security cannot be pooled.
The events involved in the connection include dispose, infomessage, and statechange, which can be found on msdn and will not be described in detail.
Template code:
Dim conn as sqlconnection
Conn = new sqlconnection ("...... ") 'Is the connection string
Conn. open ()
'.
Conn. Close ()

2. Command object
Ado. Net allows three different methods to obtain data commands, dataset, datareader, and command in the database. It is the most basic method to obtain data by executing SQL commands.

(1) creation can be created in two ways
A. Create a new command object
Dim cmd as new sqlcommand
Cmd. Connection = Conn
Cmd. commandtext = "select * from customer"
B. Obtain the reference to the command object in conn.
Dim cmd as sqlcommand
Cmd = conn. createcommand ();
Cmd. commandtext = "select * from customer"
Recommended method 2

(2) Four Execution Methods
Executenonquery () returns the number of lines affected by the command
Executescalar () returns the first column of the First row (used with the set function)
Executereader () returns a datareader object
Executexmlreader () returns an xmlreader object

(3) parameters are mainly used in the stored procedure in the complex and simplified forms.
Complex Method:
Dim Param as new sqlparameter ("@ return", sqldbtype. INT)
Param. Direction = parameterdirection. returnvalue
Cmd. Parameters. Add (PARAM)
Simplified Method
Cmd. Parameters. Add ("@ return_value", dbtype. int32). Direction = parameterdirection. returnvalue
Suggestion: If you need to use a parameter when processing the output value, you do not need to use a parameter when only processing the input value.

(4) Transactions
SQL statement:
Begin tran
SQL operations
If @ error <> 0
Begin
Rollback tran
Return @ Error
End
Commit tran
Return 0
Write transactions in ADO. net
Cmd. Transaction = conn. begintransaction ()
Try
{
Cmd. commandtext = "..."
Cmd. executenonquery ()
Cmd. transaction. Commit ()
}
Catch (exception ex)
Cmd. transaction. rollback ()
End try
If you want to combine the Database Transaction Processing with some external systems (for example, when the database is updated simultaneously, if the Web update fails, you want to roll back the transaction ), select the client to write the transaction processing (using ADO. net)
Write the transaction statement directly on the server if you only perform database transaction processing (write the transaction in SQL Server2000)
You can create savepoint in a transaction to implement partial transaction rollback.
Cmd. transaction. Save ("new customer ")
Cmd. transaction. rollback ("new customer ")

(5) batch query if multiple SQL statements can be executed together, you can perform batch query. Datareader supports batch query of datasets.
Cmd. commandtext = "select * from customer; select * From inovince ;"
Dim RDR as sqldatareader
RDR = cmd. executereader ()
RDR contains the execution results of two SQL statements.

3. datareader object
The datareader object can only perform top-down access to the queried dataset, but it is highly efficient. If you only access data, you can use datareader. However, datareader must be connected all the time. Therefore, a small part of the result is first stored in the memory and then read from the database, which is equivalent to a cache mechanism. This is obvious for millions of query results.
Template code:
Do while RDR. Read ()
Console. writeline (RDR (0) 'can also output RDR ("mermerid ")
Loop
If you want to limit the type, you can output (string) RDR (0) or RDR. getstring (0)

When reading data from datareader, check whether the attribute is empty. If the attribute can be empty, check whether the attribute is empty.
If not RDR. isdbnull (0)
Console. writeline (...)

When reading records with datareader, the database is locked by default. You can change the default attribute of datareader by changing it.

If the data in datareader is obtained by executing a batch processing statement, you can access it through nextresult.
Template code:
Do
Do while RDR. Read ()
Console. writeline (RDR (0 ))
Loop
Loop while RDR. nextresult ()

Process metadata (display the status of each attribute)
Dim schema as datatable
Schema = RDR. getschematable () 'to obtain the metadata table. Each column in the table corresponds to the feature set of each attribute.
For the row corresponding to each column attribute, the Data Type of this column attribute is obtained through row ("datatype.
4. Dataset
The function of dataset in ADO. NET is to provide a disconnected storage for the data source without having to worry about the data source. You can only perform operations in dataset.
There are three ways to create Dataset: 1. Use dataadapter 2, use XML file 3, manually determine the architecture, and then input data row by row.
This section describes the first method.
Dataadapter is used to connect dataset to basic data storage. It is essentially a meta-command object.
It includes selectcommand object, insertcommand object, updatecommand object, and deletecommand object.
Template code:
Dim dataadpater as new sqldataadapter ("select * from student", Conn)
Dim dataset as new dataset ()
Dataadapter. Fill (Dataset)
In this case, the dataset table name defaults to table
If you use batch query and enter the obtained results in dataset, the default table names are table, Table1, Table2 ......

Tablemappings:
Table Name ing:
Generate a dataadapter and map the table names.
Dataadapter. tablemappings. Add ("table", "customer ")
Dataadapter. Fill (Dataset)
In this case, the table alias becomes "customer" (you can use table or customer operations on dataset), and Dataset. Tables ("customer") can reference this table.
Or
Dataadapter. tablemappings. Add ("adonet", "customer ")
Dataadapter. Fill (dataset, "adonet ")
Column name ing:
Dataadapter. tablemappings. Add ("table", "customer ")
Dataadapter. tablemappings ("customer"). columnmappings. Add ("customerid", "ID)
Dataadapter. Fill (dataset, "customer ")

Schema: adds a schema through fillschema.
1. Add a primary key
Customertable. primarykey = new datacolumn [] {customertable. Columns ("customerid")} (add a primary key as an array)

2. Add Link
Dataset. relations. add ("customers_invoices", dataset. tables ("customers "). columns ("customerid"), dataset. tables ("invoinces "). columns ("customerid"), true)

3. Add Constraints
There are two main constraints: unique constraint and external code constraint (uniqueconstraint, foreignkeyconstraint)
The unique constraints are divided into deleterule (cascade deletion constraint), updaterule (cascade update constraint), and acceptrejectrule (constraints when you call acceptchanges or rejectchanges)

4. Add a trigger
You can add a trigger to the dataset event in Step 6.
Rowchanging, rowchanged, columnchanging, columnchanged, rowdeleting, rowdeleted

5. Column Architecture
For example, add a column attribute: customertable. Columns ("customerid"). readonly = true
Or add the autoincrement column:
Customertable. Columns ("customerid"). autoincrement = true
Customertable. Columns ("customerid"). autoincrementseed = 1 (column start position)
Customertable. Columns ("customerid"). autoincrementstep = 1 (column increment step)
Some people may say that this is not enough for DBMS. Why is it so costly to write the dataset again in ADO. Net?
This is mainly because of efficiency considerations. If the client-side error input can be found on the client, it can reduce unnecessary transmission without being transferred to the server for verification.

Expression column:
Dim excolumn as new datacolumn ("linetotal ")
Excolumn. datatype = typeof (float)
Excolumn. Expression = "(price-(price * discount) * quantity )"
Dataset. Tables ("items"). Columns. Add (excolumn)
V. dataset manipulation
In dataset, datarow is the basic storage location of all its data. It is mainly composed of a value array, representing a single row of datatable.
Datarow mainly includes the following information: 1. Current value of each column in the row, 2. original value of each column in the row, 3. Row status, 4. Links between the parent row and the Child row

Initialize a datarow:
Datatable = dataset. Tables [0];
Datarow newrow = able. newrow (); // use a datatable to generate a datarow. The modes in the datatable can be used.
Datatable. Rows. Add (newrow );

Delete row:
Datatable. Rows. Remove (row instance );
Datatable. Rows. removeat (row number );
Datarow. Delete (); // The row itself is removed.

Read and Write datarow values:
Row ["column name"], row [column number] can reference one of the attributes.
Datacolumn A = datatable. Columns ("column name"); // you can obtain a column

Batch modify rows:
Beginedit () starts to change, endedit () ends the change, and writes the change result to dataset, canceledit (), cancel the change
For example:
Row. beginedit ();
Modify row
Row. endedit ();

Batch load data to datatable
Datatable. beginloaddata ();
Datatable. loaddatarow (row1, false); // when the second parameter is true, able. acceptchanges () is called to accept the change. If it is false, add it directly.
......
Datatable. endloaddata ();
This data loading method can block all data constraints during data loading, and the indexes will not be maintained, which greatly speeds up data loading.

Row version:
Current: Current Value
Default: determines the default value of a row based on the operation.
Original: The value after the last call of acceptchanges ()
Proposed: The value changed before calling acceptchanges ()
For example, to obtain the original value of a row:
String oldstring = row ("firstname", datarowversion. Original );

Row status:
Row. rowstate: the row state is obtained. For example, after deletion, the row state is changed to deleted, and after data storage is updated, the row state is changed to unchanged.

6. dataset navigation
In ADO. net, each table maintains its relative independence and allows you to navigate related rows between different tables at the row level (navigation down to the Child row and parent row in the upward navigation)
For example, datarow [] invoicerows = custrow. getchildrows ("customer_invoice"); // navigate to the Child row through the link

VII. dataview
Dataview provides an external mode for the database structure.
At the same time, dataview can also provide the data binding function for Form Controls and Web controls. In each able, A dataview is built into the datatable. defaultview ();

Create dataview
Dataview sortedview = new dataview (datatable );

Sort dataview
Datatable. defaultview. Sort = "lastname ";
Datatable. defaultview. Sort = "lastname, firstname DESC ";

Filter dataview:
1. You can set the rowfilter attribute to filter data.
Datatable. defaultview. rowfilter = "vendor = 'rawlings '";
However, the filter expression can only be set to a relatively simple expression with limited functions, but it can meet basic requirements.
You can also perform a simple search in datatable and return a datarow array, for example:
Datarow [] compoundrows = datatable. Select ("vendor = 'wilson 'and price> 20.00)
2. Use rowstate to filter data
Datatable. defaultview. rowstatefilter = "dataviewrowstate. originalrows" can be used to filter out rows that meet the requirements.

Search for dataview:
Compared with dataview, rowfilter is used for filtering to obtain a rectangular dataset. Use Find and findrows to find rows matching a specific key more accurately.
When searching, you must first set the sort attribute of dataview:
Int found = datatable. defaultview. Find ("Wilson"); // obtain the row location
Datarowview [] rows = able. defaultview. findrows ("Rawlings") // get a row Array

8. update the database
In dataset, each able corresponds to a dataadapter. When dataadapter. Update (), the datatable is automatically updated.
You can use commandbuilder to automatically generate updated SQL commands Based on DataSet changes.
Sqlcommandbuilder bldr = new sqlcommandbuilder (dataadapter );
Dataadapter. Update (custtable );
However, the acceptance of dataset parameters by update is not to update dataset, but to update a table named "table" in dataset.
When using commandbuilder for updates, pay attention to the following points:
1. selectcommand must be valid
2. The master code must exist.
3. If the architecture changes after selectcommand fills the able, commandbuilder. refreshschema () should be called before Update ();
4. When updating the database, the relationship, constraints, or other tables in the dataset are not affected.
Although commandbuilder is easy to use, you do not need to write the update command yourself, but the performance of the automatically generated command is not high. In this case, you can write your own stored procedure or directly use an SQL statement 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 update range when updating dataadapter. Update:
Dataadapter. Update (invtable. getchanges (datarowstate. Deleted); // update only the deleted part

IX. Transactions
Tx = conn. begintransaction (isolationlevel. serializable );
Invda. selectcommand. Transaction = TX;
Transaction operations
TX. Commit (); Submit // Tx. rollback (); transaction rollback

10. Data Binding
Simple version: (for text boxes, labels, etc)
{Controls}. databindings. Add ("{property}", {datasource}, "{datamember }");
Property is the property to be bound, datasource is dataview or datatable, and datamember is an attribute of datasource.

Complex version: (for ListBox, ComboBox, etc)
You need to set the attributes to bind:
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 datasource, that is, it corresponds to the displaymember name value

DataGrid binding:
1. You can set the datasource attribute for static binding.
2. You can use the setdatabinding function for dynamic binding.

At the same time, the DataGrid supports master/detail table display.
Mastergrid. setdatabinding (customertable ,"");
Detailgrid. setdatabinding (customertable, "customer_invoices") // set the second attribute to a link constraint.
In this way, select a row in the primary table and find the corresponding row in the subtable Based on the foreign code of the row in the primary table.

After binding, the bound item has a currencymanager attribute to implement the cursor function.
Bindingcontext [mermertable] returns a currencymanager object. The position attribute can be changed to move the cursor.

From webasp.net Author: