/// <summary>
/// Verify parameter order when using unnamed parameters
/// </summary>
public void ExecuteTestUnnamedParameterOrder( string providerName )
{
GentleSettings.AnalyzerLevel = AnalyzerLevel.None;
PersistenceBroker broker = new PersistenceBroker( providerName, "..." );
GentleSqlFactory sf = broker.GetSqlFactory();
if( ! sf.HasCapability( Capability.NamedParameters ) )
{
// create parameterized query
SqlBuilder sb = new SqlBuilder( broker, StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.Equals, "SenderAddress", "SenderAddress" );
sb.AddConstraint( Operator.Equals, "Name", "Name" );
SqlStatement stmt = sb.GetStatement( true );
foreach( IDataParameter param in stmt.Command.Parameters )
{
Assert.IsTrue( param.ParameterName.IndexOf( (string) param.Value ) >= 0, "1: Parameter order not correctly maintained." );
}
// retry using parameters in reverse order
sb = new SqlBuilder( broker, StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.Equals, "Name", "Name" );
sb.AddConstraint( Operator.Equals, "SenderAddress", "SenderAddress" );
stmt = sb.GetStatement( true );
foreach( IDataParameter param in stmt.Command.Parameters )
{
Assert.IsTrue( param.ParameterName.IndexOf( (string) param.Value ) >= 0, "2: Parameter order not correctly maintained." );
}
}
}
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 ) );
GentleSettings.CacheObjects = false;
CacheManager.Clear();
}
public void TestCRUD()
{
l1 = new MailingList( "Test 1", "*****@*****.**" );
// 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, "*****@*****.**", "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( "*****@*****.**", l2.SenderAddress,
"The List object was not properly retrieved on construction!" );
// update
l2.Name = "Test 2";
l2.SenderAddress = "*****@*****.**";
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 virtual void TestNullNotEquals()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(Member) );
sb.AddConstraint( Operator.NotEquals, "MemberName", null );
SqlStatement stmt = sb.GetStatement( true );
}
public void TestCustomListUsingInConstraint()
{
// subselects not supported by the inferior mysql engine - skip test.
if( ! Broker.ProviderName.Equals( "MySQL" ) )
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
string sql = String.Format( "select distinct MemberAddress from {0}",
sf.GetTableName( "ListMember" ) );
sb.AddConstraint( Operator.In, "SenderAddress", Broker.Execute( sql ), "MemberAddress" );
SqlResult sr = Broker.Execute( sb.GetStatement() );
int expected = sr.Count;
// verify list retrieval (using IList data)
IList lists = MailingList.ListByCustomListConstraint();
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
// verify same result using alternative approach (using SqlResult data)
sb = new SqlBuilder( StatementType.Select, typeof(Member) );
SqlResult members = sb.GetStatement( true ).Execute();
sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
// use column name as indexer into SqlResult for list constraints
sb.AddConstraint( Operator.In, "SenderAddress", members, "MemberAddress" );
SqlStatement stmt = sb.GetStatement( true );
lists = ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
}
}
public void TestCustomListUsingLikeConstraint()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
sb.AddConstraint( Operator.Like, "SenderAddress", "%.com" );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
int expected = sr.Count;
// verify list retrieval
IList lists = MailingList.ListByDomain( ".com" );
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
}
public void TestOtherTypeListRetrieval()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// select all lists
IList lists = MailingList.ListAll;
foreach( MailingList list in lists )
{
// get the expected member count
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(Member) );
sb.AddConstraint( Operator.Equals, "ListId", list.Id );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
int expected = sr.Count;
// verify list retrieval (entire table)
IList members = list.Members;
Assert.IsNotNull( members );
Assert.AreEqual( expected, members.Count );
}
}
/// <summary>
/// Example list retrieval of objects using a custom "in" constraint.
/// </summary>
/// <returns>A list of MailingList objects</returns>
public static IList ListByCustomListConstraint()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.In, "SenderAddress", Member.ListAll, "Address" );
SqlStatement stmt = sb.GetStatement( true );
return ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
}
/// <summary>
/// Example list retrieval of objects using a custom "like" constraint.
/// </summary>
/// <param name="domain">A partial (right-side exact) string to match for senderaddress</param>
/// <returns>A list of MailingList objects</returns>
public static IList ListByDomain( string domain )
{
SqlBuilder sb = new SqlBuilder( StatementType.Select );
sb.AddConstraint( String.Format( "SenderAddress LIKE '%{0}'", domain ) );
SqlStatement stmt = sb.GetStatement( StatementType.Select, typeof(MailingList), true );
return ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
}