Tag Archives: developer tutorials

SQL 2012 Developer: Aggregates


One of the more exciting types of SQLCLR code modules is custom aggregates. Earlier versions of SQL Server didn’t offer an easy way to extend the rather short list of T-SQL aggregates, such as SUM, AVG, MAX, MIN, and COUNT. You still can’t use T-SQL to create custom aggregates, but you can write your own in SQLCLR. It’s not something you’ll need to do often, but it can greatly simplify application coding in situations where you need it. 

Unlike other SQLCLR code modules, you can define only a single custom aggregate within a class. The class must be public and requires four non-static methods, which SQL Server calls as it processes the data to aggregate:

  • Init initializes storage values. SQL Server calls this once at the beginning of the query that uses the aggregate.
  • Accumulate does the work of combining values. SQL Server calls it once for each row that is aggregated, and it must support multiple instances of the aggregation code that is executing so that SQL Server can utilize multiple threads to make operations more efficient.
  • Merge is called at the end of the operation to merge the aggregations of multiple instances into one final value.
  • Terminate is the last method called and returns the final aggregated value.

You also have to deal with serialization issues because instances of the aggregation class have to be moved between threads. This complicates the code only a little, since the .NET Framework has rich support for serialization. Most commonly, you’ll implement the IBinarySerialize interface to let .NET do all the serialization work, but there are other options if you have a special need.

Consider a sample aggregation. A common requirement in applications is to create a concatenation of strings within a table, delimited by a single character such as a semicolon. Some applications require information in this format for processing. You’ll create a Concatenate aggregation that you can use to concatenate all string values in a field. For example, once you create the aggregation, you could run the following T-SQL code to produce a single string with all product names from the Northwind Products table that have a category ID of 4, which is Dairy Products.

This code produces the following string (with line breaks added to fit on the printed page):

The first part of the SQLCLR code defines the aggregation class and decorates it with Serializable and SqlUserDefinedAggregate attributes. The SqlUserDefinedAggregate attribute needs the following parameters, most of which the query processor uses to perform the aggregation properly.

  • Format specifies the serialization format, either Format.Native or Format.UserDefined. You’ll nearly always need to use UserDefined, although SQL Server has enhanced support for the Native format.
  • IsInvariantToDuplicates indicates whether the aggregation result is the same if any of the items are duplicates. The Concatenate aggregation returns a different result if duplicates exist (they’ll appear multiple times in the resulting string), so set this value to false.
  • IsInvariantToNulls indicates whether the aggregation result is the same if any of the items are nulls. Concatenate ignores null values, so set this value to true because the same string will result whether there are nulls or not.
  • IsNullIfEmpty indicates whether the aggregation result is null if no items were aggregated, such as when the WHERE clause returns norecords to aggregate. If no strings exist, Concatenate returns a null, so set this to true.
  • MaxByteSize is required by the UserDefined serialization format and indicates the largest aggregation value that can be returned. This is set to 8000 bytes in Concatenate.

Concatenate is implemented in the following code as a structure and inherits from IBinarySerialize so that SQL Server can properly use multiple instances of the aggregate, using the listed parameters.

SQL Server calls the Init() method before aggregation begins. This is the point where you should initialize any variables used in the aggregation. Concatenate uses a StringBuilder object in an sb variable to hold the aggregated values, so that variable is instantiated here.

SQL Server calls the Accumulate method when it has a new value to add to the aggregation. Concatenate works only on string type data, so the method receives a value of type SqlString. If the value is null, it returns immediately without doing any aggregation task. Otherwise, it calls the Append method of the StringBuilder variable to concatenate the string.

The Merge method is used to aggregate multiple instances of the Concatenate object. In this case it simply concatenates the value passed to it by the SQL Server query processor to the current instance’s aggregation value. The method is passed an object of type Concatenate and reads the object’s StringBuilder’s ToString property to get the current aggregation value.

Often you’ll need to do more work here; for example, when the resulting aggregation needs to be in alphabetical order. In that case, you may want to use a sortable .NET list object to store the strings internally while processing the aggregate. But here a simple append is sufficient, since the order of the items in the final string is not important.

The last of the four required aggregation methods is Terminate. SQL Server calls this after it processes the last value that it aggregates. This method needs to return the final aggregated value, so it first checks whether the current string is of zero length. If it is, it returns a null. Otherwise it strips off the final semicolon and returns the value. It also clears the sb object in case this instance of Concatenate is used for another aggregation.

