Overblog Follow this blog
Administration Create my blog
Balavardhan Reddy Narani

Recent posts

Interview Questions of SQLServer

March 17 2013 , Written by Balavardhan Reddy Published on #SQL Server2005

Do you know what are User Defined functions.

Scalar, Inline Table-Valued and Multi-statement Table-valued.

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.

Can I return a table from a user defined function?

If I return a table from user defined function, can I join that table with another table?

What are differences between a function and SP
Return Value, and not exec
Not mandatory to return value, executable


What are views
Virtual Tables

What are advantages of a View?
More Performance

Why would I need to go for a view?
Instead of writing query in sp, you just create a view and call that obj in sp.. good performance

Can I create a view with Order by clause
No, because View is also a virtual table, so you can order the result set of View


Diff between Delete and Truncate table
TRUNCATE is a DDL command whereas DELETE is a DML command.

TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deletinga table you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
In case of TRUNCATE Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.


What is a temporary table
table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table
If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created
If I create a temp table the memory allocated in Tempdb

what is the diff between temp table and Variable table
    Table variables don't need to be dropped when you are done with them.
If I want to creaate a where condition dynamically and then assign it to Select statement how do I do it and how can I execute it

If you have less than 100 rows generally use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.
If you need to create indexes on it then you must use a temporary table.
When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

what are differrent contraints

Domain Integerity Constraints:
Check: Limits the range of values or domain, checks the values which a col can have.
Referntial Integrity Constraints:
Foreign Key: Referntial integrity b/w 2 tables, one table col values based on other table col values.
Entity Integrity Constraints:
Default Constraint: defines the default value for a column.
Null: allows null value.
NOT null:Donot allow null value.
Primary Key: Ensure no duplicate data and not allow nulls.
Unique Key: donot allow duplicate values, but allows one null per col.Can have multiple unique constraints on a table


What is rank keyword in SQL Server.
Rank() Applies to monotonically increasing number for each value in the set
Rank() and Row_Number differ only if there are ties. (rank same for two rows.. next rows has incremental)
In Dense_Rank() Method the rank is increasing number for each value in the set.
NTILE(n)- Which splits the records into partition based on the Column in the Over() and assigns the Rank for the partition.


What is OLAP?
Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data.

What is OLTP?
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.


What is SQL Server Agent?
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs.
we have roles created for using SQL Server agents.
·         SQLAgentUserRole
·         SQLAgentReaderRole
·         SQLAgentOperatorRole


Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.


When we done operation on SQL SERVER that is not commited directly to the database example:we are deleting bulk records from server but in between power shut down or some thing happened then that transaction is not committed from our side if we create check point on that delete statement after deleting some records it will save as check point next no need to delete those records again if check point is not there means we have to start form scratch.
All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no Check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.
When we stop the SQL Server it will take long time because Checkpoint is also fired.

Read more

Sqlserver - Query to Get Size of the data

March 17 2013 , Written by Balavardhan Reddy Published on #SQL Server2005

Query to get the size of a column data for each row.

Select DATALENGTH(<<ColumnName>>) from <<TableName>>

Query to get the size of a column data for each group.

Select DATALENGTH(<<ColumnName>>), <<Column Name>> from <<TableName>> Group by <<Column Name>>

Read more

Query to remove the matching expression SQLServer

October 12 2012 , Written by Balavardhan Reddy Published on #SQL Server2005

A simple query to remove the particular patreen from the entire string, its just like reggular expression in the ASP.Net

Query to strip the matching expression charectors from string , 

Declare @myExpression varchar(50)

Declare @queryString varchar(max)

SET @myExpression =  '%['+@myExpression +']%'

    WHILE PatIndex(@myExpression, @queryString ) > 0

        SET @queryString = Stuff(@queryString , PatIndex(@myExpression, @queryString ), 1, '')

Select @queryString

Ex : 

set @queryString = '2fsfse54646vdf00_*gf,,dg45$'

set @myExpression = 'a-z*$,'

Read more


June 6 2012 , Written by Balavardhan Reddy

Getting Started with iTextSharp


For the ASP.NET developers out there, if you need to generate PDF reports from your code, there is an open source library called iText# (iTextSharp) that is worth checking out. iTextSharp is a port of the Java iText library written entirely in C#. Source and binaries are available at http://sourceforge.net/projects/itextsharp/.
From what I’ve seen, it works well and only requires one .dll to be copied to your bin directory. Unfortunately, there is no documentation with the product, so I’ll share some of the code I used for a proof of concept that should hopefully get you started.
Using Visual Studio 2005 or greater, or your favorite text editor, create a page with a label called Label1 and a button called Button1. You can skip the label if you want, as I just used it to dump exception output, but I do have it referenced in code. The example will work with any of the Express (free) versions of Visual Studio as well.
First of all, ensure your page has references to ITextSharp.text, ITextSharp.text.PDF, System.IO, and System.Text.

PDFs in ASP.NET using iTextSharp

The "Portable Document Format" (PDF) is an open standard for document exchange. This file format, created by Adobe Systems in 1993, is used for representing documents in a manner independent of application software, hardware, and operating systems. Each PDF file encapsulates a complete description of a fixed-layout flat document, including the text, fonts, graphics, and other information needed to display it. There are many vendors that provide PDF readers across virtually all operating systems, and many proprietary programs, such as Microsoft Word, include a "Save as PDF" option. A person writing a document using Microsoft Word for Windows can save the document as a PDF, which can then be read by others whether or not they are using Windows and whether or not they have Microsoft Word installed. 

