Embedd Facebook "Like" button to application

Today, we would be discussing how to embedd facebook adhoc 'Like' button in application. It doesn't requires any programming and we would be inserting some ad hoc markup into the Web pages.

So, we can make our website popular by inserting Like button on the page, so that user can click on Like button to make website popular on the Facebook.

So, let's start...

We simply need to insert below code snippet on the web page

 <iframe src="http://www.facebook.com/plugins/like.php?href=XXX" style="border:none; width:450px;height:80px"/>

Replace XXX with your website name

Below is the screenshot for the same:

Hope this article would make you familiar with facebook adhoc buttons.

SQL - CrossTab Queries using PIVOT

Today, we would be going through CrossTab queries using the PIVOT function available in SQL.
So let's start....and consider the below scenario

Below is the design of the table

Below is the data present in the table

Now, we want to get data in the manner so that we can see how much money Employee has spent on each travel medium. So, we would be making use of PIVOT here


select EmployeeName,[Train] as Train,[Bus] as Bus
from
(
select EmployeeName,TravelMedium,AmountSpent
from EmployeeExpenses)  ee
PIVOT
(
SUM(AmountSpent)
FOR TravelMedium IN ([Train],[Bus])
) as pvt





Below is the result:

Hope so you get an overview of PIVOT with this.

MVC4-Integrating OAuth/OpenID

 MVC 4 has enabled login from facebook and other sites using OAuth and OpenID. MVC 4 uses DotNetOpenAuth library for OAuth and OpenID authentication. In this blog post, we would see how to configure MVC 4 for Facebook Login

1) Create MVC4 application

2) Go to Facebook below URL - https://developers.facebook.com/apps

3) Click on 'Create New App' button

4) Enter your application Name

5) There could be captcha image to validate yourself

6) Fill in the basic information in the next form that is shown. Display Name and Email Address would be prefilled.

7) Click on ‘Website with Facebook login link’ and enter your site URL (like www.example.com).

However if your testing your application on your local machine enter the url like http://localhost:12345 (where 12345 would be your local port number)

8) Click on ‘Save Changes’ button and you would see App ID and App Secret Key

9) Now its time to make changes in application

Open the AuthConfig.cs in App_Start folder. Here you will all the code commented for different clients like Facebook, Twitter,GooglClient etc

            OAuthWebSecurity.RegisterFacebookClient(

                appId: "",

                appSecret: "");

10) Uncomment code lines for Facebook. Add the App ID and App Secret Key.

11) Run the application and click on Log In button. There you would see additional button for Facebook Login

12) Clicking on 'Facebook' button will authenticate user with Facebook credentials.

13) Once you register, you can see that you are authenticates and you can see your name.

Hope this article of mine would give some overview of MVC OAuth.

SQL Query: Removing Zero's

Today, we would consider a scenario of removing ZERO's from left side present in the column for one SQL Table.

Consider we are having following data in the table:


Select * from
[dbo].[RemoveZero]

Result:

Column1
0001
000100
100100
000 0001
00.001
01.001

and following is the result we are looking for:

Column1
1
100
100100
1
.001
1.001

So, how to do this? 

We can achieve the above result by using REPLACE and TRIM functionality

Below is the query which would produce the above result:

Select replace(ltrim(replace(numcol,'0',' ')),' ' ,'0') from 

[dbo].[RemoveZero]

So ,if you see:

a) We are replacing '0' with space by using Replace function

b) Then we are using LTRIM function as it would only Left trim the spaces from Left side

c) Then we are again replacing the spaces with Zero's , so this would bring back the zero anywhere already present.

Hope you would have liked this scenario.

DISTINCT Keyword

Today, we would be discussing about the DISTINCT keyword. Often we use distinct in our SQL queries to get the distinct records.

Consider the below data present in Employee table

Employee Name	Salary
Varun Khanna	50000
Garima Khanna	60000
Garima Khanna	70000
Varun Khanna	75000

If we execute below query 

SELECT distinct Employee_Name,Salary

  FROM Employees_Salary

Result

Employee Name	Salary
Garima Khanna	60000
Garima Khanna	70000
Varun Khanna	50000
Varun Khanna	75000

Now you must have expected distinct record of each employee; however this is not the case as to how DISTINCT works.

When we apply distinct in above query it doesn't search only for Employee Name; however it looks for distinct records for both Employee Name and Salary. Meaning if there have been duplicate record then it would have been shown only once.

 It is just a tag that you can put after the word SELECT to indicate that you want only distinct combinations of all columns in the result set returned.

Below are the key points

• DISTINCT always operates on all columns in the final result
• DISTINCT is not a function that accepts a column as an argument

We can modify the above query to return us the distinct employee Name.

Query would be 

SELECT distinct Employee_Name,max(Salary) as MaximumSalary

  FROM Employees_Salary

  group by Employee_Name

Result

Employee Name	Salary
Garima Khanna	70000
Varun Khanna	75000

Hope this article gives some basic understanding of DISTINCT keyword.

Difference between Function and Stored Procedure

Today, i am listing down just the basic difference between Function and Stored Procedure often asked in interviews.

	Function	Stored Procedure
1	Function always needs to return the value	Stored Procedure may or may not return the value
2	Function can be called from within the Stored Procedure	Stored Procedure cannot be called from Function
3	Function cannot have any DML statement i.e. it cannot be used for Data Manipulation in Tables	Stored Procedure can have DML statements i.e. it can be used to perform Data Manipulation in Tables
4	Function cannot have an output parameter	Stored Procedure may or maynot have an output parameter defined
5	Function can be called directly in Select statement	Stored Procedure can be called using EXEC or EXECUTE keyword

Hope this helps !!

SQL Server Function - SP_RENAME and NEWID

Today, we will have an overview of two SQL Server functions

• SP_RENAME
• NEWID

SP_RENAME is used to rename table name or rename any column of the table.
NEWID is used to extract random records from table.

Renaming of Column in Table

Syntax
sp_RENAME 'Tablename.ColumnName','NewColumnName','Column'

I had a table named as Employee_Salary in my SQL Server 2012 under Northwind database. It has four columns ID (Primary Key), Employee_Name,Salary and Sal_Month.
I want to rename column named as 'Employee_Name' to 'Employees_Name'

So, below is the command that needs to be executed

sp_RENAME 'Employee_Salary.Employee_Name','Employees_Name','Column'

Once done, you will see that column name would be updated.

Renaming of Table

We can rename the table with sp_RENAME function provided in SQL Server.
Syntax
sp_RENAME 'TableName' , 'TableNewName'

Below is the command executed

sp_RENAME 'Employee_Salary','Employees_Salary'

NEWID

As told, NEWID() is used to extract random records from the table.
We had table named as Employees_Salary containing employees salary for all the months. Below are the records present in the table

Below is the query to extract random records


select top 2 * from Employees_Salary order by NEWID()

Below is the data extracted when executed the query

If we execute the above query again below is the data returned

So, you have seen every time we execute the same query we are getting different records from table.

Hope with this article of mine, you have become familiar with very little but important functions of SQL Server.

Tip: Swapping two variables without using third variable

Just a small trick to swap two variables without using the third variable.

Here we go...

Consider the following example

1) int i =10
2) int j=15
3) i=i+j which is 10+15 making it 25. Now, i=25
4) j=i-j which is 25-15 making j=10
5) i=i-j which is 25-10 making i=15

So, you see that following are the values of both i & j after performing  above code logic
i=15 and j=10

Hope this trick helps in any of the scenario

SQL Function - ROW_NUMBER() and PARTITION BY

I recently came across one of new functions of SQL - ROW_NUMBER and PARTITION BY. So, how did i had encounter with these function. Let me tell you the scenario

Scenario

I had a Employee table containing the columns Employee Name, Total Salary credited and for which month salary was credited. I need to get the last paid salary of each employee. Employee table had all the salary records of the employee who ever worked with the company. This means that table had records of those employee also who had already left the company and who are presently working in the company.

Below is the table design:

Below is the data in the table

Below is the result we are expecting 

So, what we have to do to get this result. SQL  has function named as ROW_NUMBER() which returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Below is the syntax:

ROW_NUMBER ( ) 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Below is the query to get our expected result:

With summary as
(
select Employee_Name,Salary,Sal_Month,ROW_NUMBER() over (Partition by Employee_Name order by Sal_month desc) as rk
from [Employee_Salary]
)

select * from summary
where rk=1

I hope you would find many scenarios where you can use this function.

Get back to me in case of any queries / questions

Mobile Application in MVC

Overview

With Visual Studio 2012, there has been various new enhancements in ASP.NET and MVC. I have blogged about certain new features introduced in ASP.NET 4.5. Today, we would get overview of how we can create mobile application in MVC.

So let's start...

1) Open the Visual Studio 2012.I am using Visual C# template. Click on Web and select MVC4 application. Below is the screenshot

2)  Once click OK, you will see various MVC templates available. As we want to develop Mobile application, we would select 'Mobile Application' template. By default, View engine selected should be 'Razor'. Below is the screenshot

3) Once done it would create visual studio application. You can run the application without making any changes. Since, it is mobile application, it doesn't means that it would not run in our default browser (Internet Explorer, Google Chrome etc). Hence, we can run the application in browser too.

Below is the screenshot for Internet Explorer

However, we can run the same application in Mobile Phone Emulator. Below is the screenshot when running in Windows Phone Emulator

About Screen

Contact Screen

Log In Screen

Hope with this article of mine you will get overview of nee mobile template introduced in MVC.

Do get back to me in case of any questions.

Response new function named as Response.RedirectPermanent

CodeProject

OVERVIEW 

Recently, I came to know about the new function of Response named as Response.RedirectPermanent. So what is Response.RedirectPermanent? Where to use it?
Its same like Response.Redirect() function as it redirects you from one page to another but it returns HTTP status code 301 as compared to HTTP 302 returned by response.redirect().
So, in this article we would have brief overview of Response.RedirectPermanent. 

Response.RedirectPermanent

Consider a following scenario:
We have a web application which sells products to end users. In order to increase the user count or to bring more users to the website, company decides to start with discounts on certain products. Hence, it introduces the new page named as Discount.aspx listing all the products which has discounts on them. When user clicks on any of the discounted products, website redirects the user to page named as Products.aspx where user can view the details of product.
In order to increase the revenue company hires the SEO and registers the page on different search engines like Google, Yahoo etc. Hence, discount page gets registered on different search engines.

Now, after some months company decides that it don't want to give discounts on the products and thinks of removing the page. But they don't want to lose their customers who were coming to their site through discounted page.

In this case, we have two options
1) Copying the content of products page to discounted page
Disadvantage: When search engines see the same data on both discounted and products page, they can penalize the site for having the duplicate content and would delist the site.

2) Use Response.Redirect
We can use Response.Redirect on the page load of discounted page which would redirect the user to products page.
Disadvantage: When we use response.redirect, then it returns the status code as HTTP 302 temporarily redirected to different search engines.So, search engine store this information in their table that this discounted page had returned status code HTTP 302 and it was temporary re-directed.
Search engines expect that next time when we would hit discounted page, it would show the data and shouldn't return 302 status code. If next time again, page returns status code 302 then search engine penalizes the site and would delist the site.

Hence, in this scenario we should always use Response.RedirectPermanent.
What does Response.RedirectPermanent?
When we use Response.RedirectPermanent then it returns status code HTTP 301 Moved Permanently.It tells different search engines this page has permanently moved to new page. By this different search engines updates their index with new page. So, next time if someone clicks on discounted page, user would be automatically directed to products page by search engines.

So, let's build a small application, which would have  two buttons - one button would use Response.Redirect and other button would use Response.RedirectPermanent. 

We can view the GET request details by using the tool named as HTTPDebugger.Below are the GET request. You can see that when we have use Response.Redirect then browser has returned status code as 302 Found; however in case of Response.RedirectPermanent status code returned is 301 Moved Permanently. 
Below is the screenshot

Conclusion

Whenever we want to remove any page from website, we should always use Response.RedirectPermanent. With this we wouldn't loose our customers directed to our site through different search engines.

Hope with this article, we would have brief overview of Response.RedirectPermanent.

File Upload in Update Panel

CodeProject

BACKGROUND

AJAX update panel is used to prevent the page to have the full postback.Using update panel we can have partial page postback. Partial page postback does the following 
1) Improves the performance of the application 
2) Decrease the page load time of the application 
3) Decreases the round trip between the application and server 
4) Only the section in web page which needs to be refreshed gets postback 

We know that we can upload file to server using File upload control. However, if we use file upload control inside update panel, then it doesn't works. The reason for this is that file upload control doesn't work with asynchronous postback.
In this article, we would be working on making file upload control work with update panel and update progress. 

Let's do some coding !!

CODE

We would be creating small web application, where we would be have following

• File upload control to upload the files
• Upload button to SAVE image and view the uploaded image
• Process button to show Upload progress control

So let's start

To make AJAX work in your application, we have to first place SCRIPT MANAGER on our page. Now, if you are using master page in your application and your web page inherits master page, then you can place script manager in master page. If you don't want master page to have script manager,you can also place script manager on specific web pages.

Here, i am placing script manager on my web form as i am not inheriting any master page

 <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

Next we would place update panel on our web page. Inside update panel, we would place file Upload control, Upload button, Process button, Upload progress control and image control.
Below is the code

  <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
            <ContentTemplate>
                <asp:FileUpload ID="fileUploadImage" runat="server"></asp:FileUpload>
                <asp:Button ID="btnUpload" runat="server" Text="Upload Image" OnClick="btnUpload_Click" />
                <br />
                <asp:Button ID="btnProcessData" runat="server" Text="Process Data" OnClick="btnProcessData_Click" /><br />
                <asp:Label ID="lblMessage" runat="server" Text="Image uploaded successfully." Visible="false"></asp:Label><br />
                <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
                    <ProgressTemplate>
                        Please wait image is getting uploaded....
                    </ProgressTemplate>
                </asp:UpdateProgress>
                <br />
                <b>Please view the below image uploaded</b><br />
                <asp:Image ID="img" runat="server" Width="100" Height="100" ImageAlign="Middle" />
            </ContentTemplate>
        </asp:UpdatePanel> 

As told previously, file upload control doesn't works with asynchronous postback, Hence, we would need to postback to get the file uploaded. Here comes the role of TRIGGERS.

Update Panel control has property named as trigger which has 2 properties - PostbackTrigger and AsynchronousTrigger. In these properties we can specify, for which control we want the postback even if it is specified inside update panel.

Below is the update code:


  <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
            <ContentTemplate>
                <asp:FileUpload ID="fileUploadImage" runat="server"></asp:FileUpload>
                <asp:Button ID="btnUpload" runat="server" Text="Upload Image" OnClick="btnUpload_Click" />
                <br />
                <asp:Button ID="btnProcessData" runat="server" Text="Process Data" OnClick="btnProcessData_Click" /><br />
                <asp:Label ID="lblMessage" runat="server" Text="Image uploaded successfully." Visible="false"></asp:Label><br />
                <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1">
                    <ProgressTemplate>
                        Please wait image is getting uploaded....
                    </ProgressTemplate>
                </asp:UpdateProgress>
                <br />
                <b>Please view the below image uploaded</b><br />
                <asp:Image ID="img" runat="server" Width="100" Height="100" ImageAlign="Middle" />
            </ContentTemplate>
            <Triggers>
                <asp:PostBackTrigger ControlID="btnUpload"  />
                <asp:AsyncPostBackTrigger ControlID="btnProcessData" />
            </Triggers>

 </asp:UpdatePanel>

Below is the code behind code for both Upload button and ProcessData button:

Upload Button code:

 protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (fileUploadImage.HasFile)
            {
                
                 fileName = fileUploadImage.FileName;
                fileUploadImage.SaveAs("~/Images/" + fileName);
                img.ImageUrl = "~/Images/" + fileName;
            }
        }

ProcessData Button code:


  protected void btnProcessData_Click(object sender, EventArgs e)
        {
            System.Threading.Thread.Sleep(5000);
            lblMessage.Visible = true;
            
        }

I hope with this article will help you to work with fileupload control in update panel.

Dark Color Theme in Visual Studio 2012

CodeProject Visual Studio 2012 comes with default theme with gray color and all the menus in CAPS. I had posted an article to have different color themes in Visual Studio 2012.

Today, I read that Visual Studio 2012 has one more color theme named as DARK. When you install Visual Studio 2012, you will have only 2 themes - Light or Dark.

To change the current LIGHT theme to DARK theme, select Options from Tools menu

After this you will view following screen, where you can see option to select 'Color Theme'

Change the Color theme from Light to Dark.

After this you would altogether feel different Visual Studio experience.

Hope this article of mine would make you familiar with one more feature of Visual Studio 2012.Stay tuned to my blog to read more articles.