SQL Server Cursor with example

hey guys! you know that 'cursor' is little confusing concept in sql server,there are many blogs given this concept in his/her way but not in understandably way.Here I am trying to give concept of 'cursor' in easy way.
CURSOR:-  Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.

T-SQL retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statement, zero or more of them. Using simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows.

Sometimes there is a need to step through rows forward or backward, and one or more at a time. This is what cursors are used for. A cursor is a database query stored in SQL Server, not a SELECT statement, but the result set retrieved by that statement.

So finally we can say that -'Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time.'

Life Cycle of cursor-
1.Declare cursor-Before a cursor can be used, it must be declared (defined). This
process does not actually retrieve any data; it merely defines the
SELECT statement to be used.
Syntax:

DECLARE cursor_name CURSOR

 [LOCAL | GLOBAL] --define cursor scope

 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)

 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor

 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks

 FOR select_statement --define SQL Select statement

 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

2.Open -After it is declared, the cursor must be opened for use.
Syntax:

OPEN [GLOBAL] cursor_name --by default it is local 

3.Fetch-With the cursor populated with data, individual rows can be
fetched  as needed.
Syntax:

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]

FROM [GLOBAL] cursor_name

INTO @Variable_name[1,2,..n]

4.Close-Once the desired data has been fetched, the cursor must be
closed.
Syntax:

CLOSE cursor_name --after closing it can be reopen

5.Deallocate-Finally the cursor must be deallocate.After deallocating, it delete the cursor definition and released all the system resources associated with the cursor.
Syntax:

DEALLOCATE cursor_name --after deallocation it can't be reopen

Now understand whole concept is understanding in following example-
Suppose we have following student table-

CREATE TABLE [dbo].[student](

 [name] [varchar](50) NULL,

 [address] [varchar](50) NULL

)


INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Randhir','Basti')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Atul','Varanasi')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Brijesh','Varanasi')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Anoop','raiBareily')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Shyam','Ghazipur')



INSERT INTO [login].[dbo].[student] ([name],[address]) VALUES('Sachin','Varanasi')

after executing above query data looks in following way-

Now applying cursor on this table to fetch and process data one by one in following way-

-- Local variables

DECLARE @Student_name varchar(50);

DECLARE @Student_Address varchar(50);

-- Define the cursor

DECLARE Students_cursor CURSOR

FOR

SELECT * FROM Student ;

-- Open cursor (retrieve data)

OPEN Students_cursor;

-- Perform the first fetch (get first row)

FETCH NEXT FROM Students_cursor INTO @Student_name,@Student_Address;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

--@@FETCH_STATUS returns the status of the last cursor FETCH statement issued against  

--  any cursor currently opened by the connection. 

--   – @@FETCH_STATUS = 0 means The FETCH statement was successful. 

--   – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. 

--   – @@FETCH_STATUS = -2 The row fetched is missing. 

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Name-'+ @Student_name +',Address-'+ @Student_Address

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM Students_cursor INTO @Student_name,@Student_Address;

END

-- Close cursor

CLOSE Students_cursor

-- And finally, remove it

DEALLOCATE Students_cursor;

After running , It will produce result like this -

I hope, Now you are able to understand cursor in sql server.
To acquire more programming knowledge about dot net and sql server,Please keep in touch.

LINQ -Knowledge & Practice-part 2

hey guys! you have read my previous post 'LINQ-Knowledge & practice' , which is focused about basic knowledge about LINQ,but you know without practical and practice you can't understand anything in programming.so I will try to give you ability to make a good sense about LINQ.

Lets take an example, In which you have to fetch even number from given set of numbers, so If you don't know LINQ then you will go to looping to traverse numbers one by one,then you can get your result.This is very lengthy process and also produce poor performance.

Now try the above example with LINQ-

class EvenNumber
    {
        static void Main(string[] args)
        {
            int[] array = { 1, 2, 3, 6, 7, 8,9,10,11,12,13,14,15,16,17,18,19,20 };
            // Query expression for linq.
            var elements = from element in array 
                           orderby element 
                           where (element%2)==0
                           select element;

//all the even numbers is in 'elements'

            // Enumerate.
            foreach (var element in elements)
            {
                Console.Write(element);
                Console.Write(' ');
            }
            Console.WriteLine();
        }
    } 

when you run this program ,you will found this result-

2 6 8 10 12 14 16 18 20
so you can see you get even number to just write couples of line without traversing numbers one by one.
when you reading above code then your are aware about all codes such as foreach,var keyword etc, but you are confused in Query Expression section, so lets start to understand how to write LINQ Query Expression.


 var elements =                                          //query variable 
                           from element in array      //This is required
                           orderby element             //This is optional 
                           where (element%2)==0   //This is optional 
                           select element;                 //must end with select or group


This is just starting of LINQ. there are many concept which is most useful to write LINQ query, so lets discuss about some of these ,which is most important-

1.Anonymous type:-
                              Anonymous type is the type that is created anonymously. Anonymous type is a class that is created automatically by compiler in somewhere you can’t see directly while you are declaring the structure of that class. Confused? Let me show you one example.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LINQ
{
    class Program
    {
        static void Main(string[] args)
        {           
            Employee employee = new Employee { ID = 1, Name = "Anoop", city = "Varanasi" };
            Console.WriteLine("Name: {0} and city: {1}", employee.Name, employee.city);
            Console.ReadLine();
        }
    }

    //employee class
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string city { get; set; }
    }
}

This is normal way  of creating a class and initializing the instance that we have been doing this for couples of years.Till here we are not using Anonymous type.

The first thing that you need to do is that remove the class. (C# compiler will create the anonymous class based on what you initialize with.) Secondly, we have to change employee variable to implicitly-typed local variable by replacing “Employee” with “var”. Because we have removed Employee class from our code so that we won’t know about the type. That’s why the variable “employee ” should be declared as a implicitly-typed local variable. Then, we will remove “Employee ” after “new” keyword.

So, our final code will be like that below-


namespace LINQ
{


class Program
        {
            static void Main(string[] args)
            {
                var employee = new
                {
                    ID = 1,
                    Name = "Anoop",
                    city = "Varanasi"
                };

                Console.WriteLine("Name: {0} and city: {1}", employee.Name, employee.city);
                Console.ReadLine();
            }
        }
}


2. Object Initializers :-
                                     Object initializers lets you assign values to the properties of an objects at the time
of creating the object. Normally in .NET 1.1 and 2.0, we define the class with properties, then create the instance, and then define the values for properties either in constructor or in the function which is using the object. Here, in C# 3.0 we can define the values at the time of creation itself.

Lets take an example-
we have to make class of Milk with two properties- Name and Price


public class Milk
{
public string Name { get; set; }
public double Price { get; set; }
}

                                        
Now, in C# 2.0, we would have to write a piece of code like this to create a Milk instance and set its properties:

        Milk milk = new Milk();
                milk.Name = "Toned Milk";
                milk.Price = 15.50;


It's just fine really, but with C# 3.0, it can be done a bit more cleanly, thanks to the new object initializer syntax:
 Milk milk = new Milk { Name = "Toned milk", Price = 15.50 };

3.Collection Initializers:-
                                       Collection initializers use object initializers to initialize their object collection. By using a collection initializer, we do not have to initialize objects by having multiple calls.Just like C# 3.0 offers a new way of initializing objects, a new syntax for initializing a list with a specific set of items added to it, has been included. Lets take example of 'Milk' class of above example.

If we wanted to create a list to contain a types of milk, we would have to do something like this with C# 2.0:



               Milk milk;
                List<Milk> milklist = new List<Milk>();

                milk = new Milk();
                milk.Name = "Toned Milk";
                milk.Price = 15.50;
                milklist.Add(milk);

                milk = new Milk();
                milk.Name = "full cream";
                milk.Price = 20;
                milklist.Add(milk);

Using object initializers, we could do it a bit shorter:


         List<Milk> milklist= new List<Milk>();
                cars.Add(new Milk { Name = "Toned Milk", Price = 15.50 });
                cars.Add(new Milk { Name = "full cream", Price = 20 });

However, it can be even simpler, when combined with collection initializers:


List<Milk> milklist = new List<Milk> 
                {
                    new Milk {  Name = "Toned Milk", Price = 15.50 },
                    new Milk { Name = "full cream", Price = 20}
                };


4.Lambda Expressions:-
                                         Lambda expressions are similar to Anonymous Functions introduced in C# 2.0, except that lambda expressions are more concise and more flexible. All lambda expressions use the lambda operator =>, which is read as “goes to”. Lambda expressions use special syntax. They allow functions to be used as data such as variables or fields. The lambda expression syntax uses the => operator. This separates the parameters and statement body of the anonymous function.
Lets EvenNumber class(given above) converted in lamda expression form-


 class EvenNumber
    {
        static void Main(string[] args)
        {
            int[] array = { 1, 2, 3, 6, 7, 8,9,10,11,12,13,14,15,16,17,18,19,20 }; 
            //Lamda expression is used below
            var evenNumber= array.Where(n => n % 2 == 0);
           //all the even numbers is in 'elements'           
            foreach (var element in evenNumber)
            {
                Console.Write(element);
                Console.Write(' ');
            }
            Console.WriteLine();
        }
    } 



when you run this program ,you will found this result-

2 6 8 10 12 14 16 18 20

Now I am giving you a complete example to understand LINQ with including of all above concept.
Taking the example of milk class, In which we created milk list and finding the milk has price less than or equal to 15.


namespace LINQ
{
    public class Milk
    {
        public string name;
        public string totalFat;
        public string cholesterol;
        public string totalCarbohydrates;
        public string protein;
        public double price;
    }

    class LinqExample
    {
        static void Main(string[] args)
        {
            List<Milk> milkList = new List<Milk>
        {
                            new Milk
                            {
                            name="Toned",
                            totalFat="35g",
                            cholesterol="90mg",
                            totalCarbohydrates="35g",
                            protein="4g",
                            price=15
                            },
                            new Milk
                            {
                            name="Double Toned",
                            totalFat="86g",
                            cholesterol="55mg",
                            totalCarbohydrates="96g",
                            protein="4g", price=13.50
                            },
                            new Milk
                            {
                            name="Full Cream",
                            totalFat="03g",
                            cholesterol="08mg",
                            totalCarbohydrates="04g", protein="6g",
                            price=20
                            }
        };

            var MilkLessPrice =
                                        from milk in milkList
                                        where milk.price <= 15
                                        select new
                                        {
                                            Name = milk.name,
                                            Price = milk.price
                                        };
            Console.WriteLine("Milk with price less than 15:");
            foreach (var milkitem in MilkLessPrice)
            {
                Console.WriteLine("{0} is {1}", milkitem.Name,
                milkitem.Price);
            }
        }
    }
}


Enjoy this post , and will meet soon for next post ,which is focuses to topic-"LINQ to Object'
happy programming !!

LINQ -Knowledge & Practice

LINQ (Language Integrated Query)-

Language Integrated Query is a new feature in Visual Studio 2008 and onward that extends the query capabilities, using C# and Visual Basic. Visual Studio 2008 comes with LINQ provider assemblies that enable the use of Language Integrated Queries with different data sources such as in-memory collections, SQL relational database,ADO.NET Datasets, XML documents and other data sources.

The LINQ language extensions use the new Standard Query Operators API, which is the query language for any collection that implements IEnumerable<T>. It means that all collections and arrays can be queried using LINQ.

The following figure shows the architecture of LINQ, which can query different data sources using different programming languages:

Types of LINQ:

1. LINQ to Objects:-

                                Refers to the use of LINQ queries to access in-memory data structures.We can query any type that supports IEnumerable(Of T) (Visual Basic) or IEnumerable<T> (C#) such as arrays, lists, and other collection types.

2. LINQ to SQL:-

                             LINQ to SQL is used for managing the relational data as objects.LINQ to SQL translates the Language Integrated Queries in the object model to SQL and sends them to the database for execution. When the database returns the result, LINQ to SQL translates them back to objects that we can work with. LINQ to SQL also supports stored procedures and user-defined functions in the database.

3. LINQ to Datasets:

                                    LINQ to Datasets makes it easier to query the data cached in Datasets. A Dataset is disconnected, consolidated data from different data sources.

4. LINQ to Entities:

                                  The Entity Data Model is a conceptual data model that can be used to model the data so that applications can interact with data as entities or objects. Through the Entity Data Model,ADO.NET exposes the entities as objects.

5. LINQ to XML:

                              Using LINQ to XML, we can query, modify, navigate, and save the changes of an XML document. It enables us to write queries to navigate and retrieve a collection of elements and attributes. It is similar to XPath and XQuery.

Next part of this post is LINQ -Knowledge & Practice-part 2

To read more about above types ,you have to wait some time more.

"HAPPY PROGRAMMING !!!"