Due to their ubiquity and fixed-layout capabilities, it's not uncommon for a websites to use PDF technology. For example, when purchasing goods at an online store you may be offered the ability to download an invoice as a PDF file. PDFs also support form fields, which are user interface elements like textboxes, checkboxes, comboboxes, and the like. 

But unfortunately the .NET framework does not contain any native way to work with PDF files. So, if you want to generate or work with PDF files as part of your ASP.NET web application, you will have to rely on one of the many third party components that are available. One of the popular and free components is iTextSharp library, which is ported from famous open source java library 'iText' for PDF generation. iTextSharp is written entirely in C# for the .NET platform and has a separate codebase. 

In this and next few post i'll be explaining the features of iTextSharp library and that how to programmatically work with PDF files from an ASP.NET application. 

First thing to do is to download the iTextSharp from here (used in this tutorial). You will be presented with a .zip file containing itextsharp.dll. Unzip this, and save it somewhere on your machine. Now create a new Web Site from within Visual Studio or Visual Web Developer. Make sure that you have added the Bin folder from the options in the Add ASP.NET Folder option. 

Right-click on the Bin folder and select Add Reference. The dialogue box should appear. Choose the Browse tab, and locate the itextsharp.dll you saved earlier. 

The dll will be copied to your Bin directory, and is now available to the web site or project. Make sure your solution explorer looks similar to mine. 

Now to start with, it is best to add a couple of using statements to the default ones in your code-behind to avoid typing full references to the dll. Also you would need to reference  System.IO, as you will be creating, opening and closing files, and classes in this namespace are required. And if your PDF would contain ASCII or Unicode character encodings, you'll need to add System.Text namespace too.

using System.IO;
using System.Text;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.

After adding these namespaces to your code behind file let's start creating a PDF document programmatically from the ground up level. 
The principal object within iTextSharp is the Document object. You need to create an instance of this to be able to work with your PDF in memory. So, the first thing to do is to instantiate one. (Here it's important to decide that upon what event whether on a new page load or on a button click you want to generate the PDF.)

//Create a Document object in memory with following settings, which models the PDF you are creating
Document pdfDoc = new Document(PageSize.A4, 50f, 50f, 50f, 50f);

 //Create a PdfWriter object which is responsible for serializing the PDF document you create to some store, such as in memory or to disk PdfWriter.GetInstance(pdfDoc, Response.OutputStream); //Set some header stuff in the PDF pdfDoc.AddTitle("Some Name"); pdfDoc.AddSubject("Some Subject"); pdfDoc.AddCreator("iTextSharp_v5.3.1"); pdfDoc.AddAuthor("M.Rashid Khan"); //Open the Document pdfDoc.Open();  
 //Add any paragraph text (OPTIONAL) Paragraph myParagraph = new Paragraph("Lorem ipsum dolor sit amet, consectetuer adipiscing elit."); pdfDoc.Add(myParagraph); 
 //Put any image in PDF (OPTIONAL) iTextSharp.text.Image myImage = iTextSharp.text.Image.GetInstance(Request.MapPath("~/Balu.jpg")); myImage.Alignment = iTextSharp.text.Image.MIDDLE_ALIGN; myImage.ScalePercent(24f); pdfDoc.Add(myImage); 
 //Create a table in PDF (OPTIONAL) PdfPTable myTable = new PdfPTable(3); //Actual width of table in points myTable.TotalWidth = 484f; //fix the absolute width of the table myTable.LockedWidth = true; //Leave a gap before and after the table myTable.SpacingBefore = 20f; myTable.SpacingAfter = 30f; //Set the column widths float[] widths = new float[] { 2f, 2f, 2f }; myTable.SetWidths(widths); //Horizontal alignment 0=Left, 1=Centre, 2=Right myTable.HorizontalAlignment = 1; 
 //Create the cells in table PdfPCell cell = new PdfPCell(new Phrase("New Table")); cell.Colspan = 3; cell.HorizontalAlignment = 1; myTable.AddCell(cell); myTable.AddCell("Col 1 Row 1"); myTable.AddCell("Col 2 Row 1"); myTable.AddCell("Col 3 Row 1"); myTable.AddCell("Col 1 Row 2"); myTable.AddCell("Col 2 Row 2"); myTable.AddCell("Col 3 Row 2");  
 //Following code closes the Document object and the PDF is streamed back to the visitor's browser pdfDoc.Close(); Response.ContentType = "application/pdf"; Response.AddHeader("Content-Disposition", "attachment;" + "filename=Simple.pdf"); Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.Write(pdfDoc); Response.End(); 
 So this was a basic tutorial for creating simple PDFs in ASP.NET. For advance and more professional features in PDFs like the one which can fetch data and tables directly from database, or PDFs with advance formatting, I'll probably write another advance tutorial later on PDFs creation in ASP.NET using iTesxtSharp.  
Read more


December 29 2011 , Written by Balavardhan Reddy Published on #ASP.Net







   ASP.NET, the next version of ASP, is a programming framework used to create enterprise-class Web Applications. These applications are accessble on a global basis leading to effecient information management. The advantages ASP.NET offers is more than just the next version of ASP.



Since 1995, Microsoft has been constantly working to shift it's focus from Windows-based platforms to the Internet. As a result, Microsoft introduced ASP (Active Server Pages) in November 1996. ASP offered the efficiency of ISAPI applications along with a new level of simplicity that made it easy to understand and use. However, ASP script was an interpreted script and consisted unstructured code and was difficult to debug and maintain. As the web consists of many different technologies, software integrationfor Web development was complicated and required to understand many different technologies. Also, as applications grew bigger in size and became more complex, thenumber of lines of source code in ASP applications increased dramatically and was hard to maintain. Therefore, an architecture was needed that would allow development of Web applications in a structured and consistent way.

The .NET Framework was introduced with a vision to create globally distributed software with Internet functionality and interoperability. The .NET Framework consists of many class libraries, includes multiple language support and a common execution platform. It's a very flexible foundation on which many different types of top class applications can be developed that do different things. Developing Internet applications with the .NET Framework is very easy. ASP.NET is built into this framework, we can create ASP.NET applications using any of the built-in languages.

Unlike ASP, ASP.NET uses the Common Language Runtime (CLR) provided by the .NET Framework. This CLR manages execution of the code we write. ASP.NET code is a compiled CLR code instead of  interpreted code (ASP). CLR also allows objects written in different languages to interact with each other. The CLR makes developement of Web applications simple.

Advantages Using ASP.NET

  • ASP.NET drastically reduces the amount of code required to build large applications
  • ASP.NET makes development simpler and easier to maintain with an event-driven, server-side programming model
  • ASP.NET pages are easy to write and maintain because the source code and HTML are together
  • The source code is executed on the server. The pages have lots of power and flexibility by this approach
  • The source code is compiled the first time the page is requested. Execution is fast as the Web Server compiles the page the first time it is requested. The server saves the compiled version of the page for use next time the page is requested
  • The HTML produced by the ASP.NET page is sent back to the browser. The application source code you write is not sent and is not easily stolen
  • ASP.NET makes for easy deployment. There is no need to register components because the configuration information is built-in
  • The Web server continuously monitors the pages, components and applications running on it. If it noticies memory leaks, infinite loops, other illegal software or activities, it seamlessly kills those activities and restarts itself
  • ASP.NET validates information (validation controls) entered by the user without writing a single line of code
  • ASP.NET easily works with ADO .NET using data-binding and page formatting features
  • ASP.NET applications run fater and counters large volumes of users without performance problems

Client-Side Scripting

Javascript and VBScript and generally used for Client-side scripting. Client-side scripting executes in the browser after the page is loaded. Using client-side scripting you can add some cool features to your page. Both, HTML and the script are together in the same file and the script is download as part of the page which anyone can view. A client-side script runs only on a browser that supports scripting and specifically the scripting language that is used. Since the script is in the same file as the HTML and as it executes on the machine you use, the page may take longer time to download.

Server-Side Scripting

ASP.NET is purely server-side technology. ASP.NET code executes on the server before it is sent to the browser. The code that is sent back to the browser is pure HTML and not ASP.NET code. Like client-side scripting, ASP.NET code is similar in a way that it allows you to write your code alongside HTML. Unlike client-side scripting, ASP.NET code is executed on the server and not in the browser. The script that you write alongside your HTML is not sent back to the browser and that prevents others from stealing the code you developed.

ASP.NET Features

ASP.NET is not just a simple upgrade or the latest version of ASP. ASP.NET combines unprecedented developer productivity with performance, reliability, and deployment. ASP.NET redesigns the whole process. It's still easy to grasp for new comers but it provides many new ways of managing projects. Below are the features of ASP.NET.

Easy Programming Model 

ASP.NET makes building real world Web applications dramatically easier. ASP.NET server controls enable an 
HTML-like style of declarative programming that let you build great pages with far less code than with classic ASP.  Displaying data, validating user input, and uploading files are all amazingly easy. Best of all, ASP.NET pages work in all browsers including Netscape, Opera, AOL, and Internet Explorer.

Flexible Language Options  

ASP.NET lets you leverage your current programming language skills.  Unlike classic ASP, which supports only interpreted VBScript and JScript, ASP.NET now supports more than 25 .NET languages (built-in support for VB.NET, C#, and JScript.NET), giving you unprecedented flexibility in your choice of language.

Great Tool Support 

You can harness the full power of ASP.NET using any text editor, even Notepad.  But Visual Studio .NET adds the productivity of Visual Basic-style development to the Web. Now you can visually design ASP.NET Web Forms using familiar drag-drop-doubleclick techniques, and enjoy full-fledged code support including statement completion and color-coding. VS.NET also provides integrated support for debugging and deploying ASP.NET Web applications. The Enterprise versions of Visual Studio .NET deliver life-cycle features to help organizations plan, analyze, design, build, test, and coordinate teams that develop ASP.NET Web applications.  These include UML class modeling, database modeling (conceptual, logical, and physical models), testing tools (functional, performance and scalability), and enterprise frameworks and templates, all available within the integrated Visual Studio .NET environment.

Rich Class Framework 

Application features that used to be hard to implement, or required a 3rd-party component, can now be added in just a few lines of code using the .NET Framework.  The .NET Framework offers over 4500 classes that encapsulate rich functionality like XML, data access, file upload, regular expressions, image generation, performance monitoring and logging, transactions, message queuing, SMTP mail, and much more. With Improved Performance and Scalability ASP.NET lets you use serve more users with the same hardware.

Compiled execution 

ASP.NET is much faster than classic ASP, while preserving the "just hit save" update model of ASP.  However, no explicit compile step is required. ASP.NET will automatically detect any changes, dynamically compile the files if needed, and store the compiled results to reuse for subsequent requests. Dynamic compilation ensures that your application is always up to date, and compiled execution makes it fast.  Most applications migrated from classic 
ASP see a 3x to 5x increase in pages served.

Rich output caching

ASP.NET output caching can dramatically improve the performance and scalability of your application. When output caching is enabled on a page, ASP.NET executes the page just once, and saves the result in memory in addition to sending it to the user.  When another user requests the same page, ASP.NET serves the cached result from memory without re-executing the page. Output caching is configurable, and can be used to cache individual regions or an entire page. Output caching can dramatically improve the performance of data-driven pages by eliminating the need to query the database on every request.

Web-Farm Session State

ASP.NET session state lets you share session data user-specific state values across all machines in your Web farm.  Now a user can hit different servers in the Web farm over multiple requests and still have full access to her session.  And since business components created with the .NET Framework are free-threaded, you no longer need to worry about thread affinity.

Enhanced Reliability

ASP.NET ensures that your application is always available to your users.

Memory Leak, DeadLock and Crash Protection 

ASP.NET automatically detects and recovers from errors like deadlocks and memory leaks to ensure your application is always available to your users.  For example, say that your application has a small memory leak, and that after a week the leak has tied up a significant percentage of your server's virtual memory. ASP.NET will detect this condition, automatically start up another copy of the ASP.NET worker process, and direct all new requests to the new process. Once the old process has finished processing its pending requests, it is gracefully disposed and the leaked memory is released. Automatically, without administrator intervention or any interruption of service, ASP.NET has recovered from the error.   

Easy Deployment

ASP.NET takes the pain out of deploying server applications. "No touch" application deployment. ASP.NET dramatically simplifies installation of your application. With ASP.NET, you can deploy an entire application as easily as an HTML page, just copy it to the server.  No need to run regsvr32 to register any components, and configuration settings are stored in an XML file within the application.

Dynamic update of running application 

ASP.NET now lets you update compiled components without restarting the web server. In the past with classic COM components, the developer would have to restart the web server each time he deployed an update.  With ASP.NET, you simply copy the component over the existing DLL, ASP.NET will automatically detect the change and start using the new code.

Easy Migration Path 

You don't have to migrate your existing applications to start using ASP.NET. ASP.NET runs on IIS side-by-side with classic ASP on Windows 2000 and Windows XP platforms. Your existing ASP applications continue to be processed by ASP.DLL, while new ASP.NET pages are processed by the new ASP.NET engine. You can migrate application by application, or single pages.  And ASP.NET even lets you continue to use your existing classic COM business components. 

XML Web Services 

XML Web services allow applications to communicate and share data over the Internet, regardless of operating system or programming language. ASP.NET makes exposing and calling XML Web Services simple. Any class can be converted into an XML Web Service with just a few lines of code, and can be called by any SOAP client.  Likewise, ASP.NET makes it incredibly easy to call XML Web Services from your application. No knowledge of networking, XML, or SOAP is required.

Mobile Web Device Support 

ASP.NET Mobile Controls let you easily target cell phones, PDAs and over 80 mobile Web devices. You write your application just once, and the mobile controls automatically generate WAP/WML, HTML, or iMode as required by the requesting device. 

What is ASP.NET?

Microsoft ASP.NET is a server side technology that enables programmers to build dynamic Web sites, web applications, and XML Web services. It is a part of the .NET based environment and is built on the Common Language Runtime (CLR) . So programmers can write ASP.NET code using any .NET compatible language.

Which is the latest version of ASP.NET? What were the previous versions released?

The latest version of ASP.NET is 4.0. There have been 3 versions of ASP.NET released as of date. They are as follows :

ASP.NET 1.0 – Released on January 16, 2002.

ASP.NET 1.1 – Released on April 24, 2003.

ASP.NET 2.0 – Released on November 7, 2005.

Additionally, ASP.NET 3.5 is tentatively to be released by the end of the 2007.

Explain the Event Life cycle of ASP.NET 2.0?

The events occur in the following sequence. Its best to turn on tracing(<% @Page Trace=”true”%>) and track the flow of events :

PreInit – This event represents the entry point of the page life cycle. If you need to change the Master page or theme programmatically, then this would be the event to do so. Dynamic controls are created in this event.

Init – Each control in the control collection is initialized.

Init Complete* - Page is initialized and the process is completed.

PreLoad* - This event is called before the loading of the page is completed.

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

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

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

PreRenderComplete* - Is called to explicitly state that the PreRender phase is completed.

SaveStateComplete* - In this event, the current state of the control is completely saved to the ViewState.

Unload – This event is typically used for closing files and database connections. At times, it is also used for logging some wrap-up tasks.

The events marked with * have been introduced in ASP.NET 2.0.

You have created an ASP.NET Application. How will you run it?

With ASP.NET 2.0, Visual Studio comes with an inbuilt ASP.NET Development Server to test your pages. It functions as a local Web server. The only limitation is that remote machines cannot access pages running on this local server. The second option is to deploy a Web application to a computer running IIS version 5 or 6 or 7.

Explain the AutoPostBack feature in ASP.NET?

AutoPostBack allows a control to automatically postback when an event is fired. For eg: If we have a Button control and want the event to be posted to the server for processing, we can set AutoPostBack = True on the button.

How do you disable AutoPostBack?

Hence the AutoPostBack can be disabled on an ASP.NET page by disabling AutoPostBack on all the controls of a page. AutoPostBack is caused by a control on the page.

What are the different code models available in ASP.NET 2.0?

There are 2 code models available in ASP.NET 2.0. One is the single-file page and the other one is the code behind page.

Which base class does the web form inherit from?

Page class in the System.Web.UI namespace.

Which are the new special folders that are introduced in ASP.NET 2.0?

There are seven new folders introduced in ASP.NET 2.0 :

\App_Browsers folder – Holds browser definitions(.brower) files which identify the browser and their capabilities.

\App_Code folder – Contains source code (.cs, .vb) files which are automatically compiled when placed in this folder. Additionally placing web service files generates a proxy class(out of .wsdl) and a typed dataset (out of .xsd).

\App_Data folder – Contains data store files like .mdf (Sql Express files), .mdb, XML files etc. This folder also stores the local db to maintain membership and role information.

\App_GlobalResources folder – Contains assembly resource files (.resx) which when placed in this folder are compiled automatically. In earlier versions, we were required to manually use the resgen.exe tool to compile resource files. These files can be accessed globally in the application.

\App_LocalResources folder – Contains assembly resource files (.resx) which can be used by a specific page or control.

\App_Themes folder – This folder contains .css and .skin files that define the appearance of web pages and controls.

\App_WebReferences folder – Replaces the previously used Web References folder. This folder contains the .disco, .wsdl, .xsd files that get generated when accessing remote web services.

Explain the ViewState in ASP.NET?

Http is a stateless protocol. Hence the state of controls is not saved between postbacks. Viewstate is the means of storing the state of server side controls between postbacks. The information is stored in HTML hidden fields. In other words, it is a snapshot of the contents of a page.

You can disable viewstate by a control by setting the EnableViewState property to false.

What does the EnableViewState property signify?

EnableViewState saves the state of an object in a page between postbacks. Objects are saved in a Base64 encoded string. If you do not need to store the page, turn it off as it adds to the page size.

There is an excellent article by Peter Bromberg to understand Viewstate in depth.

Explain the ASP.NET Page Directives?

Page directives configure the runtime environment that will execute the page. The complete list of directives is as follows:

@ Assembly - Links an assembly to the current page or user control declaratively.

@ Control - Defines control-specific attributes used by the ASP.NET page parser and compiler and can be included only in .ascx files (user controls).

@ Implements - Indicates that a page or user control implements a specified .NET Framework interface declaratively.

@ Import - Imports a namespace into a page or user control explicitly.

@ Master - Identifies a page as a master page and defines attributes used by the ASP.NET page parser and compiler and can be included only in .master files.

@ MasterType - Defines the class or virtual path used to type the Master property of a page.

@ OutputCache - Controls the output caching policies of a page or user control declaratively.

@ Page - Defines page-specific attributes used by the ASP.NET page parser and compiler and can be included only in .aspx files.

@ PreviousPageType - Creates a strongly typed reference to the source page from the target of a cross-page posting.

@ Reference - Links a page, user control, or COM control to the current page or user control declaratively.

@ Register - Associates aliases with namespaces and classes, which allow user controls and custom server controls to be rendered when included in a requested page or user control.

This list has been taken from here.

Explain the Validation Controls used in ASP.NET 2.0?

Validation controls allows you to validate a control against a set of rules. There are 6 different validation controls used in ASP.NET 2.0.

RequiredFieldValidator – Checks if the control is not empty when the form is submitted.

CompareValidator – Compares the value of one control to another using a comparison operator (equal, less than, greater than etc).

RangeValidator – Checks whether a value falls within a given range of number, date or string.

RegularExpressionValidator – Confirms that the value of a control matches a pattern defined by a regular expression. Eg: Email validation.

CustomValidator – Calls your own custom validation logic to perform validations that cannot be handled by the built in validators.

ValidationSummary – Show a summary of errors raised by each control on the page on a specific spot or in a message box.

How do you indentify that the page is post back?

By checking the IsPostBack property. If IsPostBack is True, the page has been posted back.

What are Master Pages?

Master pages is a template that is used to create web pages with a consistent layout throughout your application. Master Pages contains content placeholders to hold page specific content. When a page is requested, the contents of a Master page are merged with the content page, thereby giving a consistent layout.

How is a Master Page different from an ASP.NET page?

The MasterPage has a @Master top directive and contains ContentPlaceHolder server controls. It is quiet similar to an ASP.NET page.

How do you attach an exisiting page to a Master page?

By using the MasterPageFile attribute in the @Page directive and removing some markup.

How do you set the title of an ASP.NET page that is attached to a Master Page?

By using the Title property of the @Page directive in the content page. Eg:

<@Page MasterPageFile="Sample.master" Title="I hold content" %>

What is a nested master page? How do you create them?

A Nested master page is a master page associated with another master page. To create a nested master page, set the MasterPageFile attribute of the @Master directive to the name of the .master file of the base master page.

What are Themes?

Themes are a collection of CSS files, .skin files, and images. They are text based style definitions and are very similar to CSS, in that they provide a common look and feel throughout the website.

What are skins?

A theme contains one or more skin files. A skin is simply a text file with a .skin extension and contains definition of styles applied to server controls in an ASP.NET page. For eg:

<asp:button runat="server" BackColor="blue" BorderColor="Gray" Font-Bold ="true" ForeColor="white"/>

Defines a skin that will be applied to all buttons throughout to give it a consistent look and feel.

What is the difference between Skins and Css files?

Css is applied to HTML controls whereas skins are applied to server controls.

What is a User Control?

User controls are reusable controls, similar to web pages. They cannot be accessed directly.

Explain briefly the steps in creating a user control?

· Create a file with .ascx extension and place the @Control directive at top of the page.

· Included the user control in a Web Forms page using a @Register directive

What is a Custom Control?

Custom controls are compiled components that run on the server and that encapsulate user-interface and other related functionality into reusable packages. They can include all the design-time features of standard ASP.NET server controls, including full support for Visual Studio design features such as the Properties window, the visual designer, and the Toolbox.

What are the differences between user and custom controls?

User controls are easier to create in comparison to custom controls, however user controls can be less convenient to use in advanced scenarios.

User controls have limited support for consumers who use a visual design tool whereas custom controls have full visual design tool support for consumers.

A separate copy of the user control is required in each application that uses it whereas only a single copy of the custom control is required, in the global assembly cache, which makes maintenance easier.

A user control cannot be added to the Toolbox in Visual Studio whereas custom controls can be added to the Toolbox in Visual Studio.

User controls are good for static layout whereas custom controls are good for dynamic layout.

Where do you store your connection string information?

The connection string can be stored in configuration files (web.config).

What is the difference between ‘Web.config’ and ‘Machine.config’?

Web.config files are used to apply configuration settings to a particular web application whereas machine.config file is used to apply configuration settings for all the websites on a web server.

Web.config files are located in the application's root directory or inside a folder situated in a lower hierarchy. The machine.config is located in the Windows directory Microsoft.Net\Framework\Version\CONFIG.

There can be multiple web.config files in an application nested at different hierarchies. However there can be only one machine.config file on a web server.

What is the difference between Server.Transfer and Response.Redirect?

Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.

Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages.

Response.Redirect can be used to redirect a user to an external websites. Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server.

Response.Redirect changes the url in the browser. So they can be bookmarked. Whereas Server.Transfer retains the original url in the browser. It just replaces the contents of the previous page with the new one.

What method do you use to explicitly kill a users session?


What is a webservice?

Web Services are applications delivered as a service on the Web. Web services allow for programmatic access of business logic over the Web. Web services typically rely on XML-based protocols, messages, and interface descriptions for communication and access. Web services are designed to be used by other programs or applications rather than directly by end user. Programs invoking a Web service are called clients. SOAP over HTTP is the most commonly used protocol for invoking Web services.








Read more

ASP.NET Important Topics

December 29 2011 , Written by Balavardhan Reddy Published on #ASP.Net

1) Asp.net Page Life Cycle & Page Architecture.

2) Assemblies.

