Money Talk's !

ITIL Service Management Life Cycle

2016-02-12T10:17:00.002+05:30

ITIL Process Implementation

2016-02-12T10:13:00.001+05:30

Find Duplicate Records

2011-08-09T18:25:00.003+05:30

SELECT     tablefield, COUNT(tablefield) AS dup_count
FROM         table
GROUP BY tablefield
HAVING     (COUNT(tablefield) > 1)

Difference Between COUNT(DISTINCT) vs COUNT(ALL)

2011-03-10T12:34:00.000+05:30

SELECT COUNT([FieldName])

FROM [TableName]

GOSELECT COUNT(ALL [FieldName])

FROM [TableName]

GOSELECT COUNT(DISTINCT [FieldName])

FROM [TableName]

GO

Date Conversions with Different Formats.

2011-03-08T17:07:00.001+05:30

select convert (varchar(20),getdate(),20)
output result
2011-03-08 16:53:04

select convert (varchar(20),getdate(),21)
output result
2011-03-08 17:04:09.

select convert (varchar(20),getdate(),22)
output result
03/08/11 5:04:42 PM

select convert (varchar(20),getdate(),23)
output result
2011-03-08

select convert (varchar(20),getdate(),24)
output result
17:05:51

select convert (varchar(20),getdate(),25)
output result
2011-03-08 17:06:16.

select convert (varchar(20),getdate(),100)
output result
Mar 8 2011 4:54PM

select convert (varchar(20),getdate(),101)
output result
03/08/2011

select convert (varchar(20),getdate(),102)
output result
2011.03.08

select convert (varchar(20),getdate(),103)
output result
08/03/2011

select convert (varchar(20),getdate(),104)
output result
08.03.2011

select convert (varchar(20),getdate(),105)
output result
08-03-2011

select convert (varchar(20),getdate(),106)
output result
08 Mar 2011

select convert (varchar(20),getdate(),107)
output result
Mar 08, 2011

select convert (varchar(20),getdate(),108)
output result
17:00:33

select convert (varchar(20),getdate(),109)
output result
Mar 8 2011 5:01:09

select convert (varchar(20),getdate(),110)
output result
03-08-2011

select convert (varchar(20),getdate(),111)
output result
2011/03/08

select convert (varchar(20),getdate(),112)
output result
20110308

select convert (varchar(20),getdate(),113)
output result
08 Mar 2011 17:03:06

select convert (varchar(20),getdate(),114)
output result
17:03:31:553

Show Total in SQL

2011-02-23T10:53:00.001+05:30

SELECT distinct(b.fieldname),CONVERT(varchar,SUM(a.fieldname),1) as total
from tablename a, tablename b
where a.fieldname=b.fieldname
group by b.tablename

Insert Record from Temp Table to Actual Table

2011-02-16T11:52:00.000+05:30

insert into ActualTableName
Select FiledName from TempTable$
where FieldName NOT IN(select FieldName from ActualTableName)

Previous Month Count in SP

2011-02-07T17:53:00.001+05:30

CREATE PROCEDURE [dbo].[SP_NAME]

AS
BEGIN

select FieldName,count(FieldName)as alias from tablename
where fielddate>= DATEADD(month, DATEDIFF(month, 0, getdate()) - 1, 0)
AND dt < DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
group by FieldName
HAVING COUNT(FieldName) >= 1
ORDER BY FieldName ASC
END

Store Procedure with Date Difference and DateTime Conversion

2011-01-20T15:04:00.000+05:30

@Parameter int,
@ParameterDateName DateTime,
@ParameterDateName DateTime
SELECT FieldName,FieldName,DATEDIFF(day, FieldDateName, GETDATE()) as abc,FieldName from TableName where DATEDIFF(day, FieldDateName, GETDATE()) > @Parameter
  and
  FieldDateName>= convert(datetime, @ParameterDateName, 20)
  and FieldDateName <= convert(datetime, @ParameterDateName, 20)

  ORDER BY Fieldname desc

Store Procedure using Date Difference

2011-01-19T09:49:00.002+05:30

CREATE Proc dbo.ProcedureName @Parameter datatype

AS
Begin
SELECT fieldname,fieldname,DATEDIFF(day, fielddate, GETDATE()) as ABC,fieldname from TableName where DATEDIFF(day, fielddate, GETDATE()) >= @Parameter
End
GO

SQL CURSOR Example

2011-01-18T11:17:00.002+05:30

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Store Procedure Name]

AS
BEGIN

DECLARE @tmp TABLE
(
Fieldname varchar(500),
Fieldname varchar(500)
)
Declare @temp_Fieldname varchar(500)
declare @temp_Fieldname varchar(500)
declare @count int

DECLARE PROCEDURE_CURSOR CURSOR FOR
   select Fieldname,Fieldname from Table where DATEDIFF (dd,importdt,GETDATE()) = 0

  OPEN PROCEDURE_CURSOR
  FETCH NEXT FROM PROCEDURE_CURSOR INTO @temp_Fieldname,@temp_Fieldname
  WHILE @@FETCH_STATUS = 0
   BEGIN
    select @count = count(Fieldname) from @tmp where Fieldname = @temp_Fieldname
    if (@count = 0)
     begin
      insert into @tmp values(@temp_Fieldname,@temp_Fieldname)
     end
    else
     begin
      insert into @tmp (Fieldname) values(@temp_Fieldname)
     end
    FETCH NEXT FROM PROCEDURE_CURSOR INTO @temp_Fieldname,@temp_Fieldname
   END

CLOSE PROCEDURE_CURSOR
DEALLOCATE PROCEDURE_CURSOR
Select * from @tmp
END

Biztalk Server Defination

2011-01-16T10:58:00.000+05:30

Biztalk is a messaging based integration tool. It consists of several tools like Business Processes (Orchestrations), Business Activity Monitoring (BAM), HAT (Health and Activity Tracking), Rules Engines, BizTalk Server Administrative Console etc.

BizTalk Server 2006 R2 builds upon the Business Process Management and SOA/ESB capabilities and address core challenges found in key vertical industries such as manufacturing and retail. Native capabilities of BTS R2 include support for Electronic Data Interchange (EDI) and AS2 ,Windows Workflow Foundation, WCF as well as RFID.

BizTalk server 2006 R2 application can be created using Visual Studio 2005 provided BizTalk Server SDK is installed into the system. A standard BizTalk Server application consists of Schema, Mapping, and Orchestrations. The heart of the BizTalk Server application is schema that is used to define the message format of source and destination data.

BizTalk Server has capability to talk with any kind of legacy system as it only understand the plain text data (in the form of xml), in order to talk with different systems it has several inbuilt adapter like SQL Adapter, MSMQ Adapter, SMTP Adapter, File Adapter, SOAP Adapter etc.

SQL Question Answers

2011-01-15T23:01:00.001+05:30

What is normalization? Explain different levels of normalization?

Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

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

It will be a good idea to read up a database designing fundamentals text book.

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow Nulls, but unique key allows one NULL only.

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

What is bit datatype and what's the information that can be stored inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

What are defaults? Is there a column to which a default can't be bound?

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.

Explain different isolation levels

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.

CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".

Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.

Explain the architecture of SQL Server

This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.

What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

Explain the storage models of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.

What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?

This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.

What are constraints? Explain different types of constraints.

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

What is RAID and what are different types of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board's homepage

What are the steps you will take to improve performance of a poor performing query?

This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.

Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com

What are the steps you will take, if you are tasked with securing an SQL Server?

Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.

Read the white paper on SQL Server security from Microsoft website. Also check out My SQL Server security best practices

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What is blocking and how would you troubleshoot it?

Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.

Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.

Explain CREATE DATABASE syntax

Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.

How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online for more parameters and their explanations.

As a part of your job, what are the DBCC commands that you commonly use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.

What are statistics, under what circumstances they go out of date, how do you update them?

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats

What are the different ways of moving data/databases between servers and databases in SQL Server?

There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?

Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.

What is database replicaion? What are the different types of replication you can set up in SQL Server?

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

Snapshot replication
Transactional replication (with immediate updating subscribers, with queued updating subscribers)
Merge replication

See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.

How to determine the service pack currently installed on SQL Server?

The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions.

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors. Here is an example:

If you have to give a flat hike to your employees using the following criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.

Write down the general syntax for a SELECT statements covering all the options.

Here's the basic syntax: (Also checkout SELECT in books online for advanced syntax).

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]

What is a join and explain different types of joins.

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".

Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.

What is the system function to get the current user's user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder

Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.

Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.

Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()

There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.

What is a self join? Explain it with an example.

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

Given an employee table, how would you find out the second highest salary?

How to Send EMAIL in VB 6.0

2011-01-15T23:00:00.001+05:30

=============== FORM ==============================
Private Sub Command1_Click()
Dim RetVal As Boolean
Dim i As Long

' In a real application we'd check the return values to ensure all was well
' Start a session
RetVal = AlterMailSession(Me, StartSession)

' Send a message with multiple addresses and attachments
RetVal = CreateMailMessage(Me, "Test Message 1", " " & Chr(13) & "Test Contents" & Chr(13) & "More stuff")
RetVal = MailMessageTo(Me, "elliot spencer", Primary)
RetVal = MailMessageTo(Me, "elliot spencer", CC)
RetVal = MailMessageTo(Me, "elliot spencer", Primary)
RetVal = MailMessageTo(Me, "elliot spencer", CC)
RetVal = MailMessageTo(Me, "elliot spencer", BlindCC)
RetVal = AddAttachment(Me, "pq.doc", "c:\pq.doc", DataFile)
RetVal = AddAttachment(Me, "pq.doc", "c:\pq.doc", OLEStatic)
RetVal = SendMailMessage(Me)

' Abort this message
RetVal = CreateMailMessage(Me, "Test Message 2", "Test Contents" & Chr(13) & "More stuff")
RetVal = MailMessageTo(Me, "elliot spencer", CC)
RetVal = AbortMailMessage(Me)

' Save this message
RetVal = CreateMailMessage(Me, "Test Message 3", "Test Contents" & Chr(13) & "More stuff")
RetVal = MailMessageTo(Me, "elliot spencer", Primary)
RetVal = SaveMailMessage(Me)

' Stop the current session
RetVal = AlterMailSession(Me, StopSession)
End Sub
================== MODULE ==========================================

'
' Created by E.Spencer - This code is public domain.
'
Private Declare Function RegOpenKey Lib "AdvAPI32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "AdvAPI32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, _
ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, _
lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "AdvAPI32.dll" (ByVal hKey As Long) As Long
Private Const HKEY_CURRENT_USER = &H80000001
Public Enum SessMode
   StartSession
   StopSession
End Enum
Public Enum AddrType
   Primary
   CC
   BlindCC
End Enum
Public Enum AttachType
   DataFile
   OLEEmbedded
   OLEStatic
End Enum
Public SStatus, MStatus As String

' Call this function to start and stop MAPI sessions
' Example :- MyBool = AlterMailSession(Me, StartSession)
' MyBool = AlterMailSession(Me, StopSession)
' MyBool will be true if operation succeeded
' First parameter is reference to form that contains MAPI message / session controls
' Second parameter is the required session mode - stop or start.
Public Function AlterMailSession(ByRef FName As Form, Mode As SessMode) As Boolean
AlterMailSession = True
On Error GoTo SessError
If Mode = StartSession Then
   ' Get the default exchange profile name
   FName.MAPISession1.UserName = ReadRegistry(HKEY_CURRENT_USER, _
      "Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\", "DefaultProfile")
   ' If session is already open return immediately
   If SStatus = "Open" Then Exit Function
   ' Set up profile - Default for exchange
   FName.MAPISession1.UserName = "MS Exchange Settings"
   FName.MAPISession1.SignOn ' Start mail session
   FName.MAPIMessages1.SessionID = FName.MAPISession1.SessionID ' Allocate session ID to Mail holder
   SStatus = "Open"
   MStatus = "Ready"
ElseIf Mode = StopSession Then
   ' If session is already closed return immediately
   If SStatus = "Closed" Then Exit Function
   FName.MAPISession1.SignOff ' End mail session
   FName.MAPIMessages1.SessionID = 0
   SStatus = "Closed"
   MStatus = "NotReady"
End If
Exit Function
SessError:
SStatus = "Closed"
MStatus = "NotReady"
AlterMailSession = False
End Function

' Call this function to start a new mail message
' Example :- MyBool = CreateMailMessage(Me, "Test Message", "Test Contents")
' MyBool will be true if operation succeeded
' First parameter is reference to form that contains MAPI message / session controls
' The second parameter is the message subject line.
' The third parameter is the message content text (embed Chr(13) for newlines)
Public Function
CreateMailMessage(ByRef FName As Form, Subject As String, Contents As String) As Boolean
CreateMailMessage = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
CreateMailMessage = True
On Error GoTo MessError
FName.MAPIMessages1.Compose ' Start new message composition
FName.MAPIMessages1.MsgSubject = Subject ' Insert message subject line
FName.MAPIMessages1.MsgNoteText = Contents & Chr(13) & " " ' Insert message text
MStatus = "Open"
Exit Function
MessError:
MStatus = "NotReady"
CreateMailMessage = False
End Function

' Call this function to abort a mail message
' Example :- MyBool = AbortMailMessage(Me)
' MyBool will be true if operation succeeded
' First parameter is reference to form that contains MAPI message / session controls
Public Function AbortMailMessage(ByRef FName As Form) As Boolean
AbortMailMessage = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
' If no current mail message then return immediately
If MStatus <> "Open" Then Exit Function
AbortMailMessage = True
On Error GoTo MessError
FName.MAPIMessages1.Delete (mapMessageDelete)
MStatus = "Ready"
Exit Function
MessError:
AbortMailMessage = False
End Function

' Call this function to send a complete mail message
' Example :- MyBool = SendMailMessage(Me)
' MyBool will be true if operation succeeded
' First parameter is reference to form that contains MAPI message / session controls
Public Function SendMailMessage(ByRef FName As Form) As Boolean
Dim Tries As Integer
SendMailMessage = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
' If no current mail message then return immediately
If MStatus <> "Open" Then Exit Function
SendMailMessage = True
On Error GoTo MessError
Retry:
FName.MAPIMessages1.Send
MStatus = "Ready"
Exit Function
MessError:
Tries = Tries + 1
If Tries < 10 Then GoTo Retry
SendMailMessage = False
End Function

' Call this function to save a complete mail message without sending it
' Example :- MyBool = SaveMailMessage(Me)
' MyBool will be true if operation succeeded
' First parameter is reference to form that contains MAPI message / session controls
Public Function SaveMailMessage(ByRef FName As Form) As Boolean
SaveMailMessage = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
' If no current mail message then return immediately
If MStatus <> "Open" Then Exit Function
SaveMailMessage = True
On Error GoTo MessError
FName.MAPIMessages1.Save
MStatus = "Ready"
Exit Function
MessError:
SaveMailMessage = False
End Function

' Call this function to address a mail message to a recipient
' Example :- MyBool = MailMessageTo(Me, "elliot spencer", Primary)
' MyBool will be true if operation succeeded. Supply display names from address book
' list - names will be resolved to addresses in the address book before being added to
' recipient list.
' First parameter is reference to form that contains MAPI message / session controls
' Second parameter is name of recipient (as displayed in address list)
' Third parameter is type of recipient
Public Function MailMessageTo(ByRef FName As Form, ToName As String, AddrMode As AddrType) As Boolean
MailMessageTo = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
' If no current mail message then return immediately
If MStatus <> "Open" Then Exit Function
MailMessageTo = True
On Error GoTo MessError
FName.MAPIMessages1.RecipIndex = FName.MAPIMessages1.RecipCount ' Update count of recipients
If AddrMode = Primary Then FName.MAPIMessages1.RecipType = 1 ' Set to primary recipient type
If AddrMode = CC Then FName.MAPIMessages1.RecipType = 2 ' Set to carbon copy type
If AddrMode = BlindCC Then FName.MAPIMessages1.RecipType = 3 ' Set to blind carbon copy type
FName.MAPIMessages1.RecipDisplayName = ToName ' Display name as provided
FName.MAPIMessages1.ResolveName ' Resolve display name to real address via address book
Exit Function
MessError:
MailMessageTo = False
End Function

' Call this function to address a mail message to a recipient
' Example :- MyBool = AddAttachment(Me, "Test File", "c:\test.txt", DataFile)
' MyBool will be true if operation succeeded.
' First parameter is reference to form that contains MAPI message / session controls
' Second parameter is name of recipient (as displayed in address list)
' Third parameter is type of recipient
Public Function AddAttachment(ByRef FName As Form, AName As String, APath As String, AttMode As AttachType) As Boolean
AddAttachment = False
' If session is not open return immediately
If SStatus <> "Open" Then Exit Function
' If no current mail message then return immediately
If MStatus <> "Open" Then Exit Function
AddAttachment = True
On Error GoTo MessError
FName.MAPIMessages1.AttachmentIndex = FName.MAPIMessages1.AttachmentCount ' Update count of attachments
If AttMode = DataFile Then FName.MAPIMessages1.AttachmentType = 0
If AttMode = OLEEmbedded Then FName.MAPIMessages1.AttachmentType = 1
If AttMode = OLEStatic Then FName.MAPIMessages1.AttachmentType = 2
FName.MAPIMessages1.AttachmentPosition = FName.MAPIMessages1.AttachmentIndex
FName.MAPIMessages1.AttachmentPathName = APath ' File or object path as provided
FName.MAPIMessages1.AttachmentName = AName ' File or object name as provided
Exit Function
MessError:
AddAttachment = False
End Function

' From my registry read module - just to get the default
' exchange user name (profile name)
'
Public Function ReadRegistry(ByVal Group As Long, ByVal Section As String, ByVal Key As String) As String
Dim lResult As Long, lKeyValue As Long, lDataTypeValue As Long, lValueLength As Long, sValue As String
On Error Resume Next
lResult = RegOpenKey(Group, Section, lKeyValue)
sValue = Space$(2048)
lValueLength = Len(sValue)
lResult = RegQueryValueEx(lKeyValue, Key, 0&, lDataTypeValue, sValue, lValueLength)
If (lResult = 0) And (Err.Number = 0) Then
   sValue = Left$(sValue, lValueLength - 1)
Else
   sValue = "Not Found"
End If
lResult = RegCloseKey(lKeyValue)
ReadRegistry = sValue
End Function

Crystal Report Questions

2011-01-15T22:58:00.001+05:30

1 Is it possible to join more than one universe in Business Objects BOE XI? If its so Please explain how is that possible?
Yes, we can join more than one universe in BO we can join 2 master or kernel universes to one derived universe. To Link the universes go to universe parameters->links tab->add link.
2 How many .rpt files are be there in a main report having 2
sub reports? We can keep, one .rpt file for both or we can individually
create .rpt files and made them as single with sub report.[there are 3 .rpt files including the main and 2 sub report. but when the sub reports are inserted into main it will behave like one only.]
3 How to Filter the Crystal reports?
To filter the reports we have to use ?Formulas?.We can prepare the formulas with
(1) Field Explorer (Window) or (2) Writing the code
Syntax for preparing the formulas:
{classname.FieldName} & value. For Ex:
?{Employee.Emp_id}=? + value
?{Employee.Emp_name}=? + value + ??? (Strings to be in single quotes)
4 How to present data from Crystal Reports on to form?
Drag Crystal Report viewer control from toolbox to form.Properties:Crystal Report Name: CRV1
Report Source= or
And call show () method
DOCK=Fill (The control resizes while form is resized [Using Crystalviewer]
5 Is there any feature like summing total in crystal report?
Yes, we can add any database field afterwards also. Select
‘Database Field’ in Insert menu item.
6 Can we use stored procedure for creating the report?
Can. By Adding Command in Crystal Report XI.
7 Does Crystal Report support all the functions that we have
in Oracle?
No, Crystal report does not support all the functions. Like Decode function is there in SQL but not there is crystal report. You need to convert that in crystal report format (in if and else etc.).
8 Can we add any database field once we have chosen ?Close?
button?
Yes, we can add any database field afterwards also. Select
Database Field in Insert menu item.
9 What are the sections that we have in Crystal reports?
1. report header 2. report footer 3. page header 4. page footer 5. details
10 Are there any limitations in crystal reports?
1. In export- While exporting data formatting is lost.
2. If database is having field whose length is more than 255 characters, then you cannot make formula using that field.
3.When you browse data just by right clicking on the field then it displays that is there in the database not the data selected by the query.
[Yes , 1)It will not recognize the HTML tag .This is giving a problem in order to get a bullet indent for some text display in PDF reports.
2) Its not possible to customize the length and width of the legend of a chart.
3) Table object is not given in crystal report. Because of this if we cannot have a table auto grow option as we find it in the MS word table.]
[Limit of tables I have near about 13000+ tables which are created at runtime. I cannot select these tables I think there is a limit near about 8500]
[Crystal doesn't support an LDAP connection.]
[1) You cannot change the format of the date field used in parameter
2) You cannot insert sub report into another sub report
3) You cannot export the report to PDF with group ( it is introduced in Crystal Reports 11 R2)
4) There is no option to save the report in previous versions]
11 Can we use our own SQL for creating a report?
We can also make our own query using Crystal SQL Designer tool provided by SQL. Here you can insert your SQL statement as such. It will save this file as query . And when you create a report instead of using Database button
use Crystal SQL Statement button. [Yes, Using Crystal SQL writer]
12 Can we export data of reports into other format like in world doc etc? yes ..u can, not only in word but also in several formats like pdf, excel. .etc. .u can go to file in the menu in that u have this option
[Reports can be exported to a number of formats, such as spreadsheet, word processor, HTML, ODBC, and common data interchange formats.
Go to File->Export. There are two sub menus, Export Report, Report Export Options. This can be achievable through coding.
ExportDestinationType eExportDestinationType =
ExportDestinationType.DiskFile;
ExportFormatType eExportFormatType =
ExportFormatType.PortableDocFormat;
ExportOptions eExportOptions =
ExportOptions.CreateDiskFileDestinationOptions;]
[Sure.We can save the crystal report as many formats.There are Doc ,xsl, .pdf and etc. The sample code i used in my project is given below.
case "pdf": {
//setting disk file name
crDiskFileDestinationOptions.DiskFileName =
ExportPath + filename;
//setting destination type in our case
disk file
crExportOptions.DestinationOptions =
crDiskFileDestinationOptions;
//setting export format type
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
//setting previously defined
destination opions to our input report document
crExportOptions.ExportFormatType =
ExportFormatType.PortableDocFormat;
break; }
case "xls": {
//setting disk file name
crDiskFileDestinationOptions.DiskFileName =
ExportPath + filename;
//setting destination type in our case
disk file
crExportOptions.ExportDestinationType =
ExportDestinationType.DiskFile;
//setting export format type
crExportOptions.ExportFormatType =
ExportFormatType.Excel;
//setting previously defined
destination opions to our input report document
crExportOptions.DestinationOptions =
crDiskFileDestinationOptions;
break; }]
13 Can we create report using more than one database?
Yes. We can create report using more than one database like Oracle, Access. Create data source name for both the databases. Select tables from them and create the report. Only restriction is if you use two databases then you cannot see the SQL generated by crystal reports.
14 How do we format field?
Right click on that field and choose format field.
15 How do we connect to the database?
1. Use crystal report built in query.
2. Use the tool Crystal SQL Designer provided by crystal report.
[Most data sources can be chosen through the Database Expert dialog box. The Database Expert appears when you create a report from scratch using Blank Report, or when you choose Database Expert from the Database menu. Note: You also select a data source in the Report Creation Wizards. The Data screen in all of the Report Creation Wizards, except the OLAP Report Creation Wizard, is much like the Database Expert dialog box.
To select a data source Choose Database Expert from the Database menu.
The Database Expert dialog box appears. Use the tree view in the Available Data Sources list of the Data screen to select your data source: Current Connections Favorites History Create New Connection Repository Some popular choices in the Create New Connection folder are described here:
Access/Excel (DAO), Database Files, ODBC (RDO), OLAP OLE DB (ADO)
Note: The data source options available in the Create New Connections folder depend on the data access components selected during installation.]
16 Can we use Crystal report as a stand-alone application?
We can make crystal report stand-alone application also. But for that limitation is for viewing the report user should have crystal reports installed
17 How to "Print" in Crystal Reports while using ASP DOT NET
Platform?
using Crystal Decisions. Shared
CrystalReport1 cr= new CrystalReport1;
'here crystalreport1 is crystal report which we design.
CrystalViewer1.ReportSource = cr;
OR we can directly print the report without showing report
CrystalReport1 cr= new CrystalReport1;
cr.PrintToPrinter(1,False,1,1);
18 What are the advantages or disadvantages of using Crystal
Reports in a Windows Forms application as opposed to say rolling our own reports as HTML and displaying them in the Internet Explorer control?
Advantages (1) Secure as End user cannot modify the data which is appear in the report if we use crystal report to show the report but in HTML, user can modify the report data (2) Report layout is not transparent, so that end user will not know about how we design the report. But in HTML, anybody can study the report layout and can copy the technology (3) Can export into different format like PDF, HTML, XML, etc, and many more...
Disadvantages (1) End user need to have the crystal report viewer in his PC in order to see the crystal report output. But this can be overcome if you export the report as PDF/HTML (2) You need to buy Crystal Report license for each pc you are used to design the crystal report.(3) Must buy the version which allows you to install run time components in end user PC (4) Crystal Report is slow as compare to Active Reports (5) If we have 100 pages report then crystal will need to process all pages and then it shows the output. and many more...
19 What are Crystal Reports and Crystal Reports Explorer?
With Crystal reports explorer one can quickly create and modify reports on the web, and since its built on the trusted Business object enterprise platform, it can easily manage massive user loads, individual access, and application customization.
20 How to conditionally suppress a field based on whether a page number is odd or even?
Select the fieldClick FORMATClick FORMAT FIELDSelect the COMMON tabClick the FORMULA BUTTON to the right of SUPPRESS
(Don't click the suppress check box)
To suppress a field on odd numbered pages
PageNumber MOD 2 = 1
To suppress a field on even numebred pages
PageNumber MOD 2 = 0 Click SAVE IconClose the formula editorClick OK on the FORMAT screen
21 How to pass stored Procedure Parameters to the report?
Choose FileOptions menu. In the Options dialog box Click the Database tab and ensure that Stored Procedures is selected. Selecting "Stored Procedures" automatically displays any available stored procedures when you log on to an SQL database.Click OK to exit the Options dialog box. On the Start Page, click Blank Report.Locate and select the SQL Server data source that contains the stored procedure you want to use.Click Next to go to the Connection Information dialog boxEnter the required information to log in.Click FinishHighlight an SQL stored procedure in the Stored Procedures folder, and click thearrow to add it to the Selected Tables list.
The Enter Parameter Values dialog box appears.Highlight a parameter in the Parameter Fields listAssign a value by typing into the Discrete Value box and then click OKYou are returned to the Database ExpertClick OK and create your report using the fields in the stored procedure.
22 What is the "refresh" button suppose to do on the crystal
Report viewer?
(1). When you use? Refresh? Button in crystal report viewer, it refreshes the report data. (2).When you refresh data from the Preview tab, the Refresh Report Data dialog box appearsSelect the "Use current parameter values" option to use the current parameter value, Select the "Prompt for new parameter values" optionto enter a new parameter value. When you select this option and click OK, the Enter Prompt Values dialog box appears. The program now runs the report using the new value(s) you specified.
23 Is there a way to export a report definition without writing code?
Yes. The following steps work assuming you have Crystal Reports on the machine and the appropriate export dlls loaded.
Open the reportPreview itClick the EXPORT envelopeSelect REPORT DEFINITION for the formatSelect DISK FILEClick OKEnter a file name or accept the defaultClick SAVE. The file is a text file and can be viewed with NOTEPAD.
24 Where is the "save data with report" option?
You can find File menu.
25 Is there a way to export the report formulas, like totals to excel?
Yeah we can do that using crystal report
26 What does it mean when we get the error message "Invalid report version" when try to open a Crystal Report?
Crystal in its evolution has changed the report file format several times. In most cases the file format is FORWARD-compatible. By this I mean a file created in Crystal 6 can be opened in Crystal 6 or higher. When the file format changes were major the format is not BACKWARD compatible. By this I mean a file created in CR 9 cannot be opened in CR 8 or before. Crystal changed the report file format with CR 8 and CR 9. The CR 9 change was because of the introduction of Unicode support. CR 8 has the ability to save a file in CR 7 format. Because of the support for Unicode CR 9, 10, and XI cannot save the file in previous formats.
27 How to write a formula to change a font based upon data?
Select the fieldClick FORMATClick FORMAT FIELDSelect the FONT tabClick the FORMULA BUTTON to the right of FONT
if ({YourField} = Value) then
"Arial"
else
"Times New Roman"
Click SAVE IconClose the formula editorClick OK on the FORMAT screen
28 How to refresh the crystal report from the application?
calling crystalreportcontrolname.reset (by selecting Report menu under Refresh the report menu item at design time) will refresh the report
[crystalreport1.DiscardSavedData=True]
29 How many sub-reports can report can have? 0-256
30 How many sections are there in the report
Header, Detail, Footer and Page Header, Report Header, Page Footer, Report Footer [There are several sections. 1)Report header, 2)Page header, 3)Group header, 4)Detail section, 5)Report footer, 6)Page footer And also we can create our own sections if necessary.]
(collected from http://www.sourcecodesworld.com/faqs/crystal-reports-faq.asp)
31. What is Crystal Report?
Ans: Crystal report is a report generation tool. Generally have interface with VB6. Crystal report basically generates dynamic data. You can format the data in whichever way you feel like.
32. Can we use Crystal report as a stand-alone application?
Ans: Generally we use Crystal Reports with VB6. However we can make crystal report stand-alone application also. But for that limitation is for viewing the report user should have crystal reports installed on his/her PC.
33. How do we connect to the database?
Ans: There are two ways of creating the report: -
1. Use crystal report built in query.
2. Use the tool ‘ Crystal SQL Designer’ provided by crystal report.
When you create report using crystal report built in query then it asks for the data source name that you have created. When you run the report, then for the first time it will ask for the user id and password. After that it will store the same.
When you create ‘.qry’ using ‘Crystal SQL’ Designer then at that time it will ask for the user id and password. When you run the report for the first time instead of asking for the user id and password it will ask for the ‘.qry’ file location. You can change the query location also. For that open the report, select ‘Set Location’ option in Database menu item, and set the location.
34. How do we format field?
Ans: For formatting any field just right click on it and you will get many options like ‘Format Field.’ ‘Browse field data’ etc. Click on ‘Format Field.’ You can align data, suppress, make it multiline, change the font size, style, and type, make it hyperlink etc.
If it is an amount field then you can display currency symbol also. Right click on the field select ‘Format Field’.
35. Can we give parameters to the report?
Ans: We can very well give parameters to the report. For creating parameters select ‘Parameter Field’ in Insert menu item. ‘Create Parameter Field’ dialog box will popped up, it will ask for the name of parameter, prompting text and datatype. Now when you run the report it will prompt for these parameters.
36.Can we create our own formulas in reports?
Ans: We can create our own formulas in reports. Select ‘Formula Field’ in Insert menu item. Write the formula in ‘Formula Editor’. Here you will get ‘Field Tree’, ‘Function Tree’, and ‘Operator Tree’, which will display the report fields, functions that are supported by crystal reports (like CDATE () etc.), operators (arithmetic, strings etc.) respectively.
37. Can we create report using more than one database?
Ans: We can create report using more than one database like Oracle, Access. Create data source name for both the databases. Select tables from them and create the report.
Only restriction is if you use two databases then you cannot see the SQL generated by crystal reports.
38. Can we export data of reports into other format like in world doc etc?
Ans: Generated data can be exported to word doc, or in rich text format. Just click on ‘Export’ icon in the menu. Export dialog box will be popped up. It will ask for the ‘Format’ like comma-separated value (csv) etc and the ‘Destination’ like disk, application etc. After that it will ask for the file name and save the data.
Only restriction is formatting of data will be lost, but crystal report will try to maintain as much formatting as it can.
39. Can we use our own SQL for creating a report?
Ans: We can also make our own query using ‘Crystal SQL Designer’ tool provided by SQL. Here you can insert your SQL statement as such. It will save this file as ‘.qry’ . And when you create a report instead of using ‘Database’ button use ‘Crystal SQL Statement’ button.
40. Can we edit SQL made by Crystal reports?
Ans: We cannot edit the SQL made by crystal reports. However we can view the SQL. For that select ‘Show SQL Query’ in Database menu item. Limitation is if you are using only one database. If you use two databases then you can’t even view the SQL prepared by crystal report.
41. Are there any limitations in crystal reports?
Ans: There are certain limitations in crystal reports. They are: -
1. If database is having field whose length is more than 255 characters, then you cannot make formula using that field.
2. While exporting data formatting is lost.
3. When you browse data just by right clicking on the field then it displays that is there in the database not the data selected by the query.
42. Can we suppress printing in crystal reports if 0 records are fetched?
Ans: Yes, we can suppress printing if no records are fetched. Select ‘Report Options’ in File menu item. ‘Report Options’ dialog box will pop up. In that there is one option ‘ Suppress printing if no records’ Check this option. If no records are found then nothing will be printed on the report.
43. What are the sections that we have in Crystal reports?
Ans: Report has got standard sections like ‘Page Header’, ‘Page Footer’, ‘Report Header’, ‘Report Footer’, and ‘Details’.
However you can add other sections also. Select ‘Sections’ in the Insert menu item. You can insert group sections also.
If you don’t want to show any section just right click on that section and suppress that.
44. Can we add any database field once we have chosen ‘Close’ button?
Ans: Yes, we can add any database field afterwards also. Select ‘Database Field’ in Insert menu item.
If you are using crystal report built in query then it will display the tables that you have selected. And you can select whichever field you want to display on the report.
But if you are using ‘.qry’ file then it will display Query and you can select only those field, which are there in the query.
45. Does Crystal Report support all the functions that we have in Oracle?
Ans: No, Crystal report does not support all the functions. Like Decode function is there in SQL but not there is crystal report. You need to convert that in crystal report format (in if and else etc.).
However if you use ‘.qry’ files then it take the SQL as such. There is no need of changing any syntax.
46. Can we use stored procedure for creating the report?
Ans: Yes, we can use stored procedure.
47. Is there any feature like summing total in crystal report?
Ans: Crystal reports provide features like grand total, sub-total, running total etc. You can select any of these features in Insert menu item.
You can sum up records on the basis of each record or on change of group using ‘Running Total ‘ option in Insert menu item.
48. I am using two tables one is of access database and other is of oracle database, I am getting an error saying that ‘SQL odbc error’ what should I do?
Ans: If you are getting such an error then click the icon for ‘Report Expert’. It will give a warning saying that formatting will be lost. Ignore this you will get ‘Standard Report Expert’ dialog box. Reverse the links of access database table and it will work.
49 What versions of Crystal Reports have you used?
Please indicate both release version (9, 10, XI) and type of version (.NET or standalone boxed exition, for example). A) I was willing to consider individuals who were primarily programming version users, if they were able to adequately answer questions about Crystal Reports functionality. It has been my experience, however, that "programmer" Crystal Reports writers have vastly different skill sets and opinions about solutions that "report writer" or "business user" Crystal Reports writers.
50 What is 'Set Location'
A) This is very basic Crystal Reports functionality. If the candidate doesn't know what this is, then that's a red flag.
51 Please describe, in plain English, the most complex report you've ever written. What made it complex and how did you resolve the issues that presented themselves?
A) This question helps me to understand their level of understanding of functionality, their ability to potentially address complex requirements and their creativity.
52 Have you ever written a repot against a View or Stored Procedure?
A) No? They might be skilled in Crystal Reports, but may not understand the relationship between Crystal and the database. They may also not be skilled in SQL.
53 Is it better to build a report against a View or a Stored Procedure and why? Please explain...
A) This is a very good question, in my opinion. First, the right answer is neither. It really depends on the situation. That being said, there is a contingent of report writers out there that have been brainwashed into saying a 'stored procedure is always better, because its pre-compiled'. In my opinion, this is bunk and here's
why: A stored procedure is absolutely great to use if you're going to manipulate a lot of data or crunch a lot of numbers to return aggregates. If you're simply creating a SELECT/FROM/WHERE query, it's a complete waste. Furthermore, using a Stored Procedure (or a SQL Command Object, for that matter) can severely limit the functionality you would otherwise have in Crystal Reports. For example, a common report requirement is to have a parameter that allows the user to select one, multiple or all items in the parameter list. Crystal Reports allows this functionality through multi-value parameter selection. Stored procedures do not allow this functionality...
** A View can be written to contain all of the necessary fields, joins, subqueries, etc..., but still be open enough to be used by a variety of reports. For example, I have a client who needed 15 reports, all of which were to be written against a particular module of their application. All of the reports had very similar fields, they were mostly just different in display and in the selection criteria. As such, I created a single view with both a SELECT and FROM clause, but no WHERE clause. I then built the 15 different reports all against the same view, but added selection criteria to each report. By doing so, they all used a single, easily maintainable database object, but were each unique and efficient.
55 What is a SQL Expression field in Crystal Reports? (collected from the site http://www.allinterview.com/Interview-Questions/Crystal-Reports/page3.html)

56 what is the difference between crystal reports and normal reports?
in crystal reports we can genarate reports with any type of charts (bar charts,pie charts) it is automatically genarated when we give values vto it,but that is not possible with genaral reports we manually should caluculate every thing
57 What is FSG and its use ? Financial Statement Generator is a powerful report building tool for Oracle GL. Uses of FSG :1. Generate financial reports such as income statements an
58 What is Reporting Services Microsoft SQL Server Reporting Services is server-based reporting platform similar to Crystal reports, Brio which allow you to create tabular, matrix,
59 what is different the vb.net and XML?
vb.net is product of micro soft corporation.it is for developing appication programs.
xml is an open standerd one.it is for representing data.xml is not restricted to
the particular language.we can use to represent data of any type of application.XML is common one.for bussiness to bussiness communication XML is Best one.

How to access serial and parallel ports by using Visual Basic .NET

2011-01-15T22:57:00.000+05:30

How to access serial and parallel ports by using Visual Basic .NET
View products that this article applies to.
For a Microsoft Visual Studio 2005 version of this article, see 904795 (http://support.microsoft.com/kb/904795/ ) .
On This Page
• SUMMARY
o Requirements
o Use the MSComm control in Visual Basic .NET to access serial ports
o Use platform invoke services to call Win32 API functions in Visual Basic .NET to access serial and parallel ports
o Sample code listing (Module1.vb)
o Troubleshoot
• REFERENCES
Expand all | Collapse all
SUMMARY
This step-by-step article describes how to access serial ports and how to acces...
This step-by-step article describes how to access serial ports and how to access parallel ports by using Microsoft Visual Basic .NET. This article also contains sample code that illustrates the concepts that are discussed in this article.
Back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
• Microsoft Windows Server 2003, Microsoft Windows XP, or Microsoft Windows 2000
• Visual Basic .NET
This article assumes that you are familiar with the following topics:
• Programming in Visual Basic .NET
• Platform invoke services in Visual Basic .NET
Back to the top
Use the MSComm control in Visual Basic .NET to access serial ports
Because no Microsoft .NET Framework classes exist to access the communications resources that connected to your computer, you can use the MSComm control in Microsoft Visual Basic 6.0. The MSComm control provides serial communications for your application by enabling the transmission and reception of data through a serial port. To implement basic serial communications by using a modem, follow these steps:
1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual Basic Projects.
4. Under Templates, click Console Application.
5. In the Name box, type MyConsoleApplication, and then click OK.

By default, Module1.vb is created.
6. Right-click the MyConsoleApplication project, and then click Add Reference.
7. Click the COM tab, click Microsoft Comm Control 6.0 under Component Name, click Select, and then click OK.

Note To use the MSComm control, you must install the related COM components of Microsoft Visual Basic 6.0 on the same computer that has Microsoft Visual Studio .NET installed.

For more information about license issues when you use Visual Basic 6.0 controls in Visual Studio .NET, click the following article number to view the article in the Microsoft Knowledge Base:
318597 (http://support.microsoft.com/kb/318597/ ) Errors when you use Visual Basic 6.0 controls in Visual Studio .NET
8. Replace the code in Module1.vb with the following code example.
9. Imports MSCommLib
10.
11. Module Module1
12.
13. Sub Main()
14. 'New a MSComm control
15. Dim MSComm1 As MSComm
16. MSComm1 = New MSComm
17. ' Buffer to hold input string.
18. Dim Buffer As String
19. ' Use the COM1 serial port.
20. MSComm1.CommPort = 1
21. ' 9600 baud, no parity, 8 data, and 1 stop bit.
22. MSComm1.Settings = "9600,N,8,1"
23. ' Tell the control to read the whole buffer when Input is used.
24. MSComm1.InputLen = 0
25. ' Open the serial port.
26. MSComm1.PortOpen = True
27. Console.WriteLine("Open the serial port.")
28. ' Tell the control to make the Input property return text data.
29. MSComm1.InputMode() = InputModeConstants.comInputModeText
30. 'Clear the receive buffer.
31. MSComm1.InBufferCount() = 0
32. ' Send the attention command to the modem.
33. MSComm1.Output = "ATV1Q0" & Chr(13)
34. Console.WriteLine("Send the attention command to the modem.")
35. Console.WriteLine("Wait for the data to come back to the serial port...")
36. ' Make sure that the modem responds with "OK".
37. ' Wait for the data to come back to the serial port.
38. Do
39. Buffer = Buffer & MSComm1.Input
40. Loop Until InStr(Buffer, "OK" & vbCrLf)
41. ' Read the "OK" response data in the serial port.
42. ' Close the serial port.
43. Console.WriteLine("Read the OK response data in the serial port.")
44. MSComm1.PortOpen = False
45. Console.WriteLine("Close the serial port.")
46. End Sub
47.
End Module
48. Press CRTL+F5 to build and run this project. You will receive the following output messages:
Open the serial port.
Send the attention command to the modem.
Wait for data to come back to the serial port...
Read the OK response data in the serial port.
Close the serial port.
Back to the top
Use platform invoke services to call Win32 API functions in Visual Basic .NET to access serial and parallel ports
To do this, follow these steps:
1. Start Microsoft Visual Studio .NET.
2. On the File menu, point to New, and then click Project.
3. Under Project Types, click Visual Basic Projects.
4. Under Templates, click Console Application.
5. In the Name text box, type MyConsoleApplication, and then click OK.

By default, Module1.vb is created.
6. Add the following code to Module1.vb before the Module Module1 statement:
7. Option Strict On
8.
9. ' Define a CommException class that inherits from the ApplicationException class,
10. ' and then throw an object of type CommException when you receive an error message.
11. Class CommException
12. Inherits ApplicationException
13. Sub New(ByVal Reason As String)
14. MyBase.New(Reason)
15. End Sub
End Class
16. Declare structures, constants, and references to external functions that are in Kernel32.dll

To call unmanaged functions from your managed Visual Basic .NET application, you must declare references to the structures that you pass as parameters to the unmanaged functions, and you must declare the constants that you pass as parameters to the unmanaged functions. To do this, add the following code to Module1.vb after the Module Module1 statement:
17. 'Declare structures.
18. Public Structure DCB
19. Public DCBlength As Int32
20. Public BaudRate As Int32
21. Public fBitFields As Int32 'See Comments in Win32API.Txt
22. Public wReserved As Int16
23. Public XonLim As Int16
24. Public XoffLim As Int16
25. Public ByteSize As Byte
26. Public Parity As Byte
27. Public StopBits As Byte
28. Public XonChar As Byte
29. Public XoffChar As Byte
30. Public ErrorChar As Byte
31. Public EofChar As Byte
32. Public EvtChar As Byte
33. Public wReserved1 As Int16 'Reserved; Do Not Use
34. End Structure
35.
36. Public Structure COMMTIMEOUTS
37. Public ReadIntervalTimeout As Int32
38. Public ReadTotalTimeoutMultiplier As Int32
39. Public ReadTotalTimeoutConstant As Int32
40. Public WriteTotalTimeoutMultiplier As Int32
41. Public WriteTotalTimeoutConstant As Int32
42. End Structure
43.
44. 'Declare constants.
45. Public Const GENERIC_READ As Int32 = &H80000000
46. Public Const GENERIC_WRITE As Int32 = &H40000000
47. Public Const OPEN_EXISTING As Int32 = 3
48. Public Const FILE_ATTRIBUTE_NORMAL As Int32 = &H80
49. Public Const NOPARITY As Int32 = 0
50. Public Const ONESTOPBIT As Int32 = 0
51.
52. 'Declare references to external functions.
53. Public Declare Auto Function CreateFile Lib "kernel32.dll" _
54. (ByVal lpFileName As String, ByVal dwDesiredAccess As Int32, _
55. ByVal dwShareMode As Int32, ByVal lpSecurityAttributes As IntPtr, _
56. ByVal dwCreationDisposition As Int32, ByVal dwFlagsAndAttributes As Int32, _
57. ByVal hTemplateFile As IntPtr) As IntPtr
58.
59. Public Declare Auto Function GetCommState Lib "kernel32.dll" (ByVal nCid As IntPtr, _
60. ByRef lpDCB As DCB) As Boolean
61.
62. Public Declare Auto Function SetCommState Lib "kernel32.dll" (ByVal nCid As IntPtr, _
63. ByRef lpDCB As DCB) As Boolean
64.
65. Public Declare Auto Function GetCommTimeouts Lib "kernel32.dll" (ByVal hFile As IntPtr, _
66. ByRef lpCommTimeouts As COMMTIMEOUTS) As Boolean
67.
68. Public Declare Auto Function SetCommTimeouts Lib "kernel32.dll" (ByVal hFile As IntPtr, _
69. ByRef lpCommTimeouts As COMMTIMEOUTS) As Boolean
70.
71. Public Declare Auto Function WriteFile Lib "kernel32.dll" (ByVal hFile As IntPtr, _
72. ByVal lpBuffer As Byte(), ByVal nNumberOfBytesToWrite As Int32, _
73. ByRef lpNumberOfBytesWritten As Int32, ByVal lpOverlapped As IntPtr) As Boolean
74.
75. Public Declare Auto Function ReadFile Lib "kernel32.dll" (ByVal hFile As IntPtr, _
76. ByVal lpBuffer As Byte(), ByVal nNumberOfBytesToRead As Int32, _
77. ByRef lpNumberOfBytesRead As Int32, ByVal lpOverlapped As IntPtr) As Boolean
78.
Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal hObject As IntPtr) As Boolean
79. Before you can access a serial port or a parallel port, you must obtain a handle to the appropriate port and then configure the port communications. To do this, add the following initialization code to Module1.vb after the Sub Main statement.

Note To establish communication with the LPTx port, you must stop the Print Spooler service. To do this, use the Services tool in Administrative Tools.
80. ' Declare the local variables that you will use in the code.
81. Dim hSerialPort, hParallelPort As IntPtr
82. Dim Success As Boolean
83. Dim MyDCB As DCB
84. Dim MyCommTimeouts As COMMTIMEOUTS
85. Dim BytesWritten, BytesRead As Int32
86. Dim Buffer() As Byte
87.
88. ' Declare the variables to use for encoding.
89. Dim oEncoder As New System.Text.ASCIIEncoding
90. Dim oEnc As System.Text.Encoding = oEncoder.GetEncoding(1252)
91.
92. ' Convert String to Byte().
93. Buffer = oEnc.GetBytes("Test")
94. Try
95. ' Access the serial port.
96. Console.WriteLine("Accessing the COM1 serial port")
97. ' Obtain a handle to the COM1 serial port.
98. hSerialPort = CreateFile("COM1", GENERIC_READ Or GENERIC_WRITE, 0, IntPtr.Zero, _
99. OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, IntPtr.Zero)
100. ' Verify that the obtained handle is valid.
101. If hSerialPort.ToInt32 = -1 Then
102. Throw New CommException("Unable to obtain a handle to the COM1 port")
103. End If
104. ' Retrieve the current control settings.
105. Success = GetCommState(hSerialPort, MyDCB)
106. If Success = False Then
107. Throw New CommException("Unable to retrieve the current control settings")
108. End If
109. ' Modify the properties of the retrieved DCB structure as appropriate.
110. ' WARNING: Make sure to modify the properties according to their supported values.
111. MyDCB.BaudRate = 9600
112. MyDCB.ByteSize = 8
113. MyDCB.Parity = NOPARITY
114. MyDCB.StopBits = ONESTOPBIT
115. ' Reconfigure COM1 based on the properties of the modified DCB structure.
116. Success = SetCommState(hSerialPort, MyDCB)
117. If Success = False Then
118. Throw New CommException("Unable to reconfigure COM1")
119. End If
120. ' Retrieve the current time-out settings.
121. Success = GetCommTimeouts(hSerialPort, MyCommTimeouts)
122. If Success = False Then
123. Throw New CommException("Unable to retrieve current time-out settings")
124. End If
125. ' Modify the properties of the retrieved COMMTIMEOUTS structure as appropriate.
126. ' WARNING: Make sure to modify the properties according to their supported values.
127. MyCommTimeouts.ReadIntervalTimeout = 0
128. MyCommTimeouts.ReadTotalTimeoutConstant = 0
129. MyCommTimeouts.ReadTotalTimeoutMultiplier = 0
130. MyCommTimeouts.WriteTotalTimeoutConstant = 0
131. MyCommTimeouts.WriteTotalTimeoutMultiplier = 0
132. ' Reconfigure the time-out settings, based on the properties of the modified COMMTIMEOUTS structure.
133. Success = SetCommTimeouts(hSerialPort, MyCommTimeouts)
134. If Success = False Then
135. Throw New CommException("Unable to reconfigure the time-out settings")
136. End If
137. ' Write data to COM1.
138. Console.WriteLine("Writing the following data to COM1: Test")
139. Success = WriteFile(hSerialPort, Buffer, Buffer.Length, BytesWritten, IntPtr.Zero)
140. If Success = False Then
141. Throw New CommException("Unable to write to COM1")
142. End If
143. ' Read data from COM1.
144. Success = ReadFile(hSerialPort, Buffer, BytesWritten, BytesRead, IntPtr.Zero)
145. If Success = False Then
146. Throw New CommException("Unable to read from COM1")
147. End If
148. Catch ex As Exception
149. Console.WriteLine(Ex.Message)
150. Finally
151. ' Release the handle to COM1.
152. Success = CloseHandle(hSerialPort)
153. If Success = False Then
154. Console.WriteLine("Unable to release handle to COM1")
155. End If
156. End Try
157.
158. Try
159. ' Parallel port.
160. Console.WriteLine("Accessing the LPT1 parallel port")
161. ' Obtain a handle to the LPT1 parallel port.
162. hParallelPort = CreateFile("LPT1", GENERIC_READ Or GENERIC_WRITE, 0, IntPtr.Zero, _
163. OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, IntPtr.Zero)
164. ' Verify that the obtained handle is valid.
165. If hParallelPort.ToInt32 = -1 Then
166. Throw New CommException("Unable to obtain a handle to the LPT1 port")
167. End If
168. ' Retrieve the current control settings.
169. Success = GetCommState(hParallelPort, MyDCB)
170. If Success = False Then
171. Throw New CommException("Unable to retrieve the current control settings")
172. End If
173. ' Modify the properties of the retrieved DCB structure as appropriate.
174. ' WARNING: Make sure to modify the properties according to their supported values.
175. MyDCB.BaudRate = 9600
176. MyDCB.ByteSize = 8
177. MyDCB.Parity = NOPARITY
178. MyDCB.StopBits = ONESTOPBIT
179. ' Reconfigure LPT1 based on the properties of the modified DCB structure.
180. Success = SetCommState(hParallelPort, MyDCB)
181. If Success = False Then
182. Throw New CommException("Unable to reconfigure LPT1")
183. End If
184. ' Retrieve the current time-out settings.
185. Success = GetCommTimeouts(hParallelPort, MyCommTimeouts)
186. If Success = False Then
187. Throw New CommException("Unable to retrieve current time-out settings")
188. End If
189. ' Modify the properties of the retrieved COMMTIMEOUTS structure as appropriate.
190. ' WARNING: Make sure to modify the properties according to their supported values.
191. MyCommTimeouts.ReadIntervalTimeout = 0
192. MyCommTimeouts.ReadTotalTimeoutConstant = 0
193. MyCommTimeouts.ReadTotalTimeoutMultiplier = 0
194. MyCommTimeouts.WriteTotalTimeoutConstant = 0
195. MyCommTimeouts.WriteTotalTimeoutMultiplier = 0
196. ' Reconfigure the time-out settings, based on the properties of the modified COMMTIMEOUTS structure.
197. Success = SetCommTimeouts(hParallelPort, MyCommTimeouts)
198. If Success = False Then
199. Throw New CommException("Unable to reconfigure the time-out settings")
200. End If
201. ' Write data to LPT1.
202. ' Note: You cannot read data from a parallel port by calling the ReadFile function.
203. Console.WriteLine("Writing the following data to LPT1: Test")
204. Success = WriteFile(hParallelPort, Buffer, Buffer.Length, BytesWritten, IntPtr.Zero)
205. If Success = False Then
206. Throw New CommException("Unable to write to LPT1")
207. End If
208. Catch ex As Exception
209. Console.WriteLine(Ex.Message)
210. Finally
211. ' Release the handle to LPT1.
212. Success = CloseHandle(hParallelPort)
213. If Success = False Then
214. Console.WriteLine("Unable to release handle to LPT1")
215. End If
216. End Try
217.
218. Console.WriteLine("Press ENTER to quit")
219. Console.ReadLine()
220. On the Build menu, click Build Solution.
221. On the Debug menu, click Start to run the application.

You may receive the following text in the console:
Accessing the COM1 serial port
Writing the following data to COM1: Test

Read the following data from COM1: Serial Data
Accessing the LPT1 parallel port

Writing the following data to LPT1: Test

Press ENTER to quit
Note Serial Data represents the data that you read from the serial port.
222. To close the application, press the ENTER key in the console.
Back to the top
Sample code listing (Module1.vb)
Before you use the following code example, replace COM1 with the name of your serial port, and replace LPT1 with the name of your parallel port.

Note The following code works only when serial devices and parallel devices are connected to the corresponding ports on the computer. If you do not connect these devices and you run the program, the program waits indefinitely.
Option Strict On

' Define a CommException class that inherits from the ApplicationException class.
' Then throw an object of type CommException when you receive an error message.
Class CommException
Inherits ApplicationException
Sub New(ByVal Reason As String)
MyBase.New(Reason)
End Sub
End Class

Module Module1
'Declare structures
Public Structure DCB
Public DCBlength As Int32
Public BaudRate As Int32
Public fBitFields As Int32
Public wReserved As Int16
Public XonLim As Int16
Public XoffLim As Int16
Public ByteSize As Byte
Public Parity As Byte
Public StopBits As Byte
Public XonChar As Byte
Public XoffChar As Byte
Public ErrorChar As Byte
Public EofChar As Byte
Public EvtChar As Byte
Public wReserved1 As Int16 'Reserved; Do Not Use
End Structure

Public Structure COMMTIMEOUTS
Public ReadIntervalTimeout As Int32
Public ReadTotalTimeoutMultiplier As Int32
Public ReadTotalTimeoutConstant As Int32
Public WriteTotalTimeoutMultiplier As Int32
Public WriteTotalTimeoutConstant As Int32
End Structure

'Declare constants.
Public Const GENERIC_READ As Int32 = &H80000000
Public Const GENERIC_WRITE As Int32 = &H40000000
Public Const OPEN_EXISTING As Int32 = 3
Public Const FILE_ATTRIBUTE_NORMAL As Int32 = &H80
Public Const NOPARITY As Int32 = 0
Public Const ONESTOPBIT As Int32 = 0

'Declare references to external functions.
Public Declare Auto Function CreateFile Lib "kernel32.dll" _
(ByVal lpFileName As String, ByVal dwDesiredAccess As Int32, _
ByVal dwShareMode As Int32, ByVal lpSecurityAttributes As IntPtr, _
ByVal dwCreationDisposition As Int32, ByVal dwFlagsAndAttributes As Int32, _
ByVal hTemplateFile As IntPtr) As IntPtr

Public Declare Auto Function GetCommState Lib "kernel32.dll" (ByVal nCid As IntPtr, _
ByRef lpDCB As DCB) As Boolean

Public Declare Auto Function SetCommState Lib "kernel32.dll" (ByVal nCid As IntPtr, _
ByRef lpDCB As DCB) As Boolean

Public Declare Auto Function GetCommTimeouts Lib "kernel32.dll" (ByVal hFile As IntPtr, _
ByRef lpCommTimeouts As COMMTIMEOUTS) As Boolean

Public Declare Auto Function SetCommTimeouts Lib "kernel32.dll" (ByVal hFile As IntPtr, _
ByRef lpCommTimeouts As COMMTIMEOUTS) As Boolean

Public Declare Auto Function WriteFile Lib "kernel32.dll" (ByVal hFile As IntPtr, _
ByVal lpBuffer As Byte(), ByVal nNumberOfBytesToWrite As Int32, _
ByRef lpNumberOfBytesWritten As Int32, ByVal lpOverlapped As IntPtr) As Boolean

Public Declare Auto Function ReadFile Lib "kernel32.dll" (ByVal hFile As IntPtr, _
ByVal lpBuffer As Byte(), ByVal nNumberOfBytesToRead As Int32, _
ByRef lpNumberOfBytesRead As Int32, ByVal lpOverlapped As IntPtr) As Boolean

Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal hObject As IntPtr) As Boolean

Sub Main()

' Declare local variables that you will use in the code.
Dim hSerialPort, hParallelPort As IntPtr
Dim Success As Boolean
Dim MyDCB As DCB
Dim MyCommTimeouts As COMMTIMEOUTS
Dim BytesWritten, BytesRead As Int32
Dim Buffer() As Byte

' Declare variables to use for encoding.
Dim oEncoder As New System.Text.ASCIIEncoding
Dim oEnc As System.Text.Encoding = oEncoder.GetEncoding(1252)

' Convert String to Byte().
Buffer = oEnc.GetBytes("Test")

Try
' Serial port.
Console.WriteLine("Accessing the COM1 serial port")
' Obtain a handle to the COM1 serial port.
hSerialPort = CreateFile("COM1", GENERIC_READ Or GENERIC_WRITE, 0, IntPtr.Zero, _
OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, IntPtr.Zero)
' Verify that the obtained handle is valid.
If hSerialPort.ToInt32 = -1 Then
Throw New CommException("Unable to obtain a handle to the COM1 port")
End If
' Retrieve the current control settings.
Success = GetCommState(hSerialPort, MyDCB)
If Success = False Then
Throw New CommException("Unable to retrieve the current control settings")
End If
' Modify the properties of the retrieved DCB structure as appropriate.
' WARNING: Make sure to modify the properties according to their supported values.
MyDCB.BaudRate = 9600
MyDCB.ByteSize = 8
MyDCB.Parity = NOPARITY
MyDCB.StopBits = ONESTOPBIT
' Reconfigure COM1 based on the properties of the modified DCB structure.
Success = SetCommState(hSerialPort, MyDCB)
If Success = False Then
Throw New CommException("Unable to reconfigure COM1")
End If
' Retrieve the current time-out settings.
Success = GetCommTimeouts(hSerialPort, MyCommTimeouts)
If Success = False Then
Throw New CommException("Unable to retrieve current time-out settings")
End If
' Modify the properties of the retrieved COMMTIMEOUTS structure as appropriate.
' WARNING: Make sure to modify the properties according to their supported values.
MyCommTimeouts.ReadIntervalTimeout = 0
MyCommTimeouts.ReadTotalTimeoutConstant = 0
MyCommTimeouts.ReadTotalTimeoutMultiplier = 0
MyCommTimeouts.WriteTotalTimeoutConstant = 0
MyCommTimeouts.WriteTotalTimeoutMultiplier = 0
' Reconfigure the time-out settings, based on the properties of the modified COMMTIMEOUTS structure.
Success = SetCommTimeouts(hSerialPort, MyCommTimeouts)
If Success = False Then
Throw New CommException("Unable to reconfigure the time-out settings")
End If
' Write data to COM1.
Console.WriteLine("Writing the following data to COM1: Test")
Success = WriteFile(hSerialPort, Buffer, Buffer.Length, BytesWritten, IntPtr.Zero)
If Success = False Then
Throw New CommException("Unable to write to COM1")
End If
' Read data from COM1.
Success = ReadFile(hSerialPort, Buffer, BytesWritten, BytesRead, IntPtr.Zero)
If Success = False Then
Throw New CommException("Unable to read from COM1")
End If
Catch ex As Exception
Console.WriteLine(Ex.Message)
Finally
' Release the handle to COM1.
Success = CloseHandle(hSerialPort)
If Success = False Then
Console.WriteLine("Unable to release handle to COM1")
End If
End Try

Try
' Parallel port.
Console.WriteLine("Accessing the LPT1 parallel port")
' Obtain a handle to the LPT1 parallel port.
hParallelPort = CreateFile("LPT1", GENERIC_READ Or GENERIC_WRITE, 0, IntPtr.Zero, _
OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, IntPtr.Zero)
' Verify that the obtained handle is valid.
If hParallelPort.ToInt32 = -1 Then
Throw New CommException("Unable to obtain a handle to the LPT1 port")
End If
' Retrieve the current control settings.
Success = GetCommState(hParallelPort, MyDCB)
If Success = False Then
Throw New CommException("Unable to retrieve the current control settings")
End If
' Modify the properties of the retrieved DCB structure as appropriate.
' WARNING: Make sure to modify the properties according to their supported values.
MyDCB.BaudRate = 9600
MyDCB.ByteSize = 8
MyDCB.Parity = NOPARITY
MyDCB.StopBits = ONESTOPBIT
' Reconfigure LPT1 based on the properties of MyDCB.
Success = SetCommState(hParallelPort, MyDCB)
If Success = False Then
Throw New CommException("Unable to reconfigure LPT1")
End If
' Reconfigure LPT1 based on the properties of the modified DCB structure.
Success = GetCommTimeouts(hParallelPort, MyCommTimeouts)
If Success = False Then
Throw New CommException("Unable to retrieve current time-out settings")
End If
' Modify the properties of the retrieved COMMTIMEOUTS structure as appropriate.
' WARNING: Make sure to modify the properties according to their supported values.
MyCommTimeouts.ReadIntervalTimeout = 0
MyCommTimeouts.ReadTotalTimeoutConstant = 0
MyCommTimeouts.ReadTotalTimeoutMultiplier = 0
MyCommTimeouts.WriteTotalTimeoutConstant = 0
MyCommTimeouts.WriteTotalTimeoutMultiplier = 0
' Reconfigure the time-out settings, based on the properties of the modified COMMTIMEOUTS structure.
Success = SetCommTimeouts(hParallelPort, MyCommTimeouts)
If Success = False Then
Throw New CommException("Unable to reconfigure the time-out settings")
End If
' Write data to LPT1.
' Note: You cannot read data from a parallel port by calling the ReadFile function.
Console.WriteLine("Writing the following data to LPT1: Test")
Success = WriteFile(hParallelPort, Buffer, Buffer.Length, BytesWritten, IntPtr.Zero)
If Success = False Then
Throw New CommException("Unable to write to LPT1")
End If
Catch ex As Exception
Console.WriteLine(Ex.Message)
Finally
' Release the handle to LPT1.
Success = CloseHandle(hParallelPort)
If Success = False Then
Console.WriteLine("Unable to release handle to LPT1")
End If
End Try

Console.WriteLine("Press ENTER to quit")
Console.ReadLine()

End Sub

End Module
Back to the top
Troubleshoot
• When you run the application, you may receive the following error message:
System.NullReferenceException: Object reference not set to an instance of an object.
You may receive this error message because your function declarations are incorrect. This error message typically occurs when your declarations contain ByVal parameters instead of ByRef parameters.
• Your application may wait indefinitely when you invoke the ReadFile function. This behavior typically occurs when you set the read time-outs to zero in the retrieved COMMTIMEOUTS structure. To resolve this issue, modify the properties of the COMMTIMEOUTS structure, as appropriate.
Back to the top
REFERENCES
For more information, visit the following Microsoft Developer Network (MSDN) Web...
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Communications resources
http://msdn2.microsoft.com/en-us/library/aa363196.aspx (http://msdn2.microsoft.com/en-us/library/aa363196.aspx)

Interoperating with unmanaged code
http://msdn2.microsoft.com/en-us/library/sd10k43k(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/sd10k43k(vs.71).aspx)

System.Runtime.InteropServices namespace
http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices(vs.71).aspx)

