/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Monday, December 21, 2015

C# Interview Question OOPS - Top 50

1.       What is OOPS?
OOPS is abbreviated as Object Oriented Programming system in which programs are considered as a collection of objects. Each object is nothing but an instance of a class.
2.       Write basic concepts of OOPS?
Following are the concepts of OOPS and are as follows:.
  1. Abstraction.
  2. Encapsulation.
  3. Inheritance.
  4. Polymorphism.
3.       What is a class?
A class is simply a representation of a type of object. It is the blueprint/ plan/ template that describe the details of an object.
4.       What is an object?
Object is termed as an instance of a class, and it has its own state, behavior and identity.
5.       What is Encapsulation?
Encapsulation is an attribute of an object, and it contains all data which is hidden. That hidden data can be restricted to the members of that class.
Levels are Public, Protected, Private, Internal and Protected Internal.
6.       What is Polymorphism?
Polymorphism is nothing but assigning behavior or value in a subclass to something that was already declared in the main class. Simply, polymorphism takes more than one form.
7.       What is Inheritance?
Inheritance is a concept where one class shares the structure and behavior defined in another class. Inheritance applied on one class is called Single Inheritance, and if it depends on multiple classes, then it is called multiple Inheritances.
8.       What are manipulators?
Manipulators are the functions which can be used in conjunction with the insertion (<<) and extraction (>>) operators on an object. Examples are endl and setw.
9.       Define a constructor?
Constructor is a method used to initialize the state of an object, and it gets invoked at the time of object creation. Rules for constructor are:
  • Constructor Name should be same as class name.
  • Constructor must have no return type.
10.   Define Destructor?
Destructor is a method which is automatically called when the object is made of scope or destroyed. Destructor name is also same as class name but with the tilde (~) symbol before the name.
11.   What is Inline function?
Inline function is a technique used by the compilers and instructs to insert complete body of the function wherever that function is used in the program source code.
12.   What is a virtual function?
Virtual function is a member function of class and its functionality can be overridden in its derived class. This function can be implemented by using a keyword called virtual, and it can be given during function declaration.
Virtual function can be achieved in C++, and it can be achieved in C Language by using function pointers or pointers to function.             
13.   What is friend function?
Friend function is a friend of a class that is allowed to access to Public, private or protected data in that same class. If the function is defined outside the class cannot access such information.
Friend can be declared anywhere in the class declaration, and it cannot be affected by access control keywords like private, public or protected.
14.   What is function overloading?
Function overloading is defined as a normal function, but it has the ability to perform different tasks. It allows creation of several methods with the same name which differ from each other by type of input and output of the function.
Example
void add(int& a, int& b);
void add(double& a, double& b);
void add(struct bob& a, struct bob& b);
15.   What is operator overloading?
Operator overloading is a function where different operators are applied and depends on the arguments. Operator,-,* can be used to pass through the function , and it has their own precedence to execute.
Example:
class complex {
double real, imag;
public:
complex(double r, double i) :
real(r), imag(i) {}
complex operator+(complex a, complex b);
complex operator*(complex a, complex b);
complex& operator=(complex a, complex b);
}
a=1.2, b=6
16.   What is an abstract class?
An abstract class is a class which cannot be instantiated. Creation of an object is not possible with abstract class, but it can be inherited. An abstract class can contain only Abstract method.
17.   What is a ternary operator?
Ternary operator is said to be an operator which takes three arguments. Arguments and results are of different data types, and it is depends on the function. Ternary operator is also called as conditional operator. Condition? First expression: Second expression;
18.   What is the use of finalize method?
Finalize method helps to perform cleanup operations on the resources which are not currently used. Finalize method is protected, and it is accessible only through this class or by a derived class.
At runtime C#, C++ destructors are automatically converted to finalize method. But in VB.NET you need to override Finalize method, since it does not support destructor.
You should not implement a Finalize method for managed objects, because the garbage collector cleans up managed resources automatically.