3) User Controls & Custom Controls.

4) Gridview Control.

5) Themes, Skins and Style Sheets.

6) Master Pages.

7) Site Maps.

8) State Management.

9) Security(Web.config)

    (Authentication & Authorization)

     i) Forms Authentication

    ii) Windows Authentication

    iii) Passport Authentication.

10) Caching

     - Output Caching

     - Data Caching

     - Fragment Caching.

Read more

FOR XML - SQL Server

June 9 2010 , Written by Balavardhan Reddy Published on #SQL Server2005


In Microsoft SQL Server 2000, we introduced the FOR XML clause to the SELECT statement. This clause provides the ability to aggregate the relational rowset returned by the SELECT statement into XML. FOR XML on the server supports three modes—RAW, AUTO, and EXPLICIT—that provide different transformation semantics.

mode generates single elements, which are named row, for each row returned.

The AUTO mode employs a heuristic to infer a simple, one element name-per-level hierarchy based on the lineage information and the order of the data in a SELECT statement.

Finally, the EXPLICIT mode requires a specific rowset format that can be mapped into almost any XML shape, while still being formulated by a single SQL query.

The EXPLICIT mode format is highly successful in achieving its goals. There are only a few things it cannot do (arbitrary recursive part-list trees are one of them). The preferred SQL expression to generate the rowset format is a formidable "query from hell," however.

