The blog has moved to the new site F10Debug.com

Wednesday, April 20, 2011

Having Clause vs Where Clause




Difference between having and where clause:

“A query can contain both a WHERE clause and a HAVING clause.” WHERE comes 1st then Having comes if they are in one sql query."

Having clause:
  • Operates on Groups. 
  • used only with the SELECT statement.
  • Is used after GROUP BY clause.

Where clause:


  • Operates on individual rows.
  • Where Clause can be used other than Select statement also.
  •   Where is used before GROUP BY clause




Stored Procedure vs User Defined Functions.


Functions :  
  • Can be used with Select statement. 
  •  Not returning output parameter.
  • Returns table variables.  
  • You can join UDF. 
  • Functions are compiled and executed at run time.  
  • Functions cannot affect the state of the database.  
  • Function can not change server environment.

Stored Procedure: 

  • Have to use EXEC or EXECUTE.
  • Return output parameter.
  • Won’t return Table Variables 
  • You can not join SP.
  • Stored procedures are stored in parsed and compiled format in the database. 
  • Stored Procedures can affect the state of the databases
  • Stored procedures can change server environment

Thursday, April 14, 2011

Difference between delete and truncate

I have attended many interviews in my 4 years of experience and all time this questions asked to me by interviewer. Many times this would be turning point of interview. so thought to give clear idea to user.

Difference between delete and truncate in SQL Server


Delete
Truncate
1. DML Statement.
1. DDL Statement.
2. It maintains transaction logs.
2. It does not maintain transaction logs.
3. So It can be rollback.
3. It cannot be rollback.
4. We can write where condition with delete clause.
4.  Truncate does not allow where condition.
5. It does not reset identity column.
5. It resets identity column.
6. Activates triggers.
6. Does not activate triggers.
7. Does provide no of rows affected.
7. It does not give no of rows affected.
8. If you need to delete the entire records of the Table, is pretty recommended to use the Truncate instruction, otherwise you can obtain time out error for example.

According to me all the above points are clear expect point number 4.

Explanation for point 5:

Suppose we have table name “Employee” and it contains 5 rows.

Case Delete) 
If we delete all the records of table, and enters new row into table it will start from 6 instead of 1. It means it does not reset identity column.

Case Truncate) 
If we truncate table, and enters new row into table it will start from 1 again. So it means that truncate command resets identity column.


If any one knows better explanation please suggest ....

Wednesday, April 13, 2011

What is the difference between an abstract class and interface?

Feature
Interface
Abstract class
Declaration and implementation of Methods
Contain only declaration of functions and methods. It does not provide implementation of methods.
We Can provide default implementation of methods.
Implement Every Method or Override every method
The derived class must implement all the methods of interface.
Not necessary to implement all methods.
Fields and Constants
Fields can not be define
Fields can be define.
Access Modfiers
An interface cannot have access modifiers for the functions, properties etc everything is assumed as public
An abstract class can contain access modifiers for the functions, properties

Monday, April 4, 2011

Three tier architecture in asp.net





Three Tier Architecture in Asp.net
      

       3-Tier architecture is client server architecture consists of three main layers which are separate from each other.
  
  • Presentation Layer: Contains actual display of pages like Html and .aspx etc, where data is presented to user or input is taken from user.   
  • Business Access Layer (BAL): Also called as Business logic layer, Contains business logic validations or calculations related with the data. 
  • Data Access Layer (DAL): It contains methods that helps BAL to connect to database and perform required action e.g.: insert, update, delete etc. 

-->
Implementation of three tier architecture:      
 
