Data Access blog

Quick update re: Data Access blog.

We're putting this blog (Data Access blog) into suspended animation. That doesn't mean we will stop blogging about ADO.NET and data access stuff, or that we'll take down published posts or comments here - it's just means we'll carry on blogging about ADO.NET (current and future releases including info on ADO.NET Entity Framework, EDM, LINQ to Entities, etc) at our shiny new ADO.NET blog and blog about other data access technology related posts at our other team blogs (see below).

If you are subscribed to this blog's feed (there are quite a few of you!), please update you reader / aggregator so it points to the new ADO.NET blog's RSS feed or Atom feed. We can't automagically redirect you, so sorry for the hassle here.

Comments on this blogs.msdn/com/dataaccess blog will be closed, but we have re-published this blog's June and July posts over to the new blog, so you can comment there if you like.

In case you are wondering, here's run down of the various blogs run by the Data Programmability teams:

• The Data Programmability team blog (more about data access in general, rather than about a specific technology)
• SQL Protocols team blog (discussions related to Microsoft's SQL Server Protocols - Netlibs, TDS and (new for SQL 2005) SOAP. Topics include connections and SQL connectivity)
• XML team blog (covers XQuery, XPath, SQLXML, XML Editor, XSLT, XSD, XLinq (LINQ to XML) and more XML goodness)
• ADO.NET team blog (ADO.NET and System.Data namepace, posts on current and future releases including info on ADO.NET Entity Framework, EDM, LINQ to Entities, etc)

Also, check out the MSDN Data Access and MSDN XML Developer Centers.

Hope to see you there!

ADO.NET and SQL Server Everywhere

Steve Lasker has posted a screencast / video over on Channel 9 providing an overview on the various ADO.NET programming options available for SQL Server Everywhere, including an updateable resultset (SqlCeResultSet).

While on the subject, check out the new SQL Server Everywhere team blog and info on the SQL Server Everywhere CTP. Steve's SQL Server (three s') Everywhere FAQ is also a good resource.

What I Wish Developers Knew About 'x' in / for SQL Server 2005 (webcasts)

Database Journal has a good list of some upcoming SQL Server related live webcasts scheduled for July (on MSDN and TechNet):

Some that should be of interest for developers using ADO.NET:

• What I Wish Developers Knew About Writing Queries in SQL Server 2005
• What I Wish Developers Knew About Writing Applications for SQL Server 2005
• Enhancements to the SqlClient Data Provider (ref docs here)
• Implementing a Data Access Layer with the Visual Studio 2005 Dataset Designer (see this recent article for ASP.NET 'Creating a Data Access Layer')

ADO.NET vNext - feedback so far

Since announcing our ADO.NET vNext plans at TechEd last week, the team has been on the lookout for your feedback on where we heading with the next release. This post is a round up of some of the comments / feedback we've heard. It's certainly not all of it, but should give you a flavor of what we are hearing. Some of it positive, some if it less so, but all very valuable...Lots of good questions and clarification being asked for.

TechEd

Let's start with Andres Aguiar. He sat through Dave Campbell's session on Microsoft's Data Platform Vision (I'll post a link to the webcast once it's up. In the meantime, Kent Tegels has some useful notes).

Andres enjoyed the Entity Framework news:

"It was a good session. I totally buy MS's Data Vision. The most interesting stuff for me is the Entity Framework. At last there will be a conceptual data model that all MS products will share (Reporting, Replication, Analysis Services, ADO.NET). This is a big and important improvement in the way we used data. The 'Data Dude' tool looks cool. If they manage to integrate it with the Entity Framework, it would rock."

Pablo Castro, technical lead on ADO.NET also presented at TechEd. He provided a talk called 'Next-generation Data Access in .NET Applications with ADO.NET vNext' (summarized at DevX here, Kent's notes here and Jason Coyne's notes here). We had good face time with customers giving us plenty of feedback after the talks. While at TechEd, the team also had meet up with a few ADO.NET friends and gurus.

(Dave Sceppa and Pablo also presented 'Integrated Innovation: Using ADO.NET 2.0 with SQL Server 2005'. You can view the webcast here - registration required)

This week

This week, Pablo followed up TechEd with this post providing an outline of the planned improvements with links to more detailed documentation. He asked for more feedback - you've been keeping us busy by providing lots of it on your blogs and comments at Pablo's post.

Techworld covered the news too:

"Entities will help improve communication between business people and developers, said Lyn Robison, an analyst with the application platforms group at the Burton Group. "It will raise the level of abstraction so that you can begin to think of data from a business perspective, not just from a rows-and-tables-in-a-database perspective," Robison said."

Some of the early feedback on the documents included this post on Ayende Rahien's blog on the topic of LINQ for Entities, specifically: many-to-many relationships; the extensibility of the data model; and Indexed and Custom collections. You can read Pablo's response in the post's comments. Wagnerblog also had some thoughts to share in the context of ADO.NET Entities and ORMs. Tim Mallalieu (a PM on the ADO.NET team) also blogged his thoughts on Entities, sparking off some further discussion there.

A recurring theme of some of the feedback is summed up by Andres Aguiar.

"OK, it actually happened. We'll have two mapping technologies in .NET v.next.

LinQ for SQL, previously known as DLinQ is the 'simple' mapping technology.

LinQ for Entities, will be on top of the new ADO.NET Entity Framework, and will be the 'complex' (we could say 'real') mapping technology.

Now, does this make sense? How will someone decide to use one or the other?"

Ayende again:

"Oh joy! Three ORM frameworks. Linq to SQL, Linq to DataSet, Linq to Entities."

This was echoed by David Hayden and others. Clearly we have work to do in this area - this point was acknowledged by Somasegar (VP of Microsoft's Developer Division) where he blogged this week about LINQ and ADO.NET Entities:

"Our teams are busy working on the next version of Visual Studio and incorporating these technologies in a consolidated way to ensure the very best experience for our developers."

Another common question we heard was about the ADO.NET vNext CTP and its timing. In this Forum post by Lance Olson, the ADO.NET Group PM, he provides an August 2006 target date. This was later blogged by Erwyn Van Der Meer and Alex at Base4 after the team confirmed it.

Getting more into developer experience, Shyam posted up a 'great' screencast showing ADO.NET vNext in action, again prompting yet more feedback and questions.

More feedback

As we move toward the CTP later this year, we want to make sure we have a systematic, scalable process around capturing your feedback (Bugs, Suggestions and Other) on the bits we release. We also want to provide you with status on these as the product development goes on. We'll be using the Microsoft Connect platform developed for beta programs at Microsoft (IE is one of the many teams using it). Using Connect, the feedback provided goes directly into our team's bug tracking and development systems. It has recently been upgraded to replace the Product Feedback Center (Ladybug). Look out for more info with respect to ADO.NET vNext later in the year.

In the meantime, keep the feedback coming!

Oh, and don't forget to check out the new Data blog. If you want to get your thoughts heard by Sam Druker, Product Unit Manager (PUM) for Microsoft's Data Programmability team, then this is the place to do it.

Thanks!

Alex Barnett, Community PM

ADO.NET vNext screencast

Hi - I'm Shyam Pather, Development Lead on the ADO.NET vNext team.

I’m incredibly excited to share some demos of ADO.NET vNext in action. By now, many of you may have read the whitepapers and blog entries describing the new features. In this pair of screencasts (Part 1 and Part 2) you’ll be able to see the developer experience of using these features in code.

In these screencasts, I start from a program that uses the ADO.NET stack we shipped in the .NET Framework 2.0. Using a preview of the upcoming ADO.NET vNext bits, I show how it can be evolved it to take advantage of the Entity Data Model, Entity SQL, new Metadata APIs, and LINQ. Most of the time is spent in Visual Studio, looking at working code samples.

Here’s a high-level breakdown of what you’ll see in Part 1:

0:00-2:25      Intro and demo of basic ADO.NET 2.0 code.
2:25-8:00      Creating a conceptual data model
8:00-12:30    Using the Map Provider to query with Entity SQL
12:30-16:05  Explicit relationship navigation in Entity SQL
16:05-17:50  Accessing result metadata via IExtendedDataRecord
17:50-21:55  Polymorphic Queries
21:55-23:40  Filtering on entity type at the server

Part 2 builds on this and covers the following additional topics:

0:00-6:15     Obtaining results as objects
6:15-9:34     Polymorphic queries with results as objects
9:34-11:53   Removing the connection handling code
11:53-14:48 Using LINQ to express queries
14:48-21:02 Adding and updating entities

You can access the screencasts from the following locations:

• Part 1
• Part 2

I hope you find this useful and look forward to hearing your feedback.

Shyam Pather
Development Lead
ADO.NET vNext

ADO.NET vNext: The Entity Framework, LINQ and more

Once you are done with shipping large products such as SQL Server 2005 and Visual Studio 2005 you’d expect to have a quiet time for a while, slow down a bit, that kind of stuff…

Turns out that it wasn’t the case this time. Right after SQL Server 2005 and Visual Studio 2005 were ready to ship (and actually even before they were completely done) we started to work hard on the next version of the data programming technologies; we worked on a broad vision that spans various releases and various technologies, and also specifically on how ADO.NET plays in that vision.

In the context of the vision for data programmability and ADO.NET, we decided that the next big step for ADO.NET was to move to a higher-level of abstraction. Connections, commands and readers are great for low-level stuff, but it’s not exactly what you want to be dealing with continuously when writing business logic. There are other aspects that are similarly “low level” and applications have to deal with, like the actual database schemas (e.g. did you even wonder why you have to do a 3-way join just to navigate a relationship between entities instead of just saying “traverse the relationship”?).

Now we’re making public our vision on data programmability, it’s a great read, I highly recommend it.

We’re also making public the specific plans for the next version of ADO.NET, a bit more technical, less formal, but with all the details of how we’re moving the technology forward and describes the ADO.NET Entity Framework, as well as the ADO.NET Entity Data Model (EDM).

The first of a series of Channel 9 videos has been posted - this one features Sam and Anders talking about Entities, LINQ and a few details about how all the stuff fits together.

Feedback on all of this stuff is welcome. I highly encourage folks to check out all of the content we’re putting out there and write us with your thoughts.

Pablo Castro
ADO.NET Technical Lead
Microsoft Corporation

Lightweight Promotable Transactions, SqlDataReader and Commitment.

Here's a small issue you may need to watch out for when using a System.Transactions transaction with SqlClient v2.0 and Sql Server 2005.  This combination is required to get LWPTs.

The basics of LWPTs have been pretty widely discussed (here's an example: http://blogs.msdn.com/angelsb/archive/2004/07/12/181385.aspx), so I won't go into it too much.  The tricky part I'm looking at is when the transaction ends.  Let's look at an example:

  SqlConnection conn = new SqlConnection(someConnectionString);
  try
  {
    using (TransactionScope scope = new TransactionScope()) {
    {
      conn.Open();
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT 1; SELECT 2";
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
      }
    } // end of transaction scope
  }
  finally
  {
    if (conn != null)
    conn.Close();
  }

Run this, and you get an error that the connection is busy.  What?!?

At the end of the TransactionScope's using statement, the scope is Disposed(), which in this case causes a rollback (we didn't call scope.Completed()). Since it's a LWPT, the underlying transaction is just a Sql Server local transaction on the connection, and the rollback has to be issued across the connection.  The problem is that the reader is still open on the connection (notice the second select statement in the command text), so the attempted rollback fails.  This happens any time you let a reader's lifetime run into the transaction scope ending.

There are a couple of ways around the connection busy issue, but they have their own potential problems.  One is to switch the transaction to a distributed transaction.  Distributed transactions communicate through the transaction coordinator to commit or roll back, not directly over the database connection.  Of course the transaction may not commit while the reader is open, since the batch the server is running to support the reader may still be working in the database.

A second way around the problem is to turn MARS on, which will allow the commit / rollback code to execute a second batch on the connection.  This approach still suffers from the problem of the batch the reader is on hogging the the transaction context, and the commit / rollback may fail.

The moral?  The best practice is to always close your readers INSIDE the transaction scope they are associated with.  This is another good reason to wrap readers in a "using" statement.  The language itself will prevent you from having overlapping lifetimes between the scope's "using" and the reader's "using".

~Alazel

Updated on 4/15 to add DTC and MARS workarounds.

Data Access with Visual Basic 2005 (Webcast)

Register to watch this:

MSDN Webcast: Data Access with Visual Basic 2005 (Level 200)

• Start Time:   Wednesday, April 12, 2006 9:00 AM (GMT-08:00) Pacific Time (US & Canada) 
• End Time:   Wednesday, April 12, 2006 10:00 AM (GMT-08:00) Pacific Time (US & Canada)  
   
• Description:   This webcast, part of the "Visual Basic 2005 Today" series, explores the new data access controls, tools, and application programming interfaces (APIs) available in Microsoft Visual Basic 2005. Join us to explore the designer and ADO.NET enhancements and how they can help make your data access development projects faster and easier to produce.

The presenter is Jacob Cynamon.

We love customers like David Hayden!

We love customers like David Hayden!

David has been spending time at the MSDN Forums (in particular the .NET Framework Data Access forum) helping out those who've got stuck and blogging a bunch of ADO.NET 2.0 stuff.

Here's a list of David's helpful ADO.NET 2.0 articles and free tutorials:

• GetSchema and DbProviderFactories - List of Tables in a Database
• Enterprise Library 2.0 DAAB and ADO.NET 2.0 Batch Updates Feature
• ADO.NET 2.0 Tutorial : SqlBulkCopy Revisited for Transferring Data at High Speeds
• Retrieve Identity Column Value After Inserting Record in Database Table - SQL Server - Free ADO.NET Tutorials
• DataView Sorting Filtering and DataBinding in ADO.NET 2.0 - Converting DataView to Table - ADO.NET Tutorials
• Copy a DataTable in ADO.NET 2.0 - Free ADO.NET Tutorials
• Insert Update SQL Query Code Generator - Information_Schema.Tables - GetSchema
• Get List of Tables in a Database - Query INFORMATION_SCHEMA.Tables - ADO.NET
• SqlCommandBuilder and DbCommandBuilder - Generating Update Insert Delete Queries From Select Query

Thanks David!

-

If you're blogging about ADO.NET 2.0 stuff we'd love to know. Please mail me: alexbarn@microsoft.com. with your blog address and some links to stuff you'd think we'd like. Thanks!

-

 Tags: ADO.NET Microsoft

Adding nesting options to DbConnectionScope.

I managed to squeeze in enough time to implement nesting options for the DbConnectionScope class.  My approach was to follow the nesting option pattern given by TransactionScope, so I added a DbConnectionScopeOptions enum with Required, RequiresNew and Suppress options.  The effect of these options is entirely contained within the DbConnectionScope constructor.

Required: If Current is null, makes this instance Current.  Otherwise, mark this instance as already Disposed.

RequiresNew: Same logic as original constructor -- save prior value of Current to restore later and makes this instance Current.

Suppress: Saves prior value of Current to restore on Dispose and set Current to null.

I've also added an option to search through prior scopes for a connection.  If you pass in true, the current stack of connection scopes will be searched.  False means only search the current scope.

I'd like to thank Jian Zeng from the ADO.Net test team and Jim Carley from the System.Transaction team for taking the time to review the code and provide feedback prior to posting.

ADO.NET Versions and Indexing

Julia Lerman over at DevSource posted this quick article:

"Someone asked me about ADO.NET indexing which encouraged me to write about some of the amazing performance tests I have done comparing VS2003 and VS2005's DataTable indexing."

Fun Hash Joins with VB9 LINQ

Brian Beckman is a Software Architect in the Data Programmability team here at Microsoft.

His latest post should give you a good idea of the kind if stuff he's working on - Fun Hash Joins with VB9 LINQ.

Developers! - Express contest - win $10K

A new worldwide contest for developers has been launched by the folks at Visual Studio Express and SQL Server Express.

Here's how it goes: you think of something cool using Visual Studio Express and SQL Server Express. It could be desktop app, a web app, a mobile app, whatever.  Then you submit your cool idea here.

Once the finalists are picked, then you build. The top prize is $10,000 (USD) in cash, another winner will win $1,000 in cash, and all 12 finalists who complete their project will receive $250 in Amazon gift certificates.

There are three judges. Microsoft's Robert Scoble, MAKE's Philip Torrone and a 'Mystery Judge'.

Here's the contest site....good luck!

An Introduction to Data Structures

In November 2003 MSDN Online published a popular six-part series on using data structures in the .NET Framework. In 2005, the first part was updated to take advantage of the new data structures and features available with the .NET Framework version 2.0, and C# 2.0:

"We'll examine both built-in data structures present in the .NET Framework, as well as essential data structures we'll build ourselves. This first part focuses on an introduction to data structures, defining what data structures are, how the efficiency of data structures are analyzed, and why this analysis is important. In this article, we'll also examine two of the most commonly used data structures present in the .NET Framework: the Array and List."

(This article assumes the reader is familiar with C#).

You can read the updated article here.

Conversation with scientist, engineer and database legend Jim Gray

Channel 9 has posted great video interview with Jim Gray, a researcher and manager of Microsoft Research's eScience Group.

"This episode features Jim Gray. He is a "Technical Fellow" in the Scaleable Servers Research Group (Sky Server, Terra Server) and manager of Microsoft's Bay Area Research Center (BARC). Jim has been called a "giant" in the fields of database and transaction processing computer systems. In 1998, Jim was awarded the ACM’s prestigious A.M. Turing Award. 

Look out for his answer to the question: "What is your favorite data structure?".

Via Euan Garden.