private static void updateTemplate(TemplateDS.TemplateTableRow template, TemplateDS stops)
{
//
SqlConnection sqlConnection = null;
try {
sqlConnection = new SqlConnection(Mediator.Connection);
sqlConnection.Open();
using (SqlTransaction trans = sqlConnection.BeginTransaction()) {
try {
executeNonQueryTypedParams(trans, USP_TEMPLATES_UPDATE, template);
foreach (TemplateDS.TemplateTableRow stop in stops.TemplateTable.Rows)
{
if (stop.IsStopIDNull())
{
//We need to add Stop here instead of update
stop.TemplateID = template.TemplateID;
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_NEW, stop);
}
else
{
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_UPDATE, stop);
}
}
trans.Commit();
}
catch (Exception ex) { trans.Rollback(); throw ex; }
}
}
finally { if (sqlConnection != null)
{
sqlConnection.Dispose();
}
}
}
private static void addTemplate(TemplateDS.TemplateTableRow template, TemplateDS stops)
{
SqlConnection sqlConnection = null;
try {
sqlConnection = new SqlConnection(Mediator.Connection);
sqlConnection.Open();
using (SqlTransaction trans = sqlConnection.BeginTransaction()) {
try {
template.TemplateLastUpdated = DateTime.Now;
template.TemplateUser = Environment.UserName;
SqlParameter[] spParams = setTemplateParameters(template);
executeNonQuery(trans, USP_TEMPLATES_NEW, spParams);
string templateID = spParams[0].Value.ToString();
foreach (TemplateDS.TemplateTableRow stop in stops.TemplateTable.Rows)
{
stop.TemplateID = templateID;
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_NEW, stop);
}
trans.Commit();
}
catch (Exception ex) { trans.Rollback(); throw ex; }
}
}
finally { if (sqlConnection != null)
{
sqlConnection.Dispose();
}
}
}
public static TemplateDS GetTemplates()
{
//Get a collection of all ship schedules templates for all terminals
TemplateDS templates = null;
try {
//Clear and update ship schedules
templates = new TemplateDS();
DataSet ds = fillDataset(USP_TEMPLATES, TBL_TEMPLATES, null);
if (ds.Tables[TBL_TEMPLATES].Rows.Count > 0)
{
templates.Merge(ds, true, MissingSchemaAction.Ignore);
}
}
catch (Exception ex) { throw new ApplicationException("Failed to refresh templates list.", ex); }
return(templates);
}
public static TemplateDS GetTemplates()
{
//Get templates list
TemplateDS templates = null;
try {
templates = new TemplateDS();
_Client = new ShipScheduleServiceClient();
DataSet ds = _Client.GetTemplates();
templates.Merge(ds);
_Client.Close();
}
catch (TimeoutException te) { _Client.Abort(); throw new ApplicationException(te.Message, te.InnerException); }
catch (FaultException <ShipScheduleFault> ssf) { throw new ApplicationException(ssf.Reason.ToString(), ssf.InnerException); }
catch (FaultException fe) { throw new ApplicationException(fe.Message, fe.InnerException); }
catch (CommunicationException ce) { _Client.Abort(); throw new ApplicationException(ce.Message, ce.InnerException); }
return(templates);
}
private static TemplateDS populateStopTemplateRow(TemplateDS.TemplateTableRow template)
{
//Build First Stop row
TemplateDS ds = new TemplateDS();
TemplateDS.TemplateTableRow stop1 = ds.TemplateTable.NewTemplateTableRow();
stop1.StopID = !template.IsStopIDNull() ? template.StopID : "";
stop1.StopNumber = "1";
stop1.Tag = template.Tag;
stop1.AgentTerminalID = template.AgentTerminalID;
stop1.ScheduledArrivalDateOffset = template.ScheduledArrivalDateOffset;
stop1.ScheduledArrivalTime = template.ScheduledArrivalTime;
stop1.ScheduledOFD1Offset = template.ScheduledOFD1Offset;
stop1.Notes = template.IsNotesNull() ? "" : template.Notes;
stop1.Stop1LastUpdated = DateTime.Today;
stop1.Stop1User = Environment.UserName;
stop1.Stop1RowVersion = !template.IsStop1RowVersionNull() ? template.Stop1RowVersion : "";
ds.TemplateTable.AddTemplateTableRow(stop1);
//check for the second stop
if (!template.IsS2MainZoneNull() && template.S2MainZone.Trim() != "")
{
TemplateDS.TemplateTableRow stop2 = ds.TemplateTable.NewTemplateTableRow();
stop2.StopID = !template.IsS2StopIDNull() ? template.S2StopID : "";
stop2.StopNumber = "2";
stop2.Tag = template.S2Tag;
stop2.AgentTerminalID = template.S2AgentTerminalID;
stop2.ScheduledArrivalDateOffset = template.S2ScheduledArrivalDateOffset;
stop2.ScheduledArrivalTime = template.S2ScheduledArrivalTime;
stop2.ScheduledOFD1Offset = template.S2ScheduledOFD1Offset;
stop2.Notes = template.IsS2NotesNull() ? "" : template.S2Notes;
stop2.Stop1LastUpdated = DateTime.Today;
stop2.Stop1User = Environment.UserName;
stop2.Stop1RowVersion = !template.IsStop2RowVersionNull() ? template.Stop2RowVersion : "";
ds.TemplateTable.AddTemplateTableRow(stop2);
}
return(ds);
}
