Friday, July 13, 2012

How to find the tables used in stored procedures ?

;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

Thursday, May 31, 2012

Difference between Set-Based approach and Cursor-Based approach in SQL Server ?

Think of it this way. If your wife wants you to fold the washing that's come out of the tumble dryer - she might well say 'can you please fold the washing?'. That's a set based approach - it's an operation over a collection of items.
The cursor based approach would be the equivalent of your wife asking you to fold a t-shirt, then when you had come back with that asking you to fold a pair of trousers, then when you had come back with that, asking if you could fold a jumper. No doubt, that would really hack you off. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.

When working in T-SQL, try to tell the system what you want to do with the data, not how you want it done.

What are the benefits of using Exists ?

Many times you're required to write query to determine if a record exists. Typically you use this to determine whether to insert or update a records. Using the EXISTS keyword is a great way to accomplish this.
Here's a simple example from the pubs database using EXISTS:

if EXISTS (select *
 from authors
 where au_id = '172-32-1176')
  Print 'Record exits - Update'
ELSE
  Print 'Record doesn''t exist - Insert'
 
The EXISTS function takes one parameter which is a SQL statement. If any records exist that match the criteria it returns true, otherwise it returns false. This gives you a clean, efficient way to write a stored procedure that does either an insert or update.
The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. Consider the following two queries:

if exists (select *
 from authors
 where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
 from authors
 where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'
 
In the pubs database there are only 23 records in the authors table.  
Even with that small number of records, the IF EXISTS version runs 4 
times faster than selecting a count.  This is because it stops as soon 
as it finds a single record that matches the criteria.  The second 
statement must process all the rows that match.

 

What are the differenct types of cursors in SQL Server ?

Following are the different types of cursors in Sql Server:
  • Base Table : These are the lowest level cursor available. These can scroll forward or backward with minimal cost, and can be updated.
  • Static : Cursor can move to any record, but the changes on data can't be seen.
  • Forward-Only : Cursor moves one step forward. Can't move backward
  • Dynamic : Most resource extensive. Cursor can move anywhere and all the changes on the data can be seen
  • Keyset-driven : Only updated data can be viewed, inserted and deleted data cannot be viewed.

What are the types of temporary tables and where are they stored in SQL Server ?

There are two types of temporary tables:
  • Local temporary tables:
    • Only available to the current connection to the database for the current login
    • They are dropped when the connection is closed
  • Global temporary tables:
    • Available to any connection upon their creation
    • They are dropped when the last connection using them is closed  
     
Local Temporary Table CREATE TABLE #table_name (
    column_name [DATATYPE] )
 

Global Temporary Table CREATE TABLE ##table_name (
column_name
[DATATYPE] )

Thursday, April 5, 2012

Tuesday, March 13, 2012

What are the advantages of using Stored Procedures ?

Stored procedures have many advantages. They help you to separate the client application from the underlying structure of the database which allows you to simplify client coding, and improve the stability of the application. Furthermore, by using stored procedures, you can very easily create reusable code that can be executed from all of the applications that you and other developers write.

Stored procedures are very effective in terms of performance because they are pre-compiled and execute quickly. When you build a stored procedure, a query plan is created-which contains the most efficient method of executing the stored procedure given available indexes and other environmental factors. Another benefit of stored procedures is that they tend to lock data for shorter periods of time than the equivalent application code.

Stored procedures facilitate the security of data. This is because when you give users or groups rights to stored procedures, it is not necessary for you to give them rights to the underlying tables. A common scenario is to give users or groups view-only rights to tables. This way, they can build their own queries and reports. You then use stored procedures to add, edit, and delete data. Once you give users or groups the rights to the stored procedures, it is not necessary to give them add, edit, and delete rights to the underlying tables.

You can use stored procedures in either a two-tier or a three-tier application development model. In the two-tier model, the data and the stored procedures reside on a SQL Server. The application tier contains the GUI (graphical user interface) as well as the code that interacts with the tables, views, and stored procedures that reside on the SQL Server. In the three-tier model, the data and the stored procedures once again reside on the SQL Server. The difference is that the application tier contains only the GUI. The code that interacts with the data tier is located in a separate library, generally a Visual Basic DLL. We refer to this as the business logic tier. You can then use the business object, or DLL, with multiple applications. The three-tier model promotes reuse, and further separates the application from the data tier.

What is the difference between Stored Procedures and Functions

Functions
----------
1) can be used with Select statement
2) Not returning output parameter but returns Table variables
3) You can join UDF
4) Cannot be used to change server configuration
5) Cannot be used with XML FOR clause
6) Cannot have transaction within function

