Quickly Revise PL/SQL used in SQL Server 2000/2005/2008

RAM RAM Friends

Quickly Revise PL/SQL used in SQL Server 2000/2005/2008

Following are the some of the important categories out of many for PL/SQL of SQL Server 2000/2005/2008

• some important(frequently used) Built-In Functions
• some important(frequently used) Built-in Stored Procedures
• Stored procedures
• Functions (UDF) (User Defined Functions) & their types
• Triggers & their types
• Views

Note:>>>> Below I have just mentioned their use & very less description as this is only for quickly revise

create proc InsertTable1
@name nvarchar(50)=null,
@image image=null,
@myimage2 image=null
as
begin
INSERT INTO Table1(name,image,myimage2) VALUES(@name,@image,@myimage2)
end

drop proc updatetable1

create proc UpdateTable1
@name nvarchar(50),
@image image,
@myimage2 image,
@id int
as
begin
if(@name is not null and @image is not null)
begin
UPDATE Table1
SET name=@name,
image=@image
end
end

USE arundb1
--==================
CREATE TABLE Countries
(
CountryId INT IDENTITY PRIMARY KEY,
CountryName NVARCHAR(50) UNIQUE
)

CREATE TABLE States
(
StateId INT IDENTITY PRIMARY KEY,
StateName NVARCHAR(50) NOT NULL,
CountryId INT FOREIGN KEY REFERENCES Countries(CountryId)
)

CREATE TABLE Cities
(
CityId INT IDENTITY PRIMARY KEY,
CityName NVARCHAR(50) NOT NULL,
StateId INT FOREIGN KEY REFERENCES States(StateId)
)

INSERT INTO Countries(CountryName)
SELECT 'Country1'
UNION ALL
SELECT 'Country2'

CREATE PROC FillCountries
as
begin
DECLARE @CountryNo INT
DECLARE @cmd NVARCHAR(200)

SELECT @CountryNo=1
WHILE(@CountryNo<5)
begin
SELECT @cmd='INSERT INTO Countries(CountryName) Values(''Cnt' + CONVERT(VARCHAR,@CountryNo) + ''')'
EXEC(@cmd)
SELECT @CountryNo=@CountryNo + 1
end
end


CREATE PROC FillStates
as
begin
DECLARE @StateNo INT
DECLARE @cmd NVARCHAR(200)
DECLARE @CountryId INT

DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries

OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId


WHILE(@@FETCH_STATUS=0)  --CountryId
begin
SELECT @StateNo=1
WHILE(@StateNo<5) --insert 4 States
begin
SELECT @cmd='INSERT INTO States(CountryId,StateName) VALUES('+ CONVERT(VARCHAR,@CountryId) + ',''Cnt' + CONVERT(VARCHAR,@CountryId) + '_Stat' + CONVERT(VARCHAR,@StateNo) + ''')'
EXEC(@cmd)
SELECT @StateNo = @StateNo + 1
end

FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
end


Create PROC FillCities
as
Begin
DECLARE @CountryId INT
DECLARE @StateId2 INT
DECLARE @CityNo INT
DECLARE @StateNo INT
DECLARE @cmd nvarchar(200)


DECLARE crsCountries CURSOR
FOR
SELECT CountryId FROM Countries
OPEN crsCountries
FETCH NEXT FROM crsCountries INTO @CountryId

WHILE(@@FETCH_STATUS=0)  --Countryid
begin
DECLARE  crsStates CURSOR
FOR
SELECT StateId FROM States
WHERE CountryId=@CountryId
OPEN crsStates
FETCH NEXT FROM crsStates INTO @StateId2

SELECT @StateNo=1
WHILE(@@FETCH_STATUS=0) -- StateId
begin
SELECT @CityNo=1

