/// <summary> /// Transfere dados de um arquivo Excel para o banco de dados atual. /// Conexão com o banco atual precisa estar aberta. /// Não pára a execução se der um problema num comando de inserção específico. /// </summary> /// <returns>Número de linhas transferidas.</returns> /// <param name="p_filename">Nome do arquivo de origem.</param> /// <param name="p_separator">Separador de campos do arquivo CSV.</param> /// <param name="p_delimitator">Delimitador de campos do arquivo CSV.</param> /// <param name="p_header">Se deve considerar a primeira linha como cabeçalho ou não.</param> /// <param name="p_encoding">Codificação para leitura do arquivo CSV.</param> /// <param name="p_newtable">Nome da nova tabela a ser criada no banco de dados.</param> /// <param name="p_progress">Evento de progresso.</param> /// <param name="p_error">Evento de erro.</param> public override uint TransferFromFile(string p_filename, char p_separator, char p_delimitator, bool p_header, System.Text.Encoding p_encoding, string p_newtable, Spartacus.Utils.ProgressEventClass p_progress, Spartacus.Utils.ErrorEventClass p_error) { Spartacus.Database.Command v_cmd; Spartacus.Utils.Excel v_excel = null; uint v_transfered = 0; string v_createtable; string v_insert; try { v_excel = new Spartacus.Utils.Excel(); v_excel.Import(p_filename, p_separator, p_delimitator, p_header, p_encoding); v_createtable = "create table " + p_newtable + " ("; for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++) { if (k < v_excel.v_set.Tables[0].Columns.Count-1) v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " text,"; else v_createtable += v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + " text)"; } try { this.Execute(v_createtable); } catch (Spartacus.Database.Exception e) { p_error.FireEvent(v_createtable + "\n" + e.v_message); } v_cmd = new Spartacus.Database.Command(); v_cmd.v_text = "insert into " + p_newtable + " values ("; for (int k = 0; k < v_excel.v_set.Tables[0].Columns.Count; k++) { if (k < v_excel.v_set.Tables[0].Columns.Count-1) v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#,"; else v_cmd.v_text += "#" + v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower() + "#)"; v_cmd.AddParameter(v_excel.v_set.Tables[0].Columns[k].ColumnName.ToLower(), Spartacus.Database.Type.QUOTEDSTRING); } foreach (System.Data.DataRow r in v_excel.v_set.Tables[0].Rows) { foreach (System.Data.DataColumn c in v_excel.v_set.Tables[0].Columns) v_cmd.SetValue(c.ColumnName, r[c].ToString()); v_insert = v_cmd.GetUpdatedText(); try { this.Execute(v_insert); v_transfered++; p_progress.FireEvent(v_transfered); } catch (Spartacus.Database.Exception e) { p_error.FireEvent(v_insert + "\n" + e.v_message); } } return v_transfered; } catch (Spartacus.Utils.Exception e) { throw new Spartacus.Database.Exception(e); } catch (Spartacus.Database.Exception e) { throw e; } finally { if (v_excel != null) { v_excel.Clear(); v_excel = null; } } }
/// <summary> /// Audits a publishing transaction /// </summary> /// <param name="transaction"><see cref="T:Tridion.ContentManager.Publishing.PublishTransaction" /></param> /// <param name="args">The <see cref="SaveEventArgs"/> instance containing the event data.</param> /// <param name="phase"><see cref="T:Tridion.ContentManager.Extensibility.EventPhases" /></param> private void AuditPublish(PublishTransaction transaction, SaveEventArgs args, EventPhases phase) { if (phase == EventPhases.TransactionCommitted && transaction.State == PublishTransactionState.Success) { try { using (SqlConnection connection = Connection) { // Register the publish transaction using (SqlCommand sqlAuditPublishTransaction = new SqlCommand() { CommandText = "AuditPublishTransaction", CommandType = CommandType.StoredProcedure, Connection = connection }) { sqlAuditPublishTransaction.AddParameter("@Transaction", SqlDbType.VarChar, transaction.Id.ToString()); sqlAuditPublishTransaction.AddParameter("@Action", SqlDbType.VarChar, (transaction.Instruction is PublishInstruction ? "Publish" : "Unpublish")); sqlAuditPublishTransaction.AddParameter("@TimeStamp", SqlDbType.DateTime, transaction.StateChangeDateTime); sqlAuditPublishTransaction.AddParameter("@Username", SqlDbType.VarChar, transaction.Creator.Title.ToUpper()); sqlAuditPublishTransaction.AddParameter("@UserDescription", SqlDbType.NVarChar, transaction.Creator.Description); int transactionId = Convert.ToInt32(sqlAuditPublishTransaction.ExecuteScalar()); if (transactionId > 0) { using (SqlCommand sqlAuditPublishedItem = new SqlCommand() { CommandText = "AuditPublishedItem", CommandType = CommandType.StoredProcedure, Connection = connection }) { // Register the publication transaction sqlAuditPublishedItem.AddParameter("@TransactionID", SqlDbType.Int, transactionId); sqlAuditPublishedItem.AddParameter("@PublicationTarget", SqlDbType.VarChar); sqlAuditPublishedItem.AddParameter("@Publication", SqlDbType.VarChar); sqlAuditPublishedItem.AddParameter("@ItemID", SqlDbType.VarChar); sqlAuditPublishedItem.AddParameter("@ItemTitle", SqlDbType.NVarChar); sqlAuditPublishedItem.AddParameter("@ItemTemplate", SqlDbType.VarChar); sqlAuditPublishedItem.AddParameter("@IsComponentTemplate", SqlDbType.Bit); sqlAuditPublishedItem.AddParameter("@IsDCP", SqlDbType.Bit); foreach (PublishContext publishContext in transaction.PublishContexts) { foreach (ProcessedItem processedItem in publishContext.ProcessedItems) { // Register each published item ResolvedItem resolvedItem = processedItem.ResolvedItem; sqlAuditPublishedItem.SetValue("@PublicationTarget", publishContext.PublicationTarget.Id.ToString()); sqlAuditPublishedItem.SetValue("@Publication", publishContext.Publication.Id.ToString()); sqlAuditPublishedItem.SetValue("@ItemID", resolvedItem.Item.VersionedItemId()); sqlAuditPublishedItem.SetValue("@ItemTitle", resolvedItem.Item.Title); sqlAuditPublishedItem.SetValue("@ItemTemplate", resolvedItem.Template.VersionedItemId()); sqlAuditPublishedItem.SetValue("@IsComponentTemplate", resolvedItem.IsComponentPresentation); sqlAuditPublishedItem.SetValue("@IsDCP", resolvedItem.IsDynamicComponentPresentation); sqlAuditPublishedItem.ExecuteNonQuery(); } } } } } } } catch (Exception ex) { Logger.Write(ex, "TcmEvents.Audit", LoggingCategory.General, TraceEventType.Error); } } }