Programming in ASP.NET using C#,Javascript,AJAX and MS SQL Server

Wednesday, April 19, 2017

How to restrict accessing a website by IP Address or Domain Name

Suppose in an organisation there is a scenario that they want the website to be
accessed only by certain IP Addresses and wants to restrict others from accessing,
we can use the feature in IIS for that.

In IIS 6 :

Right click website - Properties - Directory Security tab - Grant or

deny access to this resource using IP addresses or Internet domain

names as fig 1

Tuesday, April 18, 2017

Create Report using ReportViewer

Reports can be created using third party Applications like Crystal Report but if we are
using Microsoft's report we will get the flexibility as it avoids deployment issues
regarding versions and license.

In this article I will explain how to create a report using Visual Studio Report(.rdlc)
and display using ReportViewer Control in Local Processing mode.

ReportViewer Control can be used in two processing modes, Local and Remote.

In Local processing mode, report processing takes place in the client Application
where as in Remote processing mode, processing takes place in Reporting Services
Report Server of MS Sql Server 2008 or above.

Display report using ReportViewer Control in Local Processing mode can be used
as an alternative to Crystal Report or other Softwares.

For this DataSets can be used.

I will summarize the steps below:

1. Create one typed Dataset as follows :

Right click in Solution explorer -> Add New Item -> DataSet
It will be created by default in App_Code folder.

In DataSet Designer-> Right click -> Add DataTable.

In DataTable -> Add Column
Define all columns with definite DataType in the DataTable as per the fields
required as shown in the figure below :

2. Create Report Wizard as following :

Right click in Solution explorer -> Add New Item -> Report Wizard- Click Add

-> Click Next -> Choose the created DataSet as the Data Source

-> Click Next -> Choose the report type as tabular

-> Click Next -> Choose the available fields

-> Click Next -> Choose the table layout as stepped

-> Click Next -> Choose the table style

-> Click Next -> Click Finish

Report will be created as the following :

We can design the report as per our need.

If we want to add a header image, we have to do the following :

Click on the top toolbar menu, Report -> Embedded images
New Image -> Add

Drag Image control from the report's toolbox and select in properties window,
source as Embedded and value as the image name from the dropdownlist.

Now the report wizard is ready.

3. Create a function that returns the typed dataset created above and the code for
it in Business Layer and Data Layer Classes is as the following :

App_Code/AgentBLL :

public DataSet1 getAgentReportData()

{

return DAL.getAgentReportData();

}

App_Code/AgentDAL :

public DataSet1 getAgentReportData()

{

string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

DataSet1 ds = new DataSet1();

using (SqlConnection dbConnection = new SqlConnection(connectionString))

using (SqlCommand dbCommand = new SqlCommand())

{

string commandText = "SELECT AGN_ID,AGN_NO,AGN_Name from Agents order by AGN_SEQ desc";

dbCommand.CommandText = commandText;

dbCommand.Connection = dbConnection;

dbConnection.Open();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = dbCommand;

da.Fill(ds, "DataTable1");

dbConnection.Close();

}

return ds;

}

4. Create webform and place Microsoft ReportViewer Control in it. The code for it is :

Report.aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Report.aspx.cs" Inherits="Report" %>

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="882px">

</rsweb:ReportViewer>

</div>

</form>

</body>

</html>

Report.aspx.cs :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Microsoft.Reporting.WebForms;

using System.Data;

public partial class Report : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

ReportViewer1.ProcessingMode = ProcessingMode.Local;

ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");

DataSet1 ds = new DataSet1();

AgentBLL bll = new AgentBLL();

ds = bll.getAgentReportData();

ReportDataSource datasource = new ReportDataSource("DataSet1_DataTable1", ds.Tables[0]);

ReportViewer1.LocalReport.DataSources.Clear();

ReportViewer1.LocalReport.DataSources.Add(datasource);

}

The output will be as the following :

Tips for common Issues :

Inorder to show width and height of the table columns and page of report correctly

while printing and while exporting to pdf we have to follow the rule that

Body.Width <= Report.Width - Report.Margins.Right - Report.Margins.Left.

If we follow the above rule it will avoid blank pages coming in between in pdf

and also while printing. While printing we have to adjust the report page size

both width and height according to the paper size and also according to the

print Layout we require,either Portrait or Landscape.

If there is issue of Print button not displaying in ie, add the url of the website to
compatibility view settings.

While printing if it displays the error "Unable to load client print control", install
Microsoft ReportViewer 2008 SP1 in both web server and local pc, if we are
using ReportViewer of VS 2008 otherwise install SP1 of the appropriate version.

Monday, January 2, 2017

IIS Security and Permissions for Websites

IIS uses both built-in accounts of IIS as well as built-in accounts of windows for the
security.

While deploying an Application in IIS we should be aware of rights of each account
and prevent providing high access privileges to the files or folders in order to avoid
security risks.

IIS built in users and group accounts:

IUSR :

IUSR is the default built-in account (in IIS 7 and above) that is used by the IIS when
the anonymous access is enabled. Any unauthenticated visitor (basically normal visitors)
uses this Internet User account.This account must not be given high or privileged
permissions for critical files of the Website or Application.

