C# DbParameterCollection.Add方法代码示例

        /// <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>Ouput parameter that will contain the value retrieved by RETURNING clause.</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);
            }

            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            DbParameter autoId = null;
            if (autoIdField != null)
            {
                // RETURNING id
                output.Append(" RETURNING ");
                autoIdField.RenderColumnName(dbms, output);
                autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
                parameters.Add(autoId);
            }

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

        private DataTable FetchParentRelations(string tableName)
        {
            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT foreignKeys.CONSTRAINT_NAME, foreignKeys.COLUMN_NAME AS CHILD_COLUMN, parents.TABLE_NAME AS PARENT_TABLE, primaryKeys.COLUMN_NAME AS PARENT_COLUMN ");
            select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as foreignKeys ");
            select.AppendLine("INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as relations ON ");
            select.AppendLine("	foreignKeys.CONSTRAINT_NAME = relations.CONSTRAINT_NAME ");
            select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as parents ON ");
            select.AppendLine("	relations.UNIQUE_CONSTRAINT_NAME = parents.CONSTRAINT_NAME ");
            select.AppendLine("INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS primaryKeys ON ");
            select.AppendLine("	primaryKeys.CONSTRAINT_NAME = parents.CONSTRAINT_NAME ");
            select.AppendLine("WHERE foreignKeys.TABLE_NAME ILIKE :tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            select.AppendLine("	AND foreignKeys.ORDINAL_POSITION = primaryKeys.ORDINAL_POSITION ");
            if (!string.IsNullOrEmpty(this.Schema))
            {
                select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }

            DataTable parentData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            RemoveExcludedFieldsData(tableName, "COLUMN_NAME", parentData);
            return parentData;
        }

        private DataTable FetchConstraintData(string tableName)
        {
            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, CONSTRAINT_TYPE ");
            select.AppendLine("FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ");
            select.AppendLine("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON  ");
            select.AppendLine("	INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME ");
            select.AppendLine(" AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ");
            select.AppendLine("WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME ILIKE :tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            if (!string.IsNullOrEmpty(this.Schema))
            {
                select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }

            DataTable constraintData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            return constraintData;
        }

        private DataTable FetchColumnData(string tableName)
        {
            DbParameterCollection args = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT INFORMATION_SCHEMA.COLUMNS.*, 0 AS IsComputed, CHARACTER_MAXIMUM_LENGTH AS ColumnLength, ");
            select.AppendLine("position('nextval(' in column_default) AS IsIdentity, ");
            select.AppendLine("0 AS IsRowGuidColumn, 0 AS IsPrimaryKey, 0 AS IsForeignKey, 0 AS HasUniqueConstraint, ");
            select.AppendLine("case position('nextval(' in column_default) when 1 then trim(trailing '\"''::regclass)' from trim(leading 'nextval(''\"' from column_default)) end as SequenceName ");
            select.AppendLine("FROM INFORMATION_SCHEMA.COLUMNS ");
            select.AppendLine("WHERE TABLE_NAME ILIKE :tableName ");
            args.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
            if (!string.IsNullOrEmpty(this.Schema))
            {
                select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
                args.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            select.AppendLine("ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ");

            DataTable fieldsData = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), args, CommandType.Text, null, 30);
            RemoveExcludedFieldsData(tableName, "COLUMN_NAME", fieldsData);
            return fieldsData;
        }

        /// <summary>Fetches list with basic table and view info.</summary>
        public IEnumerable<SqlObjectHeader> FetchTablesAndViews()
        {
            DbParameterCollection parameters = new DbParameterCollection();
            StringBuilder select = new StringBuilder();
            select.AppendLine("SELECT TABLE_NAME as ObjectName, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 0 WHEN 'VIEW' THEN 1 END AS ObjectType ");
            select.AppendLine("FROM INFORMATION_SCHEMA.TABLES ");
            select.AppendLine("WHERE TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema') ");
            if (!string.IsNullOrEmpty(this.Catalog))
            {
                select.AppendLine("AND TABLE_CATALOG ILIKE :catalog ");
                parameters.Add(new DbParameter("catalog", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(this.Schema))
            {
                select.AppendLine("AND TABLE_SCHEMA ILIKE :schema ");
                parameters.Add(new DbParameter("schema", DbType.AnsiString, this.Catalog, ParameterDirection.Input));
            }
            select.AppendLine("ORDER BY TABLE_TYPE ASC, TABLE_NAME ASC ");

            DataTable tablesAndViews = DbUtil.ExecuteQuery(GetConnectionProvider(), select.ToString(), parameters, CommandType.Text, null, 30);
            List<SqlObjectHeader> list = new List<SqlObjectHeader>();
            foreach (DataRow row in tablesAndViews.Rows)
                list.Add(new SqlObjectHeader((string)row["ObjectName"], (SqlObjectType)row["ObjectType"]));

            return list;
        }

		/// <summary>
		/// Executes stored procedure uspPrintError. Uses given connection.
		/// </summary>
		/// <param name="conn">ConnectionProvider.</param>
		/// <returns>Data retrieved by stored procedure.</returns>
		public static StoredProcedureResult UspPrintError(IConnectionProvider conn)
		{
			DbParameterCollection parameters = new DbParameterCollection();
			
			// Return value is fetched as Varchar but it will be converted to integer.
			// This is because some DBMSs also support return types other than integer.
			// Setting SqlDbType to int would throw an exception in such cases.
			parameters.Add(new DbParameter("ReturnValue", DbType.AnsiString, 1000, ParameterDirection.ReturnValue, true, 0, 0, null, DataRowVersion.Proposed, null));

			// Execute stored procedure.
			DataSet data = DbUtil.ExecuteMultiQuery(conn, "uspPrintError", parameters, CommandType.StoredProcedure, 30);
      
			// Fetch return value.
			object retval = (parameters["ReturnValue"].Value == DBNull.Value) ? null : parameters["ReturnValue"].Value;

			return new StoredProcedureResult(data, retval);
		}

		private static DbParameterCollection CreateParameters(IValueSet values)
		{
			if (values == null)
				return null;

			DbParameterCollection collection = new DbParameterCollection();
			foreach (string key in values.Keys)
			{
				collection.Add(new DbParameterDescriptor(
					BuildParameterName(key), values[key]));
			}
			return collection;
		}

 private DataTable FetchForeignKeysMeta(string tableName)
 {
     DbParameterCollection selectFKsParams = new DbParameterCollection();
     selectFKsParams.Add(new DbParameter("tableName", DbType.AnsiString, tableName, ParameterDirection.Input));
     DataTable fkData = DbUtil.ExecuteQuery(GetConnectionProvider(), SelectForeignKeys, selectFKsParams, CommandType.Text, null, 30);
     foreach (DataRow row in fkData.Rows)
     {
         // Fetched tada contains trailing speces --> must trim.
         row[0] = ((string)row[0]).Trim();
         row[1] = ((string)row[1]).Trim();
         row[2] = ((string)row[2]).Trim();
         row[3] = ((string)row[3]).Trim();
         row[4] = ((string)row[4]).Trim();
     }
     return fkData;
 }

		/// <summary>
		/// Renders the parameter. RenderedName property is set after this method is executed.
		/// </summary>
		/// <param name="dbms">Target DBMS.</param>
		/// <param name="output">StringBuilder to which DBMS dependant parameter name is appended (eg: '@Id' for SQL Server).</param>
		/// <param name="parameters">SQL parameter collection to which this parameter will be added.
		/// Another unique name is automatically given to the parameter if another parameter with the
		/// same name is already in the collection.</param>
		public void Render(DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
		{
			// Each parameter in the collection must have a unique name which is ensured by appending
			// the parameter index to the end of the name. This yields better performance, O(n) complexity,
			// than scanning the collection if a parameter with the same name already exists, O(n2).
			int newParIdx = parameters.Count;

            IParameterRenderer renderer = DbmsComponentFactory.GetComponent<IParameterRenderer>(dbms);
            this.uniqueName = renderer.GetUniqueName(this.originalName, newParIdx);
            this.renderedName = renderer.RenderParameterName(this);			

			parameters.Add(this);
			output.Append(this.renderedName);
		}