Table map in ADO.NET
Release Date: 4/1/2004
| Update Date: 4/1/2004
Dino esposito
Wintellect
March 15, 2002
Table maps are how to control data adapters how to copy data sheets and data columns from a physical data source to an object in ADO.NET memory. The data adapter object fills the data to the "Datase" or "Data Table" object using the "Pack" method. Internally, the "Fill" method uses a data reader to read the data and metadata of the structure and content of the source table. Then, the read data is copied to the temporary memory container (ie, the data table). The table mapping mechanism is a set of rules and parameters. Through them, you can control how the SQL result set is mapped to memory.
The following code shows a typical way of collecting data from the data source using the data adapter.
SqlDataAdapter Da;
DataSet DS;
Da = New SqldataAdapter (m_selectcommand, m_connectionstring);
DS = new dataset ();
Da.fill (DS);
It must be recognized that this code does not have a high and complicated place. I guess you have been very familiar with it and may have successfully run over one time. However, what happened to you know about the above code? The letter does not believe in you, run a small and famous object in the code, and its nature and behavior have a big impact on the final result. When you run the above code, each result set that may generate for the "Select" statement, there is a new "Dataset" object to the data set (originally empty). If you pass a non-empty data set to the "Fill" method, you will make a merger if you find the name of the data table, the contents of the result set, and the contents of the existing "Datasheet" object. Similarly, when the data line is started to copy the result set to a given data table, the contents of the matching column will be combined. Conversely, if the column name is not found, a DataColumn object is created (using the default setting) and it is added to the data table in the memory. What we have to make is that how does the adapter map the contents of the result set to the data set composition? How can the data adapter know which tables and columns match match? The TableMappings property of the data adapter is this behind-the-scene object, which determines how the table in the result set is mapped to the object in the data set.
Mapping mechanism
Once the "Select" command is terminated, and after the data adapter returns one or more result sets, the mapping mechanism begins. The adapter obtains a reference to the internal data reader object and then start processing the extracted data. By default, the data reader is positioned on the first result set. The following pseudo code 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? BMORERESULTS = Dr.NEXTRESULTS = DR.NEXTRESULT ()
IF (! bmoreresults)
Break;
Else
// map this new result set and company the loop
DT = MapCurrentResultSet (DS);
}
Else
AddRowTodataable (DT)
}
}
"Fill" method maps the first result set to "Datasheet" objects for a given dataset. Next, it performs a loop from the head to the end of the result set and adds the data line to the data sheet. When the end of the result set is reached, the method looks for the new result set and then repeats the operation.
The process of mapping the result set to the data set includes two phases:
• Table map • Column map
During the table map, the data adapter must find a name for the data table that will contain rows in the result set.
Each result set has a default name where you can change the name. The default name of the result set depends on the signature of the "Fill" method that has been used to call. For example, let's look at the two overloads below:
Fill (DS);
Fill (DS, "MyTable");
In the previous case, the name of the first result set defaults to the table. The names of other result sets are Table1, Table2, and the like, respectively. In the latter case, the first result set name is MyTable, and other result sets are also named myTable1, MyTable2, etc.
The adapter checks its TableMappings collection to see if there is an entry that match the result set default name. If the matching entry is found, the adapter will try to find a DataTable object with the name specified in the data in the data set. If there is no such a DataTable object, create the object and then populate. If there is such a data table in the data set, the content is merged with the contents of the result set.
Figure 1. Collect the result set to the DataSet object
In Figure 1, I assume that the query will at least three result sets. TableMappings set contains three default names and corresponding mapping names. If the "Select" command creates a result set with the default name table, its content will be merged into a new or existing "Dataset" object named "Employees". How to control this process from the perspective of the code? Please see the following code segment:
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 names you mapped to your name must match the default name generated by the "Fill" method. That is, if you change the last row to Da.Fill (DS, "MyTable");, the code cannot be successfully run, because the default name is now MYTABLE, MyTable1 and MyTable2, and for these names, front TableMappings There is no matching entry in the collection.
You can have any number of table maps that do not necessarily be associated with the expected quantity quantity. For example, you can only map the second result set Table1 returned by the command. In this case, the target result set contains three tables, named Table, Products, and Table2, respectively. The DataTableMapping object describes the mapping relationship between the result set and the "Dataset" object in the data set. SourceTable property returns the default result set name, and DataSetTable contains the mapping name.
If you use Visual Studio® .NET, you can configure a table mapping by running the Data Adapter Configuration Wizard.
Column map
If you complete the table map, there is nothing special. In fact, if your purpose is to make a good name for the 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 exactly the same. However, another aspect of this mapping mechanism is very interesting, this is the column map. The following figure extends the previous map, including a detailed column mapping process.
Figure 2. Table maps and column maps
The DataTableMapping object has an attribute called columnmappings, which is actually a collection of DatacolumnMapping objects. Columns map represents the name of the column of the result set and the corresponding column name in the "Dataset" object. Basically, the ultimate goal of the DataColumnmapping object is to enable the column names you used in the data table to distinguish the column names 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 data sheets in memory of the dataset. By default, the name of the target Datacolumn is the same as the source column. However, using the column map mechanism, you can change the names of the listed in memory. For example, when the column EmployeeID is copied to the memory, it is renamed to the ID and placed in a data table named EMPLOYEES.
The name of this column is the only parameter you can change at this level. Keep in mind that the entire mapping is automatically performed in the "Fill" method. When Fill terminates, each column in the source result set has been converted to a Datacolumn object, you can join and apply further changes, constraints, primary keys, read-only, automatically increment seeds and steps, support for null values. Wait.
In short, "filling" method completes two main operations. First, it maps the source result set to the table in memory. Second, it fills the table using data extracted from the physical data source. When all of the tasks are completed, the "fill" method may produce certain special exceptions. Conceptually, exceptions need to be resolved from the code angle. When the adapter does not find a table mapping or column mapping, and when the required data sheet or data column is not found in the target data set, the adapter will lead to a lightweight exception. Unlike the true abnormality that must be solved in the code, this special adapter exception must be solved with a declaration method by selecting an operation from a few sets of possible options. The adapter will trigger the following two lightweight exceptions:
• Lack of mapping • Missing architecture
Miss map operation
When the adapter is collecting data used to populate the data set, there are two situations that require a missing mapping operation. If you do not find the default name in TableMappings, or if there is no column name in the ColumnMappings collection of the table, you need to miss the mapping operation. You must customize the behavior of the MissingMappingAction attribute of the adapter to handle this exception. The value of this property belongs to the type of MissingMappingAction, as shown in the following table.
Error generates SystemException as long as it detects a lack of columns or tables. Ignore ignores unmapped columns or tables. Passthrough defaults; add a missing table or column using the default name.
Table 1. MissingMappingAction enumeration
Unless you have explicitly set the missingmappingaction attribute before filling the adapter, it will use the default PASSTHROUGH. Therefore, the table or column is added to the data set using the default name. For example, if you have not yet specified a table mapping for the result set named Table, the target data sheet will use the same name. In fact, the following statement will finally add new data sheets to the names Table and MyTable data sets, respectively.
Da.fill (DS);
Da.fill (DS, "MyTable");
If you set the MISSINGMAPPINGATION property to Ignore, just ignore any unmapped tables or columns. At this time, no errors are detected, but there is no content (or a column thereof) related to the result set involved in the target data set.
If the missingMappingAction property is set to Error, the adapter limits the systemException exception to each time the missing mapping is detected.
Once the adapter completes the mapping phase, the target data set is filld by the contents of the selected result set. If there is no necessary "data table" or DataColumn object in the target data set, another lightweight exception is triggered, and another declaration is required: the lack of architectural operations.
Missing architecture operation
If the data set does not include the table whose name has been determined in the table map, it is necessary to miss the architectural operation. Similarly, if the data set table does not include a column with the expected mapping name, the same operation is also required. MissingsChemaAction is to indicate the properties that you want to perform actions in the absence of a table architecture. The value of this property belongs to the MissingsChemaAction enumeration type, as shown in the following table.
Error generates SystemException as long as it detects a lack of columns or tables. Ignore ignores unmapped columns or tables. Add defaults; add a missing table or column using the default name. AddWithKey adds primary keys and constraints.
Table 2. MissingsChemaAction enumeration
By default, the MissingsChemaAction property is set to add. Therefore, a complete data set is formed by adding any missing constituent items - "Dataset" or Datacolumn. However, to remember that the architecture information added to this way is very limited. It includes only names and types. If you need additional information - such as primary key, automatic increment, read-only and empty settings - use the AddWithKey option. Note that even if the AddWithKey option is used, all available information about the column is not loaded into the Datacolumn. For example, AddWithKey lays a column as automatic increment, but does not set relevant seeds and step size properties. Moreover, the default value of the source column (if any) will not be copied. The primary key is imported, but it is not that any additional indexes you may have set will be imported. The other two options, Ignore and Error, the operation mode is exactly the same as the process of processing the missingmappingaction property.
Impact on code
Although I repeatedly mentioned (lightweight) operations, you declare that the operation to be executed is not as difficult to handle if the operations you want to perform in the absence of an object. On the other hand, this does not mean that your code is not affected by this type of operation. More specifically, a data set that has been filled with all the desired schema information is a code optimization form. When the configuration of the code is to repeatedly fill the empty data set using a fixed architecture, especially the case. In this case, the global "dataset" object preloaded with architecture information helps prevent all those requests to recover.
How to populate the data set using schema information belonging to a set of result set? You guess how, the original, the data adapter object has a custom method - Fillschema.
DataTable [] Fillschema (Dataset DS, Schematype MappingMode);
Fillschema first obtains a dataset and then adds any number of tables to it by the select command associated with the adapter. This method returns the various "data table" objects created in an array (only the architecture, no data). The mapping mode parameter can be a value defined in the SchemAtype enumeration.
Mapped applies any existing table map to incoming architecture. Configure the data set with the conversion architecture. Recommended options. Source ignores any table mappings on the data adapter. Configure the data set using the incoming architecture without any conversion.
Table 3. Schematype enumeration
The desired options can see its meaning from the literal. Mapped illustrates what operations are defined when mapping. Source intends to ignore any mappings you may have already set. The table in the data set will retain its default name, all columns will also retain their original names in the source table.
Manage user profile
In order to successfully complete the discussion of the table map, let's take a look at the true situation you may have to consider using the table mapping. Suppose you must manage different user profiles. Each profile requires you to access the same table, but return different columns. You can use many ways to solve this problem, but the ADO.NET table mapping mechanism may be the best way.
The concept is that you always use a query-query for the highest privileged profile - and then mapped to only the result data set that only the column specific to the current user profile. Some of the Visual Basic® code below illustrate this point:
DIM DA As SqldataAdapter
Da = New SqldataAdapter (m_selectcommand, m_connectionstring)
DIM DTM AS DataTableMapping
DTM = Da.TableMappings.add (Da.defaultsourceTables, "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 IF
DIM DS AS DATASET = New Dataset ()
Da.MissingMappingAction = missingmappingAction.ignore
Da.MissingSchemaAction = missingschemaAction.add
Da.fill (DS)
In this simple example, the query only returns a result set, I decided to use its default name table to identify the result set. Note that in order to achieve versatility, you should use the DEFAULTSOTABLENAME attribute of the data adapter object, not the name of the text. The table map defines different column mappings according to the user's role. If the user is an administrator, the data set will include more columns. Of course, the real implementation of these concepts such as role and permissions depends entirely. The key statement that makes the entire process is set to Ignore's missingmappingAction attribute value. As a result, the unmapped column is only ignored. Finally, you have to keep in mind that this is very important for the column name, this is very important, and the case of the column map name must match the case of the source name.
summary
In this column article, I discussed the table maps provided in ADO.NET. Table mapping is a collection of rules and behaviors that manage rows from data source to the delivery process of data sets in memory. The mapping consists of two steps - table maps and column mappings - it is just a first phase of a wide range of operations involving data set by data adapter objects. The second phase starts after the target data set is actually filled. Any logical exception in the mapping phase and filling phase can be controlled, the method is to declare which operations are to be done when the table or column does not explicitly bind to the dataset table or there is no desired table or the list of columns in the data set.
Dialog bar: difference
What is the difference between @REGISTER and @IMPORT? Where is the non-system assembly DLL that is best for ASP.NET applications?
First, the ASP.NET application is the .NET application. Thus, they need to link to any assembly that contains the objects that are planned. @Register instructions are used to solve this problem. Any assembly you registered on the page will later passed to the selected compiler later. The role of @Import instruction is not very important, because its function is simplified encoding. With @Import, you can import namespaces instead of an assembly. The assembly can contain more namespaces. For example, the assembly system.data.dll contains System.Data, System.Data.OleDb, System.Data.sqlclient, and more. By importing namespace, you can write a simpler code, so there is no need to specify the full path to a given object. By importing System.Data, you can use the data set through class DataSet instead of system.Data.DataSet. To use the dataset, you don't have to use the @Import instruction, but you can't miss the reference to System.Data.dll.
Specifically, for the ASP.NET application, you do not need to explicitly register any assembly provided in the Global Assembly Cache (GAC). Use @register just to reference custom assemblies that have been registered with the system GAC.
Where is these assessments stay? They must be placed in the bin directory in the application's virtual directory. If this directory does not exist, you should create this directory. If your ASP.NET application does not use a virtual directory, it will be implicitly run from the root directory of the web server. Therefore, the bin directory is in the root directory of the web server. For example, C: / INETPUB / WWWROOT / BIN.
Dino Esposito is Wintelle's ADO.NET expert and training faculty and consultant, and the work location is located in Rome, Italy. DINO is the special editor of MSDN Magazine, which is the writer of the Cutting Edge column. He also regularly submits developer Network Journal and MSDN NEWS. In addition, Dino is the founder of the BUILDING WIB SOLUTION with ASP.NET and ADO.NET, which is the upcoming BUILDING PRESS. If you want to contact Dino, you can send an email to dinoe@wintellect.com.
