Showing posts with label Json. Show all posts
Showing posts with label Json. Show all posts

Thursday, 12 June 2014

Export JSON from SQL Server


create procedure [dbo].[GetJSON] (
@schema_name varchar(50),
@table_name varchar(50),
@registries_per_request smallint = null
)
as
begin
if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and table_name = @table_name ) > 0 )
begin
declare @json varchar(max),
@line varchar(max),
@columns varchar(max),
@sql nvarchar(max),
@columnNavigator varchar(50),
@counter tinyint,
@size varchar(10)

if (@registries_per_request is null)
begin
set @size = ''
end
else
begin
set @size = 'top ' + convert(varchar, @registries_per_request)
end
set @columns = '{'

declare schemaCursor cursor for
select column_name
from information_schema.columns
where table_schema = @schema_name
and table_name = @table_name
open schemaCursor

fetch next from schemaCursor into @columnNavigator

select @counter = count(*)
from information_schema.columns
where table_schema = @schema_name
and table_name = @table_name

while @@fetch_status = 0
begin
set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar(max), ' + @columnNavigator + ') + '''''''
set @counter = @counter - 1
if ( 0 != @counter )
begin
set @columns = @columns + ','
end

fetch next from schemaCursor into @columnNavigator
end

set @columns = @columns + '}'

close schemaCursor
deallocate schemaCursor

set @json = '['

set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from [' + @schema_name + '].[' + @table_name + ']'
print @sql
exec sp_sqlexec @sql

select @counter = count(*) from tmpJsonTable

declare tmpCur cursor for
select * from tmpJsonTable
open tmpCur

fetch next from tmpCur into @line

while @@fetch_status = 0
begin
set @counter = @counter - 1
set @json = @json + @line
if ( 0 != @counter )
begin
set @json = @json + ','
end

fetch next from tmpCur into @line
end

set @json = @json + ']'

close tmpCur
deallocate tmpCur
drop table tmpJsonTable

select @json as json
end
end

This will do the trick for small json payloads

Tuesday, 1 April 2014

Firebase: Store and sync data in real time

Check out this scalable, easy to use, web data storage for real time apps.

Example:

Firebase along with loads of cool demo apps and quickstart tutorials can be found here:  http://firebase.com

There’s also a web UI from administering your web db.

Try this out for yourself by spinning up a couple of browsers:  http://stevenhollidge.com/blog-source-code/firebase/index.html

Bear in mind the data is shared so whatever you type will appear above, be nice :)

Source:

<html>
<head>
<script src='https://cdn.firebase.com/v0/firebase.js'></script>
<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js'></script>
</head>
<body>
<div id='messagesDiv'></div>
<input type='text' id='nameInput' placeholder='Name'>
<input type='text' id='messageInput' placeholder='Message'>
<script>
var myDataRef = new Firebase('https://d5wez3l3q02.firebaseio-demo.com/');
$('#messageInput').keypress(function (e) {
if (e.keyCode == 13) {
var name = $('#nameInput').val();
var text = $('#messageInput').val();
myDataRef.push({name: name, text: text});
$('#messageInput').val('');
}
});
myDataRef.on('child_added', function(snapshot) {
var message = snapshot.val();
displayChatMessage(message.name, message.text);
});
function displayChatMessage(name, text) {
$('<div/>').text(text).prepend($('<em/>').text(name+': ')).appendTo($('#messagesDiv'));
$('#messagesDiv')[0].scrollTop = $('#messagesDiv')[0].scrollHeight;
};
</script>
</body>
</html>

It’s written in Scala and they take care of the eventual consistency and scalability issues which leaves the developer to focus on the business problem.  There’s also lots of bindings available including Angular and Ember. 


Enjoy!

Saturday, 7 December 2013

Using TPL to avoid callback hell

In this example, we fire off three service calls which individually have a continuation of converting the resultant DTO to a model object.

We then call a fourth service, to get the Spot which only fires once all the previous three service calls have completed and created their model object.

var tasks = new Task[]
{
services.GetAsTask<DTOs.MoneyMarketRate>()
.ContinueWith(t => MoneyMarketRate = Mapper.Map<DTOs.MoneyMarketRate, MoneyMarketRate>(t.Result)),

services.GetAsTask<DTOs.InvestmentBoundaries>()
.ContinueWith(t => InvestmentBoundaries = Mapper.Map<DTOs.InvestmentBoundaries, InvestmentBoundaries>(t.Result)),

services.GetAsTask<DTOs.TradingDate>()
.ContinueWith(t => TradingDate = Mapper.Map<DTOs.TradingDate, TradingDate>(t.Result)),
};

Task.Factory.ContinueWhenAll(
tasks,
ts => services.GetAsTask<DTOs.Spot>()
.ContinueWith(t => Spot = Mapper.Map<DTOs.Spot, Spot>(t.Result)));
You can take a look at my Silverlight source project here:  https://github.com/stevenh77/UsingTasksToAvoidCallbackHell
The problem with this code is that the exceptions are not properly handled.  For that we can leverage async and await:
try 
{
var tasks = new []
{
services.GetAsync<DTOs.MoneyMarketRate, MoneyMarketRate>(x => MoneyMarketRate = x),
services.GetAsync<DTOs.InvestmentBoundaries, InvestmentBoundaries>(x => InvestmentBoundaries = x),
services.GetAsync<DTOs.TradingDate, TradingDate>(x => TradingDate = x),
};

await TaskEx.WhenAll(tasks);
await services.GetAsync<DTOs.Spot, Spot>(x => Spot = x);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}

I also refactored the service executor to use async and await and move the setting to the property to an action that can be passed in.
public class ServiceExecutor
{
const string BaseAddress = "http://localhost:8080/Services/";

public async Task GetAsync<TDto, TModel>(Action<TModel> actionToSetPropertyValue)
{
var client = new WebClient();
var serviceName = typeof(TDto).Name + ".ashx";
var response = await client.DownloadStringTaskAsync(new Uri(BaseAddress + serviceName, UriKind.Absolute));
var dto = JsonConvert.DeserializeObject<TDto>(response);
actionToSetPropertyValue.Invoke(Mapper.Map<TDto, TModel>(dto));
}
}

Refactored source:  https://github.com/stevenh77/UsingTasksToAvoidCallbackHellWithAsyncAndWait/

Saturday, 6 July 2013

C# client for Server Side Events (EventSource)

Examples of one way streaming from server to client can be found on the web but it’s pretty much always JavaScript clients.  Here is a working example using the .NET stack, with WebAPI as the server and a C# console application as the client.

Download and run this WebAPI chat application which emits Server Side Events:

https://github.com/filipw/html5-push-asp.net-web-api/

I then open Visual Studio running as admin, update the code to use IIS with my machine name (Zeus) and a virtual directory, clicking the create virtual directory button, so I can track requests using Fiddler:

image

Then update the JavaScript within the app to use the same path:

image

The create a Console application, using NUGET add Json.NET and paste this code in:

using System;
using System.IO;
using System.Net;
using System.Text;
using Newtonsoft.Json;

namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
new WebClientWrapper();
Console.ReadKey();
}
}

public class WebClientWrapper
{
WebClient wc { get; set; }

public WebClientWrapper()
{
InitialiseWebClient();
}

// When SSE (Server side event) occurs this fires
private void OnOpenReadCompleted(object sender, OpenReadCompletedEventArgs args)
{
using (var streamReader = new StreamReader(args.Result, Encoding.UTF8))
{
var cometPayload = streamReader.ReadLine();
var jsonPayload = cometPayload.Substring(5);
var message = JsonConvert.DeserializeObject<Message>(jsonPayload);
Console.WriteLine("Message received: {0} {1} {2}", message.dt, message.username, message.text);
InitialiseWebClient();
}
}

private void InitialiseWebClient()
{
wc = new WebClient();
wc.OpenReadAsync(new Uri("http://zeus/chatapp/api/chat/"));
wc.OpenReadCompleted += OnOpenReadCompleted;
}
}

public class Message
{
public string username { get; set; }
public string text { get; set; }
public string dt { get; set; }
}
}


You’ll need to update the code for your machine name.


Now run Fiddler, the WebAPI project and the console app and add a message in the chat window:


image


In Fiddler, select the request your console just made and select Raw, you’ll see nothing.  Now right click and select COMETPeek and you’ll see the payload that was streamed.


image

Tuesday, 12 March 2013

Fiddler Extension: JsonpViewer

Here’s an example of a Fiddler Extension I started building for CORS workarounds using JsonP.

https://github.com/stevenh77/JsonpViewer

Some background, I’m currently using Jsonp for cross domain service calls.  As only GETS are allowed, and I want to pass data to my services as Json, I add a request query parameter which is URI encoded.  In order to debug and cleanly read the object I’ve added a inspector to Fiddler to extract this object, decode it and display within fiddler UI.

I’ve also wrote this blog post to remind me how to create an extension next time.

image

Here we can see a Json object being passed via a URI encoded HTTP GET request:

GET http://localhost:2626/api/dealdates?request=%7B%22strikeAbsolute%22%3A0.0%2C%22strikePips%22%3A1000.0%2C%22markupAbsolute%22%3A0.0%2C%22markupPercentage%22%3A0.0%2C%22clientPriceAbsolute%22%3A5.0%2C%22clientPricePercentage%22%3A0.1%2C%22currencyPair%22%3Anull%2C%22spotAsk%22%3A0.0%2C%22notional%22%3A0.0%2C%22expiry%22%3A%220001-01-01T00%3A00%3A00%22%7D HTTP/1.1
User-Agent: Fiddler
Host: localhost:2626

Click over into my new “Json Query Params” window and you get the Json object nicely formatted and display:

image

Notes:

This extension currently relies upon the JSON object being passed as “?request=” as this fit my pattern and needs.

The example above uses the same json for both the request and response, as I was too lazy to create another object and it’s late now so almost time for bed…

If you’re interested in building your own extension here’s some tips:

1. Grab my source code as a reference:  https://github.com/stevenh77/JsonpViewer

2. Switch on logging within Fiddler:

Alt + Q opens the ExecAction command line (lower left hand corner, the black bar)

Enter the following commands:

prefs set fiddler.debug.extensions.verbose True

prefs set fiddler.debug.extensions.showerrors True

image

To confirm your settings have been updated enter this command:    about:config

image

3. Now when you open Fiddler you can select the log tab to see your extension loaded.

Remember:  When deploying your class library, the DLLs for Inspectors go in the {program files}\Fiddler2\Inspectors folder

image

4. If your DLL isn’t loading remember to add a Fiddler.RequiredVersion attribute to the AssemblyInfo.cs file.

image

6.  Build your project as .NET Framework 2 or 4.  I dont think Framework 4.5 is not supported yet but I havent tested that.  My project was built with .NET 2.0 and later upgraded to .NET 4.0.  Both worked great the my version of Fiddler (4.4.3.0)

image

Good luck!

Saturday, 2 March 2013

Calling cross domain without Jsonp

Thanks to Simon Cropp @simoncropp (creator of NotifyPropertyWeaver project amongst other great projects) for pointing out that you shouldn’t need to jump through the hoops of using Jsonp.

http://enable-cors.org

http://www.html5rocks.com/en/tutorials/cors/

http://stevenhollidge.com/blog-source-code/cors   (using Javascript XMLHttpRequest rather than jQuery)

My example was taken from html5rocks website, then stripped back to contain only the cors code

Use this page to test CORS requests:  http://client.cors-api.appspot.com/client

Initially, I had been using jQuery to make my requests but their lack of support for cors with IE had led me down the wrong path:  http://bugs.jquery.com/ticket/8283

To enable CORS on your ASP.NET web server, for example for Web API, you need to add the following to your web.config:

<system.webServer>

<!-- enable cors -->
<httpProtocol>
<customHeaders>
<add name="Access-Control-Allow-Origin" value="*" />
<add name="Access-Control-Allow-Headers" value="Content-Type" />
</customHeaders>
</httpProtocol>
</system.webServer>

Tuesday, 3 April 2012

WPF Metro Shell

Here is an example of a WPF application with pluggable components for core business functionality.

The solution features the following frameworks:

MVMMLight, MahApps.Metro, Ninject, ServiceLocator, Newtonsoft.Json, Rx Extensions

 

Projects

The main projects prefixed MetroWpf have a core shell window, login page and menu system.

MetroWpf-projects

Login

image

 

image

NOTE:

This is very much a work in progress but as I keep getting side tracked on other projects I thought I’d release what I have so far.

Source code:  http://stevenhollidge.com/blog-source-code/MetroWpf-CTP-v0.1.zip

HG repo:  http://hg.assembla.com/silverbladetech/file/4b8b38d17d24/MetroWpf

Thursday, 30 June 2011

How to create a Google Chrome extension

This simple example serves to show how you can create your own bespoke Goggle Chrome extension using HTML, Javascript and JSON.

With Extension Installed

When our extension is installed it displays an icon to the left of the favourites star in the URL box (our icon in this case is a blue square with 3D style glasses) and shows a JavaScript alert to the user if the tab is browsing my website http://stevenhollidge.com.

ScreenShot043

There are two types of extensions:

  Browser Extension (notice the icons to the right of the URL box, these browser extensions run all the time)

  Page Action (which is what this example happens to be)

The source code consists of a JSON manifest file, an HTML file containing some JavaScript and three icons:

ScreenShot045

Step 1: Download and Install Google Chrome for Developers

Step 2: Create manifest.json file

Create an empty folder then add a file called manifest.json, here’s my version:

Step 3: Add background.html file

Step 4: Add your icon files

Step 5: Upload your extension to Chrome

Open Chrome and navigate to Chrome://extensions, then click the “Load unpacked extension…” button and select the folder containing your extension files.

ScreenShot042

Debugging your extension

You can debug your Chrome extension by navigating to chrome://extensions (see screenshot above) and click the background.html link below Inspect active views which will open Google Developer Tools where you can place breakpoints to step through your code:

ScreenShot048

You can download the source code here:

http://stevenhollidge.com/blog-source-code/ChromeExtension-CheckForMyWebsite.zip

Thursday, 16 June 2011

Agile RESTful Json in Silverlight

Following on from my previous post (Consuming POX in Silverlight), here’s a simple example of consuming a Json data service.

ScreenShot012

Data file (exposed via Http Handler on Web Server)

[
{
name:'Shredded Wheat',
price:4.95,
description:'Two per serving with skimmed milk',
calories:650
},
{
name:'Fresh Fruit Salad',
price:5.95,
description:'Strawberries, bananas, apples and pears',
calories:400
},
{
name:'Scrambled Eggs on Toast',
price:3.95,
description:'Two eggs on two slices of wholewheat toast',
calories:300
},
{
name:'Bacon Roll',
price:2.5,
description:'Three slices of lean bacon in a granary roll',
calories:600
},
{
name:'Homestyle Breakfast',
price:12.95,
description:'Two eggs, bacon, sausage, toast and orange juice',
calories:950
}
]

Http Handler (on Web Server)

using System.IO;
using System.Web;

namespace AgileREST.Web
{
public class FoodService : IHttpHandler
{

public void ProcessRequest(HttpContext context)
{
var dataPath = HttpContext.Current.Server.MapPath("Food.json");

using (var reader = new StreamReader(dataPath))
{
var result = reader.ReadToEnd();
context.Response.ContentType = "text/json";
context.Response.Write(result);
}
}

public bool IsReusable
{
get
{
return false;
}
}
}
}

Mapping (on the client)

namespace AgileREST
{
public class Food
{
public string Name { get; set; }
public decimal Price { get; set; }
public string Description { get; set; }
public int Calories { get; set; }
}
}

Service Call (on the client)

using System;
using System.Collections.Generic;
using System.Json;
using System.Linq;
using System.Net;
using System.Runtime.Serialization.Json;
using System.Windows;
using System.Windows.Browser;
using System.Windows.Controls;

namespace AgileREST
{
public partial class MainPage : UserControl
{
public MainPage()
{
InitializeComponent();
}

void webClientForJson_OpenReadCompleted(object sender, OpenReadCompletedEventArgs e)
{
if (e.Error != null) return;

// first option
JsonArray foods = (JsonArray)JsonArray.Load(e.Result);
var query = from food in foods
select new Food()
{
Name = (string)food["name"],
Price = (decimal)food["price"],
Description = (string)food["description"],
Calories = (int)food["calories"]
};
listboxFoodJson.ItemsSource = query.ToList();

// second option
// for implicit serialisation requires identical case sensitive name across Json and C# food objects)
//DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(List<Food>));
//List<Food> foods = (List<Food>) jsonSerializer.ReadObject(e.Result);
//listboxFoodJson.ItemsSource = foods;
}

private void btnGetFoodJson_Click(object sender, RoutedEventArgs e)
{
WebClient webClientForXml = new WebClient();
webClientForXml.OpenReadCompleted += webClientForJson_OpenReadCompleted;
webClientForXml.OpenReadAsync(new Uri(HtmlPage.Document.DocumentUri, "FoodService.ashx"));
}
}
}

Data binding (on the client)

<UserControl x:Class="AgileREST.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d"
d:DesignHeight="300" d:DesignWidth="700">

<UserControl.Resources>
<DataTemplate x:Key="itemTemplate">
<StackPanel Orientation="Horizontal">
<TextBlock x:Name="tbName"
Width="170"
Text="{Binding Path=Name}" />
<TextBlock x:Name="tbPrice"
Width="50"
Text="{Binding Path=Price}" />
<TextBlock x:Name="tbDescription"
Width="300"
Text="{Binding Path=Description}" />
<TextBlock x:Name="tbCalories"
Width="50"
Text="{Binding Path=Calories}" />
</StackPanel>
</DataTemplate>
</UserControl.Resources>

<Grid x:Name="LayoutRoot" Background="White">
<StackPanel Width="600" Margin="10">
<TextBlock Text="Json Example" Margin="5" />
<StackPanel x:Name="ColumnDescriptions" Orientation="Horizontal" Margin="5" >
<TextBlock Text="Name" Width="170" />
<TextBlock Text="Price" Width="50" />
<TextBlock Text="Description" Width="300" />
<TextBlock Text="Calories" Width="50" />
</StackPanel>
<ListBox x:Name="listboxFoodJson"
Height="150"
Margin="5"
ItemTemplate="{StaticResource itemTemplate}" />
<Button x:Name="btnGetFoodJson"
Height="50"
Margin="5"
Click="btnGetFoodJson_Click"
Content="Get Food From Json Service" />
</StackPanel>
</Grid>
</UserControl>

You can download the source code from the following link:


http://stevenhollidge.com/blog-source-code/AgileREST.zip