19.   What are different types of arguments?
A parameter is a variable used during the declaration of the function or subroutine and arguments are passed to the function, and it should match with the parameter defined. There are two types of Arguments.
  • Call by Value – Value passed will get modified only inside the function, and it returns the same value whatever it is passed it into the function.
  • Call by Reference – Value passed will get modified in both inside and outside the functions and it returns the same or different value.

20.   What is super keyword?
Super keyword is used to invoke overridden method which overrides one of its superclass methods. This keyword allows to access overridden methods and also to access hidden members of the superclass.
It also forwards a call from a constructor to a constructor in the superclass.
21.   What is method overriding?
Method overriding is a feature that allows sub class to provide implementation of a method that is already defined in the main class. This will overrides the implementation in the superclass by providing the same method name, same parameter and same return type.
22.   What is an interface?
An interface is a collection of abstract method. If the class implements an inheritance,  and then thereby inherits all the abstract methods of an interface.
23.   What is exception handling?
Exception is an event that occurs during the execution of a program. Exceptions can be of any type – Run time exception, Error exceptions. Those exceptions are handled properly through exception handling mechanism like try, catch and throw keywords.
24.   What are tokens?
Token is recognized by a compiler and it cannot be broken down into component elements. Keywords, identifiers, constants, string literals and operators are examples of tokens.
Even punctuation characters are also considered as tokens – Brackets, Commas, Braces and Parentheses.

25.   Difference between overloading and overriding?
Overloading is static binding whereas Overriding is dynamic binding. Overloading is nothing but the same method with different arguments, and it may or may not return the same value in the same class itself.
Overriding is the same method names with same arguments and returns types associates with the class and its child class.
26.   Difference between class and an object?
An object is an instance of a class. Objects hold any information, but classes don’t have any information. Definition of properties and functions can be done at class and can be used by the object.
Class can have sub-classes, and an object doesn’t have sub-objects.
27.   What is an abstraction?
Abstraction is a good feature of OOPS, and it shows only the necessary details to the client of an object. Means, it shows only necessary details for an object, not the inner details of an object. Example – When you want to switch on television, it is not necessary to show all the functions of TV. Whatever is required to switch on TV will be showed by using abstract class.
28.   What are access modifiers?
Access modifiers determine the scope of the method or variables that can be accessed from other various objects or classes. There are 5 types of access modifiers, and they are as follows:
  • Private.
  • Protected.
  • Public.
  • Friend.
  • Protected Friend.
29.   What is sealed modifiers?
Sealed modifiers are the access modifiers where it cannot be inherited by the methods. Sealed modifiers can also be applied to properties, events and methods. This modifier cannot be applied to static members. 
30.   How can we call the base method without creating an instance?
Yes, it is possible to call the base method without creating an instance, and that method should be
Static method. Doing inheritance from that class. Use Base Keyword from derived class.
31.   What is the difference between new and override?
The new modifier instructs the compiler to use the new implementation instead of the base class function. Whereas, override modifier helps to override the base class function.
32.   What are the various types of constructors?
There are three types of constructors, and they are as follows:
-          Default Constructor – With no parameters.
-          Parametric Constructor – With Parameters. Create a new instance of a class and also passing arguments simultaneously.
-          Copy Constructor – Which creates a new object as a copy of an existing object?
33.   What is early and late binding?
Early binding refers to assignment of values to variables during design time whereas late binding refers to assignment of values to variables during run time.
34.   What is ‘this’ pointer?
THIS pointer refers to the current object of a class. THIS keyword is used as a pointer which differentiates between the current object with the global object. Basically, it refers to the current object.
35.   What is the difference between structure and a class?
Structure default access type is public, but class access type is private. A structure is used for grouping data whereas class can be used for grouping data and methods. Structures are exclusively used for data and it doesn’t require strict validation, but classes are used to encapsulates and inherit data which requires strict validation.
36.   What is the default access modifier in a class?
The default access modifier of a class is Private by default.
37.   What is pure virtual function?
A pure virtual function is a function which can be overridden in the derived class but cannot be defined. A virtual function can be declared as Pure by using the operator =0.
Example -
Virtual void function1() // Virtual, Not pure
Virtual void function2() = 0 //Pure virtual
38.   What are all the operators that cannot be overloaded?
Following are the operators that cannot be overloaded -.
  1. Scope Resolution (:: )
  2. Member Selection (.)
  3. Member selection through a pointer to function (.*)

