Click here to Skip to main content
Click here to Skip to main content
Articles » Languages » XML » Web Services » Downloads
 
Add your own
alternative version
Go to top

Implementing WS-SecureConversation in Microsoft IssueVision

, 27 Sep 2005
Adding secure communications to the Microsoft IssueVision sample application using WSE 2.0.
issuevisionwsesmartclient.zip
Database
IssueVision
Controls
Outlook
Images
statusbar
toolbar
types
Libraries
ThreadHelper Documentation
Panes
Patterns
Web References
localhost
IssueVisionWebWseCS
Libraries
App.ico
IssueVision.csproj.user
IssueVision.snk
IssueVisionServices.disco
IssueVisionServices.wsdl
Reference.map
AppUpdater.dll
ThreadHelper.dll
About BackgroundWorker.doc
Async Windows Forms Programming with BackgroundWorker.doc
IssueVision-about-banner.png
mosaic-light.png
Signin.png
all_staffers.png
all_types.png
computer.png
environmental.png
help_desk.png
network.png
none.png
printer.png
staff.png
technicians.png
technicians_group.png
telecom.png
new_task.png
synchronize.png
work_offline.png
StatusConnected.ico
StatusDisconnected.ico
StatusSynchronizing.ico
IssueVisionWebWseCS.csproj.webinfo
IVDataSet.xsx
-- *******************************************************
-- Create database
-- *******************************************************
USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'IssueVision')
BEGIN
	DECLARE @spid smallint
	DECLARE @sql varchar(4000)

	DECLARE crsr CURSOR FAST_FORWARD FOR
		SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'IssueVision' AND p.dbid = d.dbid

	OPEN crsr
	FETCH NEXT FROM crsr INTO @spid

	WHILE @@FETCH_STATUS != -1
	BEGIN
		SET @sql = 'KILL ' + CAST(@spid AS varchar)
		EXEC(@sql) 
		FETCH NEXT FROM crsr INTO @spid
	END

	CLOSE crsr
	DEALLOCATE crsr

	DROP DATABASE [IssueVision]
END
GO

CREATE DATABASE [IssueVision] 
GO

Use IssueVision

-- *******************************************************
-- Create tables
-- *******************************************************

--Issues

IF OBJECT_ID('dbo.Issues') IS NOT NULL
   DROP TABLE [dbo].[Issues]