Unfortunately, FOR XML results in SQL Server 2000 can only be consumed on the client side, and authoring complex XML structures using FOR XML EXPLICIT is a complex exercise.


In SQL Server 2005, we now have alternatives to the "query from hell." In the following, we will take a look at the main features added to FOR XML in SQL Server 2005: Integration with the XML data type, assignability and nesting of expressions, and the new PATH mode.

Integration with the XML Data Type

With the introduction of the XML data type, we wanted to also give FOR XML the ability to generate an instance of XML directly (more precisely, it generates a single row, single column rowset where the cell contains the XML data type instance).


Returns the Employee elements as an XML data type instance, instead of the nvarchar(max) instance that would have been the case without the TYPE directive.

This result is guaranteed to conform to the well-formedness constraints provided by the XML data type. Since the result is an XML data type instance, you can also use XQuery expressions to query and reshape the result. For example, the following expression retrieves the Employee Name into a new Person element.

  for $c in /Employee 
  <Person name="{data($c/@employeename)}"/> 

returns (only first elements shown),

  <Person name="Maria Anders" /> 
  <Person name="Ana Trujillo" /> 
  <Person name="Antonio Moreno" /> 

Assigning FOR XML Results

The result of a FOR XML query can be assigned to a variable, or inserted into a column.

 SET @cust = (SELECT * FROM Employee FOR XML AUTO, TYPE) 
 CREATE TABLE T(i int, x XML) 