WHILE(@CityNo<5)--insert 4 Cities
begin
SELECT @cmd='INSERT INTO Cities(StateId,CityName) VALUES(' + CONVERT(VARCHAR,@StateId2) + ',''Cont' + CONVERT(VARCHAR,@CountryId) + '_St' + CONVERT(VARCHAR,@StateNo) + '_City_' + CONVERT(VARCHAR,@CityNo) + ''')'
print @cmd
exec(@cmd)
SELECT @CityNo=@CityNo +1
end

FETCH NEXT FROM crsStates INTO @StateId2
SELECT @StateNo=@StateNo+1
if(@StateNo>4)
SELECT @StateNo=1

end
close crsStates
DEALLOCATE crsStates
FETCH NEXT FROM crsCountries INTO @CountryId
end
CLOSE crsCountries
DEALLOCATE crsCountries
End

SELECT * FROM Countries
SELECT * FROM States
SELECT * FROM Cities
exec fillCountries
EXEC FillStates
EXEC FillCities
DROP PROC FillCountries
DROP PROC FillStates
DROP PROC FillCities
CLOSE crsCountries
DEALLOCATE crsCountries
DEALLOCATE @StateId
DELETE Cities
INSERT INTO Cities(CityName,StateId) VALUES('ArunCity',17)


sp_helptext usp_AddUpdateTaxes
'

• ' Alter PROCEDURE [DBO].[USP_ADDUPDATETAXES]
• '(
• '  @TAXID  INT
• ' ,@TAXTYPE NVARCHAR(15)
• ' ,@TAXNAME NVARCHAR(MAX)
• ' ,@TAXVALUE NUMERIC
• ' ,@CUSTID INT
• ' ,@MINVAL NUMERIC
• ' ,@DESCR  NVARCHAR(300)
• ' ,@UNIT  NVARCHAR(20)
• ' ,@CURRENCY NVARCHAR(50)
• ' ,@TaxTypeHandling nvarchar(300)
• ')
• 'AS
• 'BEGIN
• ' SET NOCOUNT ON;
• '
• ' BEGIN TRAN
• '  IF @TAXID=0
• '   INSERT INTO TBL_TAXCHARGES(TAXTYPE,TAXNAME,TAXVALUE,CUSTID,MINVAL,DESCR,UNIT,CURRENCY,TaxTypeHandling) VALUES (@TAXTYPE,@TAXNAME,@TAXVALUE,@CUSTID,@MINVAL,@DESCR,@UNIT,@CURRENCY,@TaxTypeHandling)
• '  Else
• '   UPDATE TBL_TAXCHARGES SET TAXTYPE=@TAXTYPE,TAXNAME=@TAXNAME,TAXVALUE=@TAXVALUE,CUSTID=@CUSTID,MINVAL=@MINVAL,DESCR=@DESCR,UNIT=@UNIT,CURRENCY=@CURRENCY,TaxTypeHandling=@TaxTypeHandling WHERE TAXID=@TAXID
• 
  
• '  -- SET @p_CompanyID_out = SCOPE_IDENTITY()
• '
• ' IF @@ERROR<>0
• '  ROLLBACK TRAN
• ' Else
• '  COMMIT TRAN
• '
• 'End

CREATE TABLE AddressBook
(
AddressId INT IDENTITY PRIMARY KEY,
MyName nvarchar(250) null,
ContactNo nvarchar(50) null,
Address nvarchar(300) null,
Photo image null
)

select * from AddressBook

CREATE PROCEDURE spInsertAddressBook
@Id int output,
@name nvarchar(250)=null,
@ContactNo nvarchar(50) =null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@name,@ContactNo, @Address,@Photo);
SELECT @Id=SCOPE_IDENTITY()
END

ALTER PROC spInsertAddressBook
@Id int Output,
@MyName nvarchar(250)=null,
@ContactNo nvarchar(50)=null,
@Address nvarchar(300)=null,
@Photo image=null
AS
BEGIN
INSERT INTO AddressBook(MyName,ContactNo,Address,Photo) VALUES(@MyName,@ContactNo,@Address,@Photo);
SELECT @Id=SCOPE_IDENTITY();
END

declare @myid int
EXEC spInsertaddressbook @myid output,'abc', 'a', 'a'
select @myid

--below is named paramerised sp calling
declare @myid int
exec spinsertaddressbook @id=@myid output,@myname='a',@contactno='aa'
select @myid
select * from AddressBook

--   Attaching a DB
use master ; EXEC sp_attach_db @dbname = 'ArunDB1',
  @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1.mdf',
  @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\ArunDB1_log.ldf'

create table myTable1
(
myId int Identity Primary Key,
FName nvarchar(200) null,
LName nvarchar(200) null,
Age numeric(3) null
)
insert into myTable1
select 'F_Ram1', 'L_Ram1',20
union all
select 'F_Ram2', 'L_Ram2', 21
union all
select 'F_Ram3', 'L_Ram3', 22
union all
select 'F_Ram4','L_Ram4',23

select * from mytable1

Select * from SysDataBases
select * from SysObjects
--P  = SP
--FN = Scalar-valued UDF
--IF = Inline-Table Valued UDF
--TF = Multi-Statement Table-Valued UDF
--TR = Trigger
--V  = View
--U == User Defined Tables

GO
if object_id('mysp1','P') is not null --mostly used now a days  also working in SQL 2K but not at all places
--if exists(select name from sysobjects where name='mysp1' and type='P') --mostly used in SQL 2k but still working in SQL 2K5
drop proc mysp1
GO  --needed since CREATE/ALTER must be first stmnt in batch query
create proc mysp1
@myFName nvarchar(200)='F%',
@myLName nvarchar(200)='L%'
as
begin
select * from mytable1 where FName like @myFName and LName like @myLName
end

exec mysp1
exec mysp1 '%','%'
exec mysp1 'F','L'
exec mysp1 'F%2'

--UDF User Defined functions---------------------
--1. Scalar valued UDF---------
Go
If object_id('GetMyAgeInYrs','FN') is not null
drop function GetMyAgeInYrs
Go
Create Function GetMyAgeInYrs (@myDOB datetime='07Apr1981', @CurrDt datetime='02May2011')
Returns int
as
begin
declare @myAge int
set @myAge=datediff(year,@myDob,@currdt)
return(@myage)
end

select GetMyAgeInYrs(default,default) as myAge --It shows error
--Scalar function name must be prefixed with either DBName.DBO.myFuntion1 or DBO.myFunction1
-- DBO is the owner name of db
select ArunDB1.DBO.GetMyAgeInYrs('07Apr1981',getdate()) as myAge
select dbo.getmyageinyrs(default,default) as myAgeInYrs

create function myNameWithAge (@myAge int=20)
returns nvarchar(200)
as
begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s age is ' + convert(nvarchar(200),@myage) --If we not use N we got error
return(@myname)
end

select DBO.myNameWithAge(default) as Msg
select ArunDB1.DBO.myNameWithAge(30) as Msg

Alter Function myNameWithAge(@myAge int=30)
returns nvarchar(200)
as
Begin
declare @myName nvarchar(200)
set @myName=N'Arun Kakkar''s Age is=' + convert(nvarchar(200),@myAge)
return (@myName)
end

select DBO.myNameWithAge(29) as Msg

--2. Inline Table valued UDF--------------
Go
if exists(select name from sysobjects where name='myGetFName' and type='IF')
--if object_id('myGetFName','IF') is not null --working fine
drop function myGetFName
Go
create function myGetFName(@myFName nvarchar(200)='F%')
returns table
as
return(select * from myTable1 where FName like @myFName)

--in Select Query, Inline function acts as any table
select * from dbo.myGetFName(default)
select * from dbo.myGetFName('F%1')
select * from mytable1 where fname like 'F%1'

--3. Mulit-Statement Table-Valued UDF ------------------
Go
--if exists(select name from sysobjects where name='myGetFNameWithAge' and type='TF')
if object_Id('myGetFNameWithAge','TF') is not null
drop function myGetFNameWithAge
Go
Create Function myGetFNameWithAge(@myAgeAfterYrs int=20)
returns @myTable table
(
mySNo int Identity primary key ,
FName nvarchar(200) null,
NewAge numeric(4) null
)
as
Begin
insert into @myTable
select Fname,age from myTable1

update @myTable set NewAge= case
when NewAge>=20 and newAge<22 then
@myAgeAFterYrs
else
newAge
end
--return (@myTable)--It gives error
return
End

select * from dbo.myGetFNameWithAge(default)
select * from dbo.myGetFNameWithAge(29)

--***** Begin ****** Triggers **************************
Triggers
1. INSTEAD OF>> INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.
2. AFTER >>AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables.

Function AFTER trigger INSTEAD OF trigger
Applicability Tables Tables and views
Quantity per table or view Multiple per triggering action (UPDATE, DELETE, and INSERT) One per triggering action (UPDATE, DELETE, and INSERT)
Cascading references No restrictions apply Are not allowed on tables that are targets of cascaded referential integrity constraints.
Execution After:
• Constraint processing
• Declarative referential actions
• inserted and deleted tables creation
• The triggering action Before:
• Constraint processing
In place of:
• The triggering action
After:
• inserted and deleted tables creation
Order of execution First and last execution may be specified Not applicable
text, ntext, and image column references in inserted and deleted tables Not allowed Allowed


1. inserted and deleted Tables>> Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
a. The inserted and deleted tables are used primarily in triggers to:
i. Extend referential integrity between tables.
ii. Insert or update data in base tables underlying a view.
iii. Check for errors and take action based on the error.
iv. Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
b. The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
c. The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
d. An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
e. Each column in the inserted and deleted tables maps directly to a column in the base table.
f. The format of the inserted and deleted tables passed to an INSTEAD OF trigger defined on a view matches the select list of the SELECT statement defined for the view. For example:
i. CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
ii. AS
iii. SELECT EmployeeID, LastName, FirstName
iv. FROM Northwind.dbo.Employees
3. Syntax of Trigger creation
a. CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}
--------------------
4. E.g 1 Use a trigger with a reminder message
a. USE pubs
b. --IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL –USED IN SQL Server 2005 onward
c. IF EXISTS (SELECT name FROM sysobjects
d. WHERE name = 'reminder' AND type = 'TR')
e. DROP TRIGGER reminder
f. GO
g. CREATE TRIGGER reminder
h. ON titles
i. FOR INSERT, UPDATE
j. AS RAISERROR (‘you are trying to do some unauthorized work’, 16, 10)
k. GO
5. E.g 2 Use a trigger with a reminder e-mail message
a. CREATE TRIGGER reminder
b. ON titles
c. FOR INSERT, UPDATE, DELETE
d. AS
e. EXEC master..xp_sendmail 'MaryM',
f. 'Don''t forget to print a report for the distributors.'
g. GO
6. E.g 3 Instead of trigger with view
a. CREATE TABLE BaseTable
b. (PrimaryKey     int IDENTITY(1,1)
c. Color          nvarchar(10) NOT NULL,
d. Material       nvarchar(10) NOT NULL,
e. ComputedCol AS (Color + Material)
f. )
g. GO

h. --Create a view that contains all columns from the base table.
i. CREATE VIEW InsteadView
j. AS SELECT PrimaryKey, Color, Material, ComputedCol
k. FROM BaseTable
l. GO

m. --Create an INSTEAD OF INSERT trigger on the view.
n. CREATE TRIGGER InsteadTrigger on InsteadView
o. INSTEAD OF INSERT
p. AS
q. BEGIN
r. --Build an INSERT statement ignoring inserted.PrimaryKey and
s. --inserted.ComputedCol.
t. INSERT INTO BaseTable
u. SELECT Color, Material
v. FROM inserted
w. END
x. GO

y. --A correct INSERT statement supplying dummy values for the
z. --PrimaryKey and ComputedCol columns.
aa. INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
bb. VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
cc. --View the results of the INSERT statement.
dd. SELECT PrimaryKey, Color, Material, ComputedCol
ee. FROM InsteadView
7. E.g 4 used in SQL Server 2005
a. USE AdventureWorks2008R2;
b. GO
c. IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
d. DROP TRIGGER Purchasing.LowCredit;
e. GO
f. This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
g. when the credit rating of the specified vendor is set to 5 (below average).

h. CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
i. AFTER INSERT
j. AS
k. DECLARE @creditrating tinyint, @vendorid int;
l. IF EXISTS (SELECT *
m. FROM Purchasing.PurchaseOrderHeader p
n. JOIN inserted AS i
o. ON p.PurchaseOrderID = i.PurchaseOrderID
p. JOIN Purchasing.Vendor AS v
q. ON v.BusinessEntityID = p.VendorID
r. WHERE v.CreditRating = 5
s. )
t. BEGIN
u. RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
v. ROLLBACK TRANSACTION;
w. RETURN
x. END;
y. GO
8. E.g 5 Using a database-scoped DDL trigger
a. USE AdventureWorks2008R2;
b. GO
c. IF EXISTS (SELECT * FROM sys.triggers
d. WHERE parent_class = 0 AND name = 'safety')
e. DROP TRIGGER safety
f. ON DATABASE;
g. GO
h. CREATE TRIGGER safety
i. ON DATABASE
j. FOR DROP_SYNONYM
k. AS
l. RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
m. ROLLBACK
n. GO
o. DROP TRIGGER safety
p. ON DATABASE;
q. GO
9. e.g. 6. example of a single SQL Server trigger that handles all (3) three conditions of the trigger actions INSERT, UPDATE, and DELETE. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. UPDATE trigger.  In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem.
a. CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with caller queries SELECT statements.
    -- If an update/insert/delete occurs on the main table, the number of records affected
    -- should only be based on that table and not what records the triggers may/may not
    -- select.
    SET NOCOUNT ON;

    --
    -- Variables Needed for this Trigger
    --
    DECLARE @PACKLIST_ID varchar(15)
    DECLARE @LINE_NO smallint
    DECLARE @SHIPPED_QTY decimal(14,4)
    DECLARE @CUST_ORDER_ID varchar(15)
    --
    -- Determine if this is an INSERT,UPDATE, or DELETE Action
    --
    DECLARE @Action as char(1)
    DECLARE @Count as int
    SET @Action = 'I' -- Set Action to 'I'nsert by default.
    SELECT @Count = COUNT(*) FROM DELETED
    if @Count > 0
        BEGIN
            SET @Action = 'D' -- Set Action to 'D'eleted.
            SELECT @Count = COUNT(*) FROM INSERTED
            IF @Count > 0
                SET @Action = 'U' -- Set Action to 'U'pdated.
        END

    if @Action = 'D'
        -- This is a DELETE Record Action
        --
        BEGIN
            SELECT @PACKLIST_ID =[PACKLIST_ID]
                        ,@LINE_NO = [LINE_NO]
            FROM DELETED

            DELETE [dbo].[MyDataTable]
            WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
        END
ELSE
        BEGIN
                --
                -- Table INSERTED is common to both the INSERT, UPDATE trigger
                --
                SELECT @PACKLIST_ID =[PACKLIST_ID]
                    ,@LINE_NO = [LINE_NO]
                    ,@SHIPPED_QTY =[SHIPPED_QTY]
                    ,@CUST_ORDER_ID = [CUST_ORDER_ID]
                FROM INSERTED

             if @Action = 'I'
                -- This is an Insert Record Action
                --
                BEGIN
                    INSERT INTO [MyChildTable]
                        (([PACKLIST_ID]
                        ,[LINE_NO]
                        ,[STATUS]
                    VALUES
                        (@PACKLIST_ID
                        ,@LINE_NO
                        ,'New Record'
                        )
                END
            else
                -- This is an Update Record Action
                --
                BEGIN
                    UPDATE [MyChildTable]
                        SET [PACKLIST_ID] = @PACKLIST_ID
                              ,[LINE_NO] = @LINE_NO
                              ,[STATUS]='Update Record'
                    WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
                END
        END
--***** End ****** Triggers **************************

--*****Begin******** VIEW ********************************************************
--VIEW-->>Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
-----------------------------------------------------------------------------
--To focus, simplify, and customize the perception each user has of the database.
--As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
--To provide a backward compatible interface to emulate a table whose schema has changed.
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ] 
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]
}
--------------
--When a view is created, the name of the view is stored in the sysobjects table. Information about the columns defined in a view is added to the syscolumns table, and information about the view dependencies is added to the sysdepends table. In addition, the text of the CREATE VIEW statement is added to the syscomments table. This is similar to a stored procedure; when a view is executed for the first time, only its query tree is stored in the procedure cache. Each time a view is accessed, its execution plan is recompiled.
----------------------------------------
--E.G 1 CREATING A SIMPLE view
USE AdventureWorks2008R2 ;
GO
--IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS  --used in SQL Server 2000
--      WHERE TABLE_NAME = 'categories')
IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL
DROP VIEW hiredate_view ;
GO
CREATE VIEW hiredate_view
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
GO
------------------------------
--E.G 2 Using built-in functions within a view
USE AdventureWorks2008R2 ;
GO
IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL
    DROP VIEW Sales.SalesPersonPerform ;
GO
CREATE VIEW Sales.SalesPersonPerform
AS
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)
GROUP BY SalesPersonID;
GO
---------------------------
--E.G. 3  Use @@ROWCOUNT function in a view
--This example uses the @@ROWCOUNT function as part of the view definition.
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'myview')
   DROP VIEW myview
GO
CREATE VIEW myview
AS
   SELECT au_lname, au_fname, @@ROWCOUNT AS bar
   FROM authors
   WHERE state = 'UT'
GO
SELECT * FROM myview
---------------------------------------------
--***** END ******** VIEW ********************************************************

--**************** Introduction to Transaction Logs  ***********************************
--Every SQL Server 2005 database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.
--The transaction log supports the following operations:
-------Recovery of individual transactions.
-------Recovery of all incomplete transactions when SQL Server is started.
-------Rolling a restored database, file, filegroup, or page forward to the point of failure.
-------Supporting transactional replication.
-------Supporting standby-server solutions.
--------------------------------------------------------------------

select * from myTable1
--Inserted>>Insert, Update
--Deleted>> Delete, Update
Go
--If exists(select Name from sysobjects where name='myTable1_AfterInsertTrig' and type='TR')
if object_Id('myTable1_AfterInsertTrig','TR') is not null
drop trigger myTable1_AfterInsertTrig
Go
Create Trigger myTable1_AfterInsertTrig
on myTable1
After Insert
as
Begin
Declare @FName nvarchar(200)
Declare @LName nvarchar(200)
Select @FName=FName, @LName=LName from Inserted
Set @FName=@FName + '_InsertTrigger'
insert into myTable1(Fname,Lname) values(@FName,@Lname)
End

insert into myTable1(FName,LName,age) values('Ram','Ram',30)
select * from myTable1

Go
if object_id('myTable1_InsteadOfDelTrig','TR') is not null
--if exists(select name from sysobjects where name='myTable1_InsteadOfDelTrig' and type='TR')
drop trigger myTable1_InsteadOfDelTrig
Go
Create Trigger myTable1_InsteadOfDelTrig
on myTable1
Instead of Delete
as
Begin
Declare @myFName nvarchar(200)
Declare @myLName nvarchar(200)
select @myFName=Fname,@myLName=LName from Deleted
set @myFName=@myFName + '_InsteadOfDelTrigger'
Insert into myTable1(FName,LName) values (@myFName,@myLName)
end

delete from myTable1 where myId=2
select * from myTable1

-- View ------------------
Go
If exists(select name from sysobjects where name='myTable1_View' and type='V')
--if object_id('myTable1_View','V') is not null
drop view myTable1_View
Go
create view myTable1_View
As
select Fname,age from myTable1
---------------
select * from myTable1_View
insert into myTable1_View values('Ram_View',30)
select * from myTable1
--triggeres set for base table also work on views
--insert stmnt apply on view also apply on base table
-------** Begin *************  Try Catch ****************************
-- Note:  Try catch mentioned below work in SQL Server 2005
Go
if exists(select name from sysobjects where name='mySP_ErrorHandler' and type='P')
--if object_Id('mySP_ErrorHandler','P') is not null
Drop proc mySP_ErrorHandler
Go
create proc mySP_ErrorHandler
@myNumerator int=1, @myDenominator int=0
As
Begin
Begin try
--print @myNumerator/@myDenominator
select @myNumerator/@myDenominator as myDivisionResult
End try
Begin Catch
select Error_Number() as ErrorNumber,
  Error_Severity() as ErrorSeverity,
  Error_State() as ErrorState,
  Error_Procedure() as ErrorProcedure,
  Error_Line() as ErrorLine,
  Error_Message() as ErrorMessage
End Catch
End

exec mySP_ErrorHandler
exec mySP_ErrorHandler 5,2
exec mySP_ErrorHandler 5,0

Due to lack of time & of course some laziness, I would not able to complete this post in proper manner.
But as soon as I got opportunity, I would update this post as I think it would be very necessary to update this post to make it more understandable, readable etc.

Wish you a very Happy Programming.

How to Revise Basic OOPs concepts in C Sharp.Net (Summary only)

RAM RAM Friends

How to Revise Basic OOPs concepts in C Sharp.Net (only syntax and concepts but very less or no description) ) like (types of Constructors,Inheritance,Polymorphism,Abstract Class,Interface,Delegate,Events,Sealed Classes,Overriding, etc)

Here I try to explain different OOPs concepts in C#.NET using examples only with very less description as it is only for revision purpose (both syntax & concept).

Below I have shown my sample console program to use different OOPs concepts

*********ArunKakkarConsoleApp1************************


using System;
using System.Collections.Generic;
using System.Text;

using System.Text.RegularExpressions;

//Using Events Method 1 (a) -->>by Inheriting ArrayList
//below namespace is used form Event learning in c#.Net
namespace myListNamespace
{
    using System.Collections;

    //step 1. delegate type for hooking up change (event)  notification
    public delegate void myDelChangedEventHandler(object sender,EventArgs e);

    public class myListClass : ArrayList
    {
        //step 2. declare event that clients can be used to notified
        //--------whenever the event raised (here elements of list change)
        public event myDelChangedEventHandler myEventChanged;

        //step 3. Raise Event (here whenever elements of list changes)
        protected virtual void myEventRaisedOnChanged(EventArgs e)
        {
            if (myEventChanged != null)
                myEventChanged(this, e);      
        }

        //step 4. create such methods etc which may raise the event
        //below are methods(add,clear,this) which r override as they may change the list
        //& in each method event is invoked
        public override int Add(object value)
        {
            int i= base.Add(value);
            myEventRaisedOnChanged(EventArgs.Empty);
            return i;
        }
        public override void Clear()
        {
            base.Clear();
            myEventRaisedOnChanged(EventArgs.Empty);
        }
        public override object this[int index]
        {
            get
            {
               return base[index];
            }
            set
            {
                base[index] = value;
                myEventRaisedOnChanged(EventArgs.Empty);
            }
        }
    }
}
namespace ArunKakkarConsoleApp1
{
    //below namespace is used to learn Events in C#.Net
    using myListNamespace;

    //Different Types of Constructor
    public class InstanceConstructor
    {
        private int x, y;
        public InstanceConstructor()
        {
            x = 0;
            y = 0;
            Console.WriteLine("in Parameterless contructor,x={0}, y={1}", x, y);
        }
        public InstanceConstructor(int i)
        {
            x = i;
            y = 0;
            Console.WriteLine("In single parameter constructor, x={0}, y={1}", x, y);
        }
        public InstanceConstructor(int i, int j)
        {
            this.x = i;
            this.y = j;
            Console.WriteLine("In double parameter constructor, x={0}, y={1}", x, y);
        }
        ~InstanceConstructor()
        {
            Console.WriteLine("Destructor of class InstanceConstructor is called");
            Console.ReadLine();
        }
    }
    public class myDefaultConstructorClass
    {
        private int _x;
        private string _s;
        public int x
        {
            get
            {
                return _x;
            }
            set
            {
                _x = value;
            }      
        }
        public string s
        {
            get
            {
                return _s;
            }
            set
            {
                _s = value;
            }
        }
        public void myShow()
        {
            //if (_x==null )
            //    Console.WriteLine("X is null");
            //if(_x != null )
                Console.WriteLine("x is not null ,x={0}",_x);          
        }
    }
    public class myPrivateConstructor
    {
        public const double pi = 3.14;
        private myPrivateConstructor()
        {
        }
    }
    public class myStaticConstructorClass
    {
        //Static constructor :
        //Static Constructors are used to instantiate static data fields in the class. Static Constructors are called only once no matter how many instances you create for that class.
        //Static Constructors are executed before any other type of constructor. Static Constructors cannot be called explicitly. They are automatically invoked when
            //An instance of the class is created
            //Any of the static members of the class are referenced
        //It is a compile time error to have access modifiers for static constructors.

        public int j;
        public static int i;

        public myStaticConstructorClass()
        {
            j = 25;
            Console.WriteLine("\n Normal Instance constructor of myStaticConstructorClass is invoked");
        }
     
        static myStaticConstructorClass()
        {
            i = 20;
            Console.WriteLine("\n static constructor of myStaticConstructorClass is invoked");
        }
    }
 
    //Static Class
    public static class myStaticClass1
    {
        public static int myVal;
        //we can declare only static constructor in a static class
        //public myStaticClass1()
     
        //we can not use public etc modifire on the static class constructor
        //public static myStaticClass1()

        //not mandatory to declare constructor for a static class i.e. we can use class without any constructor
        static myStaticClass1()
        {
            myVal = 30;
        }
        public static void myShow()
        {
            Console.WriteLine("\n Hello it is myShow() method of myStaticClass1");
        }
        //we can declare only static members in a static class
        //public void myShow2()
        //{  Console.WriteLine("\nHello it is myShow2() method of myStaticClass1");        }
    }
 
    //Polymorphism & Inheritance (Virtual->Override,  Virtual->Override->Sealed, on creating instance of child class,sequence of constructor invoked 1. GrandParent 2.Parent 3.Child)
    public class myParent
    {
        private string s;
        //protected myParent()
        public myParent()
        {
            s=@"This is a string from parent class have both \ and & signs";
            Console.WriteLine("This is myParent class constructor");
        }

        public string ParseBackSlash()
        {
            string s1;
            s1=s;
            s1=s1.Replace("\\",string.Empty);
            return s1;
        }
        public virtual void ShowMsg()
        {
            Console.WriteLine("This is showmsg from myParent class");      
        }
        public virtual void myShowMsg5()
        {
            Console.WriteLine("this is myShowMsg5 method of myParent class");
        }
    }
    public class myChild1 : myParent
    {
        public string ParseAmpersand()
        {
            string s2;
            s2 = base.ParseBackSlash();
            s2 = s2.Replace("&", string.Empty);
            return s2;
        }
        public override void ShowMsg()
        {
            Console.WriteLine("This is showmsg from myChild1 class");      
        }
        public sealed override void myShowMsg5()
        {
           //base.myShowMsg5();
            Console.WriteLine("This is override sealed method myShowMsg5 which is originally of myParent class");

        }
    }
    public class myChild2 : myParent
    {
        public myChild2()
        {
            Console.WriteLine("This is myChild2 class constructor");      
        }

        public override void ShowMsg()
        {
             //base.ShowMsg();
            Console.WriteLine("this is ShowMsg method of myChild2 class");
        }

        public void myShowAns()
        {
            Console.WriteLine("This is myShowAns method of myChild2 class");
        }
    }
    public class myGrandChild1 : myChild2
    {
        public myGrandChild1()
        {
            Console.WriteLine("This is constructor of myGrandChild1 class, its parent class is myChild2, its grandParentClass is myParent");
        }  
    }
 
    //Interface handling
    public interface myInterface_1
    {
        //i/f can't contain fields
        //protected int x;
        string id
        {
            get;
            set;
        }
     
        void myShowResult();

        //if we declare this here & do not implement it & use i/f then show compile time error
        //void myWish();  
    }
    interface myInterface_2
    {
        void myShowResult();
    }
    public class myClassUsingInterface1 :myInterface_1
    {
        protected string  x;
        public string id
        {
            get
            {
                return x;
            }
            set
            {
                x = value;
            }
        }
        public myClassUsingInterface1()
        {
            Console.WriteLine("this is constructor of myClassUsingInterface1");
        }
        public void myShowResult()
        {
            Console.WriteLine("This is myshowResult of myInterface_1 implement by myClassUsingInterface1");
        }
    }
    public class myClassUsingInterface2 : myInterface_1, myInterface_2
    {
        string  x;
        public string id
        {
            get
            {
                return x;
            }
            set
            {
                x = value;
            }
        }
        public void myShowResult()
        {
            Console.WriteLine("This is myShowResult method which is common in both myInterface_1 & myInterface_2 implement by myClassUsingInterface2");
        }
    }
    //Explicitly members of interfaces
    public class myClassUsingInterface3 : myInterface_1, myInterface_2
    {
        string y;
        public string id
        {
            get
            {
                return y;
            }
            set
            {
                y = value;
            }      
        }

        //Explicitly members of interfaces>>>> we can't use public etc keywords
        void myInterface_1.myShowResult()
        {
            Console.WriteLine("This is myInterface_1.myShowResult() method called");
        }
        void myInterface_2.myShowResult()
        {
            Console.WriteLine("This is myInterface_2.myShowResult() method called");
        }  
    }
 
    //Abstract Class
    public abstract class myAbstractClass
    {
        public void myShowMessage()
        {
            Console.WriteLine("my showmsg method of myAbstractClass class");      
        }
        public abstract  void myShowMessage2();
    }
    public class myChild3 : myAbstractClass
    {
        public void myShowMessage3()
        {
            base.myShowMessage();
            Console.WriteLine("This is myShowMessage3 method of myChild3 class");
        }
        public override void myShowMessage2()
        {
            //base.myShowMessage2();
            Console.WriteLine("This is from myShowMessage2 override by myChild3 class");
        }  
    }
 
    //Sealed Classes
    public sealed class myChild4 : myChild3
    {
        public int myAddNumbers(int x, int y)
        {
            return x + y;
        }
        public void myShowMsg4()
        {
            Console.WriteLine("this is myShowMsg4 from sealed class myChild4");      
        }

        public void myShowMsg5(int i)
        {
            Console.WriteLine("myShowMsg5 called with i=" + i);
        }
        public void myShowMsg6(int j)
        {
            Console.WriteLine("myShowMsg6 called with j={0}", j);
        }    
    }

    //5. Delegates  & events
    //(publisher [who declare/create event & told all subscriber that a particular event is raised]
    //& subsriber(who write code for event handler))

    //Using Events Method 1 (b) -->>by Inheriting ArrayList
    public class myEventListner
    {
        //step 5. class that listen the events raised by event class
        private myListClass myList;

        //step 6. create method that acts as event handler
        //called whenever list changes
        private void myListChanged(object sender, EventArgs e)
        {
            Console.WriteLine("myListChanged method is called when event(change in list) fired");
        }
        public myEventListner(myListClass myL1)
        {
            myList = myL1;
            //step 7. add events
            myList.myEventChanged += new myDelChangedEventHandler(myListChanged);
        }
        public void myDetach()
        {
            //step 8. Detach the event & delete the list
            myList.myEventChanged -= new myDelChangedEventHandler(myListChanged);
            myList = null;
        }
    }

    //Using Events Method 2 -->> Events as Publisher & Subscriber
    public class myPublisher      
    {
        //using System.Windows.Forms
        public delegate void myDelegate2(object from, EventArgs args);
        public event  myDelegate2  myEvent;
        public void myRaiseMyEvent(EventArgs args)
        {
            myEvent(this, args);
        }

        public void SendTheEvent()
        {
            Console.WriteLine("The event is fired here in SendTheEvent method of publisher class");
            this.myRaiseMyEvent(new EventArgs());      
        }
    }
    public class mySubscriber
    {
        private void myHandleMyEvent(object sender, EventArgs args)
        {
            Console.WriteLine("myEvent is handled in the myHandleMyEvent of the mySubscriber class");
            Console.WriteLine("who is the sender ? " + sender.GetType());
        }
        public mySubscriber()
        {
            myPublisher p1 = new myPublisher();
            p1.myEvent += new myPublisher.myDelegate2(myHandleMyEvent);
            p1.SendTheEvent();
        }
    }

    //Using Events Method 3 --->> Event using built-in event delegate "EventHandler"
    public class myEventPublisherWithDefaultDelegate
    {
        //For events that do not use any additional information, the .NET Framework has already defined an
            //appropriate delegate type: EventHandler.

        //If we not use EventHandler keyword, we need to define a Delegate(say myEvent2Delegate) eqvivalent to EventHanler
        private string _myVal;
        public event EventHandler myEvent2;
        public void myEvent2Raiser(EventArgs e)
        {
            myEvent2(this, e);
        }
        public string myVal
        {
            get
            {
                return _myVal;
            }
            set
            {
                _myVal = value;
                this.myEvent2Raiser(new EventArgs());
            }
        }
    }
    public class myEventSubscriberWithDefaultDelegate
    {
        public void myEvent2Handler(object sender, EventArgs e)
        {
            Console.WriteLine("\nThis is myEvent2Handler called since Event myEvent2 is raised");
            Console.WriteLine("\n It is called by = " + this.GetType());
        }
        public myEventSubscriberWithDefaultDelegate()
        {
            myEventPublisherWithDefaultDelegate p1 = new myEventPublisherWithDefaultDelegate();
            p1.myEvent2 +=new EventHandler(this.myEvent2Handler);
            p1.myVal ="ram Ram";
            p1.myVal +=" Hare Ram";
        }
    }

    class Program

    {

        //Delegate Handling ( Single Cast,  MultiCast )

        public delegate int myDel(int a, int b);

        public delegate void myDel2();

        public delegate void myMultiCastDel(int m);

        static void Main(string[] args)

        {            

            //------------------------ Topics ---------------------------------------------------------------

            //OOPS CONCEPTS

            //1. Types of Constructor (a. Default, b. Instance, c. Private, d. Static) & Destructor

            //2. Types of Classes 

                //a. Static, 

                //b. Abstract {(abstract & override) & abstract methods}, 

                //c. Sealed  (can not be inherited) & sealed override methods

                //complete syntax of a class with all options explained

            //3. Inheritance (Virtual & override), properties, static variables, private,protected,public variables etc

            //4. Polymorphism, Early/Static/CompileTime Binding & Late/Dynamic/Runtime Binding

            //5. Interface & Explicitly declaration

            //6. Delegate (Signle Cast & MultiCast)

            //7. Events

            //-------------------------------            

            //8. Regular Expression

            //9. Number To Words

            //10.Webservice

            //-------------------------------------------------------------------------------------

            //1(a). Type of Constructors-->Instance Constructor

            InstanceConstructor ic1 = new InstanceConstructor();

            InstanceConstructor ic2 = new InstanceConstructor(3);

            InstanceConstructor ic3 = new InstanceConstructor(4,5);

                //Destructor

            ic1 = null;

            GC.Collect(); //Call Garbage collector

            //1(b). Type of Constructors-->Default Constructor

            myDefaultConstructorClass dc1 = new myDefaultConstructorClass();

            Console.WriteLine(" before giving values, x={0},s={0}", dc1.x, dc1.s);

            dc1.myShow();

            dc1.x = 25;

            dc1.s = "Arun Kakkar";

            Console.WriteLine("After giving values, x={0},s={1}", dc1.x, dc1.s);

            dc1.myShow();

            //1(c). Type of Constructors-->Private constructor

            Console.WriteLine("private class value, x=" + myPrivateConstructor.pi);

            Console.ReadLine();

            //1(d). Type of Constructors-->Static Constructor

            Console.WriteLine("mystaticConstructorClass class value, x={0}", myStaticConstructorClass.i);

            Console.ReadLine();

            myStaticConstructorClass scs1=new myStaticConstructorClass();

            Console.WriteLine("\n j={0} and i={1} in myStaticConstructorClass ",scs1.j ,myStaticConstructorClass.i);

            Console.ReadLine();

            //2(a). Types of Classes---> Static Class            

            myStaticClass1.myShow();

            Console.WriteLine("\n MyVal of myStaticClass1 is=" + myStaticClass1.myVal);

            myStaticClass1.myVal = 23;

            Console.WriteLine("\n MyVal after updation of myStaticClass1 is=" + myStaticClass1.myVal);

            //2(b). Types of Classes---> Abstract class 

            myChild3 ch3 = new myChild3();

                //string s1 = "below";

                //s1 = "ch3.myShowMessage=" + ch3.myShowMessage() + "\n";

                //s1 += "ch3.myShowMessage2=" + ch3.myShowMessage2() + "\n";

                //s1 += "ch3.myShowMessage3=" + ch3.myShowMessage3();

            Console.WriteLine("various functions called by myChild3 class of parent abstract class are as below \n");

            ch3.myShowMessage();

            ch3.myShowMessage2();

            ch3.myShowMessage3();

            //2(c). Types of Classes---> Sealed classes 

                //(can not be inherited)

                //A sealed class can not be used as "base class"

                //A sealed class can not be an abstract class.

                //sealing a class means one(class) can not be driven(drived) from it

                // In C# structs are implicitly sealed.

                //In C# methods can't be declared as sealed but when we override a method(in derived class), 

                //we can declared the overriden method as sealed thus we cannot override this method further

                //public virtual void m1();  in any parent class

                //public override sealed void m1() in the child class

            myChild4 ch4 = new myChild4();

            ch4.myShowMsg4();

            //override Sealed method

            myChild1 ch1 = new myChild1();

            ch1.myShowMsg5();

            //3. Inheritance (Virtual & override), properties, static variables, private,protected,public variables etc ***************

                //1. when we create child class instance, then first parent class constructor is called then child class constructor is called

                //    & they (Constructors) are called in order from top to down sequentially.

                //2. "this" keyword show all methods,etc of both current class & all its parent class >>>this.myShowMsg()

                // but "base" keyowrd show methods of only parent class >>>base.myShowMsg()

                //3. if u don't give any modifiers to constructor,variables they are "private" by default

                //4. if both current class & parent class have same name funtion without using "virtual" keyword in the parent, we get compile time error

            myChild1 c1 = new myChild1();

            c1.ShowMsg();

            Console.WriteLine("the parsed string is=" + c1.ParseAmpersand());

            

            myChild2 c2 = new myChild2();

            c2.myShowAns();

            myGrandChild1 mgc1 = new myGrandChild1();

            mgc1.myShowAns();

            mgc1.ShowMsg();

            //4. Polymorphism, Early/Static/CompileTime Binding & Late/Dynamic/Runtime Binding

                //Polymorphism=>Through inheritance, a class can be used as more than one type;

                // it can be used as its own type, any base type, or any interface type if it 

                //implements interface. This is called Polymorphism.

            myParent myP1 = new myParent();

            myChild1 myCh1 = new myChild1();

            myChild2 myCh2 = new myChild2();

                //Early/Static/CompileTime binding

            Console.WriteLine("\n Below are Early Binding ShowMsg calls from myparent,child1,child2 classes");

            myP1.ShowMsg();

            myCh1.ShowMsg();

            myCh2.ShowMsg();

             
            //Late/Dynamic/Runtime binding

            myParent[] myP2 = new myParent[3];

            myP2[0] = new myParent();

            myP2[1] = new myChild1();

            myP2[2] = new myChild2();

            Console.WriteLine("\n Below are Late Binding ShowMsg calls from myparent,child1,child2 classes");

            for (int i = 0; i <= 2; i++)

            {

                myP2[i].ShowMsg();

            }

                    //below can be used instead of above loop

                    //myP2[0].ShowMsg();

                    //myP2[1].ShowMsg();

                    //myP2[2].ShowMsg();

            //5. Interface & Explicitly declaration

                //1. all methods of i/f must be implement by class implement i/f else get compile time error

                //2. inside i/f>>  void myShowMsg(); is enough i.e. no need to be public etc

            myClassUsingInterface1 cui1 = new myClassUsingInterface1();

            cui1.myShowResult();

            cui1.id = "ram ram";

            Console.WriteLine("Value of cui1.id=" + cui1.id);

            myClassUsingInterface2 cui2 = new myClassUsingInterface2();

            cui2.myShowResult();

                //Explicitly members of interfaces

            myClassUsingInterface3 myif3 = new myClassUsingInterface3();

            myif3.id = "ram ram ji from myIf3";

            Console.WriteLine("calling myif3.id=" + myif3.id);

            myInterface_1 myinterface1 = (myInterface_1)myif3;

            myInterface_2 myinterface2 = (myInterface_2)myif3;

            myinterface1.myShowResult();

            myinterface2.myShowResult();

            Console.WriteLine("calling myinterface1.id={0}", myinterface1.id);

            //6. Delegate (Signle Cast & MultiCast)

                //delegate are derived from System.Delegate

                //below line declared above the void main method

                //public delegate int myDel(int a, int b);

            myDel d1=new myDel(ch4.myAddNumbers);

            Console.WriteLine("addition of 20 & 30 using delegates is=" + d1(20, 30));

                //SingleCast delegate

            myDel2 d2 = new myDel2(ch4.myShowMessage);

            Console.WriteLine("void method using delegate is=\n");

            d2();

                //MultiCast Delegate

                //call more than one kind of method in sequence but problem is only same value of parameter is passed in all methods

            Console.WriteLine("Below line show multicast delegate result\n");

            myMultiCastDel d3 = new myMultiCastDel(ch4.myShowMsg5);

            d3 += new myMultiCastDel(ch4.myShowMsg6);

            d3(3);

            //7(a). Events  --->Method 1

                //Event handling using UserDefined EventDelegate

            Console.WriteLine("\nBelow line show delegate and events combination result");

            mySubscriber sub1 = new mySubscriber();

            //7(b). Events  --->Method 2

                //Event Handling using Default (DotNet framework provided EventDelegate named 'EventHandler')

            Console.WriteLine("\nBelow line shows event handling with default in-built EventDelegate named 'EventHandler'");

            myEventSubscriberWithDefaultDelegate mySub1 = new myEventSubscriberWithDefaultDelegate();

            //7(c). Events  --->Method 3

                //create new list to test Events in C#.Net

            myListClass myL = new myListClass();

                //create class that listen event (whenver list item changed) of list 

            myEventListner myE = new myEventListner(myL);

                //Add & remove items from the list

            Console.WriteLine("\nThis line before any event is fired in list class");

            myL.Add("item 1");

            Console.WriteLine("\nThis line after adding item 1 in list class");

            myL.Add("item 2");

            Console.WriteLine("\nThis line after adding item 2 in list class");

            myL.Clear();

            Console.WriteLine("This line after clearing all items in list class");

            myE.myDetach();

            Console.WriteLine("This line after detaching event from the myEventLister class");

            //8. Regular Expression

            Console.WriteLine("Please enter any alphanumeric word only it can include underscore(_) also=");

            string s = Console.ReadLine();

            string r = @"^[\w]+$";

            if (Regex.IsMatch(s, r))

            {

                Console.WriteLine("You have entered an alphanumeric word=" + s);

            }

            else

            {

                Console.WriteLine("Its a wrong word please enter only Alphanumeric word");

            }

            Console.ReadLine();   // just like getch() in C++

         
            //10.Webservice calling --Webservice1    

            localhostAK.ArunKakkarwsCustomers myWS = new localhostAK.ArunKakkarwsCustomers();

            Console.WriteLine("customer name from WebService localhostAK.ArunKakkarwsCustomers is {0}", myWS.GetCustomerName(2));

                //webservice 2

            AKWebService_1.ArunKakkarWebServices akws1 = new AKWebService_1.ArunKakkarWebServices();

            Console.WriteLine("Addition of {0} and {1} from WebService AKWebService_1.ArunKakkarWebServices is {2}",5,9, akws1.Add2IntNo(5,9));

            //globalweather_NetWS   webservice 3 download from net with link http://www.webservicex.com/globalweather.asmx?WSDL

            string myCountry;

            GlobalWeather_NetWS.GlobalWeather gw = new GlobalWeather_NetWS.GlobalWeather();

            Console.WriteLine("\nEnter country name to get list of its all cities");

            myCountry = Console.ReadLine();

            //it return a dataset in xml format

            Console.WriteLine("\n Various cities in {0} are {1}", myCountry, gw.GetCitiesByCountry(myCountry));

            Console.ReadLine();

        }

    }

}

I hope, it would help those who want to just quickly revise OOPs concepts (both syntax & use).

Due to lack of time, I did not provide, description of various terms.

Wish you best of luck for good programming.