30 June 2014

Collections

using System;
using System.Collections; // for Collections
using System.Collections.Generic; // for Generics

Collection classes are specialized classes for data storage and retrieval.

Collections are enhancement to the arrays.

Collection classes serve various purposes, such as allocating memory dynamically to elements and accessing a list of items on the basis of an index etc.

There are two distinct collection types in C#.

The standard collections, which are found under the System.Collections namespace (Array list, Hashtable, BitArray, Queue, SortedList, Stack)
And the generic collections, under System.Collections.Generic. (List, LinkedList, Dictionary)

The generic collections are more flexible and are the preferred way to work with data.
The generic collections or generics were introduced in .NET framework 2.0.
Generics enhance code reuse, type safety, and performance.
ArrayList


  • It represents ordered collection of an object that can be indexed individually.

  • It is basically an alternative to an array.

  • However unlike array you can add and remove items from a list at a specified position using an index and the array resizes itself automatically.

  • It also allows dynamic memory allocation, add, search and sort items in the list.

  • Unlike arrays, an ArrayList can hold data of multiple data types.


ArrayList al = new ArrayList();
al.Add(45);
al.Add("hello");
al.Count;
al.Sort();

  1. Add( object value ); // Adds an object to the end of the ArrayList.

  2. AddRange( ICollection c ); // Adds the elements of an ICollection to the end of the ArrayList.

  3. Clear(); // Removes all elements from the ArrayList.

  4. Contains( object item ); // Determines whether an element is in the ArrayList.

  5. GetRange( int index, int count ); // Returns an ArrayList which represents a subset of the elements in the source ArrayList.

  6. IndexOf(object); // Returns the zero-based index of the first occurrence of a value in the ArrayList or in a portion of it.

  7. Insert( int index, object value ); // Inserts an element into the ArrayList at the specified index.

  8. InsertRange( int index, ICollection c );// Inserts the elements of a collection into the ArrayList at the specified index.

  9. Remove( object obj ); // Removes the first occurrence of a specific object from the ArrayList.

  10. RemoveAt( int index ); // Removes the element at the specified index of the ArrayList.

  11. RemoveRange( int index, int count ); // Removes a range of elements from the ArrayList.

  12. Reverse(); // Reverses the order of the elements in the ArrayList.

  13. SetRange( int index, ICollection c ); // Copies the elements of a collection over a range of elements in the ArrayList.

  14. Sort(); // Sorts the elements in the ArrayList.

  15. TrimToSize(); // Sets the capacity to the actual number of elements in the ArrayList.


Hashtable


  • The Hashtable class represents a collection of key-and-value pairs that are organized based on the hash code of the key.

  • It uses a key to access the elements in the collection.

  • A hash table is used when you need to access elements by using key, and you can identify a useful key value.

  • Each item in the hash table has a key/value pair. The key is used to access the items in the collection.


Hashtable ht = new Hashtable();
ht.Add("001", "Zara Ali");

  1. Add( object key, object value ); // Adds an element with the specified key and value into the Hashtable.

  2. Clear(); // Removes all elements from the Hashtable.

  3. ContainsKey( object key ); // Determines whether the Hashtable contains a specific key.

  4. ContainsValue( object value ); // Determines whether the Hashtable contains a specific value.

  5. Remove( object key ); // Removes the element with the specified key from the Hashtable.


SortedList


  • It uses a key as well as an index to access the items in a list.

  • A sorted list is a combination of an array and a hash table.

  • It contains a list of items that can be accessed using a key or an index.

  • If you access items using an index, it is an ArrayList, and if you access items using a key , it is a Hashtable.

  • The collection of items is always sorted by the key value.


SortedList sl = new SortedList();
sl.Add("001", "Zara Ali");

  1. public virtual void Add( object key, object value ); // Adds an element with the specified key and value into the SortedList.

  2. Clear(); // Removes all elements from the SortedList.

  3. ContainsKey( object key ); // Determines whether the SortedList contains a specific key.

  4. ContainsValue( object value ); // Determines whether the SortedList contains a specific value.

  5. GetByIndex( int index ); // Gets the value at the specified index of the SortedList.

  6. GetKey( int index ); // Gets the key at the specified index of the SortedList.

  7. IList GetKeyList(); // Gets the keys in the SortedList.

  8. IList GetValueList(); // Gets the values in the SortedList.

  9. IndexOfKey( object key ); // Returns the zero-based index of the specified key in the SortedList.

  10. IndexOfValue( object value ); // Returns the zero-based index of the first occurrence of the specified value in the SortedList.

  11. Remove( object key ); // Removes the element with the specified key from the SortedList.

  12. RemoveAt( int index ); // Removes the element at the specified index of SortedList.

  13. TrimToSize(); // Sets the capacity to the actual number of elements in the SortedList.


Stack

It represents a last-in, first out collection of object. It is used when you need a last-in, first-out access of items.

When you add an item in the list, it is called pushing the item and when you remove it, it is called popping the item.

Stack st = new Stack();
st.Push('A');
st.Push('H');
st.Peek() - H
st.Pop();

  1. Clear(); // Removes all elements from the Stack.

  2. Contains( object obj ); // Determines whether an element is in the Stack.

  3. Peek(); // Returns the object at the top of the Stack without removing it.

  4. Pop(); // Removes and returns the object at the top of the Stack.

  5. Push( object obj ); // Inserts an object at the top of the Stack.

  6. ToArray(); // Copies the Stack to a new array.


Queue

It represents a first-in, first out collection of object. It is used when you need a first-in, first-out access of items.

When you add an item in the list, it is called enqueue and when you remove an item, it is called deque.

Queue q = new Queue();
q.Enqueue('A');
q.Enqueue('M');
char ch = (char)q.Dequeue(); - Removes 'A'

  1. Clear(); // Removes all elements from the Queue.

  2. Contains( object obj ); // Determines whether an element is in the Queue.

  3. Dequeue(); // Removes and returns the object at the beginning of the Queue.

  4. Enqueue( object obj ); // Adds an object to the end of the Queue.

  5. ToArray(); // Copies the Queue to a new array.

  6. TrimToSize(); // Sets the capacity to the actual number of elements in the Queue.


BitArray

It represents an array of the binary representation using the values 1 and 0.
It is used when you need to store the bits but do not know the number of bits in advance.
You can access items from the BitArray collection by using an integer index, which starts from zero.

System.Collections.Generic;


List

A List is a strongly typed list of objects that can be accessed by index.
This is an efficient, dynamically-allocated array. It does not provide fast lookup in the general case (the Dictionary is better for lookups). List is often used in loops.

List<int> list = new List<int>();
list.Add(2);
list.Add(6);
list.Remove(2);
list.Insert(1,5);

int[] arr = new int[2];
arr[0] = 2;
arr[1] = 3;
list = new List<int>(arr);
Dictionary


  • This is an implementation of a hash table: an extremely efficient way to store keys for lookup.

  • Dictionary is fast, well-designed and reliable.

  • A dictionary, also called an associative array, is a collection of unique keys and a collection of values, where each key is associated with one value.

  • Retrieving and adding values is very fast. Dictionaries take more memory, because for each value there is also a key.


Dictionary<string, string> domains = new Dictionary<string, string>();
domains.Add("de", "Germany");
domains.Add("sk", "Slovakia");
foreach(KeyValuePair<string, string> kvp in domains) { }

24 June 2014

Diff. b/w where and having in Sql Server


  • WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups.

  • We can use WHERE and HAVING clause together in a SELECT query, WHERE clause is applied first on individual rows and only after grouping HAVING clause is applied.

  • Syntactically where is used before GROUP BY clause, while having is used after GROUP BY clause.

  • We can't use having clause in update, delete queries, allowed only in select query.

  • To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause

  • We can use aggregate operations with having, but not with where

  • We can use Group By Clause with or without Where Clause.

  • select count(1) count  from emp having avg(rating)<10


How a HAVING clause works



  • The select clause specifies the columns.

  • The from clause supplies a set of potential rows for the result.

  • The where clause gives a filter for these potential rows.

  • The group by clause divide the rows in a table into smaller groups.

  • The having clause gives a filter for these group rows.

13 June 2014

Get count of emp's under a manager in Sqlserver

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[emp]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[emp](
[id] [int] IDENTITY(1,1) NOT NULL,
[empid] [int] NULL,
[empname] [varchar](50) NULL,
[mgrid] [int] NULL,
[role] [varchar](50) NULL,
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
([id] ASC)
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[emp] ON
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (1, 1, N'abc', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (2, 2, N'def', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (3, 3, N'ghi', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (4, 4, N'klm', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (5, 5, N'nop', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (6, 6, N'qrs', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (7, 7, N'tuv', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (8, 8, N'satya', 8, N'SSE')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (9, 9, N'sai', 9, N'SSE')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (12, 10, N'suman', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (13, 11, N'sri', 11, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (14, 12, N'srikanth', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (15, 13, N'sirish', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (16, 14, N'siva', 9, N'fa')
SET IDENTITY_INSERT [dbo].[emp] OFF

END

select * from emp

-- SQL Query

select e1.empid,e1.empname,e1.role,
(select count(1) from emp e2 where e1.empid=e2.mgrid and e2.empid<>e2.mgrid)noofemps
from emp e1 where e1.empid=e1.mgrid

-- LINQ Query

var res = (from t in db.emps
join l in db.emps.Where(x => x.empid != x.mgrid) on t.empid equals l.mgrid
into gj
//from subset in gj.DefaultIfEmpty()
where t.mgrid == t.empid
select
//t).Distinct().ToList();
new empmsg{ empid = Convert.ToInt32(t.empid), empname = t.empname, role = t.role, empcount = gj.Count() });//.Distinct();