C# SqlCommand.Parameter类代码示例

		/// <summary>
		/// Configures the TableGenerator by reading the value of <c>table</c>, 
		/// <c>column</c>, and <c>schema</c> from the <c>parms</c> parameter.
		/// </summary>
		/// <param name="type">The <see cref="IType"/> the identifier should be.</param>
		/// <param name="parms">An <see cref="IDictionary"/> of Param values that are keyed by parameter name.</param>
		/// <param name="dialect">The <see cref="Dialect.Dialect"/> to help with Configuration.</param>
		public virtual void Configure( IType type, IDictionary parms, Dialect.Dialect dialect )
		{
			this.tableName = PropertiesHelper.GetString( Table, parms, "hibernate_unique_key" );
			this.columnName = PropertiesHelper.GetString( Column, parms, "next_hi" );
			string schemaName = ( string ) parms[ Schema ];
			if( schemaName != null && tableName.IndexOf( StringHelper.Dot ) < 0 )
			{
				tableName = schemaName + "." + tableName;
			}

			query = "select " + columnName + " from " + tableName;
			if( dialect.SupportsForUpdate )
			{
				query += " for update";
			}

			// build the sql string for the Update since it uses parameters
			Parameter setParam = new Parameter( columnName, new Int32SqlType() );
			Parameter whereParam = new Parameter( columnName, new Int32SqlType() );

			SqlStringBuilder builder = new SqlStringBuilder();
			builder.Add( "update " + tableName + " set " )
				.Add( columnName )
				.Add( " = " )
				.Add( setParam )
				.Add( " where " )
				.Add( columnName )
				.Add( " = " )
				.Add( whereParam );

			updateSql = builder.ToSqlString();

		}

		void ISqlStringVisitor.Parameter(Parameter parameter)
		{
			if (hasReturnParameter && !foundReturnParameter)
			{
				result.Append(parameter);
				foundReturnParameter = true;
				return;
			}

			string name;

			if (queryIndexToNumberOfPreceedingParameters.Count == 0)
			{
				// there's only one query... no need to worry about indexes of parameters of previous queries
				name = formatter.GetParameterName(parameter.OriginalPositionInQuery ?? parameterIndex);
			}
			else
			{
				// multiple queries... in case the parameters were switched around (for SQL paging for instance) we need
				// to keep the number of preceeding parameters (in previous queries of the batch) into account
				if (parameter.OriginalPositionInQuery != null)
				{
					name = formatter.GetParameterName(GetNumberOfPreceedingParameters() + parameter.OriginalPositionInQuery.Value);
				}
				else
				{
					name = formatter.GetParameterName(parameterIndex);
				}
			}

			parameterIndex++;
			result.Append(name);
		}

		/// <summary>
		/// Generates an array of parameters for the given <see cref="SqlType">SqlTypes</see>.
		/// </summary>
		/// <param name="count">The number of parameters to generate.</param>
		/// <returns>An array of <see cref="Parameter"/> objects</returns>
		public static Parameter[] GenerateParameters(int count)
		{
			Parameter[] result = new Parameter[count];
			for (int i = 0; i < count; i++)
			{
				result[i] = Placeholder;
			}
			return result;
		}

		public void EqualsLengthDiffType()
		{
			Parameter x = new Parameter( "name", "alias", new SqlTypes.AnsiStringSqlType(5) );
			ParameterLength y = new ParameterLength( "name", "alias", new SqlTypes.AnsiStringSqlType(5) );
			
			// even though these contain the exact same values - they should not be 
			// equal because they are different types
			Assert.IsFalse( x.Equals(y) );
			Assert.IsFalse( y.Equals(x) );
		}

		/// <summary>
		/// Generates an IDbDataParameter for the IDbCommand.  It does not add the IDbDataParameter to the IDbCommand's
		/// Parameter collection.
		/// </summary>
		/// <param name="command">The IDbCommand to use to create the IDbDataParameter.</param>
		/// <param name="name">The name to set for IDbDataParameter.Name</param>
		/// <param name="parameter">The Parameter to convert to an IDbDataParameter.</param>
		/// <param name="dialect">The Dialect to use for Default lengths if needed.</param>
		/// <returns>An IDbDataParameter ready to be added to an IDbCommand.</returns>
		/// <remarks>
		/// This adds logic to ensure that a DbType.Boolean parameter is not created since
		/// ODP.NET doesn't support it.
		/// </remarks>
		protected override System.Data.IDbDataParameter GenerateParameter(IDbCommand command, string name, Parameter parameter, NHibernate.Dialect.Dialect dialect)
		{
			// if the parameter coming in contains a boolean then we need to convert it 
			// to another type since ODP.NET doesn't support DbType.Boolean
			if( parameter.SqlType.DbType==DbType.Boolean )
			{
				parameter = new Parameter( parameter.Name, NHibernateUtil.Int16.SqlType ) ;
			}
			return base.GenerateParameter( command, name, parameter, dialect );
		}

		public void EqualsPrecisionDiffType()
		{
			Parameter x = new Parameter( "name", "alias", new SqlTypes.DecimalSqlType( 20, 4 ) );
			ParameterPrecisionScale y = new ParameterPrecisionScale( "name", "alias", new SqlTypes.DecimalSqlType( 20, 4 ) );
			
			// even though these contain the exact same values - they should not be 
			// equal because they are different types
			Assert.IsFalse( x.Equals(y) );
			Assert.IsFalse( y.Equals(x) );
		}

		protected void CompareSqlStrings(SqlString actualSqlString, string expectedString, int expectedNumOfParameters) 
		{
			Parameter[] actualParameters = new Parameter[expectedNumOfParameters];
			int numOfParameters = 0;

			GetParameters(actualSqlString, ref actualParameters, ref numOfParameters);
			Assert.AreEqual(expectedString, actualSqlString.ToString(), "SqlString.ToString()");
			Assert.AreEqual(expectedNumOfParameters, numOfParameters, "Num of Parameters");

		}

		private static Parameter[ ] GenerateValueParameters( string prefix, SqlType sqlType, int count )
		{
			Parameter[ ] parameters = new Parameter[count];

			for( int i = 0; i < count; i++ )
			{
				string parameterName = StringHelper.Suffix( prefix, StringHelper.Underscore + i.ToString() );
				parameters[ i ] = new Parameter( parameterName, sqlType );
			}

			return parameters;
		}

		/// <summary>
		/// Used to pull the Parameter[] and Number of Parameters out of the SqlString
		/// </summary>
		/// <param name="sqlString"></param>
		/// <param name="parameters"></param>
		/// <param name="numOfParameters"></param>
		protected void GetParameters(SqlString sqlString, ref Parameter[] parameters, ref int numOfParameters) 
		{
			
			foreach(object part in sqlString.SqlParts) 
			{
				if(part is Parameter) 
				{
					parameters[numOfParameters] = (Parameter)part;
					numOfParameters++;
				}
			}
		}

		public void CompactWithNoString() 
		{
			Parameter p1 = new Parameter( "p1" );
			Parameter p2 = new Parameter( "p2" );
			
			SqlString sql = new SqlString( new object[] {p1, p2} );
			SqlString compacted = sql.Compact();

			Assert.AreEqual( 2, compacted.SqlParts.Length );
			Assert.AreEqual( ":p1:p2", compacted.ToString() );

		}

		private void AddValueOrProjection(Parameter[] parameters, int paramIndex, ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters, SqlStringBuilder result)
		{
			if (_projection == null)
			{
				result.Add(parameters[paramIndex]);
			}
			else
			{
				SqlString sql = _projection.ToSqlString(criteria, GetHashCode(), criteriaQuery, enabledFilters);
				result.Add(SqlStringHelper.RemoveAsAliasesFromSql(sql));
			}
		}

        void ISqlStringVisitor.Parameter(Parameter parameter)
        {
            if (hasReturnParameter && !foundReturnParameter)
            {
                result.Append(parameter);
                foundReturnParameter = true;
                return;
            }

            string name = formatter.GetParameterName(parameter.ParameterPosition ?? ++parameterIndex);

            result.Append(name);
        }

		public void EqualsSameType()
		{
			Parameter x = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
			Parameter y = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
			Parameter z = new Parameter( "name", "alias", new SqlTypes.Int32SqlType() );
			
			Assert.IsTrue( x.Equals(y) );
			Assert.IsTrue( y.Equals(x) );
			Assert.IsTrue( y.Equals(z) );
			Assert.IsTrue( x.Equals(z) );
			Assert.IsFalse( x.Equals(null) );

			y = new Parameter( "name2", "alias", new SqlTypes.Int32SqlType() );

			Assert.IsFalse( x.Equals(y) );
			Assert.IsFalse( y.Equals(x) );
		}

		public void SelectStringSqlTest() 
		{
			Configuration cfg = new Configuration();
			ISessionFactory factory = cfg.BuildSessionFactory( );

			ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
			SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl);
			
			select.SetSelectClause("column1, column2");
			select.SetFromClause("select_test", "select_test_alias");
			select.SetOuterJoins( new SqlString(" LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1"), new SqlString(" LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1") );
			select.SetOrderByClause("column1 DESC");

			select.SetWhereClause("select_test_alias", new string[] {"identity_column"}, NHibernateUtil.Int64);
 
			SqlString sqlString = select.ToSqlString();

			string expectedSql = new StringBuilder().Append("SELECT ")
				.Append("column1, column2 ")
				.Append("FROM select_test select_test_alias ")
				.Append("LEFT OUTER JOIN before ON select_test_alias.column1 = before.column1 ")
				.Append("WHERE select_test_alias.identity_column = :select_test_alias.identity_column ")
				.Append("LEFT OUTER JOIN after ON select_test_alias.column1 = after.column1 ")
				.Append("ORDER BY column1 DESC")
				.ToString();
				

			int numOfParams = 0;
			Parameter expectedParam = null;

			foreach(object part in sqlString.SqlParts) 
			{
				if(part is Parameter) 
				{
					numOfParams++;
					expectedParam = (Parameter)part;
				}
			}

			Assert.AreEqual(expectedSql , sqlString.ToString(), "SQL String");
			Assert.AreEqual(1, numOfParams, "One parameter");

			Parameter firstParam = new Parameter( "identity_column", "select_test_alias", new SqlTypes.Int64SqlType() );
			Assert.AreEqual(firstParam.SqlType.DbType, expectedParam.SqlType.DbType, "First Parameter Type");
			Assert.AreEqual(firstParam.Name, expectedParam.Name, "First Parameter Name");	
		}

		public void NotSqlStringTest() 
		{
			ISession session = factory.OpenSession();
			
			NExpression.ICriterion notExpression = NExpression.Expression.Not(NExpression.Expression.Eq("Address", "12 Adress"));

			SqlString sqlString = notExpression.ToSqlString(factoryImpl, typeof(Simple), "simple_alias", BaseExpressionFixture.EmptyAliasClasses );

			string expectedSql = dialect is Dialect.MySQLDialect ?
				"not (simple_alias.address = :simple_alias.address)" :
				"not simple_alias.address = :simple_alias.address";
			
			Parameter firstParam = new Parameter( "address", "simple_alias", new SqlTypes.StringSqlType() );
			CompareSqlStrings(sqlString, expectedSql, new Parameter[] {firstParam});
			
			session.Close();
		}