Microsoft's decision to have a flexible specification for the way that data providers access databases means that the implementations of ADO.NET data providers for different databases can vary widely. Even when using the Common Model for ADO.NET 2.0 to write provider independent code, programmers who switch to different data stores must expect to change their code, or even their coding logic.
Progress DataDirect Connect is known for our focus on interoperability. By using a common approach, we keep the interfaces and functionality the same for all of our ADO.NET data providers. We add interoperability to our data providers by:
The introduction of Microsoft's .NET framework was an important event for the information technology community. Unlike other data access APIs, in ADO.NET, Microsoft made a specific choice not to have a rigid specification for how data providers are built. They did this because they wanted to have a simple, flexible programming model for accessing databases. Microsoft was able to do this in ADO.NET while at the same time retaining all of the basic building blocks with which to build more complex database access code. Although this approach provides benefits such as increased performance and more control over database resources, the lack of rigidity has one major drawback. Unlike with ODBC, OLE DB, and JDBC™, it is possible in .NET to have a wide variation in the implementations of ADO.NET data providers for different databases.
This means that programmers cannot write a .NET application using one data source and expect to substitute another data source without changing their code to some degree. In fact, programmers using one ADO.NET data provider may find that its interfaces and structure are very different from data providers for other databases. In some cases, even their coding logic must change when moving between data providers.
At DataDirect Technologies, we always build our drivers and data providers using a common approach and architecture. This provides big benefits even with more rigid standards like ODBC and JDBC. With our DataDirect Connect® forADO.NET managed data providers (formerly DataDirect Connect® for .NET), we work hard to minimize the amount of code that must change when using our providers across different databases by keeping our interfaces and functionality the same across providers. Because no new learning curve is needed for additional databases, developer productivity is substantially improved.
Here are a few examples of identified interoperability issues that highlight the scope of this problem.
ADO.NET does not define a query language; therefore data providers are not required to mask the differences in supported SQL statements between databases. Progress DataDirect has added support for ODBC/JDBC SQL escapes for scalar functions, outer joins, and stored procedures in our providers. This means that developers can pass standardized SQL statements to all of our data providers.
For example, if you wanted to use the Oracle native syntax for the function Substring, your SQL statement might look like this:
SELECT substr('ABCDEFG',3,4) FROM mytableThe equivalent statement for Sybase would look like this:
SELECT substring('ABCDEFG',3,4) FROM mytableUsing ODBC/JDBC escapes, the SQL statement would be the same regardless of which backend database you access:
SELECT {substring('ABCDEFG',3,4) FROM mytable}If you wanted to execute an outer join using native Oracle syntax, your SQL statement might look like this:
SELECT ename, dname FROM emp a, dept b WHERE a.deptno = b.deptno(+)ORDER BY enameIf you wanted to execute the same outer join using native Sybase or SQL Server syntax, your SQL statement might look like this:
SELECT ename, dname FROM emp a, dept b WHERE a.dept =* b.deptno ORDER BY enameIf you used ODBC/JDBC outer join escapes, your SQL statement would be the same for all of the databases:
SELECT ename, dname FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno} ORDER BY enameDataDirect Technologies' ADO.NET data providers translate the SQL escapes for the specific backend database.
Most ADO.NET data providers use the database's native syntax for parameter markers in SQL statements. In Microsoft's SQL Server data provider, a simple SQL Statement would look like this:
SELECT * FROM table WHERE column=@parameternameWith Microsoft's Oracle data provider, that same SQL statement would look like this:
SELECT * FROM table WHERE column=:parameternameDevelopers must add database-specific code to their programs to generate the different syntax for these two data providers.
DataDirect Technologies has standardized parameter markers across our data providers. For all of our data providers (Oracle, SQL Server, DB2, and Sybase), this SQL statement would be:
SELECT * FROM table WHERE column=?Microsoft implements the interfaces for error handling differently among their data providers. Their SQL Server data provider has an ErrorCollection object and their Oracle data provider does not. This distinction makes sense based on the differences between the ways the two databases generate errors. However, application developers now have to use different logic for these two data providers: one requires iteration through the ErrorCollection object to retrieve individual errors and the other does not.
All of DataDirect Technologies' data providers implement the ErrorCollection object, so the code logic is the same across all of our data providers.
In addition to not having a completely standard error-handling methodology, ADO.NET makes no attempt to map the various database-specific errors to any kind of standard error numbers. The Error objects for all of DataDirect Technologies' ADO.NET data providers have a property called SQLState, which contains the ANSI standard SQLState mapping for any errors returned by the database. Coding to use this property can greatly simplify making your code portable across data providers and databases.
For example, if you add code to your application to handle an "invalid password" given by a user at connect time, you will get different error numbers and message text back from the various backend databases.
An invalid password error in Oracle is returned as Oracle error number 1017 with a message text of invalid username/password; logon denied.
An invalid password error in Microsoft SQL Server is retuned as SQLServer error number 18456 with a message text of Login failed for user '<username>'.
An invalid password error in Sybase is returned as Sybase error number 4067 with a message text of Login failed because an incorrect password was supplied.
Coding to handle either the native error number or the message text means that you must have completely different code when using the different databases. However, if you code to the standard ANSI SQLState, your code would expect an invalid password error to return a SQLState of 28000, regardless of the database to which you are connected, and can use the same code to handle the error. Only DataDirect Technologies' data providers offer this standard error-code mapping.
The Progress DataDirect Connect for Oracle data provider supports returning Ref Cursors as standard result sets. Other Oracle ADO.NET data providers may force you to bind the Ref Cursors as an output parameter. Ref Cursors are, conceptually, just a result set. Just as Microsoft SQL Server, DB2 UDB, and Sybase ASE return result sets from stored procedures, Ref Cursors give you the ability to return result sets from an Oracle stored procedure.
The Progress DataDirect data providers treat all of these the same way as (multiple) result sets. This means that you can code for result sets returning from stored procedures in Oracle the same way you code for them in Sybase, SQL Server, or DB2. You do not need to use different code logic just to handle Oracle.
Binding arrays of parameter values is a way for an application to specify multiple rows of values for a single SQL statement on a single execution of a command. Specifying parameters in such a manner can allow the values to be sent to the database server in a single round trip (depending on the native capabilities of the backend database) and can result in significantly improved performance. This type of operation is typically used on INSERT statements to speed up the time it can take to fill a table.
ADO.NET does not have a standardized way to specify arrays of parameters to data providers. In fact, many data providers do not have this capability at all! Progress DataDirect supports a standard mechanism to specify arrays of parameters to all of our data providers, as shown in the following C# code example:
// Create an array of values that need to be inserted int[] myArrayDeptNo = new int[3]{10, 20, 30}; // Set the command text on the Command object cmd.CommandText = "insert into dept(deptno) values (?)"; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the array operations OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32); prm.Direction = ParameterDirection.Input; prm.Value = myArrayDeptNo; // Add the parameter to the parameter collection cmd.Parameters.Add(prm); // Execute the command cmd.ExecuteNonQuery();Progress DataDirect recognizes the importance of interoperability across ADO.NET data providers and the developer productivity gains it brings. One of our development goals is to support standard approaches and to standardize functions and interfaces across data providers as much as possible. We already do this in ADO.NET with stored procedures. In addition to supporting the ODBC/JDBC SQL escape for executing stored procedures, our data providers support the standard ADO.NET mechanism for calling stored procedures. Regardless of which data provider you use, you can set the CommandType of the Command object to StoredProcedure and then set the CommandText property to the name of your stored procedure. You can then bind the parameters to the stored procedure without having to give them placeholders in the SQL statement itself.
With the release of the .NET Framework 2.0 in late 2005, Microsoft introduced a Common Programming Model to ADO.NET. The Common Model contains interfaces and objects that allow a programmer to use the same provider independent code to access the Connection, Command, Transaction, and other objects of any data providers compliant with the .NET Framework 2.0. DataDirect Connect for ADO.NET 3.0 data providers fully support the NET Framework 2.0 and the Common Programming Model. These new features will be a great boost to writing interoperable ADO.NET code. When combined with the unique interoperability features available from DataDirect Connect data providers today, application writers finally have all the tools they need to write code that works well across multiple databases.
Developers working with multiple databases in .NET applications need to be concerned about the lack of rigidity in the ADO.NET data access standard. This document has shown just a few examples of the interoperability issues programmers are most likely to face when using ADO.NET data providers from different vendors. Other issues do exist. If a careful choice of data providers is not made initially, a substantial amount of recoding may need to be done to make .NET applications work across databases.
At DataDirect Technologies, our sole focus is on building outstanding data connectivity components such as ADO.NET data providers. Our ADO.NET data providers are built with a common architecture and a focus on interoperability. We've solved many of the interoperability issues that currently exist between data providers, such as bulk inserts and bulk updates, error-handling objects and error-code mapping, SQL escapes and parameter markers in SQL statements. We will continue to focus on interoperability as we bring out new releases of existing data providers. Our programming direction ensures that our data providers will deliver the highest degree of interoperability on the market.
With a focus on interoperability, a complete set of data providers for all the major databases, and outstanding performance from an architecture that does not require database client libraries and works entirely within the .NET Common Language Runtime, DataDirect Technologies is clearly the best choice for your .NET applications.
DataDirect Technologies offers the following ADO.NET data providers that support the .NET Framework Version 2.0:
Existing code written for earlier versions of the .NET Framework and earlier versions of DataDirect Connect for .NET is compatible with the 3.0 version of the data providers. Note that the applications must be re-compiled using the .NET Framework 2.0.
However, if your applications require Windows 98 and Windows Me and/or the .NET Framework 1.x, you can use the DataDirect Connect for .NET 2.2 data providers, which DataDirect will continue to make available.