The aggregation object also has two other methods, required because the object implements the IBinarySerialize interface. The Read method is called when the object is deserialized and restores the value of the sb variable. Write puts the current string value of sb into the serialization object w.

This simple but useful example shows what is necessary to create a SQLCLR aggregate. Other custom aggregates may require more complex code to implement all the required features, and you may have to use multiple class-level variables to keep track of multiple values during the course of calculation, but it really isn’t all that hard to do once you understand the basic principles. You’ll need to have a good handle on how .NET does serialization. But these techniques really become valuable when you implement your own
user-defined types in SQLCLR, which open up whole worlds of possibilities in SQL Server.


ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Advanced SQL CLR 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.

C# 2012: Anatomy of an Async Method

There are two new keywords added to C# for asynchronous programming: async and await. The async keyword is a modifier for methods, delegates, and lambdas to indicate to the C# compiler that a method might contain async code. It also allows other code to call this method asynchronously. 

If a method doesn’t contain async code, it executes synchronously, but you will receive a warning message from the C# compiler. To run code asynchronously, use the await modifier when executing a method, anonymous method, or lambda. Here’s an example of using async and await keywords to implement an async method:

The async modifier indicates that StartAsync can contain async code. The await modifier calls the DoNetworkCommunicationsAsync method and waits for it to complete. In other words, it awaits the method.

Notice the suffix to the StartAsync method is Async. You aren’t required to add the Async suffix to the method name, but it’s a convention that Microsoft recommends and most developers follow in practice. Another tip on naming conventions refers to methods in earlier versions of .NET that implement EAP and already have the Async suffix—you would name them with the TaskAsync suffix.

To make the example simpler, the return type in the previous example is void, but async methods do have return types other than void. For now, let’s defer discussion of the return type until later, which is an important subject of its own.

The previous example doesn’t have any parameters by design in order to keep it simple. However, async methods can have parameters, but none of the parameters can be ref or out.

As in APM and EAP, as soon as the code calls an async method, with the await modifier, control returns to the caller while the method executes asynchronously. Expanding on the previous method, the following code demonstrates a basic example of how async methods work:

Let’s look at what this code shows in terms of control or what is running when. The specific threading model is defined separately for each .NET technology by a type derived from SynchronizationContext, but this is beyond the scope of this course.

The Main method calls the StartAsync method. Because StartAsync is modified as async, the code will run synchronously until it encounters a call to an async method with the await modifier. As soon as the code calls the method with await, control returns to the calling code Main. So you have two methods running at the same time—Main and StartAsync.

StartAsync continues by calling the DoNetworkCommunicationsAsync method. However, the await modifier makes StartAsync wait until DoNetworkCommunicationsAsync completes. When DoNetworkCommunicationsAsync completes, StartAsync ends. Control was already returned to Main when it encountered the await call to DoNetworkCommunicationsAsync, so StartAsync does not return at the end of the method, it just stops running.

That means that inside of StartAsync, all the code above the awaited method will run synchronously. At the same time, the async method runs. When
DoNetworkCommunicationsAsync completes, control returns to StartAsync and any code following StartAsync executes.


JoeMayoThis post is an excerpt from the online courseware for our C# 2012: Asynchronous Programming course written by expert Joe Mayo.

Joe Mayo is an author, independent consultant, and instructor specializing in Microsoft .NET and Windows 8 technology. He has several years of software development experience and has worked with .NET since July 2000.

How Learning HTML5 Can Help You


With the quick rise of HTML5 in the development of modern websites and applications, it’s now more important than ever to learn the ins and outs of what’s becoming the standard markup language of the World Wide Web.

With our comprehensive selection of HTML tutorial videos online, it’s easy to become an expert in the essential coding language of the modern internet. Here are some more ways in which an HTML5 tutorial can help you:

  1. Become The Most Productive Developer You Can Be – If you’re a developer, a big part of your job is staying up-to-date on all the latest trends in technology and design. A comprehensive online video tutorial will help ensure you’re at the head of your professional field.
  2. Be Prepared To Meet The Demands Of Your Clients – To always be the most productive professional in your field you need to be able to deliver on all of the varied needs of your clients. Be keeping your skills sharp and up-to-date with video tutorials for C# and HTML5, you’ll be best prepared to deliver the highest quality service to your clients.
  3. Be Ahead Of The Curve – HTML5 is becoming the defining language of the modern internet. It’s used to build websites for the traditional and modern web, as well as the latest applications. Keeping yourself current on the latest applications of HTML5 will help your professional development.

Thumbnail for 637


Thousands of developers worldwide use LearnNowOnline to gain the technical skills they need to succeed on the job and advance their career.

Creating Placeholder Images

url2As a courseware writer, I often need free images to work with, and I know website developers need them as placeholders when building sites.

Ran across this site today, which provides free images in many different categories (people, places, animals, food, abstract, and so on) in any size: http://lorempixel.com/

This will save me, and hopefully you, hours of digging to find appropriately sized images for future courseware! Wish I had known about it before now…

They even have the site set up so you can retrieve random images in a given size by including the appropriate link. I love it!

Thumbnail for 637

his post is an excerpt from the online courseware for our Windows 8 Using XAML: Bindings, Shapes, and Animation course written by expert Ken Getz.

Ken Getz is a Visual Studio expert with over 25 years of experience as a successful developer and consultant. He is a nationally recognized author and speaker, as well as a featured instructor for LearnNowOnline.

Programmers & Developers Take Note: Yahoo to End All Telecommuting Arrangements by June ’13

The programming and developing world is buzzing over news that Yahoo will significantly restrict work-from-home options for its employees. In case you missed it, Yahoo’s HR department recently sent a memo to all telecommuting employees telling them that come June 2013, they needed to return to work in the office.

Yahoo’s made this decision because they felt that employee interaction was critical to their company’s success. They argued that good ideas are generated in the office, where employees informally interact in the cafeteria, the kitchen, or around cubicles. That said, the news was greeted with derision in Silicon Valley, particularly other technology companies, who argued that the freewheeling, information culture should allow for telecommuting as well as other perks like free food, game rooms, and complementary bus service.

Ultimately, these kinds of perks make a difference for job-hunters. So if you’re an IT worker looking to expand your skill set, first we recommend any of our online options, such as C# tutorial video. And once you become an expert in this field, you’ll have luxury of shopping around for jobs. And if you really want to telecommute, you can cross Yahoo off your list.

Creating a Learning Culture



The better informed the people who work in your business are, the better they will be able to do their jobs effectively and efficiently. This is true in almost all work environments, but is especially true when it comes to IT departments, who are constantly having to master and learn new technologies before they have totally figured out an older program or system they are working with. Due to the speed at which technology moves these days, those within IT departments often find themselves overwhelmed by the amount of new information they have to digest in a short amount of time. This can often slow down the department and, in many cases, the business as a whole.

One great way to keep this from happening is to create a learning culture, one in which the employees of the IT department are interested and used to learning new programs and systems on a regular basis, as part of their jobs. Regular tutorial sessions may be one of the best ways to accomplish this. Video tutorials are incredibly effective, as employees can watch these on their own time or as a group. These can range from Visual Studio 2010 tutorial videos to video tutorials for C#. Courses can also be offered to employees free of charge as part of work requirements! In fact, tutorials are offered for just about every IT department program out there, so theoretically a business could create an easily accessible library of this information for their employees to access on their own, whenever they need it.

The information you make available and the more employees are encouraged and even paid to learn more about their trade, the stronger the learning culture will be at your company. When this is the case, everyone will be educated for just about any challenge work throws at them and will be easily able to overcome it.

Thumbnail for 637

Importance of Training Consistency for Developers & IT Professionals

One of the most challenging aspects of working with a team of talented, diverse people is the possible divergent working methods that each person can bring to the table. While diversity in itself can be a very good thing in many areas, when it comes to working on a group project, differing or conflicting development methodologies can be a problem. Miscommunication and misunderstandings can result in project delays, wasted time and missed deadlines. To put it plainly, the quality of the work suffers.

That’s where developer training can be really helpful. Consistency for your developers and IT professionals is possible through online IT training. Developer tutorials and video tutorials can be easily undertaken by an entire team, as well as new team members who might join the project later. This puts everyone on the same page and speaking the same language right out of the gate.

Working through a self-paced e-Learning program can ensure that all of your remote team members learn the same development methodologies as your in-house staff. With online developer training such as a Java tutorial, C# tutorial, Visual Basic tutorial or SQL tutorial, there’s no need to worry about an instructor in one area teaching a different method than another. Through user-friendly online classes and video tutorials, the end result of online developer training is consistency in methods from your staff. You’ll experience more streamlined development performance from all of your team members, fewer mistakes, fewer bugs, faster completion of projects and a higher quality of work overall.

Interested in learning more about online IT training? Visit www.learnnowonline.com for deep, thorough and consistent training.