Q1. What is WCF?
WCF stands for Windows Communication Foundation. It is a Software development kit for developing services on Windows. WCF is introduced in .NET 3.0. in the System.ServiceModel namespace. WCF is based on basic concepts of Service oriented architecture (SOA)
Q2. What is endpoint in WCF service?
The endpoint is an Interface which defines how a client will communicate with the service. It consists of three main points: Address,Binding and Contract.
Q3. Explain Address,Binding and contract for a WCF Service?
Address:Address defines where the service resides.
Binding:Binding defines how to communicate with the service.
Contract:Contract defines what is done by the service.
Q4. What are the various address format in WCF?
a)HTTP Address Format:--> http://localhost:
b)TCP Address Format:--> net.tcp://localhost:
c)MSMQ Address Format:--> net.msmq://localhost:
Q5. What are the types of binding available in WCF?
A binding is identified by the transport it supports and the encoding it uses. Transport may be HTTP,TCP etc and encoding may be text,binary etc. The popular types of binding may be as below:
a)BasicHttpBinding
b)NetTcpBinding
c)WSHttpBinding
d)NetMsmqBinding
Q6. What are the types of contract available in WCF?
The main contracts are:
a)Service Contract:Describes what operations the client can perform.
b)Operation Contract : defines the method inside Interface of Service.
c)Data Contract:Defines what data types are passed
d)Message Contract:Defines wheather a service can interact directly with messages
Q7. What are the various ways of hosting a WCF Service?
a)IIS b)Self Hosting c)WAS (Windows Activation Service)
Q8. What is the proxy for WCF Service?
A proxy is a class by which a service client can Interact with the service.
By the use of proxy in the client application we are able to call the different methods exposed by the service
Q9. How can we create Proxy for the WCF Service?
We can create proxy using the tool svcutil.exe after creating the service.
We can use the following command at command line.
svcutil.exe *.wsdl *.xsd /language:C# /out:SampleProxy.cs /config:app.config
Q10.What is the difference between WCF Service and Web Service?
a)WCF Service supports both http and tcp protocol while webservice supports only http protocol.
b)WCF Service is more flexible than web service.
Q11.What is DataContract and ServiceContract?Explain
Data represented by creating DataContract which expose the
data which will be transefered /consumend from the serive
to its clients.
**Operations which is the functions provided by this
service.
To write an operation on WCF,you have to write it as an
interface,This interface contains the "Signature" of the
methods tagged by ServiceContract attribute,and all methods
signature will be impelemtned on this interface tagged with
OperationContract attribute.
and to implement these serivce contract you have to create
a class which implement the interface and the actual
implementation will be on that class.
Code:
[ServiceContract]
Public Interface IEmpOperations
{
[OperationContract]
Decimal Get EmpSal(int EmpId);
}
Class MyEmp: IEmpOperations
{
Decimal Get EmpSal()
{
// Implementation of this method.
}
}
1)HOW TO READ XML:
C# XElement
XElement loads and parses XML. It allows you to remove lots of old code and eliminate the possibility of bugs and typos. Here we see an example of XElement from LINQ. We use it to load an XML document. We then query it using expressions in the C# language.
Here we look at how you can use the static Load method on an XML document with XElement. XElement and XDocument make using XML easy. To load an XML document into memory you only need one line of code.
Then: You can use methods on the XElement object to access the elements.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Linq;
namespace sampleTest
{
public partial class XElementExample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Example();
}
public void Example()
{
XElement _x;
_x = XElement.Load(@"D:\myFile\myFile.xml");
try
{
// Get pages matching the title specified.
string t = "Alphanumeric Sorting in C#";
var v = (from page in _x.Elements("SitePage")
where t == page.Element("Title").Value
select page).ToList();
var k = (from page in _x.Elements("SitePage")
select page).ToList();
// Get category of first page matched.
string c = v.First().Element("Category").Value;
string d = k.First().Element("Category").Value;
// Count number of elements with that category element value.
int count = (from p in _x.Elements("SitePage")
where p.Element("Category").Value == c &&
p.Element("Visibility").Value == "Regular"
select p).Count();
}
catch (Exception ex){ }}}
}
The following code reads the Authors.xml file and loops through authors and displays the contents.
XElement allData = XElement.Load("Authors.xml");
if (allData != null)
{
IEnumerable<XElement> authors = allData.Descendants("Author");
foreach(XElement author in authors)
Console.WriteLine((string)author);
}
ENDPOINT IN WCF
Every service must have Address that defines where the service resides, Contract that defines what the service does and a Binding that defines how to communicate with the service. In WCF the relationship between Address, Contract and Binding is called Endpoint.
The Endpoint is the fusion of Address, Contract and Binding.
Explain address,binding and contract for a wcf service
Address: The address uniquely identifies the endpoint and tells potential consumers of the service where it is located. It is represented in the WCF object model by the EndpointAddress class
Binding: The binding specifies how to communicate with the endpoint. This includes:
The transport protocol to use (for example, TCP or HTTP).
The encoding to use for the messages (for example, text or binary).
The necessary security requirements (for example, SSL or SOAP message security).
Contracts: The contract outlines what functionality the endpoint exposes to the client. A contract specifies:
What operations can be called by a client.
The form of the message.
The type of input parameters or data required to call the operation.
What type of processing or response message the client can expect.
1- What is jQuery ?
It's very simple but most valuable Question on jQuery means jQuery is a fast and concise JavaScript Library that simplifies HTML document traversing, animating, event handling, and Ajax interactions for rapid web development. jQuery is designed to change the way that you write JavaScript. Jquery is build library for javascript no need to write your own functions or script jquery all ready done for you
2- How you will use Jquery means requirement needed for using jquery?
Nothing more need to do just olny download jquery library(.js file) from any of the jquery site Download jquery and just linked with your html pages like all other javascript file
like below :
Code:
< script src="jquery.js" language="javascript" type="text/javascript">
3- what the use of $ symbol in Jquery?
$ Symbol is just replacement of jquery means at the place of $ you may use jquery hence $ symbol is used for indication that this line used for jquery
4- How do you select an item using css class or ID and get the value by use of jquery?
If an element of html like < div> , < p> or any tag have ID MyId and class used MyClass then we select the element by below jquery code
Code:
$('#MyId') for ID and for classs $('.MyClass')
and for value
Code:
var myValue = $('#MyId').val();
// get the value in var Myvalue by id
Or for set the value in selected item
Code:
$('#MyId').val("print me");
// set the value of a form input
5- How to get the server response from an AJAX request using Jquery?
When invoking functions that have asynchronous behavior We must provide a callback function to capture the desired result. This is especially important with AJAX in the browser because when a remote request is made, it is indeterminate when the response will be received.
Below an example of making an AJAX call and alerting the response (or error):
Code:
$.ajax({
url: 'pcdsEmpRecords.php',
success: function(response) {
alert(response);
},
error: function(xhr) {
alert('Error! Status = ' + xhr.status);
}
});
6- How do you update ajax response with id " resilts"?
By using below code we can update div content where id 'results' with ajax response
Code:
function updateStatus() {
$.ajax({
url: 'pcdsEmpRecords.php',
success: function(response) {
// update div id Results
$('#results').html(response);
}
});
}
7- How do You disable or enable a form element?
There are two ways to disable or enable form elements.
Set the 'disabled' attribute to true or false:
Code:
// Disable #pcds
$('#pcds').attr('disabled', true);
// Enable #pcds
$('#pcds').attr('disabled', false);
Add or remove the 'disabled' attribute:
// Disable #pcds
$("#pcds").attr('disabled', 'disabled');
// Enable #x
$("#pcds").removeAttr('disabled');
8- How do you check or uncheck a checkbox input or radio button?
There are two ways to check or uncheck a checkbox or radio button.
Set the 'checked' attribute to true or false.
Code:
// Check #pcds
$('#pcds').attr('checked', true);
// Uncheck #pcds
$('#pcds').attr('checked', false);
Add or remove the 'checked' attribute:
// Check #pcds
$("#pcds").attr('checked', 'checked');
// Uncheck #pcds
$("#pcds").removeAttr('checked');
9- How do you get the text value of a selected option?
Select elements typically have two values that you want to access. First there's the value to be sent to the server, which is easy:
Code:
$("#pcdsselect").val();
// => 1
The second is the text value of the select. For example, using the following select box:
Code:
<select id="pcdsselect">
<option value="1">Mr</option>
<option value="2">Mrs</option>
<option value="3">Ms</option>
<option value="4">Dr</option>
<option value="5">Prof</option>
</select>
If you wanted to get the string "Mr" if the first option was selected (instead of just "1"), you would do that in the following way:
Code:
$("#mpcdsselect option:selected").text();
// => "Mr"
WHAT IS JQUERY
JQuery is Java Script library or Java Script Framework which helps in how to traverse HTML documents, do some cool animations, and add Ajax interaction to any web page. It mainly helps programmer to reduce lines of code as huge code written in Java Script, can be done easily with JQuery in few lines.
WHAT DOES DOLLAR SIGN ($) MEANS IN JQUERY?
Dollar Sign is nothing but it's an alias for JQuery. Take a look at below jQuery code
$(document).ready(function(){
});
Over here $ sign can be replaced with "jQuery " keyword.
jQuery(document).ready(function(){
});
HOW IS BODY ONLOAD() FUNCTION IS DIFFERENT FROM DOCUMENT.READY() FUNCTION USED IN JQUERY?
Document.ready() function is different from body onload() function because off 2 reasons.
1. We can have more than one document.ready() function in a page where we can have only one onload function.
2. Document.ready() function is called as soon as DOM is loaded where body.onload() function is called when everything gets loaded on the page that includes DOM, images and all associated resources of the page.
WHAT ARE THE STEPS YOU NEED TO FOLLOW TO USE JQUERY IN ASP.NET PROJECT?
It's really simple. One just need to add reference of javascript file(.js). Go to Jquery.com and download the latest version of jQuery. When download is completed, there is a "jQuery-1.3.2.js" in the folder. Include this file
<script src="_scripts/jQuery-1.3.2.js" type="text/javascript"></script>
WHAT IS JQUERY UI?
JQuery UI is a library which is built on top of JQuery library. JQuery UI comes with cool widgets, effects and interaction mechanism.
WHAT ARE FEATURES OF JQUERY OR WHAT CAN BE DONE USING JQUERY?
Features of Jquery
1. One can easily provide effects and can do animations.
2. Applying / Changing CSS.
3. Cool plugins.
4. Ajax support
5. DOM selection events
6. Event Handling
NAME SOME OF THE METHODS OF JQUERY USED TO PROVIDE EFFECTS?
Some of the common methods are :
1. Show()
2. Hide()
3. Toggle()
4. FadeIn()
5. FadeOut()
WHAT ARE THE DIFFERENT TYPE OF SELECTORS IN JQUERY?
There are 3 types of selectors in Jquery
1. CSS Selector
2. XPath Selector
3. Custom Selector
WHAT ARE THE ADVANTAGES OF JQUERY ?
There are many advantages with JQuery. Some of them are :
. It is more like a JavaScript enhancement so there is no overhead in learning a new syntax.
. It has the ability to keep the code simple, readable, clear and reusable.
. It would eradicate the requirement for writing complex loops and DOM scripting library calls.
HOW CAN YOU SELECT ALL ELEMENTS IN A PAGE USING JQUERY?
<script language="javascript" type="text/javascript">
$("*").css("border", "2px dotted red");
</script>
WHAT IS JQUERY?
jQuery is not a language but it is a well written JavaScript code, As quoted on official jQuery website “it is a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development.“
In order to work with jQuery you should be aware of basics of JavaScript, HTML and CSS.
It was released in January 2006 at BarCamp NYC by John Resig.
Licensing:
It is free, open source software Dual-licensed under the MIT License and the GNU General Public License. Microsoft has integrated jQuery officially into its IDE Visual Studio 2010 and jQuery intellisense is available in Visual Studio 2010 now.
WHY JQUERY?
jQuery is very compact and well written JavaScript code that increases the productivity of the developer by enabling them to achieve critical UI functionality by writing very less amount of code.
It helps to
# Improve the performance of the application
# Develop most browser compatible web page
# Implement UI related critical functionality without writing hundreds of lines of codes
# Fast
# Extensible – jQuery can be extended to implement customized behavior
Other advantages of jQuery are
# No need to learn fresh new syntax's to use jQuery, knowing simple JavaScript syntax is enough
# Simple and Cleaner code, no need to write several lines of codes to achieve complex functionality.
HOW TO USE JQUERY?
jQuery usually comes as a single JavaScript file containing everything comes out of the box with jQuery. It can be included within a web page using the following mark-up:
To load local jQuery file
<script type="text/javascript" src="jQuery-1.4.1-min.js"></script>
What is JSON?
JSON is a very lightweight data format based on a subset of the JavaScript syntax, namely array and object literals. JSON allows communicating with server in a standard way. JSON is used as communication notation instead of XML.
var oBike =
{
"color" : "Green",
"Speed”: 200,
};
alert(oBike.color); //outputs "Green"
alert(oBike.Speed); //outputs 200
The above code creates an javascript object bike with two properties Color and Speed.
WHAT IS THE JSON(JAVASCRIPT OBJECT NOTATION) ?
JavaScript Object Notation(JSON) is a lightweight text-based open standard designed for human-readable data interchange. It is derived from the JavaScript programming language for representing simple data structures and associative arrays, called objects. And JSON is language-independent, with parsers available for virtually every programming language. Uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python,php
The JSON format is often used for serializing and transmitting structured data over a network connection. When third party data interchane(REST Services) then JSON may used there LIKE SHOP .It is primarily used to transmit data between a server and web application, serving as an alternative to XML.
1. What is Language Integrated Query (LINQ)?
LINQ is a programming model that is the composition of general-purpose standard query operators that allow you to work with data, regardless of the data source in any .NET based programming language. It is the name given to a set of technologies based on the integration of query capabilities into any .NET language.
2. What are LINQ query expressions?
A LINQ query, also known as a query expression, consists of a combination of query clauses that identify the data sources for the query. It includes instructions for sorting, filtering, grouping, or joining to apply to the source data. The LINQ query expressions syntax is similar to the SQL syntax. It specifies what information should be retrieved from the data source.
3. Write the basic steps to execute a LINQ query.
The following are the three basic steps to execute a LINQ query:
Obtain the data source (The data source can be either an SQL database or an XML file)
Create a query
Execute the query
4. Write the basic syntax of a LINQ query in Visual Basic as well as in C#.
In Visual Basic, the basic syntax of a LINQ query starts with the From clause and ends with the Select or Group By clause. In addition, you can use the Where, Order By, and Order By Descending clauses to perform additional functions, such as filtering data and generating the data in a specific order.
In C#, the basic syntax of a LINQ query starts with the From clause and ends with the Select or group by clause. In addition, you can use the where, orderby, and Orderby descending clauses to perform additional functions, such as filtering data and generating the data in a specific order.
5. In which statement the LINQ query is executed?
A LINQ query is executed in the For Each statement in Visual Basic and in the foreach statement in C#.
6. In LINQ, lambda expressions underlie many of the standard query operators. Is it True or False?
It is true.
What is a Lambda expression?
A Lambda expression is nothing but an Anonymous Function, can contain expressions and statements. Lambda expressions can be used mostly to create delegates or expression tree types. Lambda expression uses lambda operator => and read as 'goes to' operator.
Left side of this operator specifies the input parameters and contains the expression or statement block at the right side.
Example: myExp = myExp/10;
Now, let see how we can assign the above to a delegate and create an expression tree:
delegate int myDel(int intMyNum);
static void Main(string[] args)
{
//assign lambda expression to a delegate:
myDel myDelegate = myExp => myExp / 10;
int intRes = myDelegate(110);
Console.WriteLine("Output {0}", intRes);
Console.ReadLine();
//Create an expression tree type
//This needs System.Linq.Expressions
Expression<myDel> myExpDel = myExp => myExp /10;
}
Note:
The => operator has the same precedence as assignment (=) and is right-associative.
Lambdas are used in method-based LINQ queries as arguments to standard query operator methods such as Where.
What is LINQ?
It stands for Language Integrated Query. LINQ is collection of standard query operators that provides the query facilities into .NET framework language like C# , VB.NET.
How LINQ is beneficial than Stored Procedures?
There are couple of advantage of LINQ over stored procedures.
1. Debugging - It is really very hard to debug the Stored procedure but as LINQ is part of .NET, you can use visual studio's debugger to debug the queries.
2. Deployment - With stored procedures, we need to provide an additional script for stored procedures but with LINQ everything gets complied into single DLL hence deployment becomes easy.
3. Type Safety - LINQ is type safe, so queries errors are type checked at compile time. It is really good to encounter an error when compiling rather than runtime exception!
Why Select clause comes after from clause in LINQ?
The reason is, LINQ is used with C# or other programming languages, which requires all the variables to be declared first. From clause of LINQ query just defines the range or conditions to select records. So that’s why from clause must appear before Select in LINQ.
What is the extension of the file, when LINQ to SQL is used?
The extension of the file is .dbml
What is the LINQ file extension that interacts with Code Behind's objects.
its .dbml
Why can't datareader by returned from a Web Service's Method
Cos, it's not serializable
What is the use of System.XML.XLinq.dll?
System.XML.XLinq.dll contains classes to provide functionality to use LINQ with XML.
What is the use of System.Data.DLinq.dll?
System.Data.DLinq.dll provides functionality to work with LINQ to SQL.
Which assembly represents the core LINQ API?
System.Query.dll assembly represents the core LINQ API.
What is the benefit of using LINQ on Dataset?
The main aim of using LINQ to Dataset is to run strongly typed queries on Dataset.
Suppose we want to combine the results from two Datasets, or we want to take a distinct value from the Dataset, then it is advisable to use LINQ.
Normally you can use the SQL queries to run on the database to populate the Dataset, but you are not able to use SQL query on a Dataset to retrieve a particular values. To get this you need to use ADO.NET functionalities. But, in case of LINQ, it provides more dignified way of querying the Dataset and provides some new features as compared to ADO.NET.
What are the advantages of LINQ over Stored Procedures?
Below is the three advantages of LINQ over stored procedures.
Debugging - As debug point concern, as LINQ is part of .NET, we can use the visual studio's debugger to debug the queries but it is tough to debug the Stored procedure as it will not support the visual studio debugger.
Deployment - In case of deployment, we need to provide an additional script for stored procedures to execute but in case of LINQ, it will complie into single DLL hence deployment becomes easier.
Type Safety - As LINQ is type safe, the queries errors are type checked at compile time. Better suggest to use LINQ because it helps to encounter an error at the compile time rather than at runtime exception.
What is the disadvantage of LINQ over stored procedures?
The disadvantage with LINQ is, it is not a precompiled statement where as stored procedures are precompiled. In case of LINQ the queries need to be compile before the execution. So according to this, I can say stored procedures are faster in performance as compared to LINQ.
What are Quantifiers?
They are LINQ Extension methods which return a Boolean value
1)All
2)Any
3)Contains
4)SequenceEqual
example:
int[] arr={10,20,30};
var b=arr.All(a=>a>20);
-------------------------------------------
Output:
b will return False since all elements are not > 20.
What is the Role of IIS ?
Visual studio having It own ASP.NET Engine which is capable enough to run Asp.net web application from visual studio. So we just click on Run button to start the application.
Now this is the scenarios of local environment. But If we want to host it on server from where all user can access the sites then IIS comes into the picture.
IIS provides a redesigned WWW architecture that can help you achieve better performance, reliability, scalability, and security for our Web sites. IIS can support following Protocol HTTP/HTTPS, FTP, FTPS, SMTP Etc. We need to host the site on IIS, when request comes from client it first hits the IIS Server, then the server passed it to ASP.NET worker process to execute. Then the response also passes to client via IIS itself.
Note only Hosting of Site we can create our FTP Server, SMTP Server using IIS itself.
There are different version of IIS available like 5.1, 6.0, 7.0 etc
What are the different version on IIS that you have worked on ?
Before answering this question you need to know what are the different IIS version is available in different OS. Below is the list of IIS version with different Operating system.
Windows Server 2008 - Windows Vista - Home Premium/ Ultimate - IIS 7.0
Windows Server 2003 - IIS 6.0
Windows XP Professional - IIS 5.1
Now based on your working experience you can say that you have worked on IIS 5.1 and 6.0 or only IIS 7. Etc.
Now, the next question that can asked after answering this question is “what is the difference between them ? ” – Well I will come with this later.
What is Application Pool in IIS ?
Before Giving the Definition : you can say like this, Concept of Application pool has from IIS 6.0 .
Application pools are used to separate sets of IIS worker processes that share the same configuration and application boundaries. Application pools used to isolate our web application for better security, reliability, and availability and performance and keep running with out impacting each other . The worker process serves as the process boundary that separates each application pool so that when one worker process or application is having an issue or recycles, other applications or worker processes are not affected.
One Application Pool can have multiple worker process Also.
Main Point to Remember:
1. Isolation of Different Web Application
2. Individual worker process for different web application
3. More reliably web application
4. Better Performance
What is the Name of Default Application Pool in IIS ?
Though we can create new application pool IIS with different settings, but IIS having its own default application pool named : DefaultAppPool
What are the different types of Identity available in IIS 6.0 ?
IIS having three different Identity.
1. Local System
2. Local Services
3. NetworkServices
Name of default Identity of IIS6.0
Default Identity of IIS 6.0 is NetworkServices .
Which is having very minimum rights on your system. The user can only have the read access of the site.
What is Recycling of Application Pool ?
Recycling Application pool means recycle the Worker process (w3wp.exe ) and the memory used for the web application.
There are two types of recycling related with Application pool
1. Recycling Worker Process - Predefined Settings
2. Recycling Worker Process - Based on Memory
What are the main layers of IIS Architecture ?
IIS having mainly two layer Kernel Mode and User Mode
Below are the subsection of both of them.
1. Kernel Mode
· HTTP.SYS
2. User Mode
· Web Admin Service
· Virtual Directory
· Application Pool
What is the Role of Http.Sys in IIS ?
HTTP.SYS is the kernel level components of IIS. All client request comes from client hit the HTTP.Sys of Kernel level. HTTP.SYS then makes a queue for each and every request for each and individual application pool based on the request.
Whenever we create any application pool IIS automatically registers the pool with HTTP.SYS to identify the particular during request processing.
What are the different security settings available in IIS ?
Below are the commonly used IIS Security settings
1 Anonymous
2 Integrated Windows Authentication
3. Basic Authentication
4. Digest Authentication
5. Passport Authentication
For Set security permission you need to go to Virtul Directory > Right Click > Properties > Directory Security
What is the default authentication settings for IIS ?
Anonymous authentication is the default authentication mode for any site that is hosted on IIS, and it runs under the "IUSR_[ServerName]" account.
What is web garden ?
By default Each Application Pool runs with a Single Worker Process (W3Wp.exe). We can assign multiple Worker Process With a Single Application Pool. An Application Poll with multiple Worker process called Web Gardens. Each Worker Process Should have there own Thread and Own Memory space.
Generally its not recommended to use InProc Session mode while we are using Web Garden.
Where session data stores in case of "In-Proc" Session mode ?
Session data store inside process memory of worker process [ w3wp.exe ] .
How we can create an web garden ?
For creating web graden we need to go to Application Pool, then Right Click on Application Pool > Properties > Goto Performance Tab
In Web Garden Section, increase the number of worker process. By default it is 1.
How we can debug a web application which is hosted on IIS ?
We can easily debug any web application that are hosted on IIS by using Attaching of Worker Process.
From Visual Studio IDE > Tools > Attach To Process
Select the particular Process, then start debugging.
SQL Server Data manipulation language
Data manipulation language is used for managing data. Managing data would mean retrieving, inserting, deleting or updating data. SQL is the most common data manipulation language. SELECT, INSERT, UPDATE keywords are a part of Data manipulation language. Data manipulation language can be procedural or declarative.
Explain Data Definition Language, Data Control Language and Data Manipulation Language.
Data Definition Language (DDL):- are the SQL statements that define the database structure.
Example:
a. CREATE
b. ALTER
c. DROP
d. TRUNCATE
e. COMMENT
f. RENAME
Data Manipulation Language (DML):- statements are used for manipulate or edit data.
Example:
a. SELECT - retrieve data from the a database
b. INSERT - insert data into a table
c. UPDATE - updates existing data within a table
d. DELETE
e. MERGE
f. CALL
g. EXPLAIN PLAN
h. LOCK TABLE
Data Control Language (DCL):-statements to take care of the security and authorization.
Examples:
a. GRANT
b. REVOKE
Answer
Data definition language is used to define and manage all attributes and properties of a database.
Most DDL statements include following commands
CREATE
ALTER
DROP
Data Control Language
Data control language is used to control permissions on database objects. Permissions are controlled by using GRANT, REVOKE, and DENY statement.
Data Manipulation Language
Data manipulation language is used to select, insert, update, and delete data in the objects defined with DDL.
Explain What is Data Manipulation?
Data Manipulation can be put into CRUD
C - Create
R - Read
U - Update
D - Delete
Update may include sorting and other kinds of manipulation of data
Dealing with the data is known as data manuplation.
It includes:
insertion of data
deletion of data
updation of data
selecting/fetching data
data manuplation language in SQl includes:
Insert
delete
update
select
WHAT ARE CONSTRAINTS? EXPLAIN DIFFERENT TYPES OF CONSTRAINTS?
constraint is a rule which can not be voilated by end users.
Different types of constraints are available.They are:-
1)default constraint:-which is used to define a default
value.
2)primary key:-the key which does not allows duplication
and null values.
3)foreign key:-the key used to refer primary key defined
field in another table and it allows duplication.
4)null:-which allows NULL values.
5)not null:-which does not allows NULL values.
6)unique key:-which does not allows duplication but allows
NULL values.
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.
What is constraints?
SQL Server users constraints to enforce limitations on the data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
Can you explain Clustered index and Non-Clustered Index?
This is the SQL Server interview questions most asked during the interview so do revise before you go for the interview.
There are basically two types of indexes:-
Clustered Indexes.
Non-Clustered Indexes.
Every thing is same for both the indexes i.e. it uses “B-TREE” for searching data. However, the main difference is the way it stores physical data
In clustered index, the non-leaf level actually points to the actual data.
Figure: - Clustered Index Architecture
In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.
Figure: - Non-Clustered Index has pointers.
So here is what the main difference is in clustered and non-clustered, in clustered when we reach the leaf nodes we are on the actual data. In non-clustered indexes, we get a pointer, which then points to the actual data.
Therefore, after the above fundamentals following are the basic differences between them:-
indexes as we have pointers, which is logical arrangement we do need this compulsion. Also, note in clustered index actual data as to be sorted in same way as the clustered indexes are. While in non-clustered
So we can have only one clustered index on a table as we can have only one physical order while we can have more than one non-clustered indexes.
What is Clustered & Non-Clustered Index?
Clustered Index: Clustered index physically rearrange the data that users inserts in your tables. It is nothing but a dictionary type data where actual data remains.
Non-Clustered Index: It Non-Clustered Index contains pointers to the data that is stored in the data page. It is a kind of index backside of the book where you see only the reference of a kind of data.
How to Enable / Disable the Full-Text Index created on a View / Table ?
To Enable the Full-Text search Index already created on a View/Table :
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].Table or View Name', @action=N'activate'
To Disable the Full-Text search Index already created on a View/Table :
EXEC dbo.sp_fulltext_table @tabname=N'[dbo].Table or View Name', @action=N'deactivate'
WHAT IS FULL TEXT SEARCH ?
SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types:
char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max). Each full-text index indexes one or more columns from the base table, and each column can
have a specific language. Beginning in SQL Server 2008, full-text search supports more than 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi. For a complete list of the supported full-text languages, see sys.fulltext_languages (Transact-SQL).
For each supported language, SQL Server provides language-specific linguistic components, including a word breaker and stemmer and an empty thesaurus file. For each full-text language, SQL Server also provides a file in which you can optionally define language-specific synonyms to extend the scope of search queries (a thesaurus file). In addition, beginning in SQL Server 2008, a system stoplist is provided. To support specific language or business scenarios, you can alter the system stoplist by adding and removing stopwords (also known as noise words), and you can create additional stoplists as needed.
For writing full-text queries, SQL Server provides a set of full-text predicates (CONTAINS and FREETEXT) and rowset-valued functions (CONTAINSTABLE and FREETEXTTABLE). Using these, applications and users can perform a variety of types of full-text searches, such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word.
What is SQL Profiler ?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server.
You can capture and save data about each event to a file or SQL Server table to analyze later.
You can use SQL Profiler to monitor only the events in which you are interested.
For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
DATATABLE à 10 salary rows i want to know sum of salary without loop
We can compute in following methods:
1) sumObject = dt.Compute("Sum(Salary)", "");
2) var sum = dt.AsEnumerable().Sum(x => x.Field<int>("Salary"));
1. What is the difference between WCF and ASMX Web Services?
Simple and basic difference is that ASMX or ASP.NET web service is designed to send and receive messages using SOAP over HTTP only. While WCF can exchange messages using any format (SOAP is default) over any transport protocol (HTTP, TCP/IP, MSMQ, NamedPipes etc).
2. What are WCF Service Endpoints? Explain.
For Windows Communication Foundation services to be consumed, it’s necessary that it must be exposed; Clients need information about service to communicate with it. This is where service endpoints play their role.
A WCF service endpoint has three basic elements i.e. Address, Binding and Contract.
Address: It defines "WHERE". Address is the URL that identifies the location of the service.
Binding: It defines "HOW". Binding defines how the service can be accessed.
Contract: It defines "WHAT". Contract identifies what is exposed by the service.
3. What are the possible ways of hosting a WCF service? Explain.
For a Windows Communication Foundation service to host, we need at least a managed process, a ServiceHost instance and an Endpoint configured. Possible approaches for hosting a service are:
Hosting in a Managed Application/ Self Hosting
Console Application
Windows Application
Windows Service
Hosting on Web Server
IIS 6.0 (ASP.NET Application supports only HTTP)
Windows Process Activation Service (WAS) i.e. IIS 7.0 supports HTTP, TCP, NamedPipes, MSMQ.
4. How we can achieve Operation Overloading while exposing WCF Services?
By default, WSDL doesn’t support operation overloading. Overloading behavior can be achieved by using "Name" property of OperationContract attribute.
[ServiceContract]
interface IMyCalculator
{
[OperationContract(Name = "SumInt")]
int Sum(int arg1,int arg2);
[OperationContract(Name = "SumDouble")]
double Sum(double arg1,double arg2);
}
When the proxy will be generated for these operations, it will have 2 methods with different names i.e. SumInt and SumDouble.
5. What Message Exchange Patterns (MEPs) supported by WCF? Explain each of them briefly.
1. Request/Response 2. One Way 3. Duplex
Request/Response
It’s the default pattern. In this pattern, a response message will always be generated to consumer when the operation is called, even with the void return type. In this scenario, response will have empty SOAP body.
One Way
In some cases, we are interested to send a message to service in order to execute certain business functionality but not interested in receiving anything back. OneWay MEP will work in such scenarios. If we want queued message delivery, OneWay is the only available option.
Duplex
The Duplex MEP is basically a two-way message channel. In some cases, we want to send a message to service to initiate some longer-running processing and require a notification back from service in order to confirm that the requested process has been completed.
6. What is DataContractSerializer and How its different from XmlSerializer?
Serialization is the process of converting an object instance to a portable and transferable format. So, whenever we are talking about web services, serialization is very important.
Windows Communication Foundation has DataContractSerializer that is new in .NET 3.0 and uses opt-in approach as compared to XmlSerializer that uses opt-out. Opt-in means specify whatever we want to serialize while Opt-out means you don’t have to specify each and every property to serialize, specify only those you don’t want to serialize. DataContractSerializer is about 10% faster than XmlSerializer but it has almost no control over how the object will be serialized. If we wanted to have more control over how object should be serialized that XmlSerializer is a better choice.
7. How we can use MessageContract partially with DataContract for a service operation in WCF?
MessageContract must be used all or none. If we are using MessageContract into an operation signature, then we must use MessageContract as the only parameter type and as the return type of the operation.
8. Which standard binding could be used for a service that was designed to replace an existing ASMX web service?
The basicHttpBinding standard binding is designed to expose a service as if it is an ASMX/ASP.NET web service. This will enable us to support existing clients as applications are upgrade to WCF.
9. Please explain briefly different Instance Modes in WCF?
WCF will bind an incoming message request to a particular service instance, so the available modes are:
Per Call: instance created for each call, most efficient in term of memory but need to maintain session.
Per Session: Instance created for a complete session of a user. Session is maintained.
Single: Only one instance created for all clients/users and shared among all.Least efficient in terms of memory.
10. Please explain different modes of security in WCF? Or Explain the difference between Transport and Message Level Security.
In Windows Communication Foundation, we can configure to use security at different levels
a. Transport Level security means providing security at the transport layer itself. When dealing with security at Transport level, we are concerned about integrity, privacy and authentication of message as it travels along the physical wire. It depends on the binding being used that how WCF makes it secure because most of the bindings have built-in security.
<netTcpBinding>
<binding name="netTcpTransportBinding">
<security mode="Transport">
<Transport clientCredentialType="Windows" />
</security>
</binding>
</netTcpBinding>
b. Message Level Security For Tranport level security, we actually ensure the transport that is being used should be secured but in message level security, we actually secure the message. We encrypt the message before transporting it.
<wsHttpBinding>
<binding name="wsHttpMessageBinding">
<security mode="Message">
<Message clientCredentialType="UserName" />
</security>
</binding>
</wsHttpBinding>
It totally depends upon the requirements but we can use a mixed security mode also as follows:
<basicHttpBinding>
<binding name="basicHttp">
<security mode="TransportWithMessageCredential">
<Transport />
<Message clientCredentialType="UserName" />
</security>
</binding>
</basicHttpBinding>
Difference between view state and a hidden field?
HiddenFeilds are used to store small amout of data during the post backs.Hidden field stores a single variable in its value property and must be explicitly added it to the page.
Each control on a Web Forms page, including the page itself, has a ViewState property, it is a built-in struture for automatic retention of page and control state, which means you don’t need to do anything about getting back the data of controls after posting page to the server.
Unlike Hidden Field, the values in ViewState are invisible when ‘view source’, they are compressed and encoded.
Hidden Fields contain information that is not visible on the page but is posted to the server along with a page post back While View State is the mechanism that asp.net uses to maintain the state of controls across page post backs.
Hidden Fields are used for pages that post to themselves or to other pages while View State works only when a page posts back to itself.
Hidden Fields does not support for storing structured values while View State support for structured values.
What is Collection ?
Collection is used to Store number of values inside the object like an Array but it can contain any type of value. i.e Collection always takes value as an object type.
What is Ispostback method in ASP.Net? Why do we use that??
Basically Post back is an action performed by a interactive Webpage. When it goes to the server side for a non-client Operation Server again posts it back to the client and hence the name.
Ex:
if(!IsPostBack)
will not allow the page to post back again n again bcoz it reduces the performance.
Can User Control be stored in library?.
I will say "NO"
there are 3 types of controls:
1) User Control
2) Custom Control
3) Web parts
you can reuse User control in the current project in which you have built it, but you can't move it to other project as unless you just copy paste the same file there and make the changes for that project ( which violets the concept of library).
but custom control can be shared between projects. and you can precompile them even as a dll, so this means you can use them in library of any type.
What is the code behind feature of ASP.NET?
The code behind feature divides ASP.NET page files into two files where one defines the user interface (.aspx), while the other contains all of the logic or code (.aspx.cs for C# and .aspx.vb for VB.NET). These two files are glued together with page directives like the following line, which ties the page to the specific code behind class.
<%@ Page language="c#" Codebehind="UICode.cs" Inherits="Library.UICode" %>
What are ASHX files?
ASP.NET Web handler files have an .ashx file extension. Web handlers work just like .aspx files except you don’t have to deal with the browser interface, thus no worrying about presentation. Web handlers are generally used to generate content dynamically like returning XML or an image. Web handlers use the IHttpHandler interface with the ProcessRequest() method invoked when the handler is requested. Web handlers are simpler than pages (fewer events and wiring), so they are ideal for performance-critical applications.
ERROR LOGGING METHODS IN ASP.NET BY EVENT LOGGING
The Event Log
The Event Logs on your computer are accessible through the Event Viewer which you can find under the Administrative Tools menu. On a normal installation, you have three default Event Logs: Application, Security and System. The Application log is used to record information from applications. If you take a look in the Application log, you'll see logs from applications you're probably familiar with, like Office installations, SQL Server, backup programs and so on.
The Security log is not used much by default; however you can enable thorough access logging so more details about user sign-ons, access checks etc are logged.
The System log is used to log events from Windows components, like driver or service failures.
If you look at these three logs, you probably think that the Application log is best for logging your custom errors. That's true, but there is even a better solution: the custom Event Log. I'll get to that in a bit.
The Event Source
The Event Source is usually the name of the application that logged the event, but this is not required. In this article, however, I'll stick to this recommendation and use the full address of my Web site as the name of the Event Source. This allows me to easily distinguish errors I am interested in from errors from other sources. Feel free to choose a different naming convention, though.
The following screen shot shows you the Event Viewer. You can see the custom Event Log, My Websites, in the right-hand pane and you can see that the events are logged under the source Imar.Spaanjaars.com, the name of the Web site that generated the event.
Figure 1 - The Event Viewer with the Custom Event Log visible
Creating Your Own Event Log
It's remarkably simple to create a new Event Log. All you need to do is call the static CreateEventSource method of the EventLog class that lives in the System.Diagnostics namespace. So, fire up Visual Studio .NET to create the application that will create the Event Log and Source. (The source code for this article includes the full source of the application, in case you want to try it out directly.)
Once you started Visual Studio, create a new Visual C# Windows Application. Name the application CreateEventLog.
Drag two labels, two textboxes and a button from the Toolbox to the form and set their properties as follows:
Figure 2 - The Sample Application
Double click the button, and add the following code for the btnCreateEventLog_Click handler:
5. private void btnCreateEventLog_Click(object sender, System.EventArgs e)
{
if (txtEventLog.Text.Length > 0 && txtEventSource.Text.Length > 0)
{
System.Diagnostics.EventLog.CreateEventSource(
txtEventSource.Text, txtEventLog.Text);
MessageBox.Show("Event Log and Source " +
"created successfully.");
}
else
{
MessageBox.Show("Please fill in both the " +
"Event Log and the Event Source.");
}
}
This code will check if both required fields are filled in, and if they are create the requested Event Log and Event Source.
Press F5 to start the application, type My Websites for the Event Log name and the URL of your site, like www.MySite.com as the Event Source and click the Create Event Log button.
Open up the Event Viewer (close it first if you had it still open to refresh its contents) and you'll see your new Event Log as in the first screen shot of this article.
That was easy, wasn't it?
Writing to the Event Log
Creating a new Event Log and Source is easy, but writing to it is about as easy.
First of all, make sure you followed the steps from the article How do I Handle Errors and Send an Error Notification from ASP.NET Applications? Make sure you changed at least the Web.Config file so it includes the customErrors node with its mode set to On:
2. <customErrors mode="On">
3. <error statusCode="500" redirect="/InternalError.htm"/>
</customErrors>
The InternalError.htm page is the page your users will see when an error occurs and generally contains a user-friendly explanation that an error has occurred.
Next, open the Web application that you want to log errors for and open the Code Behind page for the Global class (Global.asax.cs, or Global.asax.vb if you're using VB.NET).
Add the following using statement to the top of the Code Behind page:
using System.Diagnostics;
Add the following code to the Application_Error event. (If you followed along with the previous article, and added code that e-mailed you the detailed error information, add the following code below the code already present for the event):
7. protected void Application_Error(Object sender, EventArgs e)
{
// Log error to the Event Log
Exception myError = null;
if (HttpContext.Current.Server.GetLastError() != null)
{
string eventLog = "My Websites";
string eventSource = "www.MySite.Com";
string myErrorMessage = "";
myError = Server.GetLastError();
while (myError.InnerException != null)
{
myErrorMessage += "Message\r\n" +
myError.Message.ToString() + "\r\n\r\n";
myErrorMessage += "Source\r\n" +
myError.Source + "\r\n\r\n";
myErrorMessage += "Target site\r\n" +
myError.TargetSite.ToString() + "\r\n\r\n";
myErrorMessage += "Stack trace\r\n" +
myError.StackTrace + "\r\n\r\n";
myErrorMessage += "ToString()\r\n\r\n" +
myError.ToString();
// Assign the next InnerException
// to catch the details of that exception as well
myError = myError.InnerException;
}
// Make sure the Eventlog Exists
if(EventLog.SourceExists(eventSource))
{
// Create an EventLog instance and assign its source.
EventLog myLog = new EventLog(eventLog);
myLog.Source = eventSource;
// Write the error entry to the event log.
myLog.WriteEntry("An error occurred in the Web application "
+ eventSource + "\r\n\r\n" + myErrorMessage,
EventLogEntryType.Error);
}
}
}
Don't forget to replace the parts in bold with the names you have chosen for your Event Log and Source.
To test out the new logging feature of your site, add a new Web Form to your application and call it TestLogging.aspx.
Open the Code Behind for the page, and add the following code that will explicitly generate an error to the Page_Load event:
10. private void Page_Load(object sender, System.EventArgs e)
{
throw new Exception("An error occurred in the " +
"Page_Load of the TestLogging page.");
}
Save the page, compile your application and then open TestLogging.aspx in your browser. Instead of the usual error page, you should see your custom error page you defined in step 1.
Start the Event Viewer and open your custom Event Log. You should see a new event log entry with the details of the error that occurred. Double-click the event log entry to see the dirty details of your error:
Figure 3 - The Event Properties Dialog Displays the Detailed Error Nessage.
For this little example, I explicitly threw a new error using the throw keyword. However, the logging mechanism I showed you will catch all errors that may occur in your code, including any unhandled exceptions. This will help tremendously in pinpointing and fixing the problem.
Notice that the code in the Application_Errorevent uses a loop to retrieve information about the InnerException. If you don't use the loop, you'll end up with a rather meaningless "System.Web.HttpUnhandledException" message, without all the nitty gritty details about your error.
Also, sometimes explicitly nested errors are created, by passing in a Exception (the Inner Exception) to the constructor of another Exception (the Outer Exception) before the Outer Exception is thrown. This allows programmers to add useful information about the error, while leaving the original error and message in tact.
Instead of looping through the exceptions, you can also call GetBaseException() to get at the inner most exception directly:
myError = Server.GetLastError().GetBaseException();
myErrorMessage += "Message\r\n" +
myError.Message.ToString() + "\r\n\r\n";
myErrorMessage += "Source\r\n" +
myError.Source + "\r\n\r\n";
myErrorMessage += "Target site\r\n" +
myError.TargetSite.ToString() + "\r\n\r\n";
myErrorMessage += "Stack trace\r\n" +
myError.StackTrace + "\r\n\r\n";
myErrorMessage += "ToString()\r\n\r\n" +
myError.ToString();
What's Next
The purpose of this article is to show you the general idea of logging to the Event Log. It's by no means a complete solution (although you can use the code directly to enable logging in relatively small applications). Here are some ideas to extend the concepts that were introduced in this article:
Create a class ExceptionLogger with a few overloaded methods like LogError. Have the methods accept various flavors of exceptions, exception messages and EventLogEntryTypes (Error, Warning, Information and so on) and log the errors to the Event log. This allows you to log errors or other events to the Event log from within a Catch block, without interrupting the page flow and relying on the global exception handler.
Add backup code that logs the errors to a text file or sends out an e-mail in case the Event Log or Source does not exist. This shouldn't happen because you created the Logs and Source yourself at installation time, but, hey, it's not a perfect world.
Create different Event Sources for your application. This allows you to make a distinction between different kind of event log entries so it's easier to locate the important ones.
There is Even More
Much of what I have introduced in this article, has already been taken care of by Microsoft. In April 2002, Microsoft released the Exception Management Application Block for .NET, a complete solution including full source code for publishing your errors to different logs, including the Event Log, SQL Server and a custom log that you define (an XML / text file or a pager or mobile phone for example).
The Application Block may be a bit overwhelming at first, and difficult to install and configure if you don't read the documentation carefully. It's not always necessary or applicable to use the Exception Management Application Block. For smaller sites or applications, the logging I demonstrated in this article should be more than enough.
Additionally, you can take a look at ELMAH (Error Logging Modules & Handlers), an open source project that allows you to send exception messages by e-mail or store them in a database. It's easy to setup and can also be implemented in existing solutions without the need to recompile anything.
What is Ajax?
The term Ajax was coined by Jesse James Garrett and is a short form for "Asynchronous Javascript and XML". Ajax represents a set of commonly used techniques, like HTML/XHTML, CSS, Document Object Model(DOM), XML/XSLT, Javascript and the XMLHttpRequest object, to create RIA's (Rich Internet Applications).
Ajax gives the user, the ability to dynamically and asynchronously interact with a web server, without using a plug-in or without compromising on the user’s ability to interact with the page. This is possible due to an object found in browsers called the XMLHttpRequest object.
What is ASP.NET AJAX?
‘ASP.NET AJAX’ is a terminology coined by Microsoft for ‘their’ implementation of AJAX, which is a set of extensions to ASP.NET. These components allow you to build rich AJAX enabled web applications, which consists of both server side and client side libraries.
Which is the current version of ASP.NET AJAX Control Toolkit?
As of this writing, the toolkit version is Version 1.0.20229 (if you are targeting Framework 2.0, ASP.NET AJAX 1.0 and Visual Studio 2005) and Version 3.0.20229 (if targeting .NET Framework 3.5 and Visual Studio 2008).
What role does the ScriptManager play?
The ScriptManager manages all ASP.NET AJAX resources on a page and renders the links for the ASP.NET AJAX client libraries, which lets you use AJAX functionality like PageMethods, UpdatePanels etc. It creates the PageRequestManager and Application objects, which are prominent in raising events during the client life cycle of an ASP.NET AJAX Web page. It also helps you create proxies to call web services asynchronously.
Can we use multiple ScriptManager on a page?
No. You can use only one ScriptManager on a page.
What is the role of a ScriptManagerProxy?
A page can contain only one ScriptManager control. If you have a Master-Content page scenario in your application and the MasterPage contains a ScriptManager control, then you can use the ScriptManagerProxy control to add scripts to content pages.
Also, if you come across a scenario where only a few pages in your application need to register to a script or a web service, then its best to remove them from the ScriptManager control and add them to individual pages, by using the ScriptManagerProxy control. That is because if you added the scripts using the ScriptManager on the Master Page, then these items will be downloaded on each page that derives from the MasterPage, even if they are not needed, which would lead to a waste of resources.
What are the requirements to run ASP.NET AJAX applications on a server?
You would need to install ‘ASP.NET AJAX Extensions’ on your server. If you are using the ASP.NET AJAX Control toolkit, then you would also need to add the AjaxControlToolkit.dll in the /Bin folder.
Note: ASP.NET AJAX 1.0 was available as a separate downloadable add-on for ASP.NET 2.0. With ASP.NET 3.5, the AJAX components have been integrated into ASP.NET.
Explain the UpdatePanel?
The UpdatePanel enables you to add AJAX functionality to existing ASP.NET applications. It can be used to update content in a page by using Partial-page rendering. By using Partial-page rendering, you can refresh only a selected part of the page instead of refreshing the whole page with a postback.
Can I use ASP.NET AJAX with any other technology apart from ASP.NET?
To answer this question, check out this example of using ASP.NET AJAX with PHP, to demonstrate running ASP.NET AJAX outside of ASP.NET. Client-Side ASP.NET AJAX framework can be used with PHP and Coldfusion.
How can you cancel an Asynchronous postback?
Yes you can. Read my article over here.
Difference between Server-Side AJAX framework and Client-side AJAX framework?
ASP.NET AJAX contains both a server-side Ajax framework and a client-side Ajax framework. The server-side framework provides developers with an easy way to implement Ajax functionality, without having to possess much knowledge of JavaScript. The framework includes server controls and components and the drag and drop functionality. This framework is usually preferred when you need to quickly ajaxify an asp.net application. The disadvantage is that you still need a round trip to the server to perform a client-side action.
The Client-Side Framework allows you to build web applications with rich user-interactivity as that of a desktop application. It contains a set of JavaScript libraries, which is independent from ASP.NET. The library is getting rich in functionality with every new build released.
How can you debug ASP.NET AJAX applications?
Explain about two tools useful for debugging: Fiddler for IE and Firebug for Mozilla.
Can we call Server-Side code (C# or VB.NET code) from javascript?
Yes. You can do so using PageMethods in ASP.NET AJAX or using webservices.
Can you nest UpdatePanel within each other?
Yes, you can do that. You would want to nest update panels to basically have more control over the Page Refresh.
How can you to add JavaScript to a page when performing an asynchronous postback?
Use the ScriptManager class. This class contains several methods like the RegisterStartupScript(), RegisterClientScriptBlock(), RegisterClientScriptInclude(), RegisterArrayDeclaration(),RegisterClientScriptResource(), RegisterExpandoAttribute(), RegisterOnSubmitStatement() which helps to add javascript while performing an asynchronous postback.
Explain differences between the page execution lifecycle of an ASP.NET page and an ASP.NET AJAX page?
In an asynchronous model, all the server side events occur, as they do in a synchronous model. The Microsoft AJAX Library also raises client side events. However when the page is rendered, asynchronous postback renders only the contents of the update panel, where as in a synchronous postback, the entire page is recreated and sent back to the browser.
Is the ASP.NET AJAX Control Toolkit(AjaxControlToolkit.dll) installed in the Global Assembly Cache?
No. You must copy the AjaxControlToolkit.dll assembly to the /Bin folder in your application.
Those were some frequently asked questions you should have knowledge about. In one of the coming articles, we will cover some more ASP.NET AJAX FAQ’s which were not covered in this article. I hope this article was useful and I thank you for viewing it.
How many types of triggers are there in update panel?
There are 2 types of triggers.
1. PostBackTrigger : It does a full postback. This is useful when any such control which placed within updatePanel but it cannot work asynchronously. Like File Upload Control.
2. AsyncPostBackTrigger :- It does partial post back asynchronously.
Tell name of all the control of Ajax?
There are 5 controls.
1. ScriptManager
2. UpdatePanel
3. UpdateProgress
4. Timer
5. ScriptManageProxy
What is ASP.NET AJAX?
ASP.NET AJAX, mostly called AJAX, is a set of extensions of ASP.NET. It is developed by Microsoft to implement AJAX functionalities in Web applications. ASP.NET AJAX provides a set of components that enable the developers to develop applications that can update only a specified portion of data without refreshing the entire page. The ASP.NET AJAX works with the AJAX Library that uses object-oriented programming (OOP) to develop rich Web applications that communicate with the server using asynchronous postback.
What is the difference between synchronous postback and asynchronous postback?
The difference between synchronous and asynchronous postback is as follows:
Asynchronous postback renders only the required part of the page; whereas, synchronous postback renders the entire page for any postback.
Asynchronous postback executes only one postback at a time, that is, if you have two buttons doing asynchronous postback, the actions will be performed one by one; whereas, synchronous postback executes all the actions at once.
Asynchronous postback only modifies the update panel that raises the postback; whereas, synchronous postback modifies the entire page.
What is Microsoft ADO.NET?
Visual Studio .NET provides access to databases through the set of tools and namespaces collectively referred to as Microsoft ADO.NET
What are the 3 major types of connection objects in ADO.NET?
OleDbConnection object : Use an OleDbConnection object to connect to a Microsoft Access or third-party database, such as MySQL. OLE database connections use the OleDbDataAdapter object to perform commands and return data.
SqlConnection object : Use a SqlConnection object to connect to a Microsoft SQL Server database. SQL database connections use the SqlDataAdapter object to perform commands and return data.
OracleConnection object : Use an OracleConnection object to connect to Oracle databases. Oracle database connections use the OracleDataAdapter object to perform commands and return data. This connection object was introduced in Microsoft .NET Framework version 1.1.
List the 4 common ADO.NET Namespaces?
System.Data : Contains Classes, types, and services for creating and accessing data sets and their subordinate objects
System.Data.SqlClient : Contains Classes and types for accessing Microsoft SQL Server databases
System.Data.OracleClient : Contains Classes and types for accessing Oracle databases (Microsoft .NET Framework version 1.1 and later)
System.Data.OleDb : Contains Classes and types for accessing other databases
List all the steps in order, to access a database through ADO.NET?
1. Create a connection to the database using a connection object.
2. Invoke a command to create a DataSet object using an adapter object.
3. Use the DataSet object in code to display data or to change items in the database.
4. Invoke a command to update the database from the DataSet object using an adapter object.
5. Close the database connection if you explicitly opened it in step 2 using the Open method. Invoking commands without first invoking the Open method implicitly opens and closes the connection with each request.
1. What is the full form of ADO?
The full form of ADO is ActiveX Data Object.
2. Explain ADO.NET in brief.
ADO.NET is a very important feature of .NET Framework, which is used to work with data that is stored in structured data sources, such as databases and XML files. The following are some of the important features of ADO.NET:
Contains a number of classes that provide you with various methods and attributes to manage the communication between your application and data source.
Enables you to access different data sources, such as Microsoft SQL Server, and XML, as per your requirements.
Provides a rich set of features, such as connection and commands that can be used to develop robust and highly efficient data services in .NET applications.
Provides various data providers that are specific to databases produced by various vendors. For example, ADO.NET has a separate provider to access data from Oracle databases; whereas, another provider is used to access data from SQL databases.
3. What are major difference between classic ADO and ADO.NET?
Following are some major differences between both
In ADO we have recordset and in ADO.NET we have dataset.
In recordset we can only have one table. If we want to accommodate more than one tables. We need to do inner join and fill the recordset. Dataset can have multiple tables.
All data persist in XML as compared to classic ADO where data persisted in Binary format also.
4. What are the two fundamental objects in ADO.NET?
DataReader and DataSet are the two fundamental objects in ADO.NET.
5. What are the benefits of using of ADO.NET in .NET 4.0.
The following are the benefits of using ADO.NET in .NET 4.0 are as follows:
Language-Integrated Query (LINQ) - Adds native data-querying capabilities to .NET languages by using a syntax similar to that of SQL. This means that LINQ simplifies querying by eliminating the need to use a separate query language. LINQ is an innovative technology that was introduced in .NET Framework 3.5.
LINQ to DataSet - Allows you to implement LINQ queries for disconnected data stored in a dataset. LINQ to DataSet enables you to query data that is cached in a DataSet object. DataSet objects allow you to use a copy of the data stored in the tables of a database, without actually getting connected to the database.
LINQ to SQL - Allows you to create queries for data stored in SQL server database in your .NET application. You can use the LINQ to SQL technology to translate a query into a SQL query and then use it to retrieve or manipulate data contained in tables of an SQL Server database. LINQ to SQL supports all the key functions that you like to perform while working with SQL, that is, you can insert, update, and delete information from a table.
SqlClient Support for SQL Server 2008 - Specifies that with the starting of .NET Framework version 3.5 Service Pack (SP) 1, .NET Framework Data Provider for SQL Server (System.Data.SqlClient namespace) includes all the new features that make it fully compatible with SQL Server 2008 Database Engine.
ADO.NET Data Platform - Specifies that with the release of .NET Framework 3.5 Service Pack (SP) 1, an Entity Framework 3.5 was introduced that provides a set of Entity Data Model (EDM) functions. These functions are supported by all the data providers; thereby, reducing the amount of coding and maintenance in your application. In .NET Framework 4.0, many new functions, such as string, aggregate, mathematical, and date/time functions have been added.
6. Which namespaces are required to enable the use of databases in ASP.NET pages?
The following namespaces are required to enable the use of databases in ASP.NET pages:
The System.Data namespace.
The System.Data.OleDb namespace (to use any data provider, such as Access, Oracle, or SQL)
The System.Data.SQLClient namespace (specifically to use SQL as the data provider)
7. Explain the DataAdapter.Update() and DataSetAcceptChanges() methods.
The DataAdapter.Update() method calls any of the DML statements, such as the UPDATE, INSERT, or DELETE statements, as the case may be to update, insert, or delete a row in a DataSet. The DataSet.Acceptchanges() method reflects all the changes made to the row since the last time the AcceptChanges() method was called.
8. What is the meaning of object pooling?
Object pooling is a concept of storing a pool (group) of objects in memory that can be reused later as needed. Whenever, a new object is required to create, an object from the pool can be allocated for this request; thereby, minimizing the object creation. A pool can also refer to a group of connections and threads. Pooling, therefore, helps in minimizing the use of system resources, improves system scalability, and performance.
9. Which properties are used to bind a DataGridView control?
The DataSource property and the DataMember property are used to bind a DataGridView control.
10. What property must be set and what method must be called in your code to bind the data from some data source to the Repeater control?
You must set the DataSource property and call the DataBind() method.
11. Mention the namespace that is used to include .NET Data Provider for SQL server in .NET code.
The System.Data.SqlClient namespace.
12. What is the difference between OLEDB Provider and SqlClient?
With respect to usage, there is no difference between OLEDB Provider and SqlClient. The difference lies in their performance. SqlClient is explicitly used to connect your application to SQL server directly, OLEDB Provider is generic for various databases, such as Oracle and Access including SQL Server.
Therefore, there will be an overhead which leads to performance degradation.
13. Name the two properties of the GridView control that have to be specified to turn on sorting and paging.
The properties of the GridView control that need to be specified to turn on sorting and paging are as follows:
The AllowSorting property of the Gridview control indicates whether sorting is enabled or not. You should set the AllowSorting property to True to enable sorting.
The AllowPaging property of the Gridview control indicates whether paging is enabled or not. You should set the AllowPaging property to True to enable paging.
14. Mention different types of data providers available in .NET Framework.
.NET Framework Data Provider for SQL Server - Provides access to Microsoft SQL Server 7.0 or later version. It uses the System.Data.SqlClient namespace.
.NET Framework Data Provider for OLE DB - Provides access to databases exposed by using OLE DB. It uses the System.Data.OleDb namespace.
.NET Framework Data Provider for ODBC - Provides access to databases exposed by using ODBC. It uses the System.Data.Odbc namespace.
.NET Framework Data Provider for Oracle - Provides access to Oracle database 8.1.7 or later versions. It uses the System.Data.OracleClient namespace.
15. Which architecture does Datasets follow?
Datasets follow the disconnected data architecture.
16. What is the role of the DataSet object in ADO.NET?
One of the major component of ADO.NET is the DataSet object, which always remains disconnected from the database and reduces the load on the database.
17. What is a DataReader object?
The DataReader object helps in retrieving the data from a database in a forward-only, read-only mode. The base class for all the DataReader objects is the DbDataReader class.
The DataReader object is returned as a result of calling the ExecuteReader() method of the Command object. The DataReader object enables faster retrieval of data from databases and enhances the performance of .NET applications by providing rapid data access speed. However, it is less preferred as compared to the DataAdapter object because the DataReader object needs an Open connection till it completes reading all the rows of the specified table.
An Open connection to read data from large tables consumes most of the system resources. When multiple client applications simultaneously access a database by using the DataReader object, the performance of data retrieval and other related processes is substantially reduced. In such a case, the database might refuse connections to other .NET applications until other clients free the resources.
18. How can you identify whether or not any changes are made to the DataSet object since it was last loaded?
The DataSet object provides the following two methods to track down the changes:
The GetChanges() method - Returns the DataSet object, which is changed since it was loaded or since the AcceptChanges() method was executed.
The HasChanges() method - Indicates if any changes occurred since the DataSet object was loaded or after a call to the AcceptChanges() method was made.
If you want to revert all changes since the DataSet object was loaded, use the RejectChanges() method.
19. Which property is used to check whether a DataReader is closed or opened?
The IsClosed property is used to check whether a DataReader is closed or opened. This property returns a true value if a Data Reader is closed, otherwise a false value is returned.
20. Name the method that needs to be invoked on the DataAdapter control to fill the generated DataSet with data?
The Fill() method is used to fill the dataset with data.
21. What is the use of the Connection object?
The Connection object is used to connect your application to a specific data source by providing the required authentication information in connection string. The connection object is used according to the type of the data source. For example, the OleDbConnection object is used with an OLE-DB provider and the SqlConnection object is used with an MS SQL Server.
22. What is the use of the CommandBuilder class?
The CommandBuilder class is used to automatically update a database according to the changes made in a DataSet.
This class automatically registers itself as an event listener to the RowUpdating event. Whenever data inside a row changes, the object of the CommandBuilder class automatically generates an SQL statement and uses the SelectCommand property to commit the changes made in DataSet.
OLEDB provider in .NET Framework has the OleDbCommandBuiider class; whereas, the SQL provider has the SqlCommandBuilder class.
23. Explain the architecture of ADO.NET in brief.
AD0.NET consists of two fundamental components:
The DataSet, which is disconnected from the data source and does not need to know where the data that it holds is retrieved from.
The .net data provider, which allows you to connect your application to the data source and execute the SQL commands against it.
The data provider contains the Connection, Command, DataReader, and DataAdapter objects. The Connection object provides connectivity to the database. The Command object provides access to database commands to retrieve and manipulate data in a database. The DataReader object retrieves data from the database in the readonly and forward-only mode. The DataAdapter object uses Command objects to execute SQL commands. The DataAdapter object loads the DataSet object with data and also updates changes that you have made to the data in the DataSet object back to the database.
24. Describe the disconnected architecture of ADO.NET's data access model.
ADO.NET maintains a disconnected database access model, which means, the application never remains connected constantly to the data source. Any changes and operations done on the data are saved in a local copy (dataset) that acts as a data source. Whenever, the connection to the server is re-established, these changes are sent back to the server, in which these changes are saved in the actual database or data source.
25. What are the usages of the Command object in ADO.NET?
The following are the usages of the Command object in AD0.NET:
The Command object in AD0.NET executes a command against the database and retrieves a DataReader or DataSet object.
It also executes the INSERT, UPDATE, or DELETE command against the database.
All the command objects are derived from the DbCommand class.
The command object is represented by two classes: SqlCommand and OleDbCommand.
The Command object provides three methods to execute commands on the database:
The ExecuteNonQuery() method executes the commands and does not return any value.
The ExecuteScalar() method returns a single value from a database query.
The ExecuteReader() method returns a result set by using the DataReader object.
26. What are the pre-requisites for connection pooling?
The prerequisites for connection pooling are as follows:
There must be multiple processes to share the same connection describing the same parameters and security settings.
The connection string must be identical.
27. What is connection pooling?
Connection pooling refers to the task of grouping database connections in cache to make them reusable because opening new connections every time to a database is a time-consuming process. Therefore, connection pooling enables you to reuse already existing and active database connections, whenever required, and increasing the performance of your application.
You can enable or disable connection pooling in your application by setting the pooling property to either true or false in connection string. By default, it is enabled in an application.
28. What are the various methods provided by the DataSet object to generate XML?
The various methods provided by the DataSet object to generate XML are:
ReadXml() - Reads XML document into a DataSet object.
GetXml() - Returns a string containing an XML document.
WriteXml() - Writes an XML data to disk.
29. Out of Windows authentication and SQL Server authentication, which authentication technique is considered as a trusted authentication method?
The Windows authentication technique is considered as a trusted authentication method because the username and password are checked with the Windows credentials stored in the Active Directory.
The SQL Server Authentication technique is not trusted as all the values are verified by SQL Server only.
30. How would you connect to a database by using .NET?
The connection class is used to connect a .NET application with a database.
31. Which adapter should you use, if you want to get the data from an Access database?
OleDbDataAdapter is used to get the data from an Access database.
32. Which object is used to add a relationship between two DataTable objects?
The DataRelation object is used to add relationship between two DataTable objects.
33. What are different types of authentication techniques that are used in connection strings to connect .NET applications with Microsoft SQL Server?
.NET applications can use two different techniques to authenticate and connect with SQL Server. These techniques are as follows:
The Windows Authentication option
The SQL Server Authentication option
34. Explain the new features in ADO.NET Entity Framework 4.0.
ADO.NET Entity Framework 4.0 is introduced in .NET Framework 4.0 and includes the following new features:
Persistence Ignorance - Facilitates you to define your own Plain Old CLR Objects (POCO) which are independent of any specific persistence technology.
Deferred or Lazy Loading - Specifies that related entities can be loaded automatically whenever required. You can enable lazy loading in your application by setting the DeferredLoadingEnabled property to true.
Self-Tracking Entities - Refers to the entities that are able to track their own changes. These changes can be passed across process boundaries and saved to the database.
Model-First Development - Allows you to create your own EDM and then generate relational model (database) from that EDM with matching tables and relations.
Built-in Functions - Enables you to use built-in SQL Server functions directly in your queries.
Model-Defined Functions - Enables you to use the functions that are defined in conceptual schema definition language (CSDL).
35. What is the difference between the Clone() and Copy() methods of the DataSet class?
The Clone() method copies only the structure of a DataSet. The copied structure includes all the relation, constraint, and DataTable schemas used by the DataSet. The Clone() method does not copy the data, which is stored in the DataSet.
The Copy() method copies the structure as well as the data stored in the DataSet.
36. What is the use of DataView?
User-defined view of a table is contained in a DataView. A complete table or a small section of table depending on some criteria can be presented by an object of the DataView class. You can use this class to sort and find data within DataTable.
The DataView class has the following methods:
Find() - Finds a row in a DataView by using sort key value.
FindRows() - Uses the sort key value to match it with the columns of DataRowView objects. It returns an array of all the corresponding objects of DataRowView whose columns match with the sort key value.
AddNew() - Adds a new row to the DataView object.
Delete() - Deletes the specified row from the DataView object according to the specified index.
37. What are the parameters that control most of connection pooling behaviors?
The parameters that control most of connection pooling behaviors are as follows:
Connect Timeout
Max Pool Size
Min Pool Size
Pooling
38. How can you add or remove rows from the DataTable object of DataSet?
The DataRowCollection class defines the collection of rows for the DataTable object in a DataSet. The DataTable class provides the NewRow() method to add a new DataRow to DataTable. The NewRow method creates a new row, which implements the same schema as applied to the DataTable. The following are the methods provided by the DataRowCollection object:
Add() - Adds a new row to DataRowCollection.
Remove()- Removes a DataRow object from DataRowCollection.
RemoveAt() - Removes a row whose location is specified by an index number.
39. Explain in brief DataAdapter class in ADO.NET.
The DataAdapter class retrieves data from the database, stores data in a dataset, and reflects the changes made in the dataset to the database. The DataAdapter class acts as an intermediary for all the communication between the database and the DataSet object. The DataAdapter Class is used to fill a DataTable or DataSet Object with data from the database using the Fill() method. The DataAdapter class applies the changes made in dataset to the database by calling the Update() method.
The DataAdapter class provides four properties that represent the database command:
SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand.
SQL SERVER – Stored Procedure Optimization
Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method
-- Instead of
SELECT * FROM MyTable -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.MyProc -- Preferred method
--Instead of
EXEC MyProc -- Avoid this method
Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')
Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.
Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH
Interview question: Tell me about sql Optimization?
1) Avoid repeated scan of big tables, instead try to use views which can cut down number of rows.
2) Avoid too much of : Normalisation, excess usage of cursor & temp tables, recompilation of stored procedure, complicated joins, Indexing, Order by. [once again, point says "avoid too much use of following"]
3) Stored procedures send flag value(sometimes know as done_proc flag) after execution of every statement in it. this value travels on network. Avoid it if you dont need it by using "SET NOCOUNT ON"
4) Always try to use some where clause in SELECT, avoid bringing whole table data.
5) For SQL Server users, use the SQL Server Profiler to create Trace Wizard.
6) "Union All" combines data from two tables. "Union" combines data from two tables and then finds distinct rows. If duplicate rows doesnt matter in your query, use "union All" over "Union", this will make ur query faster.
7) Avoid using "DISTINCT" whenever possible. It takes substantial processing.
8) Avoid "SELECT *", instead write name of columns in your query.
9) If you are MS SQL 2005 user, "Top" keyword can be used with DML(Delete, Insert, Update) statements.
10) In "Where" clause "=" is more optimised compared to "<>" .
11) If using "LIKE" statement, use preceding character, use 'm%' instead of '%m' , this will try to use index internally.
12) If you need summation, always use trigger, trigger has some overhead, but will help you avoid summation after every transaction.
13) If your app needs to insert binary data into image column, use stored procedure instead of insert statement, because insert statement has to convert binary to string, becomes double in size, then once it reaches sql, again it has get re-converted to binary type from string.
14) Avoid temp table, instead use Derived tables.
15) If you are going to insert same value again & again in table, use default table value.
16) Stored procedures are good, cos their execution plan stays in cache. Every sql statement you run needs get validated within sql engine, with stored procedure this validation thing happens only first time and remains ready to use for all other calls.
17) Avoid naming your procedure starting with sp_ for eg : sp_FindEmployees . Every procedure with this name, server first tries to search in master database.
18) Validate all parameters in start of procedure.
19) Avoid change in execution plan of stored procedure (for eg: change in where clause..everytime query is run) this doesnt give cache benefit. Instead deletgate call to different stored procedures using if then statements within stored procedure.
20) "Exec database_name.dbo.myProcedure" is better than "Exec myProcedure" i.e use fully qualified names.
21) Identify which queries runs frequently and is resource intensive, make Indexes only for them.
22) Static tables with master data (tables which dont changed thru DML statement much) can be indexed.
23) SOUNDEX in where clause helps to escape Index.
24) This one is popular question. how to avoid cursors ? Cursors usually involves row by row update, instead try to use SET operation.
for eg:
Age between 30 and 40 -- 5000 hike
Age between 40 and 55 -- 7000 hike
Age between 55 and 65 -- 9000 hike
UPDATE tbl_emp SET salary =
CASE WHEN AGE BETWEEN 30 AND 40 THEN salary + 5000
WHEN AGE BETWEEN 40 AND 55 THEN salary + 7000
WHEN AGE BETWEEN 55 AND 65 THEN salary + 10000
END
25) Another situation where cursors get used often, is when we want to call stored procedure, depending on value of one particular column. Use "While" loop to avoid it.
Bonus Point: Best way to use this article is, keep it open in front of you while giving telephonic interview and wait for interviewer to ask you "How to optimise SQL".
How IIS Process ASP.NET Request
Introduction
When request come from client to the server a lot of operation is performed before sending response to the client. This is all about how IIS Process the request. Here I am not going to describe the Page Life Cycle and there events, this article is all about the operation of IIS Level. Before we start with the actual details, let’s start from the beginning so that each and everyone understand it's details easily. Please provide your valuable feedback and suggestion to improve this article.
What is Web Server ?
When we run our ASP.NET Web Application from visual studio IDE, VS Integrated ASP.NET Engine is responsible to execute all kind of asp.net requests and responses. The process name is "WebDev.WebServer.Exe" which actually takw care of all request and response of an web application which is running from Visual Studio IDE.
Now, the name “Web Server” come into picture when we want to host the application on a centralized location and wanted to access from many locations. Web server is responsible for handle all the requests that are coming from clients, process them and provide the responses.
What is IIS ?
IIS (Internet Information Server) is one of the most powerful web servers from Microsoft that is used to host your ASP.NET Web application. IIS has it's own ASP.NET Process Engine to handle the ASP.NET request. So, when a request comes from client to server, IIS takes that request and process it and send response back to clients.
Request Processing :
Hope, till now it’s clear to you that what is Web server and IIS is and what is the use of them. Now let’s have a look how they do things internally. Before we move ahead, you have to know about two main concepts
1. Worker Process
2. Application Pool
Worker Process: Worker Process (w3wp.exe) runs the ASP.Net application in IIS. This process is responsible to manage all the request and response that are coming from client system. All the ASP.Net functionality runs under the scope of worker process. When a request comes to the server from a client worker process is responsible to generate the request and response. In a single word we can say worker process is the heart of ASP.NET Web Application which runs on IIS.
Application Pool: Application pool is the container of worker process. Application pools is used to separate sets of IIS worker processes that share the same configuration. Application pools enables a better security, reliability, and availability for any web application. The worker process serves as the process boundary that separates each application pool so that when one worker process or application is having an issue or recycles, other applications or worker processes are not affected. This makes sure that a particular web application doesn't not impact other web application as they they are configured into different application pools.
Application Pool with multiple worker process is called “Web Garden”.
Now, I have covered all the basic stuff like Web server, Application Pool, Worker process. Now let’s have look how IIS process the request when a new request comes up from client.
If we look into the IIS 6.0 Architecture, we can divided them into Two Layer
1. Kernel Mode
2. User Mode
Now, Kernel mode is introduced with IIS 6.0, which contains the HTTP.SYS. So whenever a request comes from Client to Server, it will hit HTTP.SYS First.
Now, HTTP.SYS is Responsible for pass the request to particular Application pool. Now here is one question, How HTTP.SYS comes to know where to send the request? This is not a random pickup. Whenever we creates a new Application Pool, the ID of the Application Pool is being generated and it’s registered with the HTTP.SYS. So whenever HTTP.SYS Received the request from any web application, it checks for the Application Pool and based on the application pool it send the request.
So, this was the first steps of IIS Request Processing.
Till now, Client Requested for some information and request came to the Kernel level of IIS means at HTTP.SYS. HTTP.SYS has been identified the name of the application pool where to send. Now, let’s see how this request moves from HTTP.SYS to Application Pool.
In User Level of IIS, we have Web Admin Services (WAS) which takes the request from HTTP.SYS and pass it to the respective application pool.
When Application pool receive the request, it simply pass the request to worker process (w3wp.exe) . The worker process “w3wp.exe” looks up the URL of the request in order to load the correct ISAPI extension. ISAPI extensions are the IIS way to handle requests for different resources. Once ASP.NET is installed, it installs its own ISAPI extension (aspnet_isapi.dll) and adds the mapping into IIS.
Note : Sometimes if we install IIS after installing asp.net, we need to register the extension with IIS using aspnet_regiis command.
When Worker process loads the aspnet_isapi.dll, it start an HTTPRuntime, which is the entry point of an application. HTTPRuntime is a class which calls the ProcessRequest method to start Processing.
When this methods called, a new instance of HTTPContext is been created. Which is accessible using HTTPContext.Current Properties. This object still remains alive during life time of object request. Using HttpContext.Current we can access some other objects like Request, Response, Session etc.
After that HttpRuntime load an HttpApplication object with the help of HttpApplicationFactory class.. Each and every request should pass through the corresponding HTTPModule to reach to HTTPHandler, this list of module are configured by the HTTPApplication.
Now, the concept comes called “HTTPPipeline”. It is called a pipeline because it contains a set of HttpModules ( For Both Web.config and Machine.config level) that intercept the request on its way to the HttpHandler. HTTPModules are classes that have access to the incoming request. We can also create our own HTTPModule if we need to handle anything during upcoming request and response.
HTTP Handlers are the endpoints in the HTTP pipeline. All request that are passing through the HTTPModule should reached to HTTPHandler. Then HTTP Handler generates the output for the requested resource. So, when we requesting for any aspx web pages, it returns the corresponding HTML output.
All the request now passes from httpModule to respective HTTPHandler then method and the ASP.NET Page life cycle starts. This ends the IIS Request processing and start the ASP.NET Page Lifecycle.
Conclusion
When client request for some information from a web server, request first reaches to HTTP.SYS of IIS. HTTP.SYS then send the request to respective Application Pool. Application Pool then forward the request to worker process to load the ISAPI Extension which will create an HTTPRuntime Object to Process the request via HTTPModule and HTTPHanlder. After that the ASP.NET Page LifeCycle events starts.
What is AppDomain
An application domain is the CLR equivalent of an operation system’s process. An application domain is used to isolate applications from one another. This is the same way an operating system process works. The separation is required so that applications do not affect one another. This separation is achieved by making sure than any given unique
virtual address space runs exactly one application and scopes the resources for the process or application domain using that addess space.
