Data Access blog

Quick update re: Data Access blog.

MSDNArchive — Wed, 12 Jul 2006 00:57:00 GMT

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

MSDNArchive — Fri, 07 Jul 2006 07:54:00 GMT

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)

MSDNArchive — Wed, 28 Jun 2006 07:24:00 GMT

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

MSDNArchive — Sat, 24 Jun 2006 07:45:00 GMT

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

dpblogs — Thu, 22 Jun 2006 04:59:00 GMT

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:

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

dpblogs — Tue, 20 Jun 2006 14:51:00 GMT

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.

MSDNArchive — Fri, 14 Apr 2006 23:11:00 GMT

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)

MSDNArchive — Wed, 12 Apr 2006 10:17:00 GMT

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!

MSDNArchive — Thu, 30 Mar 2006 06:29:00 GMT

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:

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.

MSDNArchive — Thu, 30 Mar 2006 00:01:00 GMT

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

MSDNArchive — Fri, 17 Mar 2006 09:04:00 GMT

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

MSDNArchive — Thu, 16 Mar 2006 08:13:00 GMT

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

MSDNArchive — Tue, 14 Mar 2006 21:45:00 GMT

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

MSDNArchive — Mon, 13 Mar 2006 19:53:00 GMT

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

MSDNArchive — Thu, 09 Mar 2006 22:40:00 GMT

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.

10 Mistakes Developers Make With Databases

MSDNArchive — Tue, 07 Mar 2006 02:01:00 GMT

A new article published at Developer.com is worth checking out: Ten of the Biggest Mistakes Developers Make With Databases.

Data Binding in ASP.NET 2.0

MSDNArchive — Sat, 04 Mar 2006 09:52:00 GMT

Jesse Liberty has written up a good article showing how to bind data into ASP.NET control over at O'Reilly's brand new ASP.NET 2.0 Training Center site:

"...in the next version of the .NET Framework a great deal of the ADO.NET object model has been incorporated into controls that let you interact with data declaratively, and that spare you from writing boilerplate code to create data sets, extract tables, bind tables or views to controls, and so forth.

In this article, you'll create a simple Web Form that lets you page through the details of the Orders table from the Northwind database."

.NET Rocks! - LINQ with Barry Gervin

MSDNArchive — Sat, 18 Feb 2006 01:44:00 GMT

Over at .Net Rocks!, Regional Director Barry Gervin introduces LINQ, a nascent set of language extentions providing query services for list-based objects.

Alex Barnett,
Community Program Manager

A ConnectionScope class. [Alazel Acheson]

MSDNArchive — Tue, 14 Feb 2006 20:48:00 GMT

I’ve heard a few comments from people who would like an easier way to manage connection lifetime & use across multiple methods. Most often, the problem is due to using a TransactionScope at an higher level, but opening and closing connections inside the methods – generally resulting in a distributed transaction unless you manually move a single connection around. For example:

void OuterMethod() {
    using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {
        InnerMethod("select * from testtable");
        InnerMethod("update testtable set col1 = N'new value'");
        tx.Complete();
    }
}

static void InnerMethod(string sqlText) {
    using (SqlConnection conn = SqlConnection(connStr)) {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.ExecuteNonQuery();
    }
}

To avoid the distributed transaction, you would need to create the connection in the OuterMethod and pass it in as a parameter (somewhat tedious) or assign it to a member variable (somewhat risky, as you are then probably maintaining a reference to the connection beyond it’s intended lifetime).

I've implemented a simple scope class for db connections that can simplify the process (see the attached file). Feel free to use this class directly or modify it as needed.

To use it, simply create a new DbConnectionScope in the OuterMethod and follow one of the two patterns for getting your connection to the inner scope:

Create, open and place your connection into the scope prior to use with AddConnection (generally the OuterMethod), assigning it a key for identification. In the InnerMethod, pull it out using GetConnection and assign it to your command before executing.
Use GetOpenConnection() in the InnerMethod and the scope will construct & open your connection as needed, using the connection string as the key.

The example, re-written using the second pattern, looks like this:

void OuterMethod() {

using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {

using (DbConnectionScope db = new DbConnectionScope()) {

InnerMethod("select * from testtable");

InnerMethod("update testtable set col1 = N'new value'");

tx.Complete();

}

static void InnerMethod(string sqlText) {

SqlCommand cmd = new SqlCommand();

cmd.Connection = (SqlConnection) DbConnectionScope.Current.GetOpenConnection(SqlClientFactory.Instance, connStr);

cmd.ExecuteNonQuery();

}

This class is only something you’d want to use only if you specifically want to re-use the same open connection – the connection pool does a much better job of handling connection re-use when your logic allows for the connection being reset, and you don't need any particular state associated with it. You also need to keep in mind the problems that using the same connection can cause – for example, only one command executing at a time if MARS is not on.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Updated: Fixed a glaringly simple bug in the Dispose() method.

Updated: Missed one other bug, now fixed.

Getting Schema in ADO.Net 2.0

MSDNArchive — Mon, 13 Feb 2006 03:27:00 GMT

Carl Perry, a Program Manager from our ADO.Net team has blogged about how to use and extend the new MetaData Schema functionality in ADO.Net 2.0. The blog is here:

http://blogs.msdn.com/cperry/archive/2006/02/06/526323.aspx

This is one blog that is definitely worth a read if you are trying to get schema information from the Data-Base servers.

Sushil Chordia,
ADO.Net

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Shawn Wildermuth on Decomposing ADO.NET

MSDNArchive — Sun, 29 Jan 2006 10:47:00 GMT

The .NET Rocks! Show had Shawn Wilderman as their guest last week. Shawn is the Microsoft C# MVP who runs ADO.NET Guy:

"Carl and Richard interview Shawn Wildermuth about ADO.NET. He's down on datasources as a one-size-fits-all solution to data access. He talks about his upcoming book in which he attempts to organize projects into archetypes and help the reader determine which archetype their project is closest to."

Shawn Wildermuth on Decomposing ADO.NET

Listen online - .wma stream
or download here - .mp3 or .wma.

Alex Barnett,
Community Program Manager

Tags: ADO.NET, Data Access

WinFS and peer-to-peer

MSDNArchive — Sat, 28 Jan 2006 07:22:00 GMT

Mark Scurrell, Program Manager on the WinFS Sync team has provided an overview on the WinFS blog of the functionality provided to allow applications to synchronize data between WinFS stores:

"For Beta 2 we have decided to focus on providing the peer-to-peer synchronization platform and will not provide any specific transport implementations. We have therefore modified Store Synchronizer so it requires a transport implementation. In the Beta 2 SDK we will of course provide sample code for a transport, guidance on how to build a transport, and reference material for the Store Synchronizer classes.

...My main goal with this article is to raise awareness of the set of synchronization services that WinFS provides to support peer-to-peer sync application development and also to highlight the flexibility we allow in terms of diverse sync scenarios, topologies and network configurations."

The full post is worth a read.

Alex Barnett,
Community Program Manager

Tags: WinFS, P2P

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Impersonation inside SQLCLR Stored Procedure [Jian Zeng]

MSDNArchive — Wed, 25 Jan 2006 20:49:00 GMT

In SQL Server 2005, we now have the ability to write managed (or CLR) code inside a Stored Procedure. This implies that you now have the capability to connect to a remote or the local SQL Server with ADO.NET via the System.Data assembly. When doing so with intergrated authentication, you normally will specify 'integrated security = true' in the connection string. Since you are inside SQLCLR, the credentials used to connect, will be the NT account from which SQL Server service is running. If the SQL Server was started as an account that doesn’t have the permission to access the remote server, you will get an error message that will indicate the login failure when executing the SQLCLR Stored Procedure. For example, if the service is running as Network Service account, you will get an error message that saying something like

"A .NET Framework error occurred during execution of user defined routine or aggregate 'p_TESTNAME': System.Data.SqlClient.SqlException: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE'.”

This might not be what you want. Most of the times you want to use the NT user account that is currently logged in that SQL Server box rather than the SQL Server service account. How can you do that? SqlContext.WindowsIdentity property is what you need. When you call SqlContext.WindowsIdentity inside SQLCLR, it will return the WindowsIdentity token of the user that logs in the machine. Then you will have to impersonate that user before the data access connection is being called. The following is the code snippet to show you how to do it:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Security.Principal;
public class c_TESTNAME
{
public static void p_TESTNAME ()
     {
         WindowsIdentity newId = SqlContext.WindowsIdentity;
         WindowsImpersonationContext impersonatedUser = newId.Impersonate();

         try {
            using (SqlConnection conn = new SqlConnection("Server=RemoteServer;Integrated Security =true"))
            {
               conn.Open();
               //Do something…
               conn.Close();
            }
         }
         finally {
            impersonatedUser.Undo();
         }
      }
}

Notice that I wrap the database connection code inside a try..finally block. This will make sure that the user’s context always gets reverted. Otherwise the execution of the SQLCLR stored procedure will complain that the thread NT token was not reverted.

The above code works fine with one limitation. When we changed the user’s context by impersonating a different user inside SQLCLR, you can’t do any inproc data access. You will get an error when you try to open a connection with “context connection = true”. The inproc data access is only allowed after you call WindowsImpersonationContex.Undo() method.

Jian Zeng, ADO.Net

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Microsoft SQL Server 2005 JDBC Driver -- RTW [Shelby Goerlitz]

MSDNArchive — Mon, 23 Jan 2006 20:52:00 GMT

After two betas and 6 months of customer feedback the SQL Server 2005 JDBC Driver is generally available for download here – http://msdn.microsoft.com/data/jdbc.

Our core goal with this new driver is to demonstrate our long-term commitment to providing enterprise-class SQL Server data access support for the Java development community – to include JDBC in the family of data access technologies that Microsoft ships for SQL Server: ADO.Net, MDAC, and SQL Native Client.

To ship this version of the driver we worked closely with most of the major technology vendors in J2EE and we look forward to extending those relationships to make SQL Server an even better fit for Java developers.

A big thanks to all the customers who left feedback and comments for our betas – we did our best to get back to everyone and we look forward to hearing from you in the future.

-Shelby Goerlitz

Microsoft SQL Server Data Programmability

PDC 05 demos - Query Processing over DataSet, Fast Uploads, Caching

MSDNArchive — Tue, 10 Jan 2006 01:38:00 GMT

First of all I’ll have to apologize because it took me several months to put this code out here. Right after PDC we were so busy that I didn’t have the chance before to sit down, clean-up the code a little bit, add a bunch of comments and write a README file.

Finally, here are the samples for the PDC session DAT408 - "ADO.NET 2.0: Advanced Data Access Patterns". The demos include:
- A small sample query processor that allows you to run queries (with projection, filter and joins) over the DataSet
- An implementation of a technique for really fast change processing against a database server
- A demonstration of a caching scheme that uses SqlDependency and DataSet for granular cache loading and expiration

You can also see the actual talk with me speaking here, you can download the slide deck here, and the whole talk for offline viewing here.

Enjoy! (and sorry again for the delay :)

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation