Archive

Archive for October, 2014

Convert sql query result in to JSON

October 30, 2014 Leave a comment

DECLARE 
 @TableName NVARCHAR(512),
 @sql NVARCHAR(MAX),
 @xml XML;


SET @TableName = N'Customers';

SET @sql = N'SELECT @xml = CONVERT(NVARCHAR(MAX), (
 SELECT top 20 * FROM ' + @TableName + ' FOR XML path, root));';

EXEC sp_executesql @sql, N'@xml XML OUTPUT', @xml OUTPUT;

SELECT dbo.ToJSON (@xml)


USE [DB]
GO
/****** Object: UserDefinedFunction [dbo].[ToJSON] Script Date: 10/30/2014 19:24:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ToJSON] (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = '', @rowcount=COUNT(*) FROM @XMLResult.nodes('/root/*') x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
 (SELECT TheLine FROM 
 (SELECT ',
 {'+
 STUFF((SELECT ',"'+COALESCE(b.c.value('local-name(.)', 'NVARCHAR(255)'),'')+'":"'+
 REPLACE( --escape tab properly within a value
 REPLACE( --escape return properly
 REPLACE( --linefeed must be escaped
 REPLACE( --backslash too
 REPLACE(COALESCE(b.c.value('text()[1]','NVARCHAR(MAX)'),''),--forwardslash
 '\', '\\'), 
 '/', '\/'), 
 CHAR(10),'\n'), 
 CHAR(13),'\r'), 
 CHAR(09),'\t') 
 +'"' 
 FROM x.a.nodes('*') b(c) 
 FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')+'}'
 FROM @XMLResult.nodes('/root/*') x(a)
 ) JSON(theLine)
 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)' )
,1,1,'')
IF @Rowcount>1 RETURN '['+@JSONVersion+'
]'
RETURN @JSONVersion
END

Advertisements
Categories: MS SQL Tags: , , ,

Call WCF service from Excel

October 13, 2014 Leave a comment

We can call a WCF service from the excel work sheet as client.

First construct a WCF service by creating a WCF Service Library from Visual Studio 2012(am using this version of VS).

Am using the same test template provided by the Visual Studio

1

Remove all composite type stuffs from the source files Service1.cs and IService1.cs (we don’t need for now)

So the new files will look like this

A small change I made was the input parameter changed to string.

3      4

Next main thing is App.config.

You can use http or tcp as service protocol. I described both in App.config (tcp configuration is commented)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

 <system.web>
 <compilation debug="true" />
 </system.web>
 <!-- When deploying the service library project, the content of the config file must be added to the host's 
 app.config file. System.Configuration does not support config files for libraries. -->
 <system.serviceModel>
 <services>
 <service behaviorConfiguration="MyTestWcfService.Service1Behavior" name="MyTestWcfService.Service1">

 <endpoint address="" binding="basicHttpBinding" bindingConfiguration=""
 contract="MyTestWcfService.IService1">
 <identity>
 <dns value="localhost" />
 </identity>
 </endpoint>

 <endpoint address="mex" binding="mexHttpBinding" bindingConfiguration=""
 contract="IMetadataExchange" />


 <!--<endpoint address="" binding="netTcpBinding" bindingConfiguration=""
 contract="MyTestWcfService.IService1">
 <identity>
 <dns value="localhost" />
 </identity>
 </endpoint>
 
 <endpoint address="mex" binding="mexTcpBinding" bindingConfiguration=""
 contract="IMetadataExchange" />-->
 
 
 <host>
 <baseAddresses>
 <!--<add baseAddress="net.tcp://localhost:8733/Test/MyTestWcfService/Service1/" />-->
 <add baseAddress="http://localhost:8733/Test/MyTestWcfService/Service1/" />
 </baseAddresses>
 </host>
 </service>
 </services>
 <behaviors>
 <serviceBehaviors>
 <behavior name="MyTestWcfService.Service1Behavior">
 <serviceMetadata httpGetEnabled="false" />
 <serviceDebug includeExceptionDetailInFaults="false" />
 </behavior>
 </serviceBehaviors>
 </behaviors>
 </system.serviceModel>

</configuration>

Run the service

5

You have done with the service end.

Lets start the Excel and press Alt + F11. You can see something like this

6

Double click sheet1 from the left side.

From the new window. Select Worksheet from the first drop down and Select change from the second drop down.

7

Remove Worksheet_SelectionChange function definition as we don’t need in this demo.


Private Sub Worksheet_Change(ByVal x As Range)
Dim addr As String
addr = "service:mexAddress=""http://localhost:8733/Test/MyTestWcfService/Service1/mex"","
addr = addr + "address=""http://localhost:8733/Test/MyTestWcfService/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""BasicHttpBinding_IService1"", bindingNamespace=""http://tempuri.org/"""

Dim service1 As Object
Set service1 = GetObject(addr)
Dim text As String
text = Cells.Item(x.Row, x.Column)
MsgBox service1.GetData(text)

End Sub

We can get the service object as service1 by calling GetObject(addr).

The above address configuration is for http protocol. For tcp please use this address configuration (Uncomment the tcp stuffs from the service too if you are using tcp)

addr = "service:mexAddress=""net.tcp://localhost:8733/Test/MyTestWcfService/Service1/mex"","
addr = addr + "address=""net.tcp://localhost:8733/Test/MyTestWcfService/Service1/"","
addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/"","
addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace=""http://tempuri.org/"""

You are done. The service accept the value typed in a cell and show it in Message Box.

Save the document as macro enabled document (Book1.xlsm)

You can see the the output as

8

Thanks for reading.

Happy coding 🙂