Profiling. Net hosting providers

Profile. Net hosting provider released on: 4/1/2004 | updated on: 4/1/2004

Dino esposito

Wintellect

October 9, 2001

Microsoft. NET hosting providers have many advantages over mature ole db providers. First, it implements a simplified data access structure, which can improve performance without affecting functional capabilities. In addition, the. NET hosting provider directly provides users with behavior specific to the provider through methods and attributes. It also uses fewer interfaces than the ole db Provider. Last but not least, the. NET hosting provider operates within the boundaries of the Common Language Runtime Library (CLR) without com interaction. For SQL Server 7.0 and SQL Server 2000, the hosting provider directly connects to the line level, achieving significant performance advantages.

The. NET data provider provides the following functions:

•	PassIdataadapterInterface Method implementation, supportDatasetClass
•	Supports connection data access, including classes that represent connections, commands, and parameters.

The simplest function of the data provider is to interact with the caller only through datasets when reading and writing data. In another case, you can control connections, transaction processing, and execute direct commands without considering the SQL language. Shows the class hierarchies of two standard managed providers (ole db providers and SQL server providers) in. net.

Figure 1. host the provider to connect, execute commands, and obtain data in a data source-specific manner.

The objects that encapsulate connections, commands, and readers are specific to the provider and may generate a set of slightly different attributes and methods. Any internal implementation is strictly capable of database recognition. The only class beyond the scope of this architecture is the dataset. This class is shared by all providers and serves as a general container for disconnected data. A dataset class is namedSystem. Data. A class specific to a data provider belongs to a specific namespace. For example,System. Data. sqlclientAndSystem. Data. oledbIt belongs to a specified namespace. Although the displayed architecture is not too simple, it is quite basic. This is a simplified architecture because it does not include all classes and interfaces involved. Closer to the real situation.

Figure 2. classes involved in hosting providers

The following table lists the interfaces that constitute the. NET provider.

Idbconnection	Indicates a unique session established with the data source.
Idbtransaction	Indicates a local Non-distributed transaction processing.
Idbcommand	Indicates a command executed when connecting to the data source.
Idataparameter	Allow parameter implementation as a command
Idatareader	Read the read-only data streams created after the command is executed.
Idataadapter	Fill the dataset and resolve the changes in the dataset back to the data source
Idbdataadapter	Provides methods to perform typical operations (insert, update, select, and delete) on a relational database.

Among all interfaces, onlyIdataadapterIt is mandatory and must exist in each managed provider. If you do not intend to implement one of the interfaces or one of the methods of a given interface, you must expose the interface in any way, but this will causeNotsupportedexceptionException. Whenever possible, do not provide non-operational implementation of methods and interfaces, because this method may cause data corruption, especially for commit/rollback of transaction processing. For example, the provider does not need to support nested transactions, even ifIdbtransactionThe interface design takes this situation into consideration.

Before explaining the roles that each class plays in the overall running of the. NET provider, let's take a look at the naming conventions that are recommended for hosting providers. This knowledge is useful if you plan to write your own providers. The first criterion is to consider namespaces. Make sure that you assign a unique namespace to your hosting provider. Next, use the alias that identifies the provider in any internal code and client code as the class prefix. For example, class names such as odbcconnection, odbccommand, and odbcdatareader are used. In the case mentioned in this Article, the alias is ODBC. In addition, try to use different files to compile different functions.

Achieve connection

Provide program connection class fromIdbconnectionAnd must be public.Connectionstring,"Status","Database"AndConnectiontimeoutAttribute. Mandatory methods include"Open","Close",Begintransaction,ChangedatabaseAndCreatecommand. You do not have to implement transaction processing. The following code snippet is a sample code for implementing the connection.

namespace DotNetMyDataProvider {  public class MyConnection : IDbConnection  {    private ConnectionState m_state;    private String m_sConnString;    public MyConnection () {m_state = ConnectionState.Closed;m_sConnString = "";    }    public MyConnection (String connString) {m_state = ConnectionState.Closed;m_sConnString = connString;    }    public IDbTransaction BeginTransaction() {      throw new NotSupportedException();    }    public IDbTransaction BeginTransaction(IsolationLevel level) {      throw new NotSupportedException();    } }}

You should provide at least two constructors, one of which is the default constructor without any parameters. Another suggested constructor only accepts connection strings. PassConnectionstringWhen the property returns the connection string, make sure that the returned string is always the string set by the user. The only exception may be caused by any sensitive security information you may wish to delete.

The items you identify and support in the connection string depend on you, but the standard name should be used whenever it makes sense."Open"The method opens the physical channel that communicates with the data source. This operation should not be called"Open"Method. If you enable the connection to become a memory-consuming operation, you can consider using a connection pool. Finally, if the provider is expected to provide automatic registration in Distributed Transaction processing, the registration should be executed"Open".

One important difference between ADO. net connections and other connections (such as ADO connections) is that you need to ensure that the connection is created and opened before you can execute any command. The client must enable and disable the connection explicitly. There is no way to enable or disable the connection for the client implicitly. This approach leads to a certain degree of centralization of security checks. When this method is used, the check is performed only after the connection is obtained, but all other classes involved in the connection object in the provider will benefit at the same time.

Method"Close"Used to close the connection. Generally,"Close"Only disconnect and return the object to the pool (if there is a pool ). You can also"Disposal"Method to customize the structure of an object. The connection status passesConnectionstateThe enumerated data type. When the client uses a connection, make sure that the internal status of the connection is"Status"The attribute content matches. For example, when you extract data"Status"Set the property to connectionstate. fetching.

Back to Top

ODBC connection

Now let's look at how a specific. Net hosting provider applies the above principles in practice. For this reason, we take a newly hosted provider as an example, although it only appears in early beta versions. This is the. net provider of the ODBC data source. You may have noticed that the. net provider of ole db does not support the DSN tag in the connection string. For such a name, you need to automatically select the msdasql provider and search for the ODBC source. The following code shows how ODBC. Net declares its connection class:

public sealed class OdbcConnection : Component, ICloneable, IdbConnection

OdbcconnectionThe object uses typical ODBC resources, such as the Environment handle and connection handle. These objects are stored internally by private members of the class. This class is also used"Close"And"Disposal". Generally, you can use any of the methods to close the connection, but it must be used before the connection object is out of the valid range. Otherwise, the release of memory (that is, the ODBC handle) will be left to the garbage collector, and you cannot control its execution time. In connection pool, the odbcconnection class relies on the ODBC driver Manager Service.

To use the ODBC. net provider (Beta 1 is currently available), you shouldSystem. Data. ODBCInclude. This ensures that the provider uses drivers for jet, SQL Server, and Oracle.

Back to Top

Implementation command

The command object creates a request for some operations and passes the request to the data source. If a result is returned, the command object is responsible for customizing the result.DatareaderObject, scalar value, and/or packaged and returned through output parameters. Based on the features of the data provider, you can arrange other formats for the results. For example, if the command text includes a for XML clause, the hosting provider for SQL Server allows results to be obtained in XML format.

The class must support at leastCommandtextAttribute, and supports at least the text command type. Command analysis and execution depend on the provider. This is a key element that makes it possible for the provider to accept any text or information as commands. Command behaviors are not mandatory. If necessary, you can support more fully-customized behaviors.

In the command, the connection can be associated with a transaction. If you reset the connection and the user should be able to change the connection at any time, disable the corresponding transaction processing object first. If transaction processing is supported, set"Transaction"Additional steps should be taken to ensure that the transaction processing you are using is associated with the connection used by the command.

The command object uses two classes that represent parameters. A class is xxxparametercollection, throughParametersThe other class is xxxparameter, which represents a single command parameter stored in the collection. Of course,XxxRepresents the alias specific to the provider. For ODBC. net, these two classes are odbcparametercollection and odbcparameter.

You can use"New"Operator orCreateparameterMethod to create command parameters specific to the provider. The newly created parameters are filled and added to the command set using the parameters set method. Then, the module used for command execution collects datasets through parameters. Do I use named parameters (just like the SQL Server Provider? Placeholders (similar to the ole db Provider) depend on you.

You must have a valid and enabled connection to run the command. Execute commands of any standard type (for example, executenonquery, executereader, and executescalar. In addition, consider"Cancel"AndPrepareMethod.

Back to Top

ODBC command

The odbccommand class does not support passing named parameters through SQL commands and stored procedures. You must use? Placeholder. At least in earlier versions, it neither supports"Cancel"And does not supportPrepare. As you expected, ODBC. net providers requireParametersThe number of command parameters in the set must match the number of placeholders found in the Command text. Otherwise, an exception is thrown. The following code line shows how to add a new parameter to the ODBC command and assign a value to the parameter.

cmd.Parameters.Add("@CustID", OdbcType.Integer).Value = 99

Note that the provider defines its own set of types. EnumerationOdbctypeAll types that can be recognized by low-level APIs including ODBC (and only these types are included ). The original ODBC types are very similar, such as SQL _binary, SQL _bigint, SQL _char, And. net. In particular, ODBC SQL _char maps to. Net string.

Back to Top

Implement data reader

A data reader is a connection-free buffer created by the provider to allow the client to read data only forward. The actual implementation of the reader depends on the writer of the provider. However, pay attention to the following rules.

First,DatareaderWhen an object is returned to a user, it should always be in the open state and be located before the first record. In addition, you cannot directly createDatareaderObject. The reader must be created and returned by the command object. To this end, you should mark the constructor as internal. Keywords should be used when C # is usedInternal

internal MyDataReader(object resultset){...}

Keyword used When Visual Basic. NET is usedFriend

Friend Sub New(ByRef resultset As object)      MyBase.New      ...End Sub

The data reader must have at least two constructors, one for query result sets and the other for connection objects used to execute commands. Only when the command mustCommandbehavior. closeconnectionTo connect. In this case, whenDatareaderWhen the object is closed, the connection must be closed automatically. Internally, the result set can take any form that meets your needs. For example, you can implement a result set as an array or dictionary.

Data readers should correctly manage attributesRecordsaffected. This attribute is only applicable to batch statements that include insert, update, or delete commands. It is generally not used for query commands. When the reader is disabled, you may want to disable some operations and change the internal status of the reader to clear internal resources, such as arrays used to store data.

Data reader'sReadThe method always advances to a new valid row (if any new valid row exists ). More importantly, it should just make the internal Data Pointer Forward without any reading. The actual reading is done by other reader-specific methods, for example,GetstringAndGetvalues. Finally,NextresultMove to the next result set. Basically, it copies a new internal structureGetvaluesAnd other methods to read from the public knowledge base.

Back to Top

ODBC data reader

Like all reader classes, odbcdatareader is sealed and cannot be inherited. The method of accessing the column value class automatically forces the data types returned by them to use the data types originally retrieved from the column. The type used to read a cell from a given column for the first time is used for all other cells in the same column. That is, you cannot successively read data from the same column as a string or a long integer.

WhenCommandtypeSet propertyStoredprocedure,CommandtextThe attribute must be set using the standard ODBC escape sequence of the process. Unlike other providers, it is not enough for ODBC. net providers to use simple names of processes. The following pattern illustrates a typical method to call a stored procedure through an ODBC driver.

{ call storedproc_name(?, ..., ?) }

The string must be enclosed by {...}, and the keyword call is placed before the actual name and parameter list.

Back to Top

Implement data adapter

Mature. NET data providers provide inheritedIdbdataadapterAndDbdataadapter. Dbdataadapter class implements data adapters for relational databases. However, in other cases, what you need isIdataadapterInterface and copy some disconnected data to the class of the programmable buffer (such as dataset) in the memory. In fact, in most casesIdataadapterInterfaceFillThe method is sufficient for the data that is disconnected from the returned data through the DataSet object.

DataadapterThe typical constructor of an object is:

XxxDataAdapter(SqlCommand selectCommand) XxxDataAdapter(String selectCommandText, String selectConnectionString) XxxDataAdapter(String selectCommandText, SqlConnection selectConnection)

The class inherited from dbdataadapter must implement all Members. If you use a feature specific to the provider, you must define additional members. Finally, the following methods must be implemented:

Fill(DataSet ds)FillSchema(DataSet ds, SchemaType st)Update(DataSet ds)GetFillParameters()

The required attributes include:

•	Tablemappings(Empty set by default)
•	Missingschemaaction(Default value:Add)
•	Missingmappingaction(Default value:Passthrough)

You can provide"Fill"Implementation of any number of methods.

Table ing controls how source tables (database tables) are mapped to data table objects in the parent dataset. Table names, column names, and attributes are considered for ing. The architecture ing considers how to process columns and tables when adding new data to an existing data set. The default value of the missing ing property requires the adapter to create a table in the same memory as the source table. The default value of the missing schema attribute may cause problems when the data table objects are actually filled. If the target dataset lacks any mapped elements (tables and columns), what measures are recommended for the value of missingschemaaction. In a sense, the twoMissingxxxAttribute is an exception handling program. ValueAddForce the adapter to add any table or column that has been proven to be missing. Unless another (addwithkey) value is assigned to the property, no key information is added.

When the application calls"Update"This class checksRowstateAnd then execute the required insert, update, or delete statements. If this class does not provideUpdatecommand,InsertcommandOrDeletecommandAttribute, butIdbdataadapterThen, you can try to generate a command in real time or generate an exception. You can also provide a custom command generator class to help generate commands.

ODBC providerOdbccommandbuilderClass is used to automatically generate a single table command. The ole db provider and SQL Server Provider provide similar classes. If you want to update a cross-referenced table, you may need to use stored procedures or ad hoc SQL batch processing. In this case, you only need to overwriteInsertcommand,UpdatecommandAndDeletecommandTo run the command objects you specified.

Back to Top

Summary

The. NET data provider provides the following functions:

•	Dataset objects that support disconnection
•	Supports connection data access, including connection, transaction processing, commands, and parameters.

The data provider in. Net passes throughIdataadapterInterface to support dataset objects. You can alsoIdataparameterThe interface supports parameterized queries. If you cannot afford the disconnected data, you canIdatareaderThe interface uses the. NET data reader.

Back to Top

Dialog Box: Name multiple result sets

Visual Studio. NET has a good function, that is, you can assign a consistent name to all the tables to be generated by the data adapter. After the data adapter object is configured in any. NET application, this dialog box displays the standard names of the tables to be created: Table, Table1, Table2, and so on. For each name, you can specify it as a more image name at a time later. Can we do this programmatically in some way?

Visual Studio. NET is an outstanding product, but its use requires some skills. The answer to the above question is-we can use some programming method to achieve this purpose. By the way, Visual Studio uses the same code in the background.

DataadapterThe object has a nameTablemappingsIs a datatablemapping object. In summary, what is table ing? Table ing is a dynamic association set between the source table and the corresponding data table object to be created by the adapter. If no ing is set, the adapter uses the same structure as the source table to create a data table object, except for the name. The name is called"Fill"Method or word"Table"The specified string. An additional table generated from multiple result sets is named after the first table. Therefore, by default, their names are Table1, Table2, and so on. However, if the data adapter is filled with the following code, the additional tables are named employees1, employees2, and so on.

myDataAdapter.Fill(myDataSet, "Employees");

When you configure the data adapter, Visual Studio createsDatatablemappingObject. The following code lines show how to programmatically assign meaningful names to the first two tables of the dataset filled in as described above.

myDataAdapter.TableMappings.Add("Employees", "FirstTable");myDataAdapter.TableMappings.Add("Employees1", "SecondTable");

The third table (if any) can be accessed through table 2.

Although this is the best way to name data table objects generated from multiple result sets, you can also use the following code:

myDataAdapter.Fill(myDataSet, "Employees");myDataSet.Tables["Employees1"].TableName = "SecondTable";

You can also access the table through indexes:

myDataSet.Tables[1].TableName = "SecondTable";

Dino espositoWorking at wintellect, he undertook training and consulting for ADO. NET and ASP. NET. He was one of the founders of VB-2-The-Max and contributed to the cutting edge column of msdn magazine. If you want to contact Dino, you can send an email to the dinoe@wintellect.com.