internal static Delivery GetDelivery(Guid deliveryID, bool deep = true, SqlConnection connection = null) { Delivery delivery = null; bool innerConnection = connection == null; if (innerConnection) { connection = DeliveryDBClient.Connect(); } try { SqlCommand cmd = SqlUtility.CreateCommand(String.Format("{0}(@deliveryID:Char, @deep:bit)", AppSettings.Get(typeof(DeliveryDB), Const.SP_DeliveryGet)), System.Data.CommandType.StoredProcedure); cmd.Connection = connection; cmd.Parameters["@deliveryID"].Value = deliveryID.ToString("N"); cmd.Parameters["@deep"].Value = deep; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { #region Delivery // .................. delivery = new Delivery(reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s))) { FullyLoaded = deep, Account = reader.Convert <int?, Account>("Account_ID", id => id.HasValue ? new Account() { ID = id.Value } : null), Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel() { ID = id.Value } : null), DateCreated = reader.Get <DateTime>("DateCreated"), DateModified = reader.Get <DateTime>("DateModified"), Description = reader.Get <string>("Description"), FileDirectory = reader.Get <string>("FileDirectory"), }; delivery.InternalSetTimePeriod( DateTimeRange.Parse(reader.Get <string>("TimePeriodDefinition")), reader.Get <DateTime>("TimePeriodStart"), reader.Get <DateTime>("TimePeriodEnd") ); // .................. #endregion if (deep) { #region DeliveryParameters // .................. if (reader.NextResult()) { while (reader.Read()) { delivery.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value"))); } } // .................. #endregion #region DeliveryFile // .................. if (reader.NextResult()) { while (reader.Read()) { DeliveryFile deliveryFile = new DeliveryFile(); deliveryFile.FileID = reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s)); deliveryFile.FileCompression = reader.Get <FileCompression>("FileCompression"); deliveryFile.SourceUrl = reader.Get <string>("SourceUrl"); deliveryFile.Name = reader.Get <string>("Name"); deliveryFile.Location = reader.Get <string>("Location"); deliveryFile.Status = reader.Get <DeliveryFileStatus>("Status"); deliveryFile.FileSignature = reader.Get <string>("FileSignature"); delivery.Files.Add(deliveryFile); } } // .................. #endregion #region DeliveryFileParameters // .................. if (reader.NextResult()) { while (reader.Read()) { DeliveryFile deliveryFile = delivery.Files[reader.Get <string>("Name")]; deliveryFile.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value"))); } } // .................. #endregion #region DeliveryOutput if (reader.NextResult()) { while (reader.Read()) { var deliveryOutput = new DeliveryOutput() { OutputID = reader.Convert <string, Guid>("OutputID", s => Guid.Parse(s)), Account = reader.Convert <int?, Account>("AccountID", id => id.HasValue ? new Account() { ID = id.Value } : null), Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel() { ID = id.Value } : null), Signature = reader.Get <string>("Signature"), Status = reader.Get <DeliveryOutputStatus>("Status"), TimePeriodStart = reader.Get <DateTime>("TimePeriodStart"), TimePeriodEnd = reader.Get <DateTime>("TimePeriodEnd"), PipelineInstanceID = reader.Get <Guid>("PipelineInstanceID") }; delivery.Outputs.Add(deliveryOutput); } } #endregion #region DeliveryOutputParameters // .................. if (reader.NextResult()) { while (reader.Read()) { DeliveryOutput deliveryOutput = delivery.Outputs[reader.Get <string>("OutputID")]; deliveryOutput.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value"))); } } // .................. #endregion #region checksum // .................. if (reader.NextResult()) { while (reader.Read()) { var deliveryOutput = delivery.Outputs[reader.Get <string>("OutputID")]; if (deliveryOutput.Checksum == null) { deliveryOutput.Checksum = new Dictionary <string, double>(); } deliveryOutput.Checksum.Add(reader["MeasureName"].ToString(), Convert.ToDouble(reader["Total"])); } } // .................. #endregion } } } } finally { if (innerConnection) { connection.Dispose(); } } return(delivery); }
internal static Guid SaveDelivery(Delivery delivery) { if (!delivery.FullyLoaded) { throw new InvalidOperationException("Cannot save a delivery that was loaded with deep = false."); } using (var client = DeliveryDBClient.Connect()) { SqlTransaction transaction = client.BeginTransaction(); Guid guid = delivery.DeliveryID; if (guid != Guid.Empty) { #region [Delete] // .................. SqlCommand cmd = SqlUtility.CreateCommand((String.Format("{0}(@deliveryID:Char)", AppSettings.Get(typeof(DeliveryDB), Const.SP_DeliveryDelete))), System.Data.CommandType.StoredProcedure); cmd.Connection = client; cmd.Transaction = transaction; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.ExecuteNonQuery(); // .................. #endregion #region Delivery // .................. if (delivery.DateCreated == DateTime.MinValue) { delivery.DateCreated = DateTime.Now; } delivery.DateModified = DateTime.Now; cmd = SqlUtility.CreateCommand(@" INSERT INTO [Delivery] ( [DeliveryID], [Account_ID], [Account_OriginalID], [ChannelID], [DateCreated], [DateModified], [Description], [FileDirectory], [TimePeriodDefinition], [TimePeriodStart], [TimePeriodEnd] ) VALUES ( @deliveryID:Char, @account_ID:Int, @account_OriginalID:NVarChar, @channelID:Int, @dateCreated:DateTime, @dateModified:DateTime, @description:NVarChar, @fileDirectory:NVarChar, @timePeriodDefinition:NVarChar, @timePeriodStart:DateTime2, @timePeriodEnd:DateTime2 ) " , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.Parameters["@account_ID"].Value = delivery.Account != null ? delivery.Account.ID : -1; cmd.Parameters["@channelID"].Value = delivery.Channel != null ? delivery.Channel.ID : -1;; cmd.Parameters["@dateCreated"].Value = delivery.DateCreated; cmd.Parameters["@dateModified"].Value = delivery.DateModified; cmd.Parameters["@description"].Value = delivery.Description == null ? (object)DBNull.Value : delivery.Description; cmd.Parameters["@fileDirectory"].Value = delivery.FileDirectory; cmd.Parameters["@timePeriodDefinition"].Value = delivery.TimePeriodDefinition.ToString(); cmd.Parameters["@timePeriodStart"].Value = delivery.TimePeriodStart; cmd.Parameters["@timePeriodEnd"].Value = delivery.TimePeriodEnd; cmd.ExecuteNonQuery(); // .................. #endregion #region DeliveryParameters // .................. foreach (KeyValuePair <string, object> param in delivery.Parameters) { cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryParameters]( [DeliveryID], [Key], [Value] ) VALUES ( @deliveryID:Char, @key:NVarChar, @value:NVarChar ) " , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.Parameters["@key"].Value = param.Key; cmd.Parameters["@value"].Value = Serialize(param.Value); cmd.ExecuteNonQuery(); } // .................. #endregion #region DeliveryFile // .................. foreach (DeliveryFile file in delivery.Files) { if (file.FileID == Guid.Empty) { file.FileID = Guid.NewGuid(); } if (file.DateCreated == DateTime.MinValue) { file.DateCreated = DateTime.Now; } file.DateModified = DateTime.Now; cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryFile] ( [DeliveryID], [FileID], [Name], [DateCreated], [DateModified], [FileCompression], [SourceUrl], [Location], [Status], [FileSignature] ) VALUES ( @deliveryID:Char, @fileID:Char, @name:NVarChar, @dateCreated:DateTime, @dateModified:DateTime, @fileCompression:Int, @sourceUrl:NVarChar, @location:NVarChar, @status:Int, @fileSignature:NVarChar )" , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = file.Delivery.DeliveryID.ToString("N"); cmd.Parameters["@fileID"].Value = file.FileID.ToString("N"); cmd.Parameters["@name"].Value = file.Name; cmd.Parameters["@dateCreated"].Value = file.DateCreated; cmd.Parameters["@dateModified"].Value = file.DateModified; cmd.Parameters["@fileCompression"].Value = file.FileCompression; cmd.Parameters["@sourceUrl"].Value = file.SourceUrl == null ? (object)DBNull.Value : file.SourceUrl; cmd.Parameters["@location"].Value = file.Location == null ? (object)DBNull.Value : file.Location; cmd.Parameters["@status"].Value = file.Status; cmd.Parameters["@fileSignature"].Value = file.FileSignature == null ? (object)DBNull.Value : file.FileSignature; cmd.ExecuteNonQuery(); } // .................. #endregion #region DeliveryFileParameters // .................. foreach (DeliveryFile file in delivery.Files) { foreach (KeyValuePair <string, object> param in file.Parameters) { cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryFileParameters] ( [DeliveryID], [Name], [Key], [Value] ) VALUES ( @deliveryID:Char, @name:NVarChar, @key:NVarChar, @value:NVarChar )" , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.Parameters["@name"].Value = file.Name; cmd.Parameters["@key"].Value = param.Key; cmd.Parameters["@value"].Value = Serialize(param.Value); cmd.ExecuteNonQuery(); } } // .................. #endregion #region DeliveryOutput // .................. foreach (DeliveryOutput output in delivery.Outputs) { if (output.OutputID == Guid.Empty) { output.OutputID = Guid.NewGuid(); } if (output.DateCreated == DateTime.MinValue) { output.DateCreated = DateTime.Now; } output.DateModified = DateTime.Now; cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryOutput] ( [DeliveryID], [OutputID], [AccountID], [AccountOriginalID], [ChannelID], [Signature], [Status] , [TimePeriodStart], [TimePeriodEnd], [DateCreated], [DateModified], [PipelineInstanceID] ) VALUES ( @deliveryID:Char, @outputID:Char, @accountID:Int, @accountOriginalID:NVarChar, @channelID:Int, @signature:NVarChar, @status:Int, @timePeriodStart:DateTime2, @timePeriodEnd:DateTime2, @dateCreated:DateTime, @dateModified:DateTime, @pipelineInstanceID:BigInt )" , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = output.Delivery.DeliveryID.ToString("N"); cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N"); cmd.Parameters["@accountID"].Value = output.Account != null ? output.Account.ID : -1; cmd.Parameters["@accountOriginalID"].Value = output.Account != null ? output.Account.OriginalID != null ? output.Account.OriginalID : (object)DBNull.Value : (object)DBNull.Value; cmd.Parameters["@channelID"].Value = output.Channel != null ? output.Channel.ID : -1; cmd.Parameters["@signature"].Value = output.Signature; cmd.Parameters["@status"].Value = output.Status; cmd.Parameters["@timePeriodStart"].Value = output.TimePeriodStart; cmd.Parameters["@timePeriodEnd"].Value = output.TimePeriodEnd; cmd.Parameters["@dateCreated"].Value = output.DateCreated; cmd.Parameters["@dateModified"].Value = output.DateModified; cmd.Parameters["@pipelineInstanceID"].Value = output.PipelineInstanceID.HasValue ? output.PipelineInstanceID.Value : (object)DBNull.Value; cmd.ExecuteNonQuery(); } // .................. #endregion #region DeliveryOutputParameters // .................. foreach (DeliveryOutput output in delivery.Outputs) { foreach (KeyValuePair <string, object> param in output.Parameters) { cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryOutputParameters] ( [DeliveryID], [OutputID], [Key], [Value] ) VALUES ( @deliveryID:Char, @outputID:NVarChar, @key:NVarChar, @value:NVarChar )" , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N"); cmd.Parameters["@key"].Value = param.Key; cmd.Parameters["@value"].Value = Serialize(param.Value); cmd.ExecuteNonQuery(); } } // .................. #endregion #region checkSum foreach (var output in delivery.Outputs) { foreach (KeyValuePair <string, double> sum in output.Checksum) { cmd = SqlUtility.CreateCommand(@" INSERT INTO [DeliveryOutputChecksum] ( [DeliveryID], [OutputID], [MeasureName], [Total] ) VALUES ( @deliveryID:Char, @outputid:Char, @measureName:NVarChar, @total:decimal )" , System.Data.CommandType.Text); cmd.Connection = client; cmd.Transaction = transaction; cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N"); cmd.Parameters["@outputid"].Value = output.OutputID.ToString("N"); cmd.Parameters["@measureName"].Value = sum.Key; cmd.Parameters["@total"].Value = sum.Value; cmd.ExecuteNonQuery(); } } #endregion transaction.Commit(); } else { throw new NotSupportedException("In Pipeline 2.9, you cannot save a Delivery without first giving it a GUID."); } return(guid); } }