Converting DataTables to JSON

When I started creating Web Services, I was using ADO.NET DataSets to retrieve data and then sending that data to my consumers using XML. Those Web Services are still there, but my consumers now want JSON.

The good news is that I don't have to rewrite my code to return the data in the right format. While I could switch to using SQL Server's new ability to convert query results into JSON, the existing code has that whole "working" feature that people like so much -- I have no desire to replace it.

The people who created NewtonSoft.JSON saw this problem coming and provided a solution for converting DataSet tables into JSON. First, you need to extract the DataTable holding your rows from your DataSet:

Dim dt As DataTable
dt = MyDataSet.Tables("Customers")

Then create a JsonServializer:

Dim js As JsonSerializer
js = JsonSerializer.CreateDefault

At this point you could set properties on the JsonSerializer to control how your JSON will be generated.

Next, pass your DataTable and Serializer to the FromObject method on NewtonSoft's JArray class. The FromObject method will convert all the rows in your DataTable into an array of JToken objects, held in a JArray object:

Dim rows As JArray
rows = JArray.FromObject(dt, js)

Now you can send the whole collection to the consumer:

Return rows.ToString()

Alternatively, you use LINQ to pull out the rows you want. This gets the first row, for example, and sends it to the consumer:

Dim row As JToken
row = rows.FirstOrDefault
Return row.ToString

Posted by Peter Vogel on 05/29/2018 at 11:03 AM0 comments


What the Colors for Changed Lines in Visual Studio Mean

If you have change tracking turned on in Visual Studio, then you'll be getting highlights in the right-hand margin of your editor window flagging the condition of lines in the current file. If you're not getting those lines and would like to, then go to Tools | Options | Text Editor and check the Track Changes option.

Here's your quick reference to the colors and icons in the editor window's right-hand margin:

  • Yellow: The line has been changed but not yet saved
  • Green: The line has been changed and saved
  • Orange: The line has been changed, saved, and the change undone
  • Little square dots in the middle of the margin: Break points
  • Little square dot on the right side of the margin: Syntax error
  • Gray block: The portion of the file that's currently being displayed
  • Solid blue line: The current position of the cursor

Posted by Peter Vogel on 05/22/2018 at 1:17 PM0 comments


Sealing Methods and Properties

In general, it's considered rude to seal classes because it prevents other developers from extending the class through inheritance. However, when you declare a base class it's considered perfectly acceptable to mark some classes as overridable/virtual ... and to leave some methods unmarked. Those methods left unmarked can not be overridden by derived classes that inherit from the base class. Essentially, the base class developer is saying that these methods are essential to the nature of the class and modifying those methods (or properties, for that matter) would distort the class.

But what about the derived class? It's not hard to imagine a derived class that overrides a method in a way that is essential to the nature of the derived class. Sealing the derived class to prevent a new class inheriting from it would be considered rude. However, like the developer of the original base class, the developer of the derived class should be allowed to say that some changes are not allowed.

This is role of the NotOverridale/sealed keywords: They allow a developer to mark an overridable method (or property) as no longer overridable. As an example, here's a CreditApproval method that's overridden a method in the base class but has been marked to prevent any further modifications. First, in Visual Basic:

Public NotOverridable Overrides Function CreditApproval() As Boolean

End Function  

Now, in C#:

public override sealed bool CreditApproval()
{

}  

Posted by Peter Vogel on 04/26/2018 at 5:03 AM0 comments


Don't Use Enumerables as Numbers

I just read another discussion of Enums in .NET where the author was all excited about the fact that (under the hood) a named, enumerable value is actually stored as a number. There are ways, in both Visual Basic and C#, to use those numeric values.

I'm not going to show you how to do that because it's wrong, wrong, wrong. The point of using enumerated values is to get away from embedding magic numbers in your code and, instead, replace those values with meaningful names. Accessing the numeric value (a textbook example of an "implementation detail") violates the purpose of setting up an enumerated value in the first place.

More importantly, using those numeric values is just an accident looking for a place to happen because those numeric values are assigned positionally. If you're using those values in some "clever" way (sarcasm intended) then your code will break if someone inserts a new value into your Enum. At that point, every subsequent enumerated value gets assigned a new numeric value.

I do make one exception: If I want to be able to add two named values together to get a new value, then I use bit flags. But bit flags work by explicitly assigning every enumerated value a numeric value (no positional assignments) and then using the enumerated names without referring to the underlying numeric values. That's restrictive enough that I don't feel I'm violating my principles when I take advantage of it.

Posted by Peter Vogel on 04/23/2018 at 3:07 PM0 comments


HTTP Protocol: Headers vs. Body

As part of putting together a request to a Web Service, I'm perfectly willing to modify the headers in the request to carry some data rather than put that data in the body of the request. There is a risk here because some proxy servers will strip out any headers they don't recognize. However, in an SSL request, headers are encrypted and, as a result, not visible to proxy services. To ensure that my custom headers aren't stripped out I only use this technique where all requests are traveling over SSL.

My rule for deciding whether data should go into the header vs. the body is driven by the way the data is being used. If this is information that's independent of the request (that is, something used in a variety of requests) and is used to control the processing of the request, then I'm more likely to put the data in the request header. Security-related information is a good example.

But I also recognize that adding custom headers also reduces interoperability. I obviously can't include a custom header of my own when sending a request to someone else's service. Even when designing a request to be sent to my own service, I have to recognize that there are toolsets that make it difficult/impossible to alter headers (at least, I'm told that such toolsets exist). If I do create a header, I need to make it clear what will happen to clients that don't provide that header.

Posted by Peter Vogel on 04/16/2018 at 9:03 AM0 comments


Object Browser: The World's Worst-Named Tool

So, you know the class you need but you don't know what class library it's in. How do you add the right reference to your project? Object Browser will let you do it in two steps.

You can do that because "Object Browser" is patently misnamed -- to begin with, it displays classes, not objects. Just as obviously, it isn't just limited to classes (objects) but also displays namespaces, enums, structs, interfaces, and class members (e.g. events, properties, etc.).

If you know what class (or interface or enum or, even, member) you want, you can search for it in Object Browser using the search box at the top of Object Browser's window. Once you find what you're looking for, just click on the Add to References icon at the top of Object Browser to add a reference to the relevant library to whatever project you have selected in Solution Explorer.

So, it isn't just a browser, either.

Worst. Name. Ever.

Posted by Peter Vogel on 04/05/2018 at 5:18 AM0 comments


Inheriting from Generics: Set the Datatype ... Or Not

When you're creating a derived class and your base type is a generic class, you have two choices in implementing your derived class: You can set the type of your derived class or you can make your derived class another generic class.

For example, imagine that you have a class called ReadRepository that accepts a variety of types:

Public Class ReadRepository(Of T)

End Class

If you create a CustomerRepository that inherits from ReadRepository, you might choose to set the type of your base class:

Public Class CustomerRepository 
    Inherits ReadRepository(Of Customer)
End Class

That's the strategy to follow when your derived class adds functionality specific to a datatype (in this case, I'm adding functionality specific to the Customer class).

On the other hand, if you wanted to create an UpdateAndReadRepository, you might choose to have your new class also be a generic class. In that case, your derived class also accepts a type placeholder and passes that placeholder to the base class:

Public Class UpdateAndReadRepository(Of T) 
    Inherits ReadRepository(Of T)
End Class

This is the strategy to follow if you're extending the base class with functionality that can be used with a variety of classes. In this case, adding Update capabilities for any class.

Posted by Peter Vogel on 04/02/2018 at 5:44 AM0 comments


Collecting Collections: The Lookup Collection

Every once in a while, I end up with a bunch of collections in memory and need the ability to pick the collection I want by name. For example, I might need a bunch of Customer collections, one for each city where I have a customer.

Furthermore, I'd like to have those collections organized into a larger collection called CityCusts so I can pull out all of the Customers for any specific city.

The code I want to use to retrieve the Customers collection for a city would look like this:

Dim lstCusts As List(Of Customer)
lstCusts = CityCusts("Regina")

I could build that CityCusts collection myself by defining a Dictionary collection that holds values that are, themselves, a collection of Customers:

Private CityCusts As Dictionary(Of String, List(Of Customer))

Initializing the individual collections for each Dictionary entry and adding the right customer to the right City collection would be a pain, though. Even using LINQ's Group By syntax is awkward, in both languages.

Fortunately, the Lookup collection makes building that collection a snap. To declare a Lookup class you just specify the type of the Dictionary's key (pretty much always a string) and the type held in the collection. Here's the declaration to create a Lookup collection that holds a collection of Customers associated with a city:

Private CityCusts As Lookup(Of String, Customer)

To load the class, I just start with a collection of Customer objects and call the collection's ToLookup method. I must pass the method a lambda expression that specifies which property to use to organize the Customers collection.

This example creates collections of Customer objects that share the same value in their City property and then stores those collections in CityCusts, under the name of the shared city:

CityCusts = Custs.ToLookup(Function(c) c.City)

In C#, the two lines of code to declare and load the collection would look like this:

private Lookup<string, Customer> CityCusts;
CityCusts = Custs.ToLookup(c => c.City);

Bada bing (as they say), bada boom.

Posted by Peter Vogel on 03/19/2018 at 5:51 AM0 comments


Finding What's Changed in Your Code

You (or someone else) rolled out a new version of an application and it's behaving…oddly. You ask the people involved (or yourself) "What changed?" and the answer you get is "Nothing." This is, of course, not true. Users do this to us all the time though the question we ask is slightly different ("What did you do differently?", "Nothing").

Of course, the answer we're really giving is "Nothing relevant," which isn't quite the same thing as "Nothing." In both cases, the person asking the question has unconsciously edited out of recollection anything that doesn't seem relevant to the problem. The only solution is to force ourselves to look at everything that changed, rather than just "what's relevant" (it's like looking for something you've lost: Once you've looked in all the places where it should be then you have to look at all the places where it shouldn't be).

Your source control system should be able to tell you all the lines of code that have changed between one version of your code and another. If you're not using a source control system, you can use Visual Studio's Compare Files option, though it's hard to get to in more recent versions of Visual Studio. To compare two files without source control, first click in the Command window (if the window isn't open, use View | Other Windows | Command Window to open it). In the Command window, type this:

Tools.DiffFiles <filename1> <fileName2>

That command isn't as bad as it looks: As soon as you start typing a file name, you'll get IntelliSense support to finish off the file's name. After you hit the Enter key, the command will open a tab with two panes showing both files with your changed/added/deleted lines highlighted.

Of course, even when you find the changed lines you have to stop thinking "Oh, well that can't be the problem." Yes, it can. In fact, it probably is.

Posted by Peter Vogel on 03/15/2018 at 2:16 PM0 comments


Converting Your ASP.NET Web Forms Application to ASP.NET MVC

If you want to build a Web application quickly, do it with ASP.NET Web Forms. However, you have to be willing to give up a lot: client-side coding and Ajax is more awkward in Web Forms than MVC, you won't have as complete control over your HTML/CSS as in MVC, and you'll have to be careful about what code goes into your code-behind file if you want to do automated testing.

Because those are areas that people do care about, the future of Web development in .NET belongs to ASP.NET MVC (it's telling that there is no "MVC" in .NET Core -- the Web application framework is just called "ASP.NET Core").

I made a good living out of Web Forms, but, except for the occasional fix to legacy applications, my clients all want me to work in ASP.NET MVC now. That being the case, I occasionally get asked "How do I migrate from Web Forms to MVC?" Here's what I tell my clients (and I won't charge you a thing):

  1. Don't. There is no migration path and your Web Forms application has that whole "working" feature that users like.
  2. If it's just a need to add client-side support or to look up-to-date, consider replacing the default Microsoft Web Form controls with controls from third-party providers. A lot of those third-party controls offer features that the default Microsoft Web Form controls don't (rich client-side models, for example). They are also often "plug-compatible" with your current controls so you can just replace your existing Microsoft control with a third-party control and start doing cool stuff.
  3. If you must convert (because you don't want to support two toolsets or there's something you want your application to do that Web Forms doesn't support or your commercial application looks old fashioned or management has mandated it or ...), remember that, since Visual Studio 2012, you can combine MVC and Web Forms in the same project. Do the conversion form by form rather than creating a whole new site. There will be some forms (those forms maintaining your lookup tables, for example) that you may never bother converting.
  4. Where you need to rewrite an existing Web Form to ASP.NET MVC, remember that you wrote that form the way you did because you wrote it in Web Forms. If you'd written it in MVC, you'd have done it differently. Do it that "differently way" now.
  5. Do create all your new pages in ASP.NET MVC even in existing Web Form projects (no sense in making the problem larger).

Posted by Peter Vogel on 03/12/2018 at 8:45 AM0 comments


Brush Up Your SQL

Thanks to LINQ and Entity Framework, I don't write a lot of online, transactional SQL any more (I like to think that, thanks to Entity Framework, I have Microsoft's ADO.NET team doing that for me). But there are features in SQL that just aren't available in the current version of LINQ (see my columns on SQL Server 2016's support for JSON and temporal tables for examples).

But it isn't just new features that may drive you to issuing raw SQL through Entity Framework, though.

For example, I had someone attending one of the SQL Server querying classes I teach with a problem that LINQ couldn't solve efficiently (I also teach the 2014 course, though it doesn't really matter because they're exactly the same course ... though, as I remember, the 2016 certification exam was easier).

His problem was that he was receiving a constant stream of reports from various vehicles and needed, from that received data to determine when the vehicle had stopped to refuel. The only way to answer this question is to compare the amount of gas in the tank at (time A) with the amount in the tank at (time A – 5 minutes).

In LINQ, this could be solved by joining every report row with all the report row from the same vehicle more than 5 minutes ago ... but that query had a response time that could be measured with a calendar, not with a stop watch. However, a raw SQL query that used the Preceding keyword (not available in LINQ), gave a very snappy response.

I love LINQ. I love Entity Framework. You're still going to need SQL.

Posted by Peter Vogel on 03/05/2018 at 7:20 AM0 comments


Avoiding Entity Framework Slowdown

Generally speaking, I don't worry much about tweaking my LINQ queries when working with Entity Framework (this is also true when I'm working with SQL directly, by the way). I'm always telling my clients that if they want to speed up their data access they should look at their database design and, especially, how they're using indexes.

There is one exception to that rule, though: If you've got a query doing a comparison to a char or varchar column, then you may be unnecessarily slowing down your LINQ queries. The problem is that Entity Framework assumes that the database equivalent to your string property is a Unicode (nchar or nvarchar) column. If that's not the case -- if your columns are char or varchar -- then you're incurring some data conversion overhead when you use those properties in a Where clause like this:

Dim res = From cust In db.Customers
 	   Where cust.Name = "Vogel"
	   Select cust

That Where clause is going to get converted into a SQL statement that looks like this:

Select *
From Customers
Where Name = N'Vogel'

Because Entity Framework assumes my column is a Unicode column, it slaps the N in front of my string constant ('Vogel') to turn it into a Unicode literal ... and then tries to compare that Unicode literal to my non-Unicode (char or varchar) Name column. SQL Server then has to do the work to convert 'Vogel' back out of Unicode (or it may be converting my Name column to Unicode, either of which is bad).

The solution is to tell Entity Framework that your column is char or varchar by decorating the property with the Column attribute and setting the attribute's TypeName property. This example tells Entity Framework that my Name column is varchar:

<Column(TypeName="varchar")>
Public Property Name As String

For a longer discussion, see Brian Sullivan's blog post on how he got burned. And stop using char and varchar!

Posted by Peter Vogel on 02/23/2018 at 8:59 AM0 comments


Most   Popular
Upcoming Events

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.