LINQ to SQL with example

LINQ to SQL -

RELATIONAL DATABASES contain important data that developers want to query and retrieve in their programs. LINQ to SQL lets developers access relational data as strongly typed objects by translating LINQ to SQL.It also provides a simple API to save to the database all the changes made to an object graph. Both query and changes are implemented by translating LINQ and API calls to SQL commands. Hence, users get the productivity of LINQ and the efficiency of the database query processor.

LINQ to SQL was created to bridge the differences between relational data and CLR objects. SQL is a rich query language, but it is not well integrated with programming languages such as C# and VB.NET. As a result,database developers have always struggled with the “impedance mismatch”between the relational data in their databases and the objects used in their programs running on the .NET framework.

Now here,I am trying to describe LINQ to SQL with easy example,so let's start a quick tour of LINQ to SQL-

we have a 'employee' table-

CREATE TABLE [dbo].[Employee](
 [FName] [varchar](100) NOT NULL,
 [LName] [varchar](100) NOT NULL,
 [Email] [varchar](100) NOT NULL PRIMARY KEY,
 [mobile] [varchar](11) NULL,
 [phone] [varchar](20) NULL
)

Inserted data in following way-

INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'subash', N'chandra', N'subash@company.com', N'9966553322', N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'Anoop', N'Singh', N'emp2@company.com', N'9966553322', N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'abhishek', N'sachan', N'emp3@company.com', NULL, N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'subash', N'chandra', N'emp4@company.com', NULL, N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'sachin', N'srivastava', N'emp5@company.com', N'9971885522', NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'brijesh', N'singh', N'emp6@company.com', NULL, N'0251499663322')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'nitish', N'sharma', N'emp7@company.com', NULL, NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'alok', N'gupta', N'emp8@company.com', NULL, NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'shyam', N'yadav', N'abc@ss.com', N'9965655555', NULL)

Now, start Visual studio,then go to File->New->Project and take asp.net website from window.

Now ,right click on solution name and add new project 'class library' in the same solution with the name 'DataAccessLayer'.Now solution look like this-

Right click on DataAccessLayer project, click on 'add new item' and select 'Linq to SQL classes' and give the name 'Employee.dbml'.

Now connect your database in server explorer(if not able to see ,then go to view and open) ,after connecting   database server explorer look like this-

drag and drop 'employee'  table in Employee.dbml (you may drag and drop many tables according to your need).

Now build 'DataAccessLayer' project.After building database table is converted in object and can be seen  in Employee.Designer.cs.
Now we have to go asp.net project and take reference of this project; i.e. taking reference of DataAccessLayer in LINQtoSQLExample project, also take reference of 'System.data.linq' (skip if already has reference)
Now we are going to search employee by their first name and last name and make UI like this -

Add gridview below it to view employee data match with linq query.

Now write the following code in search button's click event-(write 'using DataAccessLayer' in using section) 

 protected void btnsearch_Click(object sender, EventArgs e)
        {
            using (EmployeeDataContext context = new EmployeeDataContext())
            {
                var employee = from c in context.Employees
                               where c.FName.Contains(txtempname.Text.Trim()) &&
                               c.LName.Contains(txtlastname.Text.Trim())
                               select c;
                gvEmployee.DataSource = employee;
                gvEmployee.DataBind();
            }
        }

Now, you are able to search employee through his/her first and last name,for example- type 'atul' and 'dubey' in first and last name textbox respectively.
Now we are going to insert new employee value in database through LINQ to SQL.First make UI like this -

and on save button's click event ,write the following code-(write 'using DataAccessLayer' in using section)

  protected void btnSave_Click(object sender, EventArgs e)
        {
            using (EmployeeDataContext context = new EmployeeDataContext())
            {
                Employee employee = new Employee
                {
                    FName = txtfname.Text,
                    LName = txtlname.Text,
                    Email = txtemail.Text,
                    mobile = txtmobile.Text,
                    phone = txtphone.Text
                };
                context.Employees.InsertOnSubmit(employee);
                context.SubmitChanges();
            }
        }

Now you are able to inserted data successfully in database. Thanks for reading this post.Your kind suggestions are always cordially welcome. Please send your feedback at atuldubey.87@gmail.com

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

                              

Using of COALESCE() and ISNULL() in Sql Server

COALESCE() function -The COALESCE function returns the first non-NULL value from a provided list of expressions. The COALESCE function is passed an undefined number of arguments and it tests for the first nonnull expression among them.If all arguments are NULL then COALESCE returns NULL.

The syntax is as follows:

COALESCE(expression [,...n])

Suppose we have 'employee' table with some data-

we can see that there are some null in mobile column and some null in phone column, so if we need contact details of employee,it may be mobile or phone(if mobile is null then phone and if both are null then it will return null)

so In this case we have to use COALESCE function.

After executing this query, we have found this result-

this is all about COALESCE() function , now Let's discuss about ISNULL() function-

ISNULL() function-

ISNULL validates if an expression is NULL, and if so, replaces the NULL value with an alternate value. In this example, any NULL value will be replaced with a different value.

Suppose we want to mobile number of employee table, and if any value is null in mobile column then we have to return 'unknown' word in place of null as a alternative value,then we write following query -

The result set is come in this way-

Difference between ISNULL and COALESCE-

• ISNULL is limited to two arguments but in COALESCE we can use more number of arguments
• Performancewise COALESCE is faster than ISNULL
• COALESCE is ANSI SQL standard whereas ISNULL is a proprietary TSQL function
• Using select ISNULL(NULL, NULL) doesn’t throw error but select COALESCE(NULL, NULL) throw an  error

Validation controls are provided by ASP.NET

There are six main types of validation controls:-
1.RequiredFieldValidator-
It checks whether the control have any value. It is used when you want the control should not be
empty.After apply it on control , users should compulsory to fill value in that control. For Example-In login page-

2.RangeValidator-
It checks if the value in validated control is in that specific range. Example TxtCustomerCode
should not be more than eight lengths. For Example-

3.CompareValidator-
It checks that the value in controls should match some specific value. Example Textbox TxtPie
should be equal to 3.14. For Example- For conforming password in user account page-

4.RegularExpressionValidator-
When we want the control, value should match with a specific regular expression.For Example-
Suppose you want to validate email in textbox,then you use RegularExpressionValidator with specific RegularExpression.

5.CustomValidator-
It is used to define User Defined validation.For Example-
Suppose you want to enter character between 6 to 15 in any textbox(username)-

Similarities and difference between Class and structure in C#

Following are the similarities between classes and structures:-
• Both can have constructors, methods, properties, fields, constants, enumerations,
events, and event handlers.
• Structures and classes can implement interface.
• Both of them can have constructors with and without parameter.
• Both can have delegates and events.


Following are the key differences between them:-
• Structures are value types and classes are reference types. So structures use stack
and classes use heap.
• Structures members cannot be declared as protected, but class members can be. You
cannot do inheritance in structures.
• Structures do not require constructors while classes require.
• Objects created from classes are terminated using Garbage collector. Structures are
not destroyed using GC.

Something about WCF

Definition of WCF 

Windows Communication Foundation (WCF) is a framework for building service-oriented applications. Using WCF we can build secure, reliable, transacted solutions that integrate across platforms.

WCF is a unified framework which provides :

1. NET Remoting

 2.Distributed Transactions

 3.Message Queues and 

4.Web Services into a single service-oriented programming model for distributed computing.

WCF interoperate between WCF-based applications and any other processes that communicate via SOAP (Simple Object Access Protocol) messages.

Features of WCF

1. Service Orientation
2. Interoperability
3. Multiple Message Patterns
4. Service Metadata
5. Data Contracts
6. Security
7. Multiple Transports and Encodings
8. Reliable and Queued Messages
9. Durable Messages
10. Transactions
11. AJAX and REST Support
12. Extensibility

To know more about features of WCF see: http://msdn.microsoft.com/en-us/library/ms733103.aspx

Useful terms of WCF

A WCF service is exposed to the outside world as a collection of endpoints.

1. Endpoint: Endpoint is a construct at which messages are sent or received (or both). Endpoint comprises of ABC’s       

What are ABC’s of WCF ? 

A. Address - Address is a location that defines where messages can be sent

B. Binding - Binding is a specification of the communication mechanism (a binding) that described how messages should be sent

C. Contract - Contract is a definition for a set of messages that can be sent or received (or both) at that location (a service contract) that describes what message can be sent.

2. Service: A construct that exposes one or more endpoints, with each endpoint exposing one or more service operations.

3. Contracts: A contract is a agreement between two or more parties for common understanding and it is a is a platform-neutral and standard way of describing what the service does. In WCF, all services expose contracts.

Types of Contracts:
1) Operation Contract: An operation contract defines the parameters and return type of an operation.

1 2	[OperationContract] double MUL(int i, intj);

2) Service Contract: Ties together multiple related operations contracts into a single functional unit.

1 2 3 4 5 6 7 8 9 10 11 12

[ServiceContract] //System.ServiceModel public interface IMath {     [OperationContract]     double MUL(int i, int j);     [OperationContract]     double DIV(int i, int j);     }

3) Data Contract: The descriptions in metadata of the data types that a service uses.

1 2 3 4 5 6 7 8 9 10 11 12 13

// Use a data contract [DataContract] //using System.Runtime.Serialization public class Complex {     private int real;     private int imaginary;       [DataMember]     public int Real { get; set; }       [DataMember]     public int Imaginary { get; set; } }