GO
CREATE TABLE [dbo].[Issues] (
	[IssueID] [int] IDENTITY (1, 1) NOT NULL ,
	[StafferID] [int] NOT NULL ,
	[IssueTypeID] [int] NOT NULL ,
	[Title] [nvarchar] (100) NOT NULL ,
	[Description] [nvarchar] (2000) NOT NULL ,
	[DateOpened] [datetime] NOT NULL DEFAULT (getdate()),
	[DateClosed] [datetime] NULL ,
	[IsOpen] [bit] NOT NULL DEFAULT (1),
	[DateModified] [datetime] NULL ,
	CONSTRAINT [PK_Issues] PRIMARY KEY  CLUSTERED 
	(
		[IssueID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


--IssueHistory

IF OBJECT_ID('dbo.IssueHistory') IS NOT NULL
   DROP TABLE [dbo].[IssueHistory]
GO
CREATE TABLE [dbo].[IssueHistory] (
	[IssueHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[StafferID] [int] NOT NULL ,
	[IssueID] [int] NOT NULL ,
	[Comment] [nvarchar] (3000) NOT NULL ,
	[DateCreated] [datetime] NOT NULL DEFAULT (getdate()),
	CONSTRAINT [PK_IssueHistory] PRIMARY KEY  CLUSTERED 
	(
		[IssueHistoryID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


--IssueTypes

IF OBJECT_ID('dbo.IssueTypes') IS NOT NULL
   DROP TABLE [dbo].[IssueTypes]
GO
CREATE TABLE [dbo].[IssueTypes] (
	[IssueTypeID] [int] IDENTITY (1, 1) NOT NULL ,
	[IssueType] [nvarchar] (50) NOT NULL ,
	CONSTRAINT [PK_IssueTypes] PRIMARY KEY  CLUSTERED 
	(
		[IssueTypeID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


--Staffers

IF OBJECT_ID('dbo.Staffers') IS NOT NULL
   DROP TABLE [dbo].[Staffers]
GO
CREATE TABLE [dbo].[Staffers] (
	[StafferID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserName] [nvarchar] (50) NOT NULL ,
	[PasswordHash] [nvarchar] (50) NOT NULL ,
	[PasswordSalt] [nvarchar] (20) NOT NULL, 
	[DisplayName] [nvarchar] (50) NOT NULL ,
	[StafferType] [char] (1) NOT NULL
	CONSTRAINT [PK_Users] PRIMARY KEY  CLUSTERED 
	(
		[StafferID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



-- *******************************************************
-- Create foreign keys
-- *******************************************************

--Issues

IF OBJECT_ID('FK_Issues_IssueTypes') IS NOT NULL
  ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_Issues_IssueTypes]

GO

IF OBJECT_ID('FK_Issues_Staffers') IS NOT NULL
  ALTER TABLE [dbo].[Issues] DROP CONSTRAINT [FK_Issues_Staffers]

GO

ALTER TABLE [dbo].[Issues]
  ADD 
	CONSTRAINT [FK_Issues_IssueTypes] FOREIGN KEY 
	(
		[IssueTypeID]
	) REFERENCES [dbo].[IssueTypes] (
		[IssueTypeID]
	)

GO

ALTER TABLE [dbo].[Issues]
  ADD 
	CONSTRAINT [FK_Issues_Staffers] FOREIGN KEY 
	(
		[StafferID]
	) REFERENCES [dbo].[Staffers] (
		[StafferID]
	)

GO


--IssueHistory

IF OBJECT_ID('FK_IssueHistory_Issues') IS NOT NULL
  ALTER TABLE [dbo].[IssueHistory] DROP CONSTRAINT [FK_IssueHistory_Issues]

GO

IF OBJECT_ID('FK_IssueHistory_Staffers') IS NOT NULL
  ALTER TABLE [dbo].[IssueHistory] DROP CONSTRAINT [FK_IssueHistory_Staffers]

GO

ALTER TABLE [dbo].[IssueHistory]
  ADD 
	CONSTRAINT [FK_IssueHistory_Issues] FOREIGN KEY 
	(
		[IssueID]
	) REFERENCES [dbo].[Issues] (
		[IssueID]
	)

GO

ALTER TABLE [dbo].[IssueHistory]
  ADD 
	CONSTRAINT [FK_IssueHistory_Staffers] FOREIGN KEY 
	(
		[StafferID]
	) REFERENCES [dbo].[Staffers] (
		[StafferID]
	)
GO


-- *******************************************************
-- Create triggers
-- *******************************************************

--IssueHistory


IF OBJECT_ID('dbo.sync_lastupdated') IS NOT NULL
   DROP TRIGGER [dbo].[sync_lastupdated]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create trigger sync_lastupdated
on IssueHistory
for insert, update
as
update Issues
set Issues.DateModified = getdate()
from Issues inner join inserted on (Issues.IssueID = inserted.IssueID)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--Issue


IF OBJECT_ID('dbo.sync_lastupdated_issue') IS NOT NULL
   DROP TRIGGER [dbo].[sync_lastupdated_issue]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create trigger sync_lastupdated_issue
on Issues
for insert, update
as
update Issues
set Issues.DateModified = getdate()
from Issues inner join inserted on (Issues.IssueID = inserted.IssueID)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



-- *******************************************************
-- Create stored procedures
-- *******************************************************

--GetIssueHistory

IF OBJECT_ID('dbo.GetIssueHistory') IS NOT NULL
   DROP PROCEDURE [dbo].[GetIssueHistory]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.GetIssueHistory
(
	@lastAccessed datetime
)

AS

SET NOCOUNT ON;

SELECT 
	IssueHistory.IssueHistoryID, 
	IssueHistory.StafferID, 
	IssueHistory.IssueID, 
	IssueHistory.Comment, 
	IssueHistory.DateCreated, 
	Staffers.DisplayName
FROM 
	IssueHistory 
INNER JOIN 
	Staffers ON IssueHistory.StafferID = Staffers.StafferID
WHERE
	IssueHistory.DateCreated > @lastAccessed
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--CreateIssueHistory

IF OBJECT_ID('dbo.CreateIssueHistory') IS NOT NULL
   DROP PROCEDURE [dbo].[CreateIssueHistory]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [dbo].[CreateIssueHistory]
(
	@StafferID int,
	@IssueID int,
	@Comment nvarchar(3000)
)
AS
	SET NOCOUNT OFF;
INSERT INTO dbo.IssueHistory(StafferID, IssueID, Comment, DateCreated) VALUES (@StafferID, @IssueID, @Comment, GetDate());
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--UpdateIssue

IF OBJECT_ID('dbo.UpdateIssue') IS NOT NULL
   DROP PROCEDURE [dbo].[UpdateIssue]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.UpdateIssue
(
	@StafferID int,
	@IsOpen bit,
	@Original_IsOpen bit,
	@Original_StafferID int,
	@IssueID int
)
AS

SET NOCOUNT OFF;

DECLARE @DateClosed datetime

/* BUGBUG: this logic changes date every time a closed issue is changed */
IF @IsOpen = 0
	SET @DateClosed = GetDate()
ELSE
	SET @DateClosed = NULL

UPDATE 
	Issues 
SET 
	StafferID = @StafferID,   
	IsOpen = @IsOpen, 
	DateClosed = @DateClosed,
	DateModified = GetDate() 
WHERE 
	(IssueID = @IssueID) 
AND 
	(IsOpen = @Original_IsOpen)  
AND 
	(StafferID = @Original_StafferID) 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--InsertIssue

IF OBJECT_ID('dbo.InsertIssue') IS NOT NULL
   DROP PROCEDURE [dbo].[InsertIssue]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.InsertIssue
(
      @StafferID int,
      @IssueTypeID int,
      @Title nvarchar(100),
      @Description nvarchar(2000)
)
AS

SET NOCOUNT OFF;

INSERT INTO Issues (StafferID, IssueTypeID, Title, Description)
VALUES     (@StafferID,@IssueTypeID,@Title,@Description)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--GetStafferList

IF OBJECT_ID('dbo.GetStafferList') IS NOT NULL
   DROP PROCEDURE [dbo].[GetStafferList]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.GetStafferList
AS
	SET NOCOUNT ON;
SELECT StafferID, UserName, DisplayName, StafferType FROM dbo.Staffers ORDER BY DisplayName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--GetIssueTypeList

IF OBJECT_ID('dbo.GetIssueTypeList') IS NOT NULL
   DROP PROCEDURE [dbo].[GetIssueTypeList]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.GetIssueTypeList
AS
	SET NOCOUNT ON;
SELECT IssueTypeID, IssueType FROM dbo.IssueTypes ORDER BY IssueType
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--GetIssueList

IF OBJECT_ID('dbo.GetIssueList') IS NOT NULL
   DROP PROCEDURE [dbo].[GetIssueList]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.GetIssueList
(
	@lastAccessed datetime
)

AS

SET NOCOUNT ON;

SELECT 
	Issues.IssueID, 
	Issues.StafferID, 
	Issues.IssueTypeID, 
	Issues.Title, 
	Issues.[Description], 
	Issues.DateOpened, 
	Issues.DateClosed, 
	Issues.IsOpen, 
	Issues.DateModified,
	Staffers.UserName,
	Staffers.DisplayName AS [DisplayName],
	IssueTypes.IssueType
FROM 
	Issues
INNER JOIN 
	Staffers ON Issues.StafferID = Staffers.StafferID
INNER JOIN 
	IssueTypes ON Issues.IssueTypeID = IssueTypes.IssueTypeID
WHERE 
	DateModified > @lastAccessed

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--GetUser

IF OBJECT_ID('dbo.GetUser') IS NOT NULL
   DROP PROCEDURE [dbo].[GetUser]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.GetUser
(
	@UserName nvarchar(50)
)

AS
	SET NOCOUNT ON;

SELECT 
	StafferID, 
	UserName, 
	PasswordHash,
	PasswordSalt, 
	DisplayName, 
	StafferType
FROM 
	Staffers 
WHERE
	UserName = @UserName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


if not exists (select * from master.dbo.syslogins where loginname = N'IVUser')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'IssueVision', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'IVUser', N'IVUser', @logindb, @loginlang
END
GO

if not exists (select * from dbo.sysusers where name = N'IVUser' and uid < 16382)
	EXEC sp_grantdbaccess N'IVUser', N'IVUser'
GO

GRANT  EXECUTE  ON [dbo].[CreateIssueHistory]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[GetIssueHistory]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[GetIssueList]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[GetIssueTypeList]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[GetStafferList]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[InsertIssue]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[UpdateIssue]  TO [IVUser]
GO

GRANT  EXECUTE  ON [dbo].[GetUser]  TO [IVUser]
GO

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Weidong Shen
Software Developer (Senior)
United States United States
Weidong has been an information system professional since 1990. He has a Master's degree in Computer Science, and is currently a MCSD .NET

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 27 Sep 2005
Article Copyright 2005 by Weidong Shen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid