C# DbParameter类代码示例

        private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter, CommandType cmdType)
        {
            IDbDataParameter clone = new OracleParameter() { IsNullable = dbParameter.IsNullable };

            // Remove leading ':' character for stored procedures.
            if (cmdType == CommandType.StoredProcedure)
            {
                string name = parameterRenderer.RenderParameterName(dbParameter);
                if (name.StartsWith(":", StringComparison.Ordinal))
                    name = name.Substring(1, name.Length - 1);

                clone.ParameterName = name;
            }
            else
            {
                clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);
            }

            if (dbParameter.PassMode == SpArgumentPassMode.DataTableFilledByAdapter)
                ((OracleParameter)clone).OracleDbType = OracleDbType.RefCursor;
            else
                clone.DbType = dbParameter.DbType;

            clone.Direction = dbParameter.Direction;
            clone.Precision = dbParameter.Precision;
            clone.Scale = dbParameter.Scale;
            clone.Size = dbParameter.Size;
            clone.SourceColumn = dbParameter.SourceColumn;
            clone.SourceVersion = dbParameter.SourceVersion;
            clone.Value = dbParameter.Value;

            return clone;
        }

 public static DbParameter[] DeriveParameters(DbConnection dbConnection, string spName)
 {
     if (dbConnection == null)
     {
         throw new ArgumentNullException("dbConn");
     }
     if (string.IsNullOrEmpty(spName))
     {
         throw new ArgumentNullException("spName");
     }
     DbCommand command = null;
     using (DbConnection connection = (DbConnection) ((ICloneable) dbConnection).Clone())
     {
         command = connection.CreateCommand();
         command.CommandText = spName;
         command.CommandType = CommandType.StoredProcedure;
         connection.Open();
         if (!(command is SqlCommand))
         {
             throw new NotSupportedException();
         }
         SqlCommandBuilder.DeriveParameters((SqlCommand) command);
         connection.Close();
     }
     if ((command.Parameters.Count > 0) && (command.Parameters[0].Direction == ParameterDirection.ReturnValue))
     {
         command.Parameters.RemoveAt(0);
     }
     DbParameter[] array = new DbParameter[command.Parameters.Count];
     command.Parameters.CopyTo(array, 0);
     return array;
 }

        public void updateYouTube(List<YouTube> youtubes)
        {
            DbParameter[] parameters = new DbParameter[3];
            try
            {
                Ado.ExecuteNonQueryStoredProcedure("DeleteYouTubeUrls", null);
                foreach (YouTube yt in youtubes)
                {
                    parameters[0] = new SqlParameter("@YouTubeLink", yt.YOUTUBELINK);
                    parameters[0].Direction = ParameterDirection.Input;
                    parameters[0].DbType = DbType.String;

                    parameters[1] = new SqlParameter("@NumberOfTimeExecuted", yt.NUMBEROFTIMEEXECUTED);
                    parameters[1].Direction = ParameterDirection.Input;
                    parameters[1].DbType = DbType.Int16;

                    parameters[2] = new SqlParameter("@YouTubeText", yt.YOUTUBETEXT);
                    parameters[2].Direction = ParameterDirection.Input;
                    parameters[2].DbType = DbType.String;

                    Ado.ExecuteNonQueryStoredProcedure("UpdateYouTubeUrls", parameters);
                    //lblError.Visible = true;
                    //lblError.Text = "Updated Successfully";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                parameters = null;
            }
        }

        /// <summary>
        /// Renders INSERT statement and code that retrieves the new ID.
        /// </summary>
        /// <param name="insert">INSERT statement that is being rendered.</param>
        /// <param name="nextSequence">Ignored. SQL Server CE doesn't use sequences.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="output">StringBuilder to which the SQL code is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        /// <returns><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</returns>
        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Renders INSERT statements for DBMSs that support an auto-identity fields.
            // Auto-id field may or may not be in the column-value list.
            // If auto-incremented field is in the column-value list it will be skipped.
            // Table may have only one auto-identity field.
            // Method expects that all errors have been identified and processed in the caller.
            // Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
            int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);

            AppendInsertIntoTableName(insert, dbms, output);
            if (numberOfFieldsToRender > 0)
            {
                AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
                AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
            }
            else
            {
                AppendDefaultValuesExpression(output);
            }

            // Auto ID must be fetched via SELECT after INSERT.
            DbParameter autoId = null;
            return autoId;
        }

 public static DbParameter[] CloneParameters(DbParameter[] dbParameters)
 {
     int length = dbParameters.Length;
     DbParameter[] parameterArray = new DbParameter[length];
     for (int i = 0; i < length; i++)
     {
         parameterArray[i] = (DbParameter)((ICloneable)dbParameters[i]).Clone();
     }
     return parameterArray;
 }

 /// <summary>
 /// 添加SqlCommand 的参数
 /// </summary>
 /// <param name="CmdParameters">SqlCommand 的参数</param>
 public void Add(DbParameter[] CmdParameters)
 {
     foreach (MySqlParameter parameter in CmdParameters)
     {
         if ((parameter.Direction == ParameterDirection.InputOutput) & Convert.IsDBNull(RuntimeHelpers.GetObjectValue(parameter.Value)))
         {
             parameter.Value = DBNull.Value;
         }
         this.Cmd.Parameters.Add(parameter);
     }
 }

        public void UpdateImportDate(long id, DateTime importTime)
        {
            string sql = string.Format("update {0} set {1} = @{1} where {2} = @{2}", T_TableName,
                C_ImportDate, C_Id);

            DbParameter[] parameters = new DbParameter[]{
                new MySqlParameter(string.Format("@{0}", C_ImportDate), importTime),
                new MySqlParameter(string.Format("@{0}", C_Id), id)
            };

            this.ExecuteNonQuery(sql, parameters);
        }

        public void ArchiveMailsOlderThan(DateTime archiveDate)
        {
            string insertIntoArchive = "insert into " + archiveTableName + " SELECT * from " + tableName + " where " + sentColumn + "<@archiveDate";
            DbParameter[] parameters = new DbParameter[] {
                new DbParameter("@archiveDate", SqlDbType.DateTime, archiveDate)
            };
            dbTemplate.Update(insertIntoArchive, parameters);

            string deleteMails = "delete from " + tableName + " where " + sentColumn + "<@archiveDate";
            parameters = new DbParameter[] {
                new DbParameter("@archiveDate", SqlDbType.DateTime, archiveDate)
            };
            dbTemplate.Update(deleteMails, parameters);
        }

    /// <summary>
    ///     A DbConnection extension method that executes the scalar operation.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    /// <returns>An object.</returns>
    public static object ExecuteScalar(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType, DbTransaction transaction)
    {
        using (DbCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            return command.ExecuteScalar();
        }
    }

        /// <summary>
        /// Renders INSERT statement and code that retrieves the new ID.
        /// </summary>
        /// <param name="insert">INSERT statement that is being rendered.</param>
        /// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
        /// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
        /// <param name="output">StringBuilder to which the SQL code is appended.</param>
        /// <param name="parameters">SQL parameter collection to which the object's and its children's
        /// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
        /// <returns>Parameter that contains the ID of the inserted row. <b>null</b> if auto-id field is not used.</returns>
        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Renders INSERT statements for DBMSs that support an auto-identity fields.
            // Auto-id field may or may not be in the column-value list.
            // If auto-incremented field is in the column-value list it will be skipped.
            // Table may have only one auto-identity field.
            // Method expects that all errors have been identified and processed in the caller.
            // Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
            int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);

            AppendInsertIntoTableName(insert, dbms, output);
            if (numberOfFieldsToRender > 0)
            {
                AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
                AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
            }
            else
            {
                AppendDefaultValuesExpression(output);
            }

            DbParameter autoId = null;
            if (HasAutoIdField(insert.Table))
            {
                autoId = new DbParameter("___newAutoIdentity___", DbType.Int32) { Direction = ParameterDirection.Output };
                if (dbms == DbmsType.SqlServer_7)
                {
                    // @@IDENTITY
                    output.Append(" ; set ");
                    autoId.Render(dbms, output, parameters);
                    output.Append(" = @@IDENTITY");
                }
                else
                {
                    // scope_identity()
                    output.Append(" ; set ");
                    autoId.Render(dbms, output, parameters);
                    output.Append(" = scope_identity()");
                }

                // BatchQuery (if ever implemented) reads this value to assign new ID value to InsertStatement.RetrievedData property.
                //insert.NewIdOutputParameterIndexAfterRenderCompleted = parameters.Count - 1;
            }

            // Return auto-id DB parameter. Callers require it to retrieve the new ID value.
            return autoId;
        }

        private static IDbDataParameter ConvertToNativeParameter(DbParameter dbParameter)
        {
            IDbDataParameter clone = new OleDbParameter();
            ((OleDbParameter)clone).IsNullable = dbParameter.IsNullable;
            clone.ParameterName = parameterRenderer.RenderParameterName(dbParameter);

            clone.DbType = dbParameter.DbType;
            clone.Direction = dbParameter.Direction;
            clone.Precision = dbParameter.Precision;
            clone.Scale = dbParameter.Scale;
            clone.Size = dbParameter.Size;
            clone.SourceColumn = dbParameter.SourceColumn;
            clone.SourceVersion = dbParameter.SourceVersion;
            clone.Value = dbParameter.Value;

            return clone;
        }

    /// <summary>
    ///     Enumerates execute expando objects in this collection.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    /// <returns>
    ///     An enumerator that allows foreach to be used to process execute expando objects in this collection.
    /// </returns>
    public static IEnumerable<dynamic> ExecuteExpandoObjects(this DbConnection @this, string cmdText, DbParameter[] parameters, CommandType commandType, DbTransaction transaction)
    {
        using (DbCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            using (IDataReader reader = command.ExecuteReader())
            {
                return reader.ToExpandoObjects();
            }
        }
    }

 public void deleteAnswerByAnswerId(Guid guid)
 {
     DbParameter[] parameters = new DbParameter[1];
     try
     {
         parameters[0] = new SqlParameter("@guid", guid);
         parameters[0].Direction = ParameterDirection.Input;
         parameters[0].DbType = DbType.Guid;
         Ado.ExecuteNonQueryStoredProcedure("sp_DeleteAnswer", parameters);
     }
     catch (Exception ex)
     {
         throw ex;
     }
     finally
     {
         parameters = null;
     }
 }

        private static void AttachParameters(DbCommand command, DbParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (DbParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }

                        command.Parameters.Add(p);
                    }
                }
            }
        }

        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            bool hasAutoIdField = (autoIdField != null);
            bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
            bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);

            AppendInsertIntoTableName(insert, dbms, output);
            AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
            RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);

            if (autoIdField != null && nextSequence != null)
            {
                // RETURNING id
                output.Append(" RETURNING ");
                autoIdField.RenderColumnName(dbms, output);
                output.Append(" INTO ");
                nextSequence.Render(dbms, output, parameters);
            }

            return nextSequence;
        }