C# SqlSelectBuilder.SetWhereClause方法代码示例

		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(" after.some_field = after.another_field "));
			select.SetOrderByClause(new SqlString("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 ")
				.Append("after.some_field = after.another_field")
				.Append(" AND ")
				.Append("select_test_alias.identity_column = ? ")
				.Append("ORDER BY column1 DESC")
				.ToString();


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

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

		protected SqlString RenderSelect(int[] tableNumbers, int[] columnNumbers, int[] formulaNumbers)
		{
			Array.Sort(tableNumbers); //get 'em in the right order (not that it really matters)

			//render the where and from parts
			int drivingTable = tableNumbers[0];
			string drivingAlias = GenerateTableAlias(RootAlias, drivingTable); //we *could* regenerate this inside each called method!
			SqlString where = CreateWhereByKey(drivingTable, drivingAlias);
			string from = CreateFrom(drivingTable, drivingAlias);

			//now render the joins
			JoinFragment jf = CreateJoin(tableNumbers, drivingAlias);

			//now render the select clause
			SelectFragment selectFragment = CreateSelect(columnNumbers, formulaNumbers);

			//now tie it all together
			SqlSelectBuilder select = new SqlSelectBuilder(Factory);
			select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));
			select.SetFromClause(from);
			select.SetWhereClause(where);
			select.SetOuterJoins(jf.ToFromFragmentString, jf.ToWhereFragmentString);
			if (Factory.Settings.IsCommentsEnabled)
			{
				select.SetComment("sequential select " + EntityName);
			}
			return select.ToSqlString();
		}

		protected SqlString GenerateIdInsertSelect(IQueryable persister, string tableAlias, IASTNode whereClause)
		{
			var select = new SqlSelectBuilder(Factory);
			SelectFragment selectFragment = new SelectFragment(Factory.Dialect)
				.AddColumns(tableAlias, persister.IdentifierColumnNames, persister.IdentifierColumnNames);
			select.SetSelectClause(selectFragment.ToFragmentString().Substring(2));

			string rootTableName = persister.TableName;
			SqlString fromJoinFragment = persister.FromJoinFragment(tableAlias, true, false);
			SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false);

			select.SetFromClause(rootTableName + ' ' + tableAlias + fromJoinFragment);

			if (whereJoinFragment == null)
			{
				whereJoinFragment = SqlString.Empty;
			}
			else
			{
				whereJoinFragment = whereJoinFragment.Trim();
				if (whereJoinFragment.StartsWithCaseInsensitive("and "))
				{
					whereJoinFragment = whereJoinFragment.Substring(4);
				}
			}

			SqlString userWhereClause = SqlString.Empty;
			if (whereClause.ChildCount != 0)
			{
				// If a where clause was specified in the update/delete query, use it to limit the
				// returned ids here...
				try
				{
					var nodes = new CommonTreeNodeStream(whereClause);
					var gen = new SqlGenerator(Factory, nodes);
					gen.whereClause();
					userWhereClause = gen.GetSQL().Substring(7);
				}
				catch (RecognitionException e)
				{
					throw new HibernateException("Unable to generate id select for DML operation", e);
				}
				if (whereJoinFragment.Length > 0)
				{
					whereJoinFragment.Append(" and ");
				}
			}

			select.SetWhereClause(whereJoinFragment + userWhereClause);

			var insert = new InsertSelect();
			if (Factory.Settings.IsCommentsEnabled)
			{
				insert.SetComment("insert-select for " + persister.EntityName + " ids");
			}
			insert.SetTableName(persister.TemporaryIdTableName);
			insert.SetSelect(select);
			return insert.ToSqlString();
		}