In earlier versions of IIS, IUSR_MachineName was used for anonymous authentication
requests. IUSR account does not need a password and hence user does not have to worry
about password expiration. Before IIS7 each account and group has unique SID or Security
Identifier and is different for the different computer and hence cannot use 'xcopy/o'

to copy files along with their Access Control List and Ownership information to different
computer. This problem is solved in IIS 7 since SIDs of each built-in user or group account
will be the same on all systems that is using Windows Server 2008 or above.

IIS_IUSRS :

IIS_IUSRS is a built-in group from IIS 7 which has the minimum set of user rights
and permissions required to run an Application. It facilitates the convenient way that
if any user account is added to this group, it can be used as an Application Pool identity
without having to manually assign user rights and permissions to that account. In
previous versions of IIS, this group was called IIS_WPG (IIS Worker Process Group).
If any permission has to be given to any file resource for all accounts that run ASP.NET
Applications, we can give access of that resource to IIS_IUSRS in general instead of

specifically assigning separately.

IWAM_MachineName (of IIS 6 and below) :

In IIS 6 there was IWAM_MachineName (Internet Server Web Application Manager)
account which is used for starting out of process Applications like COM or DCOM
in IIS 5 isolation mode. Out of process Applications are applications that runs in a
different process space compared to the one using it where as In- process Applications
runs in the same process space as the one using it. For In-process, local method call is
used for communication but for out of process, remote procedure calls are required for communication. Examples of out of process are Java's RMI and Microsoft's COM
component.

IUSR and IWAM should only be given permissions to what they need and should

not be given permissions to access anything critical.

From IIS 7, IUSR will handle the functionalities of IUSR_MachineName and

IWAM_MachineName.

In short IUSRS is the replacement of IUSR_MachineName and IIS_IUSRS is the
replacement of IIS_WPG in IIS 7.

ASPNET (of IIS 6 and below) :

In earlier versions of IIS there is ASPNET user account which is used for running the
ASP.NET worker process in IIS 5.0 isolation mode.This account doesn't exist from
IIS 7 onwards.

LocalSystem :

This account has the highest privileges of Administrator and if any worker process
identity runs under this account, it will have full Administrative privileges of the
entire system and hence if given it can be a security threat. Therefore it should be given
only in unavoidable circumstances.

Network Service :

This account has less access privileges than LocalSystem but it can access network
resources and remote databases using the local computer's credentials or Windows
Authentication. In IIS 6 this is the default identity used by the ApplicationPool.

LocalService:

This built-in user account has the least privileges than Network Service and its access
level is limited to the local system and doesn't have network permissions.This is used
in case of scenario where access outside the server is not required.

NTFS permissions :

Basically there are two types of Access Controls, NTFS permissions which is controlled
by the Operating System and Website permissions controlled by the IIS. NTFS permissions
can be used only for drives that are formatted in NTFS (New Technology File System) and
not for FAT (File Allocation Table) which is a legacy file system.

Various NTFS Permission levels are :

- Full Control - Users have full control over the file including taking ownership of it.
- Modify - Users can view,modify file and file properties, delete and add files to the
directory. Users cannot take ownership of the file or change file permissions.
- Read and execute - Users can view and run executable files including scripts.
- Read - Users can view files and file properties.
- Write - Users can write to a file.
- List folder contents - Users can view the list of files inside the folder.

While assigning permission we should take care that write NTFS permission is given
only for the file that needs to be modified by the Application on run time and it should
not be given for remaining files or folders inorder to avoid unnecessary data uploading
to the web server.

How to secure FTP Sites?

In case of FTP sites , credentials are passed over the network and are not encrypted or
encoded and hence are vulnerable to malicious users. In order to make it more secure we
can use it over an encrypted channel like VPN secured with PPTP (Point To Point Tunneling Protocol) or Internet Protocol Security (IPSec).

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]

SET ANSI_PADDING OFF

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

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

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

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

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

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

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

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

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

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=8 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

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

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'

Programming in ASP.NET using C#,Javascript,AJAX and MS SQL Server

Wednesday, April 19, 2017

How to restrict accessing a website by IP Address or Domain Name

In IIS 6 :

Tuesday, April 18, 2017

Create Report using ReportViewer

App_Code/AgentBLL :

App_Code/AgentDAL :

Report.aspx :

Report.aspx.cs :

Tips for common Issues :

Monday, January 2, 2017

IIS Security and Permissions for Websites

IIS built in users and group accounts:

IUSR :

IIS_IUSRS :

IWAM_MachineName (of IIS 6 and below) :

ASPNET (of IIS 6 and below) :

LocalSystem :

Network Service :

LocalService:

NTFS permissions :

How to secure FTP Sites?

Monday, November 7, 2016

How to configure Website in IIS?

Installing IIS in Windows 7 :

Monday, October 17, 2016

SQL Server Triggers

After Triggers :

Instead Of Triggers :

Total Pageviews

Search This Blog

Blog Archive