Tag Archives: Kiely

Choosing the Right Action – Microsoft SQL Server 2012

The job of the action invoker to select the appropriate action method is more complicated than may appear on the surface. There could be multiple overloaded methods with the same name in a controller, they may be decorated with different attributes that make their selection appropriate in some scenarios and not others, their action method name may be different from their class method name, and other complications. Designing an effective controller requires that you understand how the selection process works.

The first thing that the action invoker does is to look for the action portion of the route definition for the request. Using the default route defined in a new MVC application shown below, it is the name at the second position.


Even if the current URL doesn’t include the action portion of the route, the default route definition defines it to be the “Index” action.

Once the action invoker has the name of the action, it needs to map that action name to a method of the controller. The simplest case occurs when there is a single method in the controller class with a name that matches the action name. For example, in a new MVC application the Index action of the Home controller maps to the single Index method in that controller.

But what qualifies as an action method? Is every method defined in the controller a potential action method? Not at all. There are a few requirements that a method must meet for the action invoker to consider it to be an action method candidate:

  •  It must be a public method and not marked as static.
  •  It cannot be a method defined on System.Object or the Controller base class. In other words, it must be defined within your custom controller object, not inherited from another object. So ToString could not be an action method because it is defined on System.Object.
  •  It cannot be a special method, such as a constructor, property accessor, or event accessor.
  •  It cannot have the NonActionAttribute decoration. This attribute is handy if you want to include a public method in a controller but don’t want to use it as an action method.

Taking all these restrictions into consideration, the action invoker uses reflection to find all methods in the controller with the same action name as the action name specified in the URL and meet the previous requirements.

But here is where things get a bit murky. Through the use of attributes on the controller’s methods, there could be multiple methods with the same name, the action name could be different from the method name, and there may be a selector attribute that restricts which requests an action method can respond to. This can create a bit of a gauntlet that the action invoker must navigate in order to find the correct action method, so let’s explore these action method features.

WARNING! By default, anyone anywhere could invoke any public method you create in a controller, unless you decorate the method with the NonAction attribute. This sounds scary, but most of the time the only public methods you have in a controller class will be action methods, so this is a non-issue.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Domain Centric Modeling – Microsoft SQL Server 2012

When the first version of Entity Framework came out, it included support for database-first design, which would reverse engineer an existing database and build a model for you. You could build a model using the designer, but there was no tooling support for building a database from the model, so you had to perform that work yourself. The next version of Entity Framework, version 4, released with .NET 4 and Visual Studio 2010, added the necessary tooling support that made model-first design a viable option for using Entity Framework. Through these two releases, the database and model were the focus of early development work on a project. But if you follow domaincentered design, you probably found that Entity Framework really didn’t fit into the way you create applications.

All that changed with Version 4.1 of Entity Framework, released in the early part of 2011. This version added yet another option, code-first design. Codefirst design lets you create the domain model for your application by building Plain Old CLR Objects (POCOs), with no Entity Framework code whatsoever and without using any base classes. Then, through some clever features, you can create a context class that almost magically turns the objects into fully functional entity data objects. Even more amazingly, you don’t have to worry about creating the database at all. Assuming that you can live with the default conventions that you’ll learn about in this chapter, Entity Framework will create the database for you automatically when you first run the application.

The way that code-first works by default will probably work for most applications early in their development cycle. But when you’re ready to deploy the application to a production server, or need more flexibility in how Entity Framework creates a database, you can take advantage of data annotations on the model classes or use a fluent API to take control of the process. And even if you have an existing database that the application will use, you can still use code-first design and map the POCOs to the existing tables and fields.

The biggest benefit of code-first design is that it lets you focus on domaincentric modeling for application development, focusing on the data objects in your application rather than on the database or the Entity Data Model. In fact, your application won’t even have a model, and won’t have an .edmx file in the project, or any XML mapping code. Entity Framework takes care of inferring the model—including the conceptual, storage, and mapping models—at runtime. It really is a code-centric style of writing database applications.

Code-first design works so well and is implemented so nicely that we are confident that it will become the method of choice for new application development.

NOTE Code-first design is only available with Entity Framework 4.1 and later. This is an out-of-band release made available well after the release of Visual Studio 2010, .NET Framework 4, and Entity Framework 4. As a result you’ll need to download and install this release separately. See the course introduction for information.

Using Code-First

There are two different ways you can use code-first for development. The first, and what we expect will turn out to be the most common way, is to write data model .NET classes in your application first, then let code-first in Entity Framework create the database for you. This is the usage scenario that you will learn about in this chapter. Using code-first this way is the easiest, most agile way to jump-start database development, and lets you evolve and refactor the application in its early stages right through to production. Code-first can even update the database as you make changes to the model.

The other usage scenario is to again create the data model classes in your application, then map those data classes to an existing database. This will provide the information Entity Framework needs to provide data access between the application and the database, without the formality of an Entity Data Model. This might sound a little like database-first design, but the difference is that you create your own POCOs rather than having Entity Framework generate them from the Entity Data Model.

As you’ll learn later in the chapter, there are two ways in Entity Framework to map classes to objects in a database, no matter which usage scenario you use. One uses data annotations on the classes and properties of the data model classes, and the other uses the DbModelBuilder API to control the mapping in code.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Types of Spatial Data

SQL Server includes two spatial data types—Geometry and Geography—that encapsulate the database’s support for spatial data. Each data type includes pretty much the same point, line, shape, and collection objects, as well as rich sets of methods and properties for working with these shapes. Figure below shows the hierarchy of available objects. The way this works is that you define a field or variable as either the Geometry or Geography data type, then assign to that field or variable data that describes the point, line, shape, or collection you want to store. The yellow/lighter objects shown in the figure are abstract objects, while the blue/darker objects are concrete objects that you can store in the spatial data type. The only difference between the Geometry and Geography object hierarchies is that in Geography there is a FullGlobe object that represents the complete surface of the earth. This is shown in Figure below in a lighter color and dashed line because it is not part of the Geometry object hierarchy.

The shapes defined by the various spatial objects are vector objects, which are collections of points, lines, curves, polygons, and compound curves. You’re likely to use the following spatial objects most often to store and manipulate within SQL Server:

  • Point: Identified by a pair of coordinates as an exact location as X and Y coordinates, but can also include Z (elevation) and M (measure). SQL Server doesn’t use the Z and M coordinates, but lets you store the data so that client applications can use them.
  • LineString: A path along a sequence of points. It is a one-dimensional shape: it has length but no area, even if multiple connected LineStrings look like a closed shape. The LineString is simple if it doesn’t cross itself, or a ring if the starting point is the same as the ending point.
  • CircularString: Similar to a LineString, but defines a curve as a portion of a circle instead of a straight line.
  • Polygon: A closed 2-dimensional shape; a ring. A polygon has both a length property and an area. It can have holes in its interior, the shapes of which are defined by other, smaller polygons. The area of a hole is excluded from the area of the outer polygon.

You can also create collections of these basic shapes, including a MultiPoint, MultiLineString, and MultiPolygon, each of which can contain only the specified shapes. If you need a collection of any type of shape, including the multi-shapes, you can use a GeomCollection. This makes it easy to work with groups of shapes as a single unit.

The Geography and Geometry data types are .NET classes implemented as system SQLCLR objects with several dozens of methods. Many of the methods, about two-thirds, have names that start with the prefix ST, such as STArea, STLength, STDistance, and STGeomFromText. These ST methods are implementations of Open Geophysical Consortium standard methods (the ST stands for spatiotemporal). These methods provide the most commonly used, basic features for working with spatial data.

Microsoft also added some custom extension methods to the objects to support features not part of the OGC standards. These method names don’t have the ST prefix, such as Parse, ToString, and Reduce.

The nice thing about the Geography and Geometry data types is that they share a lot of methods that work the same across the two types. The main difference is that with the Geography type you’ll use latitude and longitude coordinates but with the Geometry type you’ll generally use x and y coordinates.
ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

The HierarchyID Data Type

As the name suggests, relational databases are best at storing and retrieving relational data. That’s the kind of data that fits neatly into multiple tables, each consisting of rows and columns of relatively homogeneous data, all related through mechanisms such as foreign keys. Most of the material in this course covers relational data, since SQL Server is a relational database system.

But the reality is that sometimes you have chunks of data that don’t fit neatly into the relational model. One example of commonly used non-relational data is hierarchical data. This is data that defines some kind of hierarchy, such as an organizational chart or part subassemblies to build a complex object like an automobile. If the structure is rigidly set with a limited number of levels, you can do a decent job of representing the hierarchy using a table for each level, or other techniques. But it can be very difficult to represent the hierarchy if the structure needs to be flexible.

You can always store hierarchical data outside of SQL Server, but over the years, people have come up with various ways of adapting hierarchical data to fit the relational model. Some of these include:

  •  Table self-joins: In a table self-join, you use a single table to contain the hierarchical data. The table has a foreign key to itself—thus the name “self-join”—to reference another row in the table. A good example of this is the Northwind Employees table, which has a ReportsTo foreign key field to identify the person to whom an employee reports. You can use that field to construct the Northwind sales organizational chart.
  • XML: XML data is inherently hierarchical, consisting of elements that entirely contain one or more child elements in a structured hierarchy. SQL Server 2005 introduced XML as a native data type, and Microsoft integrated it well with relational data. XML is handy because you have a great deal of flexibility in how you define the hierarchy. But you have to learn specialized methods to work with the data, and searching and sorting on the data can be problematic.
  • Roll your own: SQL Server 2005 also introduced the capability to create your own custom data types using the .NET Common Language Runtime (CLR). Many people took advantage of this feature to create their own hierarchical data type, with all the features to support their needs. SQLCLR types support properties and methods, so the type could implement behaviors as well as custom data structures to support hierarchical data. Such custom types were far from trivial to implement, and it took a lot of work to get it right.

Because developers and administrators frequently need to store and work with hierarchical data in SQL Server, Microsoft introduced the HierarchyID data type in SQL Server 2008. This is a compact object you can use to manage hierarchical data using materialized paths. It is compact in that it uses very little storage, and it materializes paths and manages the hierarchy for you. It is a System CLR type, which means that Microsoft implements it via .NET code.

Because it is a system type, you don’t have to enable custom SQLCLR code in a database before using the HierarchyID data type, as you must for your own custom SQLCLR code.

You can use the HierarchyID type to represent organization charts, map a file directory system on your hard drive, create an assembly part chart with subassemblies, represent project tasks, and for many other uses. The type has rich support with methods that provide many ways to maintain the hierarchy, such as making arbitrary insertions and deletions, as well as relocating sections of the hierarchy to other places.

There is no magic to the HierarchyID. Although SQL Server gives you everything you need to maintain the integrity of the hierarchy, it doesn’t automatically enforce hierarchical relationships. You have to do that in your code, using the tools that are part of the data type. The HierarchyID type also does not guarantee uniqueness, but you can enforce uniqueness in other ways, such as with a unique constraint on a HierarchyID field in a table.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.