Venkatesh2urs…

Basic- xml

January 5, 2008 by venkatesh2urs

(B)What is XML?
XML (Extensible markup language) is all about describing data. Below is a XML which
describes invoice data.
<?xml version=”1.0″ encoding=”ISO-8859-1″?>
<invoice>
<productname>Shoes</productname>
<qty>12</qty>
<totalcost>100</totalcost>
<discount>10</discount>
</invoice>
An XML tag is not something predefined but it is something you have to define according
to your needs. For instance in the above example of invoice all tags are defined according
to business needs. The XML document is self explanatory, any one can easily understand
looking at the XML data what exactly it means.
(I)What is the version information in XML?
“version” tag shows which version of XML is used.
(B)What is ROOT element in XML?
In our XML sample given previously <invoice></invoice> tag is the root element. Root
element is the top most elements for a XML.
(B)If XML does not have closing tag will it work?
No, every tag in XML which is opened should have a closing tag. For instance in the top
if I remove </discount> tag that XML will not be understood by lot of application.
427
(B)Is XML case sensitive?
Yes, they are case sensitive.
(B)What is the difference between XML and HTML?
XML describes data while HTML describes how the data should be displayed. So HTML
is about displaying information while XML is about describing information.
(B)Is XML meant to replace HTML?
No, they both go together one is for describing data while other is for displaying data.
(A)Can you explain why your project needed XML?
Note: – This is an interview question where the interviewer wants to know why you have
chosen XML.
Remember XML was meant to exchange data between two entities as you can define your
user friendly tags with ease. In real world scenarios XML is meant to exchange data. For
instance you have two applications who want to exchange information. But because they
work in two complete opposite technologies it’s difficult to do it technically. For instance
one application is made in JAVA and the other in .NET. But both languages understand
XML so one of the applications will spit XML file which will be consumed and parsed by
other applications
You can give a scenario of two applications which are working separately and how you
chose XML as the data transport medium.
(B)What is DTD (Document Type definition)?
It defines how your XML should structure. For instance in the above XML we want to
make it compulsory to provide “qty” and “totalcost”, also that these two elements can
only contain numeric. So you can define the DTD document and use that DTD document
with in that XML.
(B)What is well formed XML?
If a XML document is confirming to XML rules (all tags started are closed, there is a root
element etc) then it’s a well formed XML.
428
(B)What is a valid XML?
If XML is confirming to DTD rules then it’s a valid XML.
(B)What is CDATA section in XML?
All data is normally parsed in XML but if you want to exclude some elements you will
need to put those elements in CDATA.
(B)What is CSS?
With CSS you can format a XML document.
(B)What is XSL?
XSL (the eXtensible Stylesheet Language) is used to transform XML document to some
other document. So its transformation document which can convert XML to some other
document. For instance you can apply XSL to XML and convert it to HTML document or
probably CSV files.
(B)What is element and attributes in XML?
In the below example invoice is the element and the invnumber the attribute.
<invoice invnumber=1002></invoice>
(B)Which are the namespaces in .NET used for XML?
“System.xml.dll” is the actual physical file which has all XML implementation. Below are
the commonly used namespaces:-
√ System.Xml
√ System.Xml.Schema
√ System.Xml.XPath
√ System.Xml.Xsl
(A)What are the standard ways of parsing XML
document?
429
Twist: – What is a XML parser?
XML parser sits in between the XML document and the application who want to use the
XML document. Parser exposes set of well defined interfaces which can be used by the
application for adding, modifying and deleting the XML document contents. Now whatever
interfaces XML parser exposes should be standard or else that would lead to different
vendors preparing there own custom way of interacting with XML document.
There are two standard specifications which are very common and should be followed by
a XML parser:-
DOM: – Document Object Model.
DOM is a W3C recommended way for treating XML documents. In DOM we load entire
XML document into memory and allows us to manipulate the structure and data of XML
document.
SAX: – Simple API for XML.
SAX is event driven way for processing XML documents. In DOM we load the whole
XML document in to memory and then application manipulates the XML document. But
this is not always the best way to process large XML documents which have huge data
elements. For instance you only want one element from the whole XML document or you
only want to see if the XML is proper which means loading the whole XML in memory
will be quiet resource intensive. SAX parsers parse the XML document sequentially and
emit events like start and end of the document, elements, text content etc. So applications
who are interested in processing these events can register implementations of callback
interfaces. SAX parser then only sends those event messages which the application has
demanded.
430
Figure 13.1 : – DOM Parser loading XML document
Above is a pictorial representation of how DOM parser works. Application queries the
DOM Parser for “quantity” field. DOM parser loads the complete XML file in to memory.
431
Figure 13.2 : – Returning the Quantity value back to application
DOM parser then picks up the “quantity” tag from the memory loaded XML file and
returns back to the application.
432
Figure 13.3 : – SAX parser in action
SAX parser does not load the whole DOM in to memory but has event based approach.
SAX parser while parsing the XML file emits events. For example in the above figure its
has emitted Invoice tag start event, Amount Tag event, Quantity tag event and Invoice
end tag event. But our application software is only interested in quantity value. So the
application has to register to the SAX parser saying that he is only interested in quantity
field and not any other field or element of the XML document. Depending on what
interest the application software has SAX parser only sends those events to the application
the rest of events is suppressed. For instance in the above figure only quantity tag event
is sent to the application software and the rest of the events are suppressed.
433
(A)In What scenarios will you use a DOM parser and
SAX parser?
√ If you do not need all the data from the XML file then SAX approach is much
preferred than DOM as DOM can quiet memory intensive. In short if you need
large portion of the XML document its better to have DOM.
√ With SAX parser you have to write more code than DOM.
√ If you want to write the XML in to a file DOM is the efficient way to do it.
√ Some time you only need to validate the XML structure and do not want to retrieve
any Data for those instances SAX is the right approach.
(A) How was XML handled during COM times?
During COM it was done by using MSXML 4.0. So old languages like VB6, VC++ used
MSXML 4.0 which was shipped with SP1( Service Pack 1).
Note: – This book will not show any samples as such for MSXML 4.0. So if anyone
interested please do refer the same in MSDN and try to compile some sample programs.
(A)What is the main difference between MSML and .NET
Framework XML classes?
MSXML supports XMLDOM and SAX parsers while .NET framework XML classes
support XML DOM and XML readers and writers.
MSXML supports asynchronous loading and validation while parsing. For instance you
can send synchronous and asynchronous calls to a remote URL. But as such there is not
direct support of synchronous and asynchronous calls in .NET framework XML. But
same can be achieved by using “System.Net” namespaces.
(B) What are the core functionalities in XML .NET
framework? Can you explain in detail those
functionalities?
The XML API for the .NET Framework comprises the following set of functionalities:
434
XML readers
With XML readers the client application get reference to instance of reader class. Reader
class allows you to scroll forward through the contents like moving from node to node or
element to element. You can compare it with the “SqlDataReader” object in ADO.NET
which is forward only. In short XML reader allows you to browse through the XML
document.
XML writers
Using XML writers you can store the XML contents to any other storage media. For
instance you want to store the whole in memory XML to a physical file or any other
media.
XML document classes
XML documents provides a in memory representation for the data in an XMLDOM
structure as defined by W3C. It also supports browsing and editing of the document. So
it gives you a complete memory tree structure representation of your XML document.
(B)What is XSLT?
XSLT is a rule based language used to transform XML documents in to other file formats.
XSLT are nothing but generic transformation rules which can be applied to transform
XML document to HTML, CS, Rich text etc.
435
Figure 13.4 : – XSLT Processor in Actions
You can see in the above figure how the XSLT processor takes the XML file and applies
the XSLT transformation to produce a different document.
(I)Define XPATH?
It is an XML query language to select specific parts of an XML document. Using XPATH
you can address or filter elements and text in a XML document. For instance a simple
XPATH expression like “Invoice/Amount” states find “Amount” node which are children
of “Invoice” node.
(A)What is the concept of XPOINTER?
XPOINTER is used to locate data within XML document. XPOINTER can point to a
particular portion of a XML document, for instance
address.xml#xpointer(/descendant::streetnumber[@id=9])
So the above XPOINTER points streetnumber=9 in “address.xml”.
436
(B)What is an XMLReader Class?
It is an abstract class available from System.XML namespace. XML reader works on a
read-only stream browsing from one node to other in a forward direction. It maintains
only a pointer to the current node but has no idea of the previous and the next node. You
can not modify the XML document, you can only move forward.
(B)What is XMLTextReader?
The “XmlTextReader” class helps to provide fast access to streams of XML data in a
forward-only and read-only manner. It also checks if the XML is well-formed. But
XMLTextReader does not validate against a schema or DTD for that you will need
“XmlNodeReader” or “XmlValidatingReader” class.
Instance of “XmlTextReader” can be created in number of ways. For example if you
want to load file from a disk you can use the below snippets.
XmlTextReader reader = new XmlTextReader(fileName);
To loop through all the nodes you need to call the “read()” method of the “XmlTextreader”
object. “read()” method returns “true” if there are records in the XML document or else
it returns “false”.
//Open the stream
XmlTextReader reader = new XmlTextReader(file);
while (reader.Read())
{
// your logic goes here
string pdata = reader.Value
}
// Close the stream
reader.Close();
To read the content of the current node on which the reader object is you use the “value”
property. As shown in the above code “pdata” gets the value from the XML using
“reader.value”.
437
(I)How do we access attributes using “XmlReader”?
Below snippets shows the way to access attributes. First in order to check whether there
any attributes present in the current node you can use “HasAttributes” function and use
the “MoveToNextAttribute” method to move forward in attribute. In case you want to
move to the next element use “MoveToElement()”.
if (reader.HasAttributes)
{
while(reader.MoveToNextAttribute())
{
// your logic goes here
string pdata = reader.Value
}
}
reader.MoveToElement();
(I) Explain simple Walk through of XmlReader ?
In this section we will do a simple walkthrough of how to use the “XmlReader” class.
Sample for the same is available in both languages (C# and VB.NET) which you can find
in “WindowsApplicationXMLVBNET” and “WindowsApplicationCSharp” folders. Task
is to load “TestingXML.XML” file and display its data in a message box. You can find
“TestingXML.XML” file in “BIN” directory of both the folders. Below is the display of
“TestingXML.XML” file and its content.
Figure 13.5 : – Testing.XML Data
438
Both the projects have command button “CmdLoadXML” which has the logic to load the
XML file and display the data in messagebox. I have pasted only the “CmdLoadXML”
command button logic for simplicity. Following are the basic steps done:-
√ Declared the “XMLTextReader” object and gave the XML filename to load the
XML data.
√ Read the “XMLTextReader” object until it has data and concatenate the data in a
temporary string.
√ Finally display the same in a message box.
Figure 13.6 : – VB.NET code for XMLReader
Same holds true for C# code as shown below.
439
Figure 13.7 : – C# code for XMLReader
Figure 13.8 : – Data Display for “TestingXML.XML”
440
(A) What does XmlValidatingReader class do?
XmlTextReader class does not validate the contents of an XML source against a schema.
The correctness of XML documents can be measured by two things is the document well
formed and is it valid. Well-formed means that the overall syntax is correct. Validation is
much deeper which means is the XML document is proper w.r.t schema defined.
So the XmlTextReader only checks if the syntax is correct but does not do validation.
There’s where XmlValidatingReader class comes in to picture. So this again comes at a
price as XmlValidatingReader have to check for DTD and Schema’s there are slower
compared to XmlTextReader.

Posted in basic- xml | Leave a Comment »

Basic – 0ops

January 5, 2008 by venkatesh2urs

(B) What is Object Oriented Programming ?
It is a problem solving technique to develop software systems. It is a technique to think
real world in terms of objects. Object maps the software model to real world concept.
These objects have responsibilities and provide services to application or other objects.
(B) What’s a Class ?
A class describes all the attributes of objects, as well as the methods that implement the
behavior of member objects. It’s a comprehensive data type which represents a blue print
of objects. It’s a template of object.
(B) What’s an Object ?
It is a basic unit of a system. An object is an entity that has attributes, behavior, and
identity. Objects are members of a class. Attributes and behavior of an object are defined
by the class definition.
(A) What is the relation between Classes and Objects ?
They look very much same but are not same. Class is a definition, while object is a
instance of the class created. Class is a blue print while objects are actual objects existing
in real world. Example we have class CAR which has attributes and methods like Speed,
Brakes, Type of Car etc. Class CAR is just a prototype, now we can create real time
objects which can be used to provide functionality. Example we can create a Maruti car
object with 100 km speed and urgent brakes.
(B) What are different properties provided by Objectoriented
systems ?
Twist :- Can you explain different properties of Object Oriented Systems?
Note:- Difference between abstraction and encapsulation is one of the favorite interview
question and quiet confusing as both the terminology look alike. Best is if you can
brainstorm with your friends or do a little reading.
Following are characteristic’s of Object Oriented System’s :-
6. OOPS
201
Abstraction
It allows complex real world to be represented in simplified manner. Example color is
abstracted to RGB. By just making the combination of these three colors we can achieve
any color in world.It’s a model of real world or concept.
Encapsulation
It is a process of hiding all the internal details of an object from the outside world.
Communication using messages
When application wants to achieve certain task it can only be done using combination of
objects. A single object can not do all the task. Example if we want to make order processing
form.We will use Customer object, Order object, Product object and Payment object to
achieve this functionality. In short these objects should communicate with each other.
This is achieved when objects send messages to each other.
Object lifetime
All objects have life time.Objects are created ,and initialized, necessary functionalities
are done and later the object is destroyed. Every object have there own state and identity
which differ from instance to instance.
Class hierarchies (Inheritance and aggregation)
Twist :- What is difference between Association, Aggregation and Inheritance relationships?
In object oriented world objects have relation and hierarchies in between them. There are
basically three kind of relationship in Object Oriented world :-
Association
This is the simplest relationship between objects. Example every customer has sales. So
Customer object and sales object have an association relation between them.
Aggregation
This is also called as composition model. Example in order to make a “Accounts” class it
has use other objects example “Voucher”, “Journal” and “Cash” objects. So accounts
class is aggregation of these three objects.
202
Inheritance
Hierarchy is used to define more specialized classes based on a preexisting generalized
class. Example we have VEHICLE class and we can inherit this class make more
specialized class like CAR, which will add new attributes and use some existing qualities
of the parent class. Its shows more of a parent-child relationship. This kind of hierarchy
is called inheritance.
Polymorphism
When inheritance is used to extend a generalized class to a more specialized class, it
includes behavior of the top class(Generalized class). The inheriting class often implement
a behavior that can be somewhat different than the generalized class, but the name of the
behavior can be same. It is important that a given instance of an object use the correct
behavior, and the property of polymorphism allows this to happen automatically.
(B) How can we acheive inheritance in VB.NET ?
Note:- The following explanation is for VB.NET
Inheritance is achieved by using “Inherits” keyword in VB.NET (For C# it is “:”). Simple
Sample is provided in CD for understanding inheritance in folder
“WindowsApplicationInheritance”. There are two classes one is the parent “ClsParent”
and second is the child “ClsChild”. Parent class has a string which has to parsed for junk
data “@” and “/”.ClsParent has the functionality which parses only cleans up
“@”.”ClsChild” then inherits from parent and adds extra functionality by parsing “/”.
Public Class ClsParent
Protected strData As String = “jksdhkj@dadad///ajkdhsjakd”
Public Function Parse() As String
Dim PstrData As String
PstrData = strData
PstrData = Replace(PstrData, “@”, “”)
Return PstrData
End Function
Public Function GetActualString() As String
Return strData
End Function
End Class
Above is the source which parses only “@” of strData variable.
203
Public Class ClsChild
Inherits ClsParent
‘ this is child and a special parse function is added which will
also parse “/”
Public Function ParseBackSlash()
Dim PstrData As String
PstrData = Me.Parse()
PstrData = Replace(PstrData, “/”, “”)
Return PstrData
End Function
End Class
Above is the source code for “ClsChild” which does the remaining work. It adds extra
functionality by parsing “/” junk character’s of the data.
Note:- Strdata was accessible only because it was defined as protected in the parent class.
Figure :- 6.1 Inheritance in action
(I) What are abstract classes ?
Following are features of a abstract class :-
√ You can not create a object of abstract class
204
√ Abstract class is designed to act as a base class (to be inherited by other classes).
Abstract class is a design concept in program development and provides a
base upon which other classes are built.
√ Abstract classes are similar to interfaces. After declaring an abstract class, it
cannot be instantiated on its own, it must be inherited.
√ In VB.NET abstract classes are created using “MustInherit” keyword.In C#
we have “Abstract” keyword.
√ Abstract classes can have implementation or pure abstract methods which
should be implemented in the child class.
Note:- In order to understand the concept simple sample of add and multiply functionality
is implemented in “WindowsAbstract” folder in CD.
From interview point of view just saying using “MustInherit” keyword is more than enough
to convince that you have used abstract classes. But to clear simple fundamental let’s try
to understand the sample code. There are two classes one is “ClsAbstract” class and
other is “ClsChild” class. “ClsAbstract” class is a abstract class as you can see the
mustinherit keyword. It has one implemented method “Add” and other is abstract method
which has to be implemented by child class “MultiplyNumber”. In the child class we
inherit the abstract class and implement the multiplynumber function.
Definitely this sample does not take out actually how things are implemented in live
projects. Basically you put all your common functionalities or half implemented
functionality in parent abstract class and later let child class define the full functionality
of the abstract class. Example i always use abstract class with all my SET GET properties
of object in abstract class and later make specialize classes for insert, update, delete for
the corresponding entity object.
Public MustInherit Class ClsAbstract
‘ use the mustinherit class to declare the class as abstract
Public Function Add(ByVal intnum1 As Integer, ByVal intnum2 As
Integer) As Integer
Return intnum1 + intnum2
End Function
‘ left this seconf function to be completed by the inheriting
class
Public MustOverride Function MultiplyNumber(ByVal intnum1 As
Integer, ByVal intnum2 As Integer) As Integer
End Class
205
Public Class ClsChild
Inherits ClsAbstract
‘ class child overrides the Multiplynumber function
Public Overrides Function MultiplyNumber(ByVal intnum1 As
Integer, ByVal intnum2 As Integer) As Integer
Return intnum1 * intnum2
End Function
End Class
Figure :- 6.2 Abstract classes in action
My attitude towards abstract class has been that i put all my common functionality in
abstract class.
(B) What is a Interface ?
Interface is a contract that defines the signature of the functionality. So if a class is
implementing a interface it says to the outer world, that it provides specific behavior.
Example if a class is implementing Idisposable interface that means it has a functionality
to release unmanaged resources. Now external objects using this class know that it has
contract by which it can dispose unused unmanaged objects.
√ Single Class can implement multiple interfaces.
√ If a class implements a interface then it has to provide implementation to all
its methods.
206
Note:- In CD sample “WindowsInterFace” is provided, which has a simple interface
implemented.
In sample there are two files.One has the interface definition and other class implements
the interface. Below is the source code “IInterface” is the interface and “ClsDosomething”
implements the “IInterface”. This sample just displays a simple message box.
Public Interface IInterFace
Sub DoSomething()
End Interface
Public Class ClsDoSomething
Implements IInterFace
Public Sub DoSomething() Implements
WindowsInterFace.IInterFace.DoSomething
MsgBox(“Interface implemented”)
End Sub
End Class
Figure:- 6.3 Interface in action
207
(A) What is difference between abstract classes and
interfaces?
Following are the differences between abstract and interfaces :-
√ Abstract classes can have concrete methods while interfaces have no methods
implemented.
√ Interfaces do not come in inheriting chain, while abstract classes come in
inheritance.
(B) What is a delegate ?
Delegate is a class that can hold a reference to a method or a function. Delegate class has
a signature and it can only reference those methods whose signature is compliant with the
class. Delegates are type-safe functions pointers or callbacks.
Below is a sample code which shows a example of how to implement delegates.
Public Class FrmDelegates
Inherits System.Windows.Forms.Form
Public Delegate Sub DelegateAddString()
Private Sub FrmDelegates_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub AddString()
lstDelegates.Items.Add(“Running AddString() method”)
End Sub
Private Sub cmdDelegates_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles cmdDelegates. Click
Dim objDelegateAddString As DelegateAddString
objDelegateAddString = AddressOf AddString
objDelegateAddString.Invoke()
End Sub
End Class
In the above there is a method called “AddString()” which adds a string to a listbox.You
can also see a delegate declared as :-
Public Delegate Sub DelegateAddString()
This delegate signature is compatible with the “AddString” method. When I mean
compatibility that means that there return types and passing parameter types are same.
208
Later in command click of the button object of the Delegate is created and the method
pointer is received from “AddressOf ” keyword. Then by using the “Invoke” method the
method is invoked.
Figure :- 6.4 Delegate in Action
(B) What are events ?
As compared to delegates events works with source and listener methodology. So listeners
who are interested in receiving some events they subscribe to the source. Once this
subscription is done the source raises events to its entire listener when needed. One
source can have multiple listeners.
In sample given below class “ClsWithEvents” is a event source class, which has a event
“EventAddString()”. Now the listeners who are interested in receiving this events they
can subscribe to this event. In class “FrmWithEvents” you can see they handle clause
which is associated with the “mobjClsWithEvents” objects.
Public Class ClsWithEvents
Event EventAddString(ByVal Value As String)
Public Sub AddString()
RaiseEvent EventAddString(“String added by Event”)
End Sub
End Class
209
Public Class FrmWithEvents
Inherits System.Windows.Forms.Form
Private WithEvents mobjClsWithEvents As New ClsWithEvents()
Private Sub FrmWithEvents_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub mobjClsWithEvents_EventAddString(ByVal Value As
String) Handles mobjClsWithEvents.EventAddString
LstData.Items.Add(Value)
End Sub
Private Sub CmdRunEvents_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles CmdRunEvents.Click
mobjClsWithEvents.AddString()
End Sub
End Class
Figure :- 6.5 Events in action
Nore:- Above source code is provided in “WindowsEvent”
210
(I) Do events have return type ?
No, events do not have return type.
(A) Can event’s have access modifiers ?
Event’s are always public as they are meant to serve every one register ing to it. But you
can access modifiers in events.You can have events with protected keyword which will
be accessible only to inherited classes.You can have private events only for object in that
class.
(A) Can we have shared events ?
Yes, you can have shared event’s note only shared methods can raise shared events.
(I) What is shadowing ?
When two elements in a program have same name, one of them can hide and shadow the
other one. So in such cases the element which shadowed the main element is referenced.
Below is a sample code, there are two classes “ClsParent” and “ClsShadowedParent”. In
“ClsParent” there is a variable “x” which is a integer. “ClsShadowedParent” overrides
“ClsParent” and shadows the “x” variable to a string.
Note:- In Sample CD “WindowsShadowing” is folder which has the sample code. If you
run the program you can have two output’s one which shows a integer and other which shows
a string.
Public Class ClsParent
Public x As Integer
End Class
Public Class ClsShadowedParent
Inherits ClsParent
Public Shadows x As String
End Class
211
Figure :- 6.6 Shadowing in Action
(A) What is the difference between Shadowing and
Overriding ?
Following are the differences between shadowing and overriding :-
√ Overriding redefines only the implementation while shadowing redefines the
whole element.
√ In overriding derived classes can refer the parent class element by using “ME”
keyword, but in shadowing you can access it by “MYBASE”.
(I) What is the difference between delegate and events?
√ Actually events use delegates in bottom. But they add an extra layer on the
delegates, thus forming the publisher and subscriber model.
√ As delegates are function to pointers they can move across any clients. So any
of the clients can add or remove events, which can be pretty confusing. But
events give the extra protection by adding the layer and making it a publisher
and subscriber model.
212
Just imagine one of your clients doing this
c.XyzCallback = null
This will reset all your delegates to nothing and you have to keep searching where the
error is.
(B) If we inherit a class do the private variables also get
inherited ?
Yes, the variables are inherited but can not be accessed directly by the class interface.
(B) What are the different accessibility levels defined in .NET
?
Following are the five levels of access modifiers :-
√ Private : Only members of class have access.
√ Protected :-All members in current class and in derived classes can access the
variables.
√ Friend (internal in C#) :- Only members in current project have access to the
elements.
√ Protected friend (protected internal in C#) :- All members in current project
and all members in derived class can access the variables.
√ Public :- All members have access in all classes and projects.
(I) Can you prevent a class from overriding ?
If you define a class as “Sealed” in C# and “NotInheritable” in VB.NET you can not
inherit the class any further.
(I) What is the use of “MustInherit” keyword in VB.NET ?
If you want to create a abstract class in VB.NET it’s done by using “MustInherit”
keyword.You can not create an object of a class which is marked as “MustInherit”. When
you define “MustInherit” keyword for class you can only use the class by inheriting.
213
Note :- There was huge typo in my previous versions for the above two questions. I hope
some one has not lost good oppurtunity because of the same. Thanks to all my readers for
pointing it out.
(I) Do interface have accessibility modifier?
All elements in Interface should be public. So by default all interface elements are public
by default.
(A) What are similarities between Class and structure ?
Following are the similarities between classes and structures :-
√ Both can have constructors, methods, properties, fields, constants,
enumerations, events, and event handlers.
√ Structures and classes can implement interface.
√ Both of them can have constructors with and without parameter.
√ Both can have delegates and events.
(A) What is the difference between Class and structure’s ?
Following are the key differences between them :-
√ Structure are value types and classes are reference types. So structures use
stack and classes use heap.
√ Structures members can not be declared as protected, but class members can
be. You can not do inheritance in structures.
√ Structures do not require constructors while classes require.
√ Objects created from classes are terminated using Garbage collector. Structures
are not destroyed using GC.
(B) What does virtual keyword mean ?
They are that method and property can be overridden.
(B) What are shared (VB.NET)/Static(C#) variables?
214
Static/Shared classes are used when a class provides functionality which is not specific to
any instance. In short if you want an object to be shared between multiple instances you
will use a static/Shared class.
Following are features of Static/Shared classes :-
√ They can not be instantiated. By default a object is created on the first method
call to that object.
√ Static/Shared classes can not be inherited.
√ Static/Shared classes can have only static members.
√ Static/Shared classes can have only static constructor.
Note :- In CD there is a folder “WindowsShared” which has a sample code for shared
variables.Below is a snippet. It has a “AddCount” function which increments a static
“intCount” variable. In form there are two buttons which creates a new object and displays
the count of the static variable. Even though the object is created and destroyed, the variable
values does not change. It retains its old value.
Public Class ClsShared
Shared intCount As Integer
Public Function AddCount() As Integer
intCount = intCount + 1
Return intCount
End Function
End Class
Public Class FrmSharedClasses
Inherits System.Windows.Forms.Form
Private Sub CmdInstance1_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles CmdInstance1.Click
Dim pobjClsShared As New ClsShared()
MessageBox.Show(“The count at this moment is” &
pobjClsShared.AddCount.ToString())
End Sub
Private Sub CmdInstance2_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles CmdInstance2.Click
Dim pobjClsShared As New ClsShared()
MessageBox.Show(“The count at this moment is” &
pobjClsShared.AddCount.ToString())
215
End Sub
End Class
Figure :- 6.7 Shared/Static In Action
(B) What is Dispose method in .NET ?
.NET provides “Finalize” method in which we can clean up our resources. But relying on
this is not always good so the best is to implement “Idisposable” interface and implement
the “Dispose” method where you can put your clean up routines.
(B) What is the use of “OverRides” and “Overridable”
keywords ?
Overridable is used in parent class to indicate that a method can be overridden. Overrides
is used in the child class to indicate that you are overriding a method
(A) Where are all .NET Collection classes located ?
216
System.Collection namespace has all the collection classes available in .NET.
(A) What is ArrayList ?
Array is whose size can increase and decrease dynamically. Array list can hold item of
different types. As Array list can increase and decrease size dynamically you do not have
to use the REDIM keyword. You can access any item in array using the INDEX value of
the array position.
(A) What’s a HashTable ?
Twist :- What’s difference between HashTable and ArrayList ?
You can access array using INDEX value of array, but how many times you know the
real value of index. Hashtable provides way of accessing the index using a user identified
KEY value, thus removing the INDEX problem.
(A) What are queues and stacks ?
Queue is for first-in, first-out (FIFO) structures. Stack is for last-in, first-out (LIFO)
structures.
(B) What is ENUM ?
It’s used to define constants.
(A) What is nested Classes ?
Nested classes are classes within classes. In sample below “ClsNested” class has a
“ChildNested” class nested inside it.
Public Class ClsNested
Public Class ChildNested
Public Sub ShowMessage()
MessageBox.Show(“Hi this is nested class”)
End Sub
End Class
End Class
This is the way we can instantiate the nested class and make the method call.
Dim pobjChildNested As New ClsNested.ChildNested()
pobjChildNested.ShowMessage()
217
Note:-In CD the above sample is provided in “WindowsNestedClasses”.
(B)What is Operator Overloading in .NET?
It provides a way to define and use operators such as +, -, and / for user-defined classes
or structs. It allows us to define/redefine the way operators work with our classes and
structs. This allows programmers to make their custom types look and feel like simple
types such as int and string.
VB.NET till now does not support operator overloading. Operator overloading is done
by using the “Operator” keyword.
Note:- Operator overloading is supported in VB.NET 2005
(I) In below sample code if we create a object of class2
which constructor will fire first ?
Public Class Class1
Sub New()
End Sub
End Class
Public Class class2
Inherits Class1
Sub New()
End Sub
End Class
* I leave this to the readers……
(B)What is the significance of Finalize method in .NET?
.NET Garbage collector does almost all clean up activity for your objects. But unmanaged
resources (ex: – Windows API created objects, File, Database connection objects, COM
objects etc) is outside the scope of .NET framework we have to explicitly clean our
resources. For these types of objects .NET framework provides Object. Finalize method
218
which can be overridden and clean up code for unmanaged resources can be put in this
section.
(A)Why is it preferred to not use finalize for clean up?
Problem with finalize is that garbage collection has to make two rounds in order to remove
objects which have finalize methods.
Below figure will make things clear regarding the two rounds of garbage collection rounds
performed for the objects having finalized methods.
In this scenario there are three objects Object1, Object2 and Object3. Object2 has the
finalize method overridden and remaining objects do not have the finalize method
overridden.
Now when garbage collector runs for the first time it searches for objects whose memory
has to free. He can see three objects but only cleans the memory for Object1 and Object3.
Object2 it pushes to the finalization queue.
Now garbage collector runs for the second time. He see’s there are no objects to be
released and then checks for the finalization queue and at this moment it clears object2
from the memory.
So if you notice that object2 was released from memory in the second round and not first.
That’s why the best practice is not to write clean up Non.NET resources in Finalize
method rather use the DISPOSE.
219
Figure :- 6.8 Garbage collection in actions
(I)How can we suppress a finalize method?
GC.SuppressFinalize ()
(B)What is the use of DISPOSE method?
Dispose method belongs to IDisposable interface. We had seen in the previous section
how bad it can be to override the finalize method for writing the cleaning of unmanaged
resources. So if any object wants to release its unmanaged code best is to implement
220
IDisposable and override the Dispose method of IDisposable interface. Now once your
class has exposed the Dispose method it’s the responsibility of the client to call the
Dispose method to do the cleanup.
(A)How do I force the Dispose method to be called
automatically, as clients can forget to call Dispose method?
Note :- I admire this question.
Call the Dispose method in Finalize method and in Dispose method suppress the finalize
method using GC.SuppressFinalize. Below is the sample code of the pattern. This is the
best way we do clean our unallocated resources and yes not to forget we do not get the hit
of running the Garbage collector twice.
Note:- It will suppress the finalize method thus avoiding the two trip.
Public Class ClsTesting
Implements IDisposable
Public Overloads Sub Dispose()Implements IDisposable.Dispose
‘ write ytour clean up code here
GC.SuppressFinalize(Me)
End Sub
Protected Overrides Sub Finalize()
Dispose()
End Sub
End Class
(I)In what instances you will declare a constructor to be
private?
When we create a private constructor, we can not create object of the class directly from
a client. So you will use private constructors when you do not want instances of the class
to be created by any external client. Example UTILITY functions in project will have no
221
instance and be used with out creating instance, as creating instances of the class would
be waste of memory.
(I)Can we have different access modifiers on get/set
methods of a property ?
No we can not have different modifiers same property. The access modifier on a property
applies to both its get and set accessors.
(I)If we write a goto or a return statement in try and catch
block will the finally block execute ?
The code in then finally always run even if there are statements like goto or a return
statements.
(A)What is Indexer ?
An indexer is a member that enables an object to be indexed in the same way as an array.
(A)Can we have static indexer in C# ?
No.
(A)In a program there are multiple catch blocks so can it
happen that two catch blocks are executed ?
No, once the proper catch section is executed the control goes finally to block. So there
will not be any scenarios in which multiple catch blocks will be executed.
(A) What is the difference between System.String and
System.StringBuilder classes?
System.String is immutable; System.StringBuilder can have mutable string where a variety
of operations can be performed.

Posted in bascic oops | Leave a Comment »

Dll Hell

January 5, 2008 by venkatesh2urs

ntroduction
[ Back To Top ]

The heading of this article reflects that there are two topics and DLL hell is the problem and Version Policy is the solution to problem. Below is stated the problem with DLL hell is and how a developer can overcome this problem by defining his or her own Version policy.
DLL Hell – The Problem
[ Back To Top ]

DLL Hell refers to the set of problems caused when multiple applications attempt to share a common component like a Dynamic Link Library (DLL) or a Component Object Model (COM) class. One application will install a new version of the shared component that is not backward compatible with the version already on the machine. Although the application that has just been installed works fine, existing applications that depended on a previous version of the shared component might no longer work.

Example 1

We have an application APP1 of Version 1.0 and a plug-in to that application PLUG1 of version 1.0 which of course depends on shared DLL of APP1. But as we upgraded the APP1 to latest version 2.0, APP1 works fine, however, the plug-in PLUG1 is dependent on the shared DLL.
Version Policy-The Solution
[ Back To Top ]

Version policy is the user defined policies which the developer can include in his or her application which will redirect the other application looking for the DLL of the previous version of the application to the DLL of the latest version.

Example 2

Continuing the previous example, if the developer of APP1 would have included version policies in the latest version 2.0 then the application PLUG1 would have worked fine because when the application PLUG1 was looking for the shared DLLs of APP1 Version 1.0 it would have been redirected to the shared DLLs of APP1 version 2.0 by the version policy included in the version 2.0 of APP1 version 2.0.
Assemblies – The Building Blocks
[ Back To Top ]

Assemblies are the building blocks used by the .NET Framework to solve the versioning and deployment issues just described. Assemblies are the deployment unit for types and resources. In many ways an assembly equates to a DLL in today’s world; in essence, assemblies are a “logical DLLs.”

Versioning in .NET is done at the assembly level—nothing smaller, like a module or a type, is versioned. Also, assemblies are used to share code between applications

Please refer to MSDN for more details on Assemblies and Assemblies Manifest.
Versioning
[ Back To Top ]

It is necessary to discuss here about private and shared assemblies because of the difference in naming convention of assemblies in case of private and shared assemblies.

Private Assemblies

An application-private assembly is an assembly that is only visible to one application. The naming requirements for private assemblies are simple: The assembly names must only be unique within the application. There is no need for a globally unique name. Keeping the names unique is not a problem because the application developer has complete control over which assemblies are isolated to the application.

This version information is not enforced for private assemblies because the developer has complete control over the assemblies that are deployed to the application directory.

Shared Assemblies

The .NET Framework also supports the concept of a shared assembly. A shared assembly is one that is used by multiple applications on the machine.

Shared assemblies can be placed in the Global Assembly Cache where CLR first searches for the assemblies. The global assembly cache is a machine-wide store for assemblies that are used by more than one application. Since it is shared by many other applications, developers do not have full control over it and special care is needed in case of shared assemblies.

For example, a shared assembly must have a name that is globally unique. Also, the system must provide for “protection of the name”—that is, preventing someone from reusing another’s assembly name.

The .NET Framework allows applications and administrators to override the version of an assembly that is used by the application by specifying version policies.
Version Policy
[ Back To Top ]

Assembly manifest records information about the version of each dependency it was built against. However, as stated in the above example where the developer of APP2 may wish to run with a different version of a dependency at run time. The .NET Framework enables this flexibility in version binding through version policies.

Assembly Version Numbers

The parts of the version number are major, minor, build and revision. As a developer you are free to change any portion of this number as you see fit.

One typical convention is as follows.

Major or minor: Changes to the major or minor portion of the version number indicate an incompatible change. Under this convention then, version 2.0.0.0 would be considered incompatible with version 1.0.0.0.

Build: The Build number is typically used to distinguish between daily builds or smaller compatible releases.

Revision: Changes to the revision number are typically reserved for an incremental build needed to fix a particular bug. You will sometimes hear this referred to as the “emergency bug fix” number in that the revision is what is often changed when a fix to a specific bug is shipped to a customer.

Default Version Policy

The CLR determines which version of the dependency to load when it comes across a reference to that assembly in code. The default version policy in .NET is simple, the caller gets the exact version with which the application was built and tested against.

Custom Version Policy

This is the section we have been talking so long where the author does not want the CLR to load the default version and redirects the CLR to the other version defined by the Author.

Version policies are stated in XML files and are simply a request to load one version of an assembly instead of another.

Example 3

The following version policy directs the CLR to load version 2.0.0.0 instead of version 1.0.0.0 of an assembly called app1.engine.

Listing 1

This is just a simple XML file. The name of this file is policy.1.0.app1.engine.config. Just place the file exactly where app1.engine.dll exists, in case of shared assembly it may be GAC.

Example 4

You can also redirect from a range of versions to another version. For example, the following policy redirects all versions from 1.0.0.0 through 5.0.0.0 of app1.engine to version 7.0.0.1.

Listing 2

This file is also similar to above example.
Different level of version policy
[ Back To Top ]

There are three levels at which version policy can be applied in .NET: application-specific policy, publisher policy and machine-wide policy.

Application-specific Policy

Each application has an optional configuration file that can specify the application’s desire to bind to a different version of a dependent assembly. The name of the configuration file varies based on the application type. For executable files, the name of the configuration file is the name of the executable + a “.config” extension. For example, the configuration file for “app1.exe” would be “app1.exe.config.” Configuration files for ASP.NET applications are always “web.config.”

Example 5

An application APP1 was developed with .NET Framework 1.1 and now we want to direct the application to start with .NET Framework 2.0. This means then, a XML file with the following codes and name of that file must be app1.exe.config must be placed in the exact directory where app1.exe is present and now APP1 will be redirected to .Net 2.0 when the application starts.

Listing 3

Publisher Policy

While application-specific policy is set either by the application developer or administrator, publisher policy is set by the vendor of the shared assembly. We have already discussed this.

Machine-wide Policy

The final policy level is machine-wide policy (sometimes referred to as Administrator policy). Policy statements made in machine.config affect all applications running on the machine. Machine-wide policy is used by Administrators to force all applications on a given machine to use a particular version of an assembly.
Policy Evaluation
[ Back To Top ]

The first thing the CLR does when binding to a shared named assembly is to determine which version of the assembly to bind to. The process starts by reading the version number of the desired assembly that was recorded in the manifest of the assembly making the reference. Policy is then evaluated to determine if any of the policy levels contain a redirection to a different version. The policy levels are evaluated in order starting with application policy, followed by publisher and finally administrator.
References
[ Back To Top ]

Side-by-Side Execution
Conclusion
[ Back To Top ]

Hope this article makes you to understand what DLL hell is and how you can avoid your application falling into the condition of DLL hell by creating your own custom version Policies.

ar.

Posted in Interview Questions | Leave a Comment »

C# – Questions

January 5, 2008 by venkatesh2urs

1.Does C# support multiple-inheritance?
No.

2.Who is a protected class-level variable available to?
It is available to any sub-class (a class inheriting this class).

3.Are private class-level variables inherited?
Yes, but they are not accessible. Although they are not visible or accessible via the class interface, they are inherited.

4.Describe the accessibility modifier “protected internal”.
It is available to classes that are within the same assembly and derived from the specified base class.

5.What’s the top .NET class that everything is derived from?
System.Object.

6.What does the term immutable mean?
The data value may not be changed. Note: The variable value may be changed, but the original immutable data value was discarded and a new data value was created in memory.

7.What’s the difference between System.String and System.Text.StringBuilder classes?
System.String is immutable. System.StringBuilder was designed with the purpose of having a mutable string where a variety of operations can be performed.

8.What’s the advantage of using System.Text.StringBuilder over System.String?
StringBuilder is more efficient in cases where there is a large amount of string manipulation. Strings are immutable, so each time a string is changed, a new instance in memory is created.

9.Can you store multiple data types in System.Array?
No.

10. What’s the difference between the System.Array.CopyTo() and System.Array.Clone()?
The Clone() method returns a new array (a shallow copy) object containing all the elements in the original array. The CopyTo() method copies the elements into another existing array. Both perform a shallow copy. A shallow copy means the contents (each array element) contains references to the same object as the elements in the original array. A deep copy (which neither of these methods performs) would create a new instance of each element’s object, resulting in a different, yet identacle object.

11. How can you sort the elements of the array in descending order?
By calling Sort() and then Reverse() methods.

12. What’s the .NET collection class that allows an element to be accessed using a unique key?
HashTable.

13. What class is underneath the SortedList class?
A sorted HashTable.

14. Will the finally block get executed if an exception has not occurred?
Yes.

15. What’s the C# syntax to catch any possible exception?
A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.

16. Can multiple catch blocks be executed for a single try statement?
No. Once the proper catch block processed, control is transferred to the finally block (if there are any).

17. Explain the three services model commonly know as a three-tier application.
Presentation (UI), Business (logic and underlying code) and Data (from storage or other sources).

Class Questions

1.What is the syntax to inherit from a class in C#?
Place a colon and then the name of the base class.
Example: class MyNewClass : MyBaseClass

2.Can you prevent your class from being inherited by another class?
Yes. The keyword “sealed” will prevent the class from being inherited.

3.Can you allow a class to be inherited, but prevent the method from being over-ridden?
Yes. Just leave the class public and make the method sealed.

4.What’s an abstract class?
A class that cannot be instantiated. An abstract class is a class that must be inherited and have the methods overridden. An abstract class is essentially a blueprint for a class without any implementation.

5.When do you absolutely have to declare a class as abstract?
1. When the class itself is inherited from an abstract class, but not all base abstract methods have been overridden.
2. When at least one of the methods in the class is abstract.

6.What is an interface class?
Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces do not provide implementation. They are implemented by classes, and defined as separate entities from classes.

7.Why can’t you specify the accessibility modifier for methods inside the interface?
They all must be public, and are therefore public by default.

8.Can you inherit multiple interfaces?
Yes. .NET does support multiple interfaces.

9.What happens if you inherit multiple interfaces and they have conflicting method names?
It’s up to you to implement the method inside your own class, so implementation is left entirely up to you. This might cause a problem on a higher-level scale if similarly named methods from different interfaces expect different data, but as far as compiler cares you’re okay.
To Do: Investigate

10. What’s the difference between an interface and abstract class?
In an interface class, all methods are abstract – there is no implementation. In an abstract class some methods can be concrete. In an interface class, no accessibility modifiers are allowed. An abstract class may have accessibility modifiers.

11. What is the difference between a Struct and a Class?
Structs are value-type variables and are thus saved on the stack, additional overhead but faster retrieval. Another difference is that structs cannot inherit.

Method and Property Questions

1.What’s the implicit name of the parameter that gets passed into the set method/property of a class?
Value. The data type of the value parameter is defined by whatever data type the property is declared as.

2.What does the keyword “virtual” declare for a method or property?
The method or property can be overridden.

3.How is method overriding different from method overloading?
When overriding a method, you change the behavior of the method for the derived class. Overloading a method simply involves having another method with the same name within the class.

4.Can you declare an override method to be static if the original method is not static?
No. The signature of the virtual method must remain the same. (Note: Only the keyword virtual is changed to keyword override)

5.What are the different ways a method can be overloaded?
Different parameter data types, different number of parameters, different order of parameters.

6.If a base class has a number of overloaded constructors, and an inheriting class has a number of overloaded constructors; can you enforce a call from an inherited constructor to a specific base constructor?
Yes, just place a colon, and then keyword base (parameter list to invoke the appropriate constructor) in the overloaded constructor definition inside the inherited class.

Events and Delegates

1.What’s a delegate?
A delegate object encapsulates a reference to a method.

2.What’s a multicast delegate?
A delegate that has multiple handlers assigned to it. Each assigned handler (method) is called.

XML Documentation Questions

1. Is XML case-sensitive?
Yes.

2.What’s the difference between // comments, /* */ comments and /// comments?
Single-line comments, multi-line comments, and XML documentation comments.

3.How do you generate documentation from the C# file commented properly with a command-line compiler?
Compile it with the /doc switch.

Debugging and Testing Questions

1. What debugging tools come with the .NET SDK?
1. CorDBG – command-line debugger. To use CorDbg, you must compile the original C# file using the /debug switch.
2. DbgCLR – graphic debugger. Visual Studio .NET uses the DbgCLR.

2.What does assert() method do?
In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.

3.What’s the difference between the Debug class and Trace class?
Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.

4.Why are there five tracing levels in System.Diagnostics.TraceSwitcher?
The tracing dumps can be quite verbose. For applications that are constantly running you run the risk of overloading the machine and the hard drive. Five levels range from None to Verbose, allowing you to fine-tune the tracing activities.

5.Where is the output of TextWriterTraceListener redirected?
To the Console or a text file depending on the parameter passed to the constructor.

6.How do you debug an ASP.NET Web application?
Attach the aspnet_wp.exe process to the DbgClr debugger.

7.What are three test cases you should go through in unit testing?
1. Positive test cases (correct data, correct output).
2. Negative test cases (broken or missing data, proper handling).
3. Exception test cases (exceptions are thrown and caught properly).

8.Can you change the value of a variable while debugging a C# application?
Yes. If you are debugging via Visual Studio.NET, just go to immediate window.

ADO.NET and Database Questions

1. What is the role of the DataReader class in ADO.NET connections?
It returns a read-only, forward-only rowset from the data source. A DataReader provides fast access when a forward-only sequential read is needed.

2.What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix. OLE-DB.NET is a .NET layer on top of the OLE layer, so it’s not as fastest and efficient as SqlServer.NET.

3.What is the wildcard character in SQL?
Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.

4.Explain ACID rule of thumb for transactions.
A transaction must be:
1. Atomic – it is one unit of work and does not dependent on previous and following transactions.
2. Consistent – data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t.
3. Isolated – no transaction sees the intermediate results of the current transaction).
4. Durable – the values persist if the data had been committed even if the system crashes right after.

5.What connections does Microsoft SQL Server support?
Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and password).

6.Between Windows Authentication and SQL Server Authentication, which one is trusted and which one is untrusted?
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.

7.What does the Initial Catalog parameter define in the connection string?
The database name to connect to.

8.What does the Dispose method do with the connection object?
Deletes it from the memory.
To Do: answer better. The current answer is not entirely correct.

9.What is a pre-requisite for connection pooling?
Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings. The connection string must be identical.

Assembly Questions

1. How is the DLL Hell problem solved in .NET?
Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.

2.What are the ways to deploy an assembly?
An MSI installer, a CAB archive, and XCOPY command.

3.What is a satellite assembly?
When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies.

4.What namespaces are necessary to create a localized application?
System.Globalization and System.Resources.

5.What is the smallest unit of execution in .NET?
an Assembly.

6.When should you call the garbage collector in .NET?
As a good rule, you should not call the garbage collector. However, you could call the garbage collector when you are done using a large object (or set of objects) to force the garbage collector to dispose of those very large objects from memory. However, this is usually not a good practice.

7.How do you convert a value-type to a reference-type?
Use Boxing.

8.What happens in memory when you Box and Unbox a value-type?
Boxing converts a value-type to a reference-type, thus storing the object on the heap. Unboxing converts a reference-type to a value-type, thus storing the value on the stack.

1. Explain the differences between Server-side and Client-side code?

ANS: Server side code will execute at server end all the business logic will execute at server end where as client side code will execute at client side at browser end.

2. What type of code (server or client) is found in a Code-Behind class?

ANS : Server side.

3. Should validation (did the user enter a real date) occur server-side or client-side? Why?

ANS : client side . there is no need to go to validate user input. If it relates to data base validation we need to validate at server side.

4. What does the “EnableViewState” property do? Why would I want it on or off?

ANS: IT keeps the data of the control during post backs.
if we turn off the values should not populate during server round trip.

5. What is the difference between Server.Transfer and
Response.Redirect? Why would I choose one over the other?

ANS: Server.Trnasfer will prevent round trip. it will redirect pages which or in the same directory. NO way to pass the query strings . Thru http context we can able to get the previous page control values.

Response.Redirect : There is a round trip to process the request. We can redirect to any page external / internal other than aspx. We can pass the query string thru which we can manage sessions.

6. Can you give an example of when it would be appropriate to use a web service as opposed to a non-serviced .NET component

ANS : Web services are best suite for Hetrogenious environment.
Remoting is best suite for Homogenious environment. The systems that under CLR.

7. Let’s say I have an existing application written using Visual Studio 6 (VB 6, InterDev 6) and this application utilizes Windows 2000 COM+ transaction services. How would you approach migrating this
application to .NET

We need to have Wrapper to communicate COM components in .net. and vis versa

CCW : Com Callable wrapper.
RCW : RUN time callable wrapper.

8. Can you explain the difference between an ADO.NET Dataset and anADO Recordset?\
ANS : DIsconnected architechure . Maintainace relation schemas. MUtilple table grouping.
Connected one .
9. Can you give an example of what might be best suited to place in the Application_Start and Session_Start subroutines?

ANS: APplication_start need for global variable which are available over the application.
Sesssion_Start : login dependent ( user dependent)

10. If I’m developing an application that must accomodate multiple security levels though secure login and my ASP.NET web appplication is
spanned across three web-servers (using round-robbin load balancing)
what would be the best approach to maintain login-in state for the
users?

ANS : Database Support.
or Thru state service.

11. What are ASP.NET Web Forms? How is this technology different than what is available though ASP (1.0-3.0)?
ANS : ASP . Interprepter.. use the script engine.
ASP.Net Compiled.

12. How does VB.NET/C# achieve polymorphism?
ANS : Function overloading.
Operator overloading.
11. Can you explain what inheritance is and an example of when you might use it?

ANS : Heridity.
Use the existing functionality along with its own properities.

13. How would you implement inheritance using VB.NET/C#?
ANS: Derived Class : Basecalss
VB.NEt : Derived Class Inherits Baseclass
14. Whats an assembly
ANS : A Basic unit of executable code >

Which contains : Manifest – Meta data
versioning , Calture , IL, Reference

15. Describe the difference between inline and code behind – which is best in a loosely coupled solution

Tightly coupled – INLINE
ANS: inline function bind at compile time can write in aspx page with in <% %> .

17. Explain what a diffgram is, and a good use for one

ANS : is an xml grammer. it talk about state of node in xml file.

18. Where would you use an iHTTPModule, and what are the limitations of any approach you might take in implementing one

ANS: Preprocessing before going to IIS.

20. What are the disadvantages of viewstate/what are the benefits
ANS : IT can be hacked . page is size is heavy.

21 Describe session handling in a webfarm, how does it work and what are the limits

ANS:
Session – mode
State sever
OUtprocess
sql

22. How would you get ASP.NET running in Apache web servers – why would you even do this?

ANS: —- Install Mod_AspDotNet
Add at the end of C:\Program Files\Apache Group\Apache2\conf\httpd.conf the following lines

23. Whats MSIL, and why should my developers need an appreciation of it if at all?

ANS : Microsoft Intermeidate lanaguage. which is the out put for all the .net supported languages after comiplation will produce.
Appreciation for cross language support.

24. In what order do the events of an ASPX page execute. As a developer is it important to undertsand these events?
ANS : INIT, PageLoad, Prerender , UNload.

25. Which method do you invoke on the DataAdapter control to load your generated dataset with data?

Fill()

26. Can you edit data in the Repeater control?
NO

27. Which template must you provide, in order to display data in a Repeater control?
ITemtemplate

28. How can you provide an alternating color scheme in a Repeatercontrol?

AlternateItemTemplate

29. What property must you set, and what method must you call in your code, in order to bind the data from some data source to the Repeatercontrol?

Datasource,
DataBind

30. What base class do all Web Forms inherit from?

System.Web.UI.Page

31. What method do you use to explicitly kill a user s session?

abondon()

32 How do you turn off cookies for one page in your site?
disablecookies.

33. Which two properties are on every validation control?
control to validate, error message
34. What tags do you need to add within the asp:datagrid tags to bind
columns manually?
autogenerated columns is set to false
35. How do you create a permanent cookie?
Cooke = ne cookee().
cooke.adddate.

36. What tag do you use to add a hyperlink column to the DataGrid?
hyper link column

37. What is the standard you use to wrap up a call to a Web service
————
38. Which method do you use to redirect the user to another page without performing a round trip to the client?
server.transfer
39. What is the transport protocol you use to call a Web service SOAP
http
40. True or False: A Web service can only be written in .NET
false
41. What does WSDL stand for? webservice discription language. it is used to generate for proxy( server object)

42. What property do you have to set to tell the grid which page to go to when using the Pager object?
Page Index.

43. Where on the Internet would you look for Web services?
UDDI
44. What tags do you need to add within the asp:datagrid tags to bind columns manually.

Autogenerate columns

45. Which property on a Combo Box do you set with a column name, prior to setting the DataSource, to display data in the combo box?

datatext
datavalue

46. How is a property designated as read-only?
get
47. Which control would you use if you needed to make sure the values in two different controls matched?
compare filed validator

48. True or False: To test a Web service you must create a windows application or Web application to consume this service?
no
49. How many classes can a single .NET DLL contain?

as many as u want..

—

? How many types of JIT compilers are available?
[Rama Naresh Talluri]
There are Two types of JIT compilers.
? standard JIT compiler.
? EconoJIT compiler.

? What are the different types of assemblies – name them?
[Rama Naresh Talluri]
Private
Public/Shared
Satellite assembly

? What is GAC? What are the steps to be taken to pick up the latest version from GAC?
[Rama Naresh Talluri]
This Global Assembly Cache(GAC) stores .NET assemblies to be shared by several applications on that computer.

publisher policy file is the configuration file to redirect to different version

1. Create the publisher Policy assembly using the assembly linker
2. Add the publisher policy assembly to the GAC using GACutil tool
Gacutil /i
3. During runtime CLR is looking into the publisher policy file and redirect the application to bind with new version assembly as specified inside the publisher policy.

? How do we use different versions of private assemblies in same application without re-build?
[Rama Naresh Talluri]
In Asseblyinfo file need specify assembly version.
assembly: AssemblyVersion

? Different methods of using a legacy COM component in .NET framework?

[Rama Naresh Talluri]
1. TLBIMP to create an Assembly from a COM component
2. Reference a COM component directly from .NET Editor

? How do you implement SSL?

[Rama Naresh Talluri]
1. create certificate request
[
=>Right click on the website (VD)
=>Click Directory Security Tab and click Server Certificate
=> Type name of certificate , Organization name , server name
location info,
=> Type the path need to save certificate information Submit certificate request.
]

2.Submit Certificate request.
[
=> Browse http://WebServerName/CertSrv/
=> select Request a Certificate and click Next
=> select Advanced Request and click Next
=> select Submit a Certificate Request using a Base64 and click Next
=> open the request document "Create a certificate request" section
=> Copy the contents of the document
=> Paste the contents of the document into the Web form's Base64 Encoded Certificate Request text box
=> Under Certificate Template, select Web Server or User, and then click Submit
]

? Is it possible to debug java-script in .NET IDE? If yes, how?
[Rama Naresh Talluri]

Yes. Write debugger statement to stop the cursor for debugging .

? How do you create a webservice proxy without .NET IDE and how do you test it?
[Rama Naresh Talluri]
wsdl utility we can test by consuming our service either windows / web application

? How to debug an assembly which is on Server?
[Rama Naresh Talluri]

[assembly: AssemblyDescription("Debug")]

? How many ways can we maintain the state of a page?
[Rama Naresh Talluri]

1.Client Side [ Query string, hidden variables, view state,cookies]
2.Server side [application , session, database]

? How do we Generate and compile source code dynamically?
[Rama Naresh Talluri]

.Net provides powerful access to the IL code generation process through the System.CodeDom.Compiler and Microsoft.CSharp and Microsoft.VisualBasic namespaces. In these namespaces we can find the tools that allow us to compile an assembly either to disk or into memory. We can also need the Reflection namespace as it contains the tools to invoke an object and its methods once we have compiled the object.

? How to pass server control values from one form to another using in-line code?
[Rama Naresh Talluri]
Server.Transfer

? How to make a webservice call asynchronous?
[Rama Naresh Talluri]

//Create a new instance of the Web Service Class
localhost.Service1 service = new MyWebServiceApp.localhost.Service1();
//Make the asynchronous Web Service call and call WriteHello
service.BeginHelloWorld(new System.AsyncCallback (WriteHello), service);

private void WriteHello(System.IAsyncResult oRes)
{
//End the call
localhost.Service1 service = (MyWebServiceApp.localhost.Service1) oRes.AsyncState;
//Write the call result to a MessageBox
MessageBox.Show(service.EndHelloWorld(oRes));
}

? How to invoke a webservice using javascript?
[Rama Naresh Talluri]

</script>

? List out all the possible ways of maintaining the state of a session.
[Rama Naresh Talluri]
InProc
OutProc [State server, sql]

? What is the use of multicast delegate?
[Rama Naresh Talluri]
A multicast delegate can call more than one method.

? What is the purpose of a private constructor?
[Rama Naresh Talluri]
Prevent the creation of instance for a class

? Differentiate Dispose and Finalize.
[Rama Naresh Talluri]
Finalize is called by the Garbage Collector, and the state of manage objects cannot be guaranteed, so we can not reference them. Also, we cannot determine when the GC will run, and which objects it will Finalize.

Dispose is called by the programmer, and is used to dispose of managed and unmanaged objects. Within we dispose method, we release all of our resources and call GC.SuppressFinalize as we have done the work of the GC.

? What is the purpose of Singleton pattern?
[Rama Naresh Talluri]

Singleton pattern is used to make sure that only one instance of a given class exists.

? Difference between structure and class.
[Rama Naresh Talluri]

Structures are value types;

classes are reference types.

Structures use stack allocation;
classes use heap allocation.

All structure elements are Public by default;
class variables and constants are Private by default, while other class members are Public by default.

Structure elements cannot be declared as Protected; class members can.

Structure variable declarations cannot specify initializers or initial sizes for arrays; class variable declarations can.

Structures implicitly inherit from the System.ValueType class and cannot inherit from any other type;

classes can inherit from any class or classes other than System.ValueType.

Structures are not inheritable; classes are.

Structures are never terminated, so the common language runtime (CLR) never calls the Finalize method on any structure; classes are terminated by the garbage collector (GC), which calls Finalize on a

class when it detects there are no active references remaining.

? Difference between DOM and SAX parser.
[Rama Naresh Talluri]

DOM Approach is useful for small documents in which the program needs to process a large portion of the document.

SAX approach is useful for large documents in which the program only needs to process a small portion of the document

SAX parsers generally requires more code than the DOM interface.
Unless we build a DOM style tree from our application’s internal representation for the data, we can’t as easily write the XML file back to disk.

The DOM tree is not constructed, so there are potentially less memory allocation.

If we convert the data in the DOM tree to another format, the SAX API may help to remove the intermediate step

If we do not need all the XML data in memory, the SAX API allows us to process the data as it is parsed

? Under what circumstances do you use DOM parser and SAX parser
[Rama Naresh Talluri]

DOM Approach is useful for small documents in which the program needs to process a large portion of the document.

SAX approach is useful for large documents in which the program only needs to process a small portion of the document

If we do not need all the XML data in memory, the SAX API allows us to process the data as it is parsed

? What is a well-formed XML and is the purpose of Diffgram?
[Rama Naresh Talluri]

A textual object is a well-formed XML document if it satisfies all the following points
1. Taken as a whole, it matches the production labeled document.
2. It meets all the well-formedness constraints given in this specification.
3. Each of the parsed entities which is referenced directly or indirectly within the document is well-formed.
The purpose of Diffgram is the Ability to pass parameters. It contains the tags that specify the original and new state of data

? Differentiate between legacy ADO recordset and .NET dataset.
What are typed datasets.
[Rama Naresh Talluri]
Recordset provides data one row at a time.
Connection is open .
Dataset is a data structure which represents the complete table data at same time.
Dataset is just a data store and manipulation is done through DataAdapters in .NET
Typed DataSets generate classes that expose each object the in the DataSet in Type-safe manner. It will raise error at compile time if any mismatch of column data type.

1. If both interfaces have method with the same name and also needs the same implementation, then provide only one implementation in the inheriting class.

2. If both interfaces have the same method name, but the implementation differs. In this case implement the method with interface name.When you creare object you can not access these method implementation with the object reference, you have to referer or call the method with the specific interface reference.

For example

interface A
{
void X();
}

interface B
{
void X();
}

class M:A,B
{
public void A.X()
{

}

public void B.X()
{
}
}

Class N
{
A oa = new M();
oa.X();
B ob = new M();
ob.X();
}

Posted in Interview Questions | Leave a Comment »

Sql questipn Part-1

January 5, 2008 by venkatesh2urs

Q. Execute an Operating System Command From Within SQL Server (Completed 36)

A. The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server.
Example:

EXEC Master..xp_cmdshell ‘Dir c:\’

www.careerenclave.com

Q. How can I create a plain-text flat file from SQL Server as input to another application?

A. One of the purposes of Extensible Markup Language (XML) is to solve challenges like this, but until all
applications become XML-enabled, consider using our faithful standby, the bulk copy program (bcp) utility.
This utility can do more than just dump a table; bcp also can take its input from a view instead of from a table. After you specify a view as the input source, you can limit the output to a subset of columns or to a subset of rows by selecting appropriate filtering (WHERE and HAVING) clauses.

More important, by using a view, you can export data from multiple joined tables. The only thing you cannot do is specify the sequence in which the rows are written to the flat file, because a view does not let you include an ORDER BY clause in it unless you also use the TOP keyword.

If you want to generate the data in a particular sequence or if you cannot predict the content of the data you want to export, be aware that in addition to a view, bcp also supports using an actual query. The only “gotcha” about using a query instead of a table or view is that you must specify queryout in place of out in the bcp command line.

For example, you can use bcp to generate from the pubs database a list of authors who reside in California by writing the following code:

bcp “SELECT * FROM pubs..authors WHERE state = ‘CA’” queryout c:\CAauthors.txt -c -T -S

Q. How can I programmatically detect whether a given connection is blocked?

A. A connection is blocked when it requires an object that another connection has a lock on. You can use the system stored procedure sp_lock to retrieve information about the current locks in SQL Server, and you can use the server process ID (SPID) to filter the information that sp_lock returns. To determine whether a given process is waiting for the release of a locked resource, you can execute the sp_GetBlockInfo procedure that follows.

Note: You must execute the procedure before the timeout.

USE master
GO

CREATE PROCEDURE sp_GetBlockInfo
@BlockedSPID as int
AS
IF EXISTS (select *
FROM master.dbo.syslockinfo
WHERE req_spid = @BlockedSPID
AND req_status = 3)

SELECT sli1.req_spid AS SPID,
SUBSTRING (u.name, 1, Cool As Mode,
DB_NAME(sli1.rsc_dbid) AS [Database],
OBJECT_NAME(sli1.rsc_objid) AS ,
sli1.rsc_Text AS
[Resource]
FROM master.dbo.syslockinfo sli1
JOIN master.dbo.spt_values u
ON sli1.req_mode + 1 = u.number
AND u.type = ‘L’
JOIN
master.dbo.syslockinfo sli2
ON sli1.rsc_dbid = sli2.rsc_dbid
AND sli1.rsc_objid = sli2.rsc_objid
AND sli1.rsc_text = sli2.rsc_text
WHERE sli2.req_spid = @BlockedSPID
AND sli1.req_status = 1
AND sli1.req_spid <> @BlockedSPID
AND sli2.req_status = 3
ELSE

SELECT CAST(1 as int) AS SPID,
SUBSTRING (”, 1, Cool AS Mode,
DB_NAME(NULL) AS [Database],
OBJECT_NAME(NULL) AS ,
CAST(NULL AS nchar(32)) AS
[Resource]
WHERE 1=2

The sp_GetBlockInfo procedure tells you the lock mode, the database and object names of the locked resource, and in the case of a blocking chain, which SPID is the root blocker. If the process is not blocked,
sp_GetBlockInfo returns an empty recordset.

You can also detect blocks by checking for error 1222, “Lock request time out period exceeded.” The LOCK_TIMEOUT setting controls how long a process will wait for locks to be released before timing out. When the lock timeout occurs, SQL Server sends error 1222 to the application. In SQL Server 7.0, this error aborts the statement but does not cause the batch to roll back, so you can look for the Transact-SQL system variable @@ERROR and determine where locks exist

Q. Can you create UNIQUE and PRIMARY KEY constraints on computed columns in SQL Server 2000?

A. In SQL Server, the physical mechanism that UNIQUE and PRIMARY KEY constraints use to enforce uniqueness is a unique index. Because SQL Server 2000 supports indexes on computed columns,
you can create UNIQUE and PRIMARY KEY constraints on computed columns.

Defining a UNIQUE constraint on a computed column is a straightforward process, as the following example shows:

CREATE TABLE T1 (
col1 int NOT NULL,
col2 AS col1 + 1 UNIQUE
)

However, if you define a PRIMARY KEY on a computed column, such as:

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS col1 + 1 PRIMARY KEY
)

you receive the following error:

Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘T2′.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Because of the primary key constraint, SQL Server requires you to guarantee that your computation’s
result will not be NULL. The computation in the computed column can overflow
(for example, when you add 1 to the largest integer) or underflow (when you subtract 1 from the smallest integer), and other computations can result in a divide-by-zero error. However, if the ARITHABORT
(which determines whether a query has ended when an overflow or a divide-by-zero error occurs)
and ANSI_WARNINGS (which specifies ANSI SQL-92 standard behavior for several error conditions)
session settings are off, instead of ending the query, the computation can have a NULL result.

In practice, when either ARITHABORT or ANSI_WARNINGS settings is off, you cannot create an index on a computed column or insert values into a table that has an index on a computed column because SQL Server detects such an attempt and returns an error. But SQL Server still requires you to guarantee that the computation will not result in NULL values. The trick is to wrap the computed column’s computation with the ISNULL() function and supply an alternative value if the computation results in NULL:

CREATE TABLE T2 (
col1 int NOT NULL,
col2 AS ISNULL(col1 + 1, 0) PRIMARY KEY
)

Q. Why does my inline or embedded SQL run faster than my stored procedures?

A. Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:

SELECT * FROM dbo.mytable

This technique helps you reuse plans and prevent cache misses.

Q. Why doesn’t SQL Server permit an ORDER BY clause in the definition of a view?

A. SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, we can’t fully explain the restriction here. However, if you need to be able to specify an ORDER BY clause in a view, consider using the following workaround:

USE pubs
GO

CREATE VIEW AuthorsByName
AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO

The TOP construct, which Microsoft introduced in SQL Server 7.0, is most useful when you combine it with the ORDER BY clause. The only time that SQL Server supports an ORDER BY clause in a view is when it is used in conjunction with the TOP keyword.

Note that the TOP keyword is a SQL Server extension to the ANSI SQL-92 standard.

Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?

A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you’re dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward. A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.

Q, The Difference between ‘Count’ and ‘Count(*)’

A. ‘Count’: Counts the number of non-null values.
‘Count(*)’: Counts the number of rows in the table, including null values and duplicates.

Q. I have two tables t1 and t2 both with the columns a1, a2. I want to find the difference of (the set of t1) – (the set of t2) without using the keyword EXCEPT because MSSQL 2000 does not recognize that word. I have tried this query but it does not give me what I want: SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.* FROM t1 INNER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)

A.SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2)

This is the solution. The difference of (the set of t1) – (the set of t2) is

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a1 AND t1.a2=t2.a2 WHERE t2.a1 IS NULL AND t2.a2 IS NULL

According to SQL Query Analyzer, this is slightly more efficient than the left join (possibly only because
of the tables I tested it with):

SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.a1=t2.a1 AND t1.a2=t2.a2)

Q, Leading Zero’s in Stored Procedure

I have a form and I am passing a value to a stored procedure. The value has leading zeros. When the values are passed the leading zeros are dropped, thus causing my stored procedure to blow up. Is there a way to maintain those zeros in passing or pick them up again in the procedure?

Try to pass value as string.

If you were passing a string value then the LZ should not be dropped, so I suspect that you are passing a
numeric value then converting it back to a varchar which will drop any LZ. If you don’t want to change the interface then you can always restore the LZ in the stored procedure by using the following.

declare @lz varchar(10)
declare @numeric numeric(10,2)
declare @result varchar(20)
select @lz = ‘0000000000′
select @numeric = 0000123.45
– the LZ will be lost upon assignment

select @result = substring(@lz, 1, datalength(@lz)-datalength(convert(varchar

Q, Method to Perform Case Sensitive Searches in SQL Server

A, By default, SQL Server 7.0 installation sets up SQL Server to run in a case insensitive mode. For most applications this may work great, but there are certain situations were case sensitive searches are required. For instance, if a web site needs to have passwords that are case sensitive a method needs to be devised to perform case-sensitive queries. The following script performs a case-sensitive search. First let’s create a table:

CREATE TABLE test
(
id INT NOT NULL,
msg VARCHAR(100) NOT NULL
)

Now let’s insert some case sensitive data into it:

INSERT INTO test (id, msg) VALUES (1, ‘bUSY’);
INSERT INTO test (id, msg) VALUES (2, ‘BUSY’);
INSERT INTO test (id, msg) VALUES (3, ‘busy’);

In our test we are searching for a ‘bUSY’ value in the msg column of the test table. So the syntax of the same query, if the SQL Server was set to be case sensitive, would be:

SELECT * FROM test where msg = ‘bUSY’;

This query will return all rows in the test table. Now, here is the script that will perform the case sensitive search.

DECLARE @table VARCHAR( 30 )
DECLARE @col VARCHAR( 30 )
DECLARE @searchVal VARCHAR( 195 )

SET @table = ‘test’
SET @col = ‘msg’
SET @searchVal = ‘bUSY’

DECLARE @sql VARCHAR( 255 )
DECLARE @colLength VARCHAR( 3 )

SELECT @colLength = CONVERT( varchar(3), DATALENGTH( @searchVal ) )

SELECT @sql = ‘SELECT * ‘ +
‘ FROM ‘ + @table +
‘ WHERE’ +
‘ CONVERT( varbinary( ‘ + @colLength + ‘), ‘ + @col + ‘) = ‘ +
‘ CONVERT( varbinary( ‘ + @colLength + ‘), “‘ + @searchVal + ‘”)’ +
‘ AND ‘ + @col + ‘=”‘ + @searchVal + ‘”‘

EXEC( @sql )

Listing Available Tables

Q, How do I list the available tables in a database I’m querying?

A, You can get a list of tables in a database by calling the INFORMATION_SCHEMA.Tables view like this:

SELECT * FROM information_schema.tables

This returns the name of the current database, the owner, the table name, and the table type for each table in the database. It’s possible to query the system tables directly, but if this gives the information you need,
it’s better to use the existing views that come with SQL Server.

Question: How do I count the number of duplicate items in a table?

Answer: Lets break down your question into several steps. First, let’s create a sample table using the following code:

create table dups
(
i int
)
go

declare @i int

@i = 0

while (@i < 35)
begin
insert into dups(i) values (cast (rand() * 50 as int))
select @i = @i + 1
end

Now, let’s find rows that are duplicates. For that we can use a simple group by statement:

select i,count(*) as num_records from dups group by i having count(*) > 1

My sample data produced the following result set:

i num_records
———– ———–
0 2
5 2
18 2
22 2
27 2
31 2
34 2
44 2
49 2

This identifies the rows that have duplicates. But it does not return the total number of duplicates in the table. The first change we must make is to recognize the above rows that show 2 contain only one duplicate.
So we want a query that basically sums up the duplicates from the above query. To do so, we take the previous query and can put that in the from statement as a derived table. We then can use the sum function to create the total for us:

select sum(num_dups)
from (select i,count(*)-1 as num_dups
from dups
group by i
having count(*)-1 > 0) as mydups

Using Distributed Queries on SQL Server 7.0

SQL Sever 7 allows developers to execute commands against OLE DB data sources on different servers. In order to execute commands on remote servers, the SQL Server instance where the commands will be issued must be setup properly. This entails adding the remote server to SQL Server’s linked server list. Do this by using the sp_addlinkedserver command.

For example, to link a remote SQL Server database that resides on the RemoteDBServer server, you would usethe following syntax:

EXEC master. . sp_addlinkedserver
@server = ‘RemoteDBServer’,
@provider = ‘SQLOLEDB’,
@srvproduct = ‘SQL Server’,
@datasrc = ‘RemoteDBServer’

Note that only members of the sysadmin role can set this server option. Once the remote database has been linked, queries can be executed against it as long as the remote tables are prefaced using Server.Database.Table Owner.Table Name.

For example, the following query would return all rows in the authors table of our RemoteDBServer SQL Server database:

SELECT * FROM RemoteDBServer.pubs.dbo.authors

Dynamic Query with …. in (@var)

Question: Can I use a variable in a query with the IN clause (a,b,c..z), without getting quotes or conversion errors?

Answer: You can use a variable as your IN clause, but this requires that you use the EXEC function to run the statement.

Delete Files from SQL Server Local Machine

Ever wanted to delete files from the local machine that your SQL Server database is running?
You can do it using the extended stored procedure xp_cmdshell like this:

EXEC master..xp_cmdshell ‘del C:\file.txt’

But this requires the sysadmin option on the SQL Server and Admin role from NT server. In most instances it is not preferable to give these privileges. So to delete files without requiring this access use the built-in SQL Server Automation APIs and the FileSystemObject:

DECLARE @hr int
DECLARE @ole_FileSystem int

EXEC @hr = sp_OACreate ‘Scripting.FileSystemObject’, @ole_FileSystem OUT

EXEC @hr = sp_OAMethod @ole_FileSystem, ‘DeleteFile’, NULL, ‘C:\file.txt’

EXEC @hr = sp_OADestroy @ole_FileSystem

Large Text Fields

Question: How do I create a text field of greater than 8,000 characters (in v7.0)? I attempted to use the “text” data type, but my code returned an error saying the maximum size was 8,000. Here’s the code:

CREATE TABLE X ( X_ID int IDENTITY(1,1), X_DESC text (60000) NOT NULL ) GO

Answer: SQL Server is returning a bogus error message. The real error has to do with your syntax.
When specifying text you don’t specify a size.

You can see the real error message if you reduce the number 60000 to 5. Then you will get this message:
Server: Msg 2716, Level 16, State 1, Line 1

Column or parameter #2: Cannot specify a column width on data type text. Instead, simply specify it as text without the parentheses and the number. The actual size of the storage used for the text field will depend on how much data you actually put in the column

Importing Excel into SQL without Using Wizard

Question: How do I give a user the option of importing Excel and a delimited text file into a SQL Server Database without manually using SQL DTS?

Answer: You can use the DTS object model to programmatically create, modify and run DTS packages.
You can do this many ways, but essentially this object model has objects for anything you can do in DTS.
If you already have the package created and saved as a file, add a reference to the DTS Library.
Then you can call it like this:

Dim Package As New DTS.Package

Package.LoadFromStorageFile App.Path “\DTSPackage.dts”
Package.Execute

Connecting to SQL Server 7 via MS-DOS 6.2

Question: I have some complex engine control software that has to run under MS-DOS 6.2, but it needs to transfer data to SQL Server 7. What’s the best way to connect to SQL Server from DOS?

Answer: You can use OSQL or ISQL to connect to SQL Server from the command line. Both of these utilities are Win32 command-line utilities that allow you to log in to a server and issue any SQL command. The OSQL utility supports the most functionality and uses ODBC to connect to SQL Server.

The syntax “osql/?” provides a listing of all OSQL flags.

This syntax connects to a server and issues a query:

osql -S “servername” -U “sa” -d “pubs” -q “SELECT * FROM Authors”

To insert data into a database, you could put OSQL commands in a batch file or provide an input file of
SQL INSERT commands. See the SQL Server BOL for details and syntax of OSQL. If you have a straight DOS box, you need to install the 16-bit client that comes with SQL Server 6.5. Both the client and the 6.5 version of ISQL use DB-Lib, which doesn’t support some SQL Server 7 features like unicode columns, varchar columns larger than 255, etc.

Passing a CSV List Within an IN Statement

Question: I have a CSV list I am passing through as a parameter in a stored procedure.

Example of Region list: 1,2,3,4,5,6,7,8,9,10
CREATE PROCEDURE sp_getReport
@Regionlist varchar(100) AS
Select * from tblRegion where Region_ID IN (@regionlist)

The varchar treats the list as one block of data. How can I change my code/syntax to read each value of the list separately within the IN statement?

Answer: When you pass variables that are part of the statement in a stored procedure, you need to use
the EXEC(UTE) statement. Here’s the syntax to call the stored procedure and modify the current stored procedure to take the regions list as a parameter (the only difference is I called the column RegionID instead of Region_ID):

sp_getreport ‘1,2,3,4,5,6,7,8,9,10′

CREATE PROCEDURE sp_getReport
@regionlist varchar(100)
AS

EXEC (‘Select * from tblRegion where RegionID IN (‘ + @regionlist +’)')

Creating Tables with Variable Names

Question: Is it possible to create tables in stored procedures using a variable for the table name?
For instance:

declare @MyName
set @MyName = ‘dbo.Unique_Name’
create table @MyName ….
This obviously doesn’t work, but does this ability exist?

Answer: Yes, you can issue DLL statements with variables; you just need to use the EXECUTE statement.
For example, this statement uses a variable for the table name, as in your example:

DECLARE @TableName varchar(255)
SET @TableName = ‘[dbo].[TestTable]‘

EXEC (‘CREATE TABLE ‘ + @TableName + ‘ (
[ID] [int] NULL ,
[ItemDesc] [char] (50) NULL
)’)

Use Sysobjects in SQL Server to Find Useful Database Information

SQL Server sysobjects Table contains one row for each object created within a database. In other words,
it has a row for every constraint, default, log, rule, stored procedure, and so on in the database.
Therefore, this table can be used to retrieve information about the database. We can use xtype column in
sysobjects table to get useful database information. This column specifies the type for the row entry in sysobjects.

For example, you can find all the user tables in a database by using this query:

select * from sysobjects where xtype=’U’

Similarly, you can find all the stored procedures in a database by using this query:

select * from sysobjects where xtype=’P’

This is the list of all possible values for this column (xtype):

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

Query to a Text File

Question: I would like to create a stored procedure that runs a query and outputs the results to a text file and allows me to add extra delimeters and static field info.

Answer: SQL Server has no native command for outputting query results to a file. You can use the extended
stored procedure xp_cmdshell and call isql (command-line SQL) with your query and output the results to a file. Any delimiters would need to be part of the SELECT string:

DECLARE @isqlString varchar(255)

SELECT @isqlString = ‘isql -Q “SELECT DateCol FROM NorthwindTest.dbo.Test” -E -o C:\Results.txt’

EXEC master..xp_cmdshell @isqlString

Dynamic Variables and Stored Procedures

Question: I need to pass the @TheVar variable to the stored procedure’s IN statement but I keep getting zero records. If I type this statement into the SQL… WHERE Name IN (‘John’, ‘Frank’, ‘Tom’) the statement works fine; 20 records are returned.

EXECUTE the_proc “‘John’, ‘Frank’, ‘Tom’”

CREATE PROCEDURE the_proc
@TheVar nvarchar(40)
AS SELECT COUNT(FieldName) FROM Clients WHERE Name IN (@TheVar)
Do you have any ideas? I use MS SQL Server 7.0 SP2.

Answer: If you want to use variables as part of your SQL statement you need to modify it to call the EXECUTE statement. I created a Clients table with a single Name field of varchar(255) with records for John, Frank, and Tom. Then I created a stored procedure (similar to yours) that uses the input variable to build the IN clause like this:

CREATE PROCEDURE GetClients
@TheVar varchar(255)
AS
EXEC (‘SELECT COUNT(Name)
FROM Clients
WHERE Name IN (‘ + @TheVar + ‘)’)

As you can see, it builds the SELECT statement using the variable and then the EXEC statement runs it.
The syntax to call this procedure is:

GetClients “‘John’, ‘Tom’, ‘Frank’”
—
3

Question: If my SQL Server has the following specs: 6.05.02 SQL-DMO 6.50.252 DB-Library Which version do I have? And which service pack version do I have?

Answer: If you want to query the version of SQL Server that’s currently running you can use the @@version variable:

SELECT @@version

This returns the version, processor, build and service pack information for the currently installed SQL Server. This information is stored in the system tables, and you can retrieve more details by calling the extended stored procedure xp_msver. Be sure to call it from the master database.

Data Type

Question: How do I compare two values when one value(data type) is char and the other is an integer?

Answer: For this you can use the CONVERT function. Since you can’t convert character data to numbers,
you’ll have to convert the integer column to character. Assuming you have a table called TestTable with two columns:

IntCol int
CharCol char(10)

with these values

IntCol CharCol
—— ——-
1 1
2 2
3 xyz
4 5

You can use this query to SELECT rows based on a comparison:

SELECT * FROM TestTable WHERE CONVERT(char(10), IntCol) = CharCol

IntCol CharCol
———– ———-
1 1
2 2

E-mail in a Query

www.careerenclave.com

Question: How do I send an e-mail to myself if a field reads “SEND_MAIL?”
For example, a user wants more info on a product, so he clicks on the Send Mail button, which in turn throws an entry into the database. This entry is SEND_MAIL. Now at the end of the day I would like the server to scan the database for the word SEND_MAIL and, when it finds one, send me an email with the name and email address of the person.

Answer: To answer this question I took the scenario of a Web site that logs user registrations and puts a
visitor’s first name, last name and e-mail address in a table. I then created a stored procedure that selects
this data and e-mails it to an e-mail address provided as an input parameter. If the SendMail column in the table is Y, then the details will be e-mailed. The stored procedure is called like this:

usp_CheckForMail ‘name@emailaddress.com’

The script to create the table is as follows:

if exists (select * from sysobjects where id = object_id(N’[dbo].[TestTable]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Fname] [varchar] (50) NULL ,
[Lname] [varchar] (50) NULL ,
[EmailAddress] [varchar] (100) NULL ,
[SendMail] [char] (1) NULL
) ON [PRIMARY]
GO

The script to create the stored procedure to send the e-mails is:

if exists (select * from sysobjects where id = object_id(N’[dbo].[usp_CheckForMail]‘) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[usp_CheckForMail]
GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE usp_CheckForMail
@SendToEmail varchar(255)
AS
DECLARE @ID int, @Fname varchar(50), @Lname varchar(50), @EmailAddress varchar(100), @MsgText varchar(255)
DECLARE WebVisitors CURSOR FOR
SELECT ID, Lname, Fname, EmailAddress FROM TestTable

WHERE SendMail = ‘Y’
ORDER BY Lname, Fname
OPEN WebVisitors

FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress

EXEC master.dbo.xp_startmail /* typically mail will be started on your server */

– Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

– PRINT ‘Visitor: ‘ + @Fname + ‘ ‘ + @Lname + ‘ at ‘ + @EmailAddress + ‘ [ID=' + CAST(@ID AS varchar) + ']‘

SET @MsgText = ‘Visitor: ‘ + @Fname + ‘ ‘ + @Lname + ‘ at ‘ + @EmailAddress + ‘ [ID=' + CAST(@ID AS varchar) + ']‘
EXEC master.dbo.xp_sendmail @recipients = @SendToEmail, @message =@MsgText, @subject = ‘New Web Visitor’

FETCH NEXT FROM WebVisitors
INTO @ID, @Lname, @Fname, @EmailAddress

END

CLOSE WebVisitors
DEALLOCATE WebVisitors

GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

Pros and Cons of Extended Stored Procedures

Question: What are the pros and cons of creating extended stored procedures?

Answer: The pros are that you can implement additional functionality and access data from DLLs from within SQL Server. If you need to do something that can be done only in C or C++, or if you have data that can be accessed only outside of SQL Server, you can still provide a link to it. The biggest con to extended stored procedures is that they run in the same process space as SQL Server. So an errant DLL could overwrite memory and cause SQL Server to crash or even corrupt data. The biggest safeguard against these problems is thorough testing of the procedure

Percent Sign in SQL

Question: I have a table in which the key field has a value stored with a percent sign, like ‘1234%’. Using this value, I want to select from another table that can have values like ‘1234567′, ‘1234678′ and ‘1234098′. How do I go about it?

Answer: The percent sign (%) is a wildcard in SQL Server. It can be used at the beginning or end of a string. So the following syntax will return all of the records you mentioned:

SELECT * FROM TestTable WHERE Col LIKE ‘1234%’

Col
——-
1234567
1234678
1234098
If you want to do an exact match for ‘1234′ without the percent sign, then you’ll have to trim off the last character, like this:
SELECT * FROM TestTable WHERE Col LIKE LEFT(‘1234%’, (LEN(‘1234%’)-1))

Posted in Interview Questions | Leave a Comment »

.Net General

January 5, 2008 by venkatesh2urs

The .NET world according to Microsoft
When it comes to Web services, there are essentially two world-views: Microsoft’s and everyone else’s. Microsoft’s world-view, of course, is called .NET and while most people have heard of it, its exact outlines are often fuzzy — so fuzzy, in fact, that a TechTarget survey found that under 10% of respondents even understand what .NET is. (Click here for more information about the survey.)

To bring .NET into focus, this week I’ll start a two-part series about the architecture. This column takes a look at .NET’s basic building blocks, while the next one will look at some of the architecture’s more controversial aspects, including its Passport technology and new TrustBridge technology.

A look at the basics
For a start, .NET is not a drastic departure from Web services — it’s simply Microsoft’s version of the technology. So the roles of UDDI, XML, SOAP, and other basic protocols are essentially no different in the Microsoft view of the world than in the rest of the Web services world.

How those services are used and built, though, differs in .NET from the rest of the world. And there’s the rub. In theory, when you build a Web service, you should be able to pick and choose among technologies and vendors, assembling a “best-of-breed” suite of development platforms, servers, security and other tools in order to deliver your service. With .NET that’s not really the case: In essence, Microsoft offers a soup-to-nuts approach with .NET, in which every part of the process, and every piece of hardware and software comes from Microsoft and is based on Windows. In fact, cynical observers might believe that Microsoft’s head-first jump into Web services via .NET is nothing more than an attempt to shore up Windows in the face of a Web services architecture in which protocols like SOAP, UDDI, and XML could conceivably make an underlying operating system irrelevant.

Whether you believe that, or whether you instead believe that .NET is Microsoft’s coherent strategy for making it easier for developers and users to achieve a kind of universal connectivity, you need to know about the core of .NET. So here is the current lineup of what .NET is and how it works, according to Microsoft.

The building blocks of .NET
In the Microsoft vision, there are four primary building blocks of .NET:

1) Developer tools, including Microsoft Visual Studio .NET and the Microsoft .NET Framework;

2) Servers, including .NET Enterprise Servers and the .NET Server Family;

3) XML-based Web services, which are the applications themselves, such as the Microsoft MapPoint .NET, a modular Web service that can be integrated into other applications and Web sites; and

4) Smart clients, which are the devices that run the Web services, and can be anything from a PC to a PocketPC, to the Xbox gaming console. Not surprisingly, they all run some version of Windows.
In the rest of this column, we’ll take a closer look at each.

Developer tools The centerpiece of Microsoft’s developer tools is Visual Studio .NET, which allows developers to use a variety of languages including Microsoft’s Visual Basic .NET, C++ .NET, and Visual C# .NET to build .NET services. Notably missing from the lineup, of course, is Java — no surprise, since Microsoft has all but abandoned its support for Java. Java developers who do want to develop for .NET can use Visual J# .NET, the beta of which has just been released. (Note: For more information about Visual Studio, head to http://msdn.microsoft.com/vstudio/, and for information about Visual J# .Net, go to http://msdn.microsoft.com/vjsharp/.) Also part of the tools is the .NET Framework, required to build and run Web services. [Editor's Note: For more .NET resources, be sure to also visit the SearchVB .NET Info Center.]

Servers Microsoft has several server product lines that support .NET: Windows 2000 Servers and .NET Enterprise Servers, and the upcoming Windows .NET Server Family. In essence, the Windows 2000 Servers and .NET Enterprise Servers are nothing new, and to a certain extent merely extend existing server software, with an eye toward supporting .NET and XML. Of them, the Microsoft BizTalk Server 2000, which serves XML-based business processes, is particularly relevant, but to one degree or another, most of Microsoft’s server line supports NET. The Windows .NET Server family, on the other hand, was designed specifically for .NET and so will greater . NET support. It’s currently in beta 3, includes the .NET Framework built into it, and integrates with other .NET technologies such as Microsoft Passport.

XML-based Web services These, of course, are the Web service applications themselves. After an initial flurry of announcements indicating that Microsoft was going full-bore into developing consumer-level .NET services such as stock alerts and similar services, it’s now unclear where it plans to go with developing .NET services itself. One of few functioning Web services is the MapPoint .NET service — a useful if run-of-the-mill service that lets you find maps and driving directions, and that, for a price, can be integrated into a Web site using .NET.

Smart clients In the Microsoft world-view, Windows will run on everything from cell phones to PDAs to gaming consoles, to kiosks — in fact, anything that has any intelligence built into it. And the plan is that those “smart client” devices will be .NET capable. In the long run, the most interesting of these devices won’t be computers — it may be your refrigerator, toaster or car. Microsoft is pushing Windows XP Embedded, a “componentized” version of the operating system designed to run on any kind of intelligent device. So .NET services could connect all of your home appliances into larger networks of retail stores, automobiles, gaming consoles and more…think of it as .NET everywhere.

What is Microsoft .NET?

Microsoft .NET is the Microsoft strategy for connecting systems, information, and devices through Web services so people can collaborate and communicate more effectively. .NET technology is integrated throughout Microsoft products, providing the capability to quickly build, deploy, manage, and use connected, security-enhanced solutions through the use of Web services.

What are Web Services?

Web services are small, reusable applications that help computers from many different operating system platforms work together by exchanging messages. Web services are based on industry protocols that include XML (Extensible Markup Language), SOAP (Simple Object Access Protocol), and WSDL (Web Services Description Language). These protocols help computers work together across platforms and programming languages.

From a business perspective, Web services are used to reenable information technology so that it can change, move, and adapt like other aspects of a business. They not only connect systems, they can help connect people with the information they need, within the software applications they are used to using, and wherever they happen to be.

Microsoft offers a complete range of software that helps organizations and individuals benefit from Web service-based connectivity. These include theMicrosoft Visual Studio .NET 2003 developer tools, the Windows Server System that hosts Web services, and familiar desktop applications such as the Microsoft Office System that “consume” Web services.

This illustration shows the relationship between the core technology components of .NET.

What are the Benefits of .NET?

.NET technologies use Web services to help enhance the computing experience with highly integrated communications and information. Because .NET includes the core technologies for building Web services, it benefits everyone: individual users, organizations, and developers.
•

.NET benefits organizations by helping them get the most out of their existing technology investments while creating new ways to implement powerful, cost-effective information technology that will meet future needs. .NET technologies and Web services can be used to integrate even the most disparate computing environments. .NET frees organizations from the confines of proprietary technology, providing the flexibility and scalability that can help organizations connect their existing IT systems and build a foundation for the next wave of computer technology. .NET and Web services can help organizations lower operating costs by helping connect systems; increase sales by helping employees access the right information when and where they need it; integrate services and applications with customers and partners; and lower the costs of information technology with tools that help developers quickly create new solutions to address business issues.
•

.NET benefits individuals by helping provide a more personal and integrated computing experience. .NET-enabled computing is centered on the user–not on the features of the software or hardware. The user’s experience becomes very customizable and provides integrated data and customized interactions that work well with a wide range of computing hardware, such as Pocket PCs, Smartphones, laptops, and other devices. The .NET-based experience enhances the mobile computing experience so user’s can get their information regardless of their location. For example, it can take place on a desktop computer, in the car on a Smartphone, or at the store on a Pocket PC.
•

.NET benefits developers by providing a tightly integrated set of tools for building and integrating Web services. Visual Studio .NET and the .NET Framework provide comprehensive development platforms that offer the best, fastest, and most cost-effective way to build Web services. With Visual Studio .NET and the .NET Framework, developers can take advantage of a programming model designed from the ground up for creating Web services in a highly productive, multilanguage environment. With scalable, high-performance execution, the .NET tools allow developers to use existing skills to create a wide range of solutions that work across a broad array of computing devices. .NET also provides a foundation for building Service Oriented Architectures (SOA).

What are Service Oriented Architectures (SOA)?

SOA describes an information technology architecture that enables distributed computing environments with many different types of computing platforms and applications. Web services are one of the technologies that help make SOAs possible. As a concept, SOA has been around since the 1980s, but many early IT technologies failed to achieve the goal of linking different types of applications and systems. By making early investments with .NET, Microsoft has helped provide the building blocks that today are putting many enterprise customers on the path to successfully implementing SOAs. With SOAs, companies can benefit from the unimpeded flow of information that is the hallmark of connected systems.

What are Web Services Enhancements for Microsoft .NET (WSE)?

WSE is an add-on to Microsoft Visual Studio .NET and the Microsoft .NET Framework that helps developers build greater security features into Web services using the latest Web services protocol specifications and standards. With WSE 2.0 developers can create security-enhanced connected systems that help improve business processes within–and beyond–corporate trust boundaries and create new revenue-generating opportunities.

What is a Smart Client?

Smart clients are client applications that consume Web services and reside on user hardware such as desktop PCs, laptops, Pocket PCs, and Smartphones. They are easily deployed and managed and provide an adaptive, responsive, and rich interactive experience by taking advantage of the computing resources on the device and intelligently connecting to distributed data sources.

What is .NET Passport?

.NET Passport is a Web-based service that is designed to make signing in to Web sites fast and easy. Passport enables participating sites to authenticate a user with a single set of sign-in credentials, alleviating the need for users to remember numerous passwords and user names.

What are the Current Microsoft Products and Technologies That Use .NET?

Microsoft .NET provides everything that is needed to develop and deploy a Web service-based IT architecture: servers to host Web services; development tools to create Web services; applications to use them; and a network of more than 35,000 Microsoft partners to help organizations deploy and manage them. .NET technologies are supported throughout the family of Microsoft products, including the Windows Server System, the Windows XP desktop operating system, and the Microsoft Office System. And .NET technologies will play an even larger role in future versions of Microsoft products.

Who is Using .NET?

Organizations worldwide are implementing .NET technologies and Web services to create connected businesses and to help individuals communicate and collaborate more effectively. For example, Honeywell, GlaxoSmithKline, Sony, Dollar Rent A Car, Farmers Insurance, and the United States Postal Service are just a few of the well-known names that are using .NET. To read more about .NET technology in use today, visit the Microsoft .NET Case Study site.

How Do I Find a Microsoft Partner to Help Me Connect My Organization Using .NET?

Microsoft Certified Partners are independent companies that can provide you with the highest levels of technical expertise, strategic thinking, and hands-on skills. Microsoft Certified Partners encompass a broad range of expertise and vendor affiliations. Their real-world perspective can help you prioritize and effectively deliver your technology solutions. Find a partner in your area, here.

ASP.NET is a technology for building powerful, dynamic Web applications. It is part of the .NET Framework.

Developer Productivity

ASP.NET helps you deliver real world Web applications in record time.

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 lets you build great pages with far less code than with classic ASP or technologies like PHP or JSP. Best of all, ASP.NET pages work in all browsers – including Firefox, Opera, Safari, and Internet Explorer.

Great Tool Support A broad range of tools support development with ASP.NET. You can work with a simple text editor (even Notepad), take advantage of the freely available Visual Web Developer 2005 Express Edition, or work with a full-powered integrated development environment, such as Visual Studio 2005. For enterprise developers, Visual Studio Team System 2005 includes additional tools for stress-testing and designing Web applications.

Starter Kits Not sure how to get started building an ASP.NET Web site? Download one of the ASP.NET Starter Kits – ready-built Web sites using best practices to help you see how a site should be created. Starter Kits for personal, club, portal and e-commerce Web sites are already available, and both Microsoft and third parties are making many more available.

Easy to get started Quickly get up to speed programming with ASP.NET by following along with the QuickStart tutorials. Read a guided tour of Visual Web Developer to become familiar with its features. Access the broad and growing community support for ASP.NET.

Improved Design

ASP.NET helps you create appealing, functional Web applications.

Master Pages ASP.NET makes it easy to create and apply a common look-and-feel to your Web site with Master Pages. Change the Master Page, and all pages automatically update to reflect those changes.

Themes Give your Web pages a consistent appearance with the built-in theming support in ASP.NET. Easily organize and apply changes to stylesheets and controls by applying a new theme to your pages.

Built Upon Standards

ASP.NET helps you create pages compliant with international standards.

XHTML Compliance Creating XHTML-compliant pages is easy with ASP.NET. Web controls support rendering valid, well-formed XHTML markup with a simple configuration option.

Accessibility Support Create Web applications usable by the widest possible audience support for building and testing accessible Web applications.
Improved Security

ASP.NET helps you protect your data and users.

Application Services There is no need to write common code for managing users and personalization with ASP.NET, as these features are included as application services. ASP.NET membership services provide best practices guidance and support for managing access to your Web applications.

Built-in Protection ASP.NET provides built-in protection from many common forms of hacker attacks, including cross-site scripting, request-replay attacks and more.

Improved Performance and Scalability

ASP.NET lets you use serve more users with the same hardware.

Caching Caching can dramatically improve the performance of Web applications, and ASP.NET supports many forms of caching. Output caching improves the rendering of controls to XHTML. Database caching enables the application to be notified when the underlying database changes, eliminating unnecessary queries.

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.

Extensible Architecture

ASP.NET can grow and change with your needs.

Provider Model The application services of ASP.NET use the provider model to connect to external services for storage and more. This enables the developer to change the database or other service used to store the membership, personalization or site map information by editing the configuration.

Web Parts Easily build portal applications with the built-in Web part functionality. Create Web applications that enable end users to organize and personalize their page content and layout.

Creating Professional Reports using Crystal Report for Visual Studio.NET (Part 2)

In the previous part we saw how the Report Expert wizard just dumps all the items of the report into the predefined sections in an order irrespective of the exact requirement. Now, it is up to us to move these items around and place it in a particular order to get the desired result. This is the tricky part of the whole report creation.

The next obvious step would seem like moving the items to its appropriate Group, but this is where the trouble starts. If you do this and view the report in a browser, you would notice all the Group Names being repeated. As you drill down each time, you would notice an extra row at the bottom of the report which is the parent Group Name, and you get to see all the details together, only after you have drilled down to the last section.

Before we proceed, it would help to get an understanding of the various sections that have been created and where each of the items has been placed. The current layout is as shown below:

To get the desired result, the trick here is to use a higher level Header section to display the headings and a lower level Footer section to display the actual values. For example, placing the headings related to the Store in the Report Header and placing its corresponding values in the Group 1 Footer will generate a report which will display the Store heading only once but will repeat the corresponding values depending on the number of records in the database.

We will also have to move the Book details and Order details to the lower sections and format it in such a way that it can be viewed only by drilling down to it.

Your Crystal Report Designer screen should now look similar to the image shown below. Incase, your report contains an extra Group 1 Header called ‘Group Header #1 b’, you could leave it alone as it would not make any difference to the final report.

The following tips explains how-to achieve certain tasks while converting from the old layout to the new layout.

- Insert a new chart into the Group 1 Header to represent the Book Titles.

Right click within the Group 1 Header area, select Insert and then select Chart.

From the pop-up window, select Bar graph from the Type tab, and set the Placement option on the Data tab to For each Stores.stor_name so that the chart is plotted depending on the Store.

- Hide Group 1 Header initially, to allow viewing the details only thru Drill down

Right click on the Group 1 Header Panel, select Hide (Drill Down OK)

- Suppress the Group 2 Header

Right click on the Group 2 Header Panel and select Suppress (No Drill Down).

Those sections that are either suppressed (permanently) or hidden (for drill down) will be shaded and hence can be distinguished from the normal sections easily.Â (It would have really helped if there was also a way to differentiate between the suppressed and hidden section, which does not seem to exist)

- Insert a new Detail Section (Details b).

Right click on the Detail Section and select Insert Section Below

- Insert an additional heading (for Quantity)

Right click within the particular Group, and select Text Object, place it at the required position, double click on it and type in a title.

- Create a formula

You must have noticed that the Publication Date and the Order Date displays the Date & Time currently and it needs to be formatted for date only. This can be achieved using a formula field.

Right click on the Formula Fields option under the Field Explorer window, and select New.

Give your formula a name and this opens up the Formula Editor window where you can type in the actual date conversion formula.Â Note that the image shows the code required to extract the date alone from the PublicationDate Field.

Now replace the original PublicationDate field on the report with this formula field.

Now view the report in a browser and you will notice that the report is getting real close to what we saw initially (at least functionality wise).

One thing that still hasn’t been fixed is that, each time you drill down into the details, one dangling parent record gets displayed at the bottom of the report. This is where conditional formatting comes to the rescue, which will be covered in the last part of this Walkthrough.

1.How many languages .NET is supporting now?

Ans :When .NET was introduced it came with several languages. VB.NET, C#, COBOL and Perl, etc. The site DotNet Languages. Net says 44 languages are supported..

2.How is .NET able to support multiple languages?

Ans :A language should comply with the Common Language Runtime standard to become a .NET language. In .NET, code is compiled to Microsoft Intermediate Language (MSIL for short). This is called as Managed Code. This Managed code is run in .NET environment. So after compilation to this IL the language is not a barrier. A code can call or use a function written in another language..

3.How ASP .NET different from ASP?

Ans :Scripting is separated from the HTML, Code is compiled as a DLL, and these DLLs can be executed on the server.

4.What is smart navigation?

Ans :The cursor position is maintained when the page gets refreshed due to the server side validation and the page gets refreshed.

5.What is view state?

Ans :The web is stateless. But in ASP.NET, the state of a page is maintained in the in the page itself automatically. How? The values are encrypted and saved in hidden controls. this is done automatically by the ASP.NET. This can be switched off / on for a single control.

6.How do you validate the controls in an ASP .NET page?

Ans :Using special validation controls that are meant for this. We have Range Validator, Email Validator.

7.Can the validation be done in the server side? Or this can be done only in the Client side?

Ans :Client side is done by default. Server side validation is also possible. We can switch off the client side and server side can be done..

8.How to manage pagination in a page?

Ans :Using pagination option in DataGrid control. We have to set the number of records for a page, then it takes care of pagination by itself..

9.What is ADO .NET and what is difference between ADO and ADO.NET?

Ans :ADO.NET is stateless mechanism. I can treat the ADO.Net as a separate in-memory database where in I can use relationships between the tables and select insert and updates to the database. I can update the actual database as a batch..

10.Describe the role of inetinfo.exe, aspnet_isapi.dll andaspnet_wp.exe
in the page loading process.

Ans :inetinfo.exe is theMicrosoft IIS server running, handling ASP.NET requests among other things.When an ASP.NET request is received (usually a file with .aspx extension),the ISAPI filter aspnet_isapi.dll takes care of it by passing the request tothe actual worker process aspnet_wp.exe..

11.What?s the difference between Response.Write() and Response.Output.Write()?

Ans :The latter one allows you to write formatted output..

12.What methods are fired during the page load?

Ans :Init() – when the pageis instantiated, Load() – when the page is loaded into server memory,PreRender() – the brief moment before the page is displayed to the user asHTML, Unload() – when page finishes loading..

13.Where does the Web page belong in the .NET Framework class hierarchy?
Ans :System.Web.UI.Page .

14.Where do you store the information about the user?s locale?

Ans :System.Web.UI.Page.Culture .

15.What?s the difference between Codebehind=”MyCode.aspx.cs” andsrc=”MyCode.aspx.cs”?

Ans :CodeBehind is relevant to Visual Studio.NET only.

16.What?s a bubbled event?

Ans :When you have a complex control, like DataGrid, writing an event processing routine for each object (cell, button, row, etc.) is quite tedious. The controls can bubble up their eventhandlers, allowing the main Data Grid event handler to take care of its constituents..

17.Suppose you want a certain ASP.NET function executed on MouseOver over a
certain button. Where do you add an event handler?

Ans :It?s the Attributesproperty, the Add function inside that property. So
btnSubmit.Attributes.Add(“onMouseOver”,”someClientCode();”) A simple?Javascript:ClientCode();? in the button control of the .aspx page will attach the handler (javascript function)to the onmouseover event..

18.What data type does the RangeValidator control support?

Ans :Integer,String and Date..

19.Where would you use an iHTTPModule, and what are the limitations of any
approach you might take in implementing one?

Ans :One of ASP.NET?s most useful features is the extensibility of the HTTP pipeline, the path that data takes between client and server. You can use them to extend your ASP.NET applications by adding pre- and post-processing to each HTTP request coming into your application. For example, if you wanted custom authentication facilities for your application, the best technique would be to intercept the request when it comes in and process the request in a custom HTTP module..

20.Explain what a diffgram is and a good use for one?

Ans :A DiffGram is an XML format that is used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents, and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the Original and Current row versions, row error information, and row order..

What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other?
Server.Transfer transfers page processing from one page directly to the next page without making a round-trip back to the client’s browser. This provides a faster response with a little less overhead on the server. Server.Transfer does not update the clients url history list or current url. Response.Redirect is used to redirect the user’s browser to another page or site. This performas a trip back to the client where the client’s browser is redirected to the new page. The user’s browser history list is updated to reflect the new address.

Can you explain the difference between an ADO.NET Dataset and an ADO Recordset?
Valid answers are:
· A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
· A DataSet is designed to work without any continuing connection to the original data source.
· Data in a DataSet is bulk-loaded, rather than being loaded on demand.
· There’s no concept of cursor types in a DataSet.
· DataSets have no current record pointer You can use For Each loops to move through the data.
· You can store many edits in a DataSet, and write them to the original data source in a single operation.
· Though the Dataset is universal, other objects in ADO.NET come in different versions for different data sources.

What is the Global.asax used for?
The Global.asax (including the Global.asax.cs file) is used to implement application and session level events.

What are the Application_Start and Session_Start subroutines used for?
This is where you can set the specific variables for the Application and Session objects.

Can you explain what inheritance is and an example of when you might use it?
When you want to inherit (use the functionality of) another class. Example: With a base class named Employee, a Manager class could be derived from the Employee base class.

Whats an assembly?
Assemblies are the building blocks of the .NET framework. Overview of assemblies from MSDN

Describe the difference between inline and code behind.
Inline code written along side the html in a page. Code-behind is code written in a separate file and referenced by the .aspx page.

Explain what a diffgram is, and a good use for one?
The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. A good use is reading database data to an XML file to be sent to a Web Service.

Whats MSIL, and why should my developers need an appreciation of it if at all?
MSIL is the Microsoft Intermediate Language. All .NET compatible languages will get converted to MSIL. MSIL also allows the .NET Framework to JIT compile the assembly on the installed computer.

Which method do you invoke on the DataAdapter control to load your generated dataset with data?
The Fill() method.

Can you edit data in the Repeater control?
No, it just reads the information from its data source.

Which template must you provide, in order to display data in a Repeater control?
ItemTemplate.

How can you provide an alternating color scheme in a Repeater control?
Use the AlternatingItemTemplate.

What property must you set, and what method must you call in your code, in order to bind the data from a data source to the Repeater control?
You must set the DataSource property and call the DataBind method.

What base class do all Web Forms inherit from?
The Page class.

Name two properties common in every validation control?
ControlToValidate property and Text property.

Which property on a Combo Box do you set with a column name, prior to setting the DataSource, to display data in the combo box?
DataTextField property.

Which control would you use if you needed to make sure the values in two different controls matched?
CompareValidator control.

How many classes can a single .NET DLL contain?
It can contain many classes.

Tricks:

This is a BoF session where attendees and presenters (from Canada, yay) share neat little things (some known, some obscure) about working in VS.NET.
Creating a class that implements an interface, pressing Tab after the interface name will create stubs for the methods of the interface
Ctrl+] jumps to the next end block.
Ctrl+Shift+] selects the block.
Ctrl+Shift+I enters incremental search.
Ctrl+Space (sometimes Ctrl+J, depending on the language) enters IntelliSense.
In command window you can create aliases (alias command) to access all menu entries
In command window, immed enters immediate mode.
Change Dockable from the context menu of the Output window to put it into the source panes.
Select New Horizontal/Vertical Tab to split the source window to view two or more files at once.
Ctrl+Tab navigates through open source windows (Ctrl+Shift+Tab navigates backwards)
Clicking Mark All in the Find dialog will create a bookmark for each entry found.
Alt+Drag selects arbitrary blocks of text.
Entering a TODO comment (// TODO: do something) will insert the comment in the task list. You can customize the list of tags that will go in the task list from the Options dialog.
Alt+Shift+Enter toggles the full screen code window.
Toggling Search Hidden Text in the Find dialog will search into the collapsed regions.
In C#, from the Class View, right click on a class and select Add > Property to get a wizard to automatically build a property structure.
When removing a file from a solution, remember to take it out also from the source code manager (Visual SourceSafe or CVS) to avoid having it compiled and built nightly.
Don’t use MDI
Lots of built-in aliases for the Command Window.
In Solution Explorer, right click the solution, select Properties and toggle Multiple Startup Projects to start both the client and the server (if you have them) applications.
Shift-F7 or Ctrl+PgUp/Ctrl+PgDn toggles between views on your file (Design, Source, HTML, Data, etc…)

.NET: Retrieving Assembly Attributes

To retrieve the value set in the Assembly Attributes in the AssemblyInfo.cs or AssemblyInfo.vb file, use the code below. The example shows retrieving the AssemblyDescription attribute.

[C#]
private static readonly string strAssemblyDescription =
((AssemblyDescriptionAttribute)
Assembly.GetExecutingAssembly().GetCustomAttributes(
typeof(AssemblyDescriptionAttribute), false)[0]).Description;

[Visual Basic]
Private ReadOnly strAssemblyDescription As String = _
CType([Assembly].GetExecutingAssembly().GetCustomAttributes( _
GetType(AssemblyDescriptionAttribute), False), _
AssemblyDescriptionAttribute())(0).Description

.NET: Getting the Path of the Executing Assembly

To retrieve the path of the executing assembly (the VB 6.0 equivalent of App.Path), use the following code:

[C#]
String strPath = System.IO.Path.GetDirectoryName(
System.Reflection.Assembly.GetExecutingAssembly().CodeBase);

[Visual Basic]
Dim strPath As String = System.IO.Path.GetDirectoryName( _
System.Reflection.Assembly.GetExecutingAssembly().CodeBase)

.NET: Setting the RootFolder to Other Values in the FolderBrowserDialog in .NET

The Browser Folder Dialog in ASP.NET 1.1 allows you to select a folder. There is a property, RootFolder, that allows you to select what folder is the root in the dialog. The RootFolder property can be set to any of the values in the System.Environment.SpecialFolder enum in the .NET Framework. However, there may be settings that are not in that enumeration that you may want to use. For example, you may want to set the root to be My Network Places.

It turns out that the Browse Folder Dialog is a cover for a Windows API call. The Windows API call takes a parameter that is a constant that indicates what the root should be. For some reason the allowed constants in Windows were not fully implemented in the System.Environment.SpecialFolder enum (for no good reason that I can figure out).

You can’t just set the RootFolder property to the values in the constants provided by Windows. Instead you will have to pull a trick using Reflection to modify the internally stored number in a private variable inside the dialog.

Include the following class in your project:

[c#]

using System;
using System.Reflection;

public sealed class FolderBrowserDialogEx
{
[Flags()]
public enum CsIdl
{
Desktop = 0×0000, // Desktop
Internet = 0×0001, // Internet Explorer (icon on desktop)
Programs = 0×0002, // Start Menu\Programs
Controls = 0×0003, // My Computer\Control Panel
Printers = 0×0004, // My Computer\Printers
Personal = 0×0005, // My Documents
Favorites = 0×0006, // user name\Favorites
Startup = 0×0007, // Start Menu\Programs\Startup
Recent = 0×0008, // user name\Recent
SendTo = 0×0009, // user name\SendTo
BitBucket = 0×000a, // desktop\Recycle Bin
StartMenu = 0×000b, // user name\Start Menu
MyDocuments = 0×000c, // logical “My Documents” desktop icon
MyMusic = 0×000d, // “My Music” folder
MyVideo = 0×000e, // “My Videos” folder
DesktopDirectory = 0×0010, // user name\Desktop
Drives = 0×0011, // My Computer
Network = 0×0012, // Network Neighborhood (My Network Places)
Nethood = 0×0013, // user name\nethood
Fonts = 0×0014, // windows\fonts
Templates = 0×0015,
CommonStartMenu = 0×0016, // All Users\Start Menu
CommonPrograms = 0×0017, // All Users\Start Menu\Programs
CommonStartup = 0×0018, // All Users\Startup
CommonDesktopDirectory = 0×0019, // All Users\Desktop
AppData = 0×001a, // user name\Application Data
PrintHood = 0×001b, // user name\PrintHood
LocalAppData = 0×001c, // user name\Local Settings\Applicaiton Data (non roaming)
AltStartup = 0×001d, // non localized startup
CommonAltStartup = 0×001e, // non localized common startup
CommonFavorites = 0×001f,
InternetCache = 0×0020,
Cookies = 0×0021,
History = 0×0022,
CommonAppdata = 0×0023, // All Users\Application Data
Windows = 0×0024, // GetWindowsDirectory()
System = 0×0025, // GetSystemDirectory()
ProgramFiles = 0×0026, // C:\Program Files
MyPictures = 0×0027, // C:\Program Files\My Pictures
Profile = 0×0028, // USERPROFILE
SystemX86 = 0×0029, // x86 system directory on RISC
ProgramFilesX86 = 0×002a, // x86 C:\Program Files on RISC
ProgramFilesCommon = 0×002b, // C:\Program Files\Common
ProgramFilesCommonx86 = 0×002c, // x86 Program Files\Common on RISC
CommonTemplates = 0×002d, // All Users\Templates
CommonDocuments = 0×002e, // All Users\Documents
CommonAdminTools = 0×002f, // All Users\Start Menu\Programs\Administrative Tools
AdminTools = 0×0030, // user name\Start Menu\Programs\Administrative Tools
Connections = 0×0031, // Network and Dial-up Connections
CommonMusic = 0×0035, // All Users\My Music
CommonPictures = 0×0036, // All Users\My Pictures
CommonVideo = 0×0037, // All Users\My Video
Resources = 0×0038, // Resource Direcotry
ResourcesLocalized = 0×0039, // Localized Resource Direcotry
CommonOemLinks = 0×003a, // Links to All Users OEM specific apps
CdBurnArea = 0×003b, // USERPROFILE\Local Settings\Application Data\Microsoft\CD Burning
ComputersNearMe = 0×003d, // Computers Near Me (computered from Workgroup membership)
FlagCreate = 0×8000, // combine with CSIDL_ value to force folder creation in SHGetFolderPath()
FlagDontVerify = 0×4000, // combine with CSIDL_ value to return an unverified folder path
FlagNoAlias = 0×1000, // combine with CSIDL_ value to insure non-alias versions of the pidl
FlagPerUserInit = 0×0800, // combine with CSIDL_ value to indicate per-user init (eg. upgrade)
FlagMask = 0xFF00, // mask for all possible flag values
}

private FolderBrowserDialogEx()
{
}

public static void SetRootFolder(System.Windows.Forms.FolderBrowserDialog fbd, CsIdl csidl)
{
Type t = fbd.GetType();
FieldInfo fi = t.GetField(“rootFolder”, BindingFlags.Instance | BindingFlags.NonPublic);
fi.SetValue(fbd, (System.Environment.SpecialFolder) csidl);
}
}

To set the FolderRoot property, call it like this:

[c#]

FolderBrowserDialogEx.SetRootFolder(fbd, FolderBrowserDialogEx.CsIdl.Network);

Where fbd is the name of the FolderBrowserDialog on your form, and the second argument is what you want to use as the root of the dialog.

.NET: Sizing Columns in a ListView Control in .NET

In a ListView control, with the View property set to Details, you can create a multi-column output. Sometimes you will want the last column of the ListView to size itself to take up all remaining space. You can do this by setting the column width to the magic value -2.

In the following example, the name of the ListView control is lvSample:

[c#]
private void Form1_Load(object sender, System.EventArgs e)
{
SizeLastColumn(lvSample);
}

private void listView1_Resize(object sender, System.EventArgs e)
{
SizeLastColumn((ListView) sender);
}

private void SizeLastColumn(ListView lv)
{
lv.Columns[lv.Columns.Count - 1].Width = -2;
}

[vb]
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
SizeLastColumn(lvSample)
End Sub

Private Sub ListView1_Resize(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles lvSample.Resize
SizeLastColumn(CType(sender, ListView))
End Sub

Private Sub SizeLastColumn(ByVal lv As ListView)
lv.Columns(lv.Columns.Count – 1).Width = -2
End Sub

You can also size a column to its contents. This is especially useful after adding an item to the ListView. For example:

[c#]
lvSample.Items.Add(“foo”);
lvSample.Columns[0].Width = -1;

[vb]
lvSample.Items.Add(“foo”)
lvSample.Columns(0).Width = -1

If the column width is set to -1, the column is sized to the width of the widest item in it. If the value is set to -2, it has a minimum width of the size of the column header, except for the last column, which takes up all remaining space.

.NET: Using Remoting in .NET
While Web Services are getting all the attention in .NET, there are other techniques that it supports that are more efficient for communicating between processes. The advantage of a Web Service is that it is a platform neutral mechanism that uses SOAP as the transport mechanism. However, if you are in control of both sides of the communication between processes, it is more efficient to use Remoting. As an example, I use Remoting as the mechanism for having an ASP.NET page talk to a Windows Service.

The basic idea is that you register a TCP channel on one side of the communications, then register a class as a well-known service type. From the other side you can instantiate the class and do things to it. The two processes can be running on the same machine, or they could be on different machines that have tcp access to each other.

So if you have one process running, it can run this code to register MyClass on tcp port 8040. The port is just an arbitrary high number that you use on both sides of the conversation:

chan = new TcpChannel(8040);
ChannelServices.RegisterChannel(chan);
RemotingConfiguration.RegisterWellKnownServiceType(
typeof(MyNameSpace.MyClass), “MyClass”, WellKnownObjectMode.Singleton);

You will also need a class, in this case called MyClass. This class will need to inherit MarshalByRefObject.

using System;

namespace MyNameSpace
{
public class MyClass : MarshalByRefObject
{
public String Check()
{
return “Success”;
}
}
}

On the other side, when you want to call the class, you execute this code:

MyClass mc = (MyClass) Activator.GetObject(typeof(MyClass), “tcp://localhost:8040/MyClass”);
System.Windows.Forms.MessageBox.Show(mc.Check())

You will need to replace localhost with the domain name or IP address of the machine that you are connecting to if it is not the current machine. If going through a firewall, that port will have to be open. (Web Services go through port 80 by default, so it usually doesn’t require any firewall configuration).

Also, if any objects are passed back from the call, they need to have the serializable attribute on them. For example, if you had a collection class called Units, you could pass an object created from this class back from a method by declaring the class like this:

[Serializable] public class Units : CollectionBase

ASP.NET: Constructing a Graphic on the Fly in ASP.NET
I needed to construct a PNG file on the fly, so that its content was constructed at the moment it was downloaded. This technique will also work with GIF and JPEG files. This proved easier than I expected in .NET. The graphic is constructed entirely virtually, and is never saved to disk. In fact, there is no file with the graphic name at all.

Step 1, You need to tell IIS that the expected file type needs to be processed by the .NET framework. This has to occur in the IIS Manager dialogs. You may need to get your ISP to perform this task for you.
Right click on the web site in IIS manager and select Properties.
Click on the Home Directory tab.
Click on Configuration button. In the Application Configuration dialog, you are going to set the mapping for .PNG files (or GIF or JPEG) to run through the .NET processing. The easiest way is to…
Select the line in the dialog for an existing extension that is mapped to aspnet_isapi.dll, such as .config and click Edit.
Select and Copy (using Ctrl+C) the path in the Executable textbox from this entry and select Cancel.
Press the Add button.
Paste (using Ctrl+V) the path that you copied.
In the extension textbox add your extension, such as .png.
In the Verbs section, select the Limit To option and put GET, HEAD into the textbox.
Press OK and close all the dialogs

Step 2, Next you have to tell IIS that it should call a piece of code when a file is requested. In the web.config file for the web site, add the following section as a child of <system.web>:

This tells .NET that when mygraphic.png is requested that it should instantiate the MyNameSpace.MyImage class.

Step 3, Create the MyImage class. The MyImage class is responsible for drawing the graphic. Add the following class to the web site:

using System;
using System.Collections;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Web;

namespace MyNameSpace
{
public class MyImage: IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
Bitmap bmp;
Graphics gph;
Brush brushFill;
System.IO.MemoryStream ms;

context.Response.BufferOutput = true;
context.Response.Clear();
context.Response.ClearHeaders();
context.Response.ClearContent();
context.Response.ContentType = “image/png”;
context.Response.Expires = 0;
context.Response.Cache.SetCacheability(HttpCacheability.NoCache);
context.Response.Cache.SetNoServerCaching();
context.Response.Cache.SetNoStore();
context.Response.Cache.SetMaxAge(System.TimeSpan.Zero);

bmp = new Bitmap(100, 100, PixelFormat.Format24bppRgb);
gph = Graphics.FromImage(bmp);
brushFill = new SolidBrush(Color.Blue);
gph.FillRectangle(brushFill, 1, 1, 50, 50);
ms = new System.IO.MemoryStream();
bmp.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
Byte[] abyte = ms.ToArray();
context.Response.OutputStream.Write(abyte, 0, abyte.Length);
context.Response.End();
bmp.Dispose();
gph.Dispose();
}

public Boolean IsReusable
{
//To enable pooling, return true here.
//This keeps the handler in memory.
get
{
return false;
}
}
}
}

Step 4, Create a web page that uses the graphic. Put the following line in the web page:

When the page is rendered, you will have a 100 x 100 pixel black square with a 50 x 50 pixel blue square in the upper left hand corner. You will need to study the GDI+ features of the .NET framework to modify the drawing. But that’s beyond the scope of what I wanted to cover here.

ASP.NET: Controlling Caching in ASP.NET Web Forms

ASP.NET allows you to cache pages. The means that when the code behind the page runs, it produces HTML, the HTML is sent down to the client, however a copy is stored in the memory of the web server. If the same page is requested again, then the page is retrieved from the cache and the code is not rerun. You have virtually infinite flexibility on controlling when the cache gets flushed.

The most basic caching is implemented by placing this line at the top of your ASPX page:

<%@ OutputCache Duration=”3600″ VaryByParam=”none”%>

What this tells the ASP.NET caching code is to cache the page for one hour.

The full spec for the @Output Cache lines is:

<%@ OutputCache Duration=”#ofseconds” Location=”Any � Client � Downstream �
Server � None” VaryByControl=”controlname” VaryByCustom=”browser �
customstring” VaryByHeader=”headers” VaryByParam=”parametername” %>

Duration is a count in seconds to cache.

Location allows the caching to occur on the server, on the client, on a proxy server in between. The default is Any. If you always want server caching (which seems to me to be the most useful choice), change the line to read:

<%@ OutputCache Duration=”3600″ Location=”Server” VaryByParam=”none”%>

VaryByControl is only used by user controls, not on standard web pages. See the .NET documentation for more details.

VaryByCustom=”browser” keeps a different copy of the output for each browser name and major version information. So if you have cloaking by browser version going on (which is easy to implement in .NET), then each separate page will get delivered.

VaryByCustom=”customstring” Allows you to specify a string that will be passed to your code. To make this useful, you must then override the GetVaryByCustomString method in the Global.asax file. For example, place this line in your ASPX file:

<%@ OutputCache Duration=”3600″ Location=”Server” VaryByCustom=”Referer” VaryByParam=”none”%>

Then in your Global.asax file add the following code:

public override String GetVaryByCustomString(System.Web.HttpContext hcContext, String strCustom)
{
switch (strCustom)
{
case “Referer”:
Uri uriReferrer = hcContext.Request.UrlReferrer;
String strRet;
if (uriReferrer != null)
strRet = uriReferrer.Host;
else
strRet = null;
return strRet;
default:
return base.GetVaryByCustomString(hcContext, strCustom);
}
}

VaryByHeader allows you to cache based off of some field in the HTTP header sent by the client. The classic example is based off the Accept-Language header line.

VaryByParam allows you to cache different versions based off of querystring or post field parameters. So http://www.domain.com/foo.aspx?bar=baz would be cached separately from http://www.domain.com/foo.aspx?bar=bletch

There are also ways of controlling the caching through code.

ASP.NET: How to use the FrontPage Server Extensions with ASP.NET

To use the FrontPage Server Extensions (fpse) 2000 or 2002 with ASP.NET on a NTFS file system can cause some problems. The problem occurs when you use the Check Server Health functionality. This feature modifies the Access Control Lists (ACL) on the files so that the ASP.NET worker process (aspnet_wp) user, ASPNET, no longer has access to the files.

The solution is a minor trick. In the FrontPage Server Extensions, add the named ASPNET as a user of the web site. Grant ASPNET “Browser” permission to the web site. Then re-check the server health, adjusting the file permissions. The FrontPage server extensions will then add read permission for ASPNET to each file on the site.

.NET: Getting and Setting the Application Version Number
In the AssemblyInfo.cs file for each project, you have the opportunity to set the version number of the Assembly. A version number is designed by four number: major.minor.build.revision. The default is 1.0.* If left to this, third number (build) will be set to the number of days since January 1, 2000 local time. The fourth number (revision) will be set to the number of seconds since midnight local time. This is useful since it will always increment every time you build the assembly, unless you are working at 2 a.m. on the last Sunday in October in most places in the United States.

However, in a production environment, you may want to control those numbers manually. In which case, you could set the version to something like 1.3.25.31.

Note that .NET considers changes to the major or minor version to be significant changes, but changes to the build and revision are not. So if you have a reference to an Assembly in the Global Assembly Cache and the major or minor version changes, the new version is considered incompatible with the old version and the previous version will still be linked to.

To retrieve the version number from the assembly in your code, you use can do this:

String strVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();

To retrieve the version number from the assembly that is calling your assembly, you can use this:

String strVersion = System.Reflection.Assembly.GetCallingAssembly().GetName().Version.ToStrin

Classes, Objects, and Structures in .NET

Summary

The following article kicks off a three-part article series that will present definitions and samples for different Object-Oriented Programming concepts and its implementation in .NET. The first part will examine the concepts of classes, objects, and structures. The second part will examine the concepts of inheritance, abstraction, and polimorphism. The third and last part will examine the concepts of interface, multiple interface inheritance, collections, and overloading.
Contents
Introduction
Class
Object
Structures
Conclusion
Reference
Introduction

Object-Oriented Programming (OOP) is a software development paradigm that suggests developers to split a program in building blocks known as objects. The OOP paradigm allows developers to define the object’s data, functions, and its relationship with other objects.

Microsoft created the .NET Framework using OOP, and knowing this concepts has helped me to understand the .NET Framework and to design and develop better software components. The purpose of this article is to describe the basic OOP concepts using real world scenarios and to provide some code samples that demonstrate how to work with OOP and .NET.
Class

The most common definition states that a class is a template for an object. Suppose that someone builds a paper pattern for a shirt. All the shirts done with the same paper pattern will be identical (same design, size, etc.). In this sample, the paper pattern is the class and the shirt is the object. To build the same exact shirt over and over, you need the paper pattern as a template. Another great example are house plans and blueprints. The plans and blueprints define the number of rooms, the size of the kitchen, the number of floors, and more. In this real world sample, the house plans and blueprints are the class and the house is the object. In OOP you program a class as a template for a specific object or groups ob objects that will always have the same features.
Class members

A class has different members, and developers in Microsoft suggest to program them in the following order:
Namespace: The namespace is a keyword that defines a distinctive name or last name for the class. A namespace categorizes and organizes the library (assembly) where the class belongs and avoids collisions with classes that share the same name.
Class declaration: Line of code where the class name and type are defined.
Fields: Set of variables declared in a class block.
Constants: Set of constants declared in a class block.
Constructors: A method or group of methods that contains code to initialize the class.
Properties: The set of descriptive data of an object.
Events: Program responses that get fired after a user or application action.
Methods: Set of functions of the class.
Destructor: A method that is called when the class is destroyed. In managed code, the Garbage Collector is in charge of destroying objects; however, in some cases developers need to take extra actions when objects are being released, such as freeing handles or deallocating unmanaged objects. In .NET, there is no concept of deterministic destructors. The Garbage Collector will call the Finalize() method at a non-deterministic time while reclaiming memory for the application.
Access keywords

Access keywords define the access to class members from the same class and from other classes. The most common access keywords are:
Public: Allows access to the class member from any other class.
Private: Allows access to the class member only in the same class.
Protected: Allows access to the class member only within the same class and from inherited classes.
Internal: Allows access to the class member only in the same assembly.
Protected internal: Allows access to the class member only within the same class, from inherited classes, and other classes in the same assembly.
Static: Indicates that the member can be called without first instantiating the class.

The following sample code illustrates a sample class in C#:
/// C#
///Imported namespaces
using System;

/// Namespace: Consider using CompanyName.Product.ComponentType
namespace DotNetTreats.OOSE.OOP_CSharp {

///Class declaration
public class employee {

///Fields
private string _name;
private int _salary;

///Constants
private const int anualBonus = 1000;

///Constructor
public employee(){
}

///Properties
public string Name {
get {
return _name;
}
set {
_name = value;
}
}
public int Salary {
get {
return _salary;
}
set {
_salary = value;
}
}

/// Event handlers
public event EventHandler OnPromotion {
add {
}
remove {
}
}

/// Methods
public void DuplicateSalary() {
_salary = _salary*2;
}

}
}

Listing 1. Sample class implementation in C#

The following sample code illustrates a sample class in VB.NET:
‘ VB.NET
‘Imported namespaces
Imports System

‘ Namespace: Consider using CompanyName.Product.ComponentType
Namespace DotNetTreats.OOSE.OOP_VBNET

‘Class declaration
Public Class employee

‘Fields
Private _name As String
Private _salary As Integer

‘Constants
Private Const anualBonus As Integer = 1000

‘Constructors
Public Sub New()
MyBase.New
End Sub

‘Properties
Public Property Name As String
Get
Return _name
End Get
Set
_name = value
End Set
End Property

Public Property Salary As Integer
Get
Return _salary
End Get
Set
_salary = value
End Set
End Property

‘ Event handlers
Public Event OnPromotion As EventHandler

‘Methods
Public Sub DuplicateSalary()
_salary = (_salary * 2)
End Sub

End Class

End Namespace

Listing 2. Sample class implementation in VB.NET

Object

Objects are the building blocks of OOP and are commonly defined as variables or data structures that encapsulate behavior and data in a programmed unit. Objects are items that can be individually created, manipulated, and represent real world things in an abstract way.
Object composition

Every object is composed by:
Object identity: Means that every object is unique and can be differentiated from other objects. Each time and object is created (instantiated) the object identity is defined.
Object behavior: What the object can do. In OOP, methods work as functions that define the set of actions that the object can do.
Object state: The data stored within the object at any given moment. In OOP, fields, constants, and properties define the state of an object.
Structures

Not everything in the real world should be represented as a class. Structures are suitable to represent lightweight objects. Structures can have methods and properties and are useful for defining types that act as user-defined primitives, but contain arbitrary composite fields. The .NET Framework defines some structures such as System.Drawing.Rectangle, System.Drawing.Point, and System.Drawing.Color.

The following code sample represents a structures in C#:
/// C#
struct Point {
private int _x;
private int _y;

Point(int x, int y){
this._x = x;
this._y = y;
}

public int X {
get {
return _x;
}
set {
_x = value;
}
}

public int Y {
get {
return _y;
}
set {
_y = value;
}
}
}

Listing 3. Sample structure implementation in C#

The following code sample represents a structure in VB.NET:
‘ VB.NET
Structure Point
Private _x As Integer
Private _y As Integer

Sub New(ByVal x As Integer, ByVal y As Integer)
MyBase.New
Me._x = x
Me._y = y
End Sub

Public Property X As Integer
Get
Return _x
End Get
Set
_x = value
End Set
End Property

Public Property Y As Integer
Get
Return _y
End Get
Set
_y = value
End Set
End Property
End Structure

Listing 4. Sample structure implementation in VB.NET

Conclusion

OOP is full of abstract concepts, and the best approach to understand them is practical and not only theoretical. I learned more OOP after making some designs and after implementing some components. The concepts presented in this article might clarify the meaning, but I strongly recommend to go and have fun playing around with OOP. In this article, I examined the concept of classes, objects, and structs. The second part will examine the concepts of inheritance, abstraction, and polimorphism.

The release of ADO.NET 2.0, as part of the Visual Studio 2005 launch and the refresh of the .NET Framework and Common Language Runtime, passed relatively unnoticed. That’s understandable. With so much going on in the Microsoft tools domain, with the new Visual Studio Team System, SQL Server 2005, and previews of Office 12 and Windows Vista, that some little database connectivity stuff could easily be overlooked.

Well, look closer. ADO.NET 2.0 is a significant revamping of the connectivity infrastructure within .NET and the CLR, and has special benefits for SQL Server 2005 developers. If you’re already been working with the new classes and methods, kudos to you. For the rest of you, it’s time to see what the updated relational data access system can do for you. Specifically, we’re going to talk about a few of my favorite enhancements, including asynchronous data access, metadata schemas, query notification, multiple active result sets and some new data types. And even with all this, we’re only scratching the surface of what ADO.NET 2.0 offers.

Async Data Access
This is a biggie, folks. With the first versions of ADO.NET, numbered 1.0 and 1.1, a database connection could only process commands serially; in a series of commands to a data provider, like SqlClient, each command had to terminate before the next could begin. Sure, that’s often perfectly fine for simple transaction-processing scenarios, where you to open a connection, write a record, and then close the connection again. But what if you are planning to execute a sequence of operations that can logically be performed in parallel, such as reading a number of datasets into memory? Why not do them all at once?

When using the SqlClient data provider under ADO.NET 2.0, you can now perform true non-blocked async I/O with the SQL Server database. This is enabled by initializing the connection using the phrase async=true in the connection string, and then by taking advantage of new methods in the data provider that can express that you want to perform operations asynchronously.

For example, where you used to have the synchronous ExecuteReader method, you now have two new methods, BeginExecuteReader and EndExecuteReader. Similarly, there’s now a BeginExecuteNonQuery / EndExecuteNonQuery pair, and a BeginExecuteXmlReader / EndExecuteXmlReader pair. The Begin… methods take all the input parameters; the End… methods take all the output parameters and provide the return value. Neat, huh? Note that these async commands work against SQL Server 7.0, SQL Server 2000 and SQL Server 2005. Learn more from “Asynchronous Command Execution in ADO.NET 2.0.”

Metadata Schemas
When you use Visual Studio 2005 to configuring ADO.NET 2.0 to talk to a live database within your application, the IDE can prepopulate many of the database access parameters. Why? Because ASP.NET 2.0 and its data providers can access metadata about that database, which the IDE can take advantage of to help you with drag-and-drop functionality as you link the database to your app. The result is that Visual Studio 2005 is more helpful, when it comes to database development, than any previous version.

Okay, so it makes coding a little easier. Beyond that, who cares about database metadata? Well, you should, if you want your own application to be as smart as Visual Studio 2005 itself. By using that metadata, your app can become more flexible, such as being able to link dynamically to databases and automatically configure itself to discover and use the appropriate tables, columns, stored procedures and triggers. Alternatively, you can use the metadata to help your users customize their interactions with databases, such as by creating reports that take advantage of tables or columns that you didn’t know about you built your application.

Where does this metadata come from? Initially, it comes from the database engine itself. Every modern relational database or data source has its own way of expressing metadata to its access clients. SQL Server 2005, for example, uses a different metadata system than SQL Server 2000, but both are understood by SqlClient. Oracle has its own metadata system, which is read the OracleClient data provider.

ADO.NET 2.0 provides a class, System.Data.Common.DbMetaDataCollectionNames class, to store the metadata, and it’s up to each data-provider writer to import that metadata from the database and populate the class. Microsoft does that with the SqlClient and OracleClient data providers included with ADO.NET 2.0, of course.

These new metadata classes work with all data sources supported by ADO.NET 2.0 data providers, not just with SQL Server 2005. Want to learn more? Check out “Schemas in ADO.NET 2.0.”

Query Notification
A lot of Web applications use external databases to store lookup tables—lists of product codes, perhaps, or branch office locations, state abbreviations, product categories. These items don’t change often; perhaps hourly, perhaps daily, perhaps annually. So, it’s reasonable to cache those items within the Web app, instead of making a new query back to SQL Server every time a browser asks for a list of states to populate a drop-down field.

The problem with caching using ADO.NET 1.0 and 1.1 was that it was difficult to know when one of those rarely changed tables had actually changed, and thus the cache should be reloaded. That meant duct-tape workarounds, such as arbitrary decisions when to reload the cache, or using triggers or other mechanisms to set a “dirty” flag, which your application would watch for somehow.

ADO.NET 2.0 and SQL Server 2005 solve this problem with their new Query Notification signaling mechanism. The ASP.NET 2.0 cache can now register a request for notification with the SQL Server 2005 database, though a new SqlNotificationRequest class contained within the SqlCommand. The SQL Server database will remember that query notification, along with the query and its rowset. If that rowset changes, SQL Server 2005 will send back a notification to the client’s SqlDependency class using the new SQL Server Service Broker. Simple and elegant. This only works with SQL Server 2005; more information is at “Query Notifications in ADO.NET 2.0.”

Multiple Active Result Sets
It seemed like a big step backwards to many Visual Basic and Visual C++ developers: The original ADO permitted you to maintain multiple active cursorless result sets within a database, but ADO.NET 1.0 / 1.1 did not. If you tried to open more than one cursorless result set, ADO.NET 1.0 / 1.1 returned an error condition. The upshot was that in ADO.NET 1.0/1.1, if you needed multiple result sets, you had to close one SqlDataReader before opening another. Annoying, and arbitrary.

When used in conjunction with SQL Server 2005, ADO.NET 2.0 solves that problem by allowing you to open multiple result sets at the same time. You can learn more about this from “Multiple Active Result Sets (MARS) In SQL Server 2005.” If you relied upon this capability in ADO, doesn’t this make your day?

User Defined, XML Data Types
Another SQL Server 2005-specific feature of ADO.NET 2.0 is support for user defined types and XML data types.

User defined types were standardized by the SQL:2003 specification published by the ISO. You can now access them by using the SqlUdt namespace in the System.Data.SqlTypes namespace; the result is a .NET Object. Similarly, there’s a new SqlXml namespace for XML data; the results are returned as a .NET String. These match up with the new XML data type within SQL Server 2005; this is a first-class data type, not just something cobbled together from strings, and you can also access XML using the new SqlCommand.Execute.XmlReader method.

For more, see “XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005.”

Classes—Wrapping Data and Behavior Together

Starting with the release of Visual Basic 4.0, the capability to create classes has been intrinsic to the Visual Basic language. Some might say that Microsoft’s move to support this was the true beginning of VB’s evolution into an object-oriented language. Whenever it started, and whatever you thought of Visual Basic’s prior ability (or inability) to support object-oriented (OO) concepts, .NET brings Visual Basic up to speed with all of the basic properties of an object-oriented programming language. The deep object support in Visual Basic .NET, and the .NET Framework in general, is certainly one of the most compelling changes offered in this new environment.

This chapter will focus on defining the concepts of object orientation as they relate to software development in general. In Chapter 4, “Introduction to the .NET Framework Class Library,” we will also examine their specific manifestations in the .NET Framework.

There have been more than a few books written on object-oriented programming, so this chapter will not attempt to deliver a full treatise on a subject well deserving of hundreds of pages. Instead, we will cover only the ground that we need to cover so that programmers new to object-oriented programming and programmers with no OO experience at all will have a good backdrop of knowledge for exploring the .NET Framework Class Library.

We’ll start by reviewing all of the pertinent characteristics of object-oriented languages—an obvious first step when you consider that the classes and other pieces of the Framework Class Library are all object-oriented in nature. Then we’ll examine how these concepts have been brought to life inside of .NET and Visual Basic .NET, hopefully arming you with a solid-enough understanding of these concepts to make your programming experiences with the Framework Class Library more productive.

In years past, many developers have debated whether Visual Basic was an object-oriented language. Instead of investigating any of these prior claims, arguments, or discussions, let’s focus instead on the here and now. Visual Basic .NET supports the major traits of an object-oriented language, including the capability to:

Wrap data and behavior together into packages called classes (this is a trait known as encapsulation)

Define classes in terms of other classes (a trait known as inheritance)

Override the behavior of a class with a substitute behavior (a trait known as polymorphism)

We’ll examine each one of these traits in detail. We’ll also examine ways in which you will see these concepts at work inside of the .NET Framework. Chapter 4 will continue this thread by specifically examining the nature of the Framework Class Library and attempting to relate these object-oriented concepts directly to the Framework Class Library.
Classes—Wrapping Data and Behavior Together

Classes are blueprints or specifications for actual objects that we will create in our code. They define a standard set of attributes and behaviors. Because classes only define a structure or intent, they are virtual in nature. For instance, a class cannot hold data, it can’t receive a message, and in fact can’t do any processing at all. This is because classes are only meant to be object factories. Just like real engineering blueprints of a building, they only exist to construct something else. When we program, this “something else” we are trying to construct is an object. An object can hold data, can receive messages, and can actually carry out processing.

While you don’t typically use the term class in your everyday (non-programming) life, we are all certainly familiar with the concept of objects. These are the things that surround us day in and day out; they are the nouns in our universe. We are used to interacting with objects. For example, you place a plate on your table for dinner. The plate has food on it—a few different types of food, in fact. We can see that all of these things have distinct properties: The plate is white with a faint flower pattern, and the food has a particular texture, taste, and smell. We also expect that objects will allow us to interact with them in different ways.

Just like in the real world, code objects (we also call them instances) are actual physical manifestations of classes.
Classes as Approximations

If we discuss classes in the context of programming, we say that they establish a template for objects by defining a common set of possible procedures and data. Procedures are used to imbue the class with a set of behaviors; when implemented in a class they are called methods. Classes maintain data inside of properties (which may or may not be visible to other classes). Behaviors are the verbs of classes, and properties are the nouns. A car, for instance, will accelerate in a prescribed fashion. This would be a behavior. A car will also have a specific weight, color, length, and so on. These are properties. From a technical, implementation point of view, there is actually no difference between the way that methods and properties are implemented. They both have function signatures, and both execute some body of code. In addition, both of them can accept parameters and return values.

Note

There are some general guidelines for when to use properties versus methods (and vice versa), but probably the best advice is to just be consistent. Most of the time, these rules will help steer you to the correct decision:

Use a method if you are going to be passing in more than a few parameters.

If you find yourself writing a method called GetXXX or SetYYY, chances are good this should be a property instead.

Methods are more appropriate than properties if there will be many object instantiations or inter-object communication inside of the function.

Properties, when implemented, should be stateless with respect to one another. In other words, a property should not require that another property be set before or after it is set. If you find this kind of dependency inside of a property, it should probably be a method instead.

Classes are typically constructed to mimic, or approximate, real-world physical structures or concepts. By using classes in your code, you can simplify both your architecture and your understanding of the code; this is due to the inherent approachability of objects—your mind is used to deal with objects. For example, which do you suppose would make more intuitive sense to you?

You are writing code to move an icon from the left side of the screen to the right side of the screen. The procedural programming way would probably have you calling some API function (maybe it’s called SystemDskRsrcBlit) and passing parameters into the function call. But, what if you were free to do this:

Create an icon object

Tell it to MoveLeft

The object-oriented way just seems to make more sense to us—it seems to appeal to the way that our minds are wired.

Note

The difference between the system that we are programming and the real-world process that we are modeling is often referred to as the semantic gap. You could summarize some of what we have been talking about here by saying that object- oriented programming aims to reduce the semantic gap between programming and the real world.

Of course, just because the basic premises of objected-oriented programming are simple to understand doesn’t mean that the actual programming of object-oriented systems is trivial. Once you can work your way through the syntax and condition yourself to think in an object-like fashion while actually designing your applications, some of the perceived complexity associated with software development will begin to fade.
Talking Between Classes

We have said that classes define a set of behaviors. These behaviors would be useless to us unless we had a way to actually stimulate or initiate a particular behavior. Therefore, we have the concept of messaging. A message is nothing more than a request, from one object to another, to perform some sort of action. The receiving object may choose to ignore the action (especially if it doesn’t have a behavior defined that would map to the requested action) or it may perform a specific action that could, in turn, send messages to other objects.

A core tenet of object-oriented systems is that classes think for themselves. A particular class knows how it should react to an incoming message; the calling class isn’t forced to understand how or why the receiving class behaves the way that it does. This is the essence of information hiding. In information hiding, an object hides its internal machinations from other objects (see Figure 3.1). Information hiding is important because it helps reduce the overall design complexity of an application. In other words, if Class A doesn’t have to implement code to understand how Class B operates, we have just reduced the complexity of the code.

As programmers, we initiate a message from one class to another by calling a method or property on the target class. Part of this message that we send encapsulates any parameters or data needed by the receiving class to execute the action.

Thus, we have classes in an object-oriented programming environment. A physical manifestation of a class in the programming world consists of code that defines these attributes and behaviors through property and method routines.

In this book, our focus on the Framework Class Library will introduce you to new classes in each chapter. They will exhibit all of the traits and characteristics of the classes that we have just defined.

Now, let’s move on and discuss the next OO trait of Visual Basic .NET—inheritance.

Inheritance—Defining Classes in Terms of One Another

Inheritance is the capability for one class to inherit or take on the traits of another class. Typically, this happens in a hierarchical fashion. Consider the following simple example to see how this inheritance results in a natural hierarchy of classes. Figure 3.2 shows three classes: HR Employee, IT Employee, and Warehouse Employee. Each of these is shown with some of their properties and methods.

By looking at them, it quickly becomes clear that we could hierarchically structure these classes by abstracting their common traits into a parent class. These three classes would then be child classes of that one parent class.

This hierarchical structure is typical of well-engineered class libraries—the Framework Class Library is organized in just such a way.

Inheritance by Natural Relationship

One of the nice things about inheritance is that it often simply realizes a relationship that we already make in our minds. That is, it is often just a recognition of real-world relationships. We can tell that a dog or a cat is a type of an animal—the inheritance between the concept of an animal class and a dog or cat class is obvious. Again, this is a good thing as it helps to reduce the semantic gap that we talked about earlier and helps you make your code organization easier to understand. Organizing your code is only one benefit of inheritance—code reuse is another.

We have said that a class can inherit the traits of another class. We have also said that the traits of a class are implemented as property and method routines. When these routines are inherited between classes, it obviously means we are assuming the actual source code of one class into another. Thus, we have code reuse.

we can see how each line of code that was written to implement the parent class methods can be reused by each of the child classes. Code reuse in this fashion becomes a powerful rapid application development enabler. If we needed to change some lines of code in one of the parent class methods, the change would be immediately realized in its children classes. This also allows us to build up complexity in a child class by inheriting from potentially simple base classes.

There are many different ways to express the inheritance relationship: parent to child, super-class to sub-class, ancestor class to descendant class, generalized class to specialized class, and so on. In this book, anytime we use these terms you should know that we are just referring back to this basic concept of inheritance relationship.

We now know that identifying logical relationships between objects will help us out in the area of code reuse. But the examples we have talked about so far have been based on relationships between objects—an appraisal of one object being a type of another object. If, however, you approach inheritance by first looking at its end result, you’ll find that you can end up with an entirely different perspective. Let’s look at an example: Let’s say that we have a class that defines operations for a specific type of printer. We’ll call this class InkJet. Intuitively, we sense a parent class that would most likely be called Printer.

Inheriting from the Printer class is a good solution for us because it already defines some basic operations (line feed, paper out, and so on) that we can use as building blocks for our InkJet class. At the same time, we will add some of our own behaviors that are specific to inkjet printers. But what if we had the requirement for some low-level communication code that would send an error signal across a parallel port? Also, what if that code was already available to us in yet another class?

This is subtly different from what we were doing before because it is very difficult to envision a logical relationship between a parallel port object and an inkjet object. After all, an inkjet printer is not a type of a parallel port—there is no obvious hierarchical relationship to draw between the two. In this case, we would be implementing inheritance to get at raw code reuse. This doesn’t do anything for us in terms of making our code easier to understand—it does not reinforce a relationship between abstract classes and real-world objects.

Inheriting for pure code reuse in the absence of a sub-type relationship is certainly something that you can do with classes, but isn’t always the best approach. You gain code reuse at the expense of increased complexity in your system (and therefore, a corresponding increase in the effort required to understand your system). In .NET, we advocate implementing an interface instead of using class inheritance to represent this relationship; you still get the desired code reuse without complicating your class relationships

Polymorphism—Overriding One Class Method with Another

The next OO trait we will discuss is polymorphism. Unlike inheritance, polymorphism is concerned with how a class presents itself to the outside world. Polymorphism roughly means “many forms,” and alludes to the fact that a specific named behavior can be implemented in different ways by different classes.

In other words, classes can reuse behavior names but implement them differently.

Overriding

One of the common examples used to demonstrate this concept involves a class library that describes geometric shapes. One of the behaviors that we would like to imbue into our shape classes is the capability to draw themselves. Using our basic knowledge of geometry, we know that each shape will require different parameters and use different operations to actually accomplish the draw operations (pi may be used when drawing circles, squares will need to know a side length, and so on). Because a procedural programming language doesn’t allow us to reuse behavior names (think methods), we would end up with a different routine for each shape type such as DrawCircle, DrawTriangle, and so on. Because we can reuse method names with polymorphism, we can simplify the programming model considerably by reusing one method called Draw; each shape class would implement this in a slightly different fashion. This is called overriding and specific manifestations of this in the Framework Class Library are discussed in Chapter 4.

Overriding further promotes the concept of information hiding that we talked about earlier: Each class knows internally how to implement its behaviors, but calling classes don’t know and don’t care. We just send a message saying, “Draw,” and the target class worries about how to carry it out. You will often see overriding with inheritance. A child class may override a parent class’s methods to implement specific functionality not relevant to the parent class.

Overloading

It represents a class, Square, and its draw methods. The implementation of the draw behavior differs based on the information that is passed into the Draw method. This is a special case of overriding called overloading. In our example here, we want to avoid implementing methods called DrawFromLength and DrawFromCoords; we simplify our class architecture by implementing just one method, Draw, and let it determine which implementation of Draw to use based on the function signature. In this way, both of the following would be valid method calls:
mySquare.Draw(10,”inches”)
mySquare.Draw(topLeftPoint, bottomRightPoint)

Polymorphism is really all about keeping interfaces between classes the same while allowing actual implementations to differ. This encourages loosely coupled object designs and hopefully clarifies system architecture and reduces complexity.

1. How to get the path for “My Documents” and other system folders?

Use the GetFolderPath method of the System.Environment class to retrieve this information.
MessageBox.Show( Environment.GetFolderPath( Environment.SpecialFolder.Personal ) );
#2. How to get the path to my running EXE?

The Application class has a static member ExecutablePath that has this information.
string appPath = Application.ExecutablePath;

Alternative: The tip below is provided by cbono2000
System.Reflection.Assembly.GetExecutingAssembly().Location
#3. How to determine which operating system is running?

Use System.Environment’s OSVersion static (shared) property.
OperatingSystem os = Environment.OSVersion;
MessageBox.Show(os.Version.ToString());
MessageBox.Show(os.Platform.ToString());
#4. How to get a file’s name from the complete path string?

Use System.IO.Path.GetFileName and System.IO.Path.GetFileNameWithoutExtension static methods.
#5. How to get a file’s extension from the complete path string?

Use System.IO.Path.GetExtension static method.
#6. What is difference beween VB.NET and C#.NET?

Hi friends, click here to find the best comparison ever between VB.NET and C#.NET .
#7. How to find whether your system has mouse or the number of buttons, whether it has wheel, or whether the mouse buttons are swapped or size of your monitor and many such information?

Download source files – 8.65 Kb
Download demo project – 4.60 Kb
Use System.Windows.Forms.SystemInformation.
SystemInformation provides static (Shared in Visual Basic) methods and properties that can be used to get information such as Windows display element sizes, operating system settings, network availability, and the capabilities of hardware installed on the system.This class cannot be instantiated. e.g
MousePresent: SystemInformation.MousePresent.ToString()
MouseButtonsSwapped: SystemInformation.MouseButtonsSwapped.ToString()
#8. What is the purpose of the [STA Thread] attribute for the Main method of a C# program?

That marks the thread as being Single
Thread Apartment which means any multiple threaded calls need to be marshaled
over to that thread before they are called. That’s there because Windows Forms
uses some OLE calls (Clipboard for example), which must be made from the thread
that initialized OLE.
#9. How to import CSV data using .NET application?

Importing CSV files using .NET application
#10. How to find size of logical drives using .NET?

Download source files – 10.0 Kb
Download demo project – 5.48 Kb

There is direct method in .NET for computing the file size but there is no such method for computing directory size and logical drive size.One may think of just adding file size to get directory size and then drive size. But this method has some drawbacks. But we can use Win32 API function GetDiskFreeSpaceEx for this purpose.
The GetDiskFreeSpaceEx function retrieves information about the amount of space that is available on a disk volume, which is the total amount of space, the total amount of free space, and the total amount of free space available to the user that is associated with the calling thread. You can do it as following: Enlist the drives:
string[] tempString = Directory.GetLogicalDrives();
foreach(string tempDrive in tempString)
{
cmbDrives.Items.Add(tempDrive);
}
cmbDrives.SelectedIndex=0;
public sealed class DriveInfo
{

[DllImport("kernel32.dll", EntryPoint="GetDiskFreeSpaceExA")]
private static extern long GetDiskFreeSpaceEx(string lpDirectoryName,
out long lpFreeBytesAvailableToCaller,
out long lpTotalNumberOfBytes,
out long lpTotalNumberOfFreeBytes);

public static long GetInfo(string drive, out long available, out long total, out long free)
{
return GetDiskFreeSpaceEx(drive,out available,out total,out free);
}

public static DriveInfoSystem GetInfo(string drive)
{
long result, available, total, free;
result = GetDiskFreeSpaceEx(drive, out available, out total, out free);
return new DriveInfoSystem(drive,result,available,total,free);
}

}

public struct DriveInfoSystem
{
public readonly string Drive;
public readonly long Result;
public readonly long Available;
public readonly long Total;
public readonly long Free;

public DriveInfoSystem(string drive, long result, long available, long total, long free)
{
this.Drive = drive;
this.Result = result;
this.Available = available;
this.Total = total;
this.Free = free;
}
}
and then you can use it as
DriveInfoSystem info = DriveInfo.GetInfo(“c:”);

OOPS
#1. What are Copy Constructors?

We know that a constructor is a special method that has the same name as the class and returns no value.
It is used to initialize the data in the object we’re creating.

There’s another kind of constructor—the copy constructor. When we copy one object to another, C# will copy the reference to the first object to the new object, which means that we now have two references to the same object. To make an actual copy, we can use a copy constructor, which is just a standard constructor that takes an object of the current class as its single parameter. For example, here’s what a copy constructor for the Student class might look like. Note that we’re copying the name field to the new object.
public Student(Student student)
{
this.name = student.name;
}

Now we can use this constructor to create copies. The copy will be a separate object, not just a reference to the original object.
class Student
{
private string name;

public Student(string name)
{
this.name = name;
}

public Student(Student student)
{
this.name = student.name;
}

public string Name
{
get
{
return name;
}
set
{
name = value;
}
}
}

class Final
{
static void Main()
{
Student student = new Student (“A”);
Student NewStudent = new Student (student);
student.Name = “B”;
System.Console.WriteLine(“The new student’s name is {0}”, NewStudent.Name);
}
}

The new student’s name is A.
#2. What are Read-Only Constants?

There are situations where we would like to decide the value of a constant member at run-time. We may also like to have different constant values for different objects of class. To overcome these shortcomings, C# provides another modifier known as readonly to be used with data members. This modifier is designed to set the value of the member using a constructor method, but cannot be modified later. The readonly members may be declared as either static fields or instance fields. When they are declared as instance fields, they can take different values with different objects. Consider the code below:
class Numbers
{
public readonly int m;
public static readonly int n;
public Numbers (int x)
{
m=x;
}

static Numbers ()
{
n=100;
}
}

The value for m is provided at the time of creation of an object using the constructor with parameter x. This value will remain constant for that object. Remember, the variable n is assigned a value of 100,even before the creation of any objects of Numbers.

Describe the differences between XML and HTML.

It’s amazing how many developers claim to be proficient programming with XML, yet do not understand the basic differences between XML and HTML. Anyone with a fundamental grasp of XML should be able describe some of the main differences outlined in the table below.

Differences Between XML and HTMLXML HTML

User definable tags
Defined set of tags designed for web display

Content driven
Format driven

End tags required for well formed documents
End tags not required

Quotes required around attributes values
Quotes not required

Slash required in empty tags
Slash not required

Describe the role that XSL can play when dynamically generating HTML pages from a relational database.

Even if candidates have never participated in a project involving this type of architecture, they should recognize it as one of the common uses of XML. Querying a database and then formatting the result set so that it can be validated as an XML document allows developers to translate the data into an HTML table using XSLT rules. Consequently, the format of the resulting HTML table can be modified without changing the database query or application code since the document rendering logic is isolated to the XSLT rules.

Give a few examples of types of applications that can benefit from using XML.

There are literally thousands of applications that can benefit from XML technologies. The ball of this question is not to have the candidate rattle off a laundry list of projects that they have worked on, but, rather, to allow the candidate to explain the rationale for choosing XML by citing a few real world examples. For instance, one appropriate answer is that XML allows content management systems to store documents independently of their format, which thereby reduces data redundancy. Another answer relates to B2B exchanges or supply chain management systems. In these instances, XML provides a mechanism for multiple companies to exchange data according to an agreed upon set of rules. A third common response involves wireless applications that require WML to render data on hand held devices.

What is DOM and how does it relate to XML?

The Document Object Model (DOM) is an interface specification maintained by the W3C DOM Workgroup that defines an application independent mechanism to access, parse, or update XML data. In simple terms it is a hierarchical model that allows developers to manipulate XML documents easily Any developer that has worked extensively with XML should be able to discuss the concept and use of DOM objects freely. Additionally, it is not unreasonable to expect advanced candidates to thoroughly understand its internal workings and be able to explain how DOM differs from an event-based interface like SAX.

What is SOAP and how does it relate to XML?

The Simple Object Access Protocol (SOAP) uses XML to define a protocol for the exchange of information in distributed computing environments. SOAP consists of three components: an envelope, a set of encoding rules, and a convention for representing remote procedure calls. Unless experience with SOAP is a direct requirement for the open position, knowing the specifics of the protocol, or how it can be used in conjunction with HTTP, is not as important as identifying it as a natural application of XML.

Can you walk us through the steps necessary to parse XML documents?

Superficially, this is a fairly basic question. However, the ball is not to determine whether candidates understand the concept of a parser but rather have them walk through the process of parsing XML documents step-by-step. Determining whether a non-validating or validating parser is needed, choosing the appropriate parser, and handling errors are all important aspects to this process that should be included in the candidate’s response.

Give some examples of XML DTDs or schemas that you have worked with.

Although XML does not require data to be validated against a DTD, many of the benefits of using the technology are derived from being able to validate XML documents against business or technical architecture rules. Polling for the list of DTDs that developers have worked with provides insight to their general exposure to the technology. The ideal candidate will have knowledge of several of the commonly used DTDs such as FpML, DocBook, HRML, and RDF, as well as experience designing a custom DTD for a particular project where no standard existed.

Using XSLT, how would you extract a specific attribute from an element in an XML document?

Successful candidates should recognize this as one of the most basic applications of XSLT. If they are not able to construct a reply similar to the example below, they should at least be able to identify the components necessary for this operation: xsl:template to match the appropriate XML element, xsl:value-of to select the attribute value, and the optional xsl:apply-templates to continue processing the document.

Extract Attributes from XML Data
Example 1.

<xsl:template match=”element-name”>
Attribute Value:
<xsl:value-of select=”@attribute”/>
<xsl:apply-templates/>
</xsl:template>

When constructing an XML DTD, how do you create an external entity reference in an attribute value?

Every interview session should have at least one trick question. Although possible when using SGML, XML DTDs don’t support defining external entity references in attribute values. It’s more important for the candidate to respond to this question in a logical way than than the candidate know the somewhat obscure answer.

How would you build a search engine for large volumes of XML data?

The way candidates answer this question may provide insight into their view of XML data. For those who view XML primarily as a way to denote structure for text files, a common answer is to build a full-text search and handle the data similarly to the way Internet portals handle HTML pages. Others consider XML as a standard way of transferring structured data between disparate systems. These candidates often describe some scheme of importing XML into a relational or object database and relying on the database’s engine for searching. Lastly, candidates that have worked with vendors specializing in this area often say that the best way the handle this situation is to use a third party software package optimized for XML data.

Posted in Interview Questions | Leave a Comment »

SQL SERVER INTERVIEW QUESTIONS

January 5, 2008 by venkatesh2urs

Here are some sql server interview questions which is very helpful in interviews. These sql server interview questions provides a lot of informations. Not at all, here you will also find interesting query i.e sql server 2000 query like Sql Insert Query, Delete Sql Query, Update Sql Query and Sql Create Query.

Question: What is the difference between UNION ALL Statement and UNION ?
Answer:- The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Question: What is COMMIT & ROLLBACK statement in SQL ?
Answer: Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.COMMIT we can also use in store procedure.
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

Question:-What is diffrence between OSQL and Query Analyzer ?
Answer:-Both are the same but ther eis little diffrence OSQL is command line tool whic is execute qery and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool.OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other usefull thing about OSQL is that its helps in scheduling.

Question: What is SQL whats its uses and its component ?
Answer: The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language . Its allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it.
The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

Question: Write some disadvantage of Cursor ?
Answer:- Cursor plays there row quite nicely but although there are some disadvantage of Cursor .
Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

Question: What is Log Shipping and its purpose ?
Answer: In Log Shipping the transactional log file from one server is automatically updated in backup database on the other server and in the case when one server fails the other server will have the same DB and we can use this as the DDR(disaster recovery) plan.

Question: What are the null values in SQL SERVER ?
Answer: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there is no values in the field.That value is something like <null>.Nulls present missing information. We can also called null propagation.

Question: What is difference between OSQL and Query Analyzer ?
Answer: Both are same for functioning but there is a little difference OSQL is command line tool which execute query and display the result same a Query Analyzer do but Query Analyzer is graphical.OSQL have not ability like Query Analyzer to analyze queries and show statistics on speed of execution .And other useful thing about OSQL is that its helps in scheduling which is done in Query Analyzer with the help of JOB.
Sql Server 2000 Query

Question: Write a Role of Sql Server 2005 in XML Web Services?
Answer:- SQL Server 2005 create a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.

Question: What are the different types of Locks ?
Answer: There are three main types of locks that SQL Server
(1)Shared locks are used for operations that does not allow to change or update data, such as a SELECT statement.
(2)Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
(3)Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Question: Explain some SQL Server 2000 Query?
Answer: Here are some sql server 2000 query like Sql Insert Query, Delete Sql Query, Update Sql Query and Sql Create Query:
1) Sql Insert Query:
a) How to encrypt data by using Sql Insert Query.
–: insert into table_name(Tablecolumn1, tablecolumn2,. . . . .) values (‘value1′, pwdencrypt(‘value’),. . . .)

b) How to copy data from one table to another with the help of Sql Insert Query.
–: insert into table_name(column1,column2,. . . . ) select column1, column2, . . . . from table_name2

c) Sql Insert Query using where clause
–: insert into tablename(column1,column2) select column1,column2 from tablename2 where id=value.

Question: What is ‘Write-ahead log’ in Sql Server 2000 ?
Answer: Before understanding it we must have an idea about the transaction log files. These files are the files which holds the data for change in database .
Now we explain when we are doing some Sql Server 2000 query or any Sql query like Sql insert query,delete sql query,update sql query and change the data in sql server database it cannot change the database directly to table .Sql server extracts the data that is modified by sql server 2000 query or by sql query and places it in memory.Once data is stores in memory user can make changes to that a log file is gernated this log file is gernated in every five mintues of transaction is done. After this sql server writes changes to database with the help of transaction log files. This is called Write-ahead log.

Question: What do u mean by Extents and types of Extends ?
Answer: An Extent is a collection of 8 sequential pages to hold database from becoming fregmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation Sql Server assign space to table in extents. So that the Sql Server keep upto date data in extents. Because these pages are continously one after another. There are usually two types of extends:-Uniform and Mixed.
Uniform means when extent is own by a single object means all collection of 8 ages hold by a single extend is called uniform.
Mixed mean when more then one object is comes in extents is known as mixed extents.

Question: What is different in Rules and Constraints ?
Answer: Rules and Constraints are similar in functionality but there is a An little diffrence between them.Rules are used for backward compatibility . One the most exclusive diffrence is that we an bind rules to a datatypes whereas constraints are bound only to columns.So we can create our own datatype with the help of Rules and get the input according to that.

Question: What is defaults in Sql Server and types of Defaults ?
Answer: Defaults are used when a field of columns is allmost common for all the rows for example in employee table all living in delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field its also work as intellisense means when user inputing d it will automatically fill the delhi . There are two types of defaults object and definations.
Object deault:-These defaults are applicable on a particular columns . These are usually deined at the time of table designing.When u set the object default field in column state this column in automatically field when u left this filed blank.
Defination default:-When we bind the datatype with default let we named this as dotnet .Then every time we create column and named its datatype as dotnet it will behave the same that we set for dotnet datatype.

Question: What Is Database ?
Answer: A database is similar to a data file in that it is a storage place for data. Like a data file, a database does not present information directly to a user; the user runs an application that accesses data from the database and presents it to the user in an understandable format.Database systems are more powerful than data files in that data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application must update at the same time. Related pieces of data are grouped together in a single structure or record, and relationships can be defined between these structures and records.When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format. A database typically has two main parts: first, the files holding the physical database and second, the database management system (DBMS) software that applications use to access data. The DBMS is responsible for enforcing the database structure, including: · Maintaining relationships between data in the database. Ensuring that data is stored correctly, and that the rules defining data relationships are not violated. · Recovering all data to a point of known consistency in case of system failures.

Question: what is Relational Database ?
Answer: Although there are different ways to organize data in a database, relational databases are one of the most effective. Relational database systems are an application of mathematical set theory to the problem of effectively organizing data. In a relational database, data is collected into tables (called relations in relational theory). A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table. For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345. When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

Question: What is Data Integrity and it’s categories ?
Answer: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company. Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity
2) Domain integrity
3) Referential integrity
4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).
Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions, NOT NULL definitions, and rules).
Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from:
· Adding records to a related table if there is no associated record in the primary table.
· Changing values in a primary table that result in orphaned records in a related table.
· Deleting records from a primary table if there are matching related records.
For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.
User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

Question: SQL Server runs on which TCP/IP port and From where can you change the default port?
Answer: SQL Server runs on port 1433 but we can also change it for better security and From the network Utility TCP/IP properties –>Port number.both on client and the server.

Question: What is the use of DBCC commands?
Answer: DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC To check that all pages in a db are correctly allocated. DBCC SQLPERF – It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

Question: What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Answer: Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Question: When do you use SQL Profiler?
Answer: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc.

Question: Can you explain the role of each service?
Answer: SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintenance, Backups DTC – Is for linking and connecting to other SQL Servers.

Question: What is Normalization ?
Answer: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system.

Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables. A greater number of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an nonnomalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft® SQL Server™ 2000 query optimizer is efficient at selecting rapid, efficient joins between tables.

Some of the benefits of normalization include:
·Faster sorting and index creation.
·A larger number of clustered indexes. For more information, Narrower and more compact indexes.
·Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements.
·Fewer null values and less opportunity for inconsistency, which increase database compactness.

As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables.

Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

Question: Can you explain what View is in SQL ?
Answer: View is just a virtual table nothing else which is based or we can say devlop with SQL SELECT query.So we can say that its a real database table (it has columns and rows just like a regular table),but one difference is that real tables store data,but views can’t. View data is generated dynamically when the view is referenced.And view can also references one or more existing database tables or other views. We can say that it is filter of database.
Question: Can you tell me the difference between DELETE &TRUNCATE commands?
Answer: Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What are the different index configurations a table can have?
A table can have one of the following index configurations:

No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor

What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.
DBCC CHECKALLOC – To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.

Accent sensitivity
a and á, o and ó, etc.

Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity
When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is a NOLOCK?
Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back using logs.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back using logs.
DELETE is DML Command.
DELETE does not reset identity of the table.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

What types of Joins are possible with Sql Server?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. HAVING criteria is applied after the the grouping of rows has occurred.

What is sub-query? Explain properties of sub-query.
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.

What are types of sub-queries?
Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.

–

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. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.

Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is User Defined Functions?
User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function
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.

Inline Table-Value User-Defined Function
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.

Multi-statement Table-Value User-Defined Function
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. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?
Windows mode and mixed mode (SQL & Windows).

To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the Security page.

Where are SQL server users names and passwords are stored in sql server?
They get stored in master db in the sysxlogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

What is SQL server agent?
SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

What is log shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

What is the difference between a local and a global variable?
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

What command do we use to rename a db?
sp_renamedb ‘oldname’ , ‘newname’
If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

What is sp_configure commands and set commands?
Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:

* Transactional
* Snapshot
* Merge

Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.

What are the OS services that the SQL Server installation adds?
MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What are three SQL keywords used to change or set someone’s permissions?
GRANT, DENY, and REVOKE.

What does it mean to have quoted_identifier on? What are the implications of having it off?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

