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

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
using System;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Web.Services.Protocols;
using System.Xml;

namespace IssueVisionWebWseCS
{
	public class IVData : Component
	{
		private SqlDataAdapter daIssueHistory;
		private SqlConnection sqlConn;
		private SqlCommand SqlInsertIssue;
		private SqlDataAdapter daStaffers;
		private SqlCommand SqlCommand1;
		private SqlCommand SqlSelectCommand1;
		private SqlCommand SqlSelectCommand4;
		private SqlDataAdapter daIssueTypes;
		private SqlCommand SqlUpdateCommand1;
		private SqlCommand SqlSelectCommand2;
		private SqlCommand SqlSelectCommand3;
		private SqlDataAdapter daIssues;

		private IContainer components = null;
		private IVDataSet.ConflictsDataTable m_conflicts;

		public IVData(IContainer Container) : this()
		{
			Container.Add(this);
		}

		public IVData()
		{
			InitializeComponent();
		}

		protected override void Dispose(bool disposing)
		{
			if (disposing && components != null)
			{
				components.Dispose();
			}
			base.Dispose(disposing);
		}

		[DebuggerStepThroughAttribute()]
		private void InitializeComponent()
		{
			AppSettingsReader configurationAppSettings = new AppSettingsReader();
			sqlConn = new SqlConnection();
			daIssueHistory = new SqlDataAdapter();
			SqlCommand1 = new SqlCommand();
			SqlSelectCommand1 = new SqlCommand();
			daIssues = new SqlDataAdapter();
			SqlInsertIssue = new SqlCommand();
			SqlSelectCommand3 = new SqlCommand();
			SqlUpdateCommand1 = new SqlCommand();
			daStaffers = new SqlDataAdapter();
			SqlSelectCommand2 = new SqlCommand();
			daIssueTypes = new SqlDataAdapter();
			SqlSelectCommand4 = new SqlCommand();
			
			sqlConn.ConnectionString = Common.ConnectionString;
			
			daIssueHistory.InsertCommand = SqlCommand1;
			daIssueHistory.SelectCommand = SqlSelectCommand1;
			daIssueHistory.TableMappings.AddRange(
													new DataTableMapping[] 
													{
														new DataTableMapping("Table", "IssueHistory",	new DataColumnMapping[] 
																										{
																											new DataColumnMapping("IssueHistoryID", "IssueHistoryID"), 
																											new DataColumnMapping("StafferID", "StafferID"), 
																											new DataColumnMapping("IssueID", "IssueID"), 
																											new DataColumnMapping("Comment", "Comment"), 
																											new DataColumnMapping("DateCreated", "DateCreated"), 
																											new DataColumnMapping("DisplayName", "DisplayName")
																										})
													});
			
			SqlCommand1.CommandText = "[CreateIssueHistory]";
			SqlCommand1.CommandType = CommandType.StoredProcedure;
			SqlCommand1.Connection = sqlConn;
			SqlCommand1.Parameters.Add(new SqlParameter("@StafferID", SqlDbType.Int, 4, "StafferID"));
			SqlCommand1.Parameters.Add(new SqlParameter("@IssueID", SqlDbType.Int, 4, "IssueID"));
			SqlCommand1.Parameters.Add(new SqlParameter("@Comment", SqlDbType.NVarChar, 3000, "Comment"));
			
			SqlSelectCommand1.CommandText = "[GetIssueHistory]";
			SqlSelectCommand1.CommandType = CommandType.StoredProcedure;
			SqlSelectCommand1.Connection = sqlConn;
			SqlSelectCommand1.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Current, null));
			SqlSelectCommand1.Parameters.Add(new SqlParameter("@lastAccessed", SqlDbType.DateTime, 8));
			
			daIssues.InsertCommand = SqlInsertIssue;
			daIssues.SelectCommand = SqlSelectCommand3;
			daIssues.TableMappings.AddRange(new DataTableMapping[] {new DataTableMapping("Table", "Issues", new DataColumnMapping[] {new DataColumnMapping("IssueID", "IssueID"), new DataColumnMapping("StafferID", "StafferID"), new DataColumnMapping("IssueTypeID", "IssueTypeID"), new DataColumnMapping("Title", "Title"), new DataColumnMapping("Description", "Description"), new DataColumnMapping("DateOpened", "DateOpened"), new DataColumnMapping("DateClosed", "DateClosed"), new DataColumnMapping("IsOpen", "IsOpen"), new DataColumnMapping("DateModified", "DateModified"), new DataColumnMapping("UserName", "UserName"), new DataColumnMapping("DisplayName", "DisplayName"), new DataColumnMapping("IssueType", "IssueType")})});
			daIssues.UpdateCommand = SqlUpdateCommand1;			
			
			SqlInsertIssue.CommandText = "[InsertIssue]";
			SqlInsertIssue.CommandType = CommandType.StoredProcedure;
			SqlInsertIssue.Connection = sqlConn;
			SqlInsertIssue.Parameters.Add(new SqlParameter("@StafferID", SqlDbType.Int, 4, "StafferID"));
			SqlInsertIssue.Parameters.Add(new SqlParameter("@IssueTypeID", SqlDbType.Int, 4, "IssueTypeID"));
			SqlInsertIssue.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 100, "Title"));
			SqlInsertIssue.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 2000, "Description"));
			
			SqlSelectCommand3.CommandText = "dbo.[GetIssueList]";
			SqlSelectCommand3.CommandType = CommandType.StoredProcedure;
			SqlSelectCommand3.Connection = sqlConn;
			SqlSelectCommand3.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Current, null));
			SqlSelectCommand3.Parameters.Add(new SqlParameter("@lastAccessed", SqlDbType.DateTime, 8));
			
			SqlUpdateCommand1.CommandText = "dbo.[UpdateIssue]";
			SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
			SqlUpdateCommand1.Connection = sqlConn;
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Current, null));
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@StafferID", SqlDbType.Int, 4, "StafferID"));
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@IsOpen", SqlDbType.Bit, 1, "IsOpen"));
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@Original_IsOpen", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "IsOpen", DataRowVersion.Original, null));
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@Original_StafferID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "StafferID", DataRowVersion.Original, null));
			SqlUpdateCommand1.Parameters.Add(new SqlParameter("@IssueID", SqlDbType.Int, 4, "IssueID"));
			
			daStaffers.SelectCommand = SqlSelectCommand2;
			daStaffers.TableMappings.AddRange(new DataTableMapping[] {new DataTableMapping("Table", "Staffers", new DataColumnMapping[] {new DataColumnMapping("StafferID", "StafferID"), new DataColumnMapping("UserName", "UserName"), new DataColumnMapping("DisplayName", "DisplayName"), new DataColumnMapping("StafferType", "StafferType")})});
						
			SqlSelectCommand2.CommandText = "[GetStafferList]";
			SqlSelectCommand2.CommandType = CommandType.StoredProcedure;
			SqlSelectCommand2.Connection = sqlConn;
			SqlSelectCommand2.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Current, null));
			
			daIssueTypes.SelectCommand = SqlSelectCommand4;
			daIssueTypes.TableMappings.AddRange(new DataTableMapping[] {new DataTableMapping("Table", "IssueTypes", new DataColumnMapping[] {new DataColumnMapping("IssueTypeID", "IssueTypeID"), new DataColumnMapping("IssueType", "IssueType")})});
			
			SqlSelectCommand4.CommandText = "[GetIssueTypeList]";
			SqlSelectCommand4.CommandType = CommandType.StoredProcedure;
			SqlSelectCommand4.Connection = sqlConn;
			SqlSelectCommand4.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, "", DataRowVersion.Current, null));
		}

		public IVDataSet GetLookupTables()
		{
			IVDataSet lookupTables = new IVDataSet();
			lookupTables.DataSetName = "LookupTables";
			
			daIssueTypes.Fill(lookupTables);
			daStaffers.Fill(lookupTables);
			
			return lookupTables;
		}

		public IVDataSet SendReceiveIssues(DataSet changedIssues, DateTime lastAccessed)
		{
			IVDataSet newIssues = new IVDataSet();
			
			try
			{
				// Second, apply "deletes" from the child table (referential integrity requires
				// this before deletes to the parent table).  The IssueHistory data in IssueVision 
				// is audit data, it is never deleted.  If it were, the code would look something like
				// the following:
				//
				//     Dim deleted as IVDataSet.IssuesDataTable
				//     deleted = (IVDataSet.IssuesDataTable)changedIssues.GetChanges(System.Data.DataRowState.Deleted);
				//     daIssueHistory.Update(deleted)
				//
				// Next, apply changes to the parent table (Issues)
				// This may produce a merge conflict, so set up the event and table to capture them
				// NOTE: The Conflicts table collects the client version; server version is returned
				// in newIssues
				m_conflicts = newIssues.Conflicts;
				daIssues.RowUpdated += new SqlRowUpdatedEventHandler(this.Issues_RowUpdated);
				
				if (changedIssues != null)
				{
					daIssues.Update(changedIssues);
				}
				
				// First, retrieve new issues
				GetNewIssueList(lastAccessed, newIssues);
				
				// Finally, apply insert/updates to the child table (IssueHistory)
				// This never produces conflicts in IssueVision, since issue history is audit data
				if (changedIssues != null)
				{
					daIssueHistory.Update(changedIssues);
				}
			}
			catch (SqlException sqlEx)
			{
				EventLogHelper.LogFailureAudit(string.Format("A problem was encounted updating Issues and IssueHistory: \n{0}", sqlEx.ToString()));
				throw new SoapException(string.Empty, SoapException.ServerFaultCode, "Database");
			}
			catch (Exception)
			{
			}
			
			return newIssues;
		}

		private void Issues_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
		{
			if (e.Status == UpdateStatus.ErrorsOccurred)
			{
				m_conflicts.Rows.Add(e.Row.ItemArray);
				e.Status = UpdateStatus.Continue;
			}
		}

		private void GetNewIssueList(DateTime lastAccessed, IVDataSet newIssues)
		{
			newIssues.DataSetName = "IssueDetails";
			
			try
			{
				try
				{
					daIssues.SelectCommand.Parameters["@lastAccessed"].Value = lastAccessed;
					daIssues.Fill(newIssues);
					newIssues.Issues.PrimaryKey = new DataColumn[] {newIssues.Issues.Columns["IssueID"]};
				}
				catch
				{
				}
				
				try
				{
					daIssueHistory.SelectCommand.Parameters["@lastAccessed"].Value = lastAccessed;
					daIssueHistory.Fill(newIssues);
					newIssues.IssueHistory.PrimaryKey = new DataColumn[]{newIssues.IssueHistory.Columns["IssueHistoryID"]};
				}
				catch 
				{
				}
				
				newIssues.Relations.Add("History", newIssues.Issues.Columns["IssueID"], newIssues.IssueHistory.Columns["IssueID"]);
			}
			catch 
			{
			}
		}
	}
}

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

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.140721.1 | Last Updated 27 Sep 2005
Article Copyright 2005 by Weidong Shen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid