Asp.net learning-ado.net (dataadapter in connectionless Mode)

In non-connection mode, we mainly discuss the following objects: dataadapter.
Dataadpater is used to transmit data between the data in the physical storage mode and the memory. Datatable indicates the database table in memory. Dataset is a set of tables and table relationships in the memory ). Dataview is used to represent the database view in memory.
The dataadapter object uses the datareader object in the background to obtain data from the database. The dataview object is used to filter and sort data. The datatable object can be used to track changes in data records, and decide whether to accept or reject these changes.
 

1. dataadapter object

The dataadapter object serves as a bridge between the physical database and the memory data table. Generally, the dataadapter object is used to obtain data from the database and load the data into the able object. Also, the modification of the data in the datatable object is written back to the physical database through the dataadapter object.
Example 1: use of a simple dataadapter object
=== App_code \ dawndataobject. CS ===

Code
Public   Class Movie_disconnect
{
Private   Static   Readonly   String _ Connectionstring;
Static Movie_disconnect ()
{
_ Connectionstring = Webconfigurationmanager. connectionstrings [ " Dawnenterprisedbconnectionstring " ].
Connectionstring. tostring ();
}

Public Datatable getall ()
{
// Initialize dataadapter
Sqldataadapter Dad =   New Sqldataadapter ( " Select ID, title, Director from movies " , _ Connectionstring );
// Initialize table
Datatable dt =   New Datatable ();
Dad. Fill (DT );
Return DT;
}
}

=== Showmovies. aspx

Code
< ASP: gridview ID = "Gridview1" Runat = "Server" Performanceid = "Objectperformance1" >
</ ASP: gridview >
< ASP: objectdatasource ID = "Objectperformance1" Typename = "Dawndataobject. movie_disconnect" Selectmethod = "Getall" Runat = "Server" >
</ ASP: objectdatasource >  

2. dataadapter Construction

In example 1, we see how to construct a sqldataadapter object and use the fill method to fill the data table into a able. It looks like the following:

Sqldataadapter Dad =   New Sqldataadapter ( " Select ID, title, Director from movies " , _ Connectionstring );
Datatable dt =   New Datatable ();
Dad. Fill (DT );

In the above example, we can't see the appearance of sqlconnection and sqlcommand objects. In fact, they still exist implicitly. These objects are called by the sqldataadapter object, even the open () of the sqlconnection object () the sqldataadapter object is also used for method calls.
If you want to explicitly use sqlconnection and sqlcommand objects, you canCodeIn this way:
Example 2: sqladapter Object Construction Method

Public Datatable getall ()
{
Datatable dt =   New Datatable ();
Using (Sqlconnection Conn =   New Sqlconnection (_ connectionstring )){
Sqlcommand command =   New Sqlcommand ( " Select ID, title, Director from movies " , Conn );
Sqldataadapter Dad =   New Sqldataadapter (command );
// Initialize table
Dad. Fill (DT );
}
Return DT;
}

As shown above, the sqldataadapter constructor can input a sqlcommand object. Sqldataadapter constructor In ADO. Net can be constructed using the following methods:
● Sqldataadapter (): No parameter. After being constructed, you can assign a sqlcommand object to the selectcommand attribute of the sqldataadapter object.
As a supplement, sqldataadapter also has the updatecommand, deletecommand, and insertcommand attributes, which will be mentioned later.
● Sqldataadapter (string commandtext, sqlconnection connection): the first parameter is the T-SQL statement, and the second parameter is a sqlconnection object.
When using this constructor, you do not need to explicitly declare the sqlcommand object.
● Sqldataadapter (string commandtext, string connectionstring): the method used in Example 1. You do not need to explicitly declare the sqlconnection and sqlcommand objects.
● Sqldataadapter (sqlcommand command): the method used in example 2.

3. Fill/fillschema method of sqldataadapter

The fill method of the dataadapter object. This method can be used not only as a datatable parameter, but also as a dataset parameter.
The fillschema method of the dataadapter object. You can add existing database constraints to the dataset/datatable parameter.
3.1 assign a value to Dataset
Because dataset is a set of datatable, you can fill multiple tables to the DataSet object.
Example 3: Fill multiple data tables to dataset objects

Private   Void Buttonfilldata_click ( Object Sender, eventargs E)
{
Dataset userdata =   New Dataset ();
Using (Sqlconnection testconnection =   New Sqlconnection (connectionstring )){
Sqlcommand testcommand = Testconnection. createcommand ();
Testcommand. commandtext =   " Select firstname, lastname from usertable; select permissiontype from permissionstable " ;
Sqldataadapter dataadapter =   New Sqldataadapter (testcommand );
Dataadapter. Fill (userdata );
} // Testconnection. Dispose called automatically.
}

3.2 fill method Overloading
By default, when sqldataadapter. Fill (Dataset) is called, the table name is not specified. Therefore, to obtain the datatable object in dataset, you need to use the index number.
You can also specify the tablename attribute of the corresponding table When fill is added to dataset. to specify the tablename attribute, call the following fill method:
Public int fill (Dataset dataset, string srctable );
The following example shows the differences between them:
Example 4: Specify the tablename attribute when the fill method is called to fill in the dataset

Protected   Void Page_load ( Object Sender, eventargs E)
{
If ( ! Ispostback)
{
String _ Connectionstring = Webconfigurationmanager. connectionstrings [ " Dawnenterprisedbconnectionstring " ].
Connectionstring. tostring ();
Dataset DS =   New Dataset ();
Using (Sqlconnection Conn =   New Sqlconnection (_ connectionstring ))
{
Sqlcommand command =   New Sqlcommand ( " Select ID, title, Director from movies " , Conn );
Sqldataadapter Dad =   New Sqldataadapter (command );
Dad. Fill (DS, " Movies " ); // Use tablename when calling the fill method
Dad. selectcommand =   New Sqlcommand ( " Select ID, name from moviecategories " , Conn );
Dad. Fill (DS ); // To use tablename
Gridview1.datasource = DS. Tables [ " Movies " ]; // Use tablename to specify
Gridview1.databind ();
Gridview2.datasource = DS. Tables [ 1 ]; // Use inde to specify
Gridview2.databind ();
}
}
}

There are a lot of methods to load fill, specific can be viewed: http://msdn.microsoft.com/zh-cn/library/system.data.sqlclient.sqldataadapter.fill.aspx

3.3 dataadapter's fillschema method
sqldataadapter class provides Fill and fillschema , this is critical for loading the data. Both methods can load information to dataset . Fill loads the data itself, while fillschema loads all available metadata (such as column names, primary keys, and constraints) related to a specific table ). The correct method for processing data loading is to run fillschema and then Fill . Example:
daauthors. fillschema (dspubs, schematype. source, "Authors");
daauthors. fill (dspubs, "Authors");
For more information, see articles :
http://msdn.microsoft.com/zh-cn/library/49z48hxc.aspx
http://support.microsoft.com/kb/314145/zh-cn
Example 5: Call the fillschema method:
// usually fill in the detailed metadata information with fillschema, and then fill the data with fill, example:

Sqldataadapter1.fillschema (dataset1, schematype. source, " Authors " );
Sqldataadapter1.fill (dataset1, " Authors " );
Datacolumn [] colarr;
Colarr = Dataset1.tables [ " Authors " ]. Primarykey;
MessageBox. Show ( " Column count: "   + Colarr. length. tostring ());
For ( Int I =   0 ; I < Colarr. length; I ++ )
{
MessageBox. Show (colarr [I]. columnname +   " "   + Colarr [I]. datatype. tostring ());
}

in the preceding example, if fillschema is not called, The primarykey information is not entered by default.
In addition, dataadapter also has a missingschemaaction attribute that accepts the following enum values:
● add --- add a required additional column to the able when a new row is added (default)
● addwithkey --- add all required columns to the able when a new row is added.
● error --- when a new row is added, an error is thrown if this row does not match the current able.
● ignore --- when adding a new row, if this row contains columns not in the datatable, the redundant columns are ignored.
DS. missingschemaaction = missingschemaaction. addwithkey