Detailed introduction and application of the five main objects in ADO. NET

First, let's look at an example.
Configuration File
Copy codeThe Code is as follows:
<Configuration>
<ConnectionStrings>
<Add name = connStr "connectionString =" Data Source =. \ SQLEXPRESS; AttachDBFilename = | DataDirectory | \ SS. mdf; Integrated Security = true; User Instance = True "/>
</ConnectionStrings>
</Configuration>

Code
Copy codeThe Code is as follows:
String strconn = ConfigurationManager. ConnectionStrings ["connStr"]. ConnectionString; // read the connection string from the configuration file
Using (SqlConnection conn = new SqlConnection (strconn) // creates a connection object, out of the using range, the connection is automatically closed, and the object is automatically destroyed
{
Conn. Open (); // Open the connection
Using (SqlCommand cmd = conn. CreateCommand () // create a command object
{
Cmd. CommandText = "select * from T_Persons"; // command content
DataSet dataset = new DataSet (); // create a DataSet, which is equivalent to a data container
SqlDataAdapter adapter = new SqlDataAdapter (cmd); // create an adapter
Adapter. Fill (dataset); // Fill the query result in the dataset
DataTable datatable = dataset. Tables [0]; // Save the queried table in the DataTable object
For (int I = 0; I <datatable. Rows. Count; I ++) // traverse
{
DataRow row = datatable. Rows [I]; // retrieves a row of Objects
String name = row ["F_Name"]. ToString (); // obtain the value of the column corresponding to the row
MessageBox. Show (name );
}
}
}

Object Parsing
Copy codeThe Code is as follows:
Connection:
To interact with a database, you must connect to it. Connection help specifies the database server, database name, user name, password, and other parameters required to connect to the database. The Connection object will be used by the Command object to know which data source to execute the Command on.
Command:
It can be used to issue commands to the database, such as issuing query, adding, modifying, and deleting data commands to the database, and calling pre-stored programs in the database. This object is structured on the Connection object, that is, the Command object is connected to the data source.
DataAdapter:
Data transmission is performed between data sources and DataSet. After commands are issued through the Command object, the obtained data can be placed into the DataSet object. This object is structured on the Command object and provides many functions used with DataSet.
DataSet:
This object can be regarded as a Cache, which can keep the data queried from the database and even display the whole database. DataSet can not only store multiple tables, but also obtain data Table structures such as primary keys through the DataAdapter object and record the association between data tables. The DataSet object can be called ADO. NET heavyweight object, which is structured on the DataAdapter object and does not have the ability to communicate with the data source. That is to say, we use the DataAdapter object as the DataSet object and a bridge between data sources for data transmission.
DataReader:
You can use the DataReader object to read data in sequence instead of other operations. The DataReader object only reads data from the data source in a descending order, and the data is read-only and does not allow other operations. Because DataReader only reads one row at a time and can only be read-only, it not only saves resources but also improves efficiency. In addition to high efficiency, DataReader can reduce network load because it does not need to transmit all data. ADO. NET uses the Connection object to connect to the database, uses the Command or DataAdapter object to execute the SQL statement, and returns the execution result to DataReader or DataAdapter, then use the obtained DataReader or DataAdapter object to operate the data results.

Strong DataSet (the main play in ADO. NET)
Usage: Right-click the project and choose add> new item> DataSet. Then, drag and drop the table from the server resource manager to DataSet. Note that the drag-and-drop process automatically generates strong DataSet and other classes based on the table structure. If the data is not hosted, the program still connects to the database and automatically writes the database connection string in the configuration file.

When defining a table, you must have a primary key (Table Name: T_Persons)
Copy codeThe Code is as follows:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter (); // first generate an adapter
DataSet demo. DataSet1.T _ PersonsDataTable datatable = adapter. GetData (); // receive the returned results in the T_PersonsDataTable type.
For (int I = 0; I <datatable. Count; I ++) // traverse each row in the table
{
DataSet demo. DataSet1.T _ PersonsRow row = datatable [I]; // put each row in a T_PersonsRow
MessageBox. Show ("name:" + row. F_Name + "Age:" + row. F_Age); // retrieve data (like using attributes)
}

Update of strong DataSet
Copy codeThe Code is as follows:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter ();
DataSet demo. DataSet1.T _ PersonsDataTable datatable = adapter. GetData (); // retrieve the query result and put it into the table
DataSet demo. DataSet1.T _ PersonsRow row = datatable [0]; // obtain the first row of the table
Row. F_Name = "newName"; // modify the name field of the first row of data
Int I = adapter. Update (datatable );
If (I> 0)
{
MessageBox. Show ("modified successfully ");
}
Else
{
MessageBox. Show ("failed to modify ");
}

No field is added to the table in the database: Right-click the dataset> Configure
Add field: Right-click dataset → configure → query Analyzer
Insert new row:
Copy codeThe Code is as follows:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter ();
Int I = adapter. Insert ("gisi Jing", 22 );
If (I> 0)
{
MessageBox. Show ("inserted successfully ");
}
Else
{
MessageBox. Show ("insertion failed ");
}

Null Value Processing
Copy codeThe Code is as follows:
If (row. IsF_NameNull () // determines whether the value of this field in the database is null (this is a method that is called directly)
{
MessageBox. Show ("data is empty ");
}

Add custom SQL statements to a strongly Typed DataSet
Copy codeThe Code is as follows:
Right-click a dataset → add → Query
Query SQL statements
SELECT * FROM dbo. T_Persons
Where F_Age> 20
Call this method:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter ();
DataSet demo. DataSet1.T _ PersonsDataTable datatable = adapter. GetDataOlder ();
Query SQL statements (with parameters)
SELECT * FROM dbo. T_Persons
Where F_Age> @ Age
Call this method:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter ();
DataSet demo. DataSet1.T _ PersonsDataTable datatable = adapter. GetDataByAge (20 );
Delete An SQL statement (with parameters)
Delete from T_Persons
WHERE (F_Name = @ Name)
Call this method:
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter ();
Int I = adapter. DeleteByName ("Li Zhengxing"); // If the deletion is successful, 1 is returned. Otherwise, 0 is returned.

Optimize batch processing of strong DataSet
(1) Insertion of 3000 data records is not optimized
Copy codeThe Code is as follows:
Stopwatch sw = new Stopwatch ();
Sw. Start (); // turn on the clock
T_testTableAdapter adapter = new T_testTableAdapter ();
For (int I = 0; I <3000; I ++) // when 3000 pieces of data are inserted
{
Adapter. Insert (I. ToString (), I );
}
Sw. Stop ();
MessageBox. Show (sw. Elapsed. ToString ());

(2) Insert 3000 data records after optimization
Copy codeThe Code is as follows:
Stopwatch sw = new Stopwatch ();
Sw. Start (); // turn on the clock
T_testTableAdapter adapter = new T_testTableAdapter ();
Adapter. Connection. Open (); // Open the Connection
For (int I = 0; I <3000; I ++) // when 3000 pieces of data are inserted
{
Adapter. Insert (I. ToString (), I );
}
Adapter. Connection. Close (); // Close the Connection
Sw. Stop ();
MessageBox. Show (sw. Elapsed. ToString ());