Register for Visual Studio 2010 Beta Exams (.NET 4.0)

Time for (free) Beta Exams again

Information can be found from here

http://blogs.technet.com/betaexams/archive/2010/03/17/register-for-tools-utilities-2010-beta-exams.aspx

Register via http://register.prometric.com

For easier reference, exams available are listed below (along with PromoCode)

Exam 71-511, TS: Windows Applications Development with Microsoft .NET Framework 4 – 511BC

Exam 71-515, TS: Web Applications Development with Microsoft .NET Framework 4 – 515AA

Exam 70-513: TS: Windows Communication Foundation Development with Microsoft .NET Framework 4 – 513CD

Exam 70-516: TS: Accessing Data with Microsoft .NET Framework 4 – 516B1

Exam 70-518: Pro: Designing and Developing Windows Applications Using Microsoft .NET Framework 4 – 518PE

Exam 70-519: Pro: Designing and Developing Web Applications Using Microsoft .NET Framework 4 – 519ZS

And as always, word of advice, read and master the items in prep guide to increase your chances of passing the exams.

Good luck!

MCTS ADO.NET 3.5 Applications results out and passed

A good way to start a day. Just got an email regarding another latest addition to my cert list. Microsoft Certified Technology Specialist : .NET Framework 3.5 ADO.NET Applications [more]

The exam I took for this was 71-561 and when going live would become 70-561.

I was excited to come up with a new logo but when I added all MCTS certifications, I got this error: Maximum Lines Exceeded – Please Remove a Certification. 

So had to settle for an all MCTS 3.5 logo 🙂

I actually did not expect much from this exam due to my unfamiliarity with Synchronization Services. But I'm guessing the rest of the beta takers had the same difficulties 🙂

 

The training kit for 70-442 TS: Designing and Optimizing and Data Access for SQL 2005 also helped for this exam and it's a good read if not a must read for developers involved in Data Access with ADO.NET

Link: SQL Server 2008: new data types and .Net 2 with and without SP1

Here's an interesting I came across in the recent Simple Talk Newsletter (by Red Gate Software).

It talks about issues with new SQL 2008 data types (date/time related) and it's effect on .NET 2.0 datetime data types when SP1 is installed or not. Something to be aware of and one of those times that you're glad you have a web rather than a desktop application. (you only have to install SP1 on the server(s))

SQL Server 2008 has introduced a few new data types, among others the
new date types, like date, time, datatime2 and datetimespan. Because
.Net 2 was released before SQL Server 2008 has introduced these data
types, there are no classes that map to these new types in .Net 2.
But this has changed with .Net 2 SP1, which introduces the DateTimeOffset structure.

Read full article from the following link :SQL Server 2008: new data types and .Net 2 with and without SP1

ADO.NET Review: Multiple Select Queries in one SqlCommand

If you perform multiple select queries inside your stored procedures I believe you actually return all of the result sets to the client. (except when you assign a value to a parameter like SELECT @myVar = [ColumnA] FROM Table1). This is something to be careful of since you might unnecessarily return results sets that you actually don't need to return.

But this behavior also has an advantage. Obviously, if you need to retrieve two result sets, there is no need to create two or more SqlCommand to retrieve them. I believe this is handled under the hood if you use SqlDataAdapter.Fill where the dataset being filled will result the sets as part of its tables collection. However in this example we will illustrate how it can be done using SqlDataReader using non other than Reader.NextResult() method.

This is something I don't see often (might not be the case for you) so without much further ado, here's a sample code that might explain better than words. [more]