39.   What is dynamic or run time polymorphism?
Dynamic or Run time polymorphism is also known as method overriding in which call to an overridden function is resolved during run time, not at the compile time. It means having two or more methods with the same name, same signature but with different implementation.
40.   Do we require parameter for constructors?
No, we do not require parameter for constructors.
41.   What is a copy constructor?
This is a special constructor for creating a new object as a copy of an existing object. There will be always only one copy constructor that can be either defined by the user or the system.
42.   What does the keyword virtual represented in the method definition?
It means, we can override the method.
43.   Whether static method can use non static members?
False
44.   What are base class, sub class and super class?
Base class is the most generalized class, and it is said to be a root class.
Sub class is a class that inherits from one or more base classes.
Super class is the parent class from which another class inherits.
45.   What is static and dynamic binding?
Binding is nothing but the association of a name with the class. Static binding is a binding in which name can be associated with the class during compilation time, and it is also called as early Binding.
Dynamic binding is a binding in which name can be associated with the class during execution time, and it is also called as Late Binding.
46.   How many instances can be created for an abstract class?
Zero instances will be created for an abstract class.
47.   Which keyword can be used for overloading?
Operator keyword is used for overloading.
48.   What is the default access specifies in a class definition?
Private access specifier is used in a class definition.
49.   Which OOPS concept is used as reuse mechanism?
Inheritance is the OOPS concept that can be used as reuse mechanism.
50.   Which OOPS concept exposes only necessary information to the calling functions?
Data Hiding / Abstraction

Read More »

Thursday, December 10, 2015

Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format

Part-2 SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database Sending Mail in HTML Format


I am just providing the steps that are enough to give you the basic undrstanding to create HTML Format


-- Creating HTML Format Using Stored Procedures for sending email from database the following Stored Procedures Required


-- 1) Create DataSourceCheck stored procedures

CREATE PROCEDURE [dbo].[DataSourceCheck]

@dataSource varchar (100) = NULL,

@db varchar(50) = NULL output,

@table varchar(100) = NULL output

AS

BEGIN

DECLARE

@buffer varchar(100),

@object varchar(100),

@objectId bigint,

@schema varchar(50),

@rcd_cnt int,

@tableHtml varchar(200),

@sql nvarchar(1000)

SET @buffer = @dataSource;

--cannot accesss a local temp table. Return.

IF SUBSTRING (@buffer, 1, 1) = '#' and SUBSTRING (@buffer, 2, 1) <> '#'

BEGIN

--use LEFT 25 to make sure the local temp table name isn't too long for the @table varchar(100) variable.

SET @table = '
Table ' + LEFT (@dataSource, 25) + ' is a local temp table. Must use a global temp or permanent table.
';

RETURN;

END;

--set up the object name in the right format so you can check the OBJECT_ID

ELSE IF (SUBSTRING (@buffer, 1, 2) = '##')

BEGIN

SET @db = 'tempdb';

SET @table = @dataSource;

SET @object = @db + '..' + @table; --need to include tempdb so OBJECT_ID finds the temp table

END;

ELSE

BEGIN

--deal with schema

SET @db = SUBSTRING (@buffer, 1, charindex ('.', @buffer) - 1);

SET @buffer = replace (@buffer, @db + '.', '');

IF SUBSTRING (@buffer, 1, 1) = '.'

BEGIN

SET @schema = '..';

SET @buffer = replace (@buffer, '.', '');

END

ELSE

BEGIN

SET @schema = SUBSTRING (@buffer, 1, charindex ('.', (@buffer)) - 1);

SET @buffer = replace (@buffer, @schema + '.', '');

END

SET @table = @buffer;

SET @object = @dataSource;

END;

--does our data source exist? Check the object_id. If object does not exist, return.

SET @objectId = OBJECT_ID (@object, 'U');

IF @objectId is NULL

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' does not exist or is improperly qualified.
';

RETURN;

END;

--we have a valid data source. Check that it has rows and notify if empty.

SET @sql = 'SELECT @rcd_cnt = count(*) from ' + @dataSource;

EXEC master.sys.sp_executesql @sql, N'@rcd_cnt int OUTPUT', @rcd_cnt OUTPUT;

IF @rcd_cnt = 0

BEGIN

SET @db = NULL;

SET @table = '
Table ' + @dataSource + ' is empty.
';

RETURN;

END;

END

Read More »

SQL SERVER 2008 Configure Database Mail – Send Email From SQL Database Using sp_send_dbmail

SQL SERVER 2008 Configure Database Mail – Send Email From SQL Database


In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:

Create Profile and Account

Configure Email

Send Email

I am just providing the steps that are enough to give you the basic undrstanding to send a mail from the database to your mail hosting server.

Here for example i am using the Gmail SMTP server details to send a test e-mail.


-- 1) First Enable the Database Mail XPs:

sp_helptext sysmail_add_account_sp


USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO


-- 2) Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail',
@description = 'Mail account for administrative e-mail.',
@email_address = 'ycuisthebest@gmail.com',
@replyto_address = 'ycuisthebest@gmail.com',
@display_name = 'DBA Mail',
@mailserver_name = 'smtp.gmail.com',
@port = 25,
@username = 'ycuisthebest@gmail.com',
@password = 'xxxxxx',
@enable_ssl = 1


-- 3) Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail',
@description = 'Profile used for administrative mail1.'


-- 4) Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail',
@account_name = 'DBA Mail',
@sequence_number =1


-- 5) Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBA Mail',
@principal_name = 'public',
@is_default = 1


-- Send A Test E-Mail With Plain Text
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail',
@recipients='ycuisthebest@gmail.com',
@subject='My First Test message',
@body='Message Sent Successfully'


-- Send A Test E-Mail with query result here i am just sending the current date as a result set
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail1',
@recipients='yogeshsangrajani@gmail.com',
@subject = 'Current date time Values',
@query = N'NOCOUNT ON SELECT getdate();',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Memory Values.txt',
@body='Message with date Sent Successfully'


--  Send A Test E-Mail with query result in HTML Format 
DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)  

SET @TableTail = '
' ;
SET @TableHead = '' + '' + '' + '' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + '
    + '
PO Date
'
    + '
ID
'
    + '
Name
'
    + '
Department
'
    + '
Age
'
    + '
Salary
'
    + '
Location
' ;

SET @Body = ( SELECT    td = E.ID, '',
                        td = E.Name, '',
                        td = E.Dept, '',
                        td = E.Age,'',
                        td = E.Salary,'',
                        td = E.Location, ''                      
              FROM      employee E                                      
            FOR   XML RAW('tr'),
                  ELEMENTS
            )  
SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail
  
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail1',
@recipients='yogeshupr@gmail.com',  
@subject='Query Result',
@body=@Body,
@body_format = 'HTML' ;   


Some other aspect that will definitely required in the testing phase
You can update the smtp server setting within the following TABLE msdb.dbo.sysmail_server

SELECT * FROM msdb.dbo.sysmail_server
UPDATE msdb.dbo.sysmail_server SET enable_ssl =1, port=25 --etc.,


Usefull table that are required to monitoring the send mail details
Quick troubleshooting queries for Database Mail

SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_account
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_configuration
SELECT * FROM msdb.dbo.sysmail_mailitems


-- Check to see if the service broker is enabled (should be 1):


SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'


-- Check to see if Database Mail is started in the msdb database:


EXECUTE msdb.dbo.sysmail_help_status_sp


-- Start Database Mail if necessary:


EXECUTE msdb.dbo.sysmail_start_sp


-- Check the status of the mail queue:


EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'


-- Check the Database Mail event logs:


SELECT * FROM msdb.dbo.sysmail_event_log


-- Check the mail queue for the status of all items (including sent mails):


SELECT * FROM msdb.dbo.sysmail_allitems


It Can Happens: Following Error Solution Link Given Below:


The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-12-10T10:24:09). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at). )


-- Usefull References: http://www.technologycrowds.com/2015/05/smtp-server-authentication-server-response-5-5-1-Authentication-Required-in-Gmail.html