What is the STUFF function and how does it differ from the REPLACE function?
STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
REPLACE function to replace existing characters of all occurance. Using this syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string.

Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

How to rebuild Master Database?
Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.
In the Rebuild Master dialog box, click Browse.
In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.
Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.
In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.
To continue, you may need to stop a server that is running.
Source: http://msdn2.microsoft.com/en-us/library/aa197950(SQL.80).aspx

What is the basic functions for master, msdb, model, tempdb databases?
The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in the instance.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is data integrity? Explain constraints?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

What are the properties of the Relational tables?
Relational tables have six properties:

* Values are atomic.
* Column values are of the same kind.
* Each row is unique.
* The sequence of columns is insignificant.
* The sequence of rows is insignificant.
* Each column must have a unique name.

What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How to get @@error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers, the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job. E.g. Back up database, Update Stats of Tables. Job steps give user control over flow of execution. If one job fails, user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and than do bulk of inserts and to restore those indexes after that.

What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

How do you load large data to the SQL server database?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

Can we rewrite subqueries into simple select statements or with joins?
Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

Can SQL Servers linked to other servers like Oracle?
SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

How to know which index a table is using?
SELECT table_name,index_name FROM user_constraints

How to copy the tables, schema and views from one SQL server to another?
Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Which virtual table does a trigger use?
Inserted and Deleted.

