Data Access Smackdown

Subject: 
You must register at https://www.clicktoattend.com/invitation.aspx?code=132141 in order to be admitted to the building and attend.
Microsoft introduced several new data access technologies in .NET 3.5 SP1. Which one should you use? Entity Framework? Dynamic Data? ADO.NET Data Services (Astoria)? Linq? POADN? (Plain old ADO .NET) What about ORMs? Has Microsoft lost its mind? Join Stephen in a discussion on Data Access Methodologies for the 21st Century, including a discussion of ATOM over REST. Note: This will require some audience participation.

Speaker: 
Stephen Forte, Telerik
Stephen Forte is Chief Strategy Officer of Telerik, a leading vendor in .NET components. Prior to his position at Telerik, Stephen was the Chief Technology Officer (CTO) and co-founder of Corzen, Inc, a New York based provider of online market research data for Wall Street Firms. Corzen was acquired by Wanted Technologies (TXV: WAN) in 2007. Stephen is also the Microsoft Regional Director for the NY Metro region and speaks regularly at industry conferences around the world. He has written several books on application and database development including Programming Microsoft SQL Server 2008 (MS Press). Prior to Corzen, Stephen served as the CTO of Zagat Survey in New York City and also was co-founder and CTO of the New York based software consulting firm The Aurora Development Group. He is currently an MVP, INETA speaker and is the co-moderator and founder of the NYC .NET Developer Group. Stephen has an MBA from the City University of New York (Baruch College). Stephen is also a certified scrum master.

Date: 
Thursday, November 20, 2008

Time: 
Reception 6:00 PM , Program 6:15 PM

Location:  
Microsoft , 1290 Avenue of the Americas (the AXA building - bet. 51st/52nd Sts.) , 6th floor

Directions:
B/D/F/V to 47th-50th Sts./Rockefeller Ctr
1 to 50th St./Bway
N/R/W to 49th St./7th Ave.

After 4 days at TechEd here in Barcelona, I have done 6 sessions and have 2 to go tomorrow. They are:

DAT02-IS

Data Access Smackdown

Interactive Session

Database Platform

11/14/2008   10:45AM - 12:00PM

DVP04-IS (R)

Tech·Ed Daily Scrum!

Interactive Session

Development Practices

11/14/2008   3:15PM - 4:30PM

A lot of RDs arrived in Barcelona yesterday to start TechED Europe 2008. We went out for some tapas and sangria last night and the bill came to 779 euros. What happens when you get 15 RDs trying to split the bill with their credit card? Chaos.

Here is a cool TSQL tip I will be showing off next week at TechEd Europe at one of my sessions (Rock Star Common Table Expressions).

Let’s say you have a product table that looks like this:

As you can see in the image, we have some duplicate data. You can use a Common Table Expression in SQL Server 2005 or 2008 to look up the “parents” using an aggregate HAVING clause. Then you join to the CTE to the Product table on the Product_Name field as well as the Product_ID field, but using the > indicator, so we only return the “children” or products with a higher product ID. Here is the CTE and code:

--Find the Dupes with a CTE

WITH CTEMinProductRecords

AS

(

  SELECT MIN (Product_ID)AS Product_ID, Product_Name

  FROM Products

  GROUP BY Product_Name

  HAVING COUNT (*) > 1

)

SELECT cte.Product_ID as DupeProductID,

cte.Product_Name as DupeProduct,

p.Product_ID as ParentID, p.Product_Name as ParentProduct,

p.Price as ParentPrice

FROM Products p

JOIN CTEMinProductRecords cte ON

 p.Product_Name = cte.Product_Name

  AND p.Product_ID > cte.Product_ID

Here are the results:

Let’s say you want to automatically delete the children. While the business case for this may exist (my old business did have a rule, the higher product id was the dupe), you will want to update all of the “many” tables to the lower product ID first. To then do the delete using the CTE all you have to do is convert the above select statement to a delete statement:

WITH CTEMinProductRecords

AS

(

  SELECT MIN (Product_ID)AS Product_ID, Product_Name

  FROM Products

  GROUP BY Product_Name

  HAVING COUNT (*) > 1

)

DELETE Products

FROM Products p

JOIN CTEMinProductRecords cte ON

 p.Product_Name = cte.Product_Name

  AND p.Product_ID > cte.Product_ID

I am a data guy. Maybe that is why the NHibernate Mafia likes to cal me a database weenie. Next week at TechEd Europe I am doing 5 sessions about data. Friday morning I am doing DAT02-IS Data Access Smackdown. As I said before, it is not really a smackdown, but a look at the SP1 technology and then have a discussion on how best to make choices about which technology to use in your projects. One of the things that I love is data as a service. That is one reason why I am so solidly behind the Astoria Project at Microsoft (aka ADO .NET Data Services.)

One thing that I show in the Smackdown session is a simple LINQ to REST demo. Using LINQ you can go against a raw REST based data service. (You can also do this via a proxy, which I will also show at the session and at a later blog post.)

I have a simple Astoria service here. Let’s take a look at how to talk to it via LINQ to REST. First you have to set a reference to System.Data.Services.Client and then pull in the namespace like so:

using System.Data.Services.Client;

Next you have to create an anonymous type to hold your data. Since we are modeling the Customer entity in my Astoria service you have to model the type to have exactly the same data types:

public class Customer

    {

        public string CustomerID { get; set; }

        public string CompanyName { get; set; }

        public string ContactName { get; set; }

        public string ContactTitle { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

        public string Region { get; set; }

        public string PostalCode { get; set; }

        public string Country { get; set; }

        public string Phone { get; set; }

        public string Fax { get; set; }

    }

Next inside of a console application we create a new Uri to point to the Astoria service on my server. Then we will query the service using the Astoria Uri syntax ?$filter= and add that to the Uri. After that is real simple, just loop through the customers and do whatever you want with them. Pretty easy! In a future blog post I will show you how to use the traditional LINQ operands (from, where, select) against an Astoria service.

static void Main(string[] args)

        {

            Console.Title = "Linq to Rest!!";

            Uri url = newUri("http://stevef.goes.com/northwindservice/NorthwindService.svc/");

            DataServiceContext ctx = newDataServiceContext(url);

            IEnumerable<Customer> customers = ctx.Execute<Customer>(

                New Uri("Customer?$filter=Country%20eq%20'Germany'", UriKind.Relative));

            //write it out to the console window

            foreach (var c in customers)

            {

                Console.WriteLine(c.CompanyName);

            }

            //keep the window open

            Console.Read();

        }

Next week at TechEd Europe I will be doing two talks on Scrum (with one repeat) and we are trying something new at TechEd this year, so let me know what you think.

The talk on Tuesday, DVM309: Using Scrum to Run Your Projects, is a typical TechEd breakout session in lecture format with Q&A encouraged. I’ll go through slides and examples from my experience as a scrum master (and also share some of my experiences from the certified scrum master class.) This is a good overview of Scrum good for beginners or experienced scrum masters trying to scale out scrum.

On Tuesday and Friday we turn the tables in DVP04-IS: The Tech*Ed Daily Scrum! This is an interactive session where I will be passing around a microphone and it will be 100% Q&A, war stories, and interactive, no slides if I can help it. (Come on, ask a lot of questions, tell a lot of war stories make my week a little easier!) I have done the “Daily Scrum” talk about 10 times this year in several places (New York, TechEd US in Orlando, Egypt, Pakistan, Netherlands, Bulgaria, Serbia, Connecticut .NET User Group, etc) and every time it is different and exciting. I always learn something from the audience as well. Everyone is welcome, you will see how Scrum works in the real world as well as real life implementations. Since it is mostly interactive, it is great for people who want to learn about scrum, as well as experts in Scrum. My only rule is no religious warfare, other than that, anything goes! (Just ask the Serbians, it was also the last session of their conference and we all drank beer as we did the Q&A.)

See you all there… If you can’t make it, I hope they will film it and put it online.

Yesterday I showed how there are some new language constructs in TSQL that will be very familiar to C# developers. I will be doing three talks on TSQL next week at TechED Developers in Barcelona and the talks are designed for application developers. (But database geeks will enjoy these sessions as well!) If you write any TSQL code in your applications today, or interact with it, these sessions will give you some advice and help change the way you approach particular problems.

Today I want to show a new feature about using Table Valued Parameters (TVP) in a stored procedure and then calling it from an ASP .NET page or any other .NET client using ADO.NET. A TVP is new in SQL Server 2008, so you can now declare a type of TABLE, insert data into it, and then pass it to a stored procedure. Have you ever passed in a CSV string to a DAL or stored procedure or even an XML data type, then parsed it and executed some SQL? TVP will eliminate that.  Here is an example:

First create a new table for us to play with:

Create Table TVP_TEST

( Field_ID intprimarykey, Field_DESC varchar(10))

Next create the TVP by declaring a new type. Notice that I have created the type to be exactly the same shape as the underlying table, this is not a requirement, however, it is important to do so if you are going to be doing inserts as we do below.

sp.SqlDbType = SqlDbType.Structured;

cmd.ExecuteNonQuery();

Pretty easy! Now you can pass data sets in as parameters to a stored procedure, eliminating some funky parsing on either the client or server.