DllImportAttribute class
http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.dllimportattribute(vs.71).aspx (http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.dllimportattribute(vs.71).aspx)

The Windows API and other dynamic-link libraries
http://msdn2.microsoft.com/en-us/library/aa141322(office.10).aspx (http://msdn2.microsoft.com/en-us/library/aa141322(office.10).aspx)

Understanding handles
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrUnderstandingHandles.asp (http://msdn2.microsoft.com/en-us/library/aa141354(office.10).aspx)
For more information about AT modem commands, click the following article number to view the article in the Microsoft Knowledge Base:
164660 (http://support.microsoft.com/kb/164660/ ) AT modem command reference
Back to the top
________________________________________
APPLIES TO
• Microsoft Visual Basic .NET 2003 Standard Edition
• Microsoft Visual Basic .NET 2002 Standard Edition
Back to the top
Keywords: kbvs2005swept kbvs2005doesnotapply kbserial kbsdk kbkern32dll kbdll kbcommport kbapi kb32bitonly kbinterop kbconsole kbconfig kbappdev kbprogramming kbsample kbhowtomaster KB823179

Crystal Report 8.5

2011-01-15T22:54:00.001+05:30

1. What is Crystal Report?
Ans: Crystal report is a report generation tool. Generally have interface with VB6. Crystal report basically generates dynamic data. You can format the data in whichever way you feel like.
2. Can we use Crystal report as a stand-alone application?
Ans: Generally we use Crystal Reports with VB6. However we can make crystal report stand-alone application
also. But for that limitation is for viewing the report user should have crystal reports installed on his/her PC.
3. How do we connect to the database?
Ans: There are two ways of creating the report: -
1. Use crystal report built in query.
2. Use the tool ‘ Crystal SQL Designer’ provided by crystal report.
When you create report using crystal report built in query then it asks for the data source name that you have created. When you run the report, then for the first time it will ask for the user id and password. After that it will store the same.
When you create ‘.qry’ using ‘Crystal SQL’ Designer then at that time it will ask for the user id and password. When you run the report for the first time instead of asking for the user id and password it will ask for the ‘.qry’ file location. You can change the query location also. For that open the report, select ‘Set Location’ option in Database menu item, and set the location.
4. How do we format field?
Ans: For formatting any field just right click on it and you will get many options like ‘Format Field.’ ‘Browse field data’ etc. Click on ‘Format Field.’ You can align data, suppress, make it multiline, change the font size, style, and type, make it hyperlink etc.
If it is an amount field then you can display currency symbol also. Right click on the field select ‘Format Field’.
5. Can we give parameters to the report?
Ans: We can very well give parameters to the report. For creating parameters select ‘Parameter Field’ in Insert menu item. ‘Create Parameter Field’ dialog box will popped up, it will ask for the name of parameter, prompting text and datatype. Now when you run the report it will prompt for these parameters.
6.Can we create our own formulas in reports?
Ans: We can create our own formulas in reports. Select ‘Formula Field’ in Insert menu item. Write the formula in ‘Formula Editor’. Here you will get ‘Field Tree’, ‘Function Tree’, and ‘Operator Tree’, which will display the report fields, functions that are supported by crystal reports (like CDATE () etc.), operators (arithmetic, strings etc.) respectively.
7. Can we create report using more than one database?
Ans: We can create report using more than one database like Oracle, Access. Create data source name for both the databases. Select tables from them and create the report.
Only restriction is if you use two databases then you cannot see the SQL generated by crystal reports.
8. Can we export data of reports into other format like in world doc etc?
Ans: Generated data can be exported to word doc, or in rich text format. Just click on ‘Export’ icon in the menu. Export dialog box will be popped up. It will ask for the ‘Format’ like comma-separated value (csv) etc and the ‘Destination’ like disk, application etc. After that it will ask for the file name and save the data.
Only restriction is formatting of data will be lost, but crystal report will try to maintain as much formatting as it can.
9. Can we use our own SQL for creating a report?
Ans: We can also make our own query using ‘Crystal SQL Designer’ tool provided by SQL. Here you can insert your SQL statement as such. It will save this file as ‘.qry’ . And when you create a report instead of using ‘Database’ button use ‘Crystal SQL Statement’ button.
10. Can we edit SQL made by Crystal reports?
Ans: We cannot edit the SQL made by crystal reports. However we can view the SQL. For that select ‘Show SQL Query’ in Database menu item. Limitation is if you are using only one database. If you use two databases then you can’t even view the SQL prepared by crystal report.
11. Are there any limitations in crystal reports?
Ans: There are certain limitations in crystal reports. They are: -
1. If database is having field whose length is more than 255 characters, then you cannot make formula using that field.
2. While exporting data formatting is lost.
3. When you browse data just by right clicking on the field then it displays that is there in the database not the data selected by the query.

12. Can we suppress printing in crystal reports if 0 records are fetched?
Ans: Yes, we can suppress printing if no records are fetched. Select ‘Report Options’ in File menu item. ‘Report Options’ dialog box will pop up. In that there is one option ‘ Suppress printing if no records’ Check this option. If no records are found then nothing will be printed on the report.
13. What are the sections that we have in Crystal reports?
Ans: Report has got standard sections like ‘Page Header’, ‘Page Footer’, ‘Report Header’, ‘Report Footer’, and ‘Details’.
However you can add other sections also. Select ‘Sections’ in the Insert menu item. You can insert group sections also.
If you don’t want to show any section just right click on that section and suppress that.
14. Can we add any database field once we have chosen ‘Close’ button?
Ans: Yes, we can add any database field afterwards also. Select ‘Database Field’ in Insert menu item.
If you are using crystal report built in query then it will display the tables that you have selected. And you can select whichever field you want to display on the report.
But if you are using ‘.qry’ file then it will display Query and you can select only those field, which are there in the query.
15. Does Crystal Report support all the functions that we have in Oracle?
Ans: No, Crystal report does not support all the functions. Like Decode function is there in SQL but not there is crystal report. You need to convert that in crystal report format (in if and else etc.).
However if you use ‘.qry’ files then it take the SQL as such. There is no need of changing any syntax.
16. Can we use stored procedure for creating the report?
Ans: Yes, we can use stored procedure.
17. Is there any feature like summing total in crystal report?
Ans: Crystal reports provide features like grand total, sub-total, running total etc. You can select any of these features in Insert menu item.
You can sum up records on the basis of each record or on change of group using ‘Running Total ‘ option in Insert menu item.
18. I am using two tables one is of access database and other is of oracle database, I am getting an error saying that ‘SQL odbc error’ what should I do?
Ans: If you are getting such an error then click the icon for ‘Report Expert’. It will give a warning saying that formatting will be lost. Ignore this you will get ‘Standard Report Expert’ dialog box. Reverse the links of access database table and it will work.

Q) What versions of Crystal Reports have you used? Please indicate both release version (9, 10, XI) and type of version (.NET or standalone boxed exition, for example).

A) I was willing to consider individuals who were primarily programming version users, if they were able to adequately answer questions about Crystal Reports functionality. It has been my experience, however, that "programmer" Crystal Reports writers have vastly different skill sets and opinions about solutions that "report writer" or "business user" Crystal Reports writers.