List few advantages of Stored Procedure.

* Stored procedure can reduced network traffic and latency, boosting application performance.
* Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
* Stored procedures help promote code reuse.
* Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
* Stored procedures provide better security to your data.

What is DataWarehousing?

* Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
* Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
* Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting;
* Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is OLTP(OnLine Transaction Processing)?
In OLTP – online transaction processing systems relational database design use the discipline of data modeling and generally follow the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

How do SQL server 2000 and XML linked? Can XML be used to access data?
FOR XML (ROW, AUTO, EXPLICIT)
You can execute SQL queries against existing relational databases to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures. To retrieve XML results, use the FOR XML clause of the SELECT statement and specify an XML mode of RAW, AUTO, or EXPLICIT.

OPENXML
OPENXML is a Transact-SQL keyword that provides a relational/rowset view over an in-memory XML document. OPENXML is a rowset provider similar to a table or a view. OPENXML provides a way to access XML data within the Transact-SQL context by transferring data from an XML document into the relational tables. Thus, OPENXML allows you to manage an XML document and its interaction with the relational environment.

What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.——

——————————–
What is normalization? Explain different levels of normalization?

Check out the article Q100139 from Microsoft knowledge base and of
course, there’s much more information available in the net. It’ll be a
good idea to get a hold of any RDBMS fundamentals text book,
especially the one by C. J. Date. Most of the times, it will be okay
if you can explain till third normal form.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of
normalization. It’s the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.