-->
I have created one project as shown above name as 3-tier. For implementation of three tier architecturewe need to separate all three layers,  so I have created business layer (AddBAL.cs) , Data access layer (AddDAL.cs) and presentation layer (Default.aspx) separately. We need to just reference BAL in presentation layer.
-->
Data Access Layer (DAL) :
AddDAL.cs
using System;
using System.Data;
using System.Configuration;
public class AddDAL

    {

        string conStr = ConfigurationManager.ConnectionStrings
                        ["ConnectionStringName"].ToString();

       // Add records into database

        public int AddMethod(string fName, string lName)

        {

            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();

            SqlCommand Cmd = new SqlCommand("AddData", conn);


Cmd.CommandType = CommandType.StoredProcedure;

            try

            {

                Cmd.Parameters.AddWithValue("@fName", fName);

                Cmd.Parameters.AddWithValue("@lName", lName);

                return Cmd.ExecuteNonQuery();

            }

            catch

            {

                throw;

            }

            finally

            {

                Cmd.Dispose();

                conn.Close();

                conn.Dispose();

            }
        } 
-->
Business Access Layer (BAL):



AddBAL.cs
 
using System;


using System.Data;


using System.Configuration;


using System.Web;


    // Summary description for PersonBAL3
 
    public class AddBAL
    {
        // Add records into database

        public int AddMethod(string fName, string lName)
        {

            AddDAL ObjAdd = new AddDAL ();
            try
            {
                return ObjAdd. AddMethod (fName, lName);
            }
            catch
            {
                throw;
            }

            finally
            {
 
                ObjAdd = null;
 
              }           }   }
 
-->
Presentation Layer :  
Default.aspx 
 
Default.aspx.cs
protected void AddRecords(object sender, EventArgs e)

    {
        int intTotal = 0;

        //insert records

        AddBAL ObjBAL = new AddBAL ();

        string firstName = txtFName.Text;

        string lastName = txtLName.Text;
        
        try
        {
            intTotal = ObjBAL.AddMethod(fName, lName)
         }

        catch (Exception Ex)

        {

            lblMessage.Text = Ex.Message.ToString();

        }

        finally

        {

            ObjBAL = null;

        }        
    }
-->

        Friday, April 1, 2011

        Event/Page Life cycle in asp.net


        Event Life cycle of ASP.NET 2.0

        To track the flowing of events in web application, you need to add “trace = true” in page directive as shown below.
               <% @Page Trace=”true”%>
        PreInit
        1)    Entry point of page life cycle.
        2)    We can create or re-create dynamic controls.
        3)    We can change master page dynamically.
        4)    We can change theme properties dynamically in this control.
        Init -
        1)    All Control are initialized in this event
        2)    The init event of each controls occurs first then init event for page occurs.

        Init Complete –
        1)    Page is initialized.
        2)    Use this event to make changes in view state that you want to make sure are affected after the next post back.

        PreLoad
        1)    This event is called before the loading of the page is completed. 

        Load
        1)    This event is raised for the Page first and then for all child controls.
        2)    The view state can be accessed at this stage.
        3)    This event indicates that the controls have been fully loaded.

        LoadComplete -
        1) This event signals indicates that the page has been loaded in the memory.
        2) It also marks the beginning of the rendering stage.

        PreRender
        1)    If you need to make any final updates to the contents of the controls or the page, then use this event. 
        2)    It first fires for the page and then for all the controls.

        PreRenderComplete
                 1) Is called to explicitly state that the PreRender phase is completed. 

        SaveStateComplete
        1)   In this event, the current state of the control is completely saved to the ViewState.
                          
        Unload
                          1)  This event is typically used for closing files and database connections.


        Stages and corresponding events in the life cycle of the ASP.NET page cycle:

         
        Stage
        Events/Method
        Page Initialization
        Page_Init
        View State Loading
        LoadViewState
        Postback data processing
        LoadPostData
        Page Loading
        Page_Load
        PostBack Change Notification
        RaisePostDataChangedEvent
        PostBack Event Handling
        RaisePostBackEvent
        Page Pre Rendering Phase
        Page_PreRender
        View State Saving
        SaveViewState
        Page Rendering
        Page_Render
        Page Unloading
        Page_UnLoad