Wednesday, August 7, 2013

SQL Server system function (T-SQL) and brief description.


System Functions (T-SQL)

@@ERROR : Returns the error number for the last Transact-SQL statement executed.

@@IDENTITY : Returns the last-inserted identity value.
@@ROWCOUNT : Returns the number of rows affected by the last statement.
@@TRANSCOUNT : Returns the number of active transactions for the current connection.
APP_NAME : Returns the application name for the current session if set by the application.
CASE : Evaluation a list of condition and returns one of multiple possible result expression.
CAST : (expression as data_type) / CONVERT : Convert an expression of one data type to another.
COALESCE (expression [ ,...n]) : Returns the first non null expression among its arguments.
CURRENT_TIMESTAMP : Returns the current date and time. ANSI SQL equivalent to GETDATE.
CURRENT_USER : Returns the name of the current user. Equivalent to USER_NAME().
DATALENGTH (Expression) : Returns the number of bytes used to represent any expression.
FORMATMESSAGE (msg_number , [param_value [,...n]]) : Constructs a message from an existing message
in sys.messages and returns the formatted message for further processing. 
GETANSINULL : Returns the default nullability for the database for this session.
HOST_ID : Returns the workstation identification number.
HOST_NAME : Returns the workstation name.
IDENT_INCR : Returns the increment value (returned as numeric  (@@MAXPRECISION,0)) specified during
the creation of an identity column in a table or view that has an identity column.
IDENT_SEED : Returns the seed value (returned as numeric (@@MAXPRECISION,)) that was specified when
 an identity column in a table or a view that has an identity column was created.
IDENTITY : to insert an identity column into a new table.
ISDATE (expression) : Determines whether an input expression is a valid date.
ISNULL (expression , replacement_value) : Replaces NULL with the specified value.
ISNUMERIC (expression) : Determines whether an expression is a valid numeric type.
NEWID : Creates a unique value of type unique identifier.
NULLIF (expression , expression) : Returns the null value if the two specified expression are equal.
PARSENAME (object_name , object_piece) : Returns the specified part of an object name. Parts of an object
that can retrieved are the object name, owner name, database name and server name.
PERMISSIONS ([objectid [,'column']]) : returns a value containing a bitmap that indicate the statement,
object or permissions of the current user.
SESSION_USER : Returns the user name of the current context in the current database.
STATS_DATE : Returns the date that the statistics for the specified index were last updated.
SYSTEM_USER : Allow a system-supplied value for the current login to be inserted into a table
 when no default value is specified.
USER_NAME ([ID]) : returns a database user name from a specified identification number.

Cumulative Sum in SQL




Running Total in SQL

Cumulative Sum in SQL

Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.


DECLARE @Temp TABLE (
SL          INT,
GroupName   NVARCHAR(100),
Amount      DECIMAL(18,2)
)

INSERT INTO @Temp VALUES(1,'Number-1',5)
INSERT INTO @Temp VALUES(2,'Number-2',8)
INSERT INTO @Temp VALUES(3,'Number-3',2)


SELECT      T1.SL,
            T1.GroupName,
            T1.Amount,
            SUM(T2.Amount) as CumulativeSum
FROM  @Temp T1 INNER JOIN
            @Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL


If we run the above code we will get the cumulative output.

Transactions in SQL Server



Transactions

A transaction groups a set of task into a single execution unit. Every transaction begins with a specific task and ends when all the tasks in the group successfully complete. If the execution of any of the tasks is fails, the transaction is fails. Therefore, we can say a transaction has only two results: success or failure. Any incomplete steps result in the failure of the total transaction.


Properties of Transactions

Every Transaction has the following four standard properties and in short form generally called ACID:
  1. Atomicity: Ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is will be failed and previous operations are rolled back to their previous state.
  2. Consistency: Ensures that the database properly changes its state based on a successfully committed transaction.
  3. Isolation: Enables transactions to work independently.
  4. Durability: Ensures that the effect of a committed transaction persist in case of any failure.


Transaction Control

The following commands are used to control transactions:
  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction


Example
      BEGIN TRAN
      BEGIN TRY
            --INSERT INTO SQL statement
            --INSERT INTO SQL statement
            COMMIT TRAN
      END TRY    
      BEGIN CATCH
            ROLLBACK TRAN --For Error
            SELECT ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
      END CATCH

Stored Procedure

Stored Procedure

Stored Procedure is one of the powerful parts of SQL Server. Basically it is a group of T-SQL statements which is complied and stored inside the SQL Server.

As a result we get some advantages like:
1. Secure due to encryption.
2. We can use repeatedly after once compile.
3. Reduces data pass over a network.
4. Secured raw data because they are accessible by only stored procedure.
Basis Syntax of Stored Procedure
CREATE PROCEDURE Object Name
Input parameter DataType
Output parameter DataType Output
AS
BEGIN
--Variable Declaration 
  @parameter DataType
--SQL Statements
……………………..
END
In the stored procedure we can use SELECT, INSERT, DELETE and UPDATE these four basic query statements. For example, we can use the following query from application.
SELECT EmpID,EmpName,Cell 
FROM Employee
WHERE DeptID = 1
So, every time this query will be compiled. But if we make a stored procedure then it will compile one time and can be used as many times as required. So, stored procedure is more efficient than general query.
Simple Stored Procedure
CREATE PROCEDURE sp_Employee 
@DID int 
AS
BEGIN
 SELECT EmpID,EmpName,Cell 
 FROM Employee
 WHERE DeptID = @DID
END
In above Stored Procedure @DID is a parameter.
Execute the Stored Procedure
EXEC sp_Employee 1
Or
EXECUTE sp_Employee 1

Random Number in SQL


How to generate unique random number in SQL?

Random Number in SQL
SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND() AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1.  If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places.  By casting to integer we can get rid of the decimal places.

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

If we pass seed parameter value then the RAND() function will always return same value. 


SELECT RAND(1) AS [RandomNumber]

The NEWID() Way
Here's a different way to generate a random number without using the RAND() function. We can use NEWID system function to generate a random numeric value.


SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS[RandomNumber]

The NEWID function returns a unique value of unique identifier data type. To convert it into integer first we have to convert it into VARBINARY then integer. The resulting integer value can be positive and negative. It we want only a positive value then we need to use the absolute value mathematical function ABS. 

Method 1 : Generate Random Numbers (Int) between Range
-- Variables for the random number
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

-- Random number between 1 and 999
SET @Lower = 1     -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower),0)
SELECT @Random

Friday, July 12, 2013

Integrating Facebook in ASP Website using C# SDK

If you like it or hate it, you can't get away with the fact that Facebook is present all over the web. Whether it's a complex web application or just a news blog, every site uses Facebook for driving traffic. Integrating your web app or website with Facebook is easy and free. Apart from driving traffic to your site it also makes your web site more social and tailored for the users. You can access user's data from Facebook to simplify or eliminate your own user registration process. If you have a asp.net website you can use Facebook C# SDK for integrating Facebook within your website/web application.

Creating a Facebook App
First of all we need to create an app through which the website will connect with users. 
  1. Visit https://developers.facebook.com/apps and click on 'Create New App'.
  2. Fill in the desired App Name and Namespace. Remember these must beunique.
  3. On the basic settings page look for 'Select how your app integrates with Facebook' section and choose 'Website with Facebook Login'.
  4. In the Site URL field, enter your site address. I am working locally, so I added http://localhost:8779/ (Don't forget to put a trailing '/').
Note: Facebook keeps changing the developer interface. So, may be when you're reading this post you have to go through a different set of steps while a creating a new app and configuring it.
Create a new Facebook App

Facebook App Settings

Installing Facebook C# SDK

Next step is to add Facebook C# SDK to your website. Search for 'facebook' in the NuGet Package Manager. Install the latest stable release. In my case it was 6.1.4.
Facebok C# SDK NuGet

Note: Make sure you have a recent version of NuGet if not, download it fromhere.

Getting Started with code
I have created a simple interface with a button, two labels and a listbox.
Facebook ASP App Design
Design

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FacebookDemo.aspx.cs" Inherits="MyApp.FacebookDemo" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Log in with Facebook" OnClick="Button1_Click" />
        <br />
        <asp:Label ID="Label1" runat="server" Text="Your Email"></asp:Label><br />
        <br />
        <asp:Label ID="Label2" runat="server" Text="Your Friends"></asp:Label><br />
        <asp:ListBox ID="ListBox1" runat="server" Width="200px" Height="200px"></asp:ListBox>
    </div>
    </form>
</body>
</html>

User Authentication
On the click of the button, we allow users to connect with our app and grant the necessary permissions through Facebook's OAuth dialog. For this we create aFacebookClient and through that a login URL. We have to add a few parameters in the login url:
app id,
a url to which users will be redirected,
response type,
and finally the permissions we require from the users.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
protected void Button1_Click(object sender, EventArgs e)
       {
   var loginUrl = fb.GetLoginUrl(new
               {
 
                   client_id = "your_app_id",
 
                   redirect_uri = "http://localhost:8779/FacebookDemo.aspx",
 
                   response_type = "code",
 
                   scope = "email" // Add other permissions as needed
 
               });
               Response.Redirect(loginUrl.AbsoluteUri);
 }

This will take the user to Facebook Login page (if already not logged in) and then to our app login dialog. A user can accept or cancel the dialog, in both cases the user will be redirected back to our page.
Facebook OAuth Dialog
OAuth Dialog

Receiving Access Token
Our very next step is to receive an access token so that we can make requests to Facebook Graph API on behalf of the user. When the user is redirected back to our website, an auth code is appended in the URL. We have to exchange that code with an access_token.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
protected void Page_Load(object sender, EventArgs e)
    {
  if (Request.QueryString["code"] != null)
            {
                string accessCode = Request.QueryString["code"].ToString();
 
                var fb = new FacebookClient();
 
                // throws OAuthException
                dynamic result = fb.Post("oauth/access_token", new
                {
 
                    client_id = "your_app_id",
 
                    client_secret = "your_app_secret",
 
                    redirect_uri = "http://localhost:8779/FacebookDemo.aspx",
 
                    code = accessCode
 
                });
 
                var accessToken = result.access_token;
              }
    }

First we extract the code from URL then we do a post request to Facebook and exchange the code for an access_token. Also, you can know the expire time for a user access token by querying response.expires which returns the number of seconds until the token expires.

Getting User Information
After getting the access token, our work is mostly simplified. Let us find user'sid,name and friend list. All these come under the basic info and we don't need ask for a permission. As we asked for the permission to access email id of the user we can easily extract it along with name and id. So here's the extended codind after the previous part.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
protected void Page_Load(object sender, EventArgs e)
        {
   if (Request.QueryString["code"] != null)
            {
                string accessCode = Request.QueryString["code"].ToString();
 
                var fb = new FacebookClient();
 
                // throws OAuthException
                dynamic result = fb.Post("oauth/access_token", new
                {
 
                    client_id = "your_app_id",
 
                    client_secret = "your_app_secret",
 
                    redirect_uri = "http://localhost:8779/FacebookDemo.aspx",
 
                    code = accessCode
 
                });
 
                var accessToken = result.access_token;
                var expires = result.expires;
 
                // Store the access token in the session
                Session["AccessToken"] = accessToken;
 
                // update the facebook client with the access token
                fb.AccessToken = accessToken;
     
                // Calling Graph API for user info
                dynamic me = fb.Get("me?fields=friends,name,email");
 
                string id = me.id; // You can store it in the database
                string name = me.name;
                string email = me.email;
 
                Label1.Text = name + "(" + email + ")";
 
                var friends = me.friends;
 
                foreach (var friend in (JsonArray)friends["data"])
                {
                    ListBox1.Items.Add((string)(((JsonObject)friend)["name"]));
                }
 
                Button1.Text = "Log Out"; //Changin the button text to Log Out
 
                FormsAuthentication.SetAuthCookie(email, false);
            }
 }

Facebook C# SDK ASP .NET
OAuthException - #100
At this moment (i.e the code is still present in the url), if you reload the page you will experience an OAuthException - #100 "This authorization code has been used.".
What's happening is, we are again exchanging the code for an access token on the page load. Facebook could return us the already generated access token but it doesn't. Rather it perceives a call to generate a new access token. You can track this Facebook bug here.
So what we could do and actually we should do is check if the user is already signed in i.e look for the session variable we stored when the user signed in. So, here's a better a page_load method.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
protected void Page_Load(object sender, EventArgs e)
        {  
            // Check if already Signed In
            if (Session["AccessToken"] != null)
            {
                Label4.Text = Session["AccessToken"].ToString();
 
                // Retrieve user information from database if stored or else create a new FacebookClient with this accesstoken and extract data again.
                var fb = new FacebookClient(Session["AccessToken"].ToString());
 
                dynamic me = fb.Get("me?fields=friends,name,email");
 
                string email = me.email;
                Label1.Text = email;              
 
                var friends = me.friends;
 
                foreach (var friend in (JsonArray)friends["data"])
                {
                    System.Diagnostics.Debug.WriteLine((string)(((JsonObject)friend)["name"]));
                    ListBox1.Items.Add((string)(((JsonObject)friend)["name"]));
                }
 
                Button1.Text = "Log Out";
 
            }
             
            // Check if redirected from facebook
            else if (Request.QueryString["code"] != null)
            {
                ...
            }
 }

User Cancels Login Dialog
What if the user clicks on 'Cancel' rather than 'Go To App'. The user will still be returned to our site but instead of code there would be 3 other parameters in the url namely error, error_reason, error_description. Taking this to account, here's a modified page_load event.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
protected void Page_Load(object sender, EventArgs e)
        {  
            // Check if already Signed In
            if (Session["AccessToken"] != null)
            {
                ...
            }
    
            // Check if redirected from facebook
            else if (Request.QueryString["code"] != null)
            {
               ...
            }
 
            else if (Request.QueryString["error"] != null)
            {
                string error = Request.QueryString["error"];
                string errorReason = Request.QueryString["error_reason"];
                string errorDescription = Request.QueryString["error_description"];
            }
 
            else
            {
                // User not connected
            }
        }

Here's the output received when the user clicks on 'Cancel':YOUR_REDIRECT_URI?error_reason=user_denied&error=access_denied&error_description=The+user+denied+your+request.

Logout
You may also want to allow the users to logout and hide all the social details. In this sample app, as you have noted earlier the text for Login Button changes toLog Out when the user logs in. So, through the button's text we can know if the user wants to Log In or Out.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
protected void Button1_Click(object sender, EventArgs e)
        {
            if (Button1.Text == "Log Out")
                logout();
            else
            {
                var fb = new FacebookClient();
 
                var loginUrl = fb.GetLoginUrl(new
                {
 
                    client_id = "your_app_id",
 
                    redirect_uri = "http://localhost:8779/FacebookDemo.aspx",
 
                    response_type = "code",
 
                    scope = "email" // Add other permissions as needed
 
                });
                Response.Redirect(loginUrl.AbsoluteUri);
            }
        }
 
        private void logout()
        {
            var fb = new FacebookClient();