Dot Guru

Interview Questions

2010-04-04T10:42:00.000-07:00

MS.NET:

Oops A to Z
Differences between stored proc and a function
Is sqlconnection managed or unmanaged code? Explain the reasons?
Explain about Garbage collection? How will you call explicitly garbage collection?
How will you handle unmanaged code with the help of Garbage collection?
What namespaces are used in ADO.NET
What is the difference between delete() and remove() methods of Datagrid row?
How to use unmanaged code in a .net application?
Explain about design patterns?
What are application blocks?
Difference between [Proctected Internal] and Internal?
Difference between data reader and dataset?
Optimization techniques
What are collections and generic collections?
Explain Asp.net Page life cycle?
What are Assemblies? How to create and implement a Shared Assembly in a application?
What is difference between user control and custom control?
Difference between Server.transfer and Response.redirect?
What are events and delegates?

Sql Server:

Explain about Indexes?
How to design a database for a given application?
Difference between Truncate and Delete?
Write a query to delete duplicate rows in a table?
Find nth max salary of an employee?
What are triggers? What are new features added in 2.0?
Differences between 2000 and 2005
Explain about Joins?

Note: Will be updated soon! If you have any thing to add please comment below or mail to rknuthakki@gmail.com :-)

SP, UDF and VIEW

2010-03-03T19:36:00.000-08:00

SPs are Databse subroutine used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.
They are compiled when first run, and the query plans are stored and cached by SQL Server's optimizer, and those cached plans are swapped out depending on frequency of usage.

View:
• To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
• To control access to rows and columns of data.
• To aggregate data for performance.

UDFS:
Set of statements which must return an object considered as function.
In general, UDFs can be a serious source of performance issues. Also, UDFs cannot be used for DML operations (INSERT/UPDATE/DELETE), cannot use non-deterministic functions, cannot use dynamic SQL, and cannot have error-handling (e.g. RAISERROR).
These are of 3 types;
1. Scalar: returns a single value, meant for doing calculations
2. Inline table valued: Returns a table, can be used in joins or in place of result set
3. Multi statement table valued: Returns a defined table.

A simple glance on all of the above:

SP	UDF	VIEW
Query plan is saved in server optimizer	Query plan is saved only for inline table functions	No plan is saved but definition of the view is saved with an exception of indexed view
DML operations can be performed	DML operations are not possible	DML operations are possible with considering certain constraints
We can use REUTURN keyword, and can return a numeric value, a string which must be a numeric. Eg: return ‘123'	We can return a single object of any kind	We can't return a value
We can use Non-deterministic functions like, getdate()	We can't use Non-Deterministic functions.	We can use
Error handling can be implemented	Error handling is not implemented	N/A
Supports Dynamic code	Doesn't supports dynamic Code	N/A

Collections and Generics: .net 2.0

2010-02-20T23:15:00.000-08:00

Name Space: System.Collections.Generic
When we look at the term "generic", unrelated to the programming world, it simply means something that is not tied to any sort of brand name. For example, if we purchase some generic dish soap, soap that has no brand name on it, we know that we are buying dish soap and expect it to help us clean our dishes, but we really don't know what exact brand (if any) will be inside the bottle itself. We can treat it as dish soap even though we don't really have any idea of its exact contents.

Think of Generics in this manner. We can refer to a class, where we don't force it to be related to any specific Type, but we can still perform work with it in a Type-Safe manner. A perfect example of where we would need Generics is in dealing with collections of items (integers, strings, Orders etc.). We can create a generic collection than can handle any Type in a generic and Type-Safe manner. For example, we can have a single array class that we can use to store a list of Users or even a list of Products, and when we actually use it, we will be able to access the items in the collection directly as a list of Users or Products, and not as objects (with boxing/unboxing, casting).

"Generic" Collections as we see them today

Currently, if we want to handle our Types in a generic manner, we always need to cast them to a System.Object, and we lose any benefit of a rich-typed development experience. For example, I'm sure most of us are familiar with the System.Collection.ArrayList class in Framework v1 and v1.1. If you have used it at all, you will notice a few things about it:

1. It requires that you store everything in it as an object

public virtual int Add(object value);
public virtual object this[int index] {get; set;}

2. You need to cast up in order to get your object back to its actual Type

3. Performance is really lacking, especially when iterating with foreach()

4. It performs no type safety for you (no exceptions are thrown even if you add objects of different types to a single list)

System.Collections.ArrayList list = new System.Collections.ArrayList();

list.Add("a string");
list.Add(45); //no exception thrown
list.Add(new System.Collections.ArrayList()); //no exception

foreach(string s in list) { //exception will be thrown!
System.Console.WriteLine(s);
}

For some situations we may feel the need to implement IEnumerable and IEnumerator in order to create a custom collection of our given Type, that is, a Type-safe collection for our needs. If you had a User object, you could create another class (which implements IEnumerable and IEnumerator, or just IList) that allows you to only add and access User objects, even though most implementations will still store them as objects. It is a lot of work implementing these two interfaces, and you can imagine the work needed to create this additional collection class every time you wanted a Type-safe collection.

The third and final way of creating a collection of items is by simply creating an array of that type, for example:

string[] mystrings = new string[]{"a", "b", "c"};

This will guarantee Type safety, but is not very reusable nor very friendly to work with. Adding a new item to this collection would mean needing to create a temporary array and copy the elements into this new temporary array, resizing the old array, copying the data back into it, and then adding the new item to the end of that collection. In my humble opinion, this is too much work that tends to be very error prone.

What we need is a way to create a Type-safe collection of items that we can use for any type imaginable. This template, or generic class, should be able to perform all of the existing duties that we need for our collections: adding, removing, inserting, etc. The ideal situation is for us to be able to create this generic collection functionality once and never have to do it again. You must also consider other types of collections that we commonly work with and their functionality, such as a Stack (First in, Last out) or a Queue (First In, First out), etc. It would be nice to be able to create different types of generic collections that behave in standard ways.

In the next I will show you how to create your first Generic Type.

Creating Our First Generic Type

In this section we will create a very simple generic class and demonstrate how it can be used as a container for a variety of other Types. Below is a simple example of what a generic class could look like:

public class Col < t > {
T t;
public T Val{get{return t;}set{t=value;}}
}

There are a few things to notice. The class name "Col<t > " is our first indication that this Type is generic, specifically the brackets containing the Type placeholder. This Type placeholder "T" is used to show that if we need to refer to the actual Type that is going to be used when we write this class, we will represent it as "T". Notice on the next line the variable declaration "T t;" creates a member variable with the type of T, or the generic Type which we will specify later during construction of the class (it will actually get inserted by the Common Language Runtime (CLR) automatically for us). The final item in the class is the public property. Again, notice that we are using the Type placeholder "T" to represent that generic type for the type of that property. Also notice that we can freely use the private variable "t" within the class.

In order to use this class to hold any Type, we simply need to create a new instance of our new Type, providing the name of the Type within the "< > " brackets and then use that class in a Type-safe manner. For example:

public class ColMain {
public static void Main() {
//create a string version of our generic class
Col<string > mystring = new Col<string > ();
//set the value
mystring.Val = "hello";

//output that value
System.Console.WriteLine(mystring.Val);
//output the value's type
System.Console.WriteLine(mystring.Val.GetType());

//create another instance of our generic class, using a different type
Col<int > myint = new Col<int > ();
//load the value
myint.Val = 5;
//output the value
System.Console.WriteLine(myint.Val);
//output the value's type
System.Console.WriteLine(myint.Val.GetType());

}
}

When we compile the two classes above and then run them, we will see the following output:

hello
System.String
5
System.Int32

Even though we used the same class to actually hold our string and int, it keeps their original type intact. That is, we are not going to and from a System.Object type just to hold them.

Generic Collections

The .NET team has provided us with a number of generic collections to work with in the the latest version of the .NET Framework. List, Stack, and Queue are all going to be implemented for us. Let's see an example on how to use the provided Generic List class.

For this example we want to be able to hold a collection of a Type that we create, which we used to represent a User in our system. Here is the definition for that class:

namespace Rob {
public class User {
protected string name;
protected int age;
public string Name{get{return name;}set{name=value;}}
public int Age{get{return age;}set{age=value;}}
}
}

Now that we have our User defined, let's create an instance of the .NET Framework Generic version of a simple List:

System.Collections.Generic.List<Rob.User > users
= new System.Collections.Generic.List<Rob.User > ();

Notice the new "Generic" namespace within the System.Collections namespace. This is where we will find all the new classes for our use. The "List" class within that namespace is synonymous with the typical System.Collections.ArrayList class, which I'm sure most of us are very familiar with by now. Please note that although they are similar there are several important differences.

So now that we have a Type-safe list of our User class, let's see how we can use this class. What we will do is simply loop 5 times and create a new User and add that User to our users collection above:

for(int x=0;x<5;x++) {
Rob.User user = new Rob.User();
user.Name="Rob" + x;
user.Age=x;
users.Add(user);
}

This should seem straight forward to you by now. In the next step we will iterate over that same collection and output each item to the console:

foreach(Rob.User user in users) {
System.Console.WriteLine(
System.String.Format("{0}:{1}", user.Name, user.Age)
);
}

This is slightly different that what we are used to. What you should notice right off the bat is that we do not have to worry about the Type being "Rob.User". This is because our generic collection is working in a Type-safe manner; it will always be what we expect.

Another way to output the same list would be to use a simple for() loop instead, and in this case we do not have to cast the object out of the collection in order to use it properly:

for(int x=0;x<users.Count;x++) {
System.Console.WriteLine(
System.String.Format("{0}:{1}", users[x].Name, users[x].Age)
);
}

No more silly casting or boxing involved. Here is the complete listing of the source plus the output of the result of executing the console application:

User.cs

namespace Rob {
public class User {
protected string name;
protected int age;
public string Name{get{return name;}set{name=value;}}
public int Age{get{return age;}set{age=value;}}
}
}

Main.cs

public class M {
public static void Main(string[] args) {
System.Collections.Generic.List<Rob.User > users = new System.Collections.Generic.List<Rob.User > ();
for(int x=0;x<5;x++) {
Rob.User user = new Rob.User();
user.Name="Rob" + x;
user.Age=x;
users.Add(user);
}

foreach(Rob.User user in users) {
System.Console.WriteLine(System.String.Format("{0}:{1}", user.Name, user.Age));
}
System.Console.WriteLine("press enter");
System.Console.ReadLine();

for(int x=0;x<users.Count;x++) {
System.Console.WriteLine(System.String.Format("{0}:{1}", users[x].Name, users[x].Age));
}

}
}

Output

Rob0:0
Rob1:1
Rob2:2
Rob3:3
Rob4:4
press enter

Rob0:0
Rob1:1
Rob2:2
Rob3:3
Rob4:4

More on Generics

More Generics features in the latest release of the .NET Framework include Generic Methods and Constraints. A generic method is very straight forward. Consider this example:

public static T[] CreateArray<t > (int size) {
return new T[size];
}

This static method simply creates a method of the given Type "T" and of the given "size" and returns it to the caller. An example of calling this method would be:

string[] myString = CreateArray<string > (5);

This will new up an instance of our string array, with an initial size of 5. You should take time to investigate the new version of the framework. You will be surprised at all the little helpful features like this.

Lastly, we should take a quick look at constraints. A constraint is setup to limit the Types which the Generic can accept. Let's say for example we had a generic type:

public class Dictionary<K, V > where K : IComparable {}

Notice the "where" clause on this class definition. It is forcing the K Type to be of Type IComparable. If K does NOT implement IComparable, you will get a Compiler error. Another type of constraint is a constructor constraint:

public class Something< v > where V: new() {}

In this example V must have at least the default constructor available, if not the Compiler will throw an error.

Ref:

MARS - Multiple Active Result Sets

2010-02-20T23:10:00.001-08:00

Multiple Active Result Sets - MARS
In a nutshell, it is the ability to have more than one pending request under a given SQL Server connection. For most cases this will directly translate to the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session.

It is probably as important to delimit what MARS is not:

Parallel execution: Though MARS enables more than one request to be submitted under the same connection, this does not imply that they will be executed in parallel inside the server. MARS will multiplex execution threads between outstanding requests in the connection, interleaving at well defined points.
Cursor replacement: As described earlier, there are some scenarios where cursors represented a suitable workaround for a lack of MARS; it may be valid to migrate those scenarios to use MARS. However, this does not imply that all current usages of cursors should be moved to MARS.
By default, all of the code snippets included in the previous section "just work" when using MARS-enabled client drivers against a SQL Server 2005 server. Also, the deadlock scenario described below where the application would hang now succeeds under MARS-enabled connection.

MARS-enabled client drivers are the following:

The SQLODBC driver included in the SQL Native Client.
The SQLOLEDB driver included in the SQL Native Client.
The SqlClient .NET Data Provider included in the Microsoft .NET Framework, Version 2.0.
By default, these drivers will establish MARS-enabled connections. If for some reason it is desired to establish connections that expose behavior of down-level drivers, each API provides an option to request non-MARS connections.

SqlClient provides the MultipleActiveResultSets connection string option. If set to false, MARS is not enabled for the session. If set to true or omitted, MARS is enabled.

Similarly, ODBC provides a SQL_COPT_SS_MARS_ENABLED connection option, while OLEDB provides a SSPROP_INIT_MARSCONNECTION option. Again, these options may only be needed to disable MARS, since it is enabled by default.

Note MARS is only available with SQL Native Client versions of ODBC and OLEDB providers. Older versions of the providers have not been enhanced to support MARS. Needless to say, new drivers cannot support MARS when connected to SQL Server 2000 or earlier servers.

Find Nth max/min Salary in SQL server

2010-01-24T08:42:00.000-08:00

SELECT * FROM temp1 t1 WHERE n - 1 = ( SELECT COUNT(DISTICNT ( sal )) FROM temp1 t2 WHERE t2.sal < t1.sal )
SELECT TOP 1 * FROM ( SELECT DISTINT TOP n sal FROM temp1 ORDER BY sal DESC) AS t ORDER BY t.sal ASC
In sql server 2005
WITH MyCTE AS(
SELECT t_id,
sal,
DENSE_RANK() OVER(ORDER BY sal DESC) as rank1
FROM temp1
)
SELECT TOP 1 t_id, sal, rank1 FROM MyCTE WHERE rank1 = n

Any updates/comments are appreciated.

Triggers

2010-01-24T06:04:00.000-08:00

Definitions: It is special kind of Stored Procedure which is executed whenever specific event is occurred in/on a table or database

2 types in Sql server 2000:

1. DML Trigger
Instead of triggers
After triggers

3 types in Sql server 2005:

• DML Triggers
Instead of triggers
After triggers
• DDL Triggers
• CLR Triggers

2 types in Oracle:

Row Level Triggers
Statement Triggers

Data Integrity

2010-01-24T04:33:00.001-08:00

Data Integrity falls into
1. Entity Integrity
2. Domain Integrity
3. Referential integrity
4. User defined integrity
Entity Integrity: Defines row as unique instance of an entity for a particular table
Domain Integrity: Is the validity of entries for a given column enforced by
i. restricting Type
ii. format
iii. Range of possible
E.g.: CHECK constraints, UNIQUE constraints, and DEFAULT constraints
Referential Integrity: Preserves defined relationships between tables when altered or deleted. E.g.: Check, Foreign key
User defined Integrity: Business rules can be enforced using triggers and stored procedures.

Reference:http://odetocode.com/Articles/79.aspx

Sortings

2010-01-24T04:17:00.000-08:00

Selection Sort:
1. Find the minimum value in the list
2. Swap it with the value in the first position
3. Repeat the steps above for the remainder of the list (starting at the second position and advancing each time)
Eg:
64 25 12 22 11

11 25 12 22 64

11 12 25 22 64

11 12 22 25 64

