Friday, December 28, 2012

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.

Tuesday, December 25, 2012

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.

Saturday, December 22, 2012

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 NameSalary
Garima Khanna60000
Garima Khanna70000
Varun Khanna50000
Varun Khanna75000

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 NameSalary
Garima Khanna70000
Varun Khanna75000

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 !!