Once again this uses AdventureWorks database. 

    1 using System;

    2 using System.Data.SqlClient;

    3 

    4 namespace Console2

    5 {

    6     internal class Program

    7     {

    8         private static void Main(string[] args)

    9         {

   10             string connectionString =

   11                 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

   12 

   13             using (SqlConnection conn = new SqlConnection(connectionString))

   14             {

   15                 conn.Open();

   16 

   17                 // our command text containing two select queries

   18                 string commandText =

   19                     "SELECT ProductCategoryID, Name FROM [Production].[ProductCategory]; " +

   20                     "SELECT ProductSubcategoryID, Name FROM [Production].[ProductSubcategory]";

   21 

   22                 SqlCommand cmd = new SqlCommand(commandText, conn);

   23                 using (SqlDataReader rdr = cmd.ExecuteReader())

   24                 {

   25                     // look into categories result set

   26                     Console.WriteLine("Categories");

   27                     while (rdr.Read())

   28                     {

   29                         int categoryID = rdr.GetInt32(0);

   30                         string categoryName = rdr.GetString(1);

   31                         // NOTE: be careful when using indexes (as in above)

   32                         // make sure they match the order of columns in your select query

   33 

   34                         Console.WriteLine(categoryID + "t" + categoryName);

   35                     }

   36 

   37                     // look into categories result set

   38                     rdr.NextResult();

   39 

   40                     // for separation in display only

   41                     Console.WriteLine(String.Empty.PadLeft(20, '-'));

   42 

   43                     Console.WriteLine("SubCategories");

   44                     while (rdr.Read())

   45                     {

   46                         int subCategoryID = rdr.GetInt32(0);

   47                         string subCategoryName = rdr.GetString(1);

   48                         Console.WriteLine(subCategoryID + "t" + subCategoryName);

   49                     }

   50                 }

   51             }

   52 

   53             Console.ReadKey();

   54         }

   55     }

   56 }

 

And running the code should result to something like in the image below. 

So there you have it, a simple example.

The same result can be expected using stored procedures. Say you have the following stored procedure:

CREATE PROC MultiQuery
AS

SELECT
  ProductCategoryID,
  Name
  FROM Production.ProductCategory

SELECT
  ProductSubcategoryID, 
  Name
  FROM Production.ProductSubcategory


And this will be your modified code

    1 using System;

    2 using System.Data;

    3 using System.Data.SqlClient;

    4 

    5 namespace Console2

    6 {

    7     internal class Program

    8     {

    9         private static void Main(string[] args)

   10         {

   11             string connectionString =

   12                 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

   13 

   14             using (SqlConnection conn = new SqlConnection(connectionString))

   15             {

   16                 conn.Open();

   17 

   18                 // stored procedure name

   19                 string commandText = "MultiQuery";

   20 

   21                 SqlCommand cmd = new SqlCommand(commandText, conn);

   22                 cmd.CommandType = CommandType.StoredProcedure;

   23                 using (SqlDataReader rdr = cmd.ExecuteReader())

   24                 {

   25                     // look into categories result set

   26                     Console.WriteLine("Categories");

   27                     while (rdr.Read())

   28                     {

   29                         int categoryID = rdr.GetInt32(0);

   30                         string categoryName = rdr.GetString(1);

   31                         // NOTE: be careful when using indexes (as in above)

   32                         // make sure they match the order of columns in your select query

   33 

   34                         Console.WriteLine(categoryID + "t" + categoryName);

   35                     }

   36 

   37                     // look into categories result set

   38                     rdr.NextResult();

   39 

   40                     // for separation in display only

   41                     Console.WriteLine(String.Empty.PadLeft(20, '-'));

   42 

   43                     Console.WriteLine("SubCategories");

   44                     while (rdr.Read())

   45                     {

   46                         int subCategoryID = rdr.GetInt32(0);

   47                         string subCategoryName = rdr.GetString(1);

   48                         Console.WriteLine(subCategoryID + "t" + subCategoryName);

   49                     }

   50                 }

   51             }

   52 

   53             Console.ReadKey();

   54         }

   55     }

   56 }

  

I just realized that selecting the code with the line numbers is a pain so including sample project (Console App) in the link below (commandType.Text sample only)

Download Sample – MultiQueryCommand.zip (2.68 kb) 

ADO.NET Review: Multiple Active Result Set (MARS)

Quoting from the SQL Programmability and API Development Team Blog: Multiple Active Result Set or MARS is defined as

a new programming interface introduced in SQL Server 2005. It
enables multiple result sets to exist at the same time for a given
connection. To say it in a simple way, you can make a connection to
server and then submit multiple requests to serve

To illustrate, [more]say we have this code which simply retrieves ProductCategory from the AdventureWorks database, loop through the resulting data reader and for each loop retrieve subcategories for the current category

* (if you don't have AdventureWorks DB you can get it from here, install and attach the MDF file).

    1 using System;

    2 using System.Data;

    3 using System.Data.SqlClient;

    4 

    5 namespace ConsoleSample

    6 {

    7     class Program

    8     {

    9         static void Main(string[] args)

   10         {

   11             // connection string (without MARS enabled)

   12             string connectionString =

   13                 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

   14 

   15             using (SqlConnection conn = new SqlConnection(connectionString))

   16             {

   17                 conn.Open();

   18 

   19                 string selectCategories =

   20                     "SELECT ProductCategoryID, Name FROM [Production].[ProductCategory]";

   21 

   22                 string selectSubCategories =

   23                     "SELECT ProductSubcategoryID, Name FROM [Production].[ProductSubcategory] WHERE ProductCategoryID = @ProductCategoryID";

   24 

   25                 SqlCommand cmdCategory = new SqlCommand(selectCategories, conn);

   26                 SqlCommand cmdSubCategory = new SqlCommand(selectSubCategories, conn);

   27 

   28                 cmdSubCategory.Parameters.Add("@ProductCategoryID", SqlDbType.Int);

   29 

   30                 // retrieve categories

   31                 using (SqlDataReader rdrCategory = cmdCategory.ExecuteReader())

   32                 {

   33                     while (rdrCategory.Read())

   34                     {

   35                         int categoryID = rdrCategory.GetInt32(0);

   36                         string categoryName = rdrCategory.GetString(1);

   37                         Console.WriteLine(categoryName); // display category name

   38 

   39                         // now we retrieve subcategories for the current category

   40                         cmdSubCategory.Parameters["@ProductCategoryID"].Value = categoryID;

   41                         using (SqlDataReader rdrSubCategory = cmdSubCategory.ExecuteReader())

   42                         {

   43                             while (rdrSubCategory.Read())

   44                             {

   45                                 int subCategoryID = rdrSubCategory.GetInt32(0);

   46                                 string subCategoryName = rdrSubCategory.GetString(1);

   47                                 Console.WriteLine("SubCategory – " + subCategoryID + " : " + subCategoryName);

   48                             }

   49                         }

   50 

   51                         // just a separator between displayed categories

   52                         Console.WriteLine("-".PadLeft(20, '-'));

   53                     }

   54                 }

   55             }

   56 

   57             Console.WriteLine(" ** Query Succesful ** ");

   58             Console.ReadKey();

   59         }

   60     }

   61 }

If you run the sample you would get an error (see image below) at line 41 

This is because without MARS enabled (and in earlier versions of SQL Server) the programming model is restricted such that at any point in time there would be at most one pending request on a given session (or effectively the opened connection).

Without much further explanation to enable MARS all you have to do is add a keyvalue pair in your connection string as in our example should look something like

@"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=true;";

Notice the MultipleActiveResultSets=true; that was appended at the end

And with the modified connection string, if we run the code it should result to 

 

Cool isn't it. Common uses include the master detail relationships similar to above (e.g. Products under a particular Category)

However, this is not to say that MARS is suitable for all situations so I would recommend reading more from the following:

http://blogs.ittoolbox.com/c/coding/archives/mars-multiple-active-result-set-16003

more sql performance tips

Hope it helps and coming up next, two/more queries (and thus two/more results sets) in one SqlCommand (not related to MARS)

Issue with System.Transactions, SqlConnection and Timeout

Just recently, a post was made in Microsoft Forums regarding a bug/behavior of Committable Transactions and SqlConnection timeout. The same issue is evident for TransactionScope which was posted in 2006. Good thing there is a fix[more]

I have used TransactionScope a number of times including in one of my previous posts on Unit Testing (integration testing if you're particular about it) DataAccess so will focus on it for now (but as said, same issue with Committable Transaction class).

When you use TransactionScope and set the timeout to a certain value (not sure what is the default is not specified) and the timeout elapsed before the TX is completed, what happens is that actions made before the timeout is rolled back but after that, the connection unbinds itself from the transaction and if any action, places itself in autocommit mode (just like a regular connection) and if actions are made after the timeout (BUT still inside the TransactionScope, since these were performed in autocommit mode, they will not be rolled back).

Trying out the code below (or the downloadable sample project at the bottom) you will notice that in the "BASIC" example (which demonstrates the issue), you try to insert 5 rows, TransactionScope timeout more or less happens after the 2nd row, then after the TransactionScope, you check the database and there are 3 rows committed (instead of NONE). 

The good news is that MSFT found this issue too and had a fix for it a little after the release (probably after the System.Transaction or release of .NET 2.0) which involves a new keyword in the connection string : transaction binding defined in MSDN as :

Controls connection association with an enlisted System.Transactions transaction.

Possible values are:

Transaction Binding=Implicit Unbind;

Transaction Binding=Explicit Unbind;

Implicit
Unbind causes the connection to detach from the transaction when it
ends. After detaching, additional requests on the connection are
performed in autocommit mode. The System.Transactions.Transaction.Current

property is not checked when executing requests while the transaction
is active. After the transaction has ended, additional requests are
performed in autocommit mode.

Explicit Unbind causes the connection to remain attached to the transaction until the connection is closed or an explicit SqlConnection.TransactionEnlist(null) is called. An InvalidOperationException is thrown if Transaction.Current is not the enlisted transaction or if the enlisted transaction is not active.

So as you can see, the Implicit Unbind was the default behavior (which exibits the issue when the TX times out) and using explicit unbind will have the connection remain "bound" to the transaction instead of detaching itself and live it's life on it's own (and in on autocommit mode). Effectively, those actions performed after the Transaction Scope times out will also be uncommitted and we have a consistent behavior.

Here's my code while trying to verify the issue on my own (code taken from the forums with a few additions to help illustrate better). NOTE: Don't forget to modify the connection accordingly (as you see fit on your environment). Had this on VS2005, .NET 2.0 and the sample project in a console application.

    1 using System;

    2 using System.Data.SqlClient;

    3 using System.Transactions;

    4 using System.Threading;

    5 

    6 class Demo

    7 {

    8     static string connectionString;

    9 

   10     static void Main(string[] argv)

   11     {

   12         // ** Change connection strings accordingly

   13 

   14         Console.WriteLine("TransactionScope – BASIC");

   15         Console.WriteLine("============================");

   16         connectionString = @"server=.sql2005;database=testDB;integrated security=SSPI";

   17         TransactionScopeTest();

   18 

   19         Console.WriteLine();

   20         Console.WriteLine("TransactionScope – IMPLICIT UNBIND");

   21         Console.WriteLine("============================");

   22         connectionString = @"server=.sql2005;database=testDB;integrated security=SSPI;transaction binding=implicit Unbind;";

   23         TransactionScopeTest();

   24 

   25         Console.WriteLine();

   26         Console.WriteLine("TransactionScope – EXPLICIT UNBIND");

   27         Console.WriteLine("============================");

   28         connectionString = @"server=.sql2005;database=testDB;integrated security=SSPI;transaction binding=explicit Unbind;";

   29         TransactionScopeTest();

   30 

   31         Console.ReadKey();

   32     }

   33 

   34     private static void TransactionScopeTest()

   35     {

   36         try

   37         {

   38             ReCreateTable();

   39             Console.WriteLine("Table recreated");

   40 

   41             try

   42             {

   43                 using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.FromSeconds(2)))

   44                 {

   45                     Console.WriteLine("Transaction started lasting 2 seconds");

   46                     using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

   47                     {

   48                         con.Open();

   49                         Console.WriteLine("Server is {0}", con.ServerVersion);

   50                         Console.WriteLine("Clr is {0}", Environment.Version);

   51 

   52                         for (int i = 0; i < 5; i++)

   53                         {

   54                             using (SqlCommand cmd = con.CreateCommand())

   55                             {

   56                                 cmd.CommandText = "insert into TXTEST values ( " + i + " )";

   57                                 cmd.ExecuteNonQuery();

   58                                 Console.WriteLine("Row inserted");

   59                             }

   60 

   61                             Thread.Sleep(TimeSpan.FromSeconds(1));

   62                         }

   63 

   64                         Console.WriteLine("Committing… now we get the timeout (sort of)");

   65 

   66                         tx.Complete();

   67                     }

   68                 }

   69             }

   70 

   71             catch (Exception e)

   72             {

   73                 Console.WriteLine(e.Message);

   74             }

   75 

   76             Console.WriteLine("Table contains {0} rows!!!", CountTable());

   77             DropTable();

   78         }

   79 

   80         catch (Exception e)

   81         {

   82             Console.WriteLine("Unexpected error:");

   83             Console.WriteLine(e.ToString());

   84         }

   85     }

   86 

   87     static void ReCreateTable()

   88     {

   89         try

   90         {

   91             DropTable();

   92         }

   93 

   94         catch (Exception) { }

   95 

   96         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

   97         {

   98             con.Open();

   99             using (SqlCommand cmd = new SqlCommand("create table TXTEST ( F1 int )", con))

  100                 cmd.ExecuteNonQuery();

  101         }

  102     }

  103 

  104     static int CountTable()

  105     {

  106         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

  107         {

  108             con.Open();

  109             using (SqlCommand cmd = new SqlCommand("select count(*) from TXTEST", con))

  110                 return (int)cmd.ExecuteScalar();

  111         }

  112     }

  113 

  114     static void DropTable()

  115     {

  116         using (SqlConnection con = new SqlConnection(connectionString)) // connection string is set in main method (see examples)

  117         {

  118             con.Open();

  119             using (SqlCommand cmd = new SqlCommand("drop table TXTEST", con))

  120                 cmd.ExecuteNonQuery();

  121         }

  122     }

  123 }