11 12 22 25 64
Bubble sort
Let us take the array of numbers "5 1 4 2 8", and sort the array from lowest number to greatest number using bubble sort algorithm. In each step, elements written in bold are being compared.
First Pass:
( 5 1 4 2 8 ) ( 1 5 4 2 8 ), Here, algorithm compares the first two elements, and swaps them.
( 1 5 4 2 8 ) ( 1 4 5 2 8 ), Swap since 5 > 4
( 1 4 5 2 8 ) ( 1 4 2 5 8 ), Swap since 5 > 2
( 1 4 2 5 8 ) ( 1 4 2 5 8 ), Now, since these elements are already in order (8 > 5), algorithm does not swap them.
Second Pass:
( 1 4 2 5 8 ) ( 1 4 2 5 8 )
( 1 4 2 5 8 ) ( 1 2 4 5 8 ), Swap since 4 > 2
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
Now, the array is already sorted, but our algorithm does not know if it is completed. The algorithm needs one whole pass without any swap to know it is sorted.
Third Pass:
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
( 1 2 4 5 8 ) ( 1 2 4 5 8 )
Finally, the array is sorted, and the algorithm can terminate.
Best/Worst/Average time complexity = O(n2)
Insertion Sort:
5 | 3 1 7 0 -> 3 5 | 1 7 9 -> 1 3 5 | 7 9 -> 1 3 5 7 | 9 -> 1 3 5 7 9
Merge sort:
1. If the list is of length 0 or 1, then it is already sorted. Otherwise:
2. Divide the unsorted list into two sublists of about half the size.
3. Sort each sublist recursively by re-applying merge sort.
4. Merge the two sublists back into one sorted list
Best/Worst/Average time complexity = O(n log n)
You can get a brief idea in the following link: http://in.answers.yahoo.com/question/index?qid=20071219060751AAjLx4O.

Delete vs Truncate in SQL SERVER

2010-01-24T00:58:00.000-08:00

Delete Command:
1.We can delete all or specific rows using WHERE clause in a table
2.Logs are maintained for deleted records
3.Slower than Truncate because of logging
4.Identity column value wouldn’t be reset after deleting all the records
Eg: DELETE "table name"

Truncate Command:
1.We can not use WHERE clause in a table
2.Logs are not maintained for deleted records
3.Faster than Delete because of logging is not done
4.Identity column value will be reset after truncating records
Eg: TRUNCATE TABLE "table name"

Assembly in .NET

2010-01-22T22:03:00.000-08:00

http://articles.techrepublic.com.com/5100-10878_11-5032712.html

MOSS 2007 Installations

2010-01-09T06:26:00.000-08:00

Follow up this link for MOSS 2007 Installation for a trial version

http://www.codeproject.com/KB/sharepoint/Setup_Virtual_SharePoint.aspx

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=67f93dcb-ada8-4db5-a47b-df17e14b2c74

Delete dupliate rows

2009-08-12T23:53:00.000-07:00

Delete duplicate rows - sql server 2005
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

Localization and globalization in asp.net

2009-08-09T22:02:00.000-07:00

Good article for localization and globalization:
http://www.codeguru.com/csharp/.net/net_asp/miscellaneous/article.php/c12407

How to install sql server 2005?

2009-08-01T03:42:00.001-07:00

Steps to install sql server 2005 can be found at:

http://articles.techrepublic.com.com/5100-10878_11-6102265.html

You need to install the following,
1. Windows installer
Note: No need of installing windows installer for server 2003 sp1,sp2
2. SQLEXPR.EXE
3. SQLServer2005_SSMSEE.msi

Download links
SQLEXPR.EXE
SQLServer2005_SSMSEE.msi

If you get client systems error 'doesn't allow remote connections' then
follow these steps,
start -> programmers -> microsoft sql server 2005 -> configuration tools ->
sql server surface area configuration

#1 enable sql browser services
#2 set the appropriate type for remote connections
#3 Stop and start modified services.

Happy Installation.

2009-08-01T02:16:00.001-07:00

Cscript.exe iisback.vbs /s tech11 /u administrator /p hmsr_2008 /backup /b "D:\hosting\iis_01-08-09.xml"

URLS:
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/475fb247-984d-4c65-842a-ae3bdd3e1080.mspx?mfr=true

http://support.microsoft.com/kb/324277

Cscript.exe iisback.vbs /s srv1 /u Administrator /p DCserver2009 /backup /b iis_backup_01_08-09

iisback /restore /b C:\Documents and Settings\Administrator\Desktop\MetaBack\MetaBack\Initial Backup - created automatically by IIS setup.SC1 /s tech11 /u administrator /p hmsr_2008

backup iis

2009-08-01T01:49:00.000-07:00

Cscript.exe iisback.vbs /s srv1 /u Administrator /p DCserver2009 /backup /b iis_backup_01_08-09

Hi all

2009-06-13T01:42:00.000-07:00

I am new bee to blogging. Have a good day.