Posts tagged with 'JSON'

My job as a developer evangelist for Couchbase means than I work much less with SQL Server than I used to. However, it doesn't mean I don't keep up with it.

In fact, inspired by Couchbase, I decided to try out using SQL Server as a sort of document data store. SQL Server 2016 introduced some interesting new features that make this kinda possible: JSON_VALUEJSON_QUERY, and JSON_MODIFY.

I set up a "document" table, which is two fields: a guid and an nvarchar(max). This is kinda like a Couchbase bucket: a key and a JSON document to go with it.

I put fairly complex hierarchical documents in these fields, something like:

I say "fairly complex", because representing this in a standard normalized fashion would require at least two tables, foreign keys, constraints, and then data migration and schema migration as the model evolves.

Now, suppose I want to execute a query and find all the document that are "Show=true". With Couchbase and N1QL, I would just use something like "SELECT * FROM `bucket` WHERE show = true".

With SQL Server, there's a little more work. "SELECT t.key, t.doc FROM [table] t WHERE JSON_VALUE(t.SpeakingInfo,'$.Show') = 'true'". Notice that JSON_VALUE is being applied to a text field, and a JSON path is used within JSON_VALUE to get a specific value from within that JSON object.

For this simple project I'm doing, that's all I need. No idea yet what kind of performance level I can expect from JSON_VALUE and more complex JSON paths.

But, this is definitely an example of the kinds of database convergence I've been telling people about. The separations between NoSQL and SQL are becoming less strict, at least in terms of data modeling and querying.

You need to be using SQL Server 2016 or SQL Server Azure to take advantage of the JSON_* functions.

I've recently had to write some ASP Classic. Don't worry, it's not like I'm writing new features, just integrating with old ones. So, since this is an ancient technology, some of this may be old news to you. But much of it was new to me, and I'm a developer with some years of ASP Classic experience.

Most of the stuff I had to do was pretty minor, but one major thing I found interesting was that I had a need to make an HTTP request in ASP classic. This turns out to be not so bad.

The trickiest part was figuring out that I had to explicitly specify the content type, since it apparently doesn't set a default.

So, yay! But also, uh oh. Now I have a response, but it's in Json. How the heck do I deal with that in ASP Classic? I was already worried about having to create an XML endpoint or finding some hacky JSON parser written in VBScript. But I took a deep breath and Googled it first. I was pleasantly shocked with what Stack Overflow told me about parsing Json in ASP. Did you know that ASP Classic is typically associated with VBScript, but actually can support other languages, like JScript and PerlScript? JScript is Microsoft's implementation of ECMAScript (commonly referred to as JavaScript), which they made available server-side via ASP long before you had a crush on Node.js. (Yeah I'm trolling a bit, but give me a break: as I write this, I've been layed up with the flu watching a House, M.D. marathon on Netflix, so I'm feeling a bit snarky).

ANYWAY

If we can run JScript and VBScript with the same ASP Classic page, then guess what, we can use Douglas Crockford's JSON library. It's so perfect and simple, and I wish I came up with it on my own.

That's about 100 times more elegant of a solution than I even expected when I started on this code.

I have a web page on domain A and, say, a Json endpoint on domain B. I would like domain A to make an Ajax request to domain B, but when I try that, I get an error message, as shown here:

Some options:

  • Make the request server-side instead of client side.
  • If you have control of the endpoint, add "Access-Control-Allow-Origin" to the response header.
  • Use jsonp (you may still need control of the endpoint if it doesn't support jsonp already).

Let's explore the jsonp option. Here's a similar request to the one above, except this time it's using jsonp.

It does not raise an error.

The way jsonp works is a little wacky, but the main thing you need to know is that you need to specify a callback function. jQuery handles this automatically on the client side. Here's how I handle it on the server side with ASP.NET MVC using the Mvc.Jsonp package.

Notice three things:

  1. SomeController inherits from JsonpControllerBase
  2. GetAllItems return type is JsonpResult and has a "callback" string parameter
  3. The Jsonp function from the base class and how callback is passed to it.

The benefit is that you can now do cross-domain scripting. So far the drawbacks seems to be that:

  • It's limited to GET requests
  • Error handling may take some extra work
  • If there is no Jsonp endpoint, then you either have to make one or you're out of luck.

I'm giving it a shot on Ledger, and seems to be working fine so far.

I've been working a little bit here and there on a mobile app. One thing this app does a lot is use a RESTful API over HTTP, where the results are JSON.

I'm using RestSharp to handle these requests, and RestSharp can automatically serialize Json results into C# object(s), assuming I have C# objects that match.

Well, some of these JSON results can be a little complex. Not terribly complex, mind you, but enough to where it's annoying to create the classes myself. But I thought to myself, maybe there's a tool out there that already does this for me. I googled around and found json2csharp, which is a handy web app that does exactly that.

Well, thanks to Rob Gibbens, I now know that this feature is actually built-in to Visual Studio 2013, and available for 2012 as part of "ASP.NET and Web Tools".

Paste JSON As Classes in Visual Studio

You can see that there are some differences, but these tools get you most of the way there!

Matthew D. Groves

About the Author

Matthew D. Groves lives in Central Ohio. He works remotely, loves to code, and is a Microsoft MVP.

Latest Comments

Twitter