C# SqlBuilder.AddConstraint方法代碼示例

		public void TestRangeConstraint()
		{
			SqlBuilder builder = new SqlBuilder( StatementType.Count, typeof(MailingList) );
			builder.AddConstraint( Operator.GreaterThanOrEquals, "Id", 2 );
			builder.AddConstraint( Operator.LessThan, "Id", 3 );
			int count = GetCount( builder );
			Assert.AreEqual( 1, count );
		}

		public void TestCRUD()
		{
			// skip test if GUIDs are not supported by database backend
			if( runTest )
			{
				o1 = new GuidHolder( 42 );
				// insert
				o1.Persist();
				// select
				o2 = GuidHolder.Retrieve( o1.Id );
				// verify select/insert
				Assert.IsNotNull( o2.Id, "The object could not be retrieved from the database!" );
				Assert.AreEqual( o1.Id, o2.Id, "The object could not be retrieved from the database!" );
				Assert.AreEqual( o1.SomeValue, o2.SomeValue, "The object could not be retrieved from the database!" );
				// update
				o2.SomeValue = 1234;
				o2.Persist();
				// verify update
				o1 = GuidHolder.Retrieve( o2.Id );
				Assert.AreEqual( o2.Id, o1.Id, "The object could not be retrieved from the database!" );
				// delete
				o2.Remove();
				// verify delete by counting the number of rows
				SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(GuidHolder) );
				sb.AddConstraint( Operator.Equals, "Id", o1.Id );
				SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
				Assert.AreEqual( 0, sr.Count, "Object not removed" );
			}
		}

		public void Final()
		{
			// make sure we have only the default 4 members 
			SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MemberSD) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 4 );
			Broker.Execute( sb.GetStatement( true ) );
			// remove the list
			list.Remove();
		}

		public void Init()
		{
			// make sure we have only 4 members 
			SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(Member) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 4 );
			Broker.Execute( sb.GetStatement( true ) );
			// make sure we have only 3 lists 
			sb = new SqlBuilder( StatementType.Delete, typeof(MailingList) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 3 );
			Broker.Execute( sb.GetStatement( true ) );
		}

		public void Init()
		{
			// make sure we have only 4 members 
			SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MemberCC) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 4 );
			Broker.Execute( sb.GetStatement( true ) );
			// make sure we have only 3 lists 
			sb = new SqlBuilder( StatementType.Delete, typeof(MailingList) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 3 );
			Broker.Execute( sb.GetStatement( true ) );
			// create an empty mailing list
			list = new MailingList( "SomeList", "[email protected]" );
			list.Persist();
		}

		public void TestCRUD_Dog()
		{
			o1 = new Dog();
			// insert
			o1.Persist();
			Assert.IsTrue( o1.Id > 0, "No identity assigned on insert." );
			// select
			o2 = Dog.Retrieve( o1.Id );
			// verify select/insert
			Assert.IsNotNull( o2.Id, "The object could not be retrieved from the database!" );
			Assert.AreEqual( o1.Id, o2.Id, "The object could not be retrieved from the database!" );
			// update
			o2.Persist();
			// verify update
			o1 = Dog.Retrieve( o2.Id );
			// delete
			o2.Remove();
			// verify delete by counting the number of rows
			SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(Dog) );
			sb.AddConstraint( Operator.Equals, "Id", o1.Id );
			SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
			Assert.AreEqual( 0, sr.Count, "Object not removed" );
		}

		public void TestCRUD()
		{
			m1 = new MemberSD( list.Id, "John Doe", "[email protected]" );
			// insert
			m1.Persist();
			Assert.AreEqual( m1.Name, "John Doe", "The object was not properly inserted!" );
			Assert.AreEqual( m1.Address, "[email protected]", "The object was not properly inserted!" );
			// select
			m2 = MemberSD.Retrieve( m1.Id );
			// verify select/insert
			Assert.IsTrue( m2.Id != 0, "The object could not be retrieved from the database!" );
			Assert.AreEqual( m1.Id, m2.Id, "The object could not be retrieved from the database!" );
			Assert.AreEqual( "John Doe", m2.Name, "The object was not properly retrieved on construction!" );
			Assert.AreEqual( "[email protected]", m2.Address,
			                 "The object was not properly retrieved on construction!" );
			// update
			m2.Name = "Jane Doe";
			m2.Address = "[email protected]";
			m2.Persist();
			// verify update
			m1 = MemberSD.Retrieve( m2.Id );
			Assert.AreEqual( m2.Name, m1.Name, "Name not updated!" );
			Assert.AreEqual( m2.Address, m1.Address, "SenderAddress not updated!" );
			// delete
			m2.Remove();
			// verify delete by counting the number of rows
			SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MemberSD) );
			sb.AddConstraint( Operator.Equals, "Id", m1.Id );
			SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
			Assert.AreEqual( 0, sr.Count, "Object not removed" );
		}

		public void TestCRUD()
		{
			a = new PropertyHolder( 0, "MyPH", 2, 3, 4, 5.0, true, DateTime.Now, DateTime.Now,
			                        "char", "nchar", "varchar", "nvarchar", "text", "ntext" );
			// insert
			a.Persist();
			Assert.AreEqual( a.Name, "MyPH" );
			// select
			b = PropertyHolder.Retrieve( a.Id );
			// verify select/insert
			Assert.IsTrue( b.Id != 0 );
			Assert.AreEqual( a.Id, b.Id );
			Assert.AreEqual( "MyPH", b.Name );
			// update
			b.Name = "NewPH";
			b.TDateTime = DateTime.MinValue; // should result in DBNull being written
			b.Persist();
			// verify update
			a = Broker.RetrieveInstance( typeof(PropertyHolder), b.GetKey() ) as PropertyHolder;
			Assert.AreEqual( b.Name, a.Name );
			Assert.AreEqual( DateTime.MinValue, a.TDateTime );
			// delete
			b.Remove();
			// verify delete by counting the number of rows
			SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(PropertyHolder) );
			sb.AddConstraint( Operator.Equals, "Id", a.Id );
			SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
			Assert.AreEqual( 0, sr.Count, "Object not removed" );
		}

		public void TestColumnInMultipleConstraints()
		{
			a = new PropertyHolder( 0, "MyPH", 2, 3, 4, 5.0, true, new DateTime( 2000, 1, 20 ), DateTime.Now,
			                        "char", "nchar", "varchar", "nvarchar", "text", "ntext" );
			// insert
			a.Persist();
			// select
			SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(PropertyHolder) );
			sb.AddConstraint( Operator.GreaterThan, "TDateTime", new DateTime( 1999, 1, 1 ) );
			sb.AddConstraint( Operator.LessThan, "TDateTime", new DateTime( 2001, 1, 1 ) );
			SqlStatement stmt = sb.GetStatement();
			SqlResult sr = stmt.Execute();
			Assert.AreEqual( 1, sr.RowsContained, "Statement did not fetch the expected row." );
		}

		public void TestCRUD()
		{
			if( GentleSettings.ConcurrencyControl )
			{
				GentleSettings.CacheObjects = false;
				CacheManager.Clear();
				m1 = new MemberCCSD( list.Id, "John Doe", "[email protected]" );
				// insert
				m1.Persist();
				Assert.AreEqual( m1.Name, "John Doe", "The object was not properly inserted!" );
				Assert.AreEqual( m1.Address, "[email protected]", "The object was not properly inserted!" );
				// select
				m2 = MemberCCSD.Retrieve( m1.Id );
				// verify select/insert
				Assert.IsTrue( m2.Id != 0, "The object could not be retrieved from the database!" );
				Assert.IsTrue( m2.Id > 4, "Existing id was reused!" );
				Assert.AreEqual( m1.Id, m2.Id, "The object could not be retrieved from the database!" );
				Assert.AreEqual( "John Doe", m2.Name, "The object was not properly retrieved on construction!" );
				Assert.AreEqual( "[email protected]", m2.Address,
				                 "The object was not properly retrieved on construction!" );
				Assert.AreEqual( m1.DatabaseVersion, m2.DatabaseVersion, "Database revision not retrieved!" );
				// update
				m2.Name = "Jane Doe";
				m2.Address = "[email protected]";
				m2.Persist();
				Assert.AreEqual( m1.DatabaseVersion + 1, m2.DatabaseVersion, "Database revision not incremented!" );
				// verify update
				m1 = MemberCCSD.Retrieve( m2.Id );
				Assert.AreEqual( m2.Name, m1.Name, "Name not updated!" );
				Assert.AreEqual( m2.Address, m1.Address, "SenderAddress not updated!" );
				Assert.AreEqual( m1.DatabaseVersion, m2.DatabaseVersion, "Database revision not retrieved!" );
				// delete
				m2.Remove();
				// verify delete by counting the number of rows
				SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MemberCCSD) );
				sb.AddConstraint( Operator.Equals, "Id", m1.Id );
				SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
				Assert.AreEqual( 0, sr.Count, "Object not removed" );
			}
		}

		public void TestCRUD()
		{
			MailingList l1, l2;
			l1 = new MailingList( "Test 1", "[email protected]" );
			// insert
			l1.Persist();
			Assert.IsTrue( l1.Id > 0, "The List object was not assigned an id by the database!" );
			Assert.AreEqual( l1.Name, "Test 1", "The List object was not properly inserted!" );
			Assert.AreEqual( l1.SenderAddress, "[email protected]", "The List object was not properly inserted!" );
			// select
			l2 = MailingList.Retrieve( l1.Id );
			// verify select/insert
			Assert.IsTrue( l2.Id != 0, "The List object could not be retrieved from the database!" );
			Assert.AreEqual( l1.Id, l2.Id, "The List object could not be retrieved from the database!" );
			Assert.AreEqual( "Test 1", l2.Name, "The List object was not properly retrieved on construction!" );
			Assert.AreEqual( "[email protected]", l2.SenderAddress,
			                 "The List object was not properly retrieved on construction!" );
			// update
			l2.Name = "Test 2";
			l2.SenderAddress = "[email protected]";
			l2.Persist();
			// verify update
			l1 = MailingList.Retrieve( l2.Id );
			Assert.AreEqual( l2.Name, l1.Name, "Name not updated!" );
			Assert.AreEqual( l2.SenderAddress, l1.SenderAddress, "SenderAddress not updated!" );
			// delete
			l2.Remove();
			// verify delete by counting the number of rows
			SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
			sb.AddConstraint( Operator.Equals, "Id", l1.Id );
			SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
			Assert.AreEqual( 0, sr.Count, "Object not removed" );
		}

		public void CleanUp()
		{
			SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MailingList) );
			sb.AddConstraint( Operator.GreaterThan, "Id", 3 );
			Broker.Execute( sb.GetStatement( true ) );
		}

		public void TestCRUD()
		{
			// skip test if picture data was not read (check the PATH constant above!)
			if( runTest && picture != null )
			{
				mp1 = new MemberPicture( picture, 1 );
				// insert
				mp1.Persist();
				Assert.AreEqual( mp1.MemberId, 1, "The object was not properly inserted!" );
				Assert.AreEqual( GetSize( mp1.Picture ), pictureSize, "The object was not properly inserted!" );
				// select
				mp2 = MemberPicture.Retrieve( mp1.Id );
				// verify select/insert
				Assert.IsNotNull( mp2.Id, "The object could not be retrieved from the database!" );
				Assert.AreEqual( mp1.Id, mp2.Id, "The object could not be retrieved from the database!" );
				Assert.AreEqual( pictureSize, GetSize( mp2.Picture ), "The object was not properly retrieved on construction!" );
				Assert.AreEqual( mp1.MemberId, mp2.MemberId,
				                 "The object was not properly retrieved on construction!" );
				// update
				mp2.MemberId = 2;
				mp2.Persist();
				// verify update
				mp1 = MemberPicture.Retrieve( mp2.Id );
				Assert.AreEqual( mp2.MemberId, mp1.MemberId, "MemberId not updated!" );
				// delete
				mp2.Remove();
				// verify delete by counting the number of rows
				SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MemberPicture) );
				sb.AddConstraint( Operator.Equals, "Id", mp1.Id );
				SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
				Assert.AreEqual( 0, sr.Count, "Object not removed" );
			}
		}

		public void TestRangeConstraintCustom()
		{
			SqlBuilder builder = new SqlBuilder( StatementType.Count, typeof(MailingList) );
			builder.AddConstraint( Operator.GreaterThanOrEquals, "Id", 2 );
			GentleSqlFactory sf = Broker.GetSqlFactory();
			string clause = String.Format( "{0} < 3", "ListId" );
			builder.AddConstraint( clause );
			int count = GetCount( builder );
			Assert.AreEqual( 1, count );
		}