How do you implement one-to-one, one-to-many and many-to-many
relationships while designing tables?

One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.

It will be a good idea to read up a database designing fundamentals
text book.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which
they are defined. But by default primary key creates a clustered index
on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn’t allow
NULLs, but unique key allows one NULL only.

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by
providing a descriptive name, and format to the database. Take for
example, in your database, there is a column called Flight_Num which
appears in many tables. In all these tables it should be varchar(8).
In this case you could create a user defined datatype called
Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

What is bit datatype and what’s the information that can be stored
inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or
false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0
and there was no support for NULL. But from SQL Server 7.0 onwards,
bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called
composite key.

What are defaults? Is there a column to which a default can’t be bound?

A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can’t have defaults bound to them. See CREATE
DEFUALT in books online.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction. For
more information and explanation of these properties, see SQL Server
books online or any RDBMS fundamentals text book.

Explain different isolation levels

An isolation level determines the degree of isolation of data between
concurrent transactions. The default SQL Server isolation level is
Read Committed. Here are the other isolation levels (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable
Read, Serializable. See SQL Server books online for an explanation of
the isolation levels. Be sure to read about SET TRANSACTION ISOLATION
LEVEL, which lets you customize the isolation level at the connection
level.

CREATE INDEX myIndex ON myTable(myColumn)

What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered
index gets created on the primary key, unless specified otherwise.

What’s the maximum size of a row?

8060 bytes. Don’t be surprised with questions like ‘what is the
maximum number of columns per table’. Check out SQL Server books
online for the page titled: “Maximum Capacity Specifications”.

Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you
don’t, at least be familiar with the way clustering works and the two
clusterning configurations Active/Active and Active/Passive. SQL
Server books online has enough information on this topic and there is
a good white paper available on Microsoft site.

Explain the architecture of SQL Server

This is a very important question and you better be able to answer it
if consider yourself a DBA. SQL Server books online is the best place
to read about SQL Server architecture. Read up the chapter dedicated
to SQL Server Architecture.

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks
(like row locks, page locks) into higher level locks (like table
locks). Every lock is a memory structure too many locks would mean,
more memory being occupied by locks. To prevent this from happening,
SQL Server escalates the many fine-grain locks to fewer coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.

What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets
logged in the transaction log, which makes it slow. TRUNCATE TABLE
also deletes all the rows in a table, but it won’t log the deletion of
each row, instead it logs the deallocation of the data pages of the
table, which makes it faster. Of course, TRUNCATE TABLE can be rolled
back.

Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more
infomation.

What are the new features introduced in SQL Server 2000 (or the latest
release of SQL Server at the time of your interview)? What changed
between the previous version of SQL Server and the current version?

This question is generally asked to see how current is your knowledge.
Generally there is a section in the beginning of the books online
titled “What’s New”, which has all such information. Of course,
reading just that is not enough, you should have tried those things to
better answer the questions. Also check out the section titled
“Backward Compatibility” in books online which talks about the changes
that have taken place in the new version.

What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database
automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages
titled: “Constraints” and “CREATE TABLE”, “ALTER TABLE”

Whar is an index? What are the types of indexes? How many clustered
indexes can be created on a table? I create a separate index on each
column of a table. what are the advantages and disadvantages of this
approach?

Indexes in SQL Server are similar to the indexes in books. They help
SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes.
When you craete a clustered index on a table, all the rows in the
table are stored in the order of the clustered index key. So, there
can be only one clustered index per table. Non-clustered indexes have
their own storage separate from the table data storage. Non-clustered
indexes are stored as B-tree structures (so do clustered indexes),
with the leaf level nodes having the index key and it’s row locater.
The row located could be the RID or the Clustered index key, depending
up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the
query performance, as the query optimizer can choose from all the
existing indexes to come up with an efficient execution plan. At the
same t ime, data modification operations (such as INSERT, UPDATE,
DELETE) will become slow, as every time data changes in the table, all
the indexes need to be updated. Another disadvantage is that, indexes
need disk space, the more indexes you have, more disk space is used.

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide
fault tolerance to database servers. There are six RAID levels 0
through 5 offering different levels of performance, fault tolerance.
MSDN has some information about RAID levels and for detailed
information, check out the RAID advisory board’s homepage

What are the steps you will take to improve performance of a poor
performing query?

This is a very open ended question and there could be a lot of reasons
behind the poor performance of a query. But some general issues that
you could talk about would be: No indexes, table scans, missing or out
of date statistics, blocking, excess recompilations of stored
procedures, procedures and triggers without SET NOCOUNT ON, poorly
written query with unnecessarily complicated joins, too much
normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance
problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET
STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance
monitor, Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from
Microsoft web site. Don’t forget to check out sql-server-performance.com

What are the steps you will take, if you are tasked with securing an
SQL Server?

Again this is another open ended question. Here are some things you
could talk about: Preferring NT authentication, using server, databse
and application roles to control access to the data, securing the
physical database files using NTFS permissions, using an unguessable
SA password, restricting physical access to the SQL Server, renaming
the Administrator account on the SQL Server computer, disabling the
Guest account, enabling auditing, using multiprotocol encryption,
setting up SSL, setting up firewalls, isolating SQL Server from the
web server etc.

Read the white paper on SQL Server security from Microsoft website.
Also check out My SQL Server security best practices

What is a deadlock and what is a live lock? How will you go about
resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one
piece of data, attempt to acquire a lock on the other’s piece. Each
process would wait indefinitely for the other to release the lock,
unless one of the user processes is terminated. SQL Server detects
deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is
repeatedly denied because a series of overlapping shared locks keeps
interfering. SQL Server detects the situation after four denials and
refuses further shared locks. A livelock also occurs when read
transactions monopolize a table or page, forcing a write transaction
to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and “Minimizing Deadlocks” in SQL
Server books online. Also check out the article Q169960 from Microsoft
knowledge base.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock
and a second connection requires a conflicting lock type. This forces
the second connection to wait, blocked on the first.

Read up the following topics in SQL Server books online: Understanding
and avoiding blocking, Coding efficient transactions.

Explain CREATE DATABASE syntax

Many of us are used to craeting databases from the Enterprise Manager
or by just issuing the command: CREATE DATABAE MyDB. But what if you
have to create a database with two filegroups, one on drive C and the
other on drive D with log on drive E with an initial size of 600 MB
and with a growth factor of 15%? That’s why being a DBA you should be
familiar with the CREATE DATABASE syntax. Check out SQL Server books
online for more information.

How to restart SQL Server in single user mode? How to start SQL Server
in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE.
This EXE has some very important parameters with which a DBA should be
familiar with. -m is used for starting SQL Server in single user mode
and -f is used to start the SQL Server in minimal confuguration mode.
Check out SQL Server books online for more parameters and their
explanations.

As a part of your job, what are the DBCC commands that you commonly
use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there
are a whole load of DBCC commands which are very useful for DBAs.
Check out SQL Server books online for more information.

What are statistics, under what circumstances they go out of date, how
do you update them?

Statistics determine the selectivity of the indexes. If an indexed
column has unique values then the selectivity of that index is more,
as opposed to an index with non-unique values. Query optimizer uses
these indexes in determining whether to choose an index or not while
executing a query.

Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added,
changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE
statement and then repopulated
3) Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE
STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP
STATISTICS, sp_autostats, sp_createstats, sp_updatestats

What are the different ways of moving data/databases between servers
and databases in SQL Server?

There are lots of options available, you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, dettaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT
scripts to generate data.

Explian different types of BACKUPs avaialabe in SQL Server? Given a
particular scenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Sever 7.0+ are Full database
backup, differential database backup, transaction log backup,
filegroup backup. Check out the BACKUP and RESTORE commands in SQL
Server books online. Be prepared to write the commands in your
interview. Books online also has information on detailed
backup/restore architecture and when one should go for a particular
kind of backup.

What is database replicaion? What are the different types of
replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:

* Snapshot replication
* Transactional replication (with immediate updating subscribers,
with queued updating subscribers)
* Merge replication

See SQL Server books online for indepth coverage on replication. Be
prepared to explain how different replication agents function, what
are the main system tables used in replication etc.

How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the
sqlservr.exe, which is used to determine the service pack installed.
To know more about this process visit SQL Server service packs and
versions.

What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See
books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one rowundtrip, however large the resultset is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Furthere, there are restrictions on
the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of
cursors. Here is an example:

If you have to give a flat hike to your employees using the following
criteria:

Salary between 30000 and 40000 — 5000 hike
Salary between 40000 and 55000 — 7000 hike
Salary between 55000 and 65000 — 9000 hike

In this situation many developers tend to use a cursor, determine each
employee’s salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don’t have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the ‘My code library’ section of my site or
search for WHILE.

Write down the general syntax for a SELECT statements covering all the
options.

Here’s the basic syntax: (Also checkout SELECT in books online for
advanced syntax).

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

What is a join and explain different types of joins.

Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from
another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are
further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL
OUTER JOINS.

For more information see pages from books online titled: “Join
Fundamentals” and “Using Joins”.

Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and
@@TRANCOUNT

What is an extended stored procedure? Can you instantiate a COM object
by using T-SQL?

An extended stored procedure is a function within a DLL (written in a
programming language like C, C++ using Open Data Services (ODS) API)
that can be called from T-SQL, just the way we call normal stored
procedures using the EXEC statement. See books online to learn how to
create extended stored procedures and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages like VB, VC++)
object from T-SQL by using sp_OACreate stored procedure. Also see
books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty,
sp_OADestroy. For an example of creating a COM object in VB and
calling it from T-SQL, see ‘My code library’ section of this site.

What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are triggers? How many triggers you can have on a table? How to
invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place
on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, this restriction is gone, and you could create multiple
triggers per each action. But in 7.0 there’s no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify
which trigger fires first or fires last using sp_settriggerorder

Triggers can’t be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on
which they are defined.

Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification
operation happens. So in a way, they are called post triggers. But in
SQL Server 2000 you could create pre triggers also. Search SQL Server
2000 books online for INSTEAD OF triggers.

Also check out books online for ‘inserted table’, ‘deleted table’ and
COLUMNS_UPDATED()

There is a trigger defined for INSERT operations on a table, in an
OLTP system. The trigger is written to instantiate a COM object and
pass the newly insterted rows to it for some custom processing. What
do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you
are doing it from within a trigger, it slows down the data insertion
process. Same is the case with sending emails from triggers. This
scenario can be better implemented by logging all the necessary data
into a separate table, and have a job which periodically checks this
table and does the needful.

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of
the same table will be joined in the query. Here is an example:
Employees table which contains rows for normal employees as well as
managers. So, to find out the managers of all the employees, you need
a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,’Vyas’
INSERT emp SELECT 2,3,’Mohan’
INSERT emp SELECT 3,NULL,’Shobha’
INSERT emp SELECT 4,2,’Shridhar’
INSERT emp SELECT 5,2,’Sourabh’

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here’s an advanced query using a LEFT OUTER JOIN that even returns the
employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

—–

1. What is normalization? – Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
2. What is a Stored Procedure? – Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.

3. Can you give an example of Stored Procedure? – sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
4. What is a trigger? – Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
5. What is a view? – If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
6. What is an Index? – When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
7. What are the types of indexes available with SQL Server? – There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
8. What is the basic difference between clustered and a non-clustered index? – The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
9. What are cursors? – Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
10. When do we use the UPDATE_STATISTICS command? – This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
11. Which TCP/IP port does SQL Server run on? – SQL Server runs on port 1433 but we can also change it for better security.
12. From where can you change the default port? – From the Network Utility TCP/IP properties –> Port number.both on client and the server.
13. Can you tell me the difference between DELETE & TRUNCATE commands? – Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
14. Can we use Truncate command on a table which is referenced by FOREIGN KEY? – No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
15. What is the use of DBCC commands? – DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
16. Can you give me some DBCC command options?(Database consistency check) – DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC – To check that all pages in a db are correctly allocated. DBCC SQLPERF – It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.
17. What command do we use to rename a db? – sp_renamedb ‘oldname’ , ‘newname’
18. Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? – In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
19. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? – Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
20. What do you mean by COLLATION? – Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary – case insensitive and Binary.
21. What is a Join in SQL Server? – Join actually puts data from two or more tables into a single result set.
22. Can you explain the types of Joins that we can have with Sql Server? – There are three types of joins: Inner Join, Outer Join, Cross Join
23. When do you use SQL Profiler? – SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
24. What is a Linked Server? – Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
25. Can you link only other SQL Servers or any database servers such as Oracle? – We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
26. Which stored procedure will you be running to add a linked server? – sp_addlinkedserver, sp_addlinkedsrvlogin
27. What are the OS services that the SQL Server installation adds? – MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
28. Can you explain the role of each service? – SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintanance, Backups DTC – Is for linking and connecting to other SQL Servers
29. How do you troubleshoot SQL Server if its running very slow? – First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
30. Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot? – First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.
31. What are the authentication modes in SQL Server? – Windows mode and mixed mode (SQL & Windows).
32. Where do you think the users names and passwords will be stored in sql server? – They get stored in master db in the sysxlogins table.
33. What is log shipping? Can we do logshipping with SQL Server 7.0 – Logshipping is a new feature of SQL Server 2000. We should have two SQL Server – Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
34. Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? – For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER –m which will basically bring it into the maintenance mode after which we can restore the master db.
35. Let us say master db itself has no backup. Now you have to rebuild the db so what kind of action do you take? – (I am not sure- but I think we have a command to do it).
36. What is BCP? When do we use it? – BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
37. What should we do to copy the tables, schema and views from one SQL Server to another? – We have to write some DTS packages for it.

Posted in Interview Questions | Leave a Comment »

Tips

January 5, 2008 by venkatesh2urs

How To Create Controls Dynamicaly in ASP.NET

ASP.NET is a very powerful and flexible technology, allowing programmers to develop sophisticated web applications within a short period of time. It is so easy to drag and drop controls in a web form and start writing the business logic in the code behind file.
Ofcourse, dragging and dropping controls from the toolbox is the easiest approach.
In this article, I will discuss how you can create ASP.NET controls at runtime using C# syntax.

1. Create a new ASP.NET web application using VS.NET
2. Drag a Drop a Panel control into webform
3. Double click on the webpage this will add a Load Event Handler in the code behind

private void Page_Load(object sender, System.EventArgs e)
{
TextBox _txt = new TextBox();
_txt.ID = “txtId”;
_txt.Text = “This is Dynamicaly genrated Textbox”;
Panel1.Controls.Add(_txt);

}
The above code will create a TextBox dynamicaly and add to the form.Using this
approach you can create any other ASP.NET server controls and add to form.

How To Specify postion for dynamicaly created control

The most common method is to use Table control to position controls and text in Page.
The following code sample will create 5 textboxes and labels. We will create 5 rows and 2 cells in each row dynamically and add our dynamicaly created textbox/label controls to the table so that they will be aligned properly and will appear in the page in proper format.

protected void Page_Load(object sender, EventArgs e)
{

Table tblDynamic = new Table();
for (int i = 0; i < 5; i++)
{
TableRow tr = new TableRow();
// Create column 1
TableCell td1 = new TableCell();
// Create a label control dynamically
Label _label = new Label();
_label.ID = “lbl” + i.ToString();
_label.Text = “Enter Value ” + i.ToString();
// Add control to the table cell
td1.Controls.Add(_label);

// Create column 2
TableCell td2 = new TableCell();
TextBox _text = new TextBox();
_text.ID = “txt_” + i.ToString();
// Add control to the table cell
td2.Controls.Add(_text);
// Add cell to the row
tr.Cells.Add(td1);
tr.Cells.Add(td2);
// Add row to the table.
tblDynamic.Rows.Add(tr);
}
pnl.Controls.Add(tblDynamic);

}

How To Create Javascript Alert from ASP.NET code behind

In Windows Forms it is very easy to pop up a status message by calling MessageBox।Show(“message”). It is that kind of object model we want in ASP.NET for printing out JavaScript alerts. We want Alert.Show(“message”) in ASP.NET.

I’ve written a static class called Alert with one public method called Show। The implementation is as simple as can be. Just put the .cs file in the App_Code folder on your website and you instantly have access to the method from all pages and user controls.

I’ve written a static class called Alert with one public method called Show. The implementation is as simple as can be. Just put the .cs file in the App_Code folder on your website and you instantly have access to the method from all pages and user controls.

using System.Web;
using System.Text;
using System.Web.UI;

///
/// A JavaScript alert
///
public static class Alert
{

///
/// Shows a client-side JavaScript alert in the browser.
///
///
The message to appear in the alert.
public static void Show(string message)
{
// Cleans the message to allow single quotation marks
string cleanMessage = message.Replace(“‘”, “\\’”);
string script = ” “;

// Gets the executing web page
Page page = HttpContext.Current.CurrentHandler as Page;

// Checks if the handler is a Page and that the script isn’t allready on the Page
if (page != null && !page.ClientScript.IsClientScriptBlockRegistered(“alert”))
{
page.ClientScript.RegisterClientScriptBlock(typeof(Alert), “alert”, script);
}
}
}

How To Use
Add a button in you asp.net file and add following code in click event of the button

void btnSave_Click(object sender, EventArgs e)
{
try
{
SaveSomething();
Alert.Show(“You document has been saved”);
}
catch (ReadOnlyException)
{
Alert.Show(“You do not have write permission to this file”);
}
}

How To Upload Files Larger Than 4 MB

By default, the size limit for ASP.NET uploads is set at 4MB. Although this is large enough for most sites, if you’re dealing in particularly hefty uploads, there’s a little-known technique for upping the cap.

Simply open machine.config in the \Microsoft.NET\Framework\ \Config folder and locate the following setting:

Now, simply alter the maxRequestLength value to a figure of your choice. To allow uploads of up to 8MB, for example, change the value from 4096 to 8192.

Do Postback With Parameters in javascript

Whenever you use a Button or LinkButton it is because you want to be able to do a postback when it is clicked. The same could be the case for CheckBox or DropDownList etc. but then you need to set the AutoPostback property to true. It all works very much the same way from a user’s point of view – click or select and the page performs a postback.

However, in some cases you want to be able to do a postback from a custom JavaScript function that emulates the click of an e.g. LinkButton. That is very simple to do so, but did you know that you also can send custom information via such a postback?

Example

The following LinkButton calls the server-side event handler OnSaveClick.

<asp:LinkButton runat=”Server” ID=”btnSave” Text=”Save” OnClick=”OnSaveClick” />

This is pretty much standard and no tricks have been used so far. Now we need the
JavaScript method that forces the LinkButton to do a postback that calls the
server-side method OnSaveClick.

function SaveWithParameter(parameter)

{

__doPostBack(‘btnSave ‘, parameter)

}

</script>

Notice that the function takes a parameter that it sends to the __doPostBack
function. All we need to do now is to call the SaveWithParamter function from
JavaScript.
>

SaveWithParameter(“Hello world!”);

Now the page performs a postback and we can now access the “Hello world!” string
that we sent as a parameter from within the OnSaveClick event handler.

protected void OnSaveClick(object sender, EventArgs e)

{

string parameter = Request["__EVENTARGUMENT"];

}

What we just did was to perform a postback from a custom JavaScript function and
send a parameter to the server-side event handler. It sounds a lot harder than it
is, right?
>

In ASP.NET 2.0 you have to set the EnableEventValidation=”false” attribute in the
page declaration or in web.config to make it work.

How To Print certain areas of Page

Create a style sheet for printing purpose, normally by removing/hiding background
colors, images…. After that include it with media=”print”, so that this style sheet
will be applied while printing.
<LINK media=”print” href=”styles/printStyle.css” type=”text/css”
rel=”stylesheet”>

Retrieve data from a web page

Summary: An example of how we can make a request to a web page and retrieve the resulting HTML

There are a few situations where it would be useful to be able to retrieve the HTML from a web page via code. Fortunately, this is made relatively easy by the HttpWebRequest and httpWebResponse classes.

Firstly, we need to decide which type of form the web page is using. The two different methods are GET and POST and for this article, I’m going to assume you have a basic understanding of these methods and I’ll simply show you an example of how to implement either method rather than go into the differences between them. For both methods we’ll need a simple page to actually display the results so let’s start by making a page with a TextBox on it:

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”Default1.aspx.vb” Inherits=”Default1″ %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>Retrieve data from a web page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:TextBox ID=”TextBox1″ runat=”server” Rows=”40″ Columns=”100″ TextMode=”MultiLine”></asp:TextBox>
</div>
</form>
</body>
</html>

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”Default1.aspx.vb” Inherits=”Default1″ %> <!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”> <html xmlns=”http://www.w3.org/1999/xhtml” > <head runat=”server”> <title>Retrieve data from a web page</title> </head> <body> <form id=”form1″ runat=”server”> <div> <asp:TextBox ID=”TextBox1″ runat=”server” Rows=”40″ Columns=”100″ TextMode=”MultiLine”></asp:TextBox> </div> </form> </body> </html>

Posted in Asp.Net | Leave a Comment »

How To Disabling a button until processing is Complete

January 5, 2008 by venkatesh2urs

Here’s the scenario – let’s say you have an Insert subroutine, called ‘doInsert’.
You want to immediately disable the Submit button, so that the end-user won’t click it
multiple times, therefore, submitting the same data multiple times.

For this, use a regular HTML button, including a Runat=”server” and an ‘OnServerClick’
event designation – like this:

Then, in the very last line of the ‘doInsert’ subroutine, add this line:
Button1.enabled=”True”

Posted in Asp.Net | Leave a Comment »

How To Clear All Textboxes in ASP.NET

January 5, 2008 by venkatesh2urs

private void Button1_Click(object sender, System.EventArgs e)
{
Control myForm = Page.FindControl(“Form1″);

foreach (Control ctl in myForm.Controls)
{
if(ctl.GetType().ToString().Equals(“System.Web.UI.WebControls.TextBox”))
{
((TextBox)ctl).Text = “”;
}
}
}
This will clear EVERYTHING from the textboxes – even if you had them pre-populated
with data. A simple way to just reset it to the condition at Page_Load time, just
do this in the Reset SubRoutine: Server.Transfer(“YourPageName.aspx”)

Posted in Asp.Net | Leave a Comment »

Venkatesh2urs…

Basic- xml

Basic – 0ops

Dll Hell

C# – Questions

Sql questipn Part-1

.Net General

SQL SERVER INTERVIEW QUESTIONS

Tips

How To Disabling a button until processing is Complete

How To Clear All Textboxes in ASP.NET

Pages

Archives

Categories

Blogroll

Meta