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


Main Menu

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
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.
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:-

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.
      timeout="30" />

8).Can you describe all the property set in web.config under session state?
      sqlconnectionstring="data source=;user id=<user id>;password=<password>"
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:
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:
  <sessionstate  Mode="off"/>

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?

  • 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 


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:
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)


name char(20),
dept char(10),
age INT,
salary INT,
location char(10)

Primary Key at column level:

( id INT,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)  ,

Primary Key at table level:

name char(20),
dept char(10),
age INT,
salary INT,
location char(10)


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".

( 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.

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

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


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:

name char(20),
dept char(10),
age INT,
salary INT,
location char(10) UNIQUE

Unique Key at table level:

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:

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:

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:

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')


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

To DROP a CHECK Constraint



Read More »

Recent Posts

My Blog List