Difference between a Function and a Stored Procedure

UDF can be used inline in SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters.

• Procedure can return zero or n values whereas scalar-function can return one value which is mandatory.


• Procedures can have input/output parameters for it whereas functions can have only input parameters.


• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.


• Functions can be called from procedure whereas procedures cannot be called from function.


• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.


• We can go for transaction management in procedure whereas we can't go in function.


• Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.


• UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). It returns error like “Invalid use of a side-effecting operator 'INSERT' within a function.”

Sometimes we face a question, why we can't execute stored procedure inside a function?
Answer:
1. Stored Procedure may contain DML statements.
2. Function can't contain DML statements.
So executing Function inside stored procedure will never break rule 1.
But executing stored procedure inside function may break rule no 2.

Get continuous sequence numbers in sql sever

with d as
(
select 1 as a
union all
select a+1 from d where a<10
)
select * from d --option (maxrecursion 0)

Output:
a
1
2
3
4
5
6
7
8
9
10

Difference between hashtable and dictionary

1. HashTable will successfully return null for a non-existent item, whereas the Dictionary will throw an error.
2. Dictionary is faster than a Hashtable because there is no boxing and unboxing.
3. In Dictionary only public static members are thread safe, but all the members in a Hashtable are thread safe.
4. Dictionary is a generic type which means we can use it with any data type.

public void MethodHashTable()
 {
      Hashtable objHashTable = new Hashtable();
      objHashTable.Add(1, 100); // int
      objHashTable.Add(2.99, 200); // float
      objHashTable.Add('A', 300); // char
      objHashTable.Add("4", 400); // string

      lblDisplay1.Text = objHashTable[1].ToString();
      lblDisplay2.Text = objHashTable[2.99].ToString();
      lblDisplay3.Text = objHashTable['A'].ToString();
      lblDisplay4.Text = objHashTable["4"].ToString();
      // ----------- Not Possible for HashTable ----------
      //foreach (KeyValuePair<string, int> pair in objHashTable)
      //{
      // lblDisplay.Text = pair.Value + " " + lblDisplay.Text;
      //}
 }

public void MethodDictionary()
 {
     Dictionary<string, int> dictionary = new Dictionary<string, int>();
     dictionary.Add("cat", 2);
     dictionary.Add("dog", 1);
     dictionary.Add("llama", 0);
     dictionary.Add("iguana", -1);
     //dictionary.Add(1, -2); // Compilation Error

     foreach (KeyValuePair<string, int> pair in dictionary)
     {
          lblDisp.Text = pair.Value + " " + pair.Key;
     }
 }

Web Application vs Web site

What is Web Application?

Web app’s are server-side app’s, which follow open standard protocols like http, ftp, smtp, xml…

Every web app that we develop is universally accessible, because of their open standard support. In order to develop a web application, we have to setup our server. The setup includes installing a server product called web-server. Web server is a software which runs as a service & which is responsible to maintain one or more websites.

Application that develop with open standards and which are hosted in a public network are called web applications. Any device and any OS can request web app and get results.

Today most of the app development is web development, because of accessibility. Any user who logs on to this public network can access web application no need of installing any software for accessing this type of app’s, in other words web app’s are universal marked by the http protocols, because they are ruled by w3c rules.

a web application or web app is a client–server software application which the client (or user interface) runs in a web browser. web applications uses HTML and JavaScript, which are supported by a variety of web browsers.

It's choice of the people can go for web application or website we cannot say that which one is better because both is having advantages and disadvantages. Check below details for webaplication and website

Web Application

1. If we create any class files / functions those will be placed anywhere in the applications folder structure and it is precomplied into one single DLL.


2. In web application we have chance of select only one programming language during creation of project either C# or VB.NET.


3. Whenever we create Web Application those will automatically create project files (.csproj or .vbproj).


4. We need to pre-compile the site before deployment.


5. If we want to deploy web application project we need to deploy only .aspx pages there is no need to deploy code behind files because the pre-compiled dll will contains these details.


6. If we make small change in one page we need to re-compile the entire sites.


7. Web applications primarily allow the user to perform actions. ex: google, yahoo

WebSite

1. If we create any class files/functions those will be placed in ASP.NET folder (App_Code folder) and it's compiled into several DLLs (assemblies) at runtime.


2. In website we can create pages in multi programming languages that means we can create one page code in C# and another page code in vb.net.


3. Web Sites won’t create any .csproj/.vbproj files in project


4. No need to recompile the site before deployment.


5. We need to deploy both .aspx file and code behind file.


6. If we make any code changes those files only will upload there is no need to re-compile entire site


7. Websites are primarily informational. ex: cnn.com

Readonly vs Const variables in C#

Nullable datatype in c#

int? ins = null;
ins = 4;
if(ins.HasValue)
{
int a = ins.Value;
}

Diff b.w declaring a variable and defining a variable in c#

Void getall(string s) // delaring variable
{
String k;  // define variable takes memory even with value or not
}

Find largest value of an array.

int[] values = new int[] {1,3,5,7,9,10,8,6,4,2};

int maxValue = int.MinValue;

foreach(int i in values) {

maxValue = i > maxValue ? i : maxValue;

}

Console.WriteLine("Largest = {0}", maxValue); // Outputs "Largest = 10"

AccessSpecifier Defaults

	Default	Permitted declared accessibilities
namespace	public	none (always implicitly public)
enum	public	none (always implicitly public)
interface	public	none
class	private	All¹
struct	private	public, internal, private²
delegate	private	All¹
constructor	protected	All¹
interface member	public	none (always implicitly public)
method	private	All¹
field	private	All¹
user-defined operator	none	public (must be declared public)

¹ All === public, protected, internal, private, protected internal

² structs cannot inherit from structs or classes (although they can, interfaces), hence protected is not a valid modifier

Current Directory in Windows Apps

File.AppendAllText(Path.Combine(Directory.GetCurrentDirectory(), "logfileAutoResults.txt"), log);

Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Apppaths.xml")

Differences between an Abstract class and an Interface

1. Abstract classes can have implementations for some of its members, but the interface can't have implementation for any of its members.

2. Interfaces cannot have fields where as an abstract class can have fields.

3. An interface can inherit from another interface only and cannot inherit from an abstract class, where as an abstract class can inherit from another abstract class or another interface.

4. A class can inherit from multiple interfaces at the same time, where as a class cannot inherit from multiple classes at the same time.

5. Abstract class members can have access modifiers where as interface members cannot have access modifiers.

Another common C# Interview Question, that is commonly asked is, When do you choose interface over an abstract class or vice versa?
A general rule of thumb is, If you have an implementation that will be the same for all the derived classes, then it is better to go for an abstract class instead of an interface. So, when you have an interface, you can move your implementation to any class that implements the interface. Where as, when you have an abstract class, you can share implementation for all derived classes in one central place, and avoid code duplication in derived classes.

Boxing vs UnBoxing

Boxing and Unboxing is a  essential concept in .Net’s type system. With Boxing and Unboxing one can link between value-types and reference-types by allowing any value of a value-type to be converted to and from type object. The basic differences are :-

	Boxing	Unboxing
Definition:	Boxing is the process of converting a value type to the reference type.	Unboxing is the process of converting a reference type to value type .
Type of Conversion:	Implicit Conversion	Explicit Conversion
Example:	int i = 221; object obj = i; //boxing	object obj = 213; i = (int)obj ; // unboxing

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) { }

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.

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();