Q1. What are all the different access specifiers in C#? Access modifiers are
Public
Private
Internal
Protected
Q2. What are delegates? Delegates are just like function pointers in C++, except that they are much safer to use due to their type safety. A delegate defines a function without implementing it and another class then provides the implementation. Events in C# are based on delegates, with the originator defining one or more callback functions.
Q3. What do you mean by type safety?
Type-safe code accesses only the memory locations it is authorized to access. (For this discussion, type safety specifically refers to memory type safety and should not be confused with type safety in a broader respect.) For example, type-safe code cannot read values from another object's private fields. It accesses types only in well-defined, allowable ways.
During just-in-time (JIT) compilation, an optional verification process examines the metadata and Microsoft intermediate language (MSIL) of a method to be JIT-compiled into native machine code to verify that they are type safe.
Q4. What is function overloading and function overriding with example?
Function overloading – function with same name with diff. signature in same class
Overloading allows a class to have several different methods of the same name as long as they accept a different list of parameters. It isn't the name of the parameters that matter either—it is their data types. Collectively, the data types of the parameters are called the method signature.
Private intQOH As Integer
Public ReadOnly Property QOH() As Integer
Get
Return intQOH
End Get
End Property
Public Sub Buy()
intQOH += 1
End Sub
This code simply adds a new property and method to the class. The method allows us to indicate that we've purchased a new copy of the book. It is somewhat limiting in that it only indicates the purchase of a single copy, so we might want another version of the method that accepts a parameter. Add the following code to the class:
Public Sub Buy(ByVal Quantity As Integer)
intQOH += Quantity
End Sub
overriding is the preferred method of altering or replacing a method on a base class because overriding is done with the permission of the base class designer.
In order to specify that a method can be overridden, that is, altered or replaced, by a subclass, the author of the method must use the Overridable keyword as part of the method declaration.
For instance, as we design the Book class we may realize that some subclasses in the future may need a different pricing scheme. To enable this, we can add the Overridable keyword to the Price declaration in the Book class:
Public Overridable Property Price() As Single
Get
Return sngPrice
End Get
Set(ByVal Value As Single
sngPrice = Value
End Set
End Property
This doesn't change any behavior in either the Book or UsedBook classes as they stand, but it does mean that the UsedBook class can now implement its own version of the Price property if we so desire. With used books, we may want to lower the price after they've been on the shelf for a while, so the Price implementation might be different. In the UsedBook class, add the following code:
Public Overrides Property Price() As Single
Get
If DateDiff(DateInterval.Day, dtPurchased, Now) > 365 Then
Return MyBase.Price / 2
Else
Return MyBase.Price
End If
End Get
Set(ByVal Value As Single)
MyBase.Price = Value
End Set
End Property
First, notice the use of the Overrides keyword in the method declaration. This is required to cause overriding to occur. Also, notice that we are altering the behavior of the existing method, not completely replacing it. The new code makes use of the MyBase keyword to invoke the Price property of the base class to store and retrieve the price value. In fact, storing the price value is handled entirely by the base class:
Set(ByVal Value As Single)
MyBase.Price = Value
End Set
The overridden base method must have the same signature as the override method.
Q5. How do we override a function in c#? Write an example.
using System;
class TestClass
{
public class Dimensions
{
public const double pi = Math.PI;
protected double x, y;
public Dimensions()
{
}
public Dimensions (double x, double y)
{
this.x = x;
this.y = y;
}
public virtual double Area()
{
return x*y;
}
}
public class Circle: Dimensions
{
public Circle(double r): base(r, 0)
{
}
public override double Area()
{
return pi * x * x;
}
}
class Sphere: Dimensions
{
public Sphere(double r): base(r, 0)
{
}
public override double Area()
{
return 4 * pi * x * x;
}
}
class Cylinder: Dimensions
{
public Cylinder(double r, double h): base(r, h)
{
}
public override double Area()
{
return 2*pi*x*x + 2*pi*x*y;
}
}
public static void Main()
{
double r = 3.0, h = 5.0;
Dimensions c = new Circle(r);
Dimensions s = new Sphere(r);
Dimensions l = new Cylinder(r, h);
// Display results:
Console.WriteLine("Area of Circle = {0:F2}", c.Area());
Console.WriteLine("Area of Sphere = {0:F2}", s.Area());
Console.WriteLine("Area of Cylinder = {0:F2}", l.Area());
}
}
Q6. If there are two interfaces having a same function which have same signature and we are implementing both the interfaces in C# class then how will we define the methods in our C# class?
Interface name dot method name will give you the access to particular method.
Public interface I1{
Public int ABC{
}
}
Public interface I2{
Public int ABC{
}
}
public class MNC:I1,I2{
{
public int I1.ABC{
}
public int I2.ABC{
}
}
}
Q7. How to get the name of a person who gets 2nd maximum salary in an Organization from Employee table? select empname,salary from employee a
where &n=(select count(*) from employee b
where b.salary>a.salary)
Q8. How to get the name,numbers of a person who had more than one phones? select empname,phoneno from employee e,employeephone p where e.empid in (select empid
from employeephone group by empid having count(empid)>1) and e.empid=p.empid
Q9. What is diff protected and protected internal and usage? Protected keyword is a member access modifier. A protected member is accessible from within the class in which it is declared, and from within any class derived from the class that declared this member.
A protected member of a base class is accessible in a derived class only if the access takes place through the derived class type. For example, consider the following code segment:
class A
{
protected int x = 123;
}
class B : A
{
void F()
{
A a = new A();
B b = new B();
a.x = 10; // Error
b.x = 10; // OK
}
}
Protected Internal – mainly used for use of whole assembly. And derived class. Generally we are not going to use.
Q10. we defined one page_load event in aspx page and same page_load event in code behind how will program run? Code behind code will run first.
Q11. one scenario was given of building, office, rooms, furniture, chairs etc. he wanted the class diagram for this.
Q12. Diff between props and methods when to use what? Properties are used to set and get a value to a object.
Methods are used to implement your business logic.
Q13. one array is there indexed 1 to 10.holding values 1 to 10.
now we make one value 0.now shuffle the array.now he wants the original value that was in place of 0.? For I = o to ubound(arrayname)
If I arrayname(i) then
Msgbox missed value is + I
End if
Next
Q14. you have a dataset 100 get records from 5 – 20 from it without for loop ? public DataRow[] Select(
string filterExpression,
string sort,
DataViewRowState recordStates
);
here rowstate will be Added CurrentRows Deleted ModifiedCurrent ModifiedOriginal None OriginalRows Unchanged
example
private static void GetRowsByFilter()
{
DataTable customerTable = new DataTable( "Customers" );
// Add columns
customerTable.Columns.Add( "id", typeof(int) );
customerTable.Columns.Add( "name", typeof(string) );
// Set PrimaryKey
customerTable.Columns[ "id" ].Unique = true;
customerTable.PrimaryKey = new DataColumn[] { customerTable.Columns["id"] };
// Add ten rows
for( int id=1; id<=10; id++ )
{
customerTable.Rows.Add(
new object[] { id, string.Format("customer{0}", id) } );
}
customerTable.AcceptChanges();
// Add another ten rows
for( int id=11; id 5";
// Sort descending by column named CompanyName.
strSort = "name DESC";
// Use the Select method to find all rows matching the filter.
DataRow[] foundRows =
customerTable.Select( strExpr, strSort, DataViewRowState.Added );
PrintRows( foundRows, "filtered rows" );
foundRows = customerTable.Select();
PrintRows( foundRows, "all rows" );
}
private static void PrintRows( DataRow[] rows, string label )
{
Console.WriteLine( "\n{0}", label );
if( rows.Length <= 0 )
{
Console.WriteLine( "no rows found" );
return;
}
foreach( DataRow r in rows )
{
foreach( DataColumn c in r.Table.Columns )
{
Console.Write( "\t {0}", r[c] );
}
Console.WriteLine();
}
}
Q15. What is the difference between remoting & web services ?
Q16.what do you mean by Singleton and SingleCall objects where you will use?
Q17.How to Intercepting soap messages in a webservices?
Q18.What do you mean by serialization and deserialization?
Q19. I have one XML data in cache in a ASP page. This XML file has to be saved to the database. I have a routine to save this xml. The question is – after saving the xml data it has to be cleared from the cache ? For this we have two solutions
1. by using Cache Object
2. using Outputcache page directives by passing parameters
Using Cache Object. The System.Web.Caching namespace provides classes for caching frequently used data on the server. This includes the Cache class, a dictionary that allows you to store arbitrary data objects, such as hash tables and data sets. It also provides expiration functionality for those objects, and methods that allow you to add and removed the objects. You can also add the objects with a dependency upon other files or cache entries, and perform a callback to notify your application when an object is removed from the Cache.
.add()
.remove()
Cache.Insert("MyData1", connectionString, new CacheDependency(Server.MapPath(\\myServer\myConfig.xml)));
Using OutputCache Page directive
Duration
The time, in seconds, that the page or user control is cached. Setting this attribute on a page or user control establishes an expiration policy for HTTP responses from the object and will automatically cache the page or user control output.
Note This attribute is required. If you do not include it, a parser error occurs.
Location
One of the OutputCacheLocation enumeration values. The default is Any.
CAUTION This attribute is not supported for @ OutputCache directives included in user controls (.ascx files).
Shared
A Boolean value that determines whether user control output can be shared with multiple pages. The default is false. For more information, see the Remarks section.
Note This attribute is not supported for @ OutputCache directives included in ASP.NET pages (.aspx files).
VaryByCustom
Any text that represents custom output caching requirements. If this attribute is given a value of browser, the cache is varied by browser name and major version information. If a custom string is entered, you must override the HttpApplication.GetVaryByCustomString method in your application's Global.asax file.
VaryByHeader
A semicolon-separated list of HTTP headers used to vary the output cache. When this attribute is set to multiple headers, the output cache contains a different version of the requested document for each specified header.
Note Setting the VaryByHeader attribute enables caching items in all HTTP 1.1 caches, not just the ASP.NET cache. This attribute is not supported for @ OutputCache directives in user controls.
VaryByParam
A semicolon-separated list of strings used to vary the output cache. By default, these strings correspond to a query string value sent with GET method attributes, or a parameter sent using the POST method. When this attribute is set to multiple parameters, the output cache contains a different version of the requested document for each specified parameter. Possible values include none, *, and any valid query string or POST parameter name.
CAUTION This attribute is required when you output cache ASP.NET pages. It is required for user controls as well unless you have included a VaryByControl attribute in the control's @ OutputCache directive. A parser error occurs if you fail to include it. If you do not want to specify a parameter to vary cached content, set the value to none. If you want to vary the output cache by all parameter values, set the attribute to *.
VaryByControl
A semicolon-separated list of strings used to vary a user control's output cache. These strings represent the ID property values of ASP.NET server controls declared in the user control. For more information, see Caching Portions of an ASP.NET Page.
Note This attribute is required in a user control @ OutputCache directive unless you have included a VaryByParam attribute. This attribute is not supported for @ OutputCache directives in ASP.NET pages.
Q20. What is MSDTC? Explain on that. Microsoft® SQL Server™ 2000 Data Transformation Services (DTS) is a set of graphical tools and programmable objects that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations.
• Importing and exporting data.
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
• Transforming data.
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
• Copying database objects.
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Note There are restrictions on this capability. For more information, see Copy SQL Server Objects Task.
• Sending and receiving messages to and from other users and packages.
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
• Executing a set of Transact-SQL statements or Microsoft ActiveX® scripts against a data source.
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Q21.I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated. GROUP BY HAVING
Q22.Can you pass SOAP messages through remoting? Yes. IHTTP Interface
Q23. what is an assembly ?
Q24.what is CLR?
Q25.What is static Constructor? Without initializing any thing to make use the variable
Q26.How to implement Multiple Inheritence in C#?
Q27. Iterface IPrint()
{
String Display();
{
Iterface IWrite()
{
String Display();
{
Class PrintDoc:IPrint,IWrite
{
//Here is implementation
}
how to implement the Display in the class printDoc (How to resolve the naming Conflict)?
Class PrintDoc:IPrint,IWrite
{
//Here is implementation
Public Int IPrint.Display()
{
}
Public Int IWrite.Display()
{
}
}
Q28.
Class Token()
{
Display()
{
//Implementation goes here
}
}
Calss IdentifierToken:Token
{
new Display()
//What is the use of new keyword
METHOD OVERLOADING AND HIDING BASE CLASS METHOD
{
//Implementation goes here
}
}
Static Void Method(Token t)
{
Console.Write(t.Display());
}
Public static Void Main()
{
IdentifierToken Variable=new IdentifierToken();
Method(Variable);
//Which Class Method is called here
DERIVED CLASS
Q29.
Class Token()
{
Public Virtual String Display()
{
//Implementation goes here
}
}
Calss IdentifierToken:Token
{
Public Override String Display()
{
//Implementation goes here
}
}
Static Void Method(Token t)
{
Console.Write(t.Display());
}
Public static Void Main()
{
IdentifierToken Variable=new IdentifierToken();
Method(Variable); //Which Class Method is called here
}
Q30. Difference between Readonly and constant. ? Constants fields are used to store values that do not change during the runtime of a program. A good example of a constant would be PI(3.14) which will not change in any circumstance. Once we specify a field as constant a value can be assigned to it only during initialization and cannot be changed aftwerwards.The syntax for declaring a constant is
const datatype variable name = value;
Q31. Difference between instance and static constructors. ?
Q32 .when a static constructor is called.?
When class initialized
Q33.What are the series of steps that happen on execution of a query in a Query Analyzer?
The basic steps that SQL Server uses to process a single SELECT statement are:
1. The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
2. A query tree, sometimes called a sequence tree, is built describing the logical steps needed to transform the source data into the format needed by the result set.
3. The query optimizer analyzes all the ways the source tables can be accessed and selects the series of steps that returns the results fastest while consuming fewer resources. The query tree is updated to record this exact series of steps, and the final, optimized version of the query tree is called the execution plan.
4. The relational engine begins executing the execution plan. As steps that need data from the base tables are processed, the relational engine uses OLE DB to request that the storage engine pass up data from the rowsets requested from the relational engine.
5. The relational engine processes the data returned from the storage engine into the format defined for the result set, and returns the result set to the client.
Q34.What are Clustered and Non-clustered indexes? When do u use each of them? What is the data that is stored on applying either a clustered or a non-clustered index? He basically asked questions related to the architecture of indexes.
Clustered Indexes
Clustered indexes have one row in sysindexes with indid = 1. The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point the key value in the inserted row fits in the ordering sequence.
Microsoft® SQL Server™ 2000 indexes are organized as B-trees. Each page in an index holds a page header followed by index rows. Each index row contains a key value and a pointer to either a lower-level page or a data row. Each page in an index is called an index node. The top node of the B-tree is called the root node. The bottom layer of nodes in the index are called the leaf nodes. The pages in each level of the index are linked together in a doubly-linked list. In a clustered index, the data pages make up the leaf nodes. Any index levels between the root and the leaves are collectively known as intermediate levels.
For a clustered index, sysindexes.root points to the top of the clustered index. SQL Server navigates down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server navigates through the index to find the starting key value in the range, and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.
This illustration shows the structure of a clustered index.
Nonclustered Indexes
Nonclustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
• The data rows are not sorted and stored in order based on their nonclustered keys.
• The leaf layer of a nonclustered index does not consist of the data pages.
Instead, the leaf nodes contain index rows. Each index row contains the nonclustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.
Nonclustered indexes can be defined on a table with a clustered index, a heap, or an indexed view. In Microsoft® SQL Server™ 2000, the row locators in nonclustered index rows have two forms:
• If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The entire pointer is known as a Row ID.
• If the table does have a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 makes duplicate keys unique by adding an internally generated value. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.
Q35.There is table that only has only non-clustered index. Now, to the table a new clustered is applied. What will happen to the data in the non-clustered before and after applying the clustered index. Will it be the same or not?
DATA won’t be changed but the previous nonclusted indexes which directly pointing to database is dropped and new cluster index is created and new data file also creates.
Q36.What are functions in SQL Server? Why do we use them? What are the different types of user defined function that we have?
Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. Microsoft® SQL Server™ 2000 does not limit users to the built-in functions defined as part of the Transact-SQL language, but allows users to create their own user-defined functions.
User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (database_name.owner_name.function_name) must be unique.
You must have been granted CREATE FUNCTION permissions to create, alter, or drop user-defined functions. Users other than the owner must be granted appropriate permissions on a function before they can use it in a Transact-SQL statement. To create or alter tables with references to user-defined functions in the CHECK constraint, DEFAULT clause, or computed column definition, you must also have REFERENCES permission on the functions.
Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. In functions, such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled.
SQL Server 2000 supports three types of user-defined functions:
• Scalar functions
• Inline table-valued functions
• Multistatement table-valued functions
A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword default DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.
Scalar functions return a single data value of the type defined in a RETURNS clause. All scalar data types, including bigint and sql_variant, can be used. The timestamp data type, user-defined data type, and nonscalar types, such as table or cursor, are not supported. The body of the function, defined in a BEGIN...END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.
Table-valued functions return a table. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. For a multistatement table-valued function, the function body, defined in a BEGIN...END block, contains the TRANSACT-SQL statements that build and insert rows into the table that will be returned. For more information about inline table-valued functions, see Inline User-Defined Functions. For more information about table-valued functions, see User-Defined Functions That Return a table Data Type.
The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.
The types of statements that are valid in a function include:
• DECLARE statements can be used to define data variables and cursors that are local to the function.
• Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
• Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.
• Control-of-flow statements.
• SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
• UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
• EXECUTE statements calling an extended stored procedure.
The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.
Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are:
@@CONNECTIONS @@PACK_SENT GETDATE
@@CPU_BUSY @@PACKET_ERRORS GetUTCDate
@@IDLE @@TIMETICKS NEWID
@@IO_BUSY @@TOTAL_ERRORS RAND
@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
@@PACK_RECEIVED @@TOTAL_WRITE
Schema-Bound Functions
CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. An attempt to alter or drop any object referenced by a schema-bound function fails.
These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:
• All views and user-defined functions referenced by the function must be schema-bound.
• All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names.
• You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.
You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.
When calling a scalar user-defined function, you must supply at least a two-part name:
SELECT *, MyUser.MyScalarFunction()
FROM MyTable
Table-valued functions can be called by using a one-part name:
SELECT *
FROM MyTableFunction()
However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:
SELECT * FROM ::fn_helpcollations()
A scalar function can be referenced any place an expression of the same data type returned by the function is allowed in a Transact-SQL statement, including computed columns and CHECK constraint definitions. For example, this statement creates a simple function that returns a decimal:
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:
CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)
dbo.CubicVolume is an example of a user-defined function that returns a scalar value. The RETURNS clause defines a scalar data type for the value returned by the function. The BEGIN...END block contains one or more Transact-SQL statements that implement the function. Each RETURN statement in the function must have an argument that returns a data value that has the data type specified in the RETURNS clause, or a data type that can be implicitly converted to the type specified in RETURNS. The value of the RETURN argument is the value returned by the function.
Q37. what is fill factor in indexes ?
CREATE NONCLUSTERED INDEX zip_ind
ON authors (zip)
WITH FILLFACTOR = 100
When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the values in the indexed columns are changed, Microsoft® SQL Server™ 2000 may have to reorganize the storage of the data in the table to make room for the new row and maintain the ordered storage of the data. This also applies to nonclustered indexes. When data is added or changed, SQL Server may have to reorganize the storage of the data in the nonclustered index pages. When a new row is added to a full index page, SQL Server moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. Page splitting can impair performance and fragment the storage of the data in a table.
When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table's data and reduce the potential for page splits. The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.
The fill factor option is provided for fine-tuning performance. However, the server-wide default fill factor, specified using the sp_configure system stored procedure, is the best choice in the majority of situations.
Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can degrade database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 percent can cause database read performance to degrade by two times.
It is useful to set the fill factor option to another value only when a new index is created on a table with existing data, and then only when future changes in that data can be accurately predicted.
The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. Trying to maintain the extra space on the data pages would defeat the purpose of originally using the fill factor because SQL Server would have to perform page splits to maintain the percentage of free space, specified by the fill factor, on each page as data is entered. Therefore, if the data in the table is significantly modified and new data added, the empty space in the data pages can fill. In this situation, the index can be re-created and the fill factor specified again to redistribute the data.
Q38.What are stored procedures and why do we use them?
Pre Complied Group of T-SQL statement to with conditions
A. Use a simple procedure with a complex SELECT
This stored procedure returns all authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure does not use any parameters.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info_all' AND type = 'P')
DROP PROCEDURE au_info_all
GO
CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
GO
The au_info_all stored procedure can be executed in these ways:
EXECUTE au_info_all
-- Or
EXEC au_info_all
Or, if this procedure is the first statement within the batch:
au_info_all
B. Use a simple procedure with parameters
This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure accepts exact matches for the parameters passed.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info' AND type = 'P')
DROP PROCEDURE au_info
GO
USE pubs
GO
CREATE PROCEDURE au_info
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
The au_info stored procedure can be executed in these ways:
EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'
Or, if this procedure is the first statement within the batch:
au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'
C. Use a simple procedure with wildcard parameters
This stored procedure returns only the specified authors (first and last names supplied), their titles, and their publishers from a four-table join. This stored procedure pattern matches the parameters passed or, if not supplied, uses the preset defaults.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
GO
The au_info2 stored procedure can be executed in many combinations. Only a few combinations are shown here:
EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or
EXECUTE au_info2 @firstname = 'A%'
-- Or
EXECUTE au_info2 '[CK]ars[OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'
D. Use OUTPUT parameters
OUTPUT parameters allow an external procedure, a batch, or more than one Transact-SQL statements to access a value set during the procedure execution. In this example, a stored procedure (titles_sum) is created and allows one optional input parameter and one output parameter.
First, create the procedure:
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name = 'titles_sum' AND type = 'P')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO
Next, use the OUTPUT parameter with control-of-flow language.
Note The OUTPUT variable must be defined during the table creation as well as during use of the variable.
The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @@SUM = variable is used).
DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
PRINT ' '
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
SELECT 'The total cost of these titles is $'
+ RTRIM(CAST(@@TOTALCOST AS varchar(20)))
Here is the result set:
Title Name
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking
(3 row(s) affected)
Warning, null value eliminated from aggregate.
All of these titles can be purchased for less than $200.
E. Use an OUTPUT cursor parameter
OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.
First, create the procedure that declares and then opens a cursor on the titles table:
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles
OPEN @titles_cursor
GO
Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.
USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO
F. Use the WITH RECOMPILE option
The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure will not be typical, and when a new execution plan should not be cached or stored in memory.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_by_author' AND type = 'P')
DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
title AS Title
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO
G. Use the WITH ENCRYPTION option
The WITH ENCRYPTION clause hides the text of a stored procedure from users. This example creates an encrypted procedure, uses the sp_helptext system stored procedure to get information on that encrypted procedure, and then attempts to get information on that procedure directly from the syscomments table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
EXEC sp_helptext encrypt_this
Here is the result set:
The object's comments have been encrypted.
Next, select the identification number and text of the encrypted stored procedure contents.
SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.name = 'encrypt_this'
Here is the result set:
Note The text column output is shown on a separate line. When executed, this information appears on the same line as the id column information.
id text
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????
(1 row(s) affected)
H. Create a user-defined system stored procedure
This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_showindexes' AND type = 'P')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO
Here is the result set:
TABLE_NAME INDEX_NAME INDEX_ID
---------------- ---------------- ----------------
employee employee_ind 1
employee PK_emp_id 2
(2 row(s) affected)
I. Use deferred name resolution
This example shows four procedures and the various ways that deferred name resolution can be used. Each stored procedure is created, although the table or column referenced does not exist at compile time.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc1' AND type = 'P')
DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
SELECT *
FROM does_not_exist
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc2' AND type = 'P')
DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
DECLARE @middle_init char(1)
SET @middle_init = NULL
SELECT au_id, middle_initial = @middle_init
FROM authors
GO
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'
Q39.What are the differences between functions and stored procedures?
Q40.What are cursors and why do we use them? How do you declare a cursor? How do you declare a simplest cursor? Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one row or a small block of rows at a time. Cursors are an extension to result sets that provide that mechanism.
Cursor process
1. Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
2. Execute the Transact-SQL statement to populate the cursor.
3. Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
4. Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
5. Close the cursor.
DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor
OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title
IF @@FETCH_STATUS 0
PRINT " <>"
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END
CLOSE titles_cursor
DEALLOCATE titles_cursor
Cursor Types
Static cursors
The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement. If rows making up the result set are updated by other users, the new data values are not displayed in the static cursor. The static cursor displays rows deleted from the database after the cursor was opened. No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened), not even modifications made using the same connection that opened the cursor.Microsoft® SQL Server™ static cursors are always read-only.Because the result set of a static cursor is stored in a work table in tempdb, the size of the rows in the result set cannot exceed the maximum row size for a SQL Server table.
Dynamic cursors
Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor. Updates are visible immediately if they are made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are not visible until they are committed, unless the cursor transaction isolation level is set to read uncommitted.
Forward-only cursors
A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor, except for the case where a value used to determine the location of the row within the result set is modified, such as updating a column covered by a clustered index.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, Microsoft® SQL Server™ 2000 does not. SQL Server considers both forward-only and scroll as options that can be applied to static, keyset-driven, and dynamic cursors. Transact-SQL cursors support forward-only static, keyset-driven, and dynamic cursors. The database API cursor models assume that static, keyset-driven, and dynamic cursors are always scrollable. When a database API cursor attribute or property is set to forward-only, SQL Server implements this as a forward-only dynamic cursor.
Keyset-driven cursors
The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.
Changes to data values in nonkeyset columns (made by the cursor owner or committed by other users) are visible as the user scrolls through the cursor. Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened. Inserts made through the cursor using an API function such as the ODBC SQLSetPos function are visible at the end of the cursor. @@FETCH_STATUS returns a "row missing" status when an attempt is made to fetch a row deleted after the cursor was opened. An update to a key column operates like a delete of the old key value followed by an insert of the new key value. The new key value is not visible if the update was not made through the cursor; it is visible at the end of the cursor if the update was made through the cursor using either an API function such as SQLSetPos or the Transact-SQL WHERE CURRENT OF clause and the SELECT statement did not contain a JOIN condition in the FROM clause. The new key value is not visible if the insert contained a remote table in the FROM clause. Attempts to retrieve the old key value get the same missing row fetch status as a deleted row.
Q41.Suppose, I have a dynamic cursor attached to table in a database. I have another means by which I will modify the table. What do you think will the values in the cursor be, when use the cursor?
When you move next record in cursor new inserted values fetched into cursor.
Q42.What are joins? What are the different kinds of joins? What will happen to the rows the left outer join when there are no matching rows in the left table? What is the difference between inner and outer joins?
Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.
Joins can be categorized as:
• Inner joins (the typical join operation, which uses some comparison operator like = or ). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.
• Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:
o LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
o RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
o FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
• Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.
For example, here is an inner join retrieving the authors who live in the same city and state as a publisher:
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
Microsoft® SQL Server™ 2000 uses these SQL-92 keywords for outer joins specified in a FROM clause:
• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.
Using Left Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson).
To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. The following is the query and results of the Transact-SQL left outer join:
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name
-------------------- ------------------------------ -----------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
(23 row(s) affected)
The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.
Using Right Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.
To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name
-------------------- ------------------------ --------------------
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books
(9 row(s) affected)
An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold fewer than 50 copies:
USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
ON s.title_id = t.title_id
AND s.qty > 50
ORDER BY s.stor_id ASC
Here is the result set:
stor_id qty title
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets
(null) (null) Emotional Security: A New Algorithm
(null) (null) Fifty Years in Buckingham Palace Kitchens
7066 75 Is Anger the Enemy?
(null) (null) Life Without Fear
(null) (null) Net Etiquette
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the
Mediterranean
(null) (null) Prolonged Data Deprivation: Four Case Studies
(null) (null) Secrets of Silicon Valley
(null) (null) Silicon Valley Gastronomic Treats
(null) (null) Straight Talk About Computers
(null) (null) Sushi, Anyone?
(null) (null) The Busy Executive's Database Guide
(null) (null) The Gourmet Microwave
(null) (null) The Psychology of Computer Cooking
(null) (null) You Can Combat Computer Stress!
(18 row(s) affected)
For more information about predicates, see WHERE.
Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ 2000 provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.
To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. The following is the query and results of the Transact-SQL full outer join:
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name
-------------------- ---------------------------- --------------------
Reginald Blotchet-Halls NULL
Michel DeFrance NULL
Innes del Castillo NULL
Ann Dull NULL
Marjorie Green NULL
Morningstar Greene NULL
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns MacFeather NULL
Heather McBadden NULL
Michael O'Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
NULL NULL Binnet & Hardley
NULL NULL Five Lakes Publishing
NULL NULL GGG&G
NULL NULL Lucerne Publishing
NULL NULL New Moon Books
NULL NULL Ramona Publishers
NULL NULL Scootney Books
(30 row(s) affected)
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. This is an example of a Transact-SQL cross join:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The result set contains 184 rows (authors has 23 rows and publishers has 8; 23 multiplied by 8 equals 184).
However, if a WHERE clause is added, the cross join behaves as an inner join. For example, these Transact-SQL queries produce the same result set:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC
-- Or
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
ORDER BY au_lname DESC
A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same ZIP Code area.
Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:
USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
ORDER BY au1.au_fname ASC, au1.au_lname ASC
Null Values and Joins
When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE clause excludes null values).
Here are two tables that each have NULL in the column that will participate in the join:
table1 table2
a b c d
------- ------ ------- ------
1 one NULL two
NULL three 4 four
4 join4
A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:
SELECT *
FROM table1 t1 JOIN table2 t2
ON t1.a = t2.c
ORDER BY t1.a
Only one row with 4 in column a and c is returned:
a b c d
----------- ------ ----------- ------
4 join4 4 four
(1 row(s) affected)
Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. For example, this SELECT statement does a left outer join on these two tables:
SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
ON t1.a = t2.c
ORDER BY t1.a
Here is the result set:
a b c d
----------- ------ ----------- ------
NULL three NULL NULL
1 one NULL NULL
4 join4 4 four
(3 row(s) affected)
The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
Q43.What are ‘jobs’ in SQL Server? How do we create one? A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command line applications, and Microsoft® ActiveX® scripts. Jobs can be created to run tasks that are often repeated or schedulable, and they can automatically notify users of job status by generating alerts.
Q44.What are the different types of Back-ups possible in SQL Server and how would you do that? • Full backup A full database backup basically copies all the pages from a database onto a backup device, which can be a local or network disk file, a local tape drive, or even a named pipe.
• Differential backup A differential backup copies only the extents that were changed since the last full backup was made. The extents are copied onto a specified backup device. SQL Server can quickly tell which extents need to be backed up by examining the bits on the DCM pages for each data file in the database. Each time a full backup is made, all the bits are cleared to 0. When any page in an extent is changed, its corresponding bit in the DCM page is changed to 1.
• Log backup In most cases, a log backup copies all the log records that have been written to the transaction log since the last full or log backup was made. However, the exact behavior of the BACKUP LOG command depends on your database's recovery mode setting. I'll discuss recovery modes shortly.
Q46.What is Candidate key, alternate key and 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.
Q47.What are the different error handling mechanisms we have in vb.net compared to VB 6.0? Visual Basic 6.0 error handling mechanism
.NET Framework provides structured exception handling, using the Try, Catch, Finally, and Throw keywords in Visual Basic .NET. This type of error handling has been available, in some fashion, in C++ for a number of years. With the release of the .NET common language runtime, this type of error handling is available to all the .NET languages including, Visual Basic .NET.
• Visual Basic 6.0 requires you to jump around within procedures, in order to handle errors. The On Error Goto, Resume, and Resume Next statements all involve jumping forwards or backwards in code. The standard Visual Basic 6.0 error-handling techniques involve at least one jump, and often more, within a procedure (one forward to the error handling block, and a second back to a common procedure exit point.)
• If you follow good programming practices in Visual Basic 6.0, including ensuring that your procedures have only a single exit point, the most convenient place to put that exit point is in the middle of procedures (before the error-handling block), making it easy to forget the important Exit Sub or Exit Function.
• There is no way to push and pop error handlers in Visual Basic 6.0. If you want to preserve the current error trap, set up a different one, and then return back to the first one. You must remember to include the correct On Error Goto… statement every time you want to change handlers.
• Visual Basic 6.0 includes only a single Err object. If an error occurs, and you don't handle that error immediately, you may have permanently lost the error information before you get a chance to handle the error.
• The Visual Basic 6.0 documentation includes almost no coverage of the types of errors (that is, the error numbers) you might receive because of an action you've taken in your code. Your only recourse is to experiment, see what error numbers you can generate by triggering errors while testing, and trap those specific errors in your code.
• Visual Basic developers were perfectly capable of using the Err.Raise method to raise errors back to calling procedures, this technique never became a standard. Many developers creating code that is called by others simply return an error value to indicate success or failure, instead of raising an error on failure. Because it's possible (and easy) to simply disregard error values returned from procedures you call, in too many cases, code that fails for any reason at runtime never raises the appropriate errors back to its callers.
DOT Net error handling mechanism
Error handling in .NET is based on the Exception class, which contains information about not only the current error, but a linked list of errors that may have triggered the current error.
• You can inherit from the Exception class, creating your own exceptions that have the same functionality as the base class, or you can create extended functionality as necessary. Because your code can trap for specific exceptions, creating your own Exception class gives you a lot of flexibility.
• Because every class in the .NET framework throws exceptions when it encounters runtime errors, developers will get in the habit of trapping for exceptions and handling them. This makes it more likely that exceptions you throw from within your components will be successfully handled.
• You can nest Try/Catch blocks within the Try, Catch, or Finally blocks. This gives developers the capability of managing exception handling to any level of granularity that they require.
Q48. What are virtual destructors? A virtual destructor is an essential entity if you use inheritance. Virtual destructor is declared in a base class. It guarantees that for all classes derived from the base class all destructors: 1) will be called and 2) will be called in proper order. It makes available a polymorphic behavior. Take a look at the snippet below:
#include "stdio.h"
class BaseClass
{
int id;
public:
BaseClass() { printf("BaseClass()\n"); }
virtual ~BaseClass() { printf("~BaseClass()\n"); } // !!!
};
class Class1 : public BaseClass
{
int id;
public:
Class1() { printf("Class1()\n"); }
~Class1() { printf("~Class1()\n"); }
};
class Class2 : public Class1
{
int id;
public:
Class2() { printf("Class2()\n"); }
~Class2() { printf("~Class2()\n"); }
};
int main(int argc, char* argv[])
{
BaseClass *p = new Class2;
delete p;
return 0;
}
The output in case without virtual destructor is:
BaseClass()
Class1()
Class2()
~BaseClass()
But the output in case with virtual destructor differs:
BaseClass()
Class1()
Class2()
~Class2()
~Class1()
~BaseClass()
Q49. what is use of ‘USING’ in sub or function ?
The using statement defines a scope at the end of which an object will be disposed.
using (expression | type identifier = initializer) statement
where:
expression
An expression you want to call Dispose on upon exiting the using statement.
type
The type of identifier.
identifier
The name, or identifier, of the type type. It is possible to define more than one identifier of type type. Precede each identifier = initializer with a comma.
initializer
An expression that creates an object.
statement
The embedded statement or statements to executed.
Remarks
You create an instance in a using statement to ensure that Dispose is called on the object when the using statement is exited. A using statement can be exited either when the end of the using statement is reached or if, for example, an exception is thrown and control leaves the statement block before the end of the statement.
The object you instantiate must implement the System.IDisposable interface.
Example
// cs_using_statement.cs
// compile with /reference:System.Drawing.dll
using System.Drawing;
class a
{
public static void Main()
{
using (Font MyFont = new Font("Arial", 10.0f), MyFont2 = new Font("Arial", 10.0f))
{
// use MyFont and MyFont2
} // compiler will call Dispose on MyFont and MyFont2
Font MyFont3 = new Font("Arial", 10.0f);
using (MyFont3)
{
// use MyFont3
} // compiler will call Dispose on MyFont3
}
}
Q50. What are abstract, virtual, static, friend, interface keywords? When do we use each and what does each mean? And related questions. Abstract:
The abstract modifier is used to indicate that a class is incomplete and that it is intended to be used only as a base class. An abstract class differs from a non-abstract class in the following ways:
• An abstract class cannot be instantiated directly, and it is a compile-time error to use the new operator on an abstract class. While it is possible to have variables and values whose compile-time types are abstract, such variables and values will necessarily either be null or contain references to instances of non-abstract classes derived from the abstract types.
• An abstract class is permitted (but not required) to contain abstract members.
• An abstract class cannot be sealed.
When a non-abstract class is derived from an abstract class, the non-abstract class must include actual implementations of all inherited abstract members, thereby overriding those abstract members. In the example
abstract class A
{
public abstract void F();
}
abstract class B: A
{
public void G() {}
}
class C: B
{
public override void F() {
// actual implementation of F
}
}
the abstract class A introduces an abstract method F. Class B introduces an additional method G, but since it doesn't provide an implementation of F, B must also be declared abstract. Class C overrides F and provides an actual implementation. Since there are no abstract members in C, C is permitted (but not required) to be non-abstract.
Virtual
The virtual keyword is used to modify a method or property declaration, in which case the method or the property is called a virtual member. The implementation of a virtual member can be changed by an overriding member in a derived class.
When a virtual method is invoked, the run-time type of the object is checked for an overriding member. The overriding member in the most derived class is called, which might be the original member, if no derived class has overridden the member.
By default, methods are non-virtual. You cannot override a non-virtual method.
You cannot use the virtual modifier with the following modifiers:
static abstract override
Virtual properties behave like abstract methods, except for the differences in declaration and invocation syntax.
• It is an error to use the virtual modifier on a static property.
• A virtual inherited property can be overridden in a derived class by including a property declaration that uses the override modifier.
For more information on virtual methods, see 10.5.3 Virtual methods.
Example
In this example, the class Dimensions contains the two coordinates x, y, and the Area() virtual method. Different shape classes such as Circle, Cylinder, and Sphere inherit the Dimensions class, and the surface area is calculated for each figure. Each derived class has it own override implementation of Area(). The program calculates and displays the proper area for each figure by invoking the proper implementation of Area() according to the object associated with the method.
// cs_virtual_keyword.cs
// Virtual and override
using System;
class TestClass
{
public class Dimensions
{
public const double pi = Math.PI;
protected double x, y;
public Dimensions()
{
}
public Dimensions (double x, double y)
{
this.x = x;
this.y = y;
}
public virtual double Area()
{
return x*y;
}
}
public class Circle: Dimensions
{
public Circle(double r): base(r, 0)
{
}
public override double Area()
{
return pi * x * x;
}
}
class Sphere: Dimensions
{
public Sphere(double r): base(r, 0)
{
}
public override double Area()
{
return 4 * pi * x * x;
}
}
class Cylinder: Dimensions
{
public Cylinder(double r, double h): base(r, h)
{
}
public override double Area()
{
return 2*pi*x*x + 2*pi*x*y;
}
}
public static void Main()
{
double r = 3.0, h = 5.0;
Dimensions c = new Circle(r);
Dimensions s = new Sphere(r);
Dimensions l = new Cylinder(r, h);
// Display results:
Console.WriteLine("Area of Circle = {0:F2}", c.Area());
Console.WriteLine("Area of Sphere = {0:F2}", s.Area());
Console.WriteLine("Area of Cylinder = {0:F2}", l.Area());
}
}
Output
Area of Circle = 28.27
Area of Sphere = 113.10
Area of Cylinder = 150.80
In the preceding example, notice that the inherited classes Circle, Sphere, and Cylinder are all using constructors that initialize the base class, for example:
public Cylinder(double r, double h): base(r, h) {}
This is analogous to the C++ initialization list.
Virtual methods
When an instance method declaration includes a virtual modifier, that method is said to be a virtual method. When no virtual modifier is present, the method is said to be a non-virtual method.
The implementation of a non-virtual method is invariant: The implementation is the same whether the method is invoked on an instance of the class in which it is declared or an instance of a derived class. In contrast, the implementation of a virtual method can be superseded by derived classes. The process of superseding the implementation of an inherited virtual method is known as overriding that method (Section 10.5.4).
In a virtual method invocation, the run-time type of the instance for which that invocation takes place determines the actual method implementation to invoke. In a non-virtual method invocation, the compile-time type of the instance is the determining factor. In precise terms, when a method named N is invoked with an argument list A on an instance with a compile-time type C and a run-time type R (where R is either C or a class derived from C), the invocation is processed as follows:
• First, overload resolution is applied to C, N, and A, to select a specific method M from the set of methods declared in and inherited by C. This is described in Section 7.5.5.1.
• Then, if M is a non-virtual method, M is invoked.
• Otherwise, M is a virtual method, and the most derived implementation of M with respect to R is invoked.
For every virtual method declared in or inherited by a class, there exists a most derived implementation of the method with respect to that class. The most derived implementation of a virtual method M with respect to a class R is determined as follows:
• If R contains the introducing virtual declaration of M, then this is the most derived implementation of M.
• Otherwise, if R contains an override of M, then this is the most derived implementation of M.
• Otherwise, the most derived implementation of M with respect to R is the same as the most derived implementation of M with respect to the direct base class of R.
The following example illustrates the differences between virtual and non-virtual methods:
using System;
class A
{
public void F() { Console.WriteLine("A.F"); }
public virtual void G() { Console.WriteLine("A.G"); }
}
class B: A
{
new public void F() { Console.WriteLine("B.F"); }
public override void G() { Console.WriteLine("B.G"); }
}
class Test
{
static void Main() {
B b = new B();
A a = b;
a.F();
b.F();
a.G();
b.G();
}
}
In the example, A introduces a non-virtual method F and a virtual method G. The class B introduces a new non-virtual method F, thus hiding the inherited F, and also overrides the inherited method G. The example produces the output:
A.F
B.F
B.G
B.G
Notice that the statement a.G() invokes B.G, not A.G. This is because the run-time type of the instance (which is B), not the compile-time type of the instance (which is A), determines the actual method implementation to invoke.
Because methods are allowed to hide inherited methods, it is possible for a class to contain several virtual methods with the same signature. This does not present an ambiguity problem, since all but the most derived method are hidden. In the example
using System;
class A
{
public virtual void F() { Console.WriteLine("A.F"); }
}
class B: A
{
public override void F() { Console.WriteLine("B.F"); }
}
class C: B
{
new public virtual void F() { Console.WriteLine("C.F"); }
}
class D: C
{
public override void F() { Console.WriteLine("D.F"); }
}
class Test
{
static void Main() {
D d = new D();
A a = d;
B b = d;
C c = d;
a.F();
b.F();
c.F();
d.F();
}
}
the C and D classes contain two virtual methods with the same signature: The one introduced by A and the one introduced by C. The method introduced by C hides the method inherited from A. Thus, the override declaration in D overrides the method introduced by C, and it is not possible for D to override the method introduced by A. The example produces the output:
B.F
B.F
D.F
D.F
Note that it is possible to invoke the hidden virtual method by accessing an instance of D through a less derived type in which the method is not hidden.
Static
Use the static modifier to declare a static member, which belongs to the type itself rather than to a specific object. The static modifier can be used with fields, methods, properties, operators, events and constructors, but cannot be used with indexers, destructors, or types.
Remarks
• A constant or type declaration is implicitly a static member.
• A static member cannot be referenced through an instance. Instead, it is referenced through the type name. For example, consider the following class:
public class MyBaseC
{
public struct MyStruct {
public static int x = 100;
}
}
To refer to the static member x, use the fully qualified name (unless it is accessible from the same scope):
MyBaseC.MyStruct.x
• While an instance of a class contains a separate copy of all instance fields of the class, there is only one copy of each static field.
• It is not possible to use this to reference static methods or property accessors.
Note The static keyword is more limited in use than in C++. To compare with the C++ keyword, see static in the C++ Language Reference.
To demonstrate instance members, consider a class that represents a company employee. Assume that the class contains a method to count employees and a field to store the number of employees. Both the method and the field do not belong to any instance employee. Instead they belong to the company class. Therefore, they should be declared as static members of the class.
Interfaces
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.
An interface represents a contract, in that a class that implements an interface must implement every aspect of that interface exactly as it is defined.
With interfaces, you can define features as small groups of closely related members. You can develop enhanced implementations for your interfaces without jeopardizing existing code, thus minimizing compatibility problems. You can also add new features at any time by developing additional interfaces and implementations.
Although interface implementations can evolve, interfaces themselves cannot be changed once published. Changes to a published interface may break existing code. If you think of an interface as a contract, it is clear that both sides of the contract have a role to play. The publisher of an interface agrees never to change that interface, and the implementer agrees to implement the interface exactly as it was designed.
Versions of Visual Basic prior to Visual Basic .NET could consume interfaces but not create them directly. Visual Basic .NET allows you to define true interfaces using the Interface statement, and to implement interfaces with an improved version of the Implements keyword.
Q51. What is a static constructor? A static constructor is used to initialize a class and is called automatically to initialize the class before the static members are accessed.
A static constructor is only called once throughout the application's lifetime. The static constructor method is called when any of the defining class' members are first accessed. The constructor is called once only.
public class myClass {
// a constructor method has the same name as that of the class containing it
// constructors have no return types
static myClass() {
}
}
Q52. What is shared keyword? Should we have only shared methods in a shared class? The Shared keyword indicates that one or more declared programming elements are shared. Shared elements are not associated with a specific instance of a class or structure. You can access them by qualifying them either with the class or structure name, or with the variable name of a specific instance of the class or structure.
Q53. What are namespaces and why do we use them? Namespaces organize the objects defined in an assembly. Assemblies can contain multiple namespaces, which can in turn contain other namespaces. Namespaces prevent ambiguity and simplify references when using large groups of objects such as class libraries.
For example, Visual Studio .NET defines the ListBox class in the System.Windows.Forms namespace. The following code fragment shows how to declare a variable using the fully qualified name for this class:
Dim LBox As System.Windows.Forms.ListBox
Namespaces address a problem sometimes known as namespace pollution, in which the developer of a class library is hampered by the use of similar names in another library. These conflicts with existing components are sometimes called name collisions.
For example, if you create a new class named ListBox, you can use it inside your project without qualification. However, if you want to use the Visual Studio .NET ListBox class in the same project, you must use a fully qualified reference to make the reference unique. If the reference is not unique, Visual Basic .NET produces an error stating that the name is ambiguous. The following code example demonstrates how to declare these objects:
' Define a new object based on your ListBox class.
Dim LBC as New ListBox
' Define a new Windows.Forms ListBox control.
Dim MyLB as New System.Windows.Forms.ListBox
Q54. What is Garbage collector? What is the namespace we use to work with it? The .NET Framework's garbage collector manages the allocation and release of memory for your application. Each time you use the new operator to create an object, the runtime allocates memory for the object from the managed heap. As long as address space is available in the managed heap, the runtime continues to allocate space for new objects. However, memory is not infinite. Eventually the garbage collector must perform a collection in order to free some memory. The garbage collector's optimizing engine determines the best time to perform a collection, based upon the allocations being made. When the garbage collector performs a collection, it checks for objects in the managed heap that are no longer being used by the application and performs the necessary operations to reclaim their memory.
System.GC is the name space we use
Q55. What is the use of GC? What does it primarily do? Controls the system garbage collector, a service that automatically reclaims unused memory.
Q56. What is the namespace used to create controls? System.web.UI.Usercontrols
System.Web.UI.WebControls
System.Windows.Forms.
Q57. What is GAC? What are the different ways to put an assembly in GAC?
The global assembly cache stores assemblies specifically designated to be shared by several applications on the computer.
You should share assemblies by installing them into the global assembly cache only when you need to. As a general guideline, keep assembly dependencies private, and locate assemblies in the application directory unless sharing an assembly is explicitly required. In addition, it is not necessary to install assemblies into the global assembly cache to make them accessible to COM interop or unmanaged code.
There are two ways to install an assembly into the global assembly cache:
• Using Microsoft Windows Installer 2.0.
This is the recommended and most common way to add assemblies to the global assembly cache. The installer provides reference counting of assemblies in the global assembly cache, plus other benefits.
• Using the Global Assembly Cache tool (Gacutil.exe).
You can use Gacutil.exe to add strong-named assemblies to the global assembly cache and to view the contents of the global assembly cache.
Note Gacutil.exe is only for development purposes and should not be used to install production assemblies into the global assembly cache.
To install a strong-named assembly into the global assembly cache
• At the command prompt, type the following command:
gacutil –I
In this command, assembly name is the name of the assembly to install in the global assembly cache.
The following example installs an assembly with the file name hello.dll into the global assembly cache.
gacutil -i hello.dll
In deployment scenarios, use Windows Installer 2.0 to install assemblies into the global assembly cache. Use Windows Explorer or the Global Assembly Cache tool only in development scenarios, because they do not provide assembly reference counting and other features provided when using the Windows Installer.
Q58. I have a webmethod in a webservice that has to return the data from a particular table? What are the different ways you would try achieving this? Specifically, whether you would use datareader or a dataset and why?
Webservices purely depend on xml Dataset will used to thru XML output.
Dataset GetXML method
Returns the XML representation of the data stored in the DataSet.
Q59. What are all the different types of formatters available? What kind of formatters would you use for remoting purposes and for webservices and why? The .NET serialization formatters encode and decode the messages between .NET applications and application domains. There are two native formatters in the .NET runtime, namely Binary (System.Runtime.Serialization.Formatters.Binary) and SOAP (System.Runtime.Serialization.Formatters.Soap).
Q60. What is SOAP? SOAP is a lightweight and simple XML-based protocol that is designed to exchange structured and typed information on the Web. The purpose of SOAP is to enable rich and automated Web services based on a shared and open Web infrastructure. SOAP can be used in combination with a variety of existing Internet protocols and formats, and can support a wide range of applications from messaging systems to RPC.
Q61. What is COM? What is the basic interface that has to be supported to be called a COM implementation?
The Microsoft Component Object Model (COM) is a platform-independent, distributed, object-oriented system for creating binary software components that can interact. COM is the foundation technology for Microsoft's OLE (compound documents), ActiveX® (Internet-enabled components), as well as others.
To understand COM (and therefore all COM-based technologies), it is crucial to understand that it is not an object-oriented language but a standard. Nor does COM specify how an application should be structured; language, structure, and implementation details are left to the application programmer. Rather, COM specifies an object model and programming requirements that enable COM objects (also called COM components, or sometimes simply objects) to interact with other objects. These objects can be within a single process, in other processes, and can even be on remote machines. They can have been written in other languages, and they may be structurally quite dissimilar, which is why COM is referred to as a binary standard—a standard that applies after a program has been translated to binary machine code.
COM defines the essential nature of a COM object. In general, a software object is made up of a set of data and the functions that manipulate the data. A COM object is one in which access to an object's data is achieved exclusively through one or more sets of related functions. These function sets are called interfaces, and the functions of an interface are called methods. Further, COM requires that the only way to gain access to the methods of an interface is through a pointer to the interface.
Q62. Is the COM architecture same as .Net architecture? What is the difference between them (if at all there is)?
Q63. How would you use a COM component in a .Net environment?
There two ways of doing this.
1.tlbImp.exe util from command prompt
2.using add ref. Com tab
Q64.What are CCW and RCW? RCW - The common language runtime exposes COM objects through a proxy called the runtime callable wrapper (RCW). Although the RCW appears to be an ordinary object to .NET clients, its primary function is to marshal calls between a .NET client and a COM object.
The runtime creates exactly one RCW for each COM object, regardless of the number of references that exist on that object. As the following illustration shows, any number of managed clients can hold a reference to the COM objects that expose INew and INewer interfaces. The runtime maintains a single RCW for each object.
Accessing COM objects through the runtime callable wrapper
Using metadata derived from a type library, the runtime creates both the COM object being called and a wrapper for that object. Each RCW maintains a cache of interface pointers on the COM object it wraps and releases its reference on the COM object when the RCW is no longer needed. The runtime performs garbage collection on the RCW.
Among other activities, the RCW marshals data between managed and unmanaged code, on behalf of the wrapped object. Specifically, the RCW provides marshaling for method arguments and method return values whenever the client and server have different representations of the data passed between them.
The standard wrapper enforces built-in marshaling rules. For example, when a .NET client passes a String type as part of an argument to a managed object, the wrapper converts the string to a BSTR type. Should the COM object return a BSTR to its managed caller, the caller receives a string. Both the client and the server send and receive data that is familiar to them. Other types require no conversion. For instance, a standard wrapper will always pass a 4-byte integer between managed and unmanaged code without converting the type.
CCW
When a COM client calls a .NET object, the common language runtime creates the managed object and a COM callable wrapper (CCW) for the object. Unable to reference a .NET object directly, COM clients use the CCW as a proxy for the managed object.
The runtime creates exactly one CCW for a managed object, regardless of the number of COM clients requesting its services. As the following illustration shows, multiple COM clients can hold a reference to the CCW that exposes the INew interface. The CCW, in turn, holds a single reference to the managed object that implements the interface and is garbage collected. Both COM and .NET clients can make requests on the same managed object simultaneously.
Accessing .NET objects through COM callable wrapper
COM callable wrappers are invisible to other classes running within the .NET Framework. Their primary purpose is to marshal calls between managed and unmanaged code; however, CCWs also manage the object identity and object lifetime of the managed objects they wrap.
Object Identity
The runtime allocates memory for the .NET object from its garbage-collected heap, which enables the runtime to move the object around in memory as necessary. In contrast, the runtime allocates memory for the CCW from a noncollected heap, making it possible for COM clients to reference the wrapper directly.
Object Lifetime
Unlike the .NET client it wraps, the CCW is reference-counted in traditional COM fashion. When the reference count on the CCW reaches zero, the wrapper releases its reference on the managed object. A managed object with no remaining references is collected during the next garbage-collection cycle.
Q65. What is the difference between arrays and Arraylist? The difference lies in thatfact that the size of an array is fixed through-out its lifetime, while an ArrayList object dy-namically grows and shrinks as needed.Another difference is that an ArrayList is notdeclared to store a particular type, since it re-ally keeps a list of references to the Objectclass. Thus, a reference to any object can beadded to an ArrayList object.A side effectis that a primitive value has to be stored in a wrapper class before it can be added into an ArrayList object
Q66. I have a listbox in an ASP.Net page. I need to populate the list box with the values of the employee name from employee table. What are the ways one would go ahead in achieving this?
SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM CategoryTable ", mycn);
ds = new DataSet();
myda.Fill (ds,"Table");
DropDownList1.DataSource =ds.Tables [0] ;
DropDownList1.DataTextField =ds.Tables[0].Columns["CategoryName"].ColumnName.ToString();
DropDownList1.DataValueField =ds.Tables[0].Columns["CategoryId"].ColumnName.ToString();
DropDownList1.DataBind () ;
for (int i =0;i < DropDownList1.Items.Count ;i++ )
{
DropDownList1.Items[i].Attributes.Add("style", "color:" + ds.Tables[0].Rows[i]["CategoryColor"].ToString () );
}
}
}
C#
SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM Products ", mycn);
ds = new DataSet();
myda.Fill (ds,"Table");
for(int i = 0 ;i < ds.Tables[0].Rows.Count - 1;i++)
{
listBox1.Items.Add (new ListItem(ds.Tables[0].Rows[i]["UnitPrice"].ToString(),
ds.Tables[0].Rows[i]["ProductID"].ToString()));
if(Convert.ToDouble(ds.Tables[0].Rows[i]["UnitPrice"].ToString()) <= 25 )
{
listBox1.Items[i].Attributes.Add("style", "color:red");
}
else
{
listBox1.Items[i].Attributes.Add("style", "color:green");
}
}
}
}
To the same employee table, there is another ASP.Net page which does the updation of the table. Now, (refer to question 1), Will I be able to get the updated records in the list? What is the way, then, to get the updated records to the listbox?
private void UpdateDataSet(DataSet myDataSet){
// Check for changes with the HasChanges method first.
if(!myDataSet.HasChanges(DataRowState.Modified)) return;
// Create temporary DataSet variable.
DataSet xDataSet;
// GetChanges for modified rows only.
xDataSet = myDataSet.GetChanges(DataRowState.Modified);
// Check the DataSet for errors.
if(xDataSet.HasErrors){
// Insert code to resolve errors.
}
// After fixing errors, update the data source with the DataAdapter
// used to create the DataSet.
myOleDbDataAdapter.Update(xDataSet);
}
Q67.What are the different ways you would consider sending data across pages in ASP (i.e between 1.asp to 2.asp)? 1.sessions
2.cookies
3.viewstate
Q68.What is viewstate? Why is it used? Suppose I navigate from 1.aspx to 2.aspx page. Is the viewstate available at 2.aspx? Microsoft ASP.NET Web Forms pages are capable of maintaining their own state across multiple client round trips. When a property is set for a control, the ASP.NET saves the property value as part of the control's state. To the application, this makes it appear that the page's lifetime spans multiple client requests. This page-level state is known as the view state of the page. In Web Forms pages, their view state is sent by the server as a hidden variable in a form, as part of every response to the client, and is returned to the server by the client as part of a postback. In this article we will see how View State is implemented in ASP.NET for state management and we will also see how effectively you can use this object in your web form.
Problems with ViewState
Viewstate has lots of advantages and as well as disadvantages, so you need to weigh carefully before making the decision to use it. As I told you early, view state doesnt require any server resources for its operation. It is passed to the client during every postback as an hidden element. Since it is added with every page, it adds few Kbytes to the page. This effects the loading of the page in the client. Other main problem with Viewstate is, since it is passed as plain text to the client. Anybody can tamper this value, because of this you shouldnt store any important data in the viewstate. View state is one of the most important features of ASP.NET, not so much because of its technical relevance, but more because it makes the magic of the Web Forms model possible. However, if used carelessly, view state can easily become a burden. Although ViewState is freely accessible in a hidden field called __VIEWSTATE, the view state information is not clear text. By default, a machine-specific authentication code is calculated on the data and appended to the view state string. The resulting text is then Base64 encoded only, but not encrypted. In order to make the view state more secure, the ASP.NET @Page directive supports an attribute called EnableViewStateMac whose only purpose is detecting any possible attempt at corrupting original data.
Implementation of ViewState
StateBag implements the view state and manages the information that ASP.NET pages and embedded controls persist across successive posts of the same page instance. The class works like a dictionary object and implements the IStateManager interface. The Page and the Control base classes expose the view state through the ViewState property. So you can add or remove items from StateBag as you would with any dictionary object:
ViewState("FontSize") = value
You should start writing to the view state only after the Init event is fired for a page request. You can read from the view state during any stage of the page lifecycle, but not after the page enters rendering mode—that is, after the PreRender event is fired.
The contents of the StateBag collection are first serialized to a string, then Base64 encoded, and finally assigned to a hidden field in the page that is served to the client. The view state for the page is a cumulative property that results from the contents of the ViewState property of the page plus the view state of all the controls hosted in the page.
Decision on ViewState Usage
As We 've discussed here, the view state represents the state of the page and its controls just before the page is rendered in HTML. When the page posts back, the view state is recovered from the hidden field, deserialized, and used to initialize the server controls in the page and the page itself. However, this is only half the story.
After loading the view state, the page reads client-side information through the Request object and uses those values to override most of the settings for the server controls. In general, the two operations are neatly separated and take place independently. In particular, though, the second operation—reading from Request.Form—in many situations ends up just overriding the settings read out of the view state. In this particular case the view state is only an extra overhead. For example consider the following case, we have one textbox in the page and a link button. If you are typing the some values in to the textbox and the posting the page using linkbutton. After postback, value in the textbox is retained though you enable or disable the viewstate. In this case you shouldnt enable viewstate for this textbox. Viewstate value is overridden by request.form values, since loadpostdata fires after loadviewstate view event in the Page lifecycle.
But if you consider that readonly property of textbox is set to False by default. Then in the Page_Load if you are trying to change its readonly property to true based on certain condition. So after setting readonly property in Page_Load and if it is posted back by clicking linkbutton. To retain its readonly property across postback, we need to enable viewstate for this property. Otherwise this property wont be retained across postback.
Viewstate in DataGrid
If you have Set EnableViewState to true for a DataGrid which is having thousands of record. Then you will end up having viewstate size more than 10 KBytes. But if you disable viewstate, you will not be able to fire any events in DataGrid. Postback and acting on postback relies on Viewstate. So if it is readonly datagrid and if you are not going to use paging and sorting provided by datagrid, then you can disable viewstate. But if you want use above mentioned feature of DataGrid, then you can not disable ViewState in DataGrid. So to avoid excessive load on client machine because of viewstate . You can disable viewstate for each item in DataGrid. Disabling can be done in two ways, one way is disabling each itemtemplate columns viewstate to false.
Other way is by disabling viewstate for each datagrid item in Pre-Render event handler.
Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles
MyBase.PreRender
Dim dgi As DataGridItem
For Each dgi In DataGrid1.Items
dgi.EnableViewState = False
Next
End Sub
Conclusion
The view state is a key element of an ASP.NET page because it is the primary means to persist the state of the Web server controls. Whenever the page posts back, the state is restored, updated using the current form parameters, then used to run the postback event handler. Normally, the view state is a hashed string encoded as Base64 and stored in a hidden field called __VIEWSTATE. In this way, the view state is not cached on the client, but simply transported back and forth with potential issues both for security and performance. Since it is performance overhead, you need to decide properly when and where you should use viewstate in your webform.
Q69.What is server.transfer,response.redirect,server.execute?
Server.Transfer (Path)
The Transfer method allows you to transfer from inside one ASP page to another ASP page. All of the state information that has been created for the first (calling) ASP page will be transferred to the second (called) ASP page. This transfered information includes all objects and variables that have been given a value in an Application or Session scope, and all items in the Request collections. For example, the second ASP page will have the same SessionID as the first ASP page.
When the second (called) ASP page completes its tasks, you do not return to the first (calling) ASP page.
In contrast, the Execute method allows you to call another ASP page, and when the called page has completed its tasks, you return to the calling ASP page.
There is one mandatory argument.
Path
The Path argument is a string specifying either the absolute or relative path of the ASP page being called. The file name must be included in the path. The entire Path must be enclosed inside a pair of quotes.
In this example, the first ASP page (CallingAsp.asp) has no output.
Code:
----------CallingAsp.asp----------
----------CalledAsp.asp----------
<%;
Response.Write "Output from CalledAsp.asp"
For Each Item in Application.Contents
If IsObject( Application.Contents(Item)) Then
Response.Write Item & " is an object.
"
Else
Response.Write Item & "=" & Application.Contents(Item) & "
"
End If
Next
%>
Output:
Output from CalledAsp.asp
name=Application Maker
publishdate=05/15/01
author=DevGuru
OBJ1 is an object.
Server.Execute(Path)
The Execute method allows you to call another ASP page from inside an ASP page. When the called ASP page completes its tasks, you are then returned to the calling ASP page. The overall effect is very similar to a function or subroutine call. Any text or output from the called ASP page will be displayed on the calling ASP page. The Execute method is a more useful alternative to using server-side includes.
In contrast, the Transfer method allows you to transfer from one ASP page to another without returning to the calling ASP page.
There is one mandatory argument.
Path
The Path argument is a string specifying either the absolute or relative path of the ASP page being called. The file name must be included in the path. The entire Path must be enclosed inside a pair of quotes. The Path argument cannot include a query string, however, any query string that was available to the calling ASP page will be available to the called ASP page.
Code:
----------CallingAsp.asp----------
How now cow?
----------CalledAsp.asp----------
Output:
How now pink cow?
Response.Redirect(URL)
The Redirect method stops processing the current script and attempts to connect the client to a different URL. This is accomplished by adding an HTTP redirection header to the output stream that is being sent from the server to the client. Unfortunately, if page content has already been sent to the client and if a proxy server lies between the server and the client, an error message can be generated. Therefore it is advisable to set Response.Buffer to true and to call Response.Clear just before calling Redirect.
Note in ASP 2.0, the buffering default setting is false and in ASP 3.0, the buffering default setting is true.
There is one mandatory argument.
URL
The URL argument is the Uniform Resource Locator (URL) that the browser is redirected to.
Code:
--------------File1.asp---------------
--------------File2.asp-----------------
Output:
File1 is written and then the browser will load File2:
------------File1.asp------------------
This is File1.asp and switching to File2.asp
------------File2.asp-------------------
This is File2.asp
Q70.What is the difference between a datareader and a dataset? A datareader like a dataset is an object that:
1- reads one record at a time.
2- gives the values of that records
3- Detroy the memory allocation of the record
4- returns to step 1
And thats the only thing that it does. Therefore it is very small object
that consumes very little memory and is optimize to do it very fast.
Also it uses a connection called readonly, forwardonly. These are not
options that you set when you define the conection object. The datareader
(I presume, since this happends inside the object) adds this attributes to the conection. Therefore since the SQL Server does not have to provide record locking (becasue you are not suppose to be updating this information)
will provide the records faster.
A Dataset on the other hand will:
1. Create a schema that define structure of the data
2. Provide memory space to store all the records of a table that resulst from the select
3. Provide memory to store more than one table
4. Provide logic to relate the records on one table with the records on another
5. Identify the primary key of the records obtained (to do so it comunicates with the sql server to obtain the key of the record.
6. Provide mecanism to update and delete the records on the table
7. Since the dataset runs disconected and the rercords in question can be changed from another process at the same time, the dataset will store the original value of the record and compare that value with the value on the database before updating the record. If the record was changed then the update will not proceed and you receive an error message.
8. Many other things that I dont remember.
Q71.I need to populate data into a dataset from an SQL server. How would you do that? Give the sequences of steps in achieving this.
SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
String strConn;
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
strConn="Data Source=localhost;uid=sa;pwd=;Initial Catalog=northwind";
mycn = new SqlConnection(strConn);
myda = new SqlDataAdapter ("Select * FROM Products ", mycn);
ds = new DataSet();
myda.Fill (ds,"Table");
for(int i = 0 ;i < ds.Tables[0].Rows.Count - 1;i++)
{
listBox1.Items.Add (new ListItem(ds.Tables[0].Rows[i]["UnitPrice"].ToString(),
ds.Tables[0].Rows[i]["ProductID"].ToString()));
if(Convert.ToDouble(ds.Tables[0].Rows[i]["UnitPrice"].ToString()) <= 25 )
{
listBox1.Items[i].Attributes.Add("style", "color:red");
}
else
{
listBox1.Items[i].Attributes.Add("style", "color:green");
}
}
}
}
Q73.What is a dataadapter and is it a must to have it when filling records to dataset? (and its related questions) The SqlDataAdapter, serves as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source.
When the SqlDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey. You may also have the SqlDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema. For more information, see Adding Existing Constraints to a DataSet.
SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a Microsoft SQL Server database.
The SqlDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties to facilitate the loading and updating of data.
When an instance of SqlDataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the SqlDataAdapter constructor.
Example
[Visual Basic, C#] The following example uses the SqlCommand, SqlDataAdapter and SqlConnection, to select records from a database, and populate a DataSet with the selected rows. The filled DataSet is then returned. To accomplish this, the method is passed an initialized DataSet, a connection string, and a query string that is a Transact-SQL SELECT statement.
[Visual Basic]
Public Function SelectSqlSrvRows(dataSet As DataSet, connection As String, query As String) As DataSet
Dim conn As New SqlConnection(connection)
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = new SqlCommand(query, conn)
adapter.Fill(dataset)
Return dataset
End Function
[C#]
public DataSet SelectSqlSrvRows(DataSet dataset,string connection,string query)
{
SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(query, conn);
adapter.Fill(dataset);
return dataset;
}
Q74.How would you define a connection string when you are connecting to an SQL server using ADO.Net? Private Sub btnConnect_Click( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnect.Click
Dim oConn As SqlClient.SqlConnection
Dim strConn As String
Try
' Create the Connection object
oConn = New SqlClient.SqlConnection()
' Build the connection string
strConn &= "Data Source=(local);"
strConn &= "Initial Catalog=Northwind;"
strConn &= "User ID=sa;"
strConn &= "Password=;"
' Set the Connection String
oConn.ConnectionString = strConn
' Open the Connection
oConn.Open()
MessageBox.Show("Connection Open", _
"btnConnect_Click()")
' Close the Connection
oConn.Close()
Catch oExcept As Exception
MessageBox.Show(oExcept.Message, _
"btnConnect_Click()")
End Try
End Sub
Q75.What are the different ways by which one can update a table in SQL Server using ADO.Net? Dataadapeter.update
Conn.execute
Q76. A table has a primary field which is an auto-identity field .It has 200 rows of data. Write a SQL query which returns all the rows except the first row ? select * from #test where roll (select min(roll) from #test)
Q77. advantages of views? •Views simplify users perception of the database (can be used to present only the necessary information while hiding details in underlying relations)
•Views improve data security preventing undesired accesses
•Views facilite the provision of additional data independence
Q78.bcp ( Purpose of it)? BCP stands for Bulk Copy Program. It is a command-line utility that enables you to import and export data to and from SQL Server. The advantage of BCP is that it is fast.
bcp [[database_name.]owner.]table_name {in | out} datafile
[/m maxerrors] [/f formatfile] [/e errfile]
[/F firstrow] [/L lastrow] [/b batchsize]
[/n] [/c] [/E]
[/t field_term] [/r row_term]
[/i inputfile] [/o outputfile]
/U login_id [/P password] [/S servername] [/v] [/a packet_size]
Q79.Connection Pooling, where do we set the Min and Max Pool size.? Customizing the connection pool ADO.NET allows you to control the connection pool by adding values to the connection string. You'll want
to use identical values in all connection strings in your application to be sure the connections can be pooled.
There are six pool-related connection string properties.
The pooling property
This property can be true (the default) or false. Use this property to specify that a particular connection should not participate in the pool, but instead should be destroyed when it is closed. This property is helpful if you know that you'll never have an identical connection request in your application and would like to avoid the overhead associated with managing the pool.
The min pool size
This property is an integer with a default of zero. It specifies a minimum number of connections to maintain in the pool. If you set this to 5, for example, the first time you connect to the server, ADO.NET will create five connections and prepare them for pooling.
The max pool size
This property is an integer with a default of 100 and specifies the maximum number of connections to maintain in the pool.
The enlist property
This one can be true (the default) or false. It controls whether a connection will automatically enlist in the calling thread's transaction when it is taken from the pool.
The connection reset
This property can be true (the default) or false. It controls whether the connection will automatically be reset (clearing any pending results) when it's returned from the pool.
The connection lifetime
This property controls the maximum age of connections, in seconds. If a connection has been open for more than this number of seconds when you call its Close or Dispose method, it will be destroyed rather than being returned to the pool. By default, this property is set to zero, which means that connections are kept in the pool regardless of age.
The pool limiter
The Max Pool Size property acts as a limiter to the connection pool. If you leave this property set to 100 (the default), up to 100 connections from your application will be placed in the pool. When the 101st connection request comes along, it is queued to wait for an available connection. If no connection becomes available during the timeout period for the connection string (this period can be customized with the Connect Timeout connection string property and defaults to 60 seconds), an error is returned instead of an open connection.
The Max Pool Size property is designed to limit the resources that a runaway process can grab. But in the case of a busy ASP.NET application, it may have the undesired side effect of limiting the number of simultaneous users for your application. If the connection pool maxes out while new connection requests are still coming in,
you'll see connection requests refused, apparently at random. The cure in this case is simply to specify a higher value for the Max Pool Size property.
Q80.Difference b/w text and image datatype.?
Use text for character data if you need to store more than 255 characters in SQL Server 6.5, or more than 8000 in SQL Server 7.0. Use image for binary large objects (BLOBs) such as digital images. With text and image data types, the data is not stored in the row,so the limit of the page size does not apply.All that is stored in the row is a pointer to the database pages that contain the data.Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row.
Q81.difference between clustered index and non clustered index? A clustered index determines the physical order of data in a table.
Per table only one clustered index. Leaf nodes contains Data pages
Consider using a clustered index for:
• Columns that contain a large number of distinct values.
• Queries that return a range of values using operators such as BETWEEN, >, >=, <, and 1
Q95.Table A contains column1 , which has 2 values (1, 2) and Table 2 contains column1,which has 2 values (2, 3).Write a query which returns the values that are not common for the tables and the query should return one column with 2 records?
select a.col1 from a,b where a.col1(select b.col1 form a,b where a.col1=b.col1) union select b.col1 from a,b where b.col1(select a.col1 from a,b where a.col1=b.col1)
Q96.Table A contains column1 which is primary key and has 2 values (1, 2) and Table B contains column1 which is primary key and has 2 values (2, 3). Write a query which returns the values that are not common for the tables and the query should return one column
same as above
Q97.There are 3 tables Titles, Authors and Title-Authors. Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has
select authors.authorname,count(authtitles.authorid)
from authors,authtitles where author.authorid=authtitles.authorid
group by authors.authorname
order by count(authtitles.authorid) desc
Q98.Type of backups?
1.Full database backup
2.Differential Back up
3.Transaction Log Backup
4.File Group Backup
Q99.What are sequence diagrams? Sequence diagrams document the interactions between classes to achieve a result , such as a use case.Becuase UML is designed for object-oriented programming , these communications between classes are known as messages. The sequence diagram lists objects horizontally, and time vertically, and models these messages over time.
Q100.what are the different authentication modes in Sqlserver? if a user is logged under windows authentication mode, how to find his userid?
There are Three Different authentication modes in sqlserver.
1.Windows Authentication Mode
2.SqlServer Authentication Mode
3.Mixed Authentication Mode
“system_user” system function in sqlserver to fetch the logged on user name.
Q101.What are the different types of replications available in sqlserver and brief about each? Replication is the process of copying or moving one database to the other.There are three entities available to define the replication.
1.Publisher
2.Distributor
3.Subscribers
The Publisher is a server that makes data available for replication to other servers. The Publisher can have one or more publications, each representing a logically related set of data. In addition to being the server where you specify which data is to be replicated, the Publisher also detects which data has changed during transactional replication and maintains information about all publications at that site.
The Distributor is a server that hosts the distribution database and stores history data, and/or transactions and meta data. The role of the Distributor varies depending on which type of replication you implement. For more information, see Types of Replication.A remote Distributor is a server that is separate from the Publisher and is configured as a Distributor of replication. A local Distributor is a server that is configured to be both a Publisher and a Distributor of replication.
Subscribers are servers that receive replicated data. Subscribers subscribe to publications, not to individual articles within a publication, and they subscribe only to the publications that they need, not all of the publications available on a Publisher. Depending on the type of replication and replication options you choose, the Subscriber could also propagate data changes back to the Publisher or republish the data to other Subscribers.
There are three types of replications available
1.Snapshot Replication:- 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.
Snapshot replication is helpful when:
• Data is mostly static and does not change often. When it does change, it makes more sense to publish an entirely new copy to Subscribers.
• It is acceptable to have copies of data that are out of date for a period of time.
• Replicating small volumes of data in which an entire refresh of the data is reasonable.
2.Transactional Replications:- With 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. Transactional replication is helpful when:
• You want incremental changes to be propagated to Subscribers as they occur.
• You need transactions to adhere to ACID properties.
• Subscribers are reliably and/or frequently connected to the Publisher.
3.Merge Replication:- 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.
Merge replication allows various sites to work autonomously and at a later time merge updates into a single,uniform result. The initial snapshot is applied to Subscribers, and then Microsoft® SQL Server™ 2000 tracks changes to published data at the Publisher and at the Subscribers. The data is synchronized between servers continuously, at a scheduled time, or on demand. Because updates are made at more than one server, the same data may have been updated by the Publisher or by more than one Subscriber. Therefore, conflicts can occur when updates are merged.
Merge Replication is helpful when:
• Multiple Subscribers need to update data at various times and propagate those changes to the Publisher and to other Subscribers.
• Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.
• You do not expect many conflicts when data is updated at multiple sites
• (because the data is filtered into partitions and then published to different Subscribers or because of the uses of your application).
However, if conflicts do occur, violations of ACID properties are acceptable
Q102.What is a DeadLock? Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system. A thread in a multi-threaded system may acquire one or more resources
(for example, locks). If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. The waiting thread is said to have a dependency on the owning thread for that particular resource.
A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources.
Q103.What is denormalisation? 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.
Q104.What is Foreign Key? A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table.
Q105.What is unique Key? Unique key distinctly identifies each row in a table , by default creates a non clustered index on the Key specified , allows only one NULL value for the key specified.
Q106.In which all scenarios you will prefer to go for impersonation type of security? Another important security feature is the ability to control the identity under which code is executed. Impersonation is when ASP.NET executes code in the context of an authenticated and authorized client. By default, ASP.NET does not use impersonation and instead executes all code using the same user account as the ASP.NET process, which is typically the ASPNET account. This is contrary to the default behavior of ASP, which uses impersonation by default. In Internet Information Services (IIS) 6, the default identity is the NetworkService account.
NET applications can optionally execute the processing thread using the identity of the client on whose behalf they are operating. You usually use impersonation for resource access control. Delegation is a more powerful form of impersonation and makes it possible for the server process to access remote resources while acting as the client.
If you enable impersonation, ASP.NET can either impersonate the authenticated identity received from IIS or one specified in the application's Web.config file. You have the following three options when configuring impersonation:
Impersonation is disabled.
Impersonation enabled
Impersonation enabled for a specific identity.
Q107. Suppose you have an ASP application using SQL Server 2k database. The SQL Server is set under mixed mode. Now you want to change mixed mode to windows authentication mode.
so for doing this what all changes are required in your ASP pages? Connection string need to change
DataSource=MySQLServer;Initial Catalog=NORTHWIND;Integrated Security=SSPI
Q108. Comparison of features of ASP.NET & ASP.?
Q109. Various ways to implement security in ASP.NET applications.
Access Control
IIS Authentication
ASP.NET Authentication
Authorization
ASP.NET Impersonation
ASP.NET Delegation
ASP.NET Process Identity
Database Security
Q110. Which one is better server controls or html controls and why ?
HTML controls and server controls are somewhat similar, and can sometimes be used interchangeably to do the same thing. For example, and
![]()
both accomplish the same task, displaying an image on a web page.
HTML controls refer to HTML tags that have the runat="server" attribute. For example,
![]()
is a standard HTML tag, where as
![]()
is an HTML Control.
Server controls refer to tags starting with <asp. For example, and . The main advantage of using server controls over HTML controls is that they are used to provide some sort of added functionality for your ASP.NET pages, such as interactive data grids and calendars. The output code is generated dynamically from server controls, and they only output code that is compatible with the client's web browser. Server controls provide programmers with a richer set of features than HTML controls.
You can use both HTML controls and server controls for just about anything. However, the main purpose of these controls is to help separate HTML presentation code from application logic code. Notice how I've said "help separate"? to completely separate them from HTML code, you need to move them to a different file. We will need to use code behind technique to accomplish this, which we will cover soon.
Q111. What is the difference between byte code and managed code ?
Q112. What are indexers in C# (do not confuse with indexes in sqlserver).
Indexers permit instances of a class or struct to be indexed in the same way as arrays. Indexers are similar to properties except that their accessors take parameters.
The following example shows how to declare a private array field, myArray, and an indexer. Using the indexer allows direct access to the instance b[i]. The alternative to using the indexer is to declare the array as a public member and access its members, myArray[i], directly.
// cs_keyword_indexers.cs
using System;
class IndexerClass
{
private int [] myArray = new int[100];
public int this [int index] // Indexer declaration
{
get
{
// Check the index limits.
if (index = 100)
return 0;
else
return myArray[index];
}
set
{
if (!(index = 100))
myArray[index] = value;
}
}
}
public class MainClass
{
public static void Main()
{
IndexerClass b = new IndexerClass();
// Call the indexer to initialize the elements #3 and #5.
b[3] = 256;
b[5] = 1024;
for (int i=0; i<=10; i++)
{
Console.WriteLine("Element #{0} = {1}", i, b[i]);
}
}
}
Output
Element #0 = 0
Element #1 = 0
Element #2 = 0
Element #3 = 256
Element #4 = 0
Element #5 = 1024
Element #6 = 0
Element #7 = 0
Element #8 = 0
Element #9 = 0
Element #10 = 0
Q113. Why do you choose C# as language in your ASP.net applications?
Q114. When will application_onstart gets fired? After staring IIS or after starting IIS and first instance of application is called? First user accessing site.
Q115. When will session onstart() gets fired?
Q116.What are satellite assemblies and how you will create this? How will you get the different language strings?
Q117.what is Linked servers ?
Q118. You have one database with a table which has huge volume of data and you don’t know anything about the table structure and how it’s used and all. There is a performance problem
in the application and you have to create indexes for this table. How will you go about it? What are the steps you will take to create the indexes?
Q119. What is Private Constructor? A private constructor is a special instance constructor. It is commonly used in classes that contain static members only. If a class has one or more private constructors and no public constructors, then other classes (except nested classes) are not allowed to create instances of this class. For example:
class NLog
{
// Private Constructor:
private NLog() {}
public static double e = 2.71828;
}
The declaration of the empty constructor prevents the automatic generation of a default constructor. Note that if you don't use an access modifier with the constructor it will still be private by default. However, the private modifier is usually used explicitly to make it clear that the class cannot be instantiated.
Private constructors are useful to prevent creation of a class when there are no instance fields or methods, such as the Math class, or when a method is called to obtain an instance of a class.
Example
The following is an example of a class using a private constructor.
// PrivateCtor1.cs
using System;
public class MyClass
{
private MyClass() {}
public static int counter;
public static int IncrementCounter()
{
return ++counter;
}
}
class MainClass
{
static void Main()
{
// If you uncomment the following statement, it will generate
// an error because the constructor is inaccessible:
// MyClass myObject = new MyClass(); // Error
MyClass.counter = 100;
MyClass.IncrementCounter();
Console.WriteLine("New count: {0}", MyClass.counter);
}
}
Output
New count: 101
Notice that if you uncomment the following statement from the example, it will generate an error because the constructor is inaccessible due to its protection level:
// MyClass myObject = new MyClass(); // error
Q120.explain authentication levels in .net ? IIS provides a variety of authentication schemes:
• Anonymous (enabled by default)
• Basic
• Digest
• Integrated Windows authentication (enabled by default)
• Client Certificate Mapping
•
Q121.explain authorization levels in .net ? The following items control authorization in an ASP.NET application:
• Windows Access Control Lists (ACLs)
• Web Server Permissions
• URL Authorization
• .NET Principal Objects
• Roles and Method-Level Security
Windows Access Control Lists
Using Windows ACLs, you can create file system permissions on specific application files. This solution works best if your application is authenticating users to Windows accounts. To use Windows ACLs, you must use the Windows NTFS file system. For more information, see Access-Control Lists (ACLs).
Web Server Permissions
You can configure IIS to specify the following permissions on Web site directories, such as read access and directory browsing. It is important to understand the distinction between Web server permissions and NTFS permissions. Unlike NTFS, Web server permissions apply to all users accessing your Web and FTP sites. NTFS permissions apply only to a specific user or group of users with a valid Windows account. NTFS controls access to physical directories on your server, whereas Web and FTP permissions control access to virtual directories on your Web or FTP site.
URL Authorization
The UrlAuthorizationModule Class maps users and roles to elements within the URI namespace, which is defined by a URL. This module implements both positive and negative authorization assertions. The module can either be used to selectively permit or deny specific users access to arbitrary elements of the URI namespace. For example, you can base access on user role membership. For more information, see ASP.NET Authorization and UrlAuthorizationModule Class
The following example grants access to several domain users, while denying it to everyone else.
WindowsPrincipal Object
The System.Security.Principal Namespace provides a WindowsPrincipal Class to represent the security context under which the code is running. This object is automatically created for you when you use Windows authentication in IIS. It allows you to check the Windows group membership of a Windows user account and restrict access accordingly. For more information, see Creating WindowsIdentity and WindowsPrincipal Objects
GenericPrincipal Object
You can create an object from the GenericPrincipal Class based on your own custom roles. Use this when you have your own user/role database. You can populate the principal object in the OnAuthenticate event. You can have a custom table mapped to Windows accounts that you access in this event. Using that information, you can create a custom principal object for the authenticated user. For returning authenticated users, you can use a cookie to recreate the principal object. For more information, see Creating GenericPrincipal and GenericIdentity Objects.
Roles and Method-Level Security
You may need to use method-level security to restrict which methods a particular client principal calls. There are several approaches to this type of security.
• If you are using Windows accounts, create roles for your users by creating Windows groups. Because the processing thread is impersonating the client and a WindowsPrincipal object is available, use the following approaches:
o Create NTFS ACLs on protected resources accessed by the ASP.NET thread.
o Call the WindowsPrincipal.IsInRole method from each method to verify the caller has the appropriate permissions. You can also implement a logic statement in code that calls a particular subroutine based on the client's group membership. For more information, see WindowsPrincipal.IsInRole Method.
• If you are using a GenericPrincipal object created from users and roles contained in a custom database:
o You can programmatically check role membership by calling the GenericPrincipal.IsInRole method in the same fashion as with the WindowsPrincipal object. For more information, see GenericPrincipal.IsInRole Method.
• If you are not using a principal object, you have other options:
o The role-based security model supports a permission object similar to the permission objects found in the code access security model. This object, PrincipalPermission, represents the identity and role that a particular principal class must have to run. You can use the PrincipalPermission class for both imperative and declarative security checks. For more information, see PrincipalPermission Objects.
o Accept user credentials as parameters to the method call and perform a look-up within the method.
o Verify the existence of a cookie as the first operation of the method call.
o Create a logon method that returns a custom key value. Subsequent methods accept the key value as a parameter. This is similar to using browser-supported cookies; however, you can use it in cases where the client does not support cookies.
By passing a user name and role to the constructor, you can require that the identity of the principal object attached to the current thread matches a particular user name and role. After you have defined identity and principal objects, you can perform security checks against them in one of the following ways:
• Using imperative security checks. Imperative security determines at run time whether the principal object attached to the current thread matches a specified PrincipalPermission object. For more information, see Performing Imperative Security Checks.
• Using declarative security checks. Declarative security uses the PrincipalPermissionAttribute to define the principal that the current thread must have to invoke a method. For more information, see Performing Declarative Security Checks.
• Directly accessing the Principal object. By accessing the Principal object directly, you can programmatically makes a security decision based on the properties of the principal object attached to the current thread. For more information, see Directly Accessing a Principal Object.
Q122.how you will provide secuiry in .asp applications? Authentication ,Authorization tags in web.config
If you enable impersonation, ASP.NET can either impersonate the authenticated identity received from IIS or one specified in the application's Web.config file. You have the following three options when configuring impersonation:
• Impersonation is disabled. This is the default setting. For backward compatibility with ASP, you must enable impersonation and change the ASP.NET process identity to use the Local System account. In this instance, the ASP.NET thread runs using the process token of the application worker process regardless of which combination of IIS and ASP.NET authentication is used. By default, the process identity of the application worker process is the ASPNET account. For more information, see ASP.NET Process Identity.
• Impersonation enabled. In this instance, ASP.NET impersonates the token passed to it by IIS, which is either an authenticated user or the anonymous Internet user account (IUSR_machinename).
• Impersonation enabled for a specific identity. In this instance, ASP.NET impersonates the token generated using an identity specified in the Web.config file.
•
If the application resides on a UNC share, ASP.NET always impersonates the IIS UNC token to access that share unless a configured account is used. If you provide an explicitly configured account, ASP.NET uses that account in preference to the IIS UNC token.
Q123.how you will provide secuiry in cleint/server applications and vba applications? Using Components
Q124.What is reflection? All .NET compilers produce metadata about the types defined in the modules they produce. This metadata is packaged along with the module (modules in turn are packaged together in assemblies), and can be accessed by a mechanism called reflection. The System.Reflection namespace contains classes that can be used to interrogate the types for a module/assembly.Using reflection to access .NET metadata is very similar to using ITypeLib/ITypeInfo to access type library data in COM, and it is used for similar purposes - e.g. determining data type sizes for marshaling data across context/process/machine boundaries.
Reflection can also be used to dynamically invoke methods (see System.Type.InvokeMember), or even create types dynamically at run-time (see System.Reflection.Emit.TypeBuilder).
Q125. What is an Application Domain? An AppDomain can be thought of as a lightweight process. Multiple AppDomains can exist inside a Win32 process. The primary purpose of the AppDomain is to isolate an application from other applications.
Win32 processes provide isolation by having distinct memory address spaces. This is effective, but it is expensive and doesn't scale well. The .NET runtime enforces AppDomain isolation by keeping control over the use of memory - all memory in the AppDomain is managed by the .NET runtime, so the runtime can ensure that AppDomains do not access each other's memory.
Q126.How does an AppDomain get created?
AppDomains are usually created by hosts. Examples of hosts are the Windows Shell, ASP.NET and IE. When you run a .NET application from the command-line, the host is the Shell. The Shell creates a new AppDomain for every application. AppDomains can also be explicitly created by .NET applications. Here is a C# sample which creates an AppDomain, creates an instance of an object inside it, and then executes one of the object's methods. Note that you must name the executable 'appdomaintest.exe' for this code to work as-is.
using System;
using System.Runtime.Remoting;
public class CAppDomainInfo : MarshalByRefObject
{
public string GetAppDomainInfo()
{
return "AppDomain="+AppDomain.CurrentDomain.FriendlyName;
}
}
public class App
{
public static int Main()
{
AppDomain ad = AppDomain.CreateDomain( "Andy's new domain", null, null );
ObjectHandle oh = ad.CreateInstance( "appdomaintest", "CAppDomainInfo" );
CAppDomainInfo adInfo = (CAppDomainInfo)(oh.Unwrap());
string info = adInfo.GetAppDomainInfo();
Console.WriteLine( "AppDomain info: " + info );
return 0;
}
}
Q127. What is serialization? Serialization is the process of converting an object into a stream of bytes. Deserialization is the opposite process of creating an object from a stream of bytes.Serialization/Deserialization is mostly used to transport objects (e.g. during remoting), or to persist objects (e.g. to a file or database).
Q128. I want to serialize instances of my class. Should I use XmlSerializer, SoapFormatter or BinaryFormatter? It depends. XmlSerializer has severe limitations such as the requirement that the target class has a parameterless constructor, and only public read/write properties and fields can be serialized. However, on the plus side, XmlSerializer has good support for customising the
XML document that is produced or consumed. XmlSerializer's features mean that it is most suitable for cross-platform work, or for constructing objects from existing XML documents.
SoapFormatter and BinaryFormatter have fewer limitations than XmlSerializer. They can serialize private fields, for example. However they both require that the target class be marked with the [Serializable] attribute, so like XmlSerializer the class needs to be written with serialization in mind. Also there are some quirks to watch out for – for example on deserialization the constructor of the new object is not invoked. The choice between SoapFormatter and BinaryFormatter depends on the application.
BinaryFormatter makes sense where both serialization and deserialization will be performed on the .NET platform and where performance is important. SoapFormatter generally makes more
sense in all other cases, for ease of debugging if nothing else.
Q129. Can I customise the serialization process? Yes. XmlSerializer supports a range of attributes that can be used to configure serialization for a particular class. For example, a field or property can be marked with the [XmlIgnore] attribute to exclude it from serialization. Another example is the [XmlElement] attribute, which can be used to specify the XML element name to be used for a
particular property or field.Serialization via SoapFormatter/BinaryFormatter can also be controlled to some extent by attributes. For example, the [NonSerialized] attribute is the equivalent of XmlSerializer's [XmlIgnore] attribute. Ultimate control of the serialization process can be acheived by implementing the the ISerializable interface on the class whose instances are to be serialized.
Q130. Why is XmlSerializer so slow? There is a once-per-process-per-type overhead with XmlSerializer. So the first time you serialize or deserialize an object of a given type in an application, there is a significant delay. This normally doesn't matter, but it may mean, for example, that XmlSerializer is a poor choice for loading configuration settings during startup of a GUI application.
Q131. Why do I get errors when I try to serialize a Hashtable?
XmlSerializer will refuse to serialize instances of any class that implements IDictionary,
e.g. Hashtable. SoapFormatter and BinaryFormatter do not have this restriction.
Q132.XmlSerializer is throwing a generic "There was an error reflecting MyClass" error. How do I find out what the problem is? Look at the InnerException property of the exception that is thrown to get a more specific error message.
Q133. What are attributes? There are at least two types of .NET attribute. The first type I will refer to as a metadata ttribute - it allows some data to be attached to a class or method. This data becomes part of the metadata for the class, and (like other class metadata) can be accessed via reflection. An example of a metadata attribute is [serializable], which can be attached to a class and means that instances of the class can be serialized.
[serializable] public class CTest {}
The other type of attribute is a context attribute. Context attributes use a similar syntax to metadata attributes but they are fundamentally different. Context attributes provide an interception mechanism whereby instance activation and method calls can be pre- and/or post-processed. If you've come across Keith Brown's universal delegator you'll be familiar with this idea.
Q134. Can I create my own metadata attributes? Yes. Simply derive a class from System.Attribute and mark it with the AttributeUsage
attribute. For example:
[AttributeUsage(AttributeTargets.Class)]
public class InspiredByAttribute : System.Attribute
{
public string InspiredBy;
public InspiredByAttribute( string inspiredBy )
{
InspiredBy = inspiredBy;
}
}
[InspiredBy("Andy Mc's brilliant .NET FAQ")]
class CTest
{
}
class CApp
{
public static void Main()
{
object[] atts = typeof(CTest).GetCustomAttributes(true);
foreach( object att in atts )
if( att is InspiredByAttribute )
Console.WriteLine( "Class CTest was inspired by {0}",
((InspiredByAttribute)att).InspiredBy );
}
}
Q135.What is Code Access Security (CAS)? CAS is the part of the .NET security model that determines whether or not a piece of code is allowed to run, and what resources it can use when it is running. For example, it is CAS that will prevent a .NET web applet from formatting your hard disk.
Q136.How does CAS work? The CAS security policy revolves around two key concepts - code groups and permissions. Each .NET assembly is a member of a particular code group, and each code group is granted the
ermissions specified in a named permission set.
For example, using the default security policy, a control downloaded from a web site belongs to the 'Zone - Internet' code group, which adheres to the permissions defined by the Internet' named permission set. (Naturally the 'Internet' named permission set represents a
very restrictive range of permissions.)
Q137.Who defines the CAS code groups? Microsoft defines some default ones, but you can modify these and even create your own. To see the code groups defined on your system, run 'caspol -lg' from the command-line. On my system it looks like this:
Level = Machine
Code Groups:
1. All code: Nothing
1.1. Zone - MyComputer: FullTrust
1.1.1. Honor SkipVerification requests: SkipVerification
1.2. Zone - Intranet: LocalIntranet
1.3. Zone - Internet: Internet
1.4. Zone - Untrusted: Nothing
1.5. Zone - Trusted: Internet
1.6.StrongName -
Note the hierarchy of code groups - the top of the hierarchy is the most general ('All code'), which is then sub-divided into several groups, each of which in turn can be sub-divided. Also note that (somewhat counter-intuitively) a sub-group can be associated with a more permissive permission set than its parent.
Q138. How do I define my own code group? Use caspol. For example, suppose you trust code from www.mydomain.com and you want it have full access to your system, but you want to keep the default restrictions for all other internet sites. To achieve this, you would add a new code group as a sub-group of the 'Zone
- Internet' group, like this:
caspol -ag 1.3 -site www.mydomain.com FullTrust
Now if you run caspol -lg you will see that the new group has been added as group 1.3.1:
...
1.3. Zone - Internet: Internet
1.3.1. Site - www.mydomain.com: FullTrust
...
Note that the numeric label (1.3.1) is just a caspol invention to make the code groups easy to manipulate from the command-line. The underlying runtime never sees it.
Q139. How do I change the permission set for a code group? Use caspol. If you are the machine administrator, you can operate at the 'machine' level -
which means not only that the changes you make become the default for the machine, but also that users cannot change the permissions to be more permissive. If you are a normal (non-admin) user you can still modify the permissions, but only to make them more
restrictive. For example, to allow intranet code to do what it likes you might do this:
caspol -cg 1.2 FullTrust
Note that because this is more permissive than the default policy (on a standard system),
you should only do this at the machine level - doing it at the user level will have no
effect.
8.6 Can I create my own permission set?
Yes. Use caspol -ap, specifying an XML file containing the permissions in the permission
set. To save you some time, here is a sample file corresponding to the 'Everything'
permission set - just edit to suit your needs. When you have edited the sample, add it to
the range of available permission sets like this:
caspol -ap samplepermset.xml
Then, to apply the permission set to a code group, do something like this:
caspol -cg 1.3 SamplePermSet
(By default, 1.3 is the 'Internet' code group)
8.7 I'm having some trouble with CAS. How can I diagnose my problem?
Caspol has a couple of options that might help. First, you can ask caspol to tell you what
code group an assembly belongs to, using caspol -rsg. Similarly, you can ask what
permissions are being applied to a particular assembly using caspol -rsp.
8.8 I can't be bothered with all this CAS stuff. Can I turn it off?
Yes, as long as you are an administrator. Just run:
caspol -s off
Q134.Can I use COM components from .NET programs?
Yes. COM components are accessed from the .NET runtime via a Runtime Callable Wrapper (RCW). This wrapper turns the COM interfaces exposed by the COM component into .NET-compatibleinterfaces. For oleautomation interfaces, the RCW can be generated automatically from a type library. For non-oleautomation interfaces, it may be necessary to develop a custom RCW which manually maps the types exposed by the COM interface to .NET-compatible types.Here's a simple example for those familiar with ATL. First, create an ATL component which implements the following IDL:
import "oaidl.idl";
import "ocidl.idl";
[
object,
uuid(EA013F93-487A-4403-86EC-FD9FEE5E6206),
helpstring("ICppName Interface"),
pointer_default(unique),
oleautomation
]
interface ICppName : IUnknown
{
[helpstring("method SetName")] HRESULT SetName([in] BSTR name);
[helpstring("method GetName")] HRESULT GetName([out,retval] BSTR *pName );
};
[
uuid(F5E4C61D-D93A-4295-A4B4-2453D4A4484D),
version(1.0),
helpstring("cppcomserver 1.0 Type Library")
]
library CPPCOMSERVERLib
{
importlib("stdole32.tlb");
importlib("stdole2.tlb");
[
uuid(600CE6D9-5ED7-4B4D-BB49-E8D5D5096F70),
helpstring("CppName Class")
]
coclass CppName
{
[default] interface ICppName;
};
};
When you've built the component, you should get a typelibrary. Run the TLBIMP utility on the
typelibary, like this:
tlbimp cppcomserver.tlb
If successful, you will get a message like this:
Typelib imported successfully to CPPCOMSERVERLib.dll
You now need a .NET client - let's use C#. Create a .cs file containing the following code:
using System;
using CPPCOMSERVERLib;
public class MainApp
{
static public void Main()
{
CppName cppname = new CppName();
cppname.SetName( "bob" );
Console.WriteLine( "Name is " + cppname.GetName() );
}
}
Note that we are using the type library name as a namespace, and the COM class name as the
class. Alternatively we could have used CPPCOMSERVERLib.CppName for the class name and gone
without the using CPPCOMSERVERLib statement.
Compile the C# code like this:
csc /r:cppcomserverlib.dll csharpcomclient.cs
Note that the compiler is being told to reference the DLL we previously generated from the
typelibrary using TLBIMP.
You should now be able to run csharpcomclient.exe, and get the following output on the
console:
Name is bob
10.5 Can I use .NET components from COM programs?
Yes. .NET components are accessed from COM via a COM Callable Wrapper (CCW). This is similar
to a RCW (see previous question), but works in the opposite direction. Again, if the wrapper
cannot be automatically generated by the .NET development tools, or if the automatic
behaviour is not desirable, a custom CCW can be developed. Also, for COM to 'see' the .NET
component, the .NET component must be registered in the registry.
Here's a simple example. Create a C# file called testcomserver.cs and put the following in
it:
using System;
namespace AndyMc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class CSharpCOMServer
{
public CSharpCOMServer() {}
public void SetName( string name ) { m_name = name; }
public string GetName() { return m_name; }
private string m_name;
}
}
Then compile the .cs file as follows:
csc /target:library testcomserver.cs
You should get a dll, which you register like this:
regasm testcomserver.dll /tlb:testcomserver.tlb /codebase
Now you need to create a client to test your .NET COM component. VBScript will do - put the
following in a file called comclient.vbs:
Dim dotNetObj
Set dotNetObj = CreateObject("AndyMc.CSharpCOMServer")
dotNetObj.SetName ("bob")
MsgBox "Name is " & dotNetObj.GetName()
and run the script like this:
wscript comclient.vbs
And hey presto you should get a message box displayed with the text "Name is bob".
An alternative to the approach above it to use the dm.net moniker developed by Jason
Whittington and Don Box. Go to http://staff.develop.com/jasonw/clr/readme.htm to check it
out.
11.1 How does .NET remoting work?
.NET remoting involves sending messages along channels. Two of the standard channels are
HTTP and TCP. TCP is intended for LANs only - HTTP can be used for LANs or WANs (internet).
Support is provided for multiple message serializarion formats. Examples are SOAP
(XML-based) and binary. By default, the HTTP channel uses SOAP (via the .NET runtime
Serialization SOAP Formatter), and the TCP channel uses binary (via the .NET runtime
Serialization Binary Formatter). But either channel can use either serialization format.
There are a number of styles of remote access:
• SingleCall. Each incoming request from a client is serviced by a new object. The
object is thrown away when the request has finished.
• Singleton. All incoming requests from clients are processed by a single server
object.
• Client-activated object. This is the old stateful (D)COM model whereby the client
receives a reference to the remote object and holds that reference (thus keeping the remote
object alive) until it is finished with it.
Distributed garbage collection of objects is managed by a system called 'leased based
lifetime'. Each object has a lease time, and when that time expires the object is
disconnected from the .NET runtime remoting infrastructure. Objects have a default renew
time - the lease is renewed when a successful call is made from the client to the object.
The client can also explicitly renew the lease.
If you're interested in using XML-RPC as an alternative to SOAP, take a look at Charles
Cook's XML-RPC.Net site at http://www.cookcomputing.com/xmlrpc/xmlrpc.shtml.
12.1.1 How do I read from a text file? First, use a System.IO.FileStream object to open the file:
FileStream fs = new FileStream( @"c:\test.txt", FileMode.Open, FileAccess.Read );
FileStream inherits from Stream, so you can wrap the FileStream object with a StreamReader
object. This provides a nice interface for processing the stream line by line:
StreamReader sr = new StreamReader( fs );
string curLine;
while( (curLine = sr.ReadLine()) != null )
Console.WriteLine( curLine );
Finally close the StreamReader object:
sr.Close();
Note that this will automatically call Close() on the underlying Stream object, so an
explicit fs.Close() is not required.
12.1.2 How do I write to a text file?
Similar to the read example, except use StreamWriter instead of StreamReader.
12.1.3 How do I read/write binary files? Similar to text files, except wrap the FileStream object with a BinaryReader/Writer object
instead of a StreamReader/Writer object.
12.6.1 Is there built-in support for tracing/logging? Yes, in the System.Diagnostics namespace. There are two main classes that deal with tracing
- Debug and Trace. They both work in a similar way - the difference is that tracing from the
Debug class only works in builds that have the DEBUG symbol defined, whereas tracing from
the Trace class only works in builds that have the TRACE symbol defined. Typically this
means that you should use System.Diagnostics.Trace.WriteLine for tracing that you want to
work in debug and release builds, and System.Diagnostics.Debug.WriteLine for tracing that
you want to work only in debug builds.
12.6.2 Can I redirect tracing to a file? Yes. The Debug and Trace classes both have a Listeners property, which is a collection of
sinks that receive the tracing that you send via Debug.WriteLine and Trace.WriteLine
respectively. By default the Listeners collection contains a single sink, which is an
instance of the DefaultTraceListener class. This sends output to the Win32
OutputDebugString() function and also the System.Diagnostics.Debugger.Log() method. This is
useful when debugging, but if you're trying to trace a problem at a customer site,
redirecting the output to a file is more appropriate. Fortunately, the
TextWriterTraceListener class is provided for this purpose.
Here's how to use the TextWriterTraceListener class to redirect Trace output to a file:
Trace.Listeners.Clear();
FileStream fs = new FileStream( @"c:\log.txt", FileMode.Create, FileAccess.Write );
Trace.Listeners.Add( new TextWriterTraceListener( fs ) );
Trace.WriteLine( @"This will be writen to c:\log.txt!" );
Trace.Flush();
Note the use of Trace.Listeners.Clear() to remove the default listener. If you don't do
this, the output will go to the file and OutputDebugString(). Typically this is not what you
want, because OutputDebugString() imposes a big performance hit.
12.6.3 Can I customise the trace output? Yes. You can write your own TraceListener-derived class, and direct all output through it.
Here's a simple example, which derives from TextWriterTraceListener (and therefore has
in-built support for writing to files, as shown above) and adds timing information and the
thread ID for each trace line:
class MyListener : TextWriterTraceListener
{
public MyListener( Stream s ) : base(s)
{
}
public override void WriteLine( string s )
{
Writer.WriteLine( "{0:D8} [{1:D4}] {2}",
Environment.TickCount - m_startTickCount,
AppDomain.GetCurrentThreadId(),
s );
}
protected int m_startTickCount = Environment.TickCount;
}
(Note that this implementation is not complete - the TraceListener.Write method is not
overridden for example.)
The beauty of this approach is that when an instance of MyListener is added to the
Trace.Listeners collection, all calls to Trace.WriteLine() go through MyListener, including
calls made by referenced assemblies that know nothing about the MyListener class.
12.5.1 Is multi-threading supported? Yes, there is extensive support for multi-threading. New threads can be spawned, and there
is a system-provided threadpool which applications can use.
12.5.2 How do I spawn a thread? Create an instance of a System.Threading.Thread object, passing it an instance of a
ThreadStart delegate that will be executed on the new thread. For example:
class MyThread
{
public MyThread( string initData )
{
m_data = initData;
m_thread = new Thread( new ThreadStart(ThreadMain) );
m_thread.Start();
}
// ThreadMain() is executed on the new thread.
private void ThreadMain()
{
Console.WriteLine( m_data );
}
public void WaitUntilFinished()
{
m_thread.Join();
}
private Thread m_thread;
private string m_data;
}
In this case creating an instance of the MyThread class is sufficient to spawn the thread
and execute the MyThread.ThreadMain() method:
MyThread t = new MyThread( "Hello, world." );
t.WaitUntilFinished();
12.5.3 How do I stop a thread? There are several options. First, you can use your own communication mechanism to tell the
ThreadStart method to finish. Alternatively the Thread class has in-built support for
instructing the thread to stop. The two principle methods are Thread.Interrupt() and
Thread.Abort(). The former will cause a ThreadInterruptedException to be thrown on the
thread when it next goes into a WaitJoinSleep state. In other words, Thread.Interrupt is a
polite way of asking the thread to stop when it is no longer doing any useful work. In
contrast, Thread.Abort() throws a ThreadAbortException regardless of what the thread is
doing. Furthermore, the ThreadAbortException cannot normally be caught (though the
ThreadStart's finally method will be executed). Thread.Abort() is a heavy-handed mechanism
which should not normally be required.
12.5.4 How do I use the thread pool? By passing an instance of a WaitCallback delegate to the ThreadPool.QueueUserWorkItem()
method:
class CApp
{
static void Main()
{
string s = "Hello, World";
ThreadPool.QueueUserWorkItem( new WaitCallback( DoWork ), s );
Thread.Sleep( 1000 ); // Give time for work item to be executed
}
// DoWork is executed on a thread from the thread pool.
static void DoWork( object state )
{
Console.WriteLine( state );
}
}
12.5.5 How do I know when my thread pool work item has completed? There is no way to query the thread pool for this information. You must put code into the
WaitCallback method to signal that it has completed. Events are useful for this.
12.5.6 How do I prevent concurrent access to my data? Each object has a concurrency lock (critical section) associated with it. The
System.Threading.Monitor.Enter/Exit methods are used to acquire and release this lock. For
example, instances of the following class only allow one thread at a time to enter method
f():
class C
{
public void f()
{
try
{
Monitor.Enter(this);
...
}
finally
{
Monitor.Exit(this);
}
}
}
C# has a 'lock' keyword which provides a convenient shorthand for the code above:
class C
{
public void f()
{
lock(this)
{
...
}
}
}
Note that calling Monitor.Enter(myObject) does NOT mean that all access to myObject is
serialized. It means that the synchronisation lock associated with myObject has been
acquired, and no other thread can acquire that lock until Monitor.Exit(o) is called. In
other words, this class is functionally equivalent to the classes above:
class C
{
public void f()
{
lock( m_object )
{
...
}
}
private m_object = new object();
}
• What is the DBCC command used for?
13. Sp_configure commands
15. Db_options userd for ?
What is the purpose of UPDATE STATISTICS?
What is the purpose of using COLLATE in a query?
What is a NOLOCK?
4. What is SQL server agent
what is diff between union and union all?
