Analysis of the .NET hosted provider
Release Date: 4/1/2004
| Update Date: 4/1/2004
Dino esposito
Wintellect
October 9, 2001
The Microsoft .NET hosting provider has many advantages over the mature OLE DB provider. First, it achieves a simplified data access structure, which often improves performance while do not affect functional capacity. In addition, the .NET managed provider provides the user directly to the user through the method and attributes. It uses more interface collections than the OLE DB. Finally, but not least, the .NET managed provider works within the boundary of the public language runtime (CLR), no COM interaction. For SQL Server 7.0 and SQL Server 2000, the hosted provider is directly connected to the line level and obtains a significant performance advantage.
The features provided by the .NET data provider can be divided into the following categories:
• Implementation of the method of the iDataAdapter interface, support DataSet classes • Supported data access, including classes that represent connectivity, commands, and parameters
The simplest function of the data provider is that only the data set is interacting with the caller when reading and writing. Another case is that you can control connection, transaction, and execute direct commands without having to consider SQL language. The following figure shows the class hierarchies of two standard hosting providers (OLE DB providers and providers for SQL Server).
Figure 1. Managed provider connections, executes commands, and acquires data in a data source.
Packaging connections, commands, commands, and readers are specific to providing programs and may generate a slightly different attributes and methods. Any internal implementation is strictly equipped with database recognition capabilities. The only class outside this architecture is a data set. This class is a general container that is commonly used as all of which is disconnected. Data category belongs to a super-namespace named System.Data. Classs specific to a data provider belong to a specific namespace. For example, system.data.sqlclient and system.data.oledb belong to a specific namespace. Although the architecture shown above is not too simple, it is a fairly basic architecture. This is a simplified architecture because it does not include all classes and interfaces involved. The following figure is closer to the real situation.
Figure 2. Categories involved in the hosting provider
The following table shows a list of interfaces that make up the .NET provider.
IDBConnection indicates a unique session IdbTransAction established with the data source represents a local non-distributed transaction IDBCommand indicates that a command iDataParameter executed when connected to the data source allows the parameter to be implemented for the command iDataReader to read only before the command is created. Read-only data stream iDataAdapter populates the data set and makes changes in the data set back to the data source IDBDataAdapter provides a method of performing typical operations (insert, update, selection, deletion) on the relational database.
In all interfaces, only iDataAdapter is mandatory and must exist in each managed provider. If you don't intend to implement one of the interfaces or a given interface, you will open this interface, but this will trigger a NotSupportedException exception. As long as it is possible, it is possible to avoid nothing to provide no operational implementation of the interface, as this approach may result in data corruption, for transaction processing / rollback, especially. For example, providing programs do not need to support nested transactions, even if the design of the IDBTransaction interface takes into account this situation.
Before further explaining the roles of each class playing in the overall operation of the .NET Provider, we will probably understand the naming conventions recommended by the hosting provider. If you plan to write a provider, knowledge in this area is very useful. The first criterion is to consider the namespace. Make sure to assign a unique namespace to your hosted provider. Next, the alias of the provider is used as the alias of the provider as the prefix of the class in any internal code and client code. For example, using ODBCConnection, ODBCCommand, ODBCDataReader, like the class names. In the case mentioned herein, the alias is ODBC. Also, try to use different files to compile different features. Realize connection
Provider connection class inherits from IDBConnection and must disclose Connectionstring, "Status", "Database", and ConnectionTimeout properties. The mandatory method includes "Open", "Close", Begintractions, ChangeDatabase, and CreateCommand. You don't have to implement transaction. The following code snippet is an example of a code for implementing a connection.
Namespace DotNetMydataProvider {
Public Class MyConnection: IDBConnection
{
PRIVATE CONNECTIONSTATE M_STATE;
PRIVATE STRING M_SCONNSTRING;
Public myconnection () {
m_state = connection;
m_sconnstring = ""
}
Public myconnection (string connString) {
m_state = connection;
m_sconnstring = connString;
}
Public idbtaction based begintransaction () {
Throw new NotSupportedException ();
}
Public IDBTRANSACTION Begintractions (isolationledLevel) {
Throw new NotSupportedException ();
}
}
}
You should provide at least two constructor, one of which is the default constructor with any parameters. Another suggested constructor only accepts the connection string. When the connection string is returned by the Connectionstring property, make sure that the returned string is always the string set by the user. The only exception may be caused by any security sensitive information you wish to delete.
The items you identify and support in the connection string depends on your own, but whenever it makes sense, you should use the standard name. The "Open" method is responsible for opening a physical channel that communicates with the data source. This operation should not be performed before calling the "open" method. If you open a connection to a time consuming memory, you can consider using some connection pool. Finally, if the provider provides automatic registration in distributed transaction processing, registration should be performed during execution of "Open".
A point in which the ADO.NET connection is different from other connections (for example, ADO connections) is that you need to make sure you can create and open the connection before any commands can be performed. The client must explicitly open and close the connection, without any way to implicitly open and close the connection for the client. This approach has led to some degree of security check concentration. When using this method, only the inspections are performed after the connection is obtained, but all other classes involving connection objects will benefit at the same time.
Method "Close" is used to close the connection. Typically, "Off" should only be disconnected and return the object (if there is a pool). You can also implement the "disposal" method from the desemerical structure of the object. The status of the connection is identified by the ConnectionState enumeration data type. When the client uses a connection, you should ensure that the internal status of the connection matches the contents of the Status property. For example, when you extract data, set the "Status" property of the connection to ConnectionState.Fetching. Back to top
ODBC connection
Now let's take a specific .NET managed provider to apply the above principles in practice. To this end, we take a new hosting provider as an example, although it only appears in an earlier test version. This is the .NET provider of the ODBC data source. You may have noticed that the DSN tag is not supported in the connection string .NET provider. Such names need to automatically select the MSDasql provider and search for the ODBC source. The following code shows how ODBC.NET declares its connection classes:
Public Sealed Class ODBCCONNECTION: Component,
Icloneable, IDBConnection
The ODBCConnection object utilizes typical resources of ODBC, such as environmental handles, and connecting handles. These objects are stored internally by private members. This class is used for "off" and "disposal". Typically, you can use any of these methods to close the connection, but you must use before the connection object is exceeded. Otherwise, the release of memory (that is, the ODBC handle) will leave the garbage collector, and you cannot control its execution time. In terms of connecting pools, the ODBCConnection class relies on the service of the ODBC driver manager.
To use the ODBC.NET provider (currently available Beta 1), you should include System.Data.odbc. This ensures that the provider uses drivers for Jet, SQL Server, and Oracle.
Back to top
Implement command
The command object creates a request for some operations and passes the request to the data source. If the result is returned, the command object is responsible for packaging and returning the result as a custom DataReader object, scalar value, and / or by output parameters. Depending on the characteristics of the data provider, you can arrange the results in other formats. For example, if the command text includes a For XML clause, the managed provider for SQL Server allows results in XML format.
Class must support at least the CommandText property and support the text command type. The analysis and execution of the command depends on the provider. This is a key element that enables the provider to accept any text or information as a command. Support command behavior is not forced, if needed, you can support more fully customized behaviors.
In the command, the connection can be associated with a transaction. If the connection is reset - and the user should be able to change the connection at any time - then the corresponding transaction object is disabled. If you support transaction processing, set an extra step to make sure that the transaction you are using is associated with the connection used by the command.
Command object uses two classes that represent parameters. A class is XXXParameterCollection, accessed by parameters attribute; another class is xxxparameter, which represents a single command parameter stored in a collection. Of course, XXX represents an alias specific to the provider. For ODBC.NET, these two classes are ODBCPARETERCOLLECTION and ODBCPARAMETER.
You can create a provider-specific command parameter using the "New" operator or the createParameter method of the command object. The newly created parameter fills and adds to the collection of the command through the parameters collection. The module used to command execution is responsible for collecting the data set through the parameter. Use the named parameter (just like the SQL Server provider) or when the placeholder (similar to the OLE DB provider) depends on you. There must be a valid and open connection to execute the command. Use any standard type command (for example, ExecuteNonQuery, ExecuteReader, and ExecuteScalar) to execute the command. In addition, it is also considered to provide implementation for "cancel" and preted methods.
Back to top
ODBC command
The ODBCCommand class does not support parameters that pass named through the SQL command and stored procedures. You must use the placeholder. At least in this earlier version, it does not support "cancellation" and does not support Prepare. As you expect, the ODBC .NET provider requires the number of command parameters in the Parameters collection to match the number of placeholders found in the command text. Otherwise, it will trigger an exception. The following code line shows how to add a new parameter to the odbc command, and assign the parameter.
cmd.parameters.add ("@ Custid", ODBCTYPE.INTEGER) .Value = 99
Note that the provider defines a set of types of yourself. Enumeration ODBCTYPE includes a low-level API of ODBC that you can identify all types (and only these types). The original ODBC type is very similar, for example, SQL_BINARY, SQL_BIGINT or SQL_CHAR and .NET type. In particular, the ODBC type SQL_CHAR is mapped to the .NET STRING type.
Back to top
Implement data reader
The data reader is a connected non-cache buffer that provides a program created in order to make the client read data only forward. The actual implementation of the reader depends on the writer of the provider. However, you should pay attention to several guidelines.
First, when the DataReader object is returned to the user, it should always be in the open state and located before the first record. In addition, users cannot create DataReader objects directly. The reader must be created and returned by the command object. To do this, you should mark the constructor as internal. Use a C # to use keyword INTERNAL
INTERNAL MYDATAREADER (Object Resultset)
{...}
Use Visual Basic® .NET to use keyword Friend
Friend Sub New (Byref ResultSet As Object)
Mybase.new
...
End Sub
The data reader must have at least two constructor, one receiving set of queries, and another connection object for executing commands. Connection is only required when the command must be executed in the form of Commandbehavior.CloseConnection. In this case, the connection must be automatically turned off when the DataReader object is turned off. Internally, the result set can adopt any form that meets your needs. For example, the result set can be implemented as an array or dictionary.
The data reader should properly manage the properties Recordsaffected. This attribute is applied only to batch statements including insert, update, or delete commands. It is usually not applied to the query command. When the reader is turned off, you may want to ban some operations and change the internal state of the reader to clean up internal resources, such as an array for storing data.
The read method of the data reader always proceeds to a new effective line (if there is any new effective line). More importantly, it should only make internal data pointers forward, but do not perform any reads. The actual read is done by other readers, for example, getString and getValues. Finally, NextResult is moved to the next result set. Basically, it is a public knowledge base that reads a new internal structure to getValues and other methods. Back to top
ODBC data reader
Like all reader classes, ODBCDataReader is sealed and cannot be inherited. The method of accessing the column value is automatically enforced the type of data returned to the data initially retrieved from the column. The type used by a given column for the first time a cell will be used for all other cells in the same column. That is, you cannot connect the data as a string and long integer read from the same column.
When the CommandType property of the command object is set to StoredProcedure, the CommandText property must be set using the standard ODBC escape sequence of the process. Unlike other providers, for the ODBC.NET provider, only the simple name of the process is not enough. The following mode illustrates a typical method for calling a stored procedure through an ODBC driver.
{Call StoredProc_name (?, ...,?)}
The string must be enclosed in {...} and placed the keyword call before the actual name and parameter list.
Back to top
Implement data adapter
The mature .NET data provider provides data adapter classes that inherit IDBDataAdapter and DBDataAdapter. Class DBDataAdapter implements data adapters for relational databases. However, in other cases, you need to implement an IDATAADAPTER interface and copy some disconnected data to the class of programmable buffers (such as data sets) in memory. In fact, in most cases, the Fill method that implements the IDATAADAPTER interface is sufficient for data that returns to disconnect through the dataset object.
The typical constructor of the DataAdapter object is:
XXXDataAdapter (SQLCommand SelectCommand)
XXXDataAdapter (String SelectCommandtext, String Selectconnectionstring)
XXXDataAdapter (String SelectCommandText, SqlConnection Selectconnection)
The classes inherited from DBDataAdapter must implement all members, and if you use the provider-specific function, you must also define additional members. Finally, the following method must be achieved:
Fill (DataSet DS)
Fillschema (DataSet DS, Schematype ST)
Update (DataSet DS)
GetFillParameters ()
The properties needed include:
• TableMappings (default empty collection) • missingschemaAction • MissingMappingAction (default is Passthrough)
You can provide any number of implementations of the "Fill" method as needed.
The table map controls the way the source table (ie the database table) is mapped to the data table object in the parent data set. Mapping considers table names and column names and properties. Architecture mapping considers how to add new data to existing datatons to process columns and tables. The default value for missing mapping properties requires the adapter to create a table in memory like the source table. The default value of the lack of architectural properties handles the problems that may be generated when the data table object is actually populated. If the target data is lacking any mapping elements (tables and columns), what measures are recommended for MissingsChemaAction. In a sense, the two MISSINGXXX attributes are an exception handler. Value add enforcement adapter adds any tables or columns that have been lacking. No key information is added unless otherwise assigned to the property, otherwise, no critical information is added. When the application calls the "Update" method, this class checks the RowState property of each line in the data set, and then performs the required INSERT, UPDATE, or DELETE statement. If this class does not provide UpdateCommand, InsertCommand, or DeleteCommand properties, you can implement idBDataAdapter, then you can try instantly generated commands or generate an exception. You can also provide custom command generator classes to help create commands.
The ODBC provider provides an ODBCCommandBuilder class as a method of automatically generating a single table command. The OLE DB provider and SQL Server provider provide similar classes. If you need to update a table with a cross-reference, you might want to use a stored procedure or an emotion SQL batch. In this case, simply override the InsertCommand, UpdateCommand, and DeleteCommand properties, so that they run the command object you specify.
Back to top
summary
The function provided by the .NET data provider can be divided into two major categories:
• Support disconnected dataset objects • Supported data access, including connection, transaction, command, parameters
The data provider in .NET supports data set objects through the IdataAdapter interface. You can also support parameterized queries by implementing the IDATAPARAMETER interface. If you are unable to turn on disconnected data, you can use the .NET data reader through the iDataReader interface.
Back to top
Dialogue: Named a number of result set
Visual Studio® .NET has a good feature that you can assign a consistent name for all tables that the data adapter is about to generate. After any .NET application configures the data adapter object, the dialog displays the standard name of the table to be created: Table, Table1, Table2, etc. For each name, you can specify a more imageable name in later one-time. Can we programmatically achieve this in a programming way in some way?
Visual Studio .NET is an excellent product, but it needs some techniques. The answer to the above problem is that we can indeed achieve this in a programming method, by way of manner, and Visual Studio use the same code in the background.
The DataAdapter object has a collection called TableMappings that is an object of a DataTableMapping type. In summary, what is the table map? The table map is a dynamic association between the source table and the adapter to create the corresponding data table objects. If no mapping is set, the adapter creates a data table object using the same structure as the source table, except the name. The name is the string specified by calling the "Fill" method or the word "table". An additional table generated from multiple result sets is named after the first table. Therefore, by default, their names are Table1, Table2, and the like, respectively. However, if the padding of the data adapter is shown in the code below, the additional table is named Employees1, Employees2, and more. MyDataAdapter.Fill (MyDataSet, "EMPLOYEES");
When configuring the data adapter, Visual Studio does create a DataTableMapping object for each association created by visual manner. The following code line displays how to allocate meaningful specific names for the first two tables of the data set fills as described above.
MyDataAdapter.tableMappings.add ("Employees", "firstTable");
MyDataAdapter.tableMappings.add ("Employees1", "SecondTable");
The third table (if any) can be accessed via Table2.
Although this is the best way to name data table objects generated from multiple result sets, you can also use the same valid code:
MyDataAdapter.Fill (MyDataSet, "EMPLOYEES");
MyDataSet.Tables ["Employees1"]. TABLENAME = "SecondTable";
You can also access the table by an index:
MyDataSet.Tables [1] .tablename = "secondtable";
Dino Esposito is working in Wintellect, which has assumed training and consulting in ADO.NET and ASP.NET. He is one of the founders of VB-2-The-Max and submits to MSDN Magazine's Cutting Edge column. If you want to contact Dino, you can send an email to dinoe@wintellect.com.
