C# SqlCommand.AddParameter方法代码示例

        public void properties_Are_assigned()
        {
            var cmd = new SqlCommand();
            cmd.CommandText = "SELECT";
            cmd.AddParameter("a", "b");

            var sut = new EntityNotFoundException("Failed", cmd);

            sut.CommandText.Should().Be(cmd.CommandText);
            sut.CommandParameters.Should().Be("a=b");
        }

        public void serialization_works_with_datacontract()
        {
            var cmd = new SqlCommand();
            cmd.CommandText = "SELECT";
            cmd.AddParameter("a", "b");
            var serializer = new DataContractSerializer(typeof (EntityNotFoundException));
            var ms = new MemoryStream();

            var sut = new EntityNotFoundException("Failed", cmd);
            serializer.WriteObject(ms, sut);
            ms.Position = 0;
            var actual = (EntityNotFoundException) serializer.ReadObject(ms);

            actual.CommandText.Should().Be(cmd.CommandText);
            actual.CommandParameters.Should().Be("a=b");
        }

        public void serialization_works_with_BinaryFormatter()
        {
            var cmd = new SqlCommand();
            cmd.CommandText = "SELECT";
            cmd.AddParameter("a", "b");
            var serializer = new BinaryFormatter();
            var ms = new MemoryStream();

            var sut = new EntityNotFoundException("Failed", cmd);
            serializer.Serialize(ms, sut);
            ms.Position = 0;
            var actual = (EntityNotFoundException) serializer.Deserialize(ms);

            actual.CommandText.Should().Be(cmd.CommandText);
            actual.CommandParameters.Should().Be("a=b");
        }

        /// <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>
		/// Generates an sync statements of this colum.
		/// </summary>
		/// <param name="context"></param>
		/// <param name="command"></param>
		/// <param name="record"></param>
		/// <param name="columnText"></param>
		/// <param name="valueText"></param>
		protected override void DoToSyncStatement(IMansionContext context, SqlCommand command, Record record, StringBuilder columnText, StringBuilder valueText)
		{
			// determine the value
			var value = GetValue(context, record.Get<object>(context, PropertyName));

			// write the SQL statement
			columnText.AppendFormat("[{0}], ", ColumnName);
			valueText.AppendFormat("@{0}, ", command.AddParameter(value));
		}

        /// <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);
                }
            }
        }

        /// <summary>
        /// Audits the content edit.
        /// </summary>
        /// <param name="action">Content Editing Action</param>
        /// <param name="identifiableObject">The identifiable object.</param>
        /// <param name="comments">Optional comments.</param>
        /// <param name="xml">Optional Xml blob.</param>
        private void AuditContentEdit(String action, IdentifiableObject identifiableObject, String comments, SqlXml xml)
        {
            try
            {
                using (SqlConnection connection = Connection)
                {
                    // Register the content editing action
                    using (SqlCommand sqlAuditContentEdit = new SqlCommand()
                    {
                        CommandText = "AuditContentEdit",
                        CommandType = CommandType.StoredProcedure,
                        Connection = connection
                    })
                    {
                        sqlAuditContentEdit.AddParameter("@Action", SqlDbType.VarChar, action);
                        sqlAuditContentEdit.AddParameter("@ItemID", SqlDbType.VarChar, identifiableObject.VersionedItemId());
                        sqlAuditContentEdit.AddParameter("@ItemTitle", SqlDbType.NVarChar, identifiableObject.Title);
                        sqlAuditContentEdit.AddParameter("@Username", SqlDbType.VarChar, identifiableObject.Session.User.Title.ToUpper());
                        sqlAuditContentEdit.AddParameter("@UserDescription", SqlDbType.NVarChar, identifiableObject.Session.User.Description);

                        if (!String.IsNullOrEmpty(comments))
                            sqlAuditContentEdit.AddParameter("@Comments", SqlDbType.NVarChar, comments);
                        else
                            sqlAuditContentEdit.AddParameter("@Comments", SqlDbType.NVarChar, DBNull.Value);

                        if (xml != null)
                            sqlAuditContentEdit.AddParameter("@Xml", SqlDbType.Xml, xml);
                        else
                            sqlAuditContentEdit.AddParameter("@Xml", SqlDbType.Xml, DBNull.Value);

                        sqlAuditContentEdit.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Write(ex, "TcmEvents.Audit", LoggingCategory.General, TraceEventType.Error);
            }
        }