-- Code Project Using Wizard: http://www.codeproject.com/Articles/29060/SQL-SERVER-Configure-Database-Mail-Send-Email
Read More »

SQL Server Generate Week Dates for a Year and Month in SQL Server

SQL Server Generate Week Start Dates End Dates for a Year or Month in SQL Server

Sometimes, you might require Start & End dates for all the weeks in a given year or month to generate week-wise report starting from Monday to Sunday

I have developed a Table-Valued function where you have to pass year and it would return a table with all the weeks and its Start & End Dates for a given year.


Getting Month Week Start Date and End Date

- - Totals by Week 

CREATE FUNCTION dbo.GetWeekDatesForMonth
        (@Year SMALLINT, @Month TINYINT)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN WITH 
 cteMonth AS   (SELECT  MonthStart     = DATEADD(mm,(@Year-1900)*12+@Month-1,0)
                       ,MonthEnd       = DATEADD(mm,(@Year-1900)*12+@Month,0)-1)
,cteWeeks AS   (SELECT  MonthStart
                       ,MonthEnd
                       ,FirstWeekStart = DATEADD(dd,DATEDIFF(dd,0,MonthStart)/7*7,0)
                       ,LastWeekStart  = DATEADD(dd,DATEDIFF(dd,0,MonthEnd  )/7*7,0)
                  FROM cteMonth)

 SELECT  [Week]       = 'Week'+LEFT(ca.Wk+1,1)
        ,StartDate    =  CAST(
                            CASE 
                            WHEN ca.Wk > 0 
                            THEN DATEADD(dd,Ca.Wk*7,FirstWeekStart) 
                            ELSE MonthStart
                            END
                        AS DATE)
        ,EndDate      = CAST(
                            CASE
                                WHEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart) <= MonthEnd 
                                THEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart) 
                                ELSE MonthEnd 
                            END 
                        AS DATE)
   FROM cteWeeks
  CROSS APPLY (SELECT TOP (DATEDIFF(dd,FirstWeekStart,LastWeekStart)/7+1)
                      t.N
                 FROM (VALUES (0),(1),(2),(3),(4),(5))t(N))ca(Wk);


Using GetWeekDatesForMonth 
-- Lets' execute the script and check the output 

SELECT * FROM dbo.GetWeekDatesForMonth(2015,12)


-- OR You can use the following function to get week start and end date

CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(
    @Year  SMALLINT
    , @Month TINYINT
)
RETURNS @WeekDates TABLE (
    Week  VARCHAR(5)
    , StartDate DATE
    , EndDate DATE
)
AS
BEGIN
    DECLARE @MonthStartdate        DATE
            , @MonthEnddate        DATE
            , @WeekStartDate    DATE
            , @WeekEndDate        DATE
            , @ctr                INT = 0

    SET @MonthStartdate = CAST(CAST(@Year*100+@Month AS VARCHAR)+'01' AS DATE)
    SET @MonthEnddate = DATEADD(d,-1,DATEADD(m,1, @MonthStartdate))

    SET @WeekStartDate = @MonthStartdate
    SET @WeekEndDate = DATEADD(d, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)

    WHILE @WeekStartDate < @MonthEnddate
    BEGIN
        SET @ctr = @ctr + 1
        SET @WeekEndDate = (CASE WHEN @WeekEndDate > @MonthEnddate THEN @MonthEnddate ELSE @WeekEndDate END)

        INSERT INTO @WeekDates
        SELECT 'Week' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate

        SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)
        SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)
    END
    RETURN
END

- -Lets' execute the script and check the output 
SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,12)


Generate Week Dates for a Year in SQL Server


CREATE FUNCTION dbo.udf_GetWeekDatesForYear(
    @Year  SMALLINT 
)
RETURNS @WeekDates TABLE (
    Week  VARCHAR(10)
    , StartDate DATE
    , EndDate DATE
)
AS
BEGIN
    DECLARE @YearStartdate  DATE
            , @YearEnddate  DATE
            , @WeekStartDate DATE
            , @WeekEndDate  DATE
            , @ctr    INT = 0

    SET @YearStartdate = CAST(CAST(@Year AS VARCHAR)+'0101' AS DATE)
    SET @YearEnddate = DATEADD(day,-1,DATEADD(year,1, @YearStartdate))

    SET @WeekStartDate = @YearStartdate
    SET @WeekEndDate = DATEADD(day, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)

    WHILE @WeekStartDate < @YearEnddate
    BEGIN
        SET @ctr = @ctr + 1
        SET @WeekEndDate = (CASE WHEN @WeekEndDate > @YearEnddate THEN @YearEnddate ELSE @WeekEndDate END)

        INSERT INTO @WeekDates
        SELECT 'Week-' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate

        SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)
        SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)
    END
    RETURN
END

- -Lets' execute the script and check the output 

SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)
Read More »

Generate Week Dates for a Month in SQL Server - IT Developer Zone

Read More »

Thursday, July 23, 2015

Interview Questions ASP.NET VIEW STATE


1). What is View State in Asp.net?
Ans: View state is nothing but a method that the ASP.NET use to preserve page and control values between postbacks. When the HTML markup for the page is rendered, the current state of the page and values that must be retained during postback are serialized into base64-encoded strings. This information is then put into the view state hidden field.

2). View state is client-side or server side state management techenique?
Ans: View state is client-side state management techenique

3). What are the client-side state management techenique supported by ASP.NET?
Ans: View state
Control state
Hidden fields
Cookies
Query strings

4). View state is used by Asp.net page atomatically or we need to apply it manuly?
Ans: View state is used automatically by the ASP.NET page framework to persist information that must be preserved between postbacks.


5). When you can use(take advantage of vs) view state?
or What you can do by use view state?

Ans: a) Keep values between postbacks without storing them in session state or in a user profile.
b) Store the values of page or control properties that you define.
c) Create a custom view state provider that lets you store view state information in a SQL Server database or in another data store.

6). What are the advantages of using view state?
Ans: No server resources are required : The view state is contained in a structure within the page code.
Simple implementation : View state does not require any custom programming to use. It is on by default to maintain state data on controls.
Enhanced security features : The values in view state are hashed, compressed, and encoded for Unicode implementations, which provides more security than using hidden fields.

7). What are the limitations of view state?
Ans: Limitations:
Because view state is stored in the page, it results in a larger total page size.
ASP.NET uses view state only with page and control properties.
View state isn't a good place to store sensitive information that the client shouldn't be allowed to see.

Read More »

Interview Questions ASP.NET State Management Techniques


Here are the top 10 questions answers for both experienced and beginners asp.net developers.

1).What is state management?
Ans: State management is the process by which you maintain state and page information over multiple requests for the same or different pages.

2).Http is stateless, What does this mean?
Ans: Stateless protocol is a communications protocol that treats each request as an independent transaction that is unrelated to any previous request so that the communication consists of independent pairs of requests and responses.

3).What is Session?
Ans: We know that Http is stateless, means when we open a webpage and fill some information and then move to next page then the data which we have entered will lost.
It happed do to Http protocol stateless nature. So here session come into existence, Session provide us the way of storing data in server memory. So you can store your page data into server
memory and retrieve it back during page postbacks.

4).What are the Advantage and disadvantage of Session?
Ans: Advantages:
Session provide us the way of maintain user state/data.
It is very easy to implement.
One big advantage of session is that we can store any kind of object in it. :eg, datatabe, dataset.. etc
By using session we don't need to worry about data collesp, because it store every client data separately.
Session is secure and transparent from the user.
Disadvantages:
Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
Overhead involved in serializing and de-serializing session data, because in the case of StateServer and SQLServer session modes, we need to serialize the objects before storing them.

5).What is Session ID in Asp.net?
Ans: Asp.Net use 120 bit identifier to track each session. This is secure enough and can't be reverse engineered. When client communicate with server, only session id is transmitted, between them. When client request for data, ASP.NET looks on to session ID and retrieves corresponding data.