Download Demo – TransactionTimeoutIssue.zip (5.05 kb)

The sample project also contains another class for illustrating the issue with Committable Transaction. Just exclude the TransactionScope class and uncomment the Main method in CommittableTransactionDemo.cs. 

Please feel free to drop me message if I'm missing something. Hope this helps (including me if I do forget about this in the future).

I'm off to the swimming pool. Laughing

More Information:

MSDN – Implementing an Implicit Transaction with TransactionScope 

MSDN – ConnectionString

Link: Entity Framework and ADO.NET Data Services will RTM with Visual Studio 2008 and .NET 3.5 Service Pack 1

The ADO.NET Team has just announced this in their blog. There is no definite release date for SP1 yet though.

See the post from the following link: Entity Framework and ADO.NET Data Services will RTM with SP1

Unit Testing Data Access with .NET TransactionScope

I was curious how to maintain my database at a consistent state while unit testing (technically integration testing already – but since it uses unit testing framework as they say the lines are terminologies are getting blurry) so I looked around for "database unit testing" write ups.

It turns out that [more]mbUnit has [Rollback] which makes use of COM born out of Roy Osherove's article on Simplified Database Unit testing using Enterprise Services (and a number of related articles).

But I'm using NUnit (at least for now until I have checked out mbUnit) and again although there are a number of articles on extending NUnit (using Roy's approaches among others), it's a little messy to extend, create new DLLs and all. So seeing that the approach made use of Enterprise Service, it made me wonder whether it's possible to use .NET 2.0 TransactionScope (and possibly other upcoming enhancements to System.Transactions).

I tried to come up with a simple test/project to see if it would work and it did the job so posting it. (also adding a link to compressed sample project at the bottom)

using System.Data.SqlClient;

using System.Transactions;

using NUnit.Framework;

 

namespace TransactionScopeNUnitTest

{

    [TestFixture]

    public class TestClass

    {

        private const string connString = @"Server=.sql2005;database=test;uid=<yourUID>;pwd=<yourPassword>";

 

        private TransactionScope scope;

 

        [TestFixtureSetUp]

        public void Setup()

        {

            scope = new TransactionScope(TransactionScopeOption.RequiresNew);

        }

 

        [TestFixtureTearDown]

        public void TearDown()

        {

            if (scope != null)

            {

                scope.Dispose();

            }

        }

 

        [Test]

        public void Delete()

        {

            // test runner could run the tests in any order

            // so we insert the a known entry first

            Insert();

 

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlCommand cmd = new SqlCommand(

                    "DELETE FROM Table1 WHERE Col1 = 1"))

                {

                    cmd.Connection = conn;

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    Verify();

                }

            }

        }

 

        [Test]

        public void Insert()

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlCommand cmd = new SqlCommand(

                    "INSERT INTO Table1 (Col1, Col2) VALUES (2, '2')"))

                {

                    cmd.Connection = conn;

                    conn.Open();

                    int retval = cmd.ExecuteNonQuery();

                    Assert.Greater(retval, 0);

                }

            }

        }

 

        [Test]

        public void Verify()

        {

            using (SqlConnection conn = new SqlConnection(connString))

            {

                using (SqlCommand cmd = new SqlCommand(

                    "SELECT * FROM Table1 WHERE Col1 = 2"))

                {

                    cmd.Connection = conn;

                    conn.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();

                    Assert.IsTrue(rdr.HasRows, "Inserted Row Not Found");

                }

            }

        }

    }

}

Here's the simple SQL script for the test 'Table1' too

CREATE TABLE [dbo].[Table1](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

 

NOTE: When I ran this, despite using just one connection string, it required "Distributed Transaction Coordinator" service to be running on the machine (Start > Run > type "services.msc"). I used to think that it is only needed if the transaction is escalated to a distributed transaction but more on that next time.

Also, System.Transactions is not added by default to new projects (at least for class library template) so you would need to explicitly add a reference to it. 

This is really not a discovery so to speak and I'm sure there are other articles out there on this as it's only brought about by the introduction of TransactionScope as an alternative to previous approaches but for those running on .NET 2.0 and want a simpler solution than extensions or more complicated stuff, I think this would suffice. 

Download sample – TransopeNUnitTest.zip (59.95 kb)

Related Links:

TransactionScope on MSDN

TransactionScope's advantages over ADO.NET and Enterprise Services

Test-Driven Development in Microsoft.NET book

MDAC, SNAC and ADO.NET (Data Access Technologies)

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 

ADO.NET 3.5 MCTS Beta Exam

Just received my MSDN Flash Special Edition regarding Invitation for ADO.NET 3.5 MCTS Exam today. The beta period will run from March 14, 2008 – April 4, 2008

71-561: TS: Microsoft .NET 3.5, ADO.NET Application Development counts as credit towards the following certification(s).

Register thru Prometric

Promo Code : 561B1

Beta exams are by invitation and not available to the general public but there have been some posting on the promo code so might as well have it too.

Also posting some additional information included in the the invitation here (and other resources as I will try to prepare for this exam too)

Preparation Guide : http://www.microsoft.com/learning/exams/70-561.mspx

[more]Find exam preparation information: http://blogs.msdn.com/gerryo/archive/2008/03/13/get-ready-for-the-ado-net-3-5-mcts-exam.aspx.

Special Offer: Register to be the first to know when Visual Studio 2008 exams become available. Go to http://www.microsoft.com/learning/mcp/sqlvs/offer/default.mspx to see the offer details.
There seems to be no prep guide available yet but even if you won't take the exam, it's always a good idea to stay ahead of the curve.
And here are some info about beta exams