C# SqlSelectBuilder.SetOrderByClause方法代碼示例

		private void InitStatementString(IOuterJoinLoadable elementPersister, string alias, int batchSize, SqlString subquery)
		{
			int joins = CountEntityPersisters(associations);
			Suffixes = BasicLoader.GenerateSuffixes(joins + 1);

			int collectionJoins = CountCollectionPersisters(associations) + 1;
			CollectionSuffixes = BasicLoader.GenerateSuffixes(joins + 1, collectionJoins);

			SqlStringBuilder whereString = WhereString(alias, oneToManyPersister.KeyColumnNames, subquery, batchSize);
			string filter = oneToManyPersister.FilterFragment(alias, EnabledFilters);
			whereString.Insert(0, StringHelper.MoveAndToBeginning(filter));

			JoinFragment ojf = MergeOuterJoins(associations);
			SqlSelectBuilder select =
				new SqlSelectBuilder(Factory).SetSelectClause(
					oneToManyPersister.SelectFragment(null, null, alias, Suffixes[joins], CollectionSuffixes[0], true)
					+ SelectString(associations)).SetFromClause(elementPersister.FromTableFragment(alias)
					                                            + elementPersister.FromJoinFragment(alias, true, true)).SetWhereClause(
					whereString.ToSqlString()).SetOuterJoins(ojf.ToFromFragmentString,
					                                         ojf.ToWhereFragmentString
					                                         + elementPersister.WhereJoinFragment(alias, true, true));

			select.SetOrderByClause(OrderBy(associations, oneToManyPersister.GetSQLOrderByString(alias)));

			if (Factory.Settings.IsCommentsEnabled)
			{
				select.SetComment("load one-to-many " + oneToManyPersister.Role);
			}

			SqlString = select.ToSqlString();
		}

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

		private void InitStatementString(string alias, int batchSize, SqlString subquery)
		{
			int joins = CountEntityPersisters(associations);
			int collectionJoins = CountCollectionPersisters(associations) + 1;
			Suffixes = BasicLoader.GenerateSuffixes(joins);
			CollectionSuffixes = BasicLoader.GenerateSuffixes(joins, collectionJoins);

			SqlStringBuilder whereString = WhereString(alias, collectionPersister.KeyColumnNames, subquery, batchSize);

			string manyToManyOrderBy = string.Empty;
			string filter = collectionPersister.FilterFragment(alias, EnabledFilters);

			if (collectionPersister.IsManyToMany)
			{
				// from the collection of associations, locate OJA for the
				// ManyToOne corresponding to this persister to fully
				// define the many-to-many; we need that OJA so that we can
				// use its alias here
				// TODO : is there a better way here?
				IAssociationType associationType = (IAssociationType)collectionPersister.ElementType;
				foreach (OuterJoinableAssociation oja in associations)
				{
					if (oja.JoinableType == associationType)
					{
						// we found it
						filter += collectionPersister.GetManyToManyFilterFragment(oja.RHSAlias, EnabledFilters);
						manyToManyOrderBy += collectionPersister.GetManyToManyOrderByString(oja.RHSAlias);
					}
				}
			}

			whereString.Insert(0, StringHelper.MoveAndToBeginning(filter));
			JoinFragment ojf = MergeOuterJoins(associations);

			SqlSelectBuilder select =
				new SqlSelectBuilder(Factory)
				.SetSelectClause(collectionPersister.SelectFragment(alias, CollectionSuffixes[0])
				                                              + SelectString(associations))
				.SetFromClause(collectionPersister.TableName, alias)
				.SetWhereClause(whereString.ToSqlString())
				.SetOuterJoins(ojf.ToFromFragmentString, ojf.ToWhereFragmentString);

			select.SetOrderByClause(OrderBy(associations, MergeOrderings(collectionPersister.GetSQLOrderByString(alias), manyToManyOrderBy)));

			if (Factory.Settings.IsCommentsEnabled)
				select.SetComment("load collection " + collectionPersister.Role);

			SqlString = select.ToSqlString();
		}

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

		private void InitStatementString( IQueryableCollection persister, string alias, IList associations, int batchSize, ISessionFactoryImplementor factory )
		{
			Suffixes = GenerateSuffixes( associations.Count );

			SqlStringBuilder whereString = WhereString( factory, alias, persister.KeyColumnNames, persister.KeyType, batchSize );
			if( persister.HasWhere )
			{
				whereString
					.Add( " and " )
					.Add( persister.GetSQLWhereString( alias ) );
			}

			JoinFragment ojf = MergeOuterJoins( associations );
			SqlSelectBuilder select = new SqlSelectBuilder( factory )
				.SetSelectClause(
				persister.SelectFragment( alias ).Append(
					SelectString( associations, factory ) ).ToString()
				)
				.SetFromClause( persister.TableName, alias )
				.SetWhereClause( whereString.ToSqlString() )
				.SetOuterJoins(
				ojf.ToFromFragmentString,
				ojf.ToWhereFragmentString
				);

			if( persister.HasOrdering )
			{
				select.SetOrderByClause( persister.GetSQLOrderByString( alias ) );
			}
			SqlString = select.ToSqlString();
		}