Fransiscus Setiawan | EV Charging & Azure Solution Architect | Sydney

Simple sample in how to use Table Valued Parameter which is a new feature in SQL Server 2008. I found it very useful to pass bulk data from one SP to another SP

CREATE TYPE JobQueueBroker AS TABLE (JobID INT NOT NULL, UpdateDate DATETIME DEFAULT(GETDATE()))
GO

CREATE PROCEDURE [dbo].[Jobs_JobX_SubmitQueueBulk]
@Jobs JobQueueBroker READONLY
AS

BEGIN
DECLARE @Message XML

SELECT @Message = ( SELECT * FROM @Jobs
FOR XML PATH(‘Job’),
TYPE
);

— Above will fomulate valid XML message
DECLARE @Handle UNIQUEIDENTIFIER ;

— Dialog Conversation starts here
BEGIN DIALOG CONVERSATION @Handle FROM SERVICE ServiceJobXJobFinishedProcessing TO SERVICE ‘ServiceJobXJobUpdate’ ON CONTRACT [JobContract] WITH ENCRYPTION = OFF ;
SEND ON CONVERSATION @Handle MESSAGE TYPE JobDetails (@Message) ;

END
GO

DECLARE @Jobs JobQueueBroker
INSERT @Jobs VALUES (1, GETDATE())
INSERT @Jobs VALUES (2, GETDATE())
INSERT @Jobs VALUES (3, GETDATE())
EXEC dbo.[Jobs_JobX_SubmitQueueBulk] @Jobs
GO

Just in case if in your development environment has a hardcoded drive mapping path in your config file and you don’t have the drive exists in your local

The workaround is run the command below

C:\Windows\System32\SUBST D: C:\

And run SUBST after to confirm

And then you can access it in command line and mapped it in TFS will work as well BUT it won’t appear on Windows Explorer.

Shutdown your computer will remove this virtual mapping. So I recommend you to create a BATCH file and put it as startup (you can put the batch file on this location "C:\Users\{User Profile}\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup")

In SQL Server 2008, we can do a table synchronisation in one transaction of SQL Command. For example normally, we have a source table and then we have an updated records and what we would like to do is "I want to delete the record from the source table if it is not in my updated list, if there is a matched record then I’d like to update the value to the latest value, and if the record is not exists at all on the source table then I’d like to insert it"

I got a sample query with the recordset and syntax below in how to use "MERGE" to handle the case above. I got the actual sample code from (http://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/) – by Arshad Ali

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = ‘Products’)
BEGIN
DROP TABLE Products
END
GO
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
–Insert records into target table
INSERT INTO Products
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 20.00),
(3, ‘Muffin’, 30.00),
(4, ‘Biscuit’, 40.00)
GO
SELECT * FROM Products
GO
–Create source table
DECLARE @UpdatedProducts TABLE
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
–Insert records into source table
INSERT INTO @UpdatedProducts
VALUES
(1, ‘Tea’, 10.00),
(2, ‘Coffee’, 25.00),
(3, ‘Muffin’, 35.00),
(5, ‘Pizza’, 60.00)

SELECT * FROM @UpdatedProducts

MERGE Products AS TARGET
USING @UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
–When records are matched, update
–the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
–When no records are matched, insert
–the incoming records from source
–table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
–When there is a row that exists in target table and
–same record does not exist in source table
–then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT * FROM Products

GO

There are a couple of ways in versioning your API

1. Through REST URL . domain.com/v1/Customers, domain.com/v2/Customers

2. Through HTTP Header – add the version as part of Content-type

3. Add the version on the REST object itself as one of the attribute/property

A few excellent articles:

http://stackoverflow.com/questions/389169/best-practices-for-api-versioning
http://codebetter.com/howarddierking/2012/11/09/versioning-restful-services/

To me it looks like testing end to end scenario instead of modular function. A unit test might pass but it might fail or behaving differently when we start put into different context

What it is?

In software engineering, behavior-driven development (abbreviated BDD) is a software development process based on test-driven development (TDD).[Behavior-driven development combines the general techniques and principles of TDD with ideas from domain-driven design and object-oriented analysis and design to provide software developers and business analysts with shared tools and a shared process to collaborate on software development, with the aim of delivering “software that matters”.

An article to keep you guys updated with the latest in .NET world

http://weblogs.asp.net/scottgu/archive/2013/10/17/announcing-the-release-of-visual-studio-2013-and-great-improvements-to-asp-net-and-entity-framework.aspx

There are few things that are very interesting to me:

1. OWIN and KATANA (OWIN is a standard interface between .NET web servers and web application – the goal of the OWIN is to decouple server and application, encourage the development of simple modules for .NET web development and KATANA is the OWIN implementations for Microsoft Servers and frameworks)

2. One ASP.NET – now you can combine ASP.NET web form, MVC and Web API

3. Responsive Project Templates with boostrap

4. Entity Framework 6 – now it supports Async and Task<T>

5. ASP.NET MVC5 – now it supports

Authentication filters: These filters allow you to specify authentication logic per-action, per-controller or globally for all controllers.
Attribute Routing: Attribute Routing allows you to define your routes on actions or controllers.

6. Deep Focus with Code Lens

http://visualstudiomagazine.com/articles/2013/10/01/deep-focus.aspx?m=1

Great article in having multiple views within one page

http://blog.yojimbocorp.com/2012/02/14/multiple-view-models-with-knockout/

To me jQuery is javascript library that helps the developer in interacting with HTML element/DOM but what library that can be used to help in manipulating the Javascript object?I found “Underscore.js” which can be found in here (http://underscorejs.org). It provides the user to manipulate the JS object (e.g It provides function to manipulate Arrays, Add delay to your function, throttling your JS function, Check the data type of an object, etc)

A couple functions that I found useful

1. Manipulating your JSON object with “Where, filter and findWhere”

2. A few helpers to validate the data type of an object