SQL11UPD04-TSCRIPT-05

This wiki is a transcript of a previously recorded video.

Related content assets:

• Presentation: What’s New in .Net 4.0 and SQL Server 2012 (SQL11UPD04-DECK-03)
• Video: What’s New in .Net 4.0 and SQL Server 2012 (SQL11UPD04-REC-05)

What’s New in .Net 4.0 and SQL Server 2012

Hi, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this session, I’m having a look at the .NET for related features in terms of SQL Server 2012.

And so, specifically going to look at the changes around the Entity Framework. In particular, we’re working with Entity Framework 4.3, have a look at changes to SQL Server Data Tools, in terms of SQL CLR support, some changes in around the installer for the SQLTypes MSI. And finally, some of the other changes that have supported in those DLLs in relation to spatial.

So first up, the designer itself in Entity Framework has had quite a lot of work. A really big issue is the support for new data types. We’ll mention more about those momentarily. But they’re all supported, the new data types, in the visual designer as well. You can also, while laying out a complex entity data model, now do multiple diagrams. So you don’t have to have a single diagram that has all the complexity of your entire model. We can do that with multiple diagrams. A request that’s often been expressed is that it can be difficult to sort of compare the projects to work out what’s different. And so, one of the changes that have been made here is that the layouts for the diagram are now all stored in separate files. And this makes it much easier to do diff operations on the source code itself. It’s much easier to import batches of stored procedures now. There’s a batch import where they all come in as function imports, and there are also whole series of visual improvements, like selection-driven highlighting, we have coloring in the entity shapes, and so on. This is a nice enhancement to the designer itself.

But more importantly, under the covers, there’s now more support for object types. So, in particular, Enums are supported. Spatial has probably been the number one request from a whole lot of people. The spatial data types – geometry, geography – are now supported, along with support for table-valued functions. If you haven’t worked with these, these are very important at being able to avoid roundtrips to the server. So, instead of perhaps I make a call to a procedure to insert an order header, and then make four or five calls to insert different rows, what I can now do is pass entire tables in a single call. Now, there were ways of doing that directly in ADO.NET before, but now the Entity Framework supports this directly in calling table-valued functions. Another thing, in terms of reducing roundtrips, has been the idea that we might have stored procedures that retrieve multiple results sets. So simple stored procedures just have a single result set, maybe it does a single select or something like that, but I could have a stored procedure that retrieves me all of the details of an order. So maybe it has an order header, then all the detail rows come back as separate result sets from the same procedure. Now previously, I could sort of do that with XML but now I have direct support for dealing with multi-result sets stored procs directly inside the Entity Framework. As always, with every version, the SQL that’s generated for the Entity Framework gets better and better as well. And the LINQ queries we executed are sort of auto-compiled and cached now.

Another substantial change is the move to code-first development. Now, the typical development model for doing Entity Framework development previously, was to go and build a database and then we would extract our data model from the database. But that’s not how a lot of people now work while doing development. So, a very common requirement from developers is that they want to build an object model in code first and have that go off and sort of create the underlying database model, rather than the other way around. So support for code-first development has been added in this version as well, even to the point that I can now create code. There are a whole lot of built-in defaults for how that would connect to say, a copy of SQL Server Express, and without me even going off and creating and working with databases and tables and things like that, I can build the code, build the object, fire it up and test the code. It’ll go and build all those objects for me under the covers.

SQL Server Data Tools is introduced in this version and it includes a number of things. It has, under a single banner, it has all of the things that were in Business Intelligence Development Studio before, but it also has all of the database project construction options and project templates. We previously had the ability with SQL Server server projects to build SQL CLR-based objects. But these were done in entirely separate projects. So, where I could be building perhaps a database project that had all of my different database objects, I’d have to build a separate project to house all those CLR objects. And so, what we have in this version is the ability to create those inside the same project. Still supported, in case you want to go off and do them in separate projects, and if you have existing projects. But the idea is that these objects can now be supported directly inside a database project. And there’s also, in a database project, the properties that we might have wanted to set before, on a CLR object, there’s a SQL CLR properties tab on the project properties for your database projects.

Now one of the (I think) very interesting things a lot of people don’t seem to realize is that while the SQL Server objects such as spatial and things have wonderful support at the server level, people don’t seem to realize that same level of code is available at the client. And so, if I have, for example, a geography object that’s living down on the server, clearly, if I want to work with it, as a geography object up in my code, I need to be able to rehydrate those objects from the stream of data coming from SQL Server. Now, for sometimes, in SQL Server CLR has been introduced, Microsoft had been publishing an MSI that allows you to go and install the CLR types. Effectively, this is the same assembly with the same code that lives inside the SQL Server server itself. So if you looked in, ah, if you selected from sys assemblies on your SQL Server, you can see that assembly in place there. But there’s an MSI that allows you to install that locally. And so the idea – this is a free download – and in fact many of the SQL Server operations you want to do with spatial data, many of the spatial data operations can be done directly on that inside code even while you’re disconnected from any type of SQL Server in place.

Another set of classes that have been provided here are the builder classes. And so, while one way of getting an object in my code – a geometry or geography object – would be to go and extract the data out of a SQL Server server, to bring it – I might want to instruct a new geometry or SQL geometry or SQL geography object or one of these collections directly in code before I push it back down to the server. The builder classes have been provided for this. So these allow me to do things like create a new geography item, then go off and sort of add all the things that needed to be added to that, or a geometry collection or a geometry object, start adding lines and curves and things directly to that, and create that in memory in code first. And those builder classes have been updated for this version as well to support the new objects at the SQL Server end.

Now, finally, the spatial work has – substantial work has been done on spatial in SQL Server 2012. And so, the biggest issue there is the support for curved objects. So where we before had vector-based objects all the time, that could represent much, much larger data than what is needed if we had support for curves. So, an example I mentioned in an earlier session that’s easy to visualize, is if I was trying to show the shape of a circle, while it is possible to draw all sorts of little vectors to get that, you’re way better off to say, look, here’s the center, and here’s the radius of a circle. Just defining curves in an appropriate way leads to much, much smaller code.

So, in this version, those sorts of circular objects are now supported in this SQLTypes DLL. In addition to that, SQL Server’s been updated so that the restriction that we had previously on objects needed to fit inside a single hemisphere – that’s been released. That limitation doesn’t exist inside the server anymore. Those same limitations have been removed now inside the objects up in the client side code as well. We have new Sink objects for geometry and geography in place. The builder classes have been updated to support these circular arcs, and we’ve now got simplified ways of rehydrating objects in code when they’ve been retrieved from the server. So, if you look at the example I’ve got here, we’ve got a SQL geometry object being instantiated, and then, previously, from some data reader, I’ve said GetSQLbytes, so grab this first column here, and then grab the stream of data, and then apply the BinaryReader over that and then call the Read method on the geometry object. And so, note the simplification on the code here, it’s now – SQL geometry exposes a de-serialized method. And I can just pass the column directly to that, and that will rehydrate the object for me.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)