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

Let's start with an example
The configuration file

 
<configuration> 
<connectionStrings> 
<add name=connStr" connectionString="Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\SS.mdf;Integrated Security=true;User Instance=True"/> 
</connectionStrings> 
</configuration> 

code

 
string strconn = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; // Read the connection string from the configuration file  
using (SqlConnection conn = new SqlConnection(strconn)) // Create the connection object, out using Scope, the connection is automatically closed, 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 1 The data set is equal to 1 Data container  
SqlDataAdapter adapter = new SqlDataAdapter(cmd); // create 1 An adapter  
adapter.Fill(dataset); // Populates the query results into the dataset  
DataTable datatable = dataset.Tables[0]; // Will be queried 1 The table is saved in DataTable In the object  
for (int i = 0; i < datatable.Rows.Count;i++ )// traverse  
{ 
DataRow row = datatable.Rows[i];// achieve 1 Line object  
string name=row["F_Name"].ToString();// Gets the value of the corresponding column for that row  
MessageBox.Show(name); 
} 
} 
} 

Object to parse

 
Connection :  
 To interact with the database, you must connect to it. The connection help specifies the database server, database name, user name, password, and other parameters needed to connect to the database. Connection Objects can be Command Object, so you know which data source the command is executing on.  
Command :  
 Can be used primarily to issue to a database 1 Some instructions, for example, can be issued to the database query, add, modify, delete data and other instructions, as well as call stored in the database procedures. This object is architected in Connection  On the object, which is Command  The object is linked to the data source.  
DataAdapter :  
 Mainly in the data source as well DataSet  Between performing the work of data transfer, it can pass through Command  After the object gives the command, the acquired data is put in DataSet  In the object. This object is architected in Command Object on, and provides a lot of coordination DataSet  Functions to use.  
DataSet :  
 This object can be viewed as 1 Temporary storage area ( Cache ), you can keep the data you query from the database, or even display the entire database. DataSet  The ability to store more than one Table  It's just, it's still passable DataAdapter Object to obtain 1 Some data table structures, such as primary keys, can be used to record data table associations. DataSet  Objects can be said to be ADO.NET  A medium - weight object that is structured in DataAdapter On the object, it does not have the ability to communicate with the data source. That means we're going to be DataAdapter Object as DataSet  A bridge between objects and data sources.  
DataReader :  
 Can be used when we need to read the data sequentially without any other operation DataReader  Object. DataReader The object is 1 time 1 The pen reads the data from the data source down, and the data is read only. No other operations are allowed. because DataReader  When reading data, only read at a time is limited 1 The pen, which is read-only, is both resource-efficient and efficient to use. use DataReader  In addition to being efficient, the object can reduce the load on the network because it does not have to transmit all the data back. ADO.NET  use Connection  Object to connect to the database using Command  or DataAdapter Object to execute SQL Statement and returns the result of the execution to DataReader  or  DataAdapter , And then you use that DataReader  or DataAdapter  Objects manipulate data results.  

Strong type DataSet (highlight of ADO.NET)
How to use: right click - add - new item - data set on the project, then drag and drop the table from server explorer into DataSet. Note that the drag-and-drop process automatically generates classes such as strongly typed DataSet based on the table structure, but the program still connects to the database and writes the database connection string in the configuration file without holding the data.

You must have a primary key when you define a table (table name: T_Persons)

 
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter();// The first generation 1 An adapter  
DataSet demo .DataSet1.T_PersonsDataTable datatable = adapter.GetData();// I'm going to return the result T_PersonsDataTable Type to receive  
for (int i = 0; i < datatable.Count;i++ )// Traverse each of the tables 1 line  
{ 
DataSet demo .DataSet1.T_PersonsRow row = datatable[i];// every 1 Line in the 1 a T_PersonsRow In the  
MessageBox.Show(" The name is: "+row.F_Name+" Age is: "+row.F_Age);// Fetch data (like using attributes) 1 Sample)  
} 

Update to strongly typed DataSet

 
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter(); 
DataSet demo .DataSet1.T_PersonsDataTable datatable = adapter.GetData();// Fetch the query results into the table  
DataSet demo .DataSet1.T_PersonsRow row = datatable[0];// Take the table first 1 line  
row.F_Name = "newName";// Modify the first 1 The name field of the row data  
int i = adapter.Update(datatable); 
if (i > 0) 
{ 
MessageBox.Show(" Modify the success "); 
} 
else 
{ 
MessageBox.Show(" Modify the failure "); 
} 

No fields added to tables in database: right-click data set → configuration
Add fields: right-click data set → configure → query analyzer
Insert new line:

 
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter(); 
int i = adapter.Insert(" JiSiJing ", 22); 
if (i > 0) 
{ 
MessageBox.Show(" Insert the success "); 
} 
else 
{ 
MessageBox.Show(" Insert the failure "); 
} 

A null value processing

 
if(row.IsF_NameNull())// Determines if the corresponding value for this field in the database is null (this is 1 Method, direct call)  
{ 
MessageBox.Show(" Data is empty "); 
} 

Strongly typed DataSet adds custom SQL statements

 
 Right-click data set → add → Query 
 The query SQL statements  
SELECT * FROM dbo.T_Persons 
where F_Age>20 
 Call the method:  
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter(); 
DataSet demo .DataSet1.T_PersonsDataTable datatable = adapter.GetDataOlder(); 
 The query SQL statements ( With parameters ) 
SELECT * FROM dbo.T_Persons 
where F_Age>@Age 
 Call the method:  
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter(); 
DataSet demo .DataSet1.T_PersonsDataTable datatable = adapter.GetDataByAge(20); 
 delete SQL statements ( With parameters ) 
DELETE FROM T_Persons 
WHERE (F_Name = @Name) 
 Call the method:  
T_PersonsTableAdapter adapter = new T_PersonsTableAdapter(); 
int i = adapter.DeleteByName(" Zheng-xing li ");// Successful delete return 1 Otherwise returns 0 

Optimized strongly typed DataSet batch processing
(1) the time for inserting 3000 pieces of data was not optimized

 
Stopwatch sw = new Stopwatch(); 
sw.Start();// Open the clock  
T_testTableAdapter adapter = new T_testTableAdapter(); 
for(int i=0;i<3000;i++)// Computing insert 3000 The time of the data  
{ 
adapter.Insert(i.ToString(), i); 
} 
sw.Stop(); 
MessageBox.Show(sw.Elapsed.ToString()); 

(2) it takes time to insert 3000 pieces of data after optimization

 
Stopwatch sw = new Stopwatch(); 
sw.Start();// Open the clock  
T_testTableAdapter adapter = new T_testTableAdapter(); 
adapter.Connection.Open();// Open the connection  
for(int i=0;i<3000;i++)// Computing insert 3000 The time of the data  
{ 
adapter.Insert(i.ToString(), i); 
} 
adapter.Connection.Close();// Close the connection  
sw.Stop(); 
MessageBox.Show(sw.Elapsed.ToString());