Detailed introduction and application of the five main objects in ADO.NET
- 2020-05-19 04:33:08
- OfStack
Let's start with an example
The configuration file
code
Object to parse
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)
Update to strongly typed DataSet
No fields added to tables in database: right-click data set → configuration
Add fields: right-click data set → configure → query analyzer
Insert new line:
A null value processing
Strongly typed DataSet adds custom SQL statements
Optimized strongly typed DataSet batch processing
(1) the time for inserting 3000 pieces of data was not optimized
(2) it takes time to insert 3000 pieces of data after optimization
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());