Q) What is 'Set Location'

A) This is very basic Crystal Reports functionality. If the candidate doesn't know what this is, then that's a red flag.

Q) Please describe, in plain English, the most complex report you've ever written. What made it complex and how did you resolve the issues that presented themselves?

A) This question helps me to understand their level of understanding of functionality, their ability to potentially address complex requirements and their creativity.

Q) Have you ever written a repot against a View or Stored Procedure?

A) No? They might be skilled in Crystal Reports, but may not understand the relationship between Crystal and the database. They may also not be skilled in SQL.

Q) Is it better to build a report against a View or a Stored Procedure and why? Please explain...

A) This is a very good question, in my opinion. First, the right answer is neither. It really depends on the situation. That being said, there is a contingent of report writers out there that have been brainwashed into saying a 'stored procedure is always better, becuase its pre-compiled'. In my opinion, this is bunk and here's why:

A stored procedure is absolutely great to use if you're going to manipulate a lot of data or crunch a lot of numbers to return aggregates. If you're simply creating a SELECT/FROM/WHERE query, it's a complete waste. Furthermore, using a Stored Procedure (or a SQL Command Object, for that matter) can severely limit the functionality you would otherwise have in Crystal Reports. For example, a common report requirement is to have a parameter that allows the user to select one, multiple or all items in the parameter list. Crystal Reports allows this functionalty through multi-value parameter selection. Stored procedures do not allow this functionality...

A View can be written to contain all of the necessary fields, joins, subqueries, etc..., but still be open enough to be used by a variety of reports. For example, I have a client who needed 15 reports, all of which were to be written against a particular module of their application. All of the reports had very similar fields, they were mostly just different in display and in the selection criteria. As such, I created a single view with both a SELECT and FROM clause, but no WHERE clause. I then built the 15 different reports all against the same view, but added selection critieria to each report. By doing so, they all used a single, easily maintainable database object, but were each unique and efficient...

Q) What is a SQL Expression field in Crystal Reports?

A) I honestly don't expect any candidate to know, much less explain their use. Almost nobody knows and most of the ones that do don't know everything you can do with them. I'm an expert using them and I don't know everything about them, I'm sure. If the incumbent knows what they are and how to use them, that's a bonus and probably indicates that the the candidate is an advanced user.

Of course, there are a ton of other questions I could provide, but this would get very long! If you're going to test the candidates, I would check to see if they use the Select Expert or the Record Selection Criteria. If its the former, its a dead giveaway that they are novices...

Interview Questions

2011-01-15T22:52:00.005+05:30

1. Introduction
1.1 What is .NET?
That's difficult to sum up in a sentence. According to Microsoft, .NET is a "revolutionary new platform, built on open Internet protocols and standards, with tools and services that meld computing and communications in new ways".
A more practical definition would be that .NET is a new environment for developing and running software applications, featuring ease of development of web-based services, rich standard run-time services available to components written in a variety of programming languages, and inter-language and inter-machine interoperability.
Note that when the term ".NET" is used in this FAQ it refers only to the new .NET runtime and associated technologies. This is sometimes called the ".NET Framework". This FAQ does NOT cover any of the various other existing and new products/technologies that Microsoft are attaching the .NET name to (e.g. SQL Server.NET).
1.2 Does .NET only apply to people building web-sites?
No. If you write any Windows software (using ATL/COM, MFC, VB, or even raw Win32), .NET may offer a viable alternative (or addition) to the way you do things currently. Of course, if you do develop web sites, then .NET has lots to interest you - not least ASP.NET.
1.3 When was .NET announced?
Bill Gates delivered a keynote at Forum 2000, held June 22, 2000, outlining the .NET 'vision'. The July 2000 PDC had a number of sessions on .NET technology, and delegates were given CDs containing a pre-release version of the .NET framework/SDK and Visual Studio.NET.
1.4 When was the first version of .NET released?
The final version of the 1.0 SDK and runtime was made publicly available around 6pm PST on 15-Jan-2002. At the same time, the final version of Visual Studio.NET was made available to MSDN subscribers.
1.5 What tools can I use to develop .NET applications?
There are a number of tools, described here in ascending order of cost:
• .NET Framework SDK: The SDK is free and includes command-line compilers for C++, C#, and VB.NET and various other utilities to aid development.
• ASP.NET Web Matrix: This is a free ASP.NET development environment from Microsoft. As well as a GUI development environment, the download includes a simple web server that can be used instead of IIS to host ASP.NET apps. This opens up ASP.NET development to users of Windows XP Home Edition, which cannot run IIS.
• Microsoft Visual C# .NET Standard 2003: This is a cheap (around $100) version of Visual Studio limited to one language and also with limited wizard support. For example, there's no wizard support for class libraries or custom UI controls. Useful for beginners to learn with, or for savvy developers who can work around the deficiencies in the supplied wizards. As well as C#, there are VB.NET and C++ versions.
• Microsoft Visual Studio.NET Professional 2003: If you have a license for Visual Studio 6.0, you can get the upgrade. You can also upgrade from VS.NET 2002 for a token $30. Visual Studio.NET includes support for all the MS languages (C#, C++, VB.NET) and has extensive wizard support.
At the top end of the price spectrum are the Visual Studio.NET 2003 Enterprise and Enterprise Architect editions. These offer extra features such as Visual Sourcesafe (version control), and performance and analysis tools. Check out the Visual Studio.NET Feature Comparison at http://msdn.microsoft.com/vstudio/howtobuy/choosing.asp.
1.6 What platforms does the .NET Framework run on?
The runtime supports Windows XP, Windows 2000, NT4 SP6a and Windows ME/98. Windows 95 is not supported. Some parts of the framework do not work on all platforms - for example, ASP.NET is only supported on Windows XP and Windows 2000. Windows 98/ME cannot be used for development.
IIS is not supported on Windows XP Home Edition, and so cannot be used to host ASP.NET. However, the ASP.NET Web Matrix web server does run on XP Home.
The Mono project is attempting to implement the .NET framework on Linux.
1.7 What languages does the .NET Framework support?
MS provides compilers for C#, C++, VB and JScript. Other vendors have announced that they intend to develop .NET compilers for languages such as COBOL, Eiffel, Perl, Smalltalk and Python.
1.8 Will the .NET Framework go through a standardisation process?
From http://msdn.microsoft.com/net/ecma/: "On December 13, 2001, the ECMA General Assembly ratified the C# and common language infrastructure (CLI) specifications into international standards. The ECMA standards will be known as ECMA-334 (C#) and ECMA-335 (the CLI)."
2. Basic terminology
2.1 What is the CLR?
CLR = Common Language Runtime. The CLR is a set of standard resources that (in theory) any .NET program can take advantage of, regardless of programming language. Robert Schmidt (Microsoft) lists the following CLR resources in his MSDN PDC# article:
• Object-oriented programming model (inheritance, polymorphism, exception handling, garbage collection)
• Security model
• Type system
• All .NET base classes
• Many .NET framework classes
• Development, debugging, and profiling tools
• Execution and code management
• IL-to-native translators and optimizers
What this means is that in the .NET world, different programming languages will be more equal in capability than they have ever been before, although clearly not all languages will support all CLR services.
2.2 What is the CTS?
CTS = Common Type System. This is the range of types that the .NET runtime understands, and therefore that .NET applications can use. However note that not all .NET languages will support all the types in the CTS. The CTS is a superset of the CLS.
2.3 What is the CLS?
CLS = Common Language Specification. This is a subset of the CTS which all .NET languages are expected to support. The idea is that any program which uses CLS-compliant types can interoperate with any .NET program written in any language.
In theory this allows very tight interop between different .NET languages - for example allowing a C# class to inherit from a VB class.
2.4 What is IL?
IL = Intermediate Language. Also known as MSIL (Microsoft Intermediate Language) or CIL (Common Intermediate Language). All .NET source code (of any language) is compiled to IL. The IL is then converted to machine code at the point where the software is installed, or at run-time by a Just-In-Time (JIT) compiler.
2.5 What is C#?
C# is a new language designed by Microsoft to work with the .NET framework. In their "Introduction to C#" whitepaper, Microsoft describe C# as follows:
"C# is a simple, modern, object oriented, and type-safe programming language derived from C and C++. C# (pronounced “C sharp”) is firmly planted in the C and C++ family tree of languages, and will immediately be familiar to C and C++ programmers. C# aims to combine the high productivity of Visual Basic and the raw power of C++."
Substitute 'Java' for 'C#' in the quote above, and you'll see that the statement still works pretty well :-).
If you are a C++ programmer, you might like to check out my C# FAQ.
2.6 What does 'managed' mean in the .NET context?
The term 'managed' is the cause of much confusion. It is used in various places within .NET, meaning slightly different things.
Managed code: The .NET framework provides several core run-time services to the programs that run within it - for example exception handling and security. For these services to work, the code must provide a minimum level of information to the runtime. Such code is called managed code. All C# and Visual Basic.NET code is managed by default. VS7 C++ code is not managed by default, but the compiler can produce managed code by specifying a command-line switch (/com+).
Managed data: This is data that is allocated and de-allocated by the .NET runtime's garbage collector. C# and VB.NET data is always managed. VS7 C++ data is unmanaged by default, even when using the /com+ switch, but it can be marked as managed using the __gc keyword.
Managed classes: This is usually referred to in the context of Managed Extensions (ME) for C++. When using ME C++, a class can be marked with the __gc keyword. As the name suggests, this means that the memory for instances of the class is managed by the garbage collector, but it also means more than that. The class becomes a fully paid-up member of the .NET community with the benefits and restrictions that brings. An example of a benefit is proper interop with classes written in other languages - for example, a managed C++ class can inherit from a VB class. An example of a restriction is that a managed class can only inherit from one base class.
2.7 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).
3. Assemblies
3.1 What is an assembly?
An assembly is sometimes described as a logical .EXE or .DLL, and can be an application (with a main entry point) or a library. An assembly consists of one or more files (dlls, exes, html files etc), and represents a group of resources, type definitions, and implementations of those types. An assembly may also contain references to other assemblies. These resources, types and references are described in a block of data called a manifest. The manifest is part of the assembly, thus making the assembly self-describing.
An important aspect of assemblies is that they are part of the identity of a type. The identity of a type is the assembly that houses it combined with the type name. This means, for example, that if assembly A exports a type called T, and assembly B exports a type called T, the .NET runtime sees these as two completely different types. Furthermore, don't get confused between assemblies and namespaces - namespaces are merely a hierarchical way of organising type names. To the runtime, type names are type names, regardless of whether namespaces are used to organise the names. It's the assembly plus the typename (regardless of whether the type name belongs to a namespace) that uniquely indentifies a type to the runtime.
Assemblies are also important in .NET with respect to security - many of the security restrictions are enforced at the assembly boundary.
Finally, assemblies are the unit of versioning in .NET - more on this below.
3.2 How can I produce an assembly?
The simplest way to produce an assembly is directly from a .NET compiler. For example, the following C# program:
public class CTest
{
public CTest()
{
System.Console.WriteLine( "Hello from CTest" );
}
}
can be compiled into a library assembly (dll) like this:
csc /t:library ctest.cs
You can then view the contents of the assembly by running the "IL Disassembler" tool that comes with the .NET SDK.
Alternatively you can compile your source into modules, and then combine the modules into an assembly using the assembly linker (al.exe). For the C# compiler, the /target:module switch is used to generate a module instead of an assembly.
3.3 What is the difference between a private assembly and a shared assembly?
• Location and visibility: A private assembly is normally used by a single application, and is stored in the application's directory, or a sub-directory beneath. A shared assembly is normally stored in the global assembly cache, which is a repository of assemblies maintained by the .NET runtime. Shared assemblies are usually libraries of code which many applications will find useful, e.g. the .NET framework classes.

• Versioning: The runtime enforces versioning constraints only on shared assemblies, not on private assemblies.
3.4 How do assemblies find each other?
By searching directory paths. There are several factors which can affect the path (such as the AppDomain host, and application configuration files), but for private assemblies the search path is normally the application's directory and its sub-directories. For shared assemblies, the search path is normally same as the private assembly path plus the shared assembly cache.
3.5 How does assembly versioning work?
Each assembly has a version number called the compatibility version. Also each reference to an assembly (from another assembly) includes both the name and version of the referenced assembly.
The version number has four numeric parts (e.g. 5.5.2.33). Assemblies with either of the first two parts different are normally viewed as incompatible. If the first two parts are the same, but the third is different, the assemblies are deemed as 'maybe compatible'. If only the fourth part is different, the assemblies are deemed compatible. However, this is just the default guideline - it is the version policy that decides to what extent these rules are enforced. The version policy can be specified via the application configuration file.
Remember: versioning is only applied to shared assemblies, not private assemblies.
4. Application Domains
4.1 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.
4.2 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;
}
}
4.3 Can I write my own .NET host?
Yes. For an example of how to do this, take a look at the source for the dm.net moniker developed by Jason Whittington and Don Box (http://staff.develop.com/jasonw/clr/readme.htm ). There is also a code sample in the .NET SDK called CorHost.
5. Garbage Collection
5.1 What is garbage collection?
Garbage collection is a system whereby a run-time component takes responsibility for managing the lifetime of objects and the heap memory that they occupy. This concept is not new to .NET - Java and many other languages/runtimes have used garbage collection for some time.
5.2 Is it true that objects don't always get destroyed immediately when the last reference goes away?
Yes. The garbage collector offers no guarantees about the time when an object will be destroyed and its memory reclaimed.
There is an interesting thread in the archives, started by Chris Sells, about the implications of non-deterministic destruction of objects in C#: http://discuss.develop.com/archives/wa.exe?A2=ind0007&L=DOTNET&P=R24819
In October 2000, Microsoft's Brian Harry posted a lengthy analysis of the problem: http://discuss.develop.com/archives/wa.exe?A2=ind0010A&L=DOTNET&P=R28572
Chris Sells' response to Brian's posting is here: http://discuss.develop.com/archives/wa.exe?A2=ind0010C&L=DOTNET&P=R983
5.3 Why doesn't the .NET runtime offer deterministic destruction?
Because of the garbage collection algorithm. The .NET garbage collector works by periodically running through a list of all the objects that are currently being referenced by an application. All the objects that it doesn't find during this search are ready to be destroyed and the memory reclaimed. The implication of this algorithm is that the runtime doesn't get notified immediately when the final reference on an object goes away - it only finds out during the next sweep of the heap.
Futhermore, this type of algorithm works best by performing the garbage collection sweep as rarely as possible. Normally heap exhaustion is the trigger for a collection sweep.
5.4 Is the lack of deterministic destruction in .NET a problem?
It's certainly an issue that affects component design. If you have objects that maintain expensive or scarce resources (e.g. database locks), you need to provide some way for the client to tell the object to release the resource when it is done. Microsoft recommend that you provide a method called Dispose() for this purpose. However, this causes problems for distributed objects - in a distributed system who calls the Dispose() method? Some form of reference-counting or ownership-management mechanism is needed to handle distributed objects - unfortunately the runtime offers no help with this.
5.5 Does non-deterministic destruction affect the usage of COM objects from managed code?
Yes. When using a COM object from managed code, you are effectively relying on the garbage collector to call the final release on your object. If your COM object holds onto an expensive resource which is only cleaned-up after the final release, you may need to provide a new interface on your object which supports an explicit Dispose() method.
5.6 I've heard that Finalize methods should be avoided. Should I implement Finalize on my class?
An object with a Finalize method is more work for the garbage collector than an object without one. Also there are no guarantees about the order in which objects are Finalized, so there are issues surrounding access to other objects from the Finalize method. Finally, there is no guarantee that a Finalize method will get called on an object, so it should never be relied upon to do clean-up of an object's resources.
Microsoft recommend the following pattern:
public class CTest : IDisposable
{
public void Dispose()
{
... // Cleanup activities
GC.SuppressFinalize(this);
}

~CTest() // C# syntax hiding the Finalize() method
{
Dispose();
}
}
In the normal case the client calls Dispose(), the object's resources are freed, and the garbage collector is relieved of its Finalizing duties by the call to SuppressFinalize(). In the worst case, i.e. the client forgets to call Dispose(), there is a reasonable chance that the object's resources will eventually get freed by the garbage collector calling Finalize(). Given the limitations of the garbage collection algorithm this seems like a pretty reasonable approach.
5.7 Do I have any control over the garbage collection algorithm?
A little. For example, the System.GC class exposes a Collect method - this forces the garbage collector to collect all unreferenced objects immediately.
5.8 How can I find out what the garbage collector is doing?
Lots of interesting statistics are exported from the .NET runtime via the '.NET CLR xxx' performance counters. Use Performance Monitor to view them.
6. Serialization
6.1 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).
6.2 Does the .NET Framework have in-built support for serialization?
There are two separate mechanisms provided by the .NET class library - XmlSerializer and SoapFormatter/BinaryFormatter. Microsoft uses XmlSerializer for Web Services, and uses SoapFormatter/BinaryFormatter for remoting. Both are available for use in your own code.
6.3 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.
6.4 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.
6.5 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.
6.6 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.
6.7 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.
7. Attributes
7.1 What are attributes?
There are at least two types of .NET attribute. The first type I will refer to as a metadata attribute - 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.
7.2 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 );
}
}
7.3 Can I create my own context attributes?
Yes. Take a look at Don Box's sample (called CallThreshold) at http://www.develop.com/dbox/dotnet/threshold/, and also Peter Drayton's Tracehook.NET at http://www.razorsoft.net/
8. Code Access Security
8.1 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.
8.2 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 permissions 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.)
8.3 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 - 0024000004800000940000000602000000240000525341310004000003
000000CFCB3291AA715FE99D40D49040336F9056D7886FED46775BC7BB5430BA4444FEF8348EBD06
F962F39776AE4DC3B7B04A7FE6F49F25F740423EBF2C0B89698D8D08AC48D69CED0FC8F83B465E08
07AC11EC1DCC7D054E807A43336DDE408A5393A48556123272CEEEE72F1660B71927D38561AABF5C
AC1DF1734633C602F8F2D5: Everything
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.
8.4 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.
8.5 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
9. Intermediate Language (IL)
9.1 Can I look at the IL for an assembly?
Yes. MS supply a tool called Ildasm which can be used to view the metadata and IL for an assembly.
9.2 Can source code be reverse-engineered from IL?
Yes, it is often relatively straightforward to regenerate high-level source (e.g. C#) from IL.
9.3 How can I stop my code being reverse-engineered from IL?
There is currently no simple way to stop code being reverse-engineered from IL. In future it is likely that IL obfuscation tools will become available, either from MS or from third parties. These tools work by 'optimising' the IL in such a way that reverse-engineering becomes much more difficult.
Of course if you are writing web services then reverse-engineering is not a problem as clients do not have access to your IL.
9.4 Can I write IL programs directly?
Yes. Peter Drayton posted this simple example to the DOTNET mailing list:
.assembly MyAssembly {}
.class MyApp {
.method static void Main() {
.entrypoint
ldstr "Hello, IL!"
call void System.Console::WriteLine(class System.Object)
ret
}
}
Just put this into a file called hello.il, and then run ilasm hello.il. An exe assembly will be generated.
9.5 Can I do things in IL that I can't do in C#?
Yes. A couple of simple examples are that you can throw exceptions that are not derived from System.Exception, and you can have non-zero-based arrays.
10. Implications for COM
10.1 Is COM dead?
This subject causes a lot of controversy, as you'll see if you read the mailing list archives. Take a look at the following two threads:
http://discuss.develop.com/archives/wa.exe?A2=ind0007&L=DOTNET&D=0&P=68241
http://discuss.develop.com/archives/wa.exe?A2=ind0007&L=DOTNET&P=R60761
FWIW my view is as follows: COM is many things, and it's different things to different people. But to me, COM is fundamentally about how little blobs of code find other little blobs of code, and how they communicate with each other when they find each other. COM specifies precisely how this location and communication takes place. In a 'pure' .NET world, consisting entirely of .NET objects, little blobs of code still find each other and talk to each other, but they don't use COM to do so. They use a model which is similar to COM in some ways - for example, type information is stored in a tabular form packaged with the component, which is quite similar to packaging a type library with a COM component. But it's not COM.
So, does this matter? Well, I don't really care about most of the COM stuff going away - I don't care that finding components doesn't involve a trip to the registry, or that I don't use IDL to define my interfaces. But there is one thing that I wouldn't like to go away - I wouldn't like to lose the idea of interface-based development. COM's greatest strength, in my opinion, is its insistence on a cast-iron separation between interface and implementation. Unfortunately, the .NET framework seems to make no such insistence - it lets you do interface-based development, but it doesn't insist. Some people would argue that having a choice can never be a bad thing, and maybe they're right, but I can't help feeling that maybe it's a backward step.
10.2 Is DCOM dead?
Pretty much, for .NET developers. The .NET Framework has a new remoting model which is not based on DCOM. Of course DCOM will still be used in interop scenarios.
10.3 Is MTS/COM+ dead?
No. The approach for the first .NET release is to provide access to the existing COM+ services (through an interop layer) rather than replace the services with native .NET ones. Various tools and attributes are provided to try to make this as painless as possible. The PDC release of the .NET SDK includes interop support for core services (JIT activation, transactions) but not some of the higher level services (e.g. COM+ Events, Queued components).
Over time it is expected that interop will become more seamless - this may mean that some services become a core part of the CLR, and/or it may mean that some services will be rewritten as managed code which runs on top of the CLR.
For more on this topic, search for postings by Joe Long in the archives - Joe is the MS group manager for COM+. Start with this message:
http://discuss.develop.com/archives/wa.exe?A2=ind0007&L=DOTNET&P=R68370
10.4 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-compatible interfaces. 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.
10.6 Is ATL redundant in the .NET world?
Yes, if you are writing applications that live inside the .NET framework. Of course many developers may wish to continue using ATL to write C++ COM components that live outside the framework, but if you are inside you will almost certainly want to use C#. Raw C++ (and therefore ATL which is based on it) doesn't have much of a place in the .NET world - it's just too near the metal and provides too much flexibility for the runtime to be able to manage it.
11. Miscellaneous
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.
11.2 How can I get at the Win32 API from a .NET program?
Use P/Invoke. This uses similar technology to COM Interop, but is used to access static DLL entry points instead of COM objects. Here is an example of C# calling the Win32 MessageBox function:
using System;
using System.Runtime.InteropServices;

class MainApp
{
[DllImport("user32.dll", EntryPoint="MessageBox", SetLastError=true, CharSet=CharSet.Auto)]
public static extern int MessageBox(int hWnd, String strMessage, String strCaption, uint uiType);

public static void Main()
{
MessageBox( 0, "Hello, this is PInvoke in operation!", ".NET", 0 );
}
}
12. Class Library
12.1 File I/O
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.2 Text Processing
12.2.1 Are regular expressions supported?
Yes. Use the System.Text.RegularExpressions.Regex class. For example, the following code updates the title in an HTML file:
FileStream fs = new FileStream( "test.htm", FileMode.Open, FileAccess.Read );
StreamReader sr = new StreamReader( fs );

Regex r = new Regex( "(.*)" );
string s;
while( (s = sr.ReadLine()) != null )
{
if( r.IsMatch( s ) )
s = r.Replace( s, "New and improved ${1}" );
Console.WriteLine( s );
}
12.3 Internet
12.3.1 How do I download a web page?
First use the System.Net.WebRequestFactory class to acquire a WebRequest object:
WebRequest request = WebRequest.Create( "http://localhost" );
Then ask for the response from the request:
WebResponse response = request.GetResponse();
The GetResponse method blocks until the download is complete. Then you can access the response stream like this:
Stream s = response.GetResponseStream();

// Output the downloaded stream to the console
StreamReader sr = new StreamReader( s );
string line;
while( (line = sr.ReadLine()) != null )
Console.WriteLine( line );
Note that WebRequest and WebReponse objects can be downcast to HttpWebRequest and HttpWebReponse objects respectively, to access http-specific functionality.
12.3.2 How do I use a proxy?
Two approaches - to affect all web requests do this:
System.Net.GlobalProxySelection.Select = new WebProxy( "proxyname", 80 );
Alternatively, to set the proxy for a specific web request, do this:
HttpWebRequest request = (HttpWebRequest)WebRequest.Create( "http://localhost" );
request.Proxy = new WebProxy( "proxyname", 80 );
12.4 XML
12.4.1 Is DOM supported?
Yes. Take this example XML document:

Fred
Bill

This document can be parsed as follows:
XmlDocument doc = new XmlDocument();
doc.Load( "test.xml" );

XmlNode root = doc.DocumentElement;

foreach( XmlNode personElement in root.ChildNodes )
Console.WriteLine( personElement.FirstChild.Value.ToString() );
The output is:
Fred
Bill
12.4.2 Is SAX supported?
No. Instead, a new XmlReader/XmlWriter API is offered. Like SAX it is stream-based but it uses a 'pull' model rather than SAX's 'push' model. Here's an example:
XmlTextReader reader = new XmlTextReader( "test.xml" );

while( reader.Read() )
{
if( reader.NodeType == XmlNodeType.Element && reader.Name == "PERSON" )
{
reader.Read(); // Skip to the child text
Console.WriteLine( reader.Value );
}
}
12.4.3 Is XPath supported?
Yes, via the XPathXXX classes:
XPathDocument xpdoc = new XPathDocument("test.xml");
XPathNavigator nav = xpdoc.CreateNavigator();
XPathExpression expr = nav.Compile("descendant::PEOPLE/PERSON");

XPathNodeIterator iterator = nav.Select(expr);
while (iterator.MoveNext())
Console.WriteLine(iterator.Current);
12.5 Threading
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();
}
12.6 Tracing
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.
Interview Questions
.NET Windows Forms

1. Write a simple Windows Forms MessageBox statement.
2. System.Windows.Forms.MessageBox.Show

3. ("Hello, Windows Forms");
4. Can you write a class without specifying namespace? Which namespace does it belong to by default??
Yes, you can, then the class belongs to global namespace which has no name. For commercial products, naturally, you wouldn’t want global namespace.
5. You are designing a GUI application with a window and several widgets on it. The user then resizes the app window and sees a lot of grey space, while the widgets stay in place. What’s the problem? One should use anchoring for correct resizing. Otherwise the default property of a widget on a form is top-left, so it stays at the same location when resized.
6. How can you save the desired properties of Windows Forms application? .config files in .NET are supported through the API to allow storing and retrieving information. They are nothing more than simple XML files, sort of like what .ini files were before for Win32 apps.
7. So how do you retrieve the customized properties of a .NET application from XML .config file? Initialize an instance of AppSettingsReader class. Call the GetValue method of AppSettingsReader class, passing in the name of the property and the type expected. Assign the result to the appropriate variable.
8. Can you automate this process? In Visual Studio yes, use Dynamic Properties for automatic .config creation, storage and retrieval.
9. My progress bar freezes up and dialog window shows blank, when an intensive background process takes over. Yes, you should’ve multi-threaded your GUI, with taskbar and main form being one thread, and the background process being the other.
10. What’s the safest way to deploy a Windows Forms app? Web deployment: the user always downloads the latest version of the code; the program runs within security sandbox, properly written app will not require additional security privileges.
11. Why is it not a good idea to insert code into InitializeComponent method when working with Visual Studio? The designer will likely throw it away; most of the code inside InitializeComponent is auto-generated.
12. What’s the difference between WindowsDefaultLocation and WindowsDefaultBounds? WindowsDefaultLocation tells the form to start up at a location selected by OS, but with internally specified size. WindowsDefaultBounds delegates both size and starting position choices to the OS.
13. What’s the difference between Move and LocationChanged? Resize and SizeChanged? Both methods do the same, Move and Resize are the names adopted from VB to ease migration to C#.
14. How would you create a non-rectangular window, let’s say an ellipse? Create a rectangular form, set the TransparencyKey property to the same value as BackColor, which will effectively make the background of the form transparent. Then set the FormBorderStyle to FormBorderStyle.None, which will remove the contour and contents of the form.
15. How do you create a separator in the Menu Designer? A hyphen ‘-’ would do it. Also, an ampersand ‘&\’ would underline the next letter.
16. How’s anchoring different from docking? Anchoring treats the component as having the absolute size and adjusts its location relative to the parent form. Docking treats the component location as absolute and disregards the component size. So if a status bar must always be at the bottom no matter what, use docking. If a button should be on the top right, but change its position with the form being resized, use anchoring.

Here is a list of some common questions I ask potential candidates when screening for interviews (and no, I don't expect answers as in depth as I've listed here):

What are some of the objects exposed by the .NET framework that lend themselves to asynchronous method invocation, and what are a few ways architecturally to use these objects? What is the name of the interface used primarily as a way to check on an asynchronous method call?

The simplest way to execute a method asynchronously is to start it with BeginInvoke, do some work on the main thread, and then call EndInvoke. EndInvoke does not return until the asynchronous call completes. This is a good technique to use with file or network operations, but because it blocks on EndInvoke, you should not use it from threads that service the user interface.

Waiting on a WaitHandle is a common thread synchronization technique. You can obtain a WaitHandle using the AsyncWaitHandle property of the IAsyncResult returned by BeginInvoke. The WaitHandle is signaled when the asynchronous call completes, and you can wait for it by calling its WaitOne. If you use a WaitHandle, you can perform additional processing after the asynchronous call completes, but before you retrieve the results by calling EndInvoke. Bear in mind that the call to EndInvoke will block the calling thread unless the asnychronous method call has completed.

You can use the boolean IsCompleted property of the IAsyncResult returned by BeginInvoke to discover when the asynchronous call completes. You might do this when making the asynchronous call from a thread that services the user interface. Polling for completion allows the user interface thread to continue processing user input, thus this is the preferred way for a GUI to call an method asynchronously.
If the thread that initiates the asynchronous call does not need to process the results, you can execute a callback method when the call completes. The callback method is executed on a ThreadPool thread. To use a callback method, you must pass BeginInvoke an AsyncCallback delegate representing the method. You can also pass an object containing information to be used by the callback method. For example, you might pass the delegate that was used to initiate the call, so the callback method can call EndInvoke. If a callback has been specified on the BeginInvoke, it will be called when the target method returns. In the callback, the EndInvoke method is used to obtain the return value and the in/out parameters. If the callback was not specified on the BeginInvoke, then EndInvoke can be used on the original thread that submitted a request.
Name a design pattern you've used on a regular basis, and explain why you've used it and how it lent itself to accomplishing the programming task you were trying to accomplish. (there are numerous answers, if they name one and can talk about it for a minute or 2, that should suffice). Common ones will be Singleton, Factory, Observer, Asynchronous, Disposable, and Controller.

What are the similarities/differences between interfaces and abstract classes? When and why would use either one, and name a specific instance when you would use one and not the other?

Interfaces support multiple inheritances, whereas you can only inherit from one abstract class.
Abstract classes support partial to full implementation, whereas interfaces only define a contract with no implementation.
If you anticipate creating multiple versions of your component, create an abstract class. Abstract classes provide a simple and easy way to version your components. By updating the base class, all inheriting classes are automatically updated with the change. Interfaces, on the other hand, cannot be changed once created. If a new version of an interface is required, you must create a whole new interface.
If the functionality you are creating will be useful across a wide range of disparate objects, use an interface. Abstract classes should be used primarily for objects that are closely related, whereas interfaces are best suited for providing common functionality to unrelated classes.
If you are designing small, concise bits of functionality, use interfaces. If you are designing large functional units, use an abstract class.
If you want to provide common, implemented functionality among all implementations of your component, use an abstract class. Abstract classes allow you to partially implement your class, whereas interfaces contain no implementation for any members.

What are 2 ways to facilitate the passing of objects across application domains via reflection? IE, there are 2 seperate programming constructs that can be used to enable objects to passed across application domains, a default class by itself cannot be passed across app domains. HINT: one is an attribute, the other is a base class.

You can mark the object with the Serializable attribute. Bear in mind that this will in turn make each and every member of the class serializable as well. If you need to implement partial serialization, inherit from ISerializable and just serialize the members you need to...or use the NonSerializable attribute on those members. An object that is serialized across application domains is done so as a copy of the object (similar to ByValue), so this can be an expensive proposition with larger objects.
If you inherit from MarshalByRefObject, you will get the same desired end result, however instead of a copy of the object being transported to the remote application domain, a proxy will be created that facilitates communication between the objects. When an object derives from MarshalByRefObject, an object reference will be passed from one application domain to another, rather than the object itself.

What are the 2 transport protocols that can be used in .NET remoting? What are the 2 transport payload types? What network scenarios are each combination best suited for?

SOAP payload via HTTP protocol. The HTTP channel uses the SOAP formatter by default and hence can be used in scenarios where clients will access the objects over the Internet. Since this approach uses HTTP, accessing .NET objects remotely through a firewall is enabled by this configuration. Objects exposed in this way can easily be configured as Web Services Objects simply by hosting these objects in IIS. Clients can then read the WSDL files of these objects to communicate with the Remote object using SOAP. There is an associated performance hit as SOAP payloads are quite a bit larger than their binary counterparts.
Binary payload via TCP protocol. The TCP Channel uses the binary formatter by default. This formatter serializes the data in binary form and uses raw sockets to transmit data across the network. This method is ideal if your object is deployed in a closed environment within the confines of a firewall. This approach is more optimized since it uses sockets to communicate binary data between objects. Using the TCP channel to expose your object gives you the advantage of low overhead in closed environments. This approach cannot be used over the Internet because of firewall and configuration issues.

Last one...what are some of the key fundamental differences between C# and VB.NET?

VB.NET doesn't support operator overloading.
C# is case sensitive.
C# instrinsically supports code comment sheets via XML tags.
You can write unsafe (C/C++ style) code in C# code blocks.
More explicit casting is necessary when writing in C#.
VB.NET supports optional parameters.
In VB.NET, parameters are passed by reference by default, whereas in C# parameters are passed by value.
Method level vars must be initialized in C# (even if it's with a null type), whereas in VB.NET there is some leniency with this.
VB.NET supports the With [typeName] construct, allowing for multiple actions to be performed on a type in one block.
VB.NET does not support unsigned numeric primitives.
VB.NET supports re-allocating an array (ReDim).

1. What do you know about .NET assemblies? Assemblies are the smallest units of versioning and deployment in the .NET application. Assemblies are also the building blocks for programs such as Web services, Windows services, serviced components, and .NET remoting applications.
2. What’s the difference between private and shared assembly? Private assembly is used inside an application only and does not have to be identified by a strong name. Shared assembly can be used by multiple applications and has to have a strong name.
3. What’s a strong name? A strong name includes the name of the assembly, version number, culture identity, and a public key token.
4. How can you tell the application to look for assemblies at the locations other than its own install? Use the
directive in the XML .config file for a given application.

should do the trick. Or you can add additional search paths in the Properties box of the deployed application.
5. How can you debug failed assembly binds? Use the Assembly Binding Log Viewer (fuslogvw.exe) to find out the paths searched.
6. Where are shared assemblies stored? Global assembly cache.
7. How can you create a strong name for a .NET assembly? With the help of Strong Name tool (sn.exe).
8. Where’s global assembly cache located on the system? Usually C:\winnt\assembly or C:\windows\assembly.
9. Can you have two files with the same file name in GAC? Yes, remember that GAC is a very special folder, and while normally you would not be able to place two files with the same name into a Windows folder, GAC differentiates by version number as well, so it’s possible for MyApp.dll and MyApp.dll to co-exist in GAC if the first one is version 1.0.0.0 and the second one is 1.1.0.0.
10. So let’s say I have an application that uses MyApp.dll assembly, version 1.0.0.0. There is a security bug in that assembly, and I publish the patch, issuing it under name MyApp.dll 1.1.0.0. How do I tell the client applications that are already installed to start using this new MyApp.dll? Use publisher policy. To configure a publisher policy, use the publisher policy configuration file, which uses a format similar app .config file. But unlike the app .config file, a publisher policy file needs to be compiled into an assembly and placed in the GAC.
11. What is delay signing? Delay signing allows you to place a shared assembly in the GAC by signing the assembly with just the public key. This allows the assembly to be signed with the private key at a later stage, when the development process is complete and the component or assembly is ready to be deployed. This process enables developers to work with shared assemblies as if they were strongly named, and it secures the private key of the signature from being accessed at different stages of development.

VB 6.0 Interview Questions

2011-01-15T22:46:00.001+05:30

VB INTERVIEW QUESTIONS

What are the three main differences between flexgrid control and dbgrid (Data bound Grid) control

Microsoft FlexGrid (MSFlexGrid) Data-bound grid (DBGrid)
1. MSFlexGrid control displays and operates on tabular data. 1. Dbgrid is a spreadsheet-like bound control that displays a series of rows and columns representing records and fields from a Recordset object.
2. It allows complete flexibility to sort, merge, and format tables containing strings and pictures. 2. The data grids are bound controls; that is, they require a data source that actually connects to a database and retrieves their data.
3. When bound to a Data control, MSFlexGrid displays read-only data. 3. When you change a record in DBGrid & move forward to a new record, the recordset is updated, if it is not locked & AllowUpdate property = True.

The Microsoft FlexGrid (MSFlexGrid) control displays and operates on tabular data. It allows complete flexibility to sort, merge, and format tables containing strings and pictures. When bound to a Data control, MSFlexGrid displays read-only data. Adaptation to existing Visual Basic code for the data-bound grid (DBGrid).

Dbgrid is a spreadsheet-like bound control that displays a series of rows and columns representing records and fields from a Recordset object.

The data grids are bound controls; that is, they require a data source that actually connects to a database and retrieves their data. And it seems that the root of the problem with DBGrid is that there's no data source that can be readily included along with the DBGrid control.
In Visual Basic, the solution is simply to include the Data Control on the same form as DBGrid. But the Data Control is an intrinsic control; it's unavailable to anything outside of the Visual Basic environment itself. and VB 6.0 has a new set of data controls (DataGrid, DataList, DataCombo, MSHFlexGrid, and MSFlexGrid) that once again are bound controls. Unlike DBGrid, though, they support OLE DB, and therefore rely on the ADO Data Source (and in particular the ActiveX Data Objects Data Control, or ADO DC) for data access. Unlike the Data Control, the ADO DC is a custom control (that is, an .OCX) that can be added to any project. In short, if you add ADO DC to your project along with the DataGrid control.

What are the ActiveX and Types of ActiveX Components in VB?
Standard EXE
ActiveX EXE
ActiveX DLL
ActiveX document
ActiveX Control

What is difference between inprocess and out of process?
An in-process component is implemented as a DLL, and runs in the same process space as its client app, enabling the most efficient communication between client and component. Each client app that uses the component starts a new instance of it.

An out-of-process component is implemented as an EXE, and unlike a dll, runs in its own process space. As a result, exe's are slower then dll's because communications between client and component must be marshalled across process boundaries. A single instance of an out of process component can service many clients.

What are the Advantage of ActiveX Dll over Active Exe?

ACTIVEX DLL:
=============
An in-process component, or ActiveX DLL, runs in another application’s process. In-process components are used by applications or other in-process components. this allows you to wrap up common functionality (like an ActiveX Exe).

ACTIVEX EXE:
=============
An out-of-process component, or ActiveX EXE, runs in its own address space. The client is usually an application running in another process. The code running in an ActiveX Exe is running in a separate process space. You would usually use this in N-Tier programming.

An ActiveX EXE runs out of process while an ActiveX DLL runs in the same process space as VB app. Also, and ActiveX EXE can be run independent of your application if desired.

Explain single thread and multithread thread apartments?
All components created with Visual Basic use the apartment model, whether they’re single-threaded or multithreaded. A single-threaded component has only one apartment, which contains all the objects the component provides.

This means that a single-threaded DLL created with Visual Basic is safe to use with a multithreaded client. However, there’s a performance trade-off for this safety. Calls from all client threads except one are marshaled, just as if they were out-of-process calls.

What is a Component?
If you compile an ActiveX dll, it becomes a component.
If you compile an ActiveX Control, it becomes both a component and a control. Component is a general term used to describe code that's grouped by functionality. More specifically, a component in COM terms is a compiled collection of properties/methods and events.

Typically a component is loaded into your project via the References whereas an ActiveX Control is loaded into your project via "components".

What is meant by "Early Binding" and "Late Binding"? Which is better?
Early binding and late binding refer to the method used to bind an interface's properties and methods to an object reference (variable). Early binding uses type library information at design time to reference procedures, while late binding handles this at run time. Late binding handles this by interrogating the reference before each call to insure that it supports a particular method. Since every call to a late bound object actually requires two calls ("Do you do this?" followed by "Okay, do it then"), late binding is much less efficient than early binding. Except where early binding is not supported (ASP, scripting, etc.), late binding should only be used in very special cases.

It is a common misconception that any code using the CreateObject function instead of Set = New is using late binding. This is not the case. The type declaration of the object variable determines whether
it is late or early bound, as in the following:

Dim A As Foo
Dim B As Foo
Dim C As Object
Dim D As Object

Set A = New Foo 'Early Bound
Set B = CreateObject("FooLib.Foo") 'Early Bound
Set C = CreateObject("FooLib.Foo") 'Late Bound
Set D = New Foo 'Late Bound

What are the Advantages of disconnected recordsets?
A disconnected Recordset, as its name implies, is a Recordset that lacks a connection.

seen that a Recordset that does not have a database connection can be very useful as a tool in your programming. It can save you time and effort and make your code more scalable.

In order to create a disconnected Recordset two Recordset properties must be set appropriately.
It is a requirement that the CursorLocation property is set to adUseClient and the LockType property is set to adLockBatchOptimistic. Note that the CursorType will default to adUseStatic if we don't explicitly state that it should be set to adUseClient.) i.e.

rst.LockType = adLockBatchOptimistic
rst.CursorLocation = adUseClient

However, we've recently discovered that these steps aren't necessary. VB automatically assigns batch optimistic locking to newly created, connectionless recordsets. And, of course, without a connection, a recordset can't have any other cursor but a client-side one. To create one of these structures, then, the only thing you need do is create the object variable instance. After that, you can simply begin adding fields to the construct.

To add fields, you use the Fields collection's Append method. This method requires two parameters, the field name and the field data type. So, to create a connectionless recordset with two fields, you’d use code similar to:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Fields.Append "CustID", adVarChar
rst.Fields.Append "CustName", adVarChar

Additional, optional Append method parameters include DefinedSize and Attrib. The DefinedSize argument takes the size of the field. Fill the Attrib parameter with constants that define additional field characteristics, such as whether it will allow null values or is updatable.
Since, in our technique, we want the fields to mirror the structure of the original recordset, we'll simply use existing values for these parameters.

Disconnected Recordsets, first available with ADO 2.0, are the most commonly used mechanism to retrieve a Recordset and open a connection for only the necessary amount of time, thus increasing scalability. They are call disconnected because the connection to the database is closed.
The collections, properties, and methods of a disconnected Recordset are still available even though the connection is closed. This frees up server resources, given that the number of open connections is limited and database locking is a non-issue.

What are Benefits of wrapping database calls into MTS transactions?
If database calls are made within the context of a transaction, aborting the transaction will undo and changes that occur within that transaction. This removes the possibility of stranded or partial data. Transaction that uses the Microsoft® Transaction Server (MTS) environment. MSMQ implicitly uses the current MTS transaction if one is available.

BENIFTIS OF USING MTS:
Database Pooling, Transactional operations, Deployment, Security, and Remote Execution This allows MTS to reuse database connections. Database connections are put to sleep. As opposed to being created and destroyed and are activated upon request.

Can database schema be changed with DAO/ADO/RDO?
A database schema describes the current structure of the tables and database views in the database. In general, ClassWizard assumes that the schema for the table(s) accessed by a recordset will not change, but the database classes can deal with some schema changes, such as adding, reordering, or deleting unbound columns. If a table changes, you can “refresh” your recordset for the table using ClassWizard’s Update Columns button and then recompile your application.

Can you create a table-type of recordset in Jet connected ODBC database engine?
You can create a table-type CdbRecordset object from a base table of a Microsoft Jet database, but not from an ODBC or linked table. You can use the table-type CdbRecordset object with ISAM databases (like Microsoft Visual FoxPro, dBASE, or Paradox) when you open them directly.

How to register a component?
Compiling the component, running REGSVR32 MyDLL.dll

Controls which do not have events?
Shape and line controls are useful for drawing graphical elements on
the surface of a form. These controls don't support any events; they
are strictly for decorative purposes.
EXTRA INFO:
The image, shape and line controls are considered to be lightweight controls; that is, they support only a subset of the properties, methods, and events found in the picture box. Because of this, they typically require less system resources and load faster than the picture box control.

What are the Control Categories
a)Intrinsic controls:
such as the command button and frame controls. These controls are contained inside the Visual Basic .exe file. Intrinsic controls are always included in the toolbox

b)ActiveX controls:
which exist as separate files with a .ocx file name extension. These include controls that are available in all editions of Visual Basic (DataCombo, DataList controls, and so on) and those that are available only in the Professional and Enterprise editions (such as Listview, Toolbar, Animation, and Tabbed Dialog). Many third-party ActiveX controls are also available.

c)Insertable Objects:
such as a Microsoft Excel Worksheet object containing a list of all your company's employees, or a Microsoft Project Calendar object containing the scheduling information for a project. Since these can
be added to the toolbox, they can be considered controls. Some of these objects also support Automation (formerly called OLE Automation),which allows you to program another application's objects from within a Visual Basic application.

DIFF between Image and Picture box controls?
The sizing behavior of the image control differs from that of the picture box. Image box has a Stretch property while the picture box has an AutoSize property. Setting the AutoSize property to True causes a picture box to resize to the dimensions of the picture; setting it to False causes the picture to be cropped (only a portion of the picture is visible). When set to False (the default), the Stretch property of the image control causes it to resize to the dimensions of the picture. Setting the Stretch property to True causes the picture to resize to the size of the image control, this may cause the picture to appear distorted.

Default property of datacontrol?
connect property......

Define the scope of Public, Private, Friend procedures?
The set of public variables, methods, properties, and events described in a class module define the interface for an object. The interface consists of the object members that are available to a programmer who's using the object from code.

You can create private variables, methods, properties, and events that are used by other procedures within the class module but are not part of the object's public interface. Additionally, constants user-defined types, and Declare statements within a class module must always be private.

The Friend keyword makes a procedure private to the project: The procedure is available to any code running within the project, but it is not available to a referencing project.

Describe Database Connection pooling relative to MTS ?
This allows MTS to reuse database connections. Database connections are put to ?sleep? as opposed to being created and destroyed and are activated upon request.

Object pooling is an important design concept required for high-performance applications. A performance optimization based on using collections of preallocated resources, such as objects or database connections. Pooling results in more efficient resource allocation.

Difference between a function and a subroutine ?
A function accepts any number of parameters (possibly zero), does something with them, and returns a value.
A subroutine performs an action, but doesn't return a value.

There are two differences between a function and a subroutine:
A)How they are invoked. B)How they are accessed.

A function call has the following syntax ::
function(arg1, arg2, ...)
where: function Is the name of the function. arg1, arg2, ... Are the arguments.

A subroutine call has the following syntax
::subroutine (arg1, arg2, ... {outputfield|'format'})
where: subroutine -->Is the name of the subroutine. arg1, arg2, ... Are the arguments. {outputfield|'format'} Is the name of the output field or its format.

In addition, on some platforms, the functions are available immediately; whereas, the subroutines are available in a special subroutine library that you must access.

Difference between Linked Object and Embedded Object?
Embedding objects -
When you embed an object, a copy of the object is inserted into the destination document. There's no link to the original file. When you change information in the source document, no changes will be reflected in the destination document. The actual data for the object is stored within the destination file. To make changes to the embedded object, double click it and it will launch the original application the source file was in.
Linking objects -
Information is updated when you modify the original source file when you use a linked object. This dynamic updating is very handy for things such as the aforementioned monthly report. You can open up the Excel spreadsheet that is referenced within your Word document. Make changes to the spreadsheet, close Excel, and when you open your Word document... viola! The changes are already there. If that object is linked to ten other Word files, the changes are already in those ten files, too! actually linking or embedding an object is fast and easy.

Difference between listbox and combo box?
A LISTBOX CONTROL displays a list of items from which the user can select one or more. If the number of items exceeds the number that can be displayed, a scroll bar is automatically added to the ListBox control. A COMBOX CONTROL combines the features of a text box and a list box. This control allows the user to select an item either by typing text into the combo box, or by selecting it from the list.
DIFF:: Generally, a combo box is appropriate when there is a list of suggested choices, and a list box is appropriate when you want to limit input to what is on the list. A combo box contains an edit field, so choices not on the list can be typed in this field.

Difference between Dynaset and Snapshot?
All Recordset objects are constructed using records (rows) and fields (columns). There are five types of Recordset objects:

Table-type Recordset ::
representation in code of a base table that you can use to add, change, or delete records from a single database table (Microsoft Jet workspaces only).

Dynaset-type Recordset ::
the result of a query that can have updatable records. A dynaset-type Recordset object is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset-type Recordset object can contain fields from one or more tables in a database. This type corresponds to an ODBC keyset cursor.

Snapshot-type Recordset ::
a static copy of a set of records that you can use to find data or generate reports. A snapshot-type Recordset object can contain fields from one or more tables in a database but can't be updated. This type corresponds to an ODBC static cursor.

Forward-only-type Recordset::
identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a result set. This type corresponds to an ODBC forward-only cursor.

Dynamic-type Recordset ::
a query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records other users add, delete, or edit in the base tables also appear in your Recordset. This type corresponds to an ODBC dynamic cursor (ODBCDirect workspaces only).

Difference Listindex and Tab index?
LIST INDEX::
Returns or sets the index of the currently selected item in the control. Not available at design time.Default LIST INDEX IS -1 for ComboBox, DirListBox, and DriveListBox controls
TAB INDEX::
Returns or sets the tab order of most objects within their parent form. Visual Basic automatically renumbers the TabIndex of other controls to reflect insertions and deletions. You can make changes at design time using the Properties window or at run time in code. The TabIndex property isn't affected by the ZOrder method.

Difference modal and modeless window?
MODAL forms are forms which require user input before any other actions can be taken place. In other words, a modal form has exclusive focus in that application until it is dismissed. When showing a modal form, the
controls outside this modal form will not take user interaction until the form is closed. The internal MsgBox and InputBox forms are examples of modal forms. To show a form modally, use the syntax:
MyForm.SHOW.vbModal ' a predeclared constant for 1
MODELESS forms are those which are shown but do not require immediate user input. MDI child forms are always modeless. To show a form modeless, use the syntax:: MyForm.SHOW

Difference Object and Class?
Classes and objects are separate but related concepts. Every object belongs to a class and every class contains one or more related objects.
1)A Class is static. All of the attributes of a class are fixed before, during, and after the execution of a program. The attributes of a class don't change. The class to which an object belongs is also (usually) static. If a particular object belongs to a certain class at the time that it is created then it almost certainly will still belong to that class right up until the time that it is destroyed.
2)An Object on the other hand has a limited lifespan. Objects are created and eventually destroyed. Also during that lifetime, the attributes of the object may undergo significant change. So basically the difference between a class and an object is that a class is a general concept while objects are the specific and real instances that embody that concept. When creating an object oriented program we define the classes and the relationships between the classes . We then execute the program to create, update, and destroy the objects which are the specific realization of these classes.

Difference Query unload and unload in form?
Occurs before a form or application closes. When an MDIForm object closes, the QueryUnload event occurs first for the MDI form and then in all MDI child forms. If no form cancels the QueryUnload event, the Unload event occurs first in all other forms and then in an MDI form. When a child form or a Form object closes, the QueryUnload event in that form occurs before the form's Unload event.

Difference Declaration and Instantiation an object?
Dim obj as OBJ.CLASS with either
Set obj = New OBJ.CLASS or
Set obj = CreateObject(?OBJ.CLASS?) or
Set obj = GetObject( ,? OBJ.CLASS?)
or
Dim obj as New OBJ.CLASS

Set object = Nothing
ensure the object is release from the memory.

If this object is a form, you can add set myform = nothing and Form_Unload() event.Maintain a habit of remove the object by using set object = nothing which will benefit at last.
Visual Basic is supposed to automatically release objects when they go out of scope. To free up some memory usage, you can set the object to nothing.

Draw and explain Sequence Modal of DAO
Connection,Container,Database,DBEngine,Document,Error,Field,Group,Index
Parameter Property,QueryDef,Recordset,Relation,TableDef,User,Workspace

Version Year Significant Changes and New Features of Visual Basic
1 1991 initial release, with drag and drop GUI creation
2 1992 ODBC, object variables
3 1993 Access Engine, OLE 2.0, Crystal Reports, new tools and controls
4 1995 classes, OCXs
5 1997 compiler, ActiveX controls
6 1998 web support, windowless controls, designers, data sources
.NET 2001 XML, SOAP, inheritance, structured exception handling

How can objects on different threads communicate with one another?
Processes communicate with one another through messages, using Microsoft's Remote Procedure Call (RPC) technology to pass information to one another. There is no difference to the caller between a call coming from a process on a remote machine and a call coming from another process on the same machine.

Multithreaded applications must avoid two threading problems: deadlocks and races. A deadlock occurs when each thread is waiting for the other to do something

How can you force new objects to be created on new threads?
The CreateThread function creates a thread to execute within the virtual address space of the calling process.

To create a thread that runs in the virtual address space of another process Creating a new thread is as easy as declaring it and supplying it with a delegate to the method where the thread is to start. When you are ready to begin execution on the thread, call the Thread.Start Method. There are special considerations involved when working with multiple threads of execution.

To create a new thread of execution
====================================
Declare the thread.
******************
' Visual Basic
Dim myThread as System.Threading.Thread

// C#
System.Threading.Thread myThread;

Instantiate the thread with the appropriate delegate for the starting point of the thread. Use the AddressOf operator to create the delegate
in Visual Basic, or create a new ThreadStart object in C#.
*******************
' Visual Basic
myThread = New System.Threading.Thread(AddressOf
myStartingMethod)

// C#
myThread = new System.Threading.Thread(new
System.Threading.ThreadStart(myStartingMethod));

call the Thread.Start method to start the thread.
*******************
' Visual Basic
myThread.Start()

// C#
myThread.Start();

How does a DCOM component know where to instantiate itself?
To create a remote instance of a script component, call the CreateObject method, passing it the name of the remote computer as a parameter. If the remotable attribute of a script component's element has been set to "true," the script component can be instantiated remotely from another computer using Distributed COM (DCOM).
Both computers must have basic DCOM installed. Note The ability to use CreateObject for instantiating remote script components requires Visual Basic 6.0 or later or VBScript 5.0 or later. The following Visual Basic example shows how to do this on a computer named "myserver":

Set newS = CreateObject("Component.MyComponent", "myserver")
Note There can be a slight delay when you first instantiate a remote script component while DCOM establishes communication between the computers.

1. You can specify the machine on which you want to create the remote server object in DCOM config ('dcomcnfg').

2. You can specify the machine name when instantiating the remote server object.
In C you can do this with a call to CoGetClassObject or CoCreateInstanceEx (instead of CoCreateInstance, which does not allow you to specify the name of the machine).
In VB you can specify the name in one of the parameters in the call to CreateObject

What type of multi-threading does VB6 implement?
Apartment model threading

What is Database Connection pooling (relative to MTS)
This allows MTS to reuse database connections. Database connections are
put to "sleep" As opposed to being created and destroyed and are activated upon request.

What is the tool used to configure the port range and protocols for DCOM communications?
DCOMCONFIG.EXE

What is a Type Library and what is its purpose ?
The type library may represent another Visual Basic project, or any other executable component that exposes a type library.
Visual Basic creates type library information for the classes you create, provides type libraries for the objects it includes, and lets you access the type libraries provided by other applications.

What are binary and project compatibility?
Visual Basic’s Version Compatibility feature is a way of enhancing your components while maintaining backward compatibility with programs that were compiled using earlier versions. The Version Compatibility box, located on the Component tab of the Project Properties dialog box, contains three options:
No Compatibility:
Each time you compile the component, new type library information is generated, including new class IDs and new interface IDs. There is no relation between versions of a component, and programs compiled to use one version cannot use subsequent versions.
Project Compatibility:
Each time you compile the component the type library identifier is kept, so that your test projects can maintain their references to the component project. All class IDs from the previous version are maintained; interface IDs are changed only for classes that are no longer binary-compatible with their earlier counterparts. Note This is a change in Project Compatibility from Visual Basic 5.0, where all class IDs and interface IDs in the project changed if any one class was no longer binary-compatible.

Important For the purpose of releasing compatible versions of a component, Project Compatibility is the same as No Compatibility.
Binary Compatibility:
When you compile the project, if any binary-incompatible changes are detected you will be presented with a warning dialog. If you choose to accept the warning, the component will retain the type library identifier and the class IDs. Interface IDs are changed only for classes that are no longer binary-compatible. This is the same behavior as Project Compatibility.
If, however, you choose to ignore the warning, the component will also maintain the interface IDs. This option is only available when the compiler determines that the change was in the procedure ID or signature of a method.

Note:: When people talk about Version Compatibility, they’re usually referring to Binary Compatibility.

How to set a shortcut key for label?
object.KeyLabel(keycode) [= string]
You would probably create the menu item as follows:
.Add "keyFile", , , "E&xit", , vbAltMask + vbCtrlMask, vbKeyEnd
The default key label for vbKeyEnd is "End". Thus, the shortcut string will be created by default as "Ctrl+Alt+End".

Name the four different cursor and locking types in ADO and describe them briefly ?
CURSORS::
*********
The cursor types are listed from least to most resource intensive.
Forward Only - Fastest, can only move forward in recordset
Static - Can move to any record in the recordset. Data is static and never changes.
KeySet - Changes are detectable, records that are deleted by other users are unavailable, and records created by other users are not detected Dynamic - All changes are visible.
LOCKING TYPES::
****************
LockPessimistic - Locks the row once after any edits occur.
LockOptimistic - Locks the row only when Update is called.
LockBatchOptimistic - Allows Batch Updates.
LockReadOnly - Read only. Cannot alter the data.

Name the different compatibility types when creating a COM component.
No Compatibility - New GUID (Globally Unique Identifier) created, references from other components will not work Project Compatibility - Default for a new component
Binary Compatibility - GUID does not change references from other components will work

Why is it important to use source control software for source code?
Modification history. Code ownership: Multiple people cannot modify the same code at the same time.

List the ADO objects?
Connection - Connects to a data source; contains the Errors collection
Command - Executes commands to the data source. The only object that can accept parameters for a stored procedure Recordset - The set of data returned from the database.
Under the ADO Command Object, The Parameters collection. collection is responsible for input to stored procedures.

What two methods are called from the ObjectContext object to inform MTS that the transaction was successful or unsuccessful?
SetComplete and SetAbort.

What is the benefit of wrapping database calls into MTS transactions?
Aborting the transaction will undo and changes that occur within that transaction. This removes the possibility of stranded, or partial data

Describe and In Process vs. Out of Process component. Which is faster?
An in-process component is implemented as a DLL, and runs in the same process space as its client app, enabling the most efficient communication between client and component. Each client app that uses the component starts a new instance of it.
An out of process component is implemented as an EXE, and unlike a dll, runs in its own process space. As a result, exe's are slower then dll's because communications between client and component must be marshaled across process boundaries. A single instance of an out of process component can service many clients.

How would you declare and raise custom events in a class?
a) Public Event OnVarChange();
b) RaiseEvent OnVarChange[(arg1, arg2, ... , argn)]

What is the difference between a Property Let and Property Set procedure?
Let - for simple variable
Set - for object

What is the difference between ANSI and UNICODE strings when passed as arguments to a DLL?
ANSI - one byte for a char UNICODE - two bytes per char - works only on NT

What is the difference in passing values ByRef or ByVal to a procedure?
ByRef -pass the address (for string -address of address of first byte)
BY REF IS VERY USEFULL When the contents itself are being modified, when there is large data. Multiple arguments are needed to be returned, instead they can be passed as reference.
ByVal -pass the value (for string -it is the address of first byte)

What is the purpose of the DoEvents command?
Fields execution so that the operating system can process other events. Returns number of open forms. Useful for things like ‘cancel search’ in Windows

Name and define the logical tiers in a traditional 3-tiered architecture?
Presentation logic - front end (HTML, Visual Basic forms)
Business Logic - Applications and components that encapsulate business logic
Data end - databases to store data

What is the difference between a PictureBox and Image control?
Image Control - Use this to display a picture. Use it over the PictureBox because it takes less operating system resources
PictureBox - While it can display pictures, it also acts as an area on which you can print text and graphics. Use it for home-grown graphics or print previews

Under which circumstance does a VB application ignore a Timer event?
When the system is really busy doing something else and when DoEvents is being executed

What does the NewIndex property return?
Used to retrieve the index of the item most recently added to a ListBox or ComboBox control

What is the purpose of the ClipControls property on a form or container?
Returns or sets a value that determines whether graphics methods in Paint events repaint the entire object or only newly exposed areas. Also determines whether the Microsoft Windows operating environment creates a clipping region that excludes non-graphical controls contained by the object. Read-only at run time.

What is the purpose of the AutoRedraw property on a form or container?
Setting AutoRedraw to True automatically redraws the output from these methods in a Form object or PictureBox control when, for example, the object is resized or redisplayed after being hidden by another object

Have you ever used Collections? Collection Classes?
A collection is a set of Repository objects that are all connected to a common source object via a relationship collection. A collection provides a way to connect a group of dependent objects with an object that ‘contains’ them. For example, an Invoice object might have a collection of LineItem objects.

What version control systems have you used?
TLIB 16-Bit add-in

How about any other database engines?
Apollo OLE DB ,Apollo Server ,Apollo SQL ,FUNCky ,R&R Report Writer

What kind of components can you use as DCOM servers?
active-x components, Com
Dim x, y as integer.
What is x and y data type?
X as variant and y as integer.

What is the size of the variant data type?
The Variant data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text.

What is the return type of Instr and Strcmp?
Instr – integer (Numeric position)
Strcmp - integer ( if both the string are equal they result = 0)
Strcmp (Str1, Str2, Comparetype)
Comparing mode = 0 – Binary Comparing
1 – Textual Comparing

What is the max size allowed for Msgbox Prompt and Input Box?
1024

Max label caption length. –
2,048

Max Text box length –
32,000

Max Control Names length –
255.

Extension in Visual Basic
Frm, bas, cls, res, vbx, ocx, frx, vbp, exe

What is frx?
When some controls like grid and third party control placed in our application then it will create frx in run time.

Name some date function
Dateadd(), Datediff(), Datepart(), Cdate()

What will be the result for 15/4 and 15\4
15/4 = 3.75 and 15\4 = 3

What is keyword used to compare to objects?
ISOperator – Returns Boolean.

How many procedures are in VB?
2. function and sub procedures. Function Will return value but a sub procedure won’t return values…

Where will we give the option explicit keyword and for what?
In the general declarations section. To trap undeclared variables.

What is Friend Variable?
Scope sharable between projects.

What is binding? What are types of binding?
Assigning variable with defined memory space. Late Binding - Memory size is allotted in later stage.
Ex:- Dim x as object
Early Binding - Memory size is allotted while declaring itself. New Key word is important.
Ex:- Dim x as New Object

What is the difference between Property Get, Set and Let.
Set – Value is assigned to ActiveX Object from the form.
Let – Value is retried to ActiveX Object from the form.
Get- Assigns the value of an expression to a variable or property.

What is Mask Edit and why it is used?
Control. Restricted data input as well as formatted data output.

Drag and Drop state numbers and functions.
State 0 – Source control is being dragged with the range of a target.
State 1 – Out of the range of a target.
State 2 – One position in the target to another.

What are the type of validation available in VB?
Field, Form

With in the form we want to check all the text box control are typed or not? How?
For each currentcontrol in controls if typeof currentcontrol is TextBox then end if next

What is control array and How many we can have it with in the form?
Group of control share the same name. Max 32, 767.

What is the default model of the form? And what is it number?
VbModaless – 0 (Zero) – We can able to place another window above this form.

Suppose from form1 to form2 object property settings will arise to ?
Invalid procedure call or argument (Run time error – 5)

What is the diff between the Std and Class Module?
Std Global with in the project. Cls Global through out the all project only thing is we want to set the type lib. Class Modules can be Instantiated.

Different type of Instantiation?
Private – Only for the Specific Module.
Public not creatable – Private & Public
Multi Use - Variable we have to declare.
Single Use – Not possible through dll.
Global Multiuse – Have variable not Required to
Declare. Global Single Use - Only for exe.

How to declare Dll Procedure?
Declare function "" lib "" Alias "" (Arg, …..) as Return type.

What is MDI form? MDI Styles?
We can have only one MDI form for a project. Multiple Document Interface. This form type is VBModal. We have set the Child property of the forms to True to place forms inside this MDI. Styles available
1. VbCascade
2. VbTitle Horizontal

How many images can be placed in the image list ?
64

Diff type of Datatypes?
LOB (Large Object Data type).
CLOB (Stores Character Objects).
BLOB ( Store Binary Objects such as Graphic, Video Chips and Sound files).
BFILE(Store file pointers to LOB It may Contain filename for photo’s store on CD_ROM).

What is Zorder Method?
Object.Zorder = 1 or 0 Place a Specified mdiform form or control at the front or back of the z-order with n it’s Graphical Level.

What is diff between the Generic Variable and Specific Variable?
Generic Variable: Create Object Ex:-Ole-Automation . No need refer the object library.
Specific Variable: Binding Procedure Early and Late Binding ( Can be Remove from the Memory).

What are properties available in Clip Board?
No Properties Available. Only the methods they are SetText, GetText, Setdata(), Getformat(), Clear.

What is Dll?
Libraries of procedure external to the application but can be called from the application.

What is Tabstrip control? What is the starting Index value? How to locate it?
It is tab control to place our controls with in the form in multiple sheets.
Index starts with 1. And to identify
If Tabstrip1.SelectedItem.Index = 1 Then
…..
End if

Why we use Treeview Control?
To list the hierarchical list of the node objects. Such of files and Directories.

Why we need OLE-Automation? Advantages?
Enables an application to exposes objects and methods to other Applications.
No need to reserve memory. No need to write functions.
Object library that simplify programming tasks. i.e., No need to Object library. (OLB, TLB).

What is the diff between the Create Object and Get object?
Create Object - To create an instance of an object.
Get Object – To get the reference to an existing object.

Have you create Properties and Methods for your own Controls?
Properties – Public variable of a Class
Method – Public procedure of a class

What is Collection Objects?
Similarly to arrays but is preferred over an array because of the following reasons.
1. A collection objects uses less Memory than an array.
2. It provides methods to add and delete members.
3. It does not required reason statement when objects are added or deleted.
4. It does not have boundary limitations.

What is Static Variable?
Its Scope will be available through out the life time.

Private Dim x as integer. Is it true?
No. Private cannot be used in front of DIM.

What is Implicit?
Instance of specific copy of a class with its own settings for the properties defined in that class. Note: The implicit defined variable is never equal to nothing.

What are the scope of the class?
Public , private, Friend

Can we set the Instancing properties like Singleuse, GlobalSingleuse to ActiveXDll?
No.

In project properties if we set Unattended what is it mean?
This cannot have user interface. This can be used for the COM creation.

What are the Style Properties of Combo Box?
Simple, Dropdown list – We can type and select.
Dropdown Combo – Only Drop Down.

What are the Style properties of List Box?
Simple –Single Select , Extended. – Multiple Select.

What are the different types of Dialog Box?
Predefined, Custom, User Defined.

What is Parser Bug?
It is difficult to use database objects declared in a module from within a form.

What is the Dll required for running the VB?
Vbrun300.dll

Can We create CGI scripts in VB?
Yes.

How to change the Mouse Pointer?
Screen.MousePointer = VBHourGlass/VBNormal.

How to check the condition in Msgbox?
If(Msgbox("Do you want to delete this Record",VbYesNo)=VbYes)Then End if

What is difference between datagrid and flexgrid?
Datagrid – Editable.
Flexigrid – Non-Editable. (Generally used for Read only purpose.)

What is ADO? What are its objects ?
ActiveX Data Object. ADO can access data from both flat files as well as the databases. I.e., It is encapsulation of DAO, RDO, and OLE that is why we call it as OLE-DB Technology. Objects are Connection, Record Set, Command, Parameter, field, Error, Property.

What is Dataware Control?
Any control bound to Data Control.
Ex:- Textbox, Check Box, Picture Box, Image Control, Label, List box, Combo Box, DB Combo,
What are two validate with Data Control?
Data_Validate, Data_Error.

Record set types and Number available in VB?
3. 1- Dynaset, 0 – Table, 2 – Snap Shot.

Referential Integrity (Take care By jet database Engine).
Cascade Delete, Cascade Update – is done setting property of Attributes.
DbRelationDeleteCascade,
DbRelationUpdateCascade.

What are the locks available in Visual Basic?
Locking is the process by which a DBMS restricts access to a row in a multi-user environment
4 types of locks. They are
1.Batch Optimistic
2.Optimistic
3.Pessimistic
4.ReadOnly

What is the diff between RDO and ADO?
RDO is Hierarchy model where as ADO is Object model. ADO can access data from both flat files as well as the data bases. I.e., It is encapsulation of DAO, RDO , OLE that is why we call it as OLE-DB Technology.

How can we call Stored procedure of Back End in RDO and ADO ?
In RDO – We can call using RDO Query Objects.
In ADO – We can call using Command Objects.

What is the different between Microsoft ODBC Driver and Oracle OBDC Driver?
Microsoft ODBC driver will support all the methods and properties of Visual Basic. Where as the Oracle not.

What are the Technologies for Accessing Database from Visual Basic?
DAO, Data Control, RDO, ODBCDIRECT, ADO, ODBC API.

Calling Stored Procedures in VB?
1. Calling Simply the Procedure with out Arguments "Call ProcedureName}"

If it is with Arguments Means then
Declare the Query Def qy
Set Qy as New Query def
Qy.SQL = "{Call ProcedureName(?,?,?)}"
qy(0)=val(Txt1.Text)
qy(1)=val(Txt2.Text)
qy(2)=val(Txt3.Text)
Set Rs = Qy.OpenresultSet
Txt(1)=Rs.RdoColumns(0)

What is MAPI ?
Messaging Application programming Interface.

Different type of Passing Value?
By value, By ref, Optional, Param Array.
Note:- Optional keyword cannot be used while declaring arguments for a function using param array.

What are the different types of error?
Syntax Errors, Runtime , Logic.

What is Seek Method which type of record set is available this?
Only in DbOpenTables.
Syntax: rs.index = "empno"
rs.seek "=" , 10
If with our setting the rs.index then run time error will occur.

What is Centralization Error Handling?
Writing function and calling it when error occurs.

Handling Error in Calling chain.
This will call the top most error where the error is handled.

To connect the Data Control with Back end What are all the properties to be set?
Data source Name, Record Source Name

How to trap Data Base Error?
Dim x as RDOError
X(0).Des
X(1).Number

What is view Port?
The area under which the container provides the view of the ActiveX Document is known as a view port.

What methods are used for DBGrid in unbound mode?
AddData, EditData, Readdata, WriteData.

How to increase the Date corresponding with month, date, year?
DateSerial(year(Now),Month(Now)+1,1)
Hour, min, sec, month, year, DateSerial, dateadd, datediff, weekday, datevalue, timeserial, timevalue.

Setting the Cursors.
Default Cursor – 0
ODBC Cursor (Client side) – 1
ServerSide Cursors (More Network traffic) - 2Cursor management
Client Batch – Batch up the Multiple SQL Statements in a single string and Send them to the Server at one time.

What are the record set types?
RdOpenFowardOnly 0 (Default used only for the read only purpose)
RdOpenStatic 1
RdOpenDynamic 2
RdOpenKeySet 3 (Normally used for the live project)

Diff types of Lock Types?
RdConcurReadOnly 0 (Default)
RdConcurLock 1 (Pessimistic Locking)
RdConcurRowver 2 (Optimistic Locking)
RdConcurValues 3
RdConcurBatch 4

What are the RDO Methods and Events?
Methods Events
Begin Trans Validate
Commit Trans Reposition
Rollback Trans Error
Cancel Query Complied
Refresh
Update Controls
Update row

What is Static Cursor?
In ADO Snap Shot is called so.

What is Mixed Cursors?
Static + Keyset

What is FireHouse Cursors?
Forward Only Some time Updateable

What is DBSqlPassThrough?
It will By Passing the Jet Query Processor.

What is DBFailError?
Rolls Back updates if any errors Occurs.

DSN Less Connection?
"Server=Oracle; Driver={Microsoft ODBC for Oracle};"

What is RdExecDirect?
Bypasses the Creation of a stored procedure to execute the query. Does not apply to Oracle.

RdoParameter Object RdoParameterConstant
Direction RdparamInput
RdparamInputOutput
RdParamOutput
Name
Type
Value

STORED Procedure

2011-01-15T22:45:00.000+05:30

CREATE PROCEDURE
Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.
Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).
Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.
Syntax
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]
Arguments
procedure_name
Is the name of the new stored procedure. Procedure names must conform to the rules for identifiers and must be unique within the database and its owner. For more information, see Using Identifiers.
Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters. Specifying the procedure owner name is optional.
;number
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
@parameter
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is executed (unless a default for the parameter is defined). A stored procedure can have a maximum of 2,100 parameters.
Specify a parameter name using an at sign (@) as the first character. The parameter name must conform to the rules for identifiers. Parameters are local to the procedure; the same parameter names can be used in other procedures. By default, parameters can take the place only of constants; they cannot be used in place of table names, column names, or the names of other database objects. For more information, see EXECUTE.
data_type
Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure. However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified. For more information about SQL Server - supplied data types and their syntax, see Data Types.

Note There is no limit on the maximum number of output parameters that can be of cursor data type.
VARYING
Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
OUTPUT
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.
n
Is a placeholder indicating that a maximum of 2,100 parameters can be specified.
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.
ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication.

Note During an upgrade, SQL Server uses the encrypted comments stored in syscomments to re-create encrypted procedures.
FOR REPLICATION
Specifies that stored procedures created for replication cannot be executed on the Subscriber. A stored procedure created with the FOR REPLICATION option is used as a stored procedure filter and only executed during replication. This option cannot be used with the WITH RECOMPILE option.
AS
Specifies the actions the procedure is to take.
sql_statement
Is any number and type of Transact-SQL statements to be included in the procedure. Some limitations apply.
n
Is a placeholder that indicates multiple Transact-SQL statements may be included in this procedure.
Remarks
The maximum size of a stored procedure is 128 MB.
A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb). The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error. To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.
It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if not specified in a CREATE TABLE or ALTER TABLE statement. If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors. If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created with the same nullability for all connections that execute the stored procedure.
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.
Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered. If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting. When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes. The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

Note Whether SQL Server interprets an empty string as either a single space or as a true empty string is controlled by the compatibility level setting. If the compatibility level is less than or equal to 65, SQL Server interprets empty strings as single spaces. If the compatibility level is equal to 70, SQL Server interprets empty strings as empty strings. For more information, see sp_dbcmptlevel.
Getting Information About Stored Procedures
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure exists with the procedure name as the parameter.

Note Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.
For a report on the objects referenced by a procedure, use sp_depends.
To rename a procedure, use sp_rename.
Referencing Objects
SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at run time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.
Deferred Name Resolution and Compatibility Level
SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at run time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.
Executing Stored Procedures
When a CREATE PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.
Parameters Using the cursor Data Type
Stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.

Note The cursor data type cannot be bound to application variables through the database APIs such as OLE DB, ODBC, ADO, and DB-Library. Because OUTPUT parameters must be bound before an application can execute a stored procedure, stored procedures with cursor OUTPUT parameters cannot be called from the database APIs. These procedures can be called from Transact-SQL batches, stored procedures, or triggers only when the cursor OUTPUT variable is assigned to a Transact-SQL local cursor variable.
Cursor Output Parameters
The following rules pertain to cursor output parameters when the procedure is executed:
• For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:
• A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.
• The procedure fetches the first 5 rows of result set RS.
• The procedure returns to its caller.
• The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.
• For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.
• For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

Note An empty result set is not the same as a null value.
• For a scrollable cursor, all the rows in the result set are returned to the calling batch, stored procedure, or trigger at the conclusion of the execution of the stored procedure. When returned, the cursor position is left at the position of the last fetch executed in the procedure.
• For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

Note The closed state matters only at return time. For example, it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, stored procedure, or trigger.
Temporary Stored Procedures
SQL Server supports two types of temporary procedures: local and global. A local temporary procedure is visible only to the connection that created it. A global temporary procedure is available to all connections. Local temporary procedures are automatically dropped at the end of the current session. Global temporary procedures are dropped at the end of the last session using the procedure. Usually, this is when the session that created the procedure ends.
Temporary procedures named with # and ## can be created by any user. When the procedure is created, the owner of the local procedure is the only one who can use it. Permission to execute a local temporary procedure cannot be granted for other users. If a global temporary procedure is created, all users can access it; permissions cannot be revoked explicitly. Explicitly creating a temporary procedure in tempdb (naming without a number sign) can be performed only by those with explicit CREATE PROCEDURE permission in the tempdb database. Permission can be granted and revoked from these procedures.

Note Heavy use of temporary stored procedures can create contention on the system tables in tempdb and adversely affect performance. It is recommended that sp_executesql be used instead. sp_executesql does not store data in the system tables and therefore avoids the problem.
Automatically Executing Stored Procedures
One or more stored procedures can execute automatically when SQL Server starts. The stored procedures must be created by the system administrator and executed under the sysadmin fixed server role as a background process. The procedure(s) cannot have any input parameters.
There is no limit to the number of startup procedures you can have, but be aware that each consumes one connection while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one connection.
Execution of the stored procedures starts when the last database is recovered at startup. To skip launching these stored procedures, specify trace flag 4022 as a startup parameter. If you start SQL Server with minimal configuration (using the -f flag), the startup stored procedures are not executed. For more information, see Trace Flags.
To create a startup stored procedure, you must be logged in as a member of the sysadmin fixed server role and create the stored procedure in the master database.
Use sp_procoption to:
• Designate an existing stored procedure as a startup procedure.
• Stop a procedure from executing at SQL Server startup.
• View a list of all procedures that execute at SQL Server startup.
Stored Procedure Nesting
Stored procedures can be nested; that is one stored procedure calling another. The nesting level is incremented when the called procedure starts execution, and decremented when the called procedure finishes execution. Exceeding the maximum levels of nesting causes the whole calling procedure chain to fail. The current nesting level is returned by the @@NESTLEVEL function.
To estimate the size of a compiled stored procedure, use these Performance Monitor Counters.
Performance Monitor object name Performance Monitor Counter name
SQLServer: Buffer Manager Cache Size (pages)
SQLServer: Cache Manager Cache Hit Ratio
Cache Pages
Cache Object Counts*

* These counters are available for various categories of cache objects including adhoc sql, prepared sql, procedures, triggers, and so on.
For more information, see SQL Server: Buffer Manager Object and SQL Server: Cache Manager Object.
sql_statement Limitations
Any SET statement can be specified inside a stored procedure except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL, which must be the only statements in the batch. The SET option chosen remains in effect during the execution of the stored procedure and then reverts to its former setting.
Inside a stored procedure, object names used with certain statements must be qualified with the name of the object owner if other users are to use the stored procedure. The statements are:
• ALTER TABLE
• CREATE INDEX
• CREATE TABLE
• All DBCC statements
• DROP TABLE
• DROP INDEX
• TRUNCATE TABLE
• UPDATE STATISTICS
Permissions
CREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.
Examples
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'
See Also
ALTER PROCEDURE
Batches
Control-of-Flow Language
Cursors
DBCC
DECLARE @local_variable
DROP PROCEDURE
Functions
GRANT
Programming Stored Procedures
SELECT
sp_addextendedproc
sp_depends
sp_helptext
sp_procoption
sp_recompile
sp_rename
System Tables
Using Comments
Using Variables and Parameters

DBA Questions

2011-01-15T22:34:00.000+05:30

What is normalization? Explain different levels of normalization?
Check out the article Q100139 from Microsoft knowledge base and of
course, there's much more information available in the net. It'll be a
good idea to get a hold of any RDBMS fundamentals textbook,
especially the one by C. J. Date. Most of the times, it will be okay
if you can explain till third normal form.
What is denormalization and when would you go for it?
As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.
How do you implement one-to-one, one-to-many and many-to-many
relationships while designing tables?
One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into
two tables with primary key and foreign key relationships.
Many-to-Many relationships are implemented using a junction table with
the keys from both the tables forming the composite primary key of the
junction table.
It will be a good idea to read up a database designing fundamentals
textbook.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which
they are defined. But by default primary key creates a clustered index
on the column, where are unique creates a nonclustered index by
default. Another major difference is that, primary key doesn't allow
NULLs, but unique key allows one NULL only.
What are users defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by
providing a descriptive name, and format to the database. Take for
example, in your database, there is a column called Flight_Num which
appears in many tables. In all these tables it should be varchar(8).
In this case you could create a user-defined datatype called
Flight_num_type of varchar (8) and use it across all your tables.
See sp_addtype, sp_droptype in books online.
What is bit datatype and what's the information that can be stored
inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or
false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0
and there was no support for NULL. But from SQL Server 7.0 onwards,
bit datatype can represent a third state, which is NULL.
Define candidate key, alternate key, composite key.
A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called
composite key.
What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them. See CREATE
DEFUALT in books online.
Back to top
SQL Server architecture (top)
What is a transaction and what are ACID properties?
A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction. For
more information and explanation of these properties, see SQL Server
books online or any RDBMS fundamentals text book.
Explain different isolation levels
An isolation level determines the degree of isolation of data between
concurrent transactions. The default SQL Server isolation level is
Read Committed. Here are the other isolation levels (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable
Read, Serializable. See SQL Server books online for an explanation of
the isolation levels. Be sure to read about SET TRANSACTION ISOLATION
LEVEL, which lets you customize the isolation level at the connection
level.
CREATE INDEX myIndex ON myTable(myColumn)
What type of Index will get created after executing the above statement?
Non-clustered index. Important thing to note: By default a clustered
index gets created on the primary key, unless specified otherwise.
What's the maximum size of a row?
8060 bytes. Don't be surprised with questions like 'what is the
maximum number of columns per table'. Check out SQL Server books
online for the page titled: "Maximum Capacity Specifications".
Explain Active/Active and Active/Passive cluster configurations
Hopefully you have experience setting up cluster servers. But if you
don't, at least be familiar with the way clustering works and the two
clustering configurations Active/Active and Active/Passive. SQL
Server books online has enough information on this topic and there is
a good white paper available on Microsoft site.
Explain the architecture of SQL Server
This is a very important question and you better be able to answer it
if consider yourself a DBA. SQL Server books online is the best place
to read about SQL Server architecture. Read up the chapter dedicated
to SQL Server Architecture.
What is lock escalation?
Lock escalation is the process of converting a lot of low level locks
(like row locks, page locks) into higher-level locks (like table
locks). Every lock is a memory structure too many locks would mean,
more memory being occupied by locks. To prevent this from happening,
SQL Server escalates the many fine-grain locks to fewer coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets
logged in the transaction log, which makes it slow. TRUNCATE TABLE
also deletes all the rows in a table, but it won't log the deletion of
each row, instead it logs the reallocations of the data pages of the
table, which makes it faster. Of course, TRUNCATE TABLE can be rolled
back.
Explain the storage models of OLAP
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more
information.
What are the new features introduced in SQL Server 2000 (or the latest
release of SQL Server at the time of your interview)? What changed
between the previous version of SQL Server and the current version?
This question is generally asked to see how current is your knowledge.
Generally there is a section in the beginning of the books online
titled "What's New", which has all such information. Of course,
reading just that is not enough, you should have tried those things to
better answer the questions. Also check out the section titled
"Backward Compatibility" in books online, which talks about the changes
that have taken place in the new version.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database
automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages
titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
What is an index? What are the types of indexes? How many clustered
indexes can be created on a table? I create a separate index on each
column of a table. What are the advantages and disadvantages of this
approach?
Indexes in SQL Server are similar to the indexes in books. They help
SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes.
When you create a clustered index on a table, all the rows in the
table are stored in the order of the clustered index key. So, there
can be only one clustered index per table. Non-clustered indexes have
their own storage separate from the table data storage. Non-clustered
indexes are stored as B-tree structures (so do clustered indexes),
with the leaf level nodes having the index key and it's row locater.
The row located could be the RID or the Clustered index key, depending
up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the
query performance, as the query optimizer can choose from all the
existing indexes to come up with an efficient execution plan. At the
same time, data modification operations (such as INSERT, UPDATE,
DELETE) will become slow, as every time data changes in the table, all
the indexes need to be updated. Another disadvantage is that, indexes
need disk space, the more indexes you have, more disk space is used.
Back to top
Database administration (top)
What is RAID and what are different types of RAID configurations?
RAID stands for Redundant Array of Inexpensive Disks, used to provide
fault tolerance to database servers. There are six RAID levels 0
through 5 offering different levels of performance, fault tolerance.
MSDN has some information about RAID levels and for detailed
information; check out the RAID advisory board's homepage
What are the steps you will take to improve performance of a poor
performing query?
This is a very open ended question and there could be a lot of reasons
behind the poor performance of a query. But some general issues that
you could talk about would be: No indexes, table scans, missing or out
of date statistics, blocking, excess recompilations of stored
procedures, procedures and triggers without SET NOCOUNT ON, poorly
written query with unnecessarily complicated joins, too much
normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance
problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET
STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance
monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from
Microsoft web site. Don't forget to check out sql-server-performance.com
What are the steps you will take, if you are tasked with securing an
SQL Server?
Again this is another open-ended question. Here are some things you
could talk about: Preferring NT authentication, using server, database
and application roles to control access to the data, securing the
physical database files using NTFS permissions, using an unguessable
SA password, restricting physical access to the SQL Server, renaming
the Administrator account on the SQL Server computer, disabling the
Guest account, enabling auditing, using multiprotocol encryption,
setting up SSL, setting up firewalls, isolating SQL Server from the
web server etc.
Read the white paper on SQL Server security from Microsoft website.
Also check out My SQL Server security best practices
What is a deadlock and what is a live lock? How will you go about
resolving deadlocks?
Deadlock is a situation when two processes, each having a lock on one
piece of data, attempt to acquire a lock on the other's piece. Each
process would wait indefinitely for the other to release the lock,
unless one of the user processes is terminated. SQL Server detects
deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is
repeatedly denied because a series of overlapping shared locks keeps
interfering. SQL Server detects the situation after four denials and
refuses further shared locks. A livelock also occurs when read
transactions monopolize a table or page, forcing a write transaction
to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks” in SQL
Server books online. Also check out the article Q169960 from Microsoft
knowledge base.
What is blocking and how would you troubleshoot it?
Blocking happens when one connection from an application holds a lock
and a second connection requires a conflicting lock type. This forces
the second connection to wait, blocked on the first.
Read up the following topics in SQL Server books online: Understanding
and avoiding blocking, coding efficient transactions.
Explain CREATE DATABASE syntax
Many of us are used to creating databases from the Enterprise Manager
or by just issuing the command: CREATE DATABAE MyDB. But what if you
have to create a database with two file groups, one on drive C and the
other on drive D with log on drive E with an initial size of 600 MB
and with a growth factor of 15%? That's why being a DBA you should be
familiar with the CREATE DATABASE syntax. Check out SQL Server books
online for more information.
How to restart SQL Server in single user mode? How to start SQL Server
in minimal configuration mode?
SQL Server can be started from command line, using the SQLSERVR.EXE.
This EXE has some very important parameters with which a DBA should be
familiar with. -m is used for starting SQL Server in single user mode
and -f is used to start the SQL Server in minimal configuration mode.
Check out SQL Server books online for more parameters and their
explanations.
As a part of your job, what are the DBCC commands that you commonly
use for database maintenance?
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there
are whole loads of DBCC commands, which are very useful for DBAs.
Check out SQL Server books online for more information.
What are statistics, under what circumstances they go out of date, how
do you update them?
Statistics determine the selectivity of the indexes. If an indexed
column has unique values then the selectivity of that index is more,
as opposed to an index with non-unique values. Query optimizer uses
these indexes in determining whether to choose an index or not while
executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added,
changed, or removed (that is, if the distribution of key values has
changed), or the table has been truncated using the TRUNCATE TABLE
statement and then repopulated
3) Database is upgraded from a previous version
Look up SQL Server books online for the following commands: UPDATE
STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP
STATISTICS, sp_autostats, sp_createstats, sp_updatestats
What are the different ways of moving data/databases between servers
and databases in SQL Server?
There are lots of options available; you have to choose your option
depending upon your requirements. Some of the options you have are:
BACKUP/RESTORE, detaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT
scripts to generate data.
Explian different types of BACKUPs avaialabe in SQL Server? Given a
particular scenario, how would you go about choosing a backup plan?
Types of backups you can create in SQL Sever 7.0+ are Full database
backup, differential database backup, transaction log backup,
filegroup backup. Check out the BACKUP and RESTORE commands in SQL
Server books online. Be prepared to write the commands in your
interview. Books online also has information on detailed
backup/restore architecture and when one should go for a particular
kind of backup.
What is database replicaion? What are the different types of
replication you can set up in SQL Server?
Replication is the process of copying/moving data between databases on
the same or different servers. SQL Server supports the following types
of replication scenarios:
* Snapshot replication
* Transactional replication (with immediate updating subscribers,
with queued updating subscribers)
* Merge replication
See SQL Server books online for indepth coverage on replication. Be
prepared to explain how different replication agents function, what
are the main system tables used in replication etc.
How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the
sqlservr.exe, which is used to determine the service pack installed.
To know more about this process visit SQL Server service packs and
versions.
Back to top
Database programming (top)
What are cursors? Explain different types of cursors. What are the
disadvantages of cursors? How can you avoid cursors?
Cursors allow row-by-row processing of the result sets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See
books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor,
it results in a network roundtrip, where as a normal SELECT query
makes only one rowundtrip, however large the resultset is. Cursors are
also costly because they require more resources and temporary storage
(results in more IO operations). Further, there are restrictions on
the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of
cursors. Here is an example:
If you have to give a flat hike to your employees using the following
criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be achieved by multiple update statements or
can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to
call a stored procedure when a column in a particular row meets
certain condition. You don't have to use cursors for this. This can be
achieved using WHILE loop, as long as there is a unique key to
identify each row. For examples of using WHILE loop for row by row
processing, check out the 'My code library' section of my site or
search for WHILE.
Write down the general syntax for a SELECT statements covering all the
options.
Here's the basic syntax: (Also checkout SELECT in books online for
advanced syntax).
SELECT select list
[INTO new table_]
FROM table source
[WHERE search condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from
another table.
Types of joins: INNER Joins, OUTER Joins, CROSS Joins. OUTER Joins are
further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL
OUTER JOINS.
For more information seepages from books online titled: "Join
Fundamentals" and "Using Joins".
Can you have a nested transaction?
Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and
@@TRANCOUNT
What is an extended stored procedure? Can you instantiate a COM object
by using T-SQL?
An extended stored procedure is a function within a DLL (written in a
programming language like C, C++ using Open Data Services (ODS) API)
that can be called from T-SQL, just the way we call normal stored
procedures using the EXEC statement. See books online to learn how to
create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++)
object from T-SQL by using sp_OACreate stored procedure. Also see
books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty,
sp_OADestroy. For an example of creating a COM object in VB and
calling it from T-SQL, see 'My code library' section of this site.
What is the system function to get the current user's user id?
USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
What are triggers? How many triggers you can have on a table? How to
invoke a trigger on demand?
Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place
on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, this restriction is gone, and you could create multiple
triggers per each action. But in 7.0 there's no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify
which trigger fires first or fires last using sp_settriggerorder
Triggers can't be invoked on demand. They get triggered only when an
associated action (INSERT, UPDATE, DELETE) happens on the table on
which they are defined.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification
operation happens. So in a way, they are called post triggers. But in
SQL Server 2000 you could create pre triggers also. Search SQL Server
2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and
COLUMNS_UPDATED()
There is a trigger defined for INSERT operations on a table, in an
OLTP system. The trigger is written to instantiate a COM object and
pass the newly insterted rows to it for some custom processing. What
do you think of this implementation? Can this be implemented better?
Instantiating COM objects is a time consuming process and since you
are doing it from within a trigger, it slows down the data insertion
process. Same is the case with sending emails from triggers. This
scenario can be better implemented by logging all the necessary data
into a separate table, and have a job which periodically checks this
table and does the needful.
What is a self join? Explain it with an example.
Self join is just like any other join, except that two instances of
the same table will be joined in the query. Here is an example:
Employees table which contains rows for normal employees as well as
managers. So, to find out the managers of all the employees, you need
a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the
employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid

The logic behind this 'last day of the month' calculations is that you get the month part of a given date, add one to it, there by getting to the next month. Then subtract 1 day from the first of that month. This whole Thing can be written in one line and here it is:

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))
1. How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
2. How do you start MySQL on Linux? - /etc/init.d/mysql start
3. Explain the difference between mysql and mysqli interfaces in PHP? - mysqli is the object-oriented version of mysql library functions.
4. What’s the default port for MySQL Server? - 3306
5. What does tee command do in MySQL? - tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
6. Can you save your connection settings to a conf file? - Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
7. How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"
8. Use mysqldump to create a copy of the database? - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
9. Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.
10. What are some good ideas regarding user security in MySQL? - There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
11. Explain the difference between MyISAM Static and MyISAM Dynamic. - In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
12. What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.
13. Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.
14. Explain advantages of MyISAM over InnoDB? - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
15. What are HEAP tables in MySQL? - HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
16. How do you control the max size of a HEAP table? - MySQL config variable max_heap_table_size.
17. What are CSV tables? - Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
18. Explain federated tables. - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
19. What is SERIAL data type in MySQL? - BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
20. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table? - It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
21. Explain the difference between BOOL, TINYINT and BIT. - Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
22. Explain the difference between FLOAT, DOUBLE and REAL. - FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
23. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table? - 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
24. What happens if a table has one column defined as TIMESTAMP? - That field gets the current timestamp whenever the row gets altered.
25. But what if you really want to store the timestamp data, such as the publication date of the article? - Create two columns of type TIMESTAMP and use the second one for your real data.
26. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
27. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do? - On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
28. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP. - A default value is used on initialization, a current timestamp is inserted on update of the row.
29. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? - CHAR(3), since MySQL automatically adjusted the data type.

OOPS Fundamental

2011-01-15T22:19:00.000+05:30

Object Oriented Programming-Interview Questions

What is OOP?
The object oriented programming is commonly known as OOP. Most of the languages
are developed using OOP concept. Object-oriented programming (OOP) is a
programming concept that uses "objects" to develop a system.
A programming object has an ability to perform actions and has attributes. It
performs just like real world entities for e.g. a motor bike. A bike performs actions
such as ’Start’, ’Stop’ etc., and it has attributes like red color, 150 cc etc. So does
an Object. Actions and attributes are represented by Methods and fields or properties
respectively in programming language.
An object hides the implementation details and exposes only the functionalities and
parameters it requires to its client. Here also an object shares the same concept as
that of a bike. While driving a motor bike, we are unaware of its implementation
details such as how it is developed, internal working of gears etc.? We know only the
functions or actions it can perform.
What are the various elements of OOP?
Various elements of OOP are:
• Object
• Class
• Method
• Encapsulation
• Information Hiding
• Inheritance
• Polymorphism
Explain an object.
An object is an entity that keeps together state and behaviors. For instance, a car
encapsulates state such as red color, 900 cc etc and behaviors as ’Start’, ’Stop’ etc.,
so does an object.
An object is an instance of a class. If you consider Dog as a class, it will contain all
possible dog traits, while object German Shepherd contains characteristics of
specific type of dog.
Define a class.
A class represents description of objects that share same attributes and actions. It
defines the characteristics of the objects such as attributes and actions or behaviors.
It is the blue print that describes objects.

What is Method?

Method is an objects behavior. If you consider Dog as an object then its behaviors
are bark, walk, run etc.
Explain Encapsulation concept in OOP.
Encapsulation means keeping actions and attributes together under a single unit.
This can also be understood using a motor bike example. A bike has actions such as
’switch on light’, ’horn’ etc. and attributes such specific color, size, weight etc. Here
the actions and attributes are bundled together under a single unit, bike.
In a programming language, methods and properties that correspond to actions and
attributes respectively are kept under a unit called object. The advantage of
encapsulation is that the implementation is not accessible to the client. The user has
to know only the functionality of encapsulated unit and information to be supplied to
get the result.
What is Information Hiding in OOP?
Information hiding concept restricts direct exposure of data. Data is accessed
indirectly using safe mechanism, methods in case of programming object. Taking
bike as an example, we have no access to the piston directly, we can use ’start
button’ to run the piston. You can understand the advantage of information hiding
concept from this example. If a bike manufacturer allows direct access to piston, it
would be very difficult to control actions on the piston.
Define Inheritance.
Inheritance concept in OOP allows us to create a new class using an existing one. It
also allows the new class to add its own functionality. This concept can also be
related to real world entity. A bike manufacturer uses same mechanism of existing
version of the bike while launching a new version with some added functionalities.
This allows him to save time and efforts.
Explain the term Polymorphism.
Polymorphism means the ability to take more than one form. An operation may
exhibit different behaviors in different instances. The behavior depends on the data
types used in the operation.
What is Overloading Polymorphism?
Overloading allows multiple functions to exist with same name but different
parameters. Again if you take bike as an example, it has a function Start with two
forms i.e. ’Auto Start’ and ’kick start’.
Explain Overriding Polymorphism.
Overriding means changing behavior of methods of base class in derive class by
overriding the base class methods. If class A is a base class with method ’calculate’
and class B inherits class A, thus derives method ’calculate’ of class A. The behavior
of ’calculate’ in class B can be changed by overriding it.
What are the advantages of OOP?

Following are the advantages of OOP:

• It presents a simple, clear and easy to maintain structure.
• It enhances program modularity since each object exists independently.
• New features can be easily added without disturbing the existing one.
• Objects can be reused in other program.

Introduction Object and Class for C#

2011-01-15T22:09:00.001+05:30

Introduction:
OOP stands for Object-Oriented Programming. OOP is relatively a new way to program computer applications. Before OOP programmers used to creating computer applications using procedural-programming (or structure-programming) but when OOP solved a lot of the problems of the procedural-programming so almost all of the programmers and developers began using OOP languages. In procedural- programming all the program functionality written in a few modules of code or maybe one module (depending on the program)and these modules depend on one another and maybe if you change a line of code you will have to rewrite the whole module again and maybe the whole program butin Object-Oriented Programming programmers write independent parts of a program called classes each class represent a part of the program functionality and these classes can be assembled to form a program and when you need to change some of the program functionality all what you have to do is to replace the target class which may contain a problem. So in OOP applications create by these of classes and these applications can contain any number of classes. That will get us to discuss the Class and Object concept.
Classes and objects:
You may find it not easy to understand the class and object story but I will try to do my best explaining it. Actually the class and object concept is related to each other and some beginners don’t care about understanding it clear so I think they will have a hard times learning C#.
Object-Oriented concept takes the most of its functionality from the real-life concepts. For example, I will discuss the concept of Classes and Objects of the world first and then you will understand the computer’s Classes and Objects before I even write anything about it.
World’s Classes and Objects:
In our world we have a classes and objects for those classes. Everything in our world considered to be an object. For example, people are objects, animals are objects too, minerals are objects, everything in the world are objects. Easy right ? but what about classes. In our world we have to differentiate between objects that we are living with. So we must understand that there are a classifications (this is how they get the name and the concepts of the Class) for all of those objects. For example, I ‘man object, David is object too, Maria is another object so we are from apeople class (or type). I have a dog called Ricky so it’s an object, Myfriend’s dog called Doby is also an object so they are from a Dogs class (ortype). A third example, I have a computer Pentium III this is object, My friend have a computer Pentium IIII so this is another object and they are froma Computers class (or type). Now I think you got the concept of theClass and Object but let me crystallize it for you. In our world we have classifications for objects and every object must be from some classification. So a Class is a way for describing some properties and functionalities or behaviors of a group of objects. In other words, The class considered to be template for some objects. So maybe I will create a class called person so thisis a template of the functionality and the properties of persons. I explained it by more than a way so wait until you see the first example and I think you will grasp it completely.
Computer’s Classes and Objects:
Computer’s Classes discussion is similar to what you grasp from the last section with some modifications to become computerized.
A C# Class Considered being the primary building block of the language. What I mean by the primary building block of the language is that every time you work with C# you will create Classes to form a program. We use Classes as a template to put the properties and functionalities or behaviors in one building block for some group of objects and after that we use that template to create the objects we need. For example, We need to have persons objects in our program so the first thing to do here is to create a Class called Person that contains all the functionalities or behaviors and properties of any person and after that we will use that Class or template to create as many objects as we need. Creating object of a specific class type called “instance of the class”. Don’t worry if you didn’t grasp it 100% and don’t worry if you don’t know what’s the Class and Object’s properties and functionalities or behaviors because we still in the beginning and until now I didn’t give any code examples. So let’s take a brief of what’s the Class and what’s an object?
The Class : Is a building block that contains the properties and functionalities that
describe some group of objects, We can create a class Person that contains:
1- The properties of any normal person on the earth like :Hair Color, Age, Height, Weight, Eyes Color.
2- The functionalities or behaviors of any normal person on the earth like : Drink water, Eat, Go to the work and later we will see how we can implement the functionalities or behaviors and properties.
There are 2 kinds of classes : The built-it classes that come with the .NET Framework and called Framework Class Library. And the programmer defined-classes which we create it.
The class contains data (in the form of variables and properties) and behaviors (in the form of methods to process these data). We will understand this concept more later in the article.
When we declare a variable in a class we call it member variables or instance variables. The name instance come from the fact that when we create an object we instance a class to create that object so instance of a class means object of that class and instance variable means variable that exists in that class.
The Object : It’s object of some classification (or class, or type. All means the same thing) and when you create the object you can specify the properties of that object. What I mean here is me as an object can have a different properties (Hair Color, Age, Height, Weight) of you as another object. For example, I have a brown eyes and you have a green eyes so when I create 2 objects I will specify a brown color for my object’s Eyes Color property and I will specify a green color for your object’s Eyes Color property.
So to complete my introduction to Classes we must discuss Properties and Variables.
Properties and Variables:
Variables declared in a class store the data for each instance, What that means ? means that when you instantiate this class (that is, When you create an object of this class) the object will allocate a memory locations to store the data of its variables. Let’s take an example to understand it well.
class Person
{
public int Age;
public string HairColor;
}
This is our simple class which contains 2 variables. Don’t worry about public keyword now because we will talk about it later . Now we will instantiate this class (that is, When you create an object of this class).
static void Main(string[]args)
{
Person Michael = newPerson();
Person Mary = new Person();
// Specify some values for the instance variables
Michael.Age = 20;
Michael.HairColor = “Brown”;
Mary.Age = 25;
Mary.HairColor = “Black”;
// print the console’s screen someof the variable’s values
Console.WriteLine(“Michael’s age = {0}, and Mary’s age= {1}”,Michael.Age,
Mary.Age);
Console.ReadLine();
}
So we begin our Main method by creating 2 objects of Person type. After creating the 2 objects we initialize the instance variables for object Michael and then for object Mary. Finally we print some values to the console. Here when you create Michael object C# compiler allocate a memory location for the 2 instance variables to put the values there. Also the same thing with Mary object the compiler will create 2 variables in the memory for Mary object. So each object now contains a different data. Note that we directly accessed the variables and we put any values we want, Right? So maybe someone doesn’t like me will put in my object’s variable Age value of 120 years so I will not get any kind of jobs. But wait there are a solution for this problem. We will use properties.
Properties:
Properties are a way to access the variables of the class in secure manner. Let’s see the same example using properties.
class Person
{
private int age;
private string hairColor;
public int Age
{
get
{
returnage;
}
set
{
if(value <= 65 && value >= 18)
{
age = value;
}
else
age = 18;
}
}
public string HairColor
{
get
{
return hairColor;
}
set
{
hairColor = value;
}
}
}
I made some modifications but please just care about the new2 properties that I created it here. So the property consists of 2 access or. The get access or which is responsible of retrieving the variable value, And the set accessor which is responsible of modifying the variable’s value. So The get access or code is very simple we just use the keyword return with the variable name to return its value. so the following code:
get
{
return hairColor;
}
return the value stored in hairColor.
Note: the keyword value is a reserved keyword by C# (that is, reserved keywords means that these keywords own only by C# and you can’t create it for other purpose. For example, You can’t create a variable called value .If you did that C# compiler will generate an error and to make things easier Visual Studio.NET will color the reserved keywords to blue.)
Let’s put this code at work and after that discuss the set access or.
static void Main(string[]args)
{
Person Michael = newPerson();
Person Mary = new Person()
;
// Specify some values for the instance variables
Michael.Age = 20;
Michael.HairColor = “Brown”;
Mary.Age = 25;
Mary.HairColor = “Black”;
// print the console’s screen some of the variable’s values
Console.WriteLine(“Michael’s age = {0}, and Mary’s age= {1}”,Michael.Age,
Mary.Age);
Console.ReadLine();
}
Here I created the same objects from last example the modifications that I used only properties to access the variable instead of access it directly. Look at the following line of code
Michael.Age = 20;
When you assign a value to the property like that C# will use the set accessor. The great thing withthe set accessor is that we can controlthe assigned value and test it and maybe change to in some cases. When you assign a value to a property C# change the value in a variable and you can access the variable’s value using the reserved keyword value exactly as I did in the example. Let’s see it again here.
set
{
if(value <= 65 && value >= 18)
{
age = value;
}
else
age = 18;
}
Here in the code I used if statement to test the assigned value because for some reason I want any object of type Person to be in age between 18 and 65. Here I test the value and if it in the range then simply I will store it in the variable age and it it’s not in the range I will put 18 as a value to age. It was just a simple example for the properties but there is a complete article about properties soon.
How we create objects and classes?
We create classes by define it like that:
using the keyword class followed by the class name like that
class Person
Then we open a left brace “{” and after we write our methods and properties we close it by a right brace “}”. That ‘show we create a class. Let’s see how we create an instance of that class.
In the same way as we declare a variable of type int we create an object variable of Person type with some modifications:
int age;
Person Michael = new Person();
In the first line of code we specified integer variable called age. In the second line we specified first the type of Object we need tocreate followed by the object’s name followed by a reserved operator called new and we end by typing the class name again followed byparenthesis “()”.
Let’s understand it step-by-step. Specifying the class nameat the beginning tell the C# Compiler to allocate a memory location for thattype (C# compiler knows all the variables and properties and methods of theclass so it will allocate the right amount of memory). Then we followed theclass name by out object variable name that we want it. The rest of the code”=new Person();” callthe object’s constructor. We will talk about constructor later but for nowunderstand that the constructor is a way to initialize your object’s variablewhile you are creating it not after you create it. For example, The Michaelobject we created it in the last section can be written as following :
Person Michael = new Person(20, “Brown”);
here I specified the variable’s values in the parameter listso I initialized the variables while I’m creating the object. But for this codeto work we will need to specify the constructor in the Person class and I willnot do that here because constructor section will come in later articles. Ithink you got a good introduction about Classes and Objects not I will completein in my next article and I will talk about constructors and building blockscoping. I hope you got a new thing from my article.