Creating a Website on Azure is super simple

With the latest azure tools (SDK 2.0) deploying a website on windows azure is easier than ever. With just few clicks and matter of seconds the changes will be reflected on cloud. Even the configuration made simple to use. Below are steps to follow.

Prerequisites:

A valid windows azure account (3 months free trial period works as well).

Visual Studio 2012

Windows Azure SDK 2.0 (can be installed easily with Web Platform Installer)

Step 1: Create a sample website using Visual Studio

Step 2: Login into Azure account and create a website by navigating to New->Compute->Website->Quick Create option

Step 3: Within few seconds site will be created. Just click on the site name and download the publish profile file as below.

dnldProfile

Step 4: Come back to Visual Studio and click on Publish option. Import the above downloaded profile by navigating to Profile->Import->Import from a publish profile file.

importProf

Step 5: Click on Publish and that’s all. It will navigate to the azure site with all the code changes.

siteDemo

Note: With the latest update to the azure, credit card information is not a mandatory for creating a 3 months free trial account.

Advertisements

Using Row_Number and Over clause without table’s column name

Recently got a requirement to show the data with serial number from function which splits a delimited string and returns individual items. For instance dbo.Split(‘honda’,’bmw’) returns the honda and bmw as rows. Here the challenge is to show the row number along with the split items in the exact order in which the function returns the data without altering the function definition. Using OVER clause on the function column will give ASC/DESC order rather than what we needed. The below is the code which solves this issue without changing the existing split function.

SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))[RowNum],
    items [Item]
FROM 
    dbo.split('Honda,Bmw,Mazda',',')

Below is the result

RowNum    Item

1             Honda

2             Bmw

3             Mazda

Hope this helps.


Custom key board mapping for Namespace organization in Visual Studio

Visual studio has lot of key board shortcuts for making the things easier. One of the mostly used feature for which IDE doesn’t have key binding is “removing unused namespaces”. I like this feature very much as it makes the code much cleaner. Normally, to make this work we need to right click on the namespaces and navigate to “organize usings ->remove and sort”. We can create a shortcut key with custom key binding as shown below. You can choose any shortcut key as per your convenience but I personally prefer Ctrl+N,O (N-Namespace, O-Organize) as this is the easy one to remember.

To create this, go to Tools->Options->environment->keyboard. By typing “organizeusing” in “Show commands containing” we can see list of IDE commands. Just select the one which ends with “OrganizeUsings.RemoveAndSort” and press the shortcut key you want to assign.

Hope this helps.


Shortcut key for entering NULL value into a table cell in SSMS

Here is a simple tip to enter null value in SQL Server Management Studio. It is a common case to enter null value into a cell. By default the cell values will be displayed with null if the table data is opened in SSMS. If we need to enter null value into a cell which has some data already into it we should explicitly type as NULL. Note that this should be in CAPS and if we give as null, SSMS assumes this null as a string value. A shortcut key for entering NULL value is ctrl+0 [ctrl key+zero].

Hope this helps


Populating Sequence values in a column to an already existing table

Recently we got a requirement to add a new identity column to an already existing table and to assign the sequential values to it. The table has thousands of rows already and manually entering the values into this column isn’t the right solution. The below query which automatically updates all the rows in a single shot has done the trick.

To do this, add the required column and update the column with the below query (change the table name and the column name as per your context) before making it as Identity column.

declare @value int=0;
update MyTable set @value=MyTableId=@value+1

 

Hope this helps


Dealing with many to many relationship in Entity Framework

Problem:

If table has many to many relationships and it doesn’t have any additional columns other than the referenced columns, entity framework will not generate the entity by default, which causes the issue to manage the relationships.

Solution:

In Entity Framework 4.0 this many to may relationship made easy which allows the automatic insertion of records in both the master and child table in a single transaction.

To describe the problem further, let us assume the below scenario.

Here the database contains Users and Roles tables. Both the tables are being referred by UserRoles. Our next step is to generate the Entities through Ado.Net Entity Data model template. After successful Entity creation, if we open the generated entities it will show only the two master tables and not the other UserRoles table. The same can be seen in model browser where all the tables are being listed in “Entities” section and only two tables in “Tables” section as below.

For a moment we might think that it’s a problem with model generator because of which the table got missed. But if we open the EDMX file in custom editor(right click on EDMX file->Open With->Automatic Editor Selector (XML)) all the properties will be listed as below.

So by this it is clear that entity generator has all the properties that are required to map the tables. It internally maintains the relationship without showing the actual table as there are no additional properties for “UserRoles” table except the primary key columns of the master tables.

Then how can we insert into this table without having entity being generated?

Entity framework will take care of that and we don’t need to write any additional queries to manage this table i.e. if we insert a user into Users table, the related record with the respective relations will be added to the UserRoles table. Here is the sample code to insert the user record and see how the records are added to both the Users and UserRoles table.

using (DemoEntities demoEnt = new DemoEntities())
{
User userInfo = new User()
{
UserId = 1,
UserName = “Super Admin”,
Password = “admin”
};
Role userRole = (from r in demoEnt.Roles where r.RoleId == 1 select r).Single();
userInfo.Roles.Add(userRole);
demoEnt.Users.AddObject(userInfo);
demoEnt.SaveChanges();
}

The resulted table results will be as below:

Note that both the DML statements will be wrapped in “Begin transaction” to avoid the redundant data on behalf of entity framework itself.
One another problem is selecting the rows from Users table associated with specific role. How can we relate the Users and Roles as there is no common column to join these?
It is pretty simple as we have relation between User and roles table. Below LINQ query would solve the issue.

public List<User> GetUsersByRole()
{
using (DemoEntities demoEnt = new DemoEntities())
{
var userList = (from u in demoEnt.Users

from r in u.Roles

where r.RoleId == 1

select u).ToList();
return userList;
}
}

Note that if UserRoles table has at least one additional column other than the existing two columns (UserId, RoleId), this table will be generated as Entity as shown below and in this scenario everything will be same as in the normal way. In the below model we have added the “notes” column to the UserRoles table.

Thanks for reading.

Happy Coding

 


Custom key board mapping in Sql Server Management Studio to get the results of any table

Here is a simple trick to get the results of any table with custom key mapping in SSMS. With this we can get rid of writing the different Select queries to get the records and can save few key strokes. Below 3 steps will do the trick.

step 1:  Create a stored procedure which accepts the table name, no of rows to be returned and the order of the results

1: CREATE PROCEDURE [dbo].[GetAnyTableRowsInSSMS]
2: (
3: @TableName VARCHAR(20),
4: –No of rows to be returned in the result set
5: @NoOfRows SMALLINT=5,
6: –‘a’ for ascending, ‘d’ for descending
7: @Order VARCHAR(4) =’a’
8: )
9: AS
10: BEGIN
11: IF @Order=’a’
12: SET @Order =’asc’
13: ELSE
14: SET @Order=’desc’;
15: DECLARE @ResultQry NVARCHAR(100);
16: SET @ResultQry= ‘SELECT TOP ‘ + CAST (@NoOfRows AS VARCHAR(5))+ ‘ * FROM ‘ + @TableName + ‘ ORDER BY 1 ‘ + @Order
17: EXEC SP_ExecuteSql @ResultQry
18: END

Step 2: Bind this stored procedure to SSMS custom key as in the below screenshot

Step 3: Execute in the query analyzer with the required parameters

Here we got top 2 rows from user table in ascending order. Like wise by pressing ctrl+5 on “users” will get the top 5 results in ascending order as we have given no of rows as 5 and order as ascending by default.

Points to Ponder:

1. Place this stored procedure in some test database rather the original database to avoid accidental copy into production.
2.  The results will be sorted by considering the first column the table. You can change this by passing the column name also as the parameter to the Stored Procedure.