I've been working with ADO.NET for sometime and so know a thing or two about it but what about MDAC, SNAC? How do they fit in and relate to each other (if at all)
Starting off with a few acronyms:
MDAC – Microsoft Data Access Components
SNAC – SQL Native Client
ADO – ActiveX Data Objects
ADO.NET – ADO for .NET Framework (but due to enhancements, can be considered an entirely different product rather than just an upgrade)
OLEDB – Object Linking and Embedding (OLE) DB (for database)
ODBC – Open DataBase Connectivity
The three are termed as "Data Access tehnologies" and thus includes a number of components (eg. providers) to access/connect to data (or services)
Here are some important points: [more]
1. ADO.NET
– recommended for managed code development (working with .NET Framework)
– comes with .NET Framework installation
– System.Data namespace in .NET
– "sub" namespace for specific data provider (eg. System.Data.SqlClient for MSSQL Server)
– Overview of ADO.NET
2. MDAC
– recommended if you are writing native code targetting Windows or if you need to write a classic ASP, Visual Basic 6.0x COM, or C++ application
– latest version is MDAC 2.8
– comes with Windows XP Service Pack 2. Otherwise can be downloaded from here
– to determine what version you have, use Component Checker, from the same link above
– allows access to connection pooling, memory management, client cursor support
– can be used to support access to SQL Server versions prior to 7.0
– three interfaces for MDAC: ODBC, OLEDB, ADO
– ODBC
– involves the concept of Data Source Name (DSN) which identifies the correct driver to use for the access
– to create a DSN, click Start > Control Panel > Administrative Tools > Data Sources (ODBC)
– sample connection string "DSN=myDSN;Uid=myUID;Pwd=myPwd"
– where myDSN is an existing (user or system) DSN name
– for fileDSN an example is "FILEDSN=c:dataConn.dsn;Uid=myUid;Pwd=myPwd"
– depending on the datasource you're accessing, you might not need the Uid (user id) or Pwd (password)
– typically used with C++ but if you have a compelling reason to use ODBC with VB.NET or C#.NET refer to these
– http://support.microsoft.com/kb/310985
– http://support.microsoft.com/kb/310988
– OLEDB
– considered the fastest and most consistent option when coding in C++
– doesn't require DSN
– for more considerations and information, refer to "OLE DB Programmer's Reference" at http://msdn2.microsoft.com/en-us/library/ms974412.aspx
– ADO
– used for scripting languages (eg. VBScript, ASP, JScript)
– a DSN or a DSN-less connection can be used
3. SNAC
– recommended if you need to access the latest features in SQL Server 2005 using ODBC or OLEDB
– introduced in SQL 2005
– used for COM based applications (otherwise use ADO.NET)
– support for SQL 2005 features like database mirroring, Multiple Active Result Sets (MARS), query notifications, user defined types (UDT) and XML data types
– stricter error handling than MDAC and reveals more information for errors
– doesn't allow access to connection pooling, memory management, client cursor support (unlike MDAC)
– doesn't implement ADO (although it does enable one to access functionality of ADO)
– will demonstrate in a while
– wraps OLEDB and ODBC into one DLL, thus enables it to perform quickly and can be secured easily
– for SNAC usage, check this link : http://msdn2.microsoft.com/en-us/library/ms130822.aspx
– or for updating applications to use SNAC from MDAC – http://msdn2.microsoft.com/en-us/library/ms130822.aspx
– MSDN on SQL Native Client
Both SQL Native Client and MDAC support read committed transaction isolation using row versioning, but only SQL Native Client supports snapshot transaction isolation.
Ohh if I may add, Wikipedia has some good overview on these too.
Reference: MCITP 70-442 Self-Paced Traning Kit