Table ing (from msdn) In ADO. net)

Release Date of table ing in ADO. Net: 4/1/2004 | update date: 4/1/2004

Dino esposito

Wintellect

March 15, 2002

Table ingIt is the process of controlling how the data adapter replicates data tables and data columns from a physical data source to an object in ADO. Net memory. Data adapter object Exploitation"Fill"Method"Select"Enter the data retrieved by the command"Dataset"Or"Data table"Object. Internally,"Fill"Methods Use a data reader to read data and metadata that describes the structure and content of the source table. Then, the read data is copied to the temporary memory container (that is, the data table.Table ing mechanismIs a set of rules and parameters, through which you can control how the SQL result set maps to objects in the memory.

The following code shows how to use a data adapter to collect data from a data source.

SqlDataAdapter da;DataSet ds;da = new SqlDataAdapter(m_selectCommand, m_connectionString);ds = new DataSet();da.Fill(ds); 

It must be admitted that this code is not complicated. I guess you are familiar with it and may have run it more than once. However, do you know what exactly happened behind the above Code? Believe it or not, a famous object is running in the Code. Its nature and behavior have a great impact on the final result. When you run the preceding code"Select"Each result set that may be generated by statement execution has a new"Data table"Add the object to the dataset (initially empty. If you pass a non-empty dataset"Fill"Method, as long as the data table name matches with it, the result set and the existing"Data table"The object content is merged. Similarly, when you start copying data rows from the result set to a given data table, the contents of the matching columns are merged. If no matching column name is found, a newDatacolumnObject (with the default settings), and will be added to the data table in the memory. The question we need to ask is, how does the adapter map the content of the result set to the dataset component? How does the data adapter know which tables and column names match? Data adapterTablemappingsAttributes are the objects behind the scenes. They determine how tables in the result set map to objects in the dataset.

Ing mechanism

Once"Select"After the command is terminated and the data adapter returns one or more result sets, the ing mechanism starts to take effect. The adapter obtains the reference to the internal data reader object and starts to process the extracted data. By default, the data reader is located in the first result set. The following pseudocode describes this process:

int Fill(DataSet ds){   // Execute the SELECT command and gets a reader   IDataReader dr = SelectCommand.ExecuteReader();      // Map the first result set to the DataSet and return the table   bool bMoreToRead, bMoreResults;   DataTable dt = MapCurrentResultSet(ds);      // Copy rows from the result set to the specified DataTable   while(true)   {   // Move to the next data row   bMoreToRead = dr.Read();   if (!bMoreToRead)   {      // No more rows in this result set. More result sets?      bMoreResults = dr.NextResult()         if (!bMoreResults)         break;      else         // Map this new result set and continue the loop         dt = MapCurrentResultSet(ds);   }   else   AddRowToDataTable(dt)   }}

"Fill"Method to map the first result set to"Data table"Object. Next, it loops the result set from start to end and adds data rows to the data table. When the end Of the result set is reached, the method searches for a new result set and repeats the operation.

The process of reflecting a result set to a dataset involves two phases:

•	Table ing
•	Column ing

During table ing, the data adapter must find a name for the data table that will contain the rows in the result set.

Each result set has a default name, which can be changed at will. The default name of the result set depends on"Fill"Method signature. For example, let's look at the following two reloads:

Fill(ds);Fill(ds, "MyTable");

In the previous case, the name of the first result set isTable. The names of other result sets areTable1,Table 2. In the latter case, the first result set name isMytableOther result sets are also namedMytable1,Mytable2.

The adapter checks itsTablemappingsSet to check whether there are entries matching the default name of the result set. If a matched entry is found, the adapter tries to find the datatable object with the specified name in the ing in the dataset. If this does not existDatatableObject. If such a data table exists in the dataset, its content is merged with the content of the result set.

Figure 1. Map the result set to the DataSet object

In Figure 1, I assume that the query generates at least three result sets.TablemappingsThe set contains three default names and corresponding ing names. If"Select"Command to create a default nameTableThe content of the result set is merged into"Employee"New or existing"Data table"Object. How can we control this process from the code perspective? See the following code snippet:

SqlDataAdapter da = new SqlDataAdapter(...);DataSet ds = new DataSet();DataTableMapping dtm1, dtm2, dtm3;dtm1 = da.TableMappings.Add("Table", "Employees"); dtm2 = da.TableMappings.Add("Table1", "Products");dtm3 = da.TableMappings.Add("Table2", "Orders");da.Fill(ds);

Of course, the default name mapped to your own name must be"Fill"The default name generated by the method is the same. That is to say, if you change the last line to DA. Fill (DS, "mytable");, the Code cannot run successfully, because the default name is now changedMytable,Mytable1AndMytable2For these namesTablemappingsThere are no matched entries in the collection.

You can map any number of tables, not necessarily related to the expected number of result sets. For example, you can map only the second result set Table1 returned by the command. In this case, the target result set contains three tables namedTable,ProductsAndTable 2.

DatatablemappingThe object describes"Data table"Mappings between objects.SourcetableProperty returns the default result set name, whileDatasettableThe ing name is included.

If you use Visual Studio. NET, you can configure table ing in a visual way by running data adapter Configuration Wizard.

Column ing

There is nothing special about table ing. In fact, if you want to name a dataset table, you can use the following code:

SqlDataAdapter da = new SqlDataAdapter(...);DataSet ds = new DataSet();da.Fill(ds);ds.Tables["Table"].TableName = "Employees";ds.Tables["Table1"].TableName = "Products";

The final effect is the same. However, this ing mechanism is interesting, that is, column ing. Expanded the previous graph, including detailed column ing process.

Figure 2. Table ing and column ing

DatatablemappingThe object has a nameColumnmappingsIs actuallyDatacolumnmappingObject set. Column ing indicates that the name of the column in The result set matches"Data table"The ing between the corresponding column names in the object. Basically,DatacolumnmappingThe object's ultimate goal is to make the names of the columns you use in the data table different from those in the data source.

SqlDataAdapter da = new SqlDataAdapter(...);DataSet ds = new DataSet();DataTableMapping dtm1;dtm1 = da.TableMappings.Add("Table", "Employees"); dtm1.ColumnMappings.Add("employeeid", "ID");dtm1.ColumnMappings.Add("firstname", "Name");dtm1.ColumnMappings.Add("lastname", "Surname");da.Fill(ds);

In the above code, I assume that the extracted result set contains columns named employeeid, firstname, and lastname. These columns must be copied to the data table subset in the memory of the dataset. By default, the name of the target datacolumn is the same as that of the source column. However, you can change the name of a column in the memory by using the column ing mechanism. For example, when the column employeeid is copied to the memory, it is renamedIDAnd put it in the data table named employees.

The column name is the only parameter that you can change at this level. Remember that the entire ing is in"Fill"Method. WhenFillAt the end, each column in the source result set is switchedDatacolumnObject, you can add and apply further changes-relationships, constraints, primary keys, read-only, auto increment seed and step size, and support for null values.

In short,"Fill"Method. First, it maps the source result to a table in the memory. Second, it uses the data extracted from the physical data source to fill the table. When you complete any of the tasks,"Fill"Methods may generate some special exceptions. In terms of concept, exceptions are abnormal situations that need to be specifically addressed from the code perspective. When the adapter does not find the table ing or column ing, and the required data table or column is not found in the target dataset, the adapter will cause a lightweight exception.

Unlike the real exceptions that must be solved in the code, this special adapter exception must be solved by selecting one of the few feasible options and declaring them. The adapter will cause the following two lightweight exceptions:

•	The ing is missing.
•	Lack of Architecture

The ing operation is missing.

When the adapter is collecting data to fill the dataset, there are two situations where the ing operation is missing. IfTablemappingsThe default name is not found in, or, ifColumnmappingsIf there is no column name in the collection, the ing operation is required. You must customizeMissingmappingactionAttribute behavior to handle such exceptions. The value of this attribute belongsMissingmappingactionEnumeration type, as shown in the following table.

Error	Systemexception is generated as long as the missing column or table is detected.
Ignore	Ignore unmapped columns or tables.
Passthrough	Default Option; use the default name to add missing tables or columns.

Table 1. missingmappingaction Enumeration

Unless you have explicitly setMissingmappingactionAttribute. Otherwise, the default value is used.Passthrough. Therefore, the default name is used to add tables or columns to the dataset. For exampleTableThe result set specifies the table ing. The target data table uses the same name. In fact, the following statement adds the new data table to the table namedTableAndMytable.

da.Fill(ds);da.Fill(ds, "MyTable");

IfMissingmappingactionSet propertyIgnoreTo ignore any tables or columns that are not mapped. At this time, no errors are detected, but there is no content (or a column of it) related to the result set in the target dataset ).

IfMissingmappingactionSet propertyError, The adapter is limitedSystemexceptionException.

Once the adapter completes the ing phase, it begins to fill the target dataset with the content of the selected result set. If the target dataset does not have any required"Data table"OrDatacolumnObject To trigger another lightweight exception. In this case, another declared operation is required: The architecture operation is missing.

Architecture operation missing

If a dataset does not contain a table whose name has been determined in the table ing step, the architecture operation is required. Similarly, if a dataset table does not contain columns with the expected ing name, the same operation is required.MissingschemaactionThis attribute is set to indicate the operation you want to perform in the absence of the table schema. The value of this attribute belongsMissingschemaactionEnumeration type, as shown in the following table.

Error	Systemexception is generated as long as the missing column or table is detected.
Ignore	Ignore unmapped columns or tables.
Add	Default Option; use the default name to add missing tables or columns.
Addwithkey	Add primary keys and constraints.

Table 2. missingschemaaction Enumeration

By default,MissingschemaactionSet propertyAdd. Therefore, you can add any missing components-"Data table"OrDatacolumnForm a complete dataset. However, remember that the architecture information added in this way is very limited. Only the names and types are included. UseAddwithkey. Note that even if you useAddwithkeyNot all available information about the column is loadedDatacolumn. For example,AddwithkeyMark a column as auto-incrementing, but do not set related seed and step attributes. In addition, the default value (if any) of the source column is not automatically copied. The primary key will be imported, but not any additional indexes you have set will be imported.

Two other options,IgnoreAndError, Its running mode and ProcessingMissingmappingactionAttributes are in the same way.

Impact on code

Although I have repeatedly mentioned (lightweight) exceptions, it is not as difficult to handle the operations you declare to be executed if an object is missing. On the other hand, this does not mean that your code is completely unaffected by such operations. More specifically, filling in a dataset that contains all the required architecture information is a form of code optimization. This is especially true when the code is constructed by repeatedly filling empty datasets with a fixed architecture. In this case, the global schema information is preloaded"Dataset"Object helps block all requests for recovery operations.

How to fill the dataset with the schema information that belongs to a set of result sets? What do you guess? originally, the data adapter object had a custom method-Fillschema.

DataTable[] FillSchema(DataSet ds, SchemaType mappingMode);

FillschemaFirst obtain a dataset, and then associate it with the adapterSelectCommand to add any number of tables to it as needed. This method returns the created items in an array."Data table"Object (only schema, no data ). The ing mode parameter can beSchematypeA value defined in enumeration.

Mapped	Apply any existing table ing to the input architecture. Configure the dataset with the converted architecture. Recommended options.
Source	Ignore any table mappings on the data adapter. Use the imported architecture to configure the dataset without any conversion.

Table 3. schematype Enumeration

The literal meaning of the optional options can be seen.MappedDescribes what operations are performed during ing. WhileSourceIgnore any mappings you may have set. The default names of tables in the dataset are retained, and all columns are retained with the original names in the source table.

Manage user configuration files

To successfully complete this discussion on table ing, let's look at a real situation where you may want to consider using Table ing. Assume that you must manage different user configuration files. Each configuration file requires you to access the same tables, but different column sets are returned. You can solve this problem in many ways, but ADO. Net table ing mechanism may be the best method.

The idea is that you always use a query-query for the configuration file with the highest permissions-and map it to a result dataset that only contains columns specific to the current user configuration file. The following Visual Basic code illustrates the key points:

Dim da As SqlDataAdapterda = New SqlDataAdapter(m_selectCommand, m_connectionString)Dim dtm As DataTableMappingdtm = da.TableMappings.Add(da.DefaultSourceTableName, "Employees")If bUserProfileAdmin Then          dtm.ColumnMappings.Add("EmployeeID", "ID")   dtm.ColumnMappings.Add("LastName", "Last Name")   dtm.ColumnMappings.Add("FirstName", "Name")   dtm.ColumnMappings.Add("Title", "Position")   dtm.ColumnMappings.Add("HireDate", "Hired")Else   dtm.ColumnMappings.Add("LastName", "Last Name")   dtm.ColumnMappings.Add("FirstName", "Name")End IfDim ds As DataSet = New DataSet()da.MissingMappingAction = MissingMappingAction.Ignoreda.MissingSchemaAction = MissingSchemaAction.Addda.Fill(ds) 

In this simple example, only one result set is returned for a query. I decided to use its default name.TableIdentify the result set. Note: To achieve universality, you should useDefaultsourcetablenameAttribute, not the text name (Table). Table ing defines different column ing based on the user role. If the user is an administrator, the dataset will contain more columns. Of course, the true implementation of concepts such as roles and permissions is entirely dependent on you. The key statement that makes the entire process work as expected is already setIgnoreOfMissingmappingactionAttribute Value. The result is that the columns not mapped are ignored. Finally, you must keep in mind that for column names, Case sensitivity is very important. The case sensitivity of the column ing name must match that of the source column name.

Summary

In this column, I discussed the table ing mechanism provided by ADO. net. Table ing is a set of rules and actions that manage the data transfer process from the data source to the in-memory dataset. Ing is composed of two steps-Table ing and column ing-it is only the first phase of a wider range of operations, involving the filling of datasets controlled by the data adapter object. The second stage starts after the target dataset is actually filled. Any logical exceptions in the ing and fill phases can be controlled: declares what operations are performed when a table or column is not explicitly bound to a dataset table or when there is no required table or column in the dataset.

Dialog Box: Differences

What is the difference between @ register and @ import? Where is the most suitable non-system assembly DLL for ASP. NET applications?

First, ASP. NET applications are. NET applications. Therefore, they need to be linked to any assembly that contains the objects to be used. The @ register command is used to solve this problem. Any assembly you register on the page will be passed as a reference to the selected compiler later. @ Import command is not very important because its function is to simplify the encoding. With @ import, You can import the namespace instead of the Assembly. An assembly can contain more namespaces. For example, the Assembly System. Data. dll contains system. Data, system. Data. oledb, and system. Data. sqlclient.

By importing a namespace, you can write simpler code without specifying the complete path to the given object. By importing system. Data, you can use a dataset through dataset instead of system. Data. dataset. To use a dataset, you do not need to use the @ import command, but cannot reference system. Data. dll.

Specifically, for ASP. NET applications, you do not need to explicitly register any assembly provided in Global Assembly Cache (GAC. Use @ register to reference a custom assembly that has been registered with the system GAC.

Where do these assemblies reside? They must be placed in the bin directory under the virtual directory of the application. If this directory does not exist, you should create this directory. If your ASP. NET application does not use a virtual directory, it runs implicitly from the root directory of the Web server. Therefore, the bin directory is under the root directory of the Web server. For example, C:/inetpub/wwwroot/bin.

Dino espositoWintellect's ADO. Net expert and training instructor and consultant, working in Rome, Italy. Dino is a Special Editor of msdn magazine and a writer of the cutting edge column. He also regularly contributes to Developer Network Journal and msdn news. In addition, Dino is to be published by Microsoft press.Building Web solutions with ASP. NET and ADO. netAnd is one of the founders of http://www.vb2themax.com. If you want to contact Dino, you can send an email to the dinoe@wintellect.com.