Monday, November 7, 2016

How to configure Website in IIS?

Webserver can be defined as a program that serves webpages of the
server to the client's browser through HTTP (Hypertext Transfer Protocol).

Computer that has Webserver program is also referred as a Webserver.
Any Computer can be used as a Webserver by installing server software and
is connected to the internet. It will have a fixed IP address and a domain name.
Apache,IIS and Nginx are the commonly used webservers.

IIS (Internet Information Services) is the webserver developed by Microsoft
which is an integral part of the Windows NT family Operating System (since
Windows NT 4.0). Earlier it was called Internet Information Server.

Installing IIS in Windows 7 :


Below are the steps to install IIS in Windows 7.

- Go to Control Panel - Programs and Features
- In the left Panel click Turn Windows features on or off
- Select Internet Information Services
- Under Internet Information Services select Web Management Tools
and World Wide Web Services. 
- Under World Wide Web Services select Application Development Features.
 and make sure default selected features like ASP.NET, .NET Extensibility,
ISAPI Extensions and ISAPI Filters are selected.

There are many sub features under the features.
Some are by default selected and we can select any additional feature if required.

Then click OK to start installation. Once installation is finished we can check
the default website by taking the url http://localhost in web browser. Then it
will display IIS 7 web page.

Next we can open IIS Manager which exists in Control Panel -> Administrative Tools.
It can also be opened from Start-> Type inetmgr.

Now IIS is ready to configure Websites. Next I will explain the steps to configure a website
in IIS.

-  Open IIS Manager

- You can see one Default Web Site under the Sites node. If we type http://localhost
or http://127.0.0.1 it is this default web site running which is pointing to the wwwroot,
default root folder of the IIS Web Server which resides inside c:/inetpub.

-  Right click the node Sites -> Add Web Site...then a window will appear as following :



















Site name can be filled with any identifiable name for your website.

Physical path has to be provided by browsing to the exact location of the website
in the local computer.

Under Binding, select default Type as http and You can provide IP address if
you have any. Otherwise it will work under default IP address 127.0.0.1 (localhost).
Default Port is 80 and this should not be changed unless your ISP blocks port 80.

In place of Host name, you can provide your Domain Name if you have any.
Otherwise leave it blank to use IP address

Then click OK and the website is ready to browse. We can test it by right clicking
on the test website -> Manage Website -> Browse.

Even though the website works fine we can add some extra configurations to
ensure it works properly.

For providing security permissions, right click on the created website ->
Edit Permissions -> Select Security tab.

For editing bindings, right click on the created website -> Edit Bindings
Then a window will open like the below :
















Here we can add more bindings like if suppose the website's domain name is
www.test.com and is provided while creating the website and in order to make
the website works for test.com, we can add binding without www here.

There are many advanced settings we can see on the right pane of IIS manager.

If we want to set Default document, click on that feature and add the name of the page
to be displayed by default if we open the website or Application. There are many default
documents already set inside IIS like default.htm,default.aspx,index.html,etc. Normally
we use any of these pages and in that case no need to add it.

Next I will explain how to add an Application under a website. Suppose I have created an Application named testApp inside wwwroot folder you can see it under default web site node.
Right click on it -> Click Convert to Application . Then a window will appear as :



















Click Ok. Then its icon will change and now the Application is ready to use.
From the right side we can set advanced settings like Authentication, Default Document, etc.

Application Pool :

Application Pool is used to group set of IIS worker processes with same configuration.
It provides isolation between Applications even if they are running on the same server
there by prevent issue on one Application affecting the remaining Applications in the
server. In IIS we can create any number of Application pools depending on server's
capacity. It provides relaibility, security and availability for the Applications.

There are two pre-defined Application Pools in IIS namely
(1) DefaultAppPool and (2) Classic .NET AppPool

For Application Pool there are two features, .NET Framework Version by which
we can select the version of Framework and Managed pipeline mode which are
of two types, Classic and Integrated.

Classic mode :
This mode is the only mode in IIS 6.0 and below. In this mode process request model
is isolated. IIS pipeline and ASP.NET pipeline are separate. Each ASP.NET request
has to undergo processing steps of IIS which is then forwarded to Aspnet_isapi dll
and the response is then again channelled back through IIS.

Integrated mode:
The latest mode introduced in IIS7 by which IIS and ASP.NET request processing
architecture is integrated to a unified process model.

Application Pool Identity :
Application pool identity is the security feature of IIS which provides privileges
for worker process in accessing resources. The three types of identities are the
following :

