First look at an example
configuration file
Copy Code code 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 Code code as follows:
String strconn = configurationmanager.connectionstrings["ConnStr"]. ConnectionString; Read the connection string from the configuration file
using (SqlConnection conn = new SqlConnection (strconn))//Create a Connection object with a using range, automatically close the connection, automatically destroy the object
{
Conn. Open ();//Turn on connection
using (SqlCommand cmd = conn. CreateCommand ())//Create Command object
{
Cmd.commandtext = "SELECT * from T_persons";//Command contents
DataSet DataSet = new DataSet (); Create a dataset that is equivalent to a data container
SqlDataAdapter adapter = new SqlDataAdapter (cmd); Create an Adapter
Adapter. Fill (DataSet); To populate a DataSet with query results
DataTable datatable = DataSet. Tables[0]; To save a query to a table in a DataTable object
for (int i = 0; i < DataTable. rows.count;i++)/Traversal
{
DataRow row = DataTable. rows[i];//get a line of objects
String name=row["F_name"]. ToString ();//Get the value of the corresponding column in the row
MessageBox.Show (name);
}
}
}
Object Resolution
Copy Code code as follows:
Connection:
and database interaction, you must connect it. The connection helps indicate the database server, database name, user name, password, and other parameters required to connect to the database. The connection object is used by the command object to know which data source to execute the command on.
Command:
Mainly can be used to send some instructions to the database, such as the database can be issued query, add, modify, delete data, and so on, and call the existence of the database stored procedures. This object is the schema on the connection object, that is, the command object is connected to the data source.
DataAdapter:
The main thing is to perform data transfer between the source and the dataset, which can be used to release commands through the command object and place the obtained data into the DataSet object. This object is the schema on the Command object and provides a number of features that work with the dataset.
DataSet:
This object can be viewed as a registers (Cache) that can be used to keep the data queried from the database and even to display the entire database. The ability of a dataset is not just to store multiple tables, but also to get some data table structures, such as primary keys, through the DataAdapter object, and to record the associations between the data tables. The DataSet object can be said to be a heavyweight object in the Ado.net, which is not capable of communicating with the data source on the DataAdapter object, that is, we are bridging the DataAdapter object as a DataSet object and the data from the data source.
DataReader:
The DataReader object can be used when we only need sequential reading of the data and no other action is required. The DataReader object simply reads data from the data source one at a time, and the data is read-only and does not allow other operations. Because DataReader restricts reading only one pen at a time while reading data, it is not only efficient but also cost-effective to use. The use of DataReader objects in addition to better efficiency, because do not have all the data back, it can reduce the network load. Ado.net uses the connection object to connect to the database, executes the SQL statement using the command or DataAdapter object, and returns the result of execution to DataReader or DataAdapter. The data results are then manipulated using the obtained DataReader or DataAdapter object.
strongly typed DataSet (plays in ado.net)
How to: Right-click on an item-add-new item-DataSet, and drag the table from Server Explorer to the dataset. Note that the drag-and-drop process is automatically based on table structure to generate a strongly typed dataset and other classes, not the data, the program is connected to the database, the database connection string is automatically written in the configuration file.
You must have a primary key when defining a table (table name: t_persons)
Copy Code code as follows:
T_personstableadapter adapter = new T_personstableadapter ()//first build an adapter
DataSet Demo. dataset1.t_personsdatatable datatable = Adapter. GetData ()//return results received with 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];//each row into a T_personsrow
MessageBox.Show ("name is:" +row.) F_name+ "Age is:" +row. F_age);//Fetch data (as with attributes)
}
update of strongly typed dataset
Copy Code code as follows:
T_personstableadapter adapter = new T_personstableadapter ();
DataSet Demo. dataset1.t_personsdatatable datatable = Adapter. GetData ()///Remove query results into table
DataSet Demo. Dataset1.t_personsrow row = datatable[0];//first row of table
Row. F_name = "NewName";//Modify first row data name field
int i = adapter. Update (DataTable);
if (i > 0)
{
MessageBox.Show ("Successful modification");
}
Else
{
MessageBox.Show ("modification failed");
}
The table in the database did not add fields: Right-key DataSet → Configuration
Add fields: Right-key dataset → configuration → Query analyzer
Insert new Row：
Copy Code code as follows:
T_personstableadapter adapter = new T_personstableadapter ();
int i = adapter. Insert ("Jisi Static", 22);
if (i > 0)
{
MessageBox.Show ("Insert succeeded");
}
Else
{
MessageBox.Show ("Insert failed");
}
Null value processing
Copy Code code as follows:
if (row. Isf_namenull ())//Determines whether the value of the field in the database is null (this is a method, called directly)
{
MessageBox.Show ("Data is empty");
}
Strongly typed DataSet Add custom SQL statement
Copy Code code as follows:
Right-key DataSet → add →query
Querying 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 statement (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 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 ("Yi Jongxing");//Successful Delete return 1 otherwise return 0
Optimizing strongly Typed DataSet batch processing
(1) Insert 3,000 data not optimized
Copy Code code as follows:
stopwatch SW = new Stopwatch ();
Sw. Start ()//Open Clock
T_testtableadapter adapter = new T_testtableadapter ();
for (int i=0;i<3000;i++)//calculate when inserting 3,000 data
{
Adapter. Insert (i.ToString (), i);
}
Sw. Stop ();
MessageBox.Show (SW. Elapsed.tostring ());
(2) Insert 3,000 data after optimization
Copy Code code as follows:
stopwatch SW = new Stopwatch ();
Sw. Start ()//Open Clock
T_testtableadapter adapter = new T_testtableadapter ();
Adapter. Connection.Open ()//Open connection
for (int i=0;i<3000;i++)//calculate when inserting 3,000 data
{
Adapter. Insert (i.ToString (), i);
}
Adapter. Connection.close ()//close connection
Sw. Stop ();
MessageBox.Show (SW. Elapsed.tostring ());
