Create More Efficient Database Code

Using the native data providers in .NET results in more efficient code than using OLE DB. The native providers improve raw performance and enable comfortable data structures to hold data. I'll show you how the .NET data providers are architecturally superior to OLE DB providers by taking the particular case of SQL Server 2000. I'll demonstrate what goes on when you call the classes of both providers, and how SQL Server gets into the game in both cases. I'll show you how this all works by using the SQL Profiler to snoop under the hood of the ADO.NET-to-SQL Server interaction.



First, some background: A .NET data provider (also known as a managed provider) is a .NET class that wraps a data source's functionalities and makes them available through a standard programming interface. The .NET Framework comes with two default data providers: one for SQL Server 7.0 and higher, and one for OLE DB providers. A third provider for Open Database Connectivity (ODBC) data sources is available as a separate download (see Resources). Keep in mind that today you can't rely on .NET providers for all data sources, so OLE DB remains a valuable resource you use through a generic .NET provider for OLE DB.



Conceptually speaking, a .NET data provider isn't much different from any OLE DB providers you happened to use with ActiveX Data Objects (ADO). ADO.NET splits ADO's Recordset object into two distinct and simpler objects—the DataSet and the DataReader—that a .NET data provider fills with fetched data, packages, and returns to caller applications. As a result, you have two sets of classes to execute commands on SQL Server 7.0 and 2000 tables. You can go through the .NET Data Provider or through your old acquaintance, the SQLOLEDB OLE DB provider. You need to decide which one is the right choice for you.