(1) NetworkServices :
This is the default identity of Application Pool which has the privileges of
authenticated local user account and can access remote resource using the
machine account.

(2) LocalServices :
LocalServices is a built-in account that has privileges of an authenticated local user
account. It does not have network access permission.

(3) LocalSystem :
LocalSystem is a built-in account with more privileges and it should be avoided for
security purpose. It has administrative privileges on the server and can access both
local and network resources.

Change Application Pool of a Website :
For changing Application Pool of a website right click on the website->
Manage Website -> Advanced Settings -> choose the Application Pool.

Create or Edit Application Pool :
For adding or editing Application Pools, there is Application Pools' node
in the beginning just under the server node of IIS Manager. For changing
features of Application Pool, select the Application Pool and click on
Advanced Settings on the right pane which opens a window from which we
can change the General features like Enable 32- Bit Applications, Rapid Fail
Protection features, Recycling features, etc.

Enable 32 bit Application in IIS 7 is false by default. If we want to run any legacy
32 bit Application on a server that has 64 Bit processor, we have to set it to true.
For this Windows uses WOW64 (Windows 32 on Windows 64) which is an Application
Compatibility Layer.



Monday, October 17, 2016

SQL Server Triggers

Triggers can be defined as a Procedural code that gets executed automatically
upon the execution of events like insertion, updation and deletion of Database Table
or View. Triggers cannot be called or executed directly like Stored Procedures but
instead works automatically on database operations like insert,update and delete.

Suppose there is a case by which we want to save log of a table into a history table
upon insert,update and delete actions, we can create a trigger under the table and
write code inside the trigger to save the records to history table.

There are generally two types of Triggers named as (1) After Triggers or For Triggers
and (2) Instead Of Triggers.

After Triggers :


After Triggers are triggers that gets executed only after the operation that fired it
runs successfully. This trigger is not supported for Views.There are three types of 
After Triggers :
1. After Insert Trigger
2. After Update Trigger
3. After Delete Trigger

After Insert Trigger  runs after the execution of the insert operation. If in case
insertion fails, this trigger won't get executed.

After Update Trigger is a trigger that executes after an update operation.

After Delete Trigger is a trigger that executes after a delete operation.

I will explain with examples below. Suppose there is a Product table and a Product_Log
table as the following :

CREATE TABLE [dbo].[Product](
      [ProductID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Price] [decimal](18, 2) NULL,
      [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
      [ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

Note : ProductID is an auto-increment identity column.
.................................................................................................

CREATE TABLE [dbo].[Product_Log](
      [LogID] [int] IDENTITY(1,1) NOT NULL,
      [ProductID] [int] NULL,
      [Name] [nvarchar](50) NULL,
      [Price] [decimal](18, 2) NULL,
      [Description] [nvarchar](max) NULL,
      [Activity] [char](1) NULL,
      [LogDate] [datetime] NULL,
 CONSTRAINT [PK_Product_Log] PRIMARY KEY CLUSTERED
(
      [LogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Product_Log] ADD  CONSTRAINT [DF_Product_Log_LogDate]  DEFAULT (getdate()) FOR [LogDate]
GO

Note : LogID is an auto-increment identity column. LogDate has default value set 
as getdate().

............................................................................................

Then below is the example for After Insert Trigger created under Product table :

CREATE TRIGGER [dbo].[ProductInsertTrigger]
   ON  [dbo].[Product]
   AFTER INSERT
AS
BEGIN
    declare @id int;
      declare @name varchar(50);
      declare @price decimal(10,2);
      declare @description varchar(MAX);
      declare @activity char(1);
    select @id=i.ProductID from inserted i;    
      select @name=i.Name from inserted i;     
      select @description=i.Description from inserted i;   
      select @price=i.Price from inserted i;
      select @activity='I';
      insert into Product_Log(ProductID,Name,Price,Description,Activity) values
      (@id,@name,@price,@description,@activity)


END

In the above trigger we can see that the inserted value is obtained from the logical
table, inserted.

Suppose I run the following insert query to Product table :

insert into Product(Name,Description,Price) values ('test','test desc',100)

we can see a new row is inserted also in Product_Log table as a result of
the execution of the After Insert Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate
1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360

Next is the example for After Update Trigger created under the above Product
table :

CREATE TRIGGER ProductUpdateTrigger
   ON  [dbo].[Product]
   AFTER UPDATE
AS
BEGIN
    declare @id int;
      declare @name varchar(50);
      declare @price decimal(10,2);
      declare @description varchar(MAX);
      declare @activity char(1);
    select @id=i.ProductID from inserted i;    
      select @name=i.Name from inserted i;     
      select @description=i.Description from inserted i;   
      select @price=i.Price from inserted i;
      select @activity='U';
      insert into Product_Log(ProductID,Name,Price,Description,Activity) values
      (@id,@name,@price,@description,@activity)

END
GO

For After Update trigger also the logical table, inserted is used as there is no logical
table named updated. In order to know that whether a specified column is updated, we
can use the function update(column-name). For eg. if we want to check whether the
price column is updated, we can add the following to the trigger :

if update(Price)
 some conditional query

Suppose I run the following update query to Product table :

Update Product set Name='test2',Price=200 where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of
the execution of the After Update Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate
1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360
2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533

Next is the example for After Delete Trigger created under the above Product
table :

CREATE TRIGGER ProductDeleteTrigger
   ON  [dbo].[Product]
   FOR DELETE
AS
BEGIN
    declare @id int;
      declare @name varchar(50);
      declare @price decimal(10,2);
      declare @description varchar(MAX);
      declare @activity char(1);
    select @id=i.ProductID from deleted i;     
      select @name=i.Name from deleted i;
      select @description=i.Description from deleted i;    
      select @price=i.Price from deleted i;
      select @activity='D';
      insert into Product_Log(ProductID,Name,Price,Description,Activity) values
      (@id,@name,@price,@description,@activity)

END
GO

In the above trigger we can see that the deleted value is obtained from the logical
table, deleted.

Suppose I run the following delete query to Product table :

Delete from Product where ProductID=1

we can see a new row is inserted also in Product_Log table as a result of
the execution of the After Delete Trigger as :

LogID ProductID   Name  Price Description Activity    LogDate
1     1     test  100.00      test desc   I     2016-10-13 16:13:08.360
2     1     test2 200.00      test desc   U     2016-10-14 23:19:22.533
3     1     test2 200.00      test desc   D     2016-10-14 23:29:05.053

Instead Of Triggers :


Instead Of Triggers are triggers that gets executed before the operation that fired it.
In short this triggers are executed instead of operations like insert, update or delete.
This trigger can be created both for tables and views.There are three types of 
Instead Of Triggers :
1. Instead Of Insert Trigger
2. Instead Of Update Trigger
3. Instead Of Delete Trigger

Instead Of  Insert Triggers:

This type of trigger is executed before the insert operation that fired it and insertion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Insert Trigger as the following :

CREATE TRIGGER [dbo].[ProductInsteadOfInsert] ON [dbo].[Product]
INSTEAD OF Insert
AS
BEGIN
DECLARE @Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)
select @Name=i.Name from inserted i
select @Price=i.Price from inserted i
select @description=i.Description from inserted i
 if(@Price<10)
 BEGIN
 RAISERROR('Cannot Insert where price < 10',16,1)
 ROLLBACK
 END
 ELSE
 BEGIN
 Insert into Product (Name,Description,Price) values (@Name,@Description,@Price)
 PRINT 'Instead Of Insert Trigger - Product inserted'
 END

END

In the trigger you can see the condition that if the price is less than 10 it will
raise the error and otherwise insertion will take place.

If I run the insert query as :
insert into Product(Name,Description,Price) values ('test','test desc',8)

It will show the error as:
Cannot Insert where price < 10


If I run the insert query as :
insert into Product(Name,Description,Price) values ('test','test desc',800)

It will insert the data to the table and print  'Instead Of Insert Trigger - Product inserted'

Instead Of  Update Triggers:

This type of trigger is executed before the update operation that fired it and updation
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Update Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfUpdate] ON [dbo].[Product]
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)
select @ID=i.ProductID from inserted i
select @Name=i.Name from inserted i
select @Price=i.Price from inserted i
select @description=i.Description from inserted i
 if(@Price<10)
 BEGIN
 RAISERROR('Cannot update where price < 10',16,1)
 ROLLBACK
 END
 ELSE
 BEGIN
 Update Product set Name=@Name,Description=@Description,Price=@Price where ProductID=@ID
 PRINT 'Instead Of Update Trigger - Product updated'
 END

END

In the trigger you can see the condition that if the price is less than 10 it will
raise the error and otherwise updation will take place.

If I run the update query as :
update Product set Price=where ProductID=6

It will show the error as:
Cannot update where price < 10


If I run the update query as :
update Product set Price=900 where ProductID=6

It will update the corresponding data of the table and print 
'Instead Of Update Trigger - Product updated'

Instead Of  Delete Triggers:

This type of trigger is executed before the delete operation that fired it and deletion
will work only if it is specified inside the trigger. Below I will show one example of
it :

Suppose in the above Product table there is an Instead Of Delete Trigger as the
following :

CREATE TRIGGER [dbo].[ProductInsteadOfDelete] ON [dbo].[Product]
INSTEAD OF Delete
AS
BEGIN
DECLARE @ID int,@Name nvarchar(50), @Price decimal(10,2), @Description nvarchar(max)
select @ID=i.ProductID from deleted i
select @Name=i.Name from deleted i
select @Price=i.Price from deleted i
select @description=i.Description from deleted i
 if(@Price>1000)
 BEGIN
 RAISERROR('Cannot delete where price>1000',16,1)
 ROLLBACK
 END
 ELSE
 BEGIN
 Delete from Product where ProductID=@ID
 PRINT 'Instead Of Delete Trigger - Product deleted'
 END

END

In the trigger you can see the condition that if the price is greater than 1000 it will
raise the error and otherwise deletion will take place. Here logical table named deleted
is used.

Suppose product with productID has price 8000 and if I run the delete query as :
Delete Product where ProductID=5

It will show the error as:
Cannot delete where price >1000


And suppose if I run the delete query for another product with price less than 1000 :
Delete Product where ProductID=6

It will delete the corresponding data of the table and print 
'Instead Of Delete Trigger - Product deleted'

Wednesday, October 12, 2016

How to write an XML file using XmlWriter

XmlWriter :


XmlWriter class writes XML data to a stream or a file in a fast and non-cached way.
Some important methods of XmlWriter are the following :

Create(String) :
This method creates an XmlWriter object using the specified filename.

Create(Stream) :
This method creates an XmlWriter object using the specified stream.

Close() :
This method is used to close the current stream and its underlying stream.

Dispose() :
This method disposes all resources of the XmlWriter object.

Flush() :
This method flushes whatever in the buffer of the current stream to the underlying
stream and also it flushes from the underlying stream.

WriteAttributeString(String,String) :
This method is used to write the attribute with the specified name and value.

WriteComment(String) :
This method writes comment with the specified string inside <!--...-->

WriteElementString(String,String) :
This method writes an element with the specified name and value.

WriteEndAttribute() :
This method closes the previous WriteStartAttribute call.

WriteEndDocument() :
This method closes any open elements or attributes and puts the writer back to
the start state.

WriteEndElement() :
This method closes the current element.

WriteNode(XmlReader,Boolean) :
This method copies everything from the XmlReader to the XmlWriter and moves the
reader to the start of the next sibling.

WriteNode(XPathNavigator,Boolean) :
This method copies everything from the XPathNavigator to the XmlWriter and
XPathNavigator will remain in the same position.

WriteStartAttribute(String) :
This method writes the start of an attribute with the specified name.

WriteStartDocument() :
This method writes the XML declaration with the version "1.0".

WriteStartElement(String) :
This method writes the start tag of the element with the specified local name.

WriteString(String) :
This method writes the specified text content.

WriteValue(String) :
This writes value which is a String.
Like this there are other parameter datatypes for WriteValue like DateTime, Int32,
Int64, Boolean, Decimal, Double, etc.

Next I will show one example for the code to write XML file using XmlWriter on a
Button click.

C# Code :


protected void Button1_Click(object sender, EventArgs e)
{
        XmlWriter xmlWriter = XmlWriter.Create(Server.MapPath("~/employee.xml"));

        xmlWriter.WriteStartDocument();
        xmlWriter.WriteStartElement("employees");

        xmlWriter.WriteStartElement("employee");
        xmlWriter.WriteAttributeString("post", "Manager");
        xmlWriter.WriteString("Anoop");
        xmlWriter.WriteEndElement();

        xmlWriter.WriteStartElement("employee");
        xmlWriter.WriteAttributeString("post", "Programmer");
        xmlWriter.WriteString("Lia");

        xmlWriter.WriteEndDocument();
        xmlWriter.Close();
 }

Output :

This will generate an XML file named employee.xml in the root directory of the
website with the following contents :

<?xml version="1.0" encoding="utf-8"?>
<employees>
<employee post="Manager">
Anoop
</employee>
<employee post="Programmer">
Lia
</employee>
</employees>