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
{
}
}
}
}
Submit an article or tip