Trying Out the OData/WCF Data Services Upgrades

Microsoft recently updated the Open Data Protocol (OData) and WCF Data Services framework and just last week provided some demo services so data developers can try out the new features.

The WCF Data Services 5.0 release offers libraries for .NET 4 and Silverlight 4 and a slew of new client and server features, including support for actions, vocabularies, geospatial data, serialization/deserialization of OData payloads, "any" and "all" functions in queries and more (including a new JSON format).

OData, now at version 3, is the Web protocol for querying data using HTTP, Atom Publishing Protocol (AtomPub) and JSON, released by Microsoft under the Open Specification Promise so third-party and open-source projects can build clients and services for it. Documentation for V3 is now available.

The three new V3 demo services include simple read-only and read-write models for Products, Categories and Suppliers, and a read-only service that exposes the trusty Northwind database.

The new support for actions looks promising, providing for example, a Discount action for Products that takes a discountPercentage integer as a parameter and decreases the price of the product by that percentage, as shown on the demo services page.

But I decided to quickly try out something a little simpler just as a proof of concept: the new "any" and "all" operators. They allow tacking onto URLs filters such as this example shown on the demo services page:

http://services.odata.org/V3/OData/OData.svc/Categories?$filter=Products/any(p: p/Rating ge 4)

As WCF Data Services supports LINQ, I experimented with the "any" and "all" operators in a LINQ query via a Visual Studio project, using the MSDN Library Quickstart here.

I changed this query:

var ordersQuery = from o in context.Orders.Expand("Order_Details")
                  where o.Customer.CustomerID == customerId
                  select o;

to this query (note the use of the "All" operator):

var ordersQuery = context.Orders.Expand("Order_Details")
                  .Where(c =>
                  c.Order_Details.All (p =>
                  p.Quantity > 50));

Sure enough, this query didn't work with the old Northwind service, but it worked after simply inserting "V3" into the service URL so it looks like:

http://services.odata.org/V3/Northwind/Northwind.svc/

Fig. 1 shows the result of my efforts, in a WPF application showing customer orders with a quantity of more than 50.

A WPF app successfully pulls Northwind orders via a LINQ query using the new Any operator.

Figure 1. A WPF app successfully pulls Northwind orders via a LINQ query using the new "Any" operator. (Click image to view larger version.)

Without the "V3" in the service URL, though, you get an error message (see Fig. 2).

Not using the new V3 OData service results in an error.

Figure 2. Not using the new V3 OData service results in an error. (Click image to view larger version.)

I recommend the Quickstart as an easy way to experiment with the new OData features, but you have to plug them in yourself because it doesn't use them, though it does require WCF Data Services 5. The completed project files are available if you don't want to go through the whole process of creating each project and just plug new feature functionality into them, as I did.

More improvements may be coming soon, as WCF Data Services, as the team is now using "semantic versioning" and NuGet, as have other products, such as Entity Framework. One reader asked about support for "Join," while Microsoft's Glenn Gailey has a list of improvements he'd like to see, including support for enums, client JSON support, functions and more (note that this wish list is included in a post of his favorite things that did make it into the new versions).

Share your thoughts on the new versions of OData and WCF Data Services. Comment here or drop me a line.

Posted by David Ramel on 05/14/2012 at 12:52 PM0 comments


Setting Up a Dev Machine

Well, I'm still astonished that I risk trashing my entire system if I try out some evaluation software, as I was told by readers a while back after a nightmarish experience trying to remove the SQL Server 2008 R2 beta and install the free Express version.

Some of the reader comments roasted me for being such a fool, along the lines of this from "Paul":

"By its very nature eval software is not to be installed on any machine you don't care about needing to be rebuilt from the ground up. Hasn't anybody ever read the warnings included with installation of eval software? If you are 'experimenting' with new software on a machine that cannot be wiped and rebuilt then the onus is on YOU not Microsoft."

That just doesn't seem right to me (and a lot of other people on the Net -- so there, "Paul").

Anyway, since losing a good chunk of my life recovering from that fiasco, I've been reluctant to try out some of the exciting new stuff that's come out in the database development world, as I only have one PC to work on and I need it to keep working.

But I enjoy checking out these new products and sharing my and others' experiences so we can all learn new things. And with the new SQL Server beta and other new evaluation software out, I'm dying to try them out. So I'm dusting off an old desktop to make it my dev machine.

It's almost 10 years old, but it has a fast processor, 4GB of RAM and a couple of decent hard disks (I used to use it a lot for video editing and related graphics stuff). Single core, but with the old "HyperThreading" technology to kind of emulate multiple cores. So far, so good. I've installed Windows 7, Visual Studio 2010, the Visual Studio 11 beta and Express beta, SQL Server 2012 beta and some related packages. The only problem I've had in these early stages is that my old graphics card doesn't support DirectX10 so I can't run the Windows Phone SDK 7.1 phone emulator, but I'm looking to soon buy a cheap card that fits the minimum bill (gotta love Craigslist).

As this is my first dev machine, I've looked around the Web for advice. I found Scott Hanselman's "2011 Ultimate Developer and Power Users Tool List for Windows" useful, but he must have 500 items listed there. I don't have time to even read through the whole list, much less download and install the recommended products. What, does he have a 5TB RAID or something to store this stuff on?

Also, I've spent more than a good long day downloading and installing this software, so I feel kind of leery spending a bunch more time to flesh out a system that I might have to rebuild from the ground up just because I try out some evaluation software. I only want to install the best, nearly essential stuff.

So I'm looking to you, dear database developer reader, for advice from the learned -- those who have traveled here before me. What (free) tools, packages, products are most essential to help me explore new database development products? What do you personally absolutely NEED to have on hand as you try out Visual Studio, SQL Server and other evaluation software? Also, what tips do you have on configuration or system optimization?

Please comment here or drop me a line. And, oh, "Paul," I really don't need to hear from you again, thanks.

Posted by David Ramel on 05/01/2012 at 12:52 PM4 comments


Visual Studio 11 Beta: Easier Development with LocalDB

Having recently set up an old desktop as a dev machine in order to avoid trashing my main computer with evaluation software (don't get me started), I decided it was time to try out Visual Studio 11 Beta and its new features that promise to ease database development.

SQL Server Data Tools, released with SQL Server 2012 and coming with Visual Studio 11 Beta, are interesting and certainly worth a look soon, but I decided to first check out something a little simpler that also purportedly makes life easier for database developers in the IDE: LocalDB.

It's a new version of SQL Server Express with easy installation and use with no onerous configuration or management required by developers who just want to write code and create applications. While meant for development/testing, Microsoft said it can be used as a production embedded database, also.

You get it as an option when you download the free SQL Server 2012 Express.

Download and installation went fairly smoothly, so I started to tinker. Using LocalDB in SQL Server Management Studio (SSMS) was as simple as typing "(localdb)\v11.0" into the Connect to Server dialog box. After connecting, you can see you're using LocalDB in the Object Explorer, as shown in Fig 1.

Connecting to LocalDB in SQL Server Management Studio.

Figure 1. Connecting to LocalDB in SQL Server Management Studio. (Click image to view larger version.)

You can do the same thing in Visual Studio 11 Beta from the new SQL Server Object Explorer -- described by Microsoft as designed to reflect the look, feel and functionality of SSMS -- by right-clicking the SQL Server node and choosing Add SQL Server, which brings up the same connection dialog box.

I decided to check out some MSDN Library instructional pages to see how to use LocalDB, keeping in mind that it's preview-only, subject-to-change documentation. The MSDN Library "Creating a SQL Server Express LocalDB Database" walkthrough seemed like a good place to start.

I completed the walkthrough successfully without major problems. It involves creating a Windows Forms project, adding a database and then creating tables for it. Be aware that in discussion of the Table Designer, the walkthrough has a note explaining that a new Table Designer is used in the SQL Server 2012 Release Candidate 0, while the walkthrough uses the "old version of Table Designer." The note contains a link to directions about how to use the new Table Designer, in the MSDN Library SQL Server Data Tools section. So, because the steps are similar but the table names and fields and other details aren't the same as in the walkthrough, I just had to go back and forth between pages to complete the tutorial.

The Table Designer is pretty cool, letting you create tables either in a graphical Columns Grid where you enter row information via the GUI fields for name, data type, nulls allowed and so on, or in a Script Pane where you write out the actual code, such as "CREATE TABLE [dbo].[Customers]." When using the latter, you can just click an Update button at the top of the designer to generate a script to update the database to reflect the changes in the graphical Columns Grid.

After creating the tables, constraints and foreign keys and clicking Update one last time, I populated the sample tables with data from the venerable Northwind database. This involved creating a connection to the Northwind database, showing table data for Customers and Products and simply copying and pasting the table data into the appropriate SampleDatabase.mdf tables I had created. Note, however, to get some older versions of the Northwind (or any) database to work with LocalDB, you need to upgrade it to work with SQL Server 2012. This is as simple as attaching to the database in SQL Server 2008 Management Studio and then detaching it.

I ended the walkthrough with a SampleDatabase.mdf database file that I used in a companion walkthrough, "Connecting to Data in a SQL Server Express LocalDB Database (Windows Forms)."

This walkthrough was easy. It involved connecting to the SampleDatabase.mdf database I just created via a Data Source Configuration Wizard. The wizard produced a SampleDatabaseDataSet that was added to my project, appearing in the Data Sources window. Then it was simply a matter of adding a control to the Form1 form by dragging a table from the dataset onto the form. That produced a DataGridView on the form that lets you see and edit table data upon running the application. Fig. 2 shows the app running.

The venerable Northwind database displayed in a Visual Studio Windows Forms app using LocalDB.

Figure 2. The venerable Northwind database displayed in a Visual Studio Windows Forms app using LocalDB. (Click image to view larger version.)

I didn't follow the Next Step of adding validation functionality. I did, however, investigate some more MSDN Library instructional material in pages linked to from the walkthroughs.

I found in the MSDN Library | Visual Studio 11 Beta | Visual Database Tools section a lot of pages about the Database Diagram Designer. It's supposedly opened up via a Database Diagrams node under the database connection in Server Explorer. I couldn't get this to work.

Figs. 3 through 5 illustrate my experience with Database Diagrams in a few different configurations of Visual Studio and SQL Server. From left to right: Visual Studio 10 running with SQL Server 2008 on my regular work machine, where the Database Diagrams node shows up under a database in Server Explorer and they work; Visual Studio 10 running with SQL Server 2012 on my dev machine, where Database Diagrams show up, but don't work (error message); and Visual Studio 11 Beta on my dev machine, running with SQL Server 2012, where the Database Diagrams don't even show up, but every other node is the same. The aforementioned error message in VS10 said: "This server version is not supported. Only servers up to Microsoft SQL Server 2008 are supported."

No Database Diagrams node under Data Connections in Visual Studio 11 Beta ...

Figure 3. Contrary to some MSDN Library preview documentation, there's no "Database Diagrams" node under Data Connections in Visual Studio 11 Beta ...

 

...while it shows up and works in VS10 with SQL Server 2008 ...

Figure 4. ...while it shows up and works in VS10 with SQL Server 2008 ...

 

... and shows up but doesn't work in VS10 with SQL Server 2012.

Figure 5. ... and shows up but doesn't work in VS10 with SQL Server 2012.

I looked around and found a "bug" titled "Missing Database Diagram Node in Server Explorer" filed by a user who encountered the same issue. Microsoft replied, "This is a by design change in the product. Diagrams are no longer supported in the new version of the SQL database, so the node is removed when you work with a new SQL server." Well, that settled that question. The user who filed the "bug" reported Microsoft's response in a forum and said, "It appears that they intend for us to use SSMS to design databases from now on." I'm kind of curious about that, considering that one of the stated goals to improve database development in Visual Studio was to incorporate more functionality in the IDE so users don't have to keep switching back and forth between it and SSMS.

Anyway, just beware of the "Database Diagrams" information. I'm not complaining, because as noted earlier, this is clearly marked as preview documentation, subject to change. I'm just hoping this might clear things up for someone who follows my same path and looks for help on the Web.

There's much more to explore, but I've spent enough time on LocalDB and need to move on to further explore Data Tools and other things that enhance database development in the latest Visual Studio. Stay tuned.

What are your database development experiences in Visual Studio 11 Beta? We'd love for you to share. Please comment here or drop me a line.

Posted by David Ramel on 05/01/2012 at 12:52 PM1 comments


More Good News for SQL Server Developers

A recent salary survey indicated that database-related programmers had excellent job security, and now there's more good news about job prospects in the exploding mobile arena and SQL Server usage in general.

First is a report that SQL Server developers are in high demand in the mobile marketplace. SQL Server skills are No. 4 on the list of most-coveted mobile expertise, according to WANTED Technologies Corp., which provides recruiting statistics based on help wanted ads and noted in the report that "Hiring demand for tech talent with mobile skill sets has grown drastically over the past years." This report was based on job ads placed in the month of February.

Java and Android come in first and second, of course, in this Google- and Apple-dominated mobile world, followed by JavaScript. No surprises there. But seeing SQL Server coming in at No. 4 was a surprise to me, beating technologies such as HTML, CSS, Linux and AJAX.

Next up is news that SQL Server is maintaining its popularity as a programming language. This comes from O'Reilly Radar, which has a new report on the popularity of various programming languages based on book sales. In analysis that indicated a popularity fall-off for languages such as C# and ".net Languages" (that's funny, I thought C# was a .NET language, but what do I know?) "SQL" actually improved slightly. True, it was pretty far down on the list of "Large" programming languages in terms of total sales numbers, but a slight improvement from 2010 to 2011 speaks well for you data drivers. "The fact that SQL is holding steady indicates that, despite all the NoSQL talk, there's plenty of interest in traditional SQL skills," noted an article on ReadWriteWeb.com.

Of course, SQL Server developers should probably bone up on their security skills in the wake of yet another report listing SQL injection as the "#1 database security concern." This comes in a press release from GreenSQL, which surveyed some 6,000 GreenSQL SMB users.

But take heart, you can improve those SQL Server security skills for free at the Microsoft Virtual Academy, which is offering "Mission Critical Confidence using Microsoft SQL Server 2012."

First up on the list of modules? "Microsoft SQL Server 2012 Security enhancements."

So there you go. Data Driver is a one-stop shop, informing you that SQL Server is a pretty good technology to know, and SQL injection is still a big problem, but the latest SQL Server edition addresses the problem and Microsoft will teach you how to do just that for free.

You're welcome.

How are you feeling job-wise if you're a database developer? Comment here or drop me a line.

Posted by David Ramel on 04/11/2012 at 12:52 PM3 comments


More Good News for SQL Server Developers

A recent salary survey indicated that database-related programmers had excellent job security, and now there's more good news about job prospects in the exploding mobile arena and SQL Server usage in general.

First is a report that SQL Server developers are in high demand in the mobile marketplace. SQL Server skills are No. 4 on the list of most-coveted mobile expertise, according to WANTED Technologies Corp., which provides recruiting statistics based on help wanted ads and noted in the report that "Hiring demand for tech talent with mobile skill sets has grown drastically over the past years." This report was based on job ads placed in the month of February.

Java and Android come in first and second, of course, in this Google- and Apple-dominated mobile world, followed by JavaScript. No surprises there. But seeing SQL Server coming in at No. 4 was a surprise to me, beating technologies such as HTML, CSS, Linux and AJAX.

Next up is news that SQL Server is maintaining its popularity as a programming language. This comes from O'Reilly Radar, which has a new report on the popularity of various programming languages based on book sales. In analysis that indicated a popularity fall-off for languages such as C# and ".net Languages" (that's funny, I thought C# was a .NET language, but what do I know?) "SQL" actually improved slightly. True, it was pretty far down on the list of "Large" programming languages in terms of total sales numbers, but a slight improvement from 2010 to 2011 speaks well for you data drivers. "The fact that SQL is holding steady indicates that, despite all the NoSQL talk, there's plenty of interest in traditional SQL skills," noted an article on ReadWriteWeb.com.

Of course, SQL Server developers should probably bone up on their security skills in the wake of yet another report listing SQL injection as the "#1 database security concern." This comes in a press release from GreenSQL, which surveyed some 6,000 GreenSQL SMB users.

But take heart, you can improve those SQL Server security skills for free at the Microsoft Virtual Academy, which is offering "Mission Critical Confidence using Microsoft SQL Server 2012."

First up on the list of modules? "Microsoft SQL Server 2012 Security enhancements."

So there you go. Data Driver is a one-stop shop, informing you that SQL Server is a pretty good technology to know, and SQL injection is still a big problem, but the latest SQL Server edition addresses the problem and Microsoft will teach you how to do just that for free.

You're welcome.

How are you feeling job-wise if you're a database developer? Comment here or drop me a line.

Posted by David Ramel on 04/11/2012 at 12:52 PM3 comments


Flipping the Light(Switch) Fantastic in Visual Studio 11

I've heard a lot about the new LightSwitch in Visual Studio 11 Beta and how it simplifies data-centric application development, so I thought I'd give it a try.

For those not familiar with LightSwitch, Eric Nelson wrote in an MSDN blog: "LightSwitch is targeted at business developers and power users creating custom LOB applications leveraging data from multiple sources that can be easily deployed to the desktop or cloud."

The new LightSwitch "has embraced OData," Microsoft said last month, so I thought I'd try out the new support for OData, which includes the ability to consume OData in your applications as well as produce and deploy your very own OData services for use by other applications.

Earlier this week, Beth Massi http://blogs.msdn.com/b/bethmassi/archive/2012/03/27/lightswitch-in-visual-studio-11-beta-resources.aspx compiled some useful LightSwitch information, including some of her own articles about producing and consuming OData. Massi pointed out the "goodies" in the new release include a new "cosmopolitan shell and theme," extensibility toolkit, documentation and a beta forum where users can ask questions.

A while back I wrote about how easy it was to cook up a LightSwitch application in less than an hour, from downloading the program to producing a query-based "screen" that let me add, edit and delete records from an a SQL Azure database, so I thought I'd revisit that scenario, too.

I also wanted to hook up to the Windows Azure Marketplace DataMarket (one of my favorite Microsoft product names -- quick, say it three times fast without spitting all over yourself!), which I've also written about before in the context of an ASP.NET Web Forms application.

So I wanted to quickly develop a pseudo-app that hooked into three different kinds of external data sources: a straight, public OData feed; a Windows Azure DataMarket OData feed; and a cloud -- in this case SQL Azure-hosted -- database. (Yes, the archaic local database connection is dying fast.)

Downloading the Visual Studio 11 Beta was by far the most time-consuming part of my experiment, taking more than a couple of hours. There's been a lot of user discussion about the new look and feel of Visual Studio, with less chrome, less color, fewer doo-dads and so on. I can't say I like it that much, but I've always been a function-over-form guy. I've never really understood why people cared so much about the minor design aspects of an app. If it works and the UI doesn't get in the way, I'm fine with it. I couldn't care less how colorful it is. And as you can see in Fig. 1, there ain't much color here.

Visual Studio 11 interface

Figure 1. Plain-looking interface for Visual Studio 11, but it's also a beta. (Click image to view larger version.)

Installation was uneventful and v11 installed side-by-side with v10. Though some haven't been that lucky.

After the lengthy download and setup, cranking up Visual Studio 11 and starting a new project shows that LightSwitch is prominent in your project type choices (see Fig. 2).

LightSwitch is flipped on by default.

Figure 2. LightSwitch is flipped on by default. (Click image to view larger version.)

After your new project is set up, you're told to "Start with data," with the option of creating a new table locally or attaching to an external data source, which brings up a wizard that gives you choices of Database, SharePoint, OData Service or WCF RIA Service. I started with OData. I plugged in the source address of my OData feed (for example, the public Northwind test database feed address is: http://services.odata.org/Northwind/Northwind.svc/) and then chose which login information to use: None, Windows Credentials or Other Credentials, the latter taking a user name and password. This public Northwind feed doesn't require any login info. I then tested my connection and proceeded to choose which entities I wanted to import and named my data source. Upon clicking Finish I got several warnings (such as the one in Fig. 3) that certain features of the data source weren't available, specifically "many-to-many" relationships:

The relationship between CustomerDemographics and Customers will not be imported. Many-to-Many relationships are not supported.

I was then presented with an Application Designer you can use to add queries or "screens" that are the main UI features of LightSwitch applications. For example, you could add a query (with wizard options -- no coding required) to just find customers in certain geographical regions, and then add a screen to present that list. Or you could just quickly add a screen to present a full table. Many other options are available, of course, but I just wanted a quick-and-dirty screen to prove it works.

So bringing the Customer table to the designer let me choose the type of screen template and the data I wanted to include (see Fig. 3).

Getting down and dirty with the Application Designer.

Figure 3. Getting down and dirty with the Application Designer. (Click image to view larger version.)

I then clicked OK and I was ready to test the app. Clicking Start loaded a bunch of symbols and eventually started the app. And there was my screen, showing ALFKI and rest of the familiar cast of Northwind customers. It was that easy. It took much less time to do it than explain it.

For SQL Azure, of course, you have to first sign up (I used the three-month trial) for a Windows Azure account and set up a SQL Azure database. By the way, migrating a database to SQL Azure still seems kind of problematic, judging from my own experience and that of other bloggers on the Web. There are various ways to do that, but the migration wizard I used previously didn't work for me this time. After much trial and tribulation, I ended up connecting to a SQL Server 2008 R2 database on my machine via SQL Server Management Studio and using the scripting function to write a migration script that actually worked fine.

To connect to my Northwind (yes, them again) SQL Azure database, I right-clicked on the project in Solution Explorer and chose Add Data Source, which brought up the familiar list of data source types. For SQL Azure, I chose Database and plugged in the server name in the Connection Properties box that popped up. This server name looks like this: lfi6gi15xg.database.windows.net (I scrambled that mix of initial letters, so this isn't my real server name).

I then chose Use SQL Server Authentication and supplied the user name and password, which you can get from your SQL Server management portal. By the way, check out the new Metro styling of the portal in Fig. 4.

Check out the Metro interface.

Figure 4. Check out the Metro interface. (Click image to view larger version.)

I provided the name of the database (it looks up the choices for you on the server you specified), conducted a successful connection test and moved on to the Attach Data Source Wizard where I chose which database objects to import (for the OData feed, remember, it was "entities" instead of "database objects") and name the data source, etc., as was done previously. From there, everything worked as before with the public OData feed.

So far, connecting to and using a public OData feed and my own private SQL Azure database worked flawlessly.

My last test scenario was a DataMarket OData feed. You can sign up to subscribe to a DataMarket feed with a Windows Live ID. I chose one of dozens of free subscriptions available, but there are also dozens of paid and free trial feeds, also. The DataMarket management portal provides you with account details such as a Primary Account Key and Customer ID. It also lets you "explore" datasets, so you can run sample queries, browse data, etc. This is where you get the Service root URL you need to connect with LightSwitch. For example, I subscribed to the free Consumer Legal Articles feed from Lawyers.com, which has a URL of: https://api.datamarket.azure.com/LexisNexis/ConsumerLegalArticles/.

Back in LightSwitch, I went to add another data source, chose OData Service as the type, plugged in the aforementioned Service root URL and chose Other Credentials, using my aforementioned Customer ID as the User name and Primary Account Key as the password. Upon a successful connection test, I proceeded as before.

And it worked! My first LightSwitch in Visual Studio 11 Beta was up and running, connecting to three different external data sources and showing screens that displayed data from each (see Fig. 5).

Ta-da! LightSwitch works as advertised.

Figure 5. Ta-da! LightSwitch works as advertised, showing how my application will look whether it's on the user's desktop, on a Web page, or somewhere up there in the cloud. (Click image to view larger version.)

That's it. Thumbs up! The new LightSwitch in Visual Studio 11 Beta works as advertised, giving you "a simpler and faster way to create professional-quality business applications for the desktop, the Web and the cloud," as Microsoft said on its LightSwitch 2011 trial page, which you can download if you don't want to bother with downloading and installing one of the more full-featured Visual Studio 11 Beta editions.

What do you think of the new Visual Studio and LightSwitch? Did you manage to say Windows Azure Marketplace DataMarket three times fast without spitting all over yourself? Comment here or drop me a line.

Posted by David Ramel on 04/03/2012 at 12:52 PM8 comments


SQL Server Opens Up to Java

Microsoft yesterday released the final JDBC Driver 4.0 for SQL Server after three community technology previews, continuing the effort to open its technology to better accommodate programmers using PHP, ODBC and even Hadoop, among others.

The driver provides access to SQL Server editions going back to SQL Server 2005 -- and SQL Azure -- from any Java application or applet. The Type 4 JDBC driver provides connectivity via standard JDBC APIs in Java SE 5 and Java SE 6.

Program manager Shamitha Reddy announced the release in a blog post, highlighting four new features:

  • Pure Java Kerberos -- Kerberos was added to the list of authentication options for non-Windows users.
  • Always On -- Instead of database mirroring, users can choose to use Always On Availability Groups, explained here.
  • Correlated Tracing with XEvents -- The SQL Server Extended Event general event-handling system that correlates data from different sources sports a new UI and lets users track driver-related actions.
  • FormatID change in XA Transactions -- The new driver supports Java Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions, sometimes called "eXtended Architecture" transactions. Responding to customer feedback, Microsoft changed the way the FormatID field is used so it can be used in all XA transactions, Reddy said.

Other enhancement listed by Reddy include better cloud (SQL Azure) support and support for UTF-16 Unicode character encoding and also for sparse columns ("ordinary columns that have an optimized storage for null values," according to MSDN), along with bug fixes. You can get it here.

What do you think about Microsoft's commitment to interoperability? Comment here or drop me a line.

Posted by David Ramel on 03/07/2012 at 12:52 PM0 comments


Entity Framework Developers Clamor for Better SQL Generation

"I have seen simple select statements [in Entity Framework] with 4 or 5 includes result in nearly 5,000 line SQL statements when an equivalent hand-written SQL statement is [about] 15 lines."

So reads the first of 21 comments on Microsoft's "ADO.NET Entity Framework (EF) Feature Suggestions" site, where developers can post, vote and comment on proposed EF enhancements. "Improved SQL Generation" is by far the No. 1 feature suggestion, with some 1,400 votes.

Several developer complaints about SQL generation concerned bloated code or slow performance. You saw an example of the former; the latter is exemplified by this comment:

"I just documented a case where the Contains() operator reduces EF query performance by a factor of 300. Further diagnosis revealed that the slowdown occurs in the query-generation portion of the request, so I agree that this needs some serious attention."

Microsoft's Diego Vega, program manager for DataFx, responded that the core EF library update coming in .NET 4.5 will provide better SQL generation in some cases, but not for all of the listed scenarios. "I am going to leave this idea as open for now, until we figure out a better way to track individual scenarios, which we need to do to get more actionable data," Vega said.

Speaking of .NET 4.5 and EF core libraries, some comments concerned the attempted separation of EF releases from .NET. The EF team shipped the first two releases as part of the .NET Framework, but then released separate updates to get them out to developers faster, as mentioned in this October blog post. That post said the June 2011 CTP was the first attempted full EF shipment separate from the .NET Framework. But technical problems resulted, so several feature improvements -- such as support for enums, spatial types and others -- that rely on .NET core library updates will have to wait for next EF preview that will ship along with the upcoming public preview of .NET 4.5.

When Vega reiterated this on the feature request page, a couple of readers found fault with that, as evidenced by this comment:

"Diego, No offense but moving to v4.5 of the framework is really not an option for ... most corporate software."

And this:

"I guess because EF core library is included in .NET framework we should wait until then! That is awful, please consider put EF libraries out of .NET framework. Good ORM needs frequent release cycle."

Umm ... isn't that what the team said they were trying to do back in October?

Why, yes, replied Vega in a response to the two reader comments that were posted earlier this month:

"We have indeed looked at taking the whole of EF out of .NET Framework for this same reason. The June 2011 CTP of EF was a first attempt that showed us it is going to be harder than we thought because of the impact on existing applications, partner teams and ADO.NET provider writers."

You can read more about the long, strange trip to EF 5.0 here and bone up on coming EF 5.0 improvements here.

Besides improved SQL generation, the top five feature requests include "Batch CUD Support (1,179 votes); "EF Support for Second Level Cache" (655 votes); "Support for multiple databases" (595 votes); and "Entity Designer: Speed up & Optimize for using with 200+ entities" (524 votes).

What feature requests would you like to see in EF? Comment here or drop me a line.

Posted by David Ramel on 02/27/2012 at 12:52 PM6 comments


Microsoft Boosts Performance in Entity Framework 5.0

The Microsoft ADO.NET team today announced that the upcoming Entity Framework 5.0 could boost application performance by some 67 percent over EF 4.0.

Reducing data access overhead in the O/RM and other performance tweaks resulted in one internal test that showed "repeat execution time of the same LINQ query has been reduced by around 6x," the team said in a blog post.

One improvement involves automatically compiling LINQ to Entities queries. Developers have long been able to compile such queries explicitly via a CompiledQuery.Compile method call, but apparently they weren't aware of the feature or found the API more difficult to work with than regular LINQ, the team said. (You can read about the nuts and bolts of automatic compilation here.) Now the translation of inline LINQ queries is automatically cached and CompiledQuery is no longer necessary.

In comparison tests of EF 4.0 and EF 5.0 involving a repeated query executed with ADO.NET, LINQ to SQL and various EF methodologies, "we've improved the performance of LINQ to Entities queries nearly 600%," the team said.

Of course, you shouldn't expect that kind of improvement in your real-world applications. "It would be amazing if we could give you 600% improved performance across the board," the team said. "Unfortunately, most applications do more interesting things than run the same query repeatedly."

However, a suite of tests simulating typical usage in real-world apps showed a 67 percent performance increase after the server was upgraded from EF 4.0 to EF 5.0. "These numbers are based on an internal build, but we're working hard to get a build available for public verification of these improvements," the team said.

Just upgrading to .NET 4.5 will provide these performance improvements to EF 4.0 apps. The ADO.NET team has stated it wants to start shipping EF versions separately from the .NET Framework, but there were some upgrade issues that required core library updates, so some EF improvements have to wait for .NET 4.5. "This includes support for Enum Types, Spatial Types, Table-Valued Functions, Stored Procedures with Multiple Results and Auto-Compiled LINQ Queries," an earlier blog post said.

Also, last week the final go-live release of EF 4.3 was announced, including improvements to Code First migrations, some bug fixes and several other tweaks.

The ADO.NET team blog post about EF 5.0 improvements is brand-new at the time I write this, so no readers have weighed in with comments yet, but I'll keep an eye out for any developer concerns or interesting feedback. In the meantime, you can comment here or drop me an e-mail.

Posted by David Ramel on 02/14/2012 at 12:52 PM1 comments


Ready for SQL Azure? New Service Tells You

Along with the "SQL Azure Security Services" mentioned earlier this week by Kurt Mackie at Redmondmag.com, Microsoft has also released a SQL Azure Compatibility Assessment.

Microsoft offers an introductory video tutorial for the service, with the following description:

"More and more companies are moving to the cloud. If often starts with moving data that already exists on premises. 'SQL Azure Compatibility Assessment' is a first step in solving the problem of knowing how easily your data can move from SQL Server on premises into the cloud."

A SQL Azure account isn’t required, just a Windows Live ID.

If you’re considering moving to the cloud, give it a try and let us know how it works by commenting here or dropping me a line.

Posted by David Ramel on 02/02/2012 at 12:52 PM0 comments


A University Education -- for Free

Tutorials, forums, tips-and-tricks sites and the like abound on the Web and I use them constantly to improve my developer skills, but they often leave much to be desired.

For example, it can take a long time to find just what I'm looking for. One of my pet peeves are tutorials that seem to offer just what I'm looking for but are undated, or the publication date is hard to find, so I waste time checking them out only to find that the content has been rendered obsolete for various reasons.

And the quality of information can vary greatly, as can the presentation. Well-meaning and informative tutorials can be spoiled when the author's grasp of English is so lacking that it becomes distracting and problematic. Also, where do you go if you have questions about the content? Authors may or may not respond to e-mail inquiries or comments, and forum responses can also be hit-and-miss. And how do you judge how well you've learned the material? How does your performance rank with others? What if you want to go more in-depth and really drill down into similar material?

I'm thinking that free online courses offered by some of the top universities in the world -- such as Stanford and MIT -- might solve a lot of these issues.

For example, Stanford just finished up a course titled Introduction to Databases. Instructor Jennifer Widom said that "Over 90,000 accounts were created, 25,000 students submitted at least some work for grading, and 6,500 students did well enough to receive a 'statement of accomplishment.' " You can still access all the online resources at the course Web site if you want to get a taste of the experience, which will soon be improved by optimizing the site for "self-serve" learning, Widom said.

And Stanford next month will offer up Computer Science 101. Of course, these are introductory courses, but other new courses this year include Machine Learning, Game Theory and Design and Analysis of Algorithms I (alas, no database-specific offerings are on tap for you database developers).

At MIT, Introduction to Computer Science and Programming is the No. 1 most-visited course in the school's OpenCourseWare initiative. This program "is a web-based publication of virtually all MIT course content" and doesn't offer certificates and structured teacher-pupil interaction. Other popular MIT offerings include Introduction to C++ and Introduction to Algorithms. Again, these are introductory, but you could also delve into Performance Engineering of Software Systems or the graduate-level Spatial Database Management and Advanced Geographic Information Systems. You can check out the OpenCourseWare Consortium for information on a huge amount of courseware available from hundreds of other schools and organizations.

Even better, MIT this spring will launch a more structured online learning program, similar to Stanford's, called MITx.

Carnegie Mellon University will be offering Secure Coding , Principles of Computing and other courses through its Open Learning Initiative (OLI) program. The university will release details when they become available.

The Harvard University Extension School participates in the OLI with courses such as Bits: The Computer Science of Digital Information.

These are just a few examples and there's a lot more out there, so fire up your browser and take a look. And let me know what you find by commenting here or by dropping me an e-mail.

Posted by David Ramel on 01/26/2012 at 12:52 PM1 comments


Entity Framework 4.3 Gets Final Tune-Up -- Enum Support Promised in 5.0

Microsoft last week shipped the Entity Framework 4.3 Beta 1, with some NuGet integration enhancements and bug fixes in preparation for the final go-live release, expected in the next couple of months.

"We are planning for this to be the last pre-release version of migrations and our next release will be the final RTM of EF 4.3," said a post on the ADO.NET team blog. It said the team is "still on-track to get a full supported, go-live, release of EF 4.3 published this quarter (first quarter of 2012)."

EF 4.3 Beta 1 includes some Code First migration work done last November in a separate beta, now integrated into the EF NuGet package. The Code First Migrations enhancements include new commands, command-line tools, XML documentation, improved logging and more.

The new beta also fixes some bugs in the DbContext API and Code First.

Another piece of good news for database developers was the reminder of upcoming--and long sought-after--enum support: "EF 5.0 (Enum support is coming... finally!)" Along with enums, the 5.0 release will include spatial data types and performance improvements. "As soon as the next preview of the .NET Framework 4.5 is available we will be shipping EF 5.0 Beta 1, which will include all these new features," the blog said.

But some readers were concerned with more than technical nuts and bolts. The disparate versioning system resulting from the separation of Entity Framework and .NET Framework releases led to several comments, such as:

"Oh? EF 5.0 in .NET 4.5? You renamed EF 2.0 to 4.0 to match .NET version, now you are jumping ahead without reasons? Why not keep EF and .NET versions synced? So .NET 4.5 has C# 5.0 and EF 5.0, that's a mess."

Another wrote:

"EF 5.0 is comming. What does it mean? In one of your previous articles you mentioned that EF in .NET 4.5 will be renamed to EF Core libraries and DbContext API will be renamed to EF. So what is EF 5.0? Is it DbContext API supporting new features from EF Core libraries included in .NET 4.5? Or is it EF Core libraries."

Microsoft's Diego B. Vega addressed the issue in his own comment:

"EF 5 will ship as a new version of the EntityFramework NuGet package at the same time NET 4.5 and VS 11 ship.

We are not really trying to differentiate EF from .NET. We are simply trying to evolve EF at a different pace. Indeed, we have decided to version EF separately from .NET and to follow semantic versioning rules strictly."

Vega went on to discuss the issue in more detail, and the entire EF versioning issue is explained in this blog post, if you're curious.

The ADO.NET team also posted some EF 4.3 hands-on tips and guidance in a Code-Based Migrations Walkthrough, an Automatic Migrations Walkthrough and a post on EF 4.3 Configuration File Settings.

How do you database developers like the new EF enhancements such as Code First and the DbContext API? Or do those mere technicalities pale in comparison to the odd versioning structure? Comment here or drop me a line.

Posted by David Ramel on 01/19/2012 at 12:52 PM4 comments