6).By default where the sessions ID's are stored ?
Ans: By default, the unique identifier for a session is stored in a non-expiring session cookie in the browser. You can specify that session identifiers not be stored in a cookie by setting the cookieless attribute to true in the sessionState configuration element.
We can also configure our application to store it in the url by specifying a "cookieless" session
The ASP Session cookie has this format:-
ASPSESSIONIDACSSDCCC=APHELKLDMNKNIOJONJACDHFN


7).Where does session stored if cookie is disabled on client’s machine?
Ans: If you want to disable the use of cookies in your ASP.NET application and still make use of session state, you can configure your application to store the session identifier in the URL instead of a cookie by setting the cookieless attribute of the sessionState configuration element to true, or to UseUri, in the Web.config file for your application.
The following code example shows a Web.config file that configures session state to use cookieless session identifiers.
Code:
<configuration>
  <system.web>
    <sessionState
      cookieless="true"
      regenerateExpiredSessionId="true"
      timeout="30" />
  </system.web>
</configuration>


8).Can you describe all the property set in web.config under session state?
Ans:
Code:
<configuration>
  <sessionstate
      mode="inproc"
      cookieless="false"
      timeout="20"
      sqlconnectionstring="data source=127.0.0.1;user id=<user id>;password=<password>"
      server="127.0.0.1"
      port="42424"
  />
</configuration>
Mode: The mode setting supports three options: inproc, sqlserver, and stateserver. As stated earlier, ASP.NET supports two modes: in process and out of process. There are also two options for out-of-process state management: memory based (stateserver), and SQL Server based (sqlserver). We'll discuss implementing these options shortly.
Cookieless: The cookieless option for ASP.NET is configured with this simple Boolean setting.
Timeout: This option controls the length of time a session is considered valid. The session timeout is a sliding value; on each request the timeout period is set to the current time plus the timeout value
Sqlconnectionstring: The sqlconnectionstring identifies the database connection string that names the database used for mode sqlserver.
Server: In the out-of-process mode stateserver, it names the server that is running the required Windows NT service: ASPState.
Port: The port setting, which accompanies the server setting, identifies the port number that corresponds to the server setting for mode stateserver.

9).What are Session Events?
Ans: There are two types of session events available in ASP.NET:
Session_Start
Session_End
You can handle both these events in the global.asax file of your web application. When a new session initiates, the session_start event is raised, and the Session_End event raised when a session is abandoned or expires.

10).How you can disable session?
Ans: If we set session Mode="off" in web.config, session will be disabled in the application. For this, we need to configure web.config the following way:
Code:
<configuration>
  <sessionstate  Mode="off"/>
</configuration>


Read More »

Wednesday, July 8, 2015

SQL Server interview questions | triggers all about triggers


Explain Triggers?

A trigger is a special type of event driven stored procedure. 

It gets initiated when Insert, Delete or Update event occurs. 

It can be used to maintain referential integrity. 

A trigger can call stored procedure.

Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

You can specify which trigger fires first or fires last using sp_settriggerorder.

Triggers can't be invoked on demand.

They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens.

Triggers are generally used to implement business rules, auditing.

Triggers can also be used to extend the referential integrity checks

Trigger is one of the database objects and executes set of Transact SQL Statements Automatically in response to an event (INSERT, UPDATE,DELETE etc) with in database.
Generally Triggers are used to implement business rules.

How many triggers you can have on a table?
A table can have up to 12 triggers defined on it.

How many types of triggers are there in Sql Server 2005?

There are two types of triggers
• Data Manipulation language (DML) triggers
• Data Definition language (DDL) triggers

DML triggers (implementation) will run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view.

DDL triggers will run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks

What are the different modes of firing triggers?

After Trigger: An AFTER trigger fires after SQL Server completes all actions successfully

Instead of Triggers: An INSTEAD OF trigger causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire.


Describe triggers features and limitations.


Trigger features:-

1. Can execute a batch of SQL code for an insert, update or delete command is executed

2. Business rules can be enforced on modification of data


Trigger Limitations:-

1. Does not accept arguments or parameters


2. Cannot perform commit or rollback


3. Can cause table errors if poorly written



What are the instances when triggers are appropriate?

