Hosting ASP.NET Web Api on Windows Azure Platform

In this post I am going to show you how to Host your ASP.NET Web Api Services on Windows Azure, to demonstrate this I am going to use my application which I have created in my previous couple of Post :

CRUD operation using ASP.NET Web Api and MVC 4 – Part 1

CRUD operation using ASP.NET Web Api and MVC 4 – Part 2

Before hosting my application I have to perform the following steps.

1. Add an Cloud Application from Add New project

2. Select ASP.NET MVC 4 Web Role, since I have used ASP.NET MVC 4 to create my Web Api project.

3. From the following Screen I have to select cancel, since I am adding this to my existing Web Api Project, and don’t want to create a new Applications.

4. Once the project creation is successful, right click on the Roles folder from the WebRole.Azure project and Select Add, from the add menu select Web Role Project in solution  and Select the WebApi Project listed in the Popup windows. This will add my Web Api project in the Roles folder of Azure project as given in the screen below.

5. Now our Azure project is configured to publish my WebApi service on Web, but before we publish we need to configure the Database connection which is currently pointing to my local system, we need to change this to the SQL Azure database. Since SQL Azure setup and configuration is out of scope of this Post, so I am not going to cover that here, but don’t worry I have given that instruction in my another post, which will help you to configure the Entity Framework EDMX with SQL Azure.

6. Now once your application is ready with the SQL Azure connection, we will publish our Application on Windows Azure. But before we continue, we may have to configure publish settings in my system, sometime back I have written couple of articles where you can find how to publish your website on Windows Azure. But simplest way to do the same I have given below. Right click on your Windows Azure project and Select Publish, You will be prompted with the screen below. Now Select the option “Sign in to download credential”

7. This link will take you to the windows azure site, where you will have to provide your credentials and then you can download the publishsettings files, using the Save as option. Save this file to your system.

8. Now select the import button and locate the downloaded publishsettings file, this will auto populate the Subscription dropdown as given below, I have used my 3 months free subscription to demonstrate this. You too can subscribe to 3 months free trial from here.

9. Provide your Name and Location details.

10. Select Ok then Next, you will land up in the below screen, where you can Say next.

11. Finally you will provided with the Publish Summary settings, where you can review your settings and it everything looks good then you can click on publish.

12. You can see the progress here, or if you want you can see the same in your Windows Azure console also.

Windows Azure console.

13. Once ready you will be come to the following screen, where you can see the status as Ready.

And that’s it we are ready to test our application. You can get the DNS name (URI) of your Web Api Service from the console once your applications are ready. Lets try out Api Services, in the browser using Developer ToolBar. Following are the list of Methods and URI are exposed to web.

Lets try with the Get (/api/values) and check in Dev Toolbar, So I am getting the Responsed code 200 and in the detailed view –> Response Body I can see my result returned from my Api Service.

Now lets try from from the Client screen where I have written few lines of jQuery to demonstrate the CRUD operation, details of which you can refer in my previous post.

CRUD operation using ASP.NET Web Api and MVC 4 – Part 1

CRUD operation using ASP.NET Web Api and MVC 4 – Part 2

GetById (/api/values/1) and GetAll (/api/values)

The codes for these sample can be downloaded from here https://docs.google.com/open?id=0BzIjFd_Ps-MSUm4zMklvYkxUMXlFZzdOWVBsaHJvQQ

This is the same code which I have already provided in my previous post : CRUD operation using ASP.NET Web Api and MVC 4 – Part 2, You will just have to do the configurations which I have mentioned in this post. Hope this helps.

Read More

SQL Server Date-Time Formats

Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2011 11:29PM
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/2011
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	93.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	2011.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/93
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/2011
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2011
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-92
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-2011
DD Mon YY	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 11
DD Mon YYYY	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2011
Mon DD, YY	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 11
Mon DD, YYYY	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 2011
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM)	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2011 12:32:29:253PM
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	01-02-11
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	01-01-2011
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	2011/11/23
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	920124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	20110124
DD Mon YYYY HH:MM:SS:MMM(24h)	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2011 00:34:55:190
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	2011-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical (with milliseconds)	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	2011-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	2011-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2011 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2011 12:39:32:429AM

Some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]	92-01-24
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]	2011-01-24
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]	08/92
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	12/2011
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	92/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2011/12
Month DD, YYYY	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2011
Mon YYYY	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2011
Month YYYY	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2011
DD Month	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September
Month DD	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11
DD Month YY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 92
DD Month YYYY	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2011
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]	12/92
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	05-2011
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]	92/12
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2011-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252011
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072011
Mon-YY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02
Mon-YYYY	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2011
DD-Mon-YY	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05
DD-Mon-YYYY	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2011

Read More

Creating a Table Relationship using SQL Server 2008 Management Studio

Most of the time we used to create table relationship using SQL Query Analyzer by writing the SQL Scripts, but today I thought why not to use Management Studio, guess what it took me more then 30 min to find out how, sounds silly!

Anyways if you too get stuck somewhere in this, I am giving you the steps how to do that.

I have created a Database called AddressBook, and added few tables named, Person, Address, Phone, etc. Now I want to create the relationship between Person and Address table, where PersonId from Person table will be the FK in Address table and Phone table.

1. First obviously open the SQL Server Management Studio

2. Right click the table you want to edit and select Design.

3. Now Select the Relationship by clicking anywhere in the table design or from the menu

4. Once the Foreign Key Relationship windows open, select Add button to add a new relationship and then select the ellipse button just next to the Tables and Column Specifications button in the right side of the pane.

5. This opens the Table and Columns windows where you can create a new relationship.

This is simple, I hope this help you !!!

Read More

My Crush List of 2009

These are few things which currently I am working on and some technologies which I want to learn in coming months to master these technologies I need your help,

So please post some good tutorials and articles on any of the following topics, if you have any.

1. Silverlight 2.0/3.0

2. Microsoft .NET 2008/2010 and Framework 3.5 SP1/4.0

3. Model View Presenter and Model View Controller Architecture

4. WCF, WSSF incuding Creating custom templates for Implementation Technologies, etc

5. Visual Studio SDK

6. Visual Studio Database Projects (SQl Server 2008 and SQL Server 2005)

7. SQL Server 2008 new Features (Auditing, Change Data Capture and Change Data Tracking)

There are some links which already I am following is given below:

Automatic properties and Object initializers
http://weblogs.asp.net/scottgu/archive/2007/03/08/new-c-orcas-language-features-automatic-properties-object-initializers-and-collection-initializers.aspx

LINQ Tutorials
http://weblogs.asp.net/scottgu/archive/2006/05/14/446412.aspx

Lambda Expression
http://weblogs.asp.net/scottgu/archive/2007/04/08/new-orcas-language-feature-lambda-expressions.aspx

WSSF Home page
http://www.codeplex.com/servicefactory

WSSF Hands on Lab
http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=servicefactory&ReleaseId=7846

Thanks
Brij Mohan

Read More

SQL Server 2008 Auditing, Change Data Capture and Tracking

When I started working on SQL Server 2008, I was truly amazed with the new features of SQL Server 2008, one of that cool features is auditing in SQL Server 2008. First I thought to give here step by step Walkthrough, but I thought instead of reinventing the wheel, why don't I just give you some really good links only, which will guide you in much better way and also save some of my time :)

Please find below the links which I followed and found very useful.

Introduction and Step by Step Walkthrough
http://blogs.msdn.com/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/dd392015.aspx

But again I would like to mention that Auditing is the feature which will give you the Audit in Object, Schema and Database Level So my next challenge was to Keep track of the Database at the Field/Column level which Auditing does not provide (Correct me if I am wrong),

So I found one new features of SQL Server 2008 known as Change Data Capture/ Change Data Tracking, which is again a very cool feature,

Change data capture enables SQL Server administrators and developers to capture insert, update and delete events in a sql server table as well as the details of the event which caused data change on the relevant database table.

When you apply Change Data Capture feature on a database table, a mirror of the tracked table is createad which reflects the same column structure of the original table and additional columns that include metadata which is used to summarize what is the change in the database table row.

So enabling the Change Data Capture feature on a database table, you can track the activity on modified rows or records in the related table.

Change Data Capture (CDC) can be considered as Microsoft solution for data capture systems in SQL Server 2008 and next versions.

There were samples of data capture solutions implemented for Microsoft SQL Server 2000 and SQL Server 2005 by using after update/insert or
after delete triggers. But CDC enables SQL Server developers to build sql server data archiving without a necessity to create triggers on
tables for logging. SQL Server database administrators or programmers can also easily monitor the activity for the logged tabled.

You can find more details of how to configure and enable the Change Data Source on Tables
http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture--Part-I.htm

Difference Between Change Data Capture and Change Data Tracking
http://msdn.microsoft.com/en-us/library/cc280519.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/bb522489.aspx

I hope you will find these useful.

~Brij

Read More