Nesting of FOR XML Expressions

FOR XML, in SQL Server 2005, recognizes XML data type columns, and will inline them as sub-elements. Thus, we can nest FOR XML queries to generate hierarchies, instead of having to rely on the AUTO mode heuristic, or writing an EXPLICIT mode query.

Let's look at the example.

  NULL as Parent, 
  CustomerID as "Customer!1!CustomerID", 
  NULL as "Order!2!OrderID", 
  NULL as "Employee!3!LastName" 
 FROM Customers 
 FROM Orders  
 JOIN Customers ON Orders.CustomerID = Customers.CustomerID 
 FROM Customers  
 JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
 JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID 
 ORDER BY "Customer!1!CustomerID","Employee!3!LastName","Order!2!OrderID" 

returns (only first customer shown),

<Customer CustomerID="ALFKI">
  <Order OrderID="10643" /> 
  <Order OrderID="10692" /> 
  <Order OrderID="10702" /> 
  <Order OrderID="10835" /> 
  <Order OrderID="10952" /> 
  <Order OrderID="11011" /> 
  <Employee LastName="Davolio" /> 
  <Employee LastName="Leverling" /> 
  <Employee LastName="Peacock" /> 
  <Employee LastName="Suyama" /> 

As you can see, we need a select statement for each element. We also repeat the parent's identification with every child, so that the order by groups the children with their parents. The streaming serialization from the rowset to XML depends on this grouping to get the correct nesting.

Now let's look at how we can rewrite this by using nesting FOR XML expressions. We can leverage the new TYPE directive to generate XML data type instances (otherwise, you will get a textual result that will be entitized if it is embedded in another FOR XML query) and nest sub selections to define the hierarchy. We now rewrite the previous EXPLICIT mode query using the AUTO mode and nesting:

SELECT CustomerID as "CustomerID",
  (SELECT OrderID as "OrderID" 
  FROM Orders "Order" 
  WHERE "Order".CustomerID = Customer.CustomerID 
  (SELECT DISTINCT LastName as "LastName" 
  FROM Employees Employee 
  JOIN Orders "Order" ON "Order".EmployeeID = Employee.EmployeeID 
  WHERE Customer.CustomerID = "Order".CustomerID 
 FROM Customers Customer 

This returns the same result as the EXPLICIT mode query. Obviously, this query is easier to write, understand, and maintain, even though it contains basically the same number of select statements and join conditions.

The New PATH Mode

The PATH mode allows you to use an XPath-like syntax as a column name, which then is mapped into an attribute (e.g., "@a"), element (e.g., "e"), sub element structure ("e1/e2"), element content ("*"), text node ("text()"), or data value ("data()"). As with the RAW mode, the default name for the row element is row and can be overwritten with an NCName (a name without a prefix).

Let us give the PATH mode formulation of the above EXPLICIT mode query, now we know why the EXPLICIT mode is sometimes called the "query from hell."

SELECT CustomerID as "@CustomerID",
  (SELECT OrderID as "@OrderID" 
  FROM Orders 
  WHERE Orders.CustomerID = Customers.CustomerID 
  FOR XML PATH('Order'), TYPE), 
  (SELECT DISTINCT LastName as "@LastName" 
  FROM Employees 
  JOIN Orders ON Orders.EmployeeID = Employees.EmployeeID 
  WHERE Customers.CustomerID = Orders.CustomerID 
  FOR XML PATH('Employee'), TYPE) 
 FROM Customers 
 FOR XML PATH('Customer') 

This example is similar to the AUTO mode version and returns the same result.

SELECT CustomerID as "@CustomerID",
  Address as "address/street", 
  City as "address/city", 
  Region as "address/region", 
  PostalCode as "address/zip", 
  Country as "address/country", 
  ContactName as "contact/name", 
  ContactTitle as "contact/title", 
  Phone as "contact/phone",  
  Fax as "contact/fax" 
 FROM Customers 
 FOR XML PATH('Customer'), ROOT('doc') 

This query results in the following document

  <Customer CustomerID="ALFKI"> 
  <CompanyName>Alfreds Futterkiste</CompanyName> 
  <street>Obere Str. 57</street> 
  <name>Maria Anders</name> 
  <title>Sales Representative</title> 

How would this query look like if it used the EXPLICIT mode? We need four select clauses—one for each non-leaf element—instead of only one selection.

Last but not least, the following gives us an example of generating a value list, and shows the use of a text node.

SELECT CustomerID as "@ID",
  (SELECT OrderID as "data()" 
  FROM Orders 
  WHERE Customers.CustomerID=Orders.CustomerID 
  ) as "@OrderIDs", 
  ContactTitle as "ContactName/@ContactTitle", 
  ContactName as "ContactName/text()", 
  PostalCode as "Address/@ZIP", 
  Address as "Address/Street", 
  City as "Address/City" 
 FROM Customers 
 FOR XML PATH('Customer') 

This creates a result of the form (showing one example customer),

 <Customer ID="HUNGC" OrderIDs="10375 10394 10415 10600 10660"> 
  <CompanyName>Hungry Coyote Import Store</CompanyName> 
  ContactTitle="Sales Representative">Yoshi Latimer</ContactName> 
  <Address ZIP="97827"> 
  <Street>City Center Plaza 516 Main St.</Street> 

The sub query, which generates the OrderIDs attribute list, maps the OrderID column values as atomic values (using the path data()). These values will then be serialized as a text node by adding a space between sibling atomic values, which are provided in the next cell in the rowset. We then avoid generating a name for the row by using the zero-length string as the PATH mode argument, so that we get a single string (note, there is no TYPE directive!) as the result of the FOR XML PATH expression. That string gets mapped to the OrderIDs attribute by the containing FOR XML expression.

The CompanyName gets mapped to a sub element of the same name.

The ContactTitle produces the ContactTitle attribute of the ContactName element, while the ContactName column value is mapped into the text node of the same element. Note that in this case, the same result would have been achieved by mapping ContactName to the ContactName element directly.

Finally, the properties for the Address element parts are put together.

More FOR XML Extensions

Besides the new features listed above, SQL Server 2005 provides the following new features:

RAW mode can be combined with the ELEMENTS directive, and take a parameter to overwrite the row element name. For example,


returns (only showing the first customer),

  <CompanyName>Alfreds Futterkiste</CompanyName> 
  <ContactName>Maria Anders</ContactName> 
  <ContactTitle>Sales Representative</ContactTitle> 
  <Address>Obere Str. 57</Address> 

The ELEMENTS directive provides an XSINIL option to map NULL values to an element with an attribute xsi:nil="true". For example,

SELECT * FROM Customers WHERE Region is null

returns (only showing the first customer),

<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <CompanyName>Alfreds Futterkiste</CompanyName> 
  <ContactName>Maria Anders</ContactName> 
  <ContactTitle>Sales Representative</ContactTitle> 
  <Address>Obere Str. 57</Address> 
  <Region xsi:nil="true" /> 

A new in-line schema inference directive XMLSCHEMA that takes a target namespace URI as an optional argument has been added for the RAW and AUTO modes. For example,

SELECT * FROM Customers FOR XML RAW('Customer'), XMLSCHEMA('urn:example.com')

returns (only showing parts of the schema and data),

<xsd:schema targetNamespace="urn:example.com"
  <xsd:import namespace= 
  "http://schemas.microsoft.com/sqlserver/2004/sqltypes" /> 
  <xsd:element name="Customer"> 
 <Customer xmlns="urn:example.com" CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545" /> … 
Read more


April 20 2010 , Written by Balavardhan Reddy Published on #SQL Server2005

Transact-SQL provides the following ranking functions:


Rank() - Applies to monotonically increasing number for each value in the set
Row_Number() - simply assigns sequential numbering to the records of a result-set or to the records within groups of a result-set
Rank() and Row_Number differ only if there are ties.
Dense_Rank() - Method the rank is increasing number for each value in the set.
NTILE(n)- Which splits the records into partition based on the Column in the Over() and assigns the Rank for the partition.

OVER clause is required in all the ranking functions and with that you specify the partitioning and ordering of records before the ranking functions are evaluated. If you don't specify it, you will get an error similar to "Incorrect syntax near 'ROW_NUMBER', expected 'OVER'."
ORDER BY clause is mandatory and if you don't specify it you will get an error similar to "The ranking function "ROW_NUMBER" must have an ORDER BY clause.". With this clause you specify a column or a set of columns which will be used to order the records within a result-set or within record groups/partitions of a result-set. Please note you can specify only those columns which are being made available by the FROM clause of the query.

Please find the below Image for the example:

Click to get full Image

Read more

Time Delay to Execute a T-SQL Query

March 17 2010 , Written by Balavardhan Reddy Published on #SQL Server2005

A New feature in SQL Server is Time delay to execute a T-SQL Query
SQL Server will wait for perticular time to excute the query.

Ex :
i) SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:20' ---- 5 Second Delay

SET @delaytime = DATEADD(s,20,GETDATE())
WAITFOR TIME @delaytime


Read more

.text { mso-number-format:\@;..."> Export Gridview data to Excel

February 18 2010 , Written by Balavardhan Reddy Published on #ASP.Net

i)  Get the Data from the Database and Bind it to the Gridview
ii) Set Gridview Allowpaging property to "False"

Write the below code to export the entire Gridview data to Excel sheet

 private void ExportGridView()
                string style = @"<style> .text { mso-number-format:\@; } </style> ";
                string attachment = "attachment; filename=Contacts.xls";
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                // Create a form to contain the grid
                HtmlForm frm = new HtmlForm();
                frm.Attributes["runat"] = "server";


Read more
1 2 3 4 5 6 7 8 9 10 > >>