Answer
  • When security is the top most priority. i.e. to allow unauthorized access
  • When backups are essential
  • When Maintenance is desired. Triggers can be fired when any error message is logged
  • Keeping the database consistent.

What is Nested Trigger?
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.


Syntax for viewing, dropping and disabling 

triggers



View trigger:

A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table.

Sp_helptrigger table_name

Drop a trigger

DROP TRIGGER Trigger_name

Disable a trigger:-

DISABLE TRIGGER [schema name] trigger name ON [object, database or ALL server ]



Read More »

SQL Server interview questions | data integrity constraints


SQL Server interview questions | data integrity constraints

What is primary key?

A Primary Key in a table identifies each and every row uniquely.
It should not allow null values.
We could assign primary key on only column or more than one column also.


What is the difference between primary key and unique key?

Primary should not allow null; where as unique key will allow nulls.
By default Primary key is created as clustered index; whereas unique key is created as non clustered index.


What are the different levels of data integrity in SQL Server?

Entity Integrity, Domain Integrity, Referential integrity
Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records.


Explain different constraints to maintain data integrity in SQL Server?

Check constraints: 
Check constraints will be useful to limit the range of possible values in a column.
We could create check constraints at two different levels

a) Column-level check constraints are applied only to the column and cannot reference data in another other column

b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables

Default constraints:
Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.

Unique constraints:
A unique constraint restricts a column or combination of columns from allowing duplicate values.

Primary key constraints:
Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.

Foreign key constraints:
Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.
Read More »

SQL Integrity Constraints Foreign Key Not Null Unique Check

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.
Enforcing data integrity ensures the quality of the data in the database. 

For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value.

Data integrity falls into these categories:

Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

Domain Integrity

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

Referential Integrity

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.

User-Defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

The Constraints available in SQL are Foreign Key, Not Null, Unique, and Check.

Constraints can be defined in two ways

1) The constraints can be specified immediately after the column definition. This is called column-level definition.

2) The constraints can be specified after all the columns are defined. This is called table-level definition.


1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

COLUMN name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

column_name1, column_name2 are the names of the columns which define the primary Key.
The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at column level:
CREATE TABLE employee
( id INT PRIMARY KEY, 
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)
);

or

CREATE TABLE employee
( id INT CONSTRAINT emp_id_pk PRIMARY KEY,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)
);

Primary Key at column level:

CREATE TABLE employee
( id INT,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)  ,
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);

Primary Key at table level:

CREATE TABLE employee
(id INT NOT NULL,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)
);

ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)

2) SQL Foreign key or Referential Integrity:

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items".

CREATE TABLE product
( product_id INT CONSTRAINT pd_id_pk PRIMARY KEY,
product_name char(20),
supplier_name char(20),
unit_price INT);

CREATE TABLE order_items
( order_id INT CONSTRAINT od_id_pk PRIMARY KEY,
product_id INT CONSTRAINT pd_id_fk REFERENCES product(product_id),
product_name char(20),
supplier_name char(20),
unit_price INT
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id INT,
product_id INT,
product_name char(20),
supplier_name char(20),
unit_price INT,
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like.

CREATE TABLE employee
(id INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
mgr_id INT REFERENCES employee(id),
salary INT,
location char(10)
);

3) SQL Not Null Constraint:

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee
( id INT,
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age INT,
salary INT,
location char(10)
);

ALTER COLUMN DATA TYPE: -

ALTER TABLE employee ALTER COLUMN name char(20) NOT NULL

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee
( id INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10) UNIQUE
);

Unique Key at table level:

CREATE TABLE employee
( id INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10) CONSTRAINT loc_un UNIQUE
);

5) SQL Check Constraint:

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.

The CHECK constraint is used to limit the value range that can be placed in a column.
Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee
( id INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
gender char(1) CHECK (gender in ('M','F')),
salary INT,
location char(10)
);

Check Constraint at table level:

CREATE TABLE employee
( id INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
gender char(1),
salary INT,
location char(10),
CONSTRAINT gender_ck CHECK (gender in ('M','F'))
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

SQL CHECK Constraint on ALTER TABLE

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

Read More »

Recent Posts

My Blog List