First, DataAdapter is used in ADO.NET to process online and offline from the database. At that time, the developer designed DataAdapter was to handle offline data, convenient to operate, about this, as long as the Fill () method could be invoked, then create a new DataTable in the DataSet. To Re-specify the name available with DataAdapter.Fill (Dataset, "TabelName"). At this point, Connection is also closed. DataAdapter can be used to submit queries and store the results into the DataSet, or to deliver changes to the database. Use its UPDATE method to reach changes to the database to submit a storage Datset.
DataAPter stores the results of the query in the DataSet or DataTable object. When this process is performed, DataAdapter uses a Command to communicate with the database, and use DataReader internally to get the query result, and finalize the result to DataSet new Go in the line. This is also the process of Fill. If there are two DataAdapter objects, use the same Connection object, you will create two Connection objects when you create, not the same, this solution is:
SqlConnection con = new SqlConnection ( "server = localhost; database = Northwind; Trusted_Connection = Yes;") SqlDataAdapter da = new SqlDataAdapter ( "select CategoryID, Description from Categories", con); SqlDataAdapter da = new SqlDataAdapter ( "select CategoryID, Description From customers ", CON);
Instead of writing a string, write a row separately.
Sometimes it may not want the architecture in the DataSet to be the same as the architecture in the database. One of the solutions of this is the alias method, the SELECT ID AS PRODUCT ID, AMOUNT AS Product Amount from Product; another solution The program is to use the TableMappings collection mechanism provided by DataAdapter, which can map the query results to the DataSet structure, which is more convenient and flexible. The TableMappings property returns a DataTableMappingsCollection object that contains a set of DataTableMappings, as long as the corresponding table name in the DataSet is the same as the table name in the database, you can use it to create a mapping (multiple tables in the DataSet). There is also a columnmappings attribute in TableMappings, which is similar to TableMappings. The principle is that DataAdapter has obtained the column name from the database from the database. It is especially noted that only the column name can only get the column name and cannot obtain the table name. DataAPter assumes the table name to Table, then encounter mapping The statement is mapped. Don't say it, see the code:
DataColumnMapping colMap; SqlConnection con = new SqlConnection ( "server = localhost; database = Northwind; Trusted_Connection = Yes;"); SqlDataAdapter da = new SqlDataAdapter ( "select CategoryID, Description from Categories", con); DataSet ds = new DataSet () DATATABLEMAPPING TBLMAP = DA.TABLEMAPPINGS.ADD ("Table", "CA"); // Here Table is the key, the mapping table name is CA
Colmap = tblmap.columnmappings.add ("categoryID", "ID"); // Mapping list colmap = tblmap.columnmappings.add ("description", "description"); // Response.write (tblmap.datasettable.tostring) )); Da.fill (ds); DataTable DT = DS.TABLES ["ca"]; // This is a table name after the map, and if it is still the table name of the database, it is invalid, special attention this.DataGrid1.DataSource = DT; this.dataGrid1.databind ();
After running the code, you will find that the column name on DataGrid1 is ID and description (^_^)
(Note: Before importing the namespace system.data.common in the name of DataTableMapping;)
You can also use the AddRange method to simplify the mapping of the tables and columns: (some of the code is above)
....... da.tablemapping tblmap = da.tablemappings.add ("table", "ca"); datacolumnmapping [] colmaparray = new datacolumnmapping [] {new DatacolumnMapping ("categoryid", "product number"), New DatacolumnMapping ("Description", "Description")}; tblmap.columnmappings.addrange (colmaparray); ......
This mapping relationship can only read the display from the database to the user. If you want to submit a changed Table change to the database, this time the library finds that the columns are different from the columns in the library, and the DataAdapter also provides MissingMappingAction property is handled.
DataAdapter1.MissingMappingAction = missingmappingAction.passthrough / ignore / error
It accepts the enumeration value of MissingMappingAction. This value indicates that if you can't find the same columns in the library, you map this column to the library. The ignore enumeration value indicates that the column that is ignored, Error means The abnormality is thrown to the corresponding column.
Page:
Page is common in applications, while DataAdapter itself provides simple features of paging, such as: DataAdapter1.fill (Dataset, Startrow, Rownum, "TableName") This feature is used for queries with less data volume. However, when there is a lot of data, the existence of this paging problem will be found. Its principle is if there is one hundred lines of data, divided into ten pages, 10 lines per page, return to the top 10 lines, then, then, second, delete the top 10 lines to get the next 10 lines, in this time, just in order to get 10 lines of data, but the database returns 20 lines, so on this class, will return 100 lines, and DataAdapter itself will help us delete 90 lines, because We look back or 10 lines, this performance is too low. Therefore, this article continues to introduce another paging method with high performance. In fact, this paging method is to store the key value of the last line on the previous page, filter it directly in the SQL statement, not like the previous type of DataAdapter to filter out. Example: con = new SqlConnection ( "server = localhost; database = Northwind; Trusted_Connection = Yes;"); da = new SqlDataAdapter ( "select top 50 CustomerID, CompanyName from Customers where CustomerID> 'BOTTM'", con); ds = New dataset (); da.fill (DS, "categories"); this.dataGrid1.datasource = ds.tables ["categories"]; this.dataGrid1.databind (); con.close ();
Here, if the last key value is "Bottm", it can be replaced with parameters so that 50 rows after 'bottm'. This approach achieves simple efficiency.