Stored Procedure
-----------------
1) have to use EXEC or EXECUTE
2) return output parameter
3) can create table but won’t return Table Variables
4) you can not join SP
5) can be used to change server configuration
6) can be used with XML FOR Clause
7) can have transaction within SP

Saturday, February 4, 2012

What's new in C# 3.5 ?

The key features that are introduced in C# 3.5 are

° Anonymous Types for LINQ

° Implicitly Typed Local Variables - The var Keyword

° Extension Methods

° Object and Collection Initializers

° Lambda Expressions

Saturday, January 28, 2012

What are the types of polymorphism ?

There are two types of polymorphism:
  1. Static (Compile time): Function Overloading, Operator Overloading
  2. Dynamic (Run time): Virtual Functions

What is object slicing ?

When a derived class object is assigned to a base class, only the base class's part of content in the derived object are copied to the base class, leaving behind the derived class specific contents. This is referred as Object Slicing.

Example:

Class Base
{
public int i;
};

class Derived : public Base
{
public int j;
};

int main()
{
Base objB;
Derived objD;
objB = objD;
//Here objD contains both i and j.
//But only i is copied to objB.
}

Friday, January 20, 2012

What is the difference between DELETE and TRUNCATE ?

  1. TRUNCATE is a DDL command whereas DELETE is a DML command.
  2. TRUNCATE is much faster than DELETE. Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
  3. You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
  4. In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
  5. You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause
  6. TRUNCATE command resets the High Water Mark for the table but DELETE does not. So after TRUNCATE the operations on table are much faster.

How to maintain the scroll position of the page on postback ?

Using MaintainScrollPositionOnPostback attribute in @Page directive

Note:- You
can define this attribute for all pages by setting the maintainScrollPostitionOnPostback attribute (note that it is case-sensitive in configuration files) on the element of the Web.config file.

What is the difference between website and webapplication ?

Web Application project model

  • Provides the same Web project semantics as Visual Studio .NET 2003 Web projects.
  • Has a project file (structure based on project files).
  • Build model - all code in the project is compiled into a single assembly.
  • Supports both IIS and the built-in ASP.NET Development Server.
  • Supports all the features of Visual Studio 2005 (refactoring, generics, etc.) and of ASP.NET 2.0 (master pages, membership and login, site navigation, themes, etc).
  • Using FrontPage Server Extensions (FPSE) are no longer a requirement.

Web Site project model

  • No project file (Based on file system).
  • New compilation model. (Read here or here for more details) and ...
  • Dynamic compilation and working on pages without building entire site on each page view.
  • Supports both IIS and the built-in ASP.NET Development Server.
  • Each page has it's own assembly.
  • Defferent code model. (Read here for more details)

Ok, all is great, but you want to create your web site now. Which model should you use?

  • You need to migrate large Visual Studio .NET 2003 applications to VS 2005? use the Web Application project.
  • You want to open and edit any directory as a Web project without creating a project file? use Web Site project.
  • You need to add pre-build and post-build steps during compilation? use Web Application project.
  • You need to build a Web application using multiple Web projects? use Web Application project.
  • You want to generate one assembly for each page? use Web Site project.
  • You prefer dynamic compilation and working on pages without building entire site on each page view? use Web Site project.
  • You prefer single-page code model to code-behind model? use Web Site project.

For more information please visit

http://msdn.microsoft.com/en-us/library/dd547590.aspx

What are partial classes, what is the use of it in .net ?

.Net 2.0 provided us with new feature called partial classes. Using partial classes we can use multiple files to keep the code of same class. Yes - we can put some methods to one file and the others to another file. Although partial classes may be extremely useful they can be also used to ruin system's technical design if developers don't know what happens behind the compiler.

Visual Studio uses partial classes to keep Windows and Web forms automatically generated code separately of user written code behind the forms.

What is the difference between String and StringBuilder ?

Both String and StringBuilder are classes used to handle strings.

The most common operation with a string is concatenation. This activity has to be performed very efficiently. When we use the "String" object to concatenate two strings, the first string is combined to the other string by creating a new copy in the memory as a string object, and then the old string is deleted. This process is a little long. Hence we say "Strings are immutable".

Simple example: The following will make many programmers cringe with pain:

string s = string.Empty;
for (i = 0; i < 1000; i++) {
s
+= i.ToString() + " ";
}
You'll end up creating 2001 strings here, of which 2000 are thrown away.

When we make use of the "StringBuilder" object, the Append method is used. This means, an insertion is done on the existing string. Operation on StringBuilder object is faster than String operations, as the copy is done to the same location. Usage of StringBuilder is more efficient in case large amounts of string manipulations have to be performed.

The same example using StringBuilder:
StringBuilder sb = new StringBuilder();
for (i = 0; i < 1000; i++) {
sb
.Append(i);
sb
.Append(' ');
}

This should place much less stress on the memory allocator :-)

It should be noted however, that the C# compiler is reasonably smart when it comes to strings. For example, the following line

string foo = "abc" + "def" + "efg" + "hij";

will be joined by the compiler, leaving only a single string at runtime. Similarly, lines such as

string foo = a + b + c + d + e + f;

will be rewritten to

string foo = string.Concat(a, b, c, d, e, f);
so you don't have to pay for five nonsensical concatenations which would be the naïve way of handling that. This won't save you in loops as above (unless the compiler unrolls the loop but only the JIT may actually do so and better don't bet on that)

Thursday, January 19, 2012

What are Cookieless Sessions ?

Sessions are identified by a unique identifier that can be read by using the SessionID property. When session state is enabled for an ASP.NET application, each request for a page in the application is examined for a SessionID value sent from the browser. If no SessionID value is supplied, ASP.NET starts a new session and the SessionID value for that session is sent to the browser with the response.

By default, SessionID values are stored in a cookie. However, you can also configure the application to store SessionID values in the URL for a "cookieless" session.

A session is considered active as long as requests continue to be made with the same SessionID value. If the time between requests for a particular session exceeds the specified time-out value in minutes, the session is considered expired. Requests made with an expired SessionID value result in a new session.


By default, the SessionID value is stored in a non-expiring session cookie in the browser. However, you can specify that session identifiers should not be stored in a cookie by setting the cookieless attribute to true in the sessionState section of the Web.config file.

The following example shows a Web.config file that configures an ASP.NET application to use cookieless session identifiers.

             

ASP.NET maintains cookieless session state by automatically inserting a unique session ID into the page's URL. For example, the following URL has been modified by ASP.NET to include the unique session ID lit3py55t21z5v55vlm25s55:

http://www.example.com/(S(lit3py55t21z5v55vlm25s55))/orderform.aspx 

When ASP.NET sends a page to the browser, it modifies any links in the page that use an application-relative path by embedding a session ID value in the links. (Links with absolute paths are not modified.) Session state is maintained as long as the user clicks links that have been modified in this manner. However, if the client rewrites a URL that is supplied by the application, ASP.NET may not be able to resolve the session ID and associate the request with an existing session. In that case, a new session is started for the request.

The session ID is embedded in the URL after the slash that follows the application name and before any remaining file or virtual directory identifier. This enables ASP.NET to resolve the application name before involving the SessionStateModule in the request.

Wednesday, January 18, 2012

Why do we need Sessions in web applications ?

HTTP is a stateless protocol, it can’t hold the client information on page. In other words after every request and response the server does not remember the state, data and who the user was. If user inserts some information, and move to the next page, that data will be lost and user would not able to retrieve the information. So, Session provides that facility to store information on server memory.

What are Sessions in Asp.Net ?

ASP.NET Session state provides a place to store values that will persist across page requests. Values stored in Session are stored on the server and will remain in memory until they are explicitly removed or until the Session expires.

Storing and retrieving a value in the Session is as simple as:

VB

Session("Name") = "John Doe"

'or

Session.Add("Name","John Doe")

'retrieving

Dim Name As String = Session("Name")

C#

Session["Name"] = "John Doe";

//or

Session.Add("Name","John Doe");

//retrievingstring

Name = (string)Session["Name"];

By default the Session will be created within the same process that your web site runs in (InProc). This is controlled by a setting in the web.config file:

Although running the Session In Process is very convenient, it does mean that all Session values will be lost whenever the application recycles (such as when deploying updates) . There are alternate modes you can use that will allow the Session state to survive even when the application recycles. The available options are:

  • Off - No session state will be stored
  • InProc - (The Default) Session state exists within the process the web is using
  • StateServer - Session data is sent to the configured stateserver service
  • SQLServer - Session data is store in the configured sql server database

Both the StateServer mode and the SQLServer mode allow Session state to survive an application recycle. But, when storing reference type objects (such as class instances), they can only be stored to StateServer or SQLServer if they have been marked with the Serializable attribute.

An important consideration for using Session state is that the Session does expire. By default, if a user does not access their Session data within 20 minutes (by default), the Session will expire and all items that had been stored in the Session will be discarded. Because of this, it is important to check the object that is returned from the Session to see if it exists or if it is null before you try to work with it. For example:

object sessionObject = Session["someObject"];
if (sessionObject != null) {
myLabel.Text = sessionObject.ToString();
}

The Session Timeout is adjustable through a web.config setting but increasing the timeout value can put memory pressure on your server that may be undesirable.

Other commonly used Session methods are:

  • Session.Abandon() - removes the Session and all items that it contains
  • Session.Clear() - removes all items from the Session
  • Session.RemoveAll() - removes all items from the Session
  • Session.Remove("itemName") - removes the item that was stored under the name "itemName"

Monday, January 16, 2012

When to use abstract class and when to use interface ?

Here are some recommendations to help you to decide whether to use an interface or an abstract class to provide polymorphism for your components.

  • If you anticipate creating multiple versions of your component, create an abstract class. Abstract classes provide a simple and easy way to version your components. By updating the base class, all inheriting classes are automatically updated with the change. Interfaces, on the other hand, cannot be changed once created. If a new version of an interface is required, you must create a whole new interface.
  • If the functionality you are creating will be useful across a wide range of disparate objects, use an interface. Abstract classes should be used primarily for objects that are closely related, whereas interfaces are best suited for providing common functionality to unrelated classes.
  • If you are designing small, concise bits of functionality, use interfaces. If you are designing large functional units, use an abstract class.
  • If you want to provide common, implemented functionality among all implementations of your component, use an abstract class. Abstract classes allow you to partially implement your class, whereas interfaces contain no implementation for any members.

A good way to distinguish between a case for the one or the other has always been the following:

1. Are there many classes that can be "grouped together" and described by one noun? If so, have an abstract class by the name of this noun, and inherit the classes from it. (A key decider is that these classes share functionality, and you would never instantiate just an Animal... you would always instantiate a certain kind of Animal: an implementation of your Animal base class)
Example: Cat and Dog can both inherit from abstract class Animal, and this abstract base class will implement a method void Breathe() which all animals will thus do in exactly the same fashion. (I might make this method virtual so that I can override it for certain animals, like Fish, which does not breath the same as most animals).

2. What kinds of verbs can be applied to my class, that might in general also be applied to others? Create an interface for each of these verbs.
Example: All animals can be fed, so I will create an interface called IFeedable and have Animal implement that. Only Dog and Horse are nice enough though to implement ILikeable - I will not implement this on the base class, since this does not apply to Cat.

As said by someone else's reply: the main difference is where you want your implementation. By creating an interface, you can move your implementation to any class that implements your interface.
By creating an abstract class, you can share implementation for all derived classes in one central place, and avoid lots of bad things like code duplication.

What is the difference between abstract class and interface ?

  • Classes may inherit from only one base class, so if you want to use abstract classes to provide polymorphism to a group of classes, they must all inherit from that class.
  • Abstract classes may also provide members that have already been implemented. Therefore, you can ensure a certain amount of identical functionality with an abstract class, but cannot with an interface.
  • An abstract class can contain internal variables but interfaces cannot.

What are Interfaces ?

Interfaces are special kind of type in C#, used to define the specifications (in terms of method signatures) that should be followed by its sub-types.
Interfaces, like abstract classes, cannot be instantiated. An interface can contain a signature of methods properties and indexers. An Interface is a type whose members are all public and abstract by default.

A class implementing the interface must provide the body for the members of the interface. A class can implement more than one interface, contrary to class-inheritance where you can inherit only one class. An interface itself can inherit other interfaces.

We can declare the reference of interface type and it can point to any class implementing it.

It is a convention in C# to prefix the name of interfaces with the uppercase "I" like IDisposable, ISerializable, IEnumerator etc.

What are abstract classes and abstract methods ?

Abstract classes can simply be defined as incomplete classes. Abstract classes contains one or more incomplete methods called abstract methods. The abstract class only provides the signature or declaration of abstract methods and leaves the implementation of these methods to derived or sub-classes. Since abstract classes are incomplete they cannot be instantiated. They must be inherited in order to use their functionality. This is the reason why an abstract class can't be sealed. A class inheriting an abstract class must implement all the abstract method in the abstract class, or it too must be declared as an abstract class.

We can declare a reference of type abstract class and it can point to the objects of the classes that have inherited the abstract class

Example:
abstract class TaxCalculator
{
protected double itemprice;
protected double tax;

public abstract double CalculateTax();

public double Tax
{
get { return tax; }
}

public double ItemPrice
{
get { return itemprice; }
}
}





Class SalesTaxCalculator : TaxCalculator
{
public SalesTaxCalculator(double itemprice)
{
this.itemprice = itemprice;
}
public override double CalculateTax()
{
tax = 0.3 * itemprice;
return itemprice + tax;
}
}

class Test
{
static void Main()
{
SalesTaxCalculator salesTaxCalc = new SalesTaxCalculator(225);
double newprice = salesTaxCalc.CalculateTax();
//Or we can do it as
TaxCalculator taxCalc = new SalesTaxCalculator(225);
double tprice = taxCalc.CalculateTax();
}
}

Features of Abstract Class
  1. An abstract class cannot be instantiated.
  2. An abstract class contain abstract members as well as non-abstract members.
  3. An abstract class cannot be a sealed class because the sealed modifier prevents a class from being inherited and the abstract modifier requires a class to be inherited.
  4. A non-abstract class which is derived from an abstract class must include actual implementations of all the abstract members of parent abstract class.
  5. An abstract class can be inherited from a class and one or more interfaces.
  6. An Abstract class can have access modifiers like private, protected, internal with class members. But abstract members cannot have private access modifier.
  7. An Abstract class can have instance variables (like constants and fields).
  8. An abstract class can have constructors and destructor.
  9. An abstract method is implicitly a virtual method.
  10. Abstract properties behave like abstract methods.
  11. An abstract class cannot be inherited by structures.
  12. An abstract class cannot support multiple inheritance.
Common design guidelines for abstract classes
  1. Don't define public constructors within abstract class. Since abstract class cannot be instantiate and constructors with public access modifiers provides visibility to the classes which can be instantiated.
  2. Define a protected or an internal constructor within an abstract class. Since a protected constructor allows the base class to do its own initialization when sub-classes are created and an internal constructor can be used to limit concrete implementations of the abstract class to the assembly which contains that class.

What is Asp.Net Page Life Cycle ?

General Page Life-Cycle:
  • Page Request
  • Start
  • Initialization
  • Load
  • Postback event handling
  • Rendering
  • Unload
Life Cycle Events
  • Preinit: Raised after the start stage is complete and before the initialization stage
  • Init: Raised after all controls have been initialized and any skin settings have been applied. The Init event of individual controls occurs before the Init event of the page.
  • InitComplete: Raised at the end of the page's initialization stage. Only one operation takes place between the Init and InitComplete events: tracking of view state changes is turned on. View state tracking enables controls to persist any values that are programmatically added to the ViewState collection. Until view state tracking is turned on, any values added to view state are lost across postbacks. Controls typically turn on view state tracking immediately after they raise their Init event.
  • PreLoad: Raised after the page loads view state for itself and all controls, and after it processes postback data that is included with the Request instance.
  • Load: The Page object calls the OnLoad method on the Page object, and then recursively does the same for each child control until the page and all controls are loaded. The Load event of individual controls occurs after the Load event of the page.
  • Control Events: Use these events to handle specific control events, such as a Button control's Click event or a TextBox control's TextChanged event.
  • LoadComplete: Raised at the end of the event-handling stage.
  • PreRender: Raised after the Page object has created all controls that are required in order to render the page, including child controls of composite controls. (To do this, the Page object calls EnsureChildControls for each control and for the page.). The Page object raises the PreRender event on the Page object, and then recursively does the same for each child control. The PreRender event of individual controls occurs after the PreRender event of the page.
  • PreRenderComplete: Raised after each data bound control whose DataSourceID property is set calls its DataBind method.
  • SaveStateComplete: Raised after view state and control state have been saved for the page and for all controls. Any changes to the page or controls at this point affect rendering, but the changes will not be retrieved on the next postback.
  • Render: This is not an event; instead, at this stage of processing, the Page object calls this method on each control. All ASP.NET Web server controls have a Render method that writes out the control's markup to send to the browser. If you create a custom control, you typically override this method to output the control's markup. However, if your custom control incorporates only standard ASP.NET Web server controls and no custom markup, you do not need to override the Render method. A user control (an .ascx file) automatically incorporates rendering, so you do not need to explicitly render the control in code.
  • Unload: Raised for each control and then for the page. In controls, use this event to do final cleanup for specific controls, such as closing control-specific database connections. For the page itself, use this event to do final cleanup work, such as closing open files and database connections, or finishing up logging or other request-specific tasks.
Note:- Above information is taken from http://msdn.microsoft.com/en-us/library/ms178472.aspx