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", "*****@*****.**" );
list.Persist();
}
/// <summary>
/// Get a list of RadioGroupMap referring to the current entity.
/// </summary>
public IList <RadioGroupMap> ReferringRadioGroupMap()
{
//select * from 'foreigntable'
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(RadioGroupMap));
// where foreigntable.foreignkey = ourprimarykey
sb.AddConstraint(Operator.Equals, "idChannel", idChannel);
// passing true indicates that we'd like a list of elements, i.e. that no primary key
// constraints from the type being retrieved should be added to the statement
SqlStatement stmt = sb.GetStatement(true);
// execute the statement/query and create a collection of User instances from the result set
return(ObjectFactory.GetCollection <RadioGroupMap>(stmt.Execute()));
// TODO In the end, a GentleList should be returned instead of an arraylist
//return new GentleList( typeof(GroupMap), this );
}
public List <Person> WhoHadAttended(Event anEvent)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Attendance));
sb.AddConstraint(Operator.Equals, "id_event", anEvent.Id);
SqlStatement stmt = sb.GetStatement(true);
IList attendances = ObjectFactory.GetCollection(typeof(Attendance), stmt.Execute());
List <Person> persons = new List <Person>();
foreach (Attendance at in attendances)
{
persons.Add(at.Person);
}
return(persons);
}
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);
}
}
public EventType Retrieve(string name)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(EventType));
sb.AddConstraint(Operator.Equals, "name", name);
SqlStatement stmt = sb.GetStatement();
EventType anEventType = null;
try {
anEventType =
(EventType)ObjectFactory.GetInstance(typeof(EventType), stmt.Execute());
}
catch (Gentle.Common.GentleException) {
throw new EventTypeDoesNotExistException();
}
return(anEventType);
}
public void OnActivated()
{
try
{
Application.DoEvents();
Cursor.Current = Cursors.WaitCursor;
UpdateMenuAndTabs();
listView1.Items.Clear();
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(GroupMap));
sb.AddConstraint(Operator.Equals, "idGroup", _channelGroup.IdGroup);
sb.AddOrderByField(true, "sortOrder");
SqlStatement stmt = sb.GetStatement(true);
IList <GroupMap> maps = ObjectFactory.GetCollection <GroupMap>(stmt.Execute());
foreach (GroupMap map in maps)
{
Channel channel = map.ReferencedChannel();
if (!channel.IsTv)
{
continue;
}
listView1.Items.Add(CreateItemForChannel(channel, map));
}
bool isAllChannelsGroup = (_channelGroup.GroupName == TvConstants.TvGroupNames.AllChannels);
removeChannelFromGroup.Enabled = !isAllChannelsGroup;
mpButtonDel.Enabled = !isAllChannelsGroup;
}
catch (Exception exp)
{
Log.Error("OnActivated error: {0}", exp.Message);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
public Event[] RetrieveLast(int numberOfEvents)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Event));
sb.SetRowLimit(numberOfEvents);
sb.AddOrderByField(false, "date");
sb.AddConstraint(Operator.LessThanOrEquals, "date", DateTime.Now);
SqlStatement stmt = sb.GetStatement(true);
IList events = ObjectFactory.GetCollection(typeof(Event), stmt.Execute());
List <Event> events_result = new List <Event>();
foreach (Event e in events)
{
events_result.Add(e);
}
return(events_result.ToArray());
}
public void TestCRUD()
{
if (GentleSettings.ConcurrencyControl)
{
GentleSettings.CacheObjects = false;
CacheManager.Clear();
m1 = new MemberCCSD(list.Id, "John Doe", "*****@*****.**");
// insert
m1.Persist();
Assert.AreEqual(m1.Name, "John Doe", "The object was not properly inserted!");
Assert.AreEqual(m1.Address, "*****@*****.**", "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("*****@*****.**", 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 = "*****@*****.**";
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");
}
}
private static int GetChannelIdByDisplayName(string aChannelName)
{
int channelId = -1;
if (string.IsNullOrEmpty(aChannelName))
{
return(channelId);
}
try
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Equals, "displayName", aChannelName);
sb.SetRowLimit(1);
SqlStatement stmt = sb.GetStatement(true);
IList <Channel> channels = ObjectFactory.GetCollection <Channel>(stmt.Execute());
if (channels.Count > 0)
{
channelId = (channels[0]).IdChannel;
}
else
{
sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Like, "displayName", "%" + aChannelName + "%");
sb.SetRowLimit(1);
stmt = sb.GetStatement(true);
channels = ObjectFactory.GetCollection <Channel>(stmt.Execute());
if (channels.Count > 0)
{
channelId = (channels[0]).IdChannel;
}
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Could not get ChannelID for DisplayName: {0}\n{1}", aChannelName, ex.Message));
}
return(channelId);
}
public List <WebRecording> GetAllRecordings(string title)
{
List <WebRecording> recInfos = new List <WebRecording>();
if (!ConnectToDatabase())
{
return(recInfos);
}
SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Recording));
sb.AddConstraint(Operator.Like, "title", title + "%");
SqlStatement stmt = sb.GetStatement(true);
IList recordings = ObjectFactory.GetCollection(typeof(Recording), stmt.Execute());
if (recordings != null && recordings.Count > 0)
{
foreach (Recording rec in recordings)
{
recInfos.Add(new WebRecording(rec));
}
}
return(recInfos);
}
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" );
}
/// <summary>
/// Get all channels from the database
/// </summary>
private void RefreshAllChannels()
{
Cursor.Current = Cursors.WaitCursor;
IList <Card> dbsCards = Card.ListAll();
_cards = new Dictionary <int, CardType>();
foreach (Card card in dbsCards)
{
_cards[card.IdCard] = RemoteControl.Instance.Type(card.IdCard);
}
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Equals, "isTv", true);
sb.AddOrderByField(true, "sortOrder");
SqlStatement stmt = sb.GetStatement(true);
_allChannels = ObjectFactory.GetCollection <Channel>(stmt.Execute());
tabControl1.TabPages[0].Text = string.Format("Channels ({0})", _allChannels.Count);
_lvChannelHandler = new ChannelListViewHandler(mpListView1, _allChannels, _cards, txtFilterString, ChannelType.Tv);
_lvChannelHandler.FilterListView("");
}
/// <summary>
/// Retrieves an entity given it's filename.
/// </summary>
public static Recording Retrieve(string fileName)
{
// Return null if id is smaller than seed and/or increment for autokey
if (string.IsNullOrEmpty(fileName))
{
return(null);
}
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Recording));
sb.AddConstraint(Operator.Equals, "fileName", fileName);
SqlStatement stmt = sb.GetStatement(true);
// execute the statement/query and create a collection of User instances from the result set
IList <Recording> getList = ObjectFactory.GetCollection <Recording>(stmt.Execute());
if (getList.Count != 0)
{
return(getList[0]);
}
return(null);
}
/// <summary>
/// gets a value from the database table "Setting"
/// </summary>
/// <returns>A Setting object with the stored value, if it doesnt exist the given default string will be the value</returns>
private Setting GetSetting(string tagName, string defaultValue)
{
if (defaultValue == null)
{
return(null);
}
if (tagName == null)
{
return(null);
}
if (tagName == "")
{
return(null);
}
SqlBuilder sb;
try
{
sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Setting));
}
catch (TypeInitializationException)
{
return(new Setting(tagName, defaultValue));
}
sb.AddConstraint(Operator.Equals, "tag", tagName);
SqlStatement stmt = sb.GetStatement(true);
IList <Setting> settingsFound = ObjectFactory.GetCollection <Setting>(stmt.Execute());
if (settingsFound.Count == 0)
{
Setting set = new Setting(tagName, defaultValue);
set.Persist();
return(set);
}
return(settingsFound[0]);
}
public void TestCRUD()
{
MailingList l1, l2;
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 void TestCRUD()
{
MailingList l1, l2;
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 void TestCRUD()
{
m1 = new MemberSD( list.Id, "John Doe", "*****@*****.**" );
// insert
m1.Persist();
Assert.AreEqual( m1.Name, "John Doe", "The object was not properly inserted!" );
Assert.AreEqual( m1.Address, "*****@*****.**", "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( "*****@*****.**", m2.Address,
"The object was not properly retrieved on construction!" );
// update
m2.Name = "Jane Doe";
m2.Address = "*****@*****.**";
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 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 );
}
public void ReLoad()
{
//System.Diagnostics.Debugger.Launch();
try
{
SetupDatabaseConnection();
Log.Info("get channels from database");
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddConstraint(Operator.Equals, "isTv", 1);
sb.AddOrderByField(true, "sortOrder");
SqlStatement stmt = sb.GetStatement(true);
channels = ObjectFactory.GetCollection(typeof(Channel), stmt.Execute());
Log.Info("found:{0} tv channels", channels.Count);
TvNotifyManager.OnNotifiesChanged();
m_groups.Clear();
TvBusinessLayer layer = new TvBusinessLayer();
RadioChannelGroup allRadioChannelsGroup =
layer.GetRadioChannelGroupByName(TvConstants.RadioGroupNames.AllChannels);
IList <Channel> radioChannels = layer.GetAllRadioChannels();
if (radioChannels != null)
{
if (radioChannels.Count > allRadioChannelsGroup.ReferringRadioGroupMap().Count)
{
foreach (Channel radioChannel in radioChannels)
{
layer.AddChannelToRadioGroup(radioChannel, allRadioChannelsGroup);
}
}
}
Log.Info("Done.");
Log.Info("get all groups from database");
sb = new SqlBuilder(StatementType.Select, typeof(ChannelGroup));
sb.AddOrderByField(true, "groupName");
stmt = sb.GetStatement(true);
IList <ChannelGroup> groups = ObjectFactory.GetCollection <ChannelGroup>(stmt.Execute());
IList <GroupMap> allgroupMaps = GroupMap.ListAll();
bool hideAllChannelsGroup = false;
using (
Settings xmlreader =
new MPSettings())
{
hideAllChannelsGroup = xmlreader.GetValueAsBool("mytv", "hideAllChannelsGroup", false);
}
foreach (ChannelGroup group in groups)
{
if (group.GroupName == TvConstants.TvGroupNames.AllChannels)
{
foreach (Channel channel in channels)
{
if (channel.IsTv == false)
{
continue;
}
bool groupContainsChannel = false;
foreach (GroupMap map in allgroupMaps)
{
if (map.IdGroup != group.IdGroup)
{
continue;
}
if (map.IdChannel == channel.IdChannel)
{
groupContainsChannel = true;
break;
}
}
if (!groupContainsChannel)
{
layer.AddChannelToGroup(channel, TvConstants.TvGroupNames.AllChannels);
}
}
break;
}
}
groups = ChannelGroup.ListAll();
foreach (ChannelGroup group in groups)
{
//group.GroupMaps.ApplySort(new GroupMap.Comparer(), false);
if (hideAllChannelsGroup && group.GroupName.Equals(TvConstants.TvGroupNames.AllChannels) && groups.Count > 1)
{
continue;
}
m_groups.Add(group);
}
Log.Info("loaded {0} tv groups", m_groups.Count);
//TVHome.Connected = true;
}
catch (Exception ex)
{
Log.Error("TVHome: Error in Reload");
Log.Error(ex);
//TVHome.Connected = false;
}
}
/// <summary>
/// Get all channels in a group, sorted by display name.
/// </summary>
/// <param name="channelType">The type of channels in the group.</param>
/// <param name="groupId">The group ID or -1 for all channels.</param>
/// <returns>A list containing zero or more channels.</returns>
public static List <TvDatabase.Channel> GetAllChannelsInGroup(ChannelType channelType, int groupId)
{
List <TvDatabase.Channel> channels = new List <TvDatabase.Channel>();
if (groupId < 0)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(TvDatabase.Channel));
if (channelType == ChannelType.Television)
{
sb.AddConstraint(Operator.Equals, "isTv", true);
}
else
{
sb.AddConstraint(Operator.Equals, "isRadio", true);
}
sb.AddConstraint(Operator.Equals, "visibleInGuide", true);
sb.AddOrderByField("displayName");
SqlResult result = Broker.Execute(sb.GetStatement());
channels = (List <TvDatabase.Channel>)
ObjectFactory.GetCollection(typeof(TvDatabase.Channel), result, new List <TvDatabase.Channel>());
}
else
{
if (channelType == ChannelType.Television)
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(TvDatabase.GroupMap));
sb.AddConstraint(Operator.Equals, "idGroup", groupId);
SqlResult result = Broker.Execute(sb.GetStatement());
List <TvDatabase.GroupMap> groupMaps = (List <TvDatabase.GroupMap>)
ObjectFactory.GetCollection(typeof(TvDatabase.GroupMap), result, new List <TvDatabase.GroupMap>());
foreach (TvDatabase.GroupMap groupMap in groupMaps)
{
TvDatabase.Channel mpChannel = groupMap.ReferencedChannel();
if (mpChannel.IsTv)
{
channels.Add(mpChannel);
}
}
}
else
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(TvDatabase.RadioGroupMap));
sb.AddConstraint(Operator.Equals, "idGroup", groupId);
SqlResult result = Broker.Execute(sb.GetStatement());
List <TvDatabase.RadioGroupMap> groupMaps = (List <TvDatabase.RadioGroupMap>)
ObjectFactory.GetCollection(typeof(TvDatabase.RadioGroupMap), result, new List <TvDatabase.RadioGroupMap>());
foreach (TvDatabase.RadioGroupMap groupMap in groupMaps)
{
TvDatabase.Channel mpChannel = groupMap.ReferencedChannel();
if (mpChannel.IsRadio)
{
channels.Add(mpChannel);
}
}
}
channels.Sort(
delegate(TvDatabase.Channel c1, TvDatabase.Channel c2) { return(c1.DisplayName.CompareTo(c2.DisplayName)); });
}
return(channels);
}
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()
{
// 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" );
}
}
private void buttonRefresh_Click(object sender, EventArgs e)
{
String name = null;
try
{
textBoxAction.Text = "Loading";
this.Refresh();
Log.Debug("Loading all channels from the tvguide[s]");
// used for partial matches
TstDictionary guideChannels = new TstDictionary();
Dictionary <string, Channel> guideChannelsExternald = new Dictionary <string, Channel>();
List <Channel> lstTvGuideChannels = readChannelsFromTVGuide();
if (lstTvGuideChannels == null)
{
return;
}
// convert to Dictionary
foreach (Channel ch in lstTvGuideChannels)
{
string tName = ch.DisplayName.Replace(" ", "").ToLowerInvariant();
if (!guideChannels.ContainsKey(tName))
{
guideChannels.Add(tName, ch);
}
// used to make sure that the available mapping is used by default
if (ch.ExternalId != null && !ch.ExternalId.Trim().Equals(""))
{
// need to check this because we can have channels with multiple display-names
// and they're currently handles as one channel/display-name.
// only in the mapping procedure of course
if (!guideChannelsExternald.ContainsKey(ch.ExternalId))
{
guideChannelsExternald.Add(ch.ExternalId, ch);
}
}
}
Log.Debug("Loading all channels from the database");
CBChannelGroup chGroup = (CBChannelGroup)comboBoxGroup.SelectedItem;
IList <Channel> channels;
bool loadRadio = checkBoxLoadRadio.Checked;
if (chGroup != null && chGroup.idGroup != -1)
{
SqlBuilder sb1 = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Channel));
SqlStatement stmt1 = sb1.GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(stmt1.StatementType, stmt1.Command,
String.Format(
"select c.* from Channel c join GroupMap g on c.idChannel=g.idChannel where " +
(loadRadio ? "" : " c.isTv = 1 and ") +
" g.idGroup = '{0}' and c.visibleInGuide = 1 order by g.sortOrder", chGroup.idGroup),
typeof(Channel));
channels = ObjectFactory.GetCollection <Channel>(ManualJoinSQL.Execute());
}
else
{
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Channel));
sb.AddOrderByField(true, "sortOrder");
if (!loadRadio)
{
sb.AddConstraint(" isTv = 1");
}
sb.AddConstraint(" visibleInGuide = 1");
SqlStatement stmt = sb.GetStatement(true);
channels = ObjectFactory.GetCollection <Channel>(stmt.Execute());
}
progressBar1.Minimum = 0;
progressBar1.Maximum = channels.Count;
progressBar1.Value = 0;
dataGridChannelMappings.Rows.Clear();
int row = 0;
if (channels.Count == 0)
{
MessageBox.Show("No tv-channels available to map");
return;
}
// add as many rows in the datagrid as there are channels
dataGridChannelMappings.Rows.Add(channels.Count);
DataGridViewRowCollection rows = dataGridChannelMappings.Rows;
// go through each channel and try to find a matching channel
// 1: matching display-name (non case-sensitive)
// 2: partial search on the first word. The first match will be selected in the dropdown
foreach (Channel ch in channels)
{
Boolean alreadyMapped = false;
DataGridViewRow gridRow = rows[row++];
DataGridViewTextBoxCell idCell = (DataGridViewTextBoxCell)gridRow.Cells["Id"];
DataGridViewTextBoxCell channelCell = (DataGridViewTextBoxCell)gridRow.Cells["tuningChannel"];
DataGridViewTextBoxCell providerCell = (DataGridViewTextBoxCell)gridRow.Cells["tuningChannel"];
DataGridViewCheckBoxCell showInGuideCell = (DataGridViewCheckBoxCell)gridRow.Cells["ShowInGuide"];
channelCell.Value = ch.DisplayName;
idCell.Value = ch.IdChannel;
showInGuideCell.Value = ch.VisibleInGuide;
DataGridViewComboBoxCell guideChannelComboBox = (DataGridViewComboBoxCell)gridRow.Cells["guideChannel"];
// always add a empty item as the first option
// these channels will not be updated when saving
guideChannelComboBox.Items.Add("");
// Start by checking if there's an available mapping for this channel
Channel matchingGuideChannel = null;
if (guideChannelsExternald.ContainsKey(ch.ExternalId))
{
matchingGuideChannel = guideChannelsExternald[ch.ExternalId];
alreadyMapped = true;
}
// no externalid mapping available, try using the name
if (matchingGuideChannel == null)
{
string tName = ch.DisplayName.Replace(" ", "").ToLowerInvariant();
if (guideChannels.ContainsKey(tName))
{
matchingGuideChannel = (Channel)guideChannels[tName];
}
}
Boolean exactMatch = false;
Boolean partialMatch = false;
if (!alreadyMapped)
{
if (matchingGuideChannel != null)
{
exactMatch = true;
}
else
{
// No name mapping found
// do a partial search, default off
if (checkBoxPartialMatch.Checked)
{
// do a search using the first word(s) (skipping the last) of the channelname
name = ch.DisplayName.Trim();
int spaceIdx = name.LastIndexOf(" ");
if (spaceIdx > 0)
{
name = name.Substring(0, spaceIdx).Trim();
}
else
{
// only one word so we'll do a partial match on the first 3 letters
if (name.Length > 3)
{
name = name.Substring(0, 3);
}
}
try
{
// Note: the partial match code doesn't work as described by the author
// so we'll use PrefixMatch method (created by a codeproject user)
ICollection partialMatches = guideChannels.PrefixMatch(name.Replace(" ", "").ToLowerInvariant());
if (partialMatches != null && partialMatches.Count > 0)
{
IEnumerator pmE = partialMatches.GetEnumerator();
pmE.MoveNext();
matchingGuideChannel = (Channel)guideChannels[(string)pmE.Current];
partialMatch = true;
}
}
catch (Exception ex)
{
Log.Error("Error while searching for matching guide channel :" + ex.Message);
}
}
}
}
// add the channels
// set the first matching channel in the search above as the selected
Boolean gotMatch = false;
string ALREADY_MAPPED = "Already mapped (got external id)";
string EXACT_MATCH = "Exact match";
string PARTIAL_MATCH = "Partial match";
string NO_MATCH = "No match";
DataGridViewCell cell = gridRow.Cells["matchType"];
foreach (DictionaryEntry de in guideChannels)
{
Channel guideChannel = (Channel)de.Value;
String itemText = guideChannel.DisplayName + " (" + guideChannel.ExternalId + ")";
guideChannelComboBox.Items.Add(itemText);
if (!gotMatch && matchingGuideChannel != null)
{
if (guideChannel.DisplayName.ToLowerInvariant().Equals(matchingGuideChannel.DisplayName.ToLowerInvariant()))
{
// set the matchtype row color according to the type of match(already mapped,exact, partial, none)
if (alreadyMapped)
{
cell.Style.BackColor = Color.White;
cell.ToolTipText = ALREADY_MAPPED;
// hack so that we can order the grid by mappingtype
cell.Value = "";
}
else if (exactMatch)
{
cell.Style.BackColor = Color.Green;
cell.ToolTipText = EXACT_MATCH;
cell.Value = " ";
}
else if (partialMatch)
{
cell.Style.BackColor = Color.Yellow;
cell.ToolTipText = PARTIAL_MATCH;
cell.Value = " ";
}
guideChannelComboBox.Value = itemText;
guideChannelComboBox.Tag = ch.ExternalId;
gotMatch = true;
}
}
}
if (!gotMatch)
{
cell.Style.BackColor = Color.Red;
cell.ToolTipText = NO_MATCH;
cell.Value = " ";
}
progressBar1.Value++;
}
textBoxAction.Text = "Finished";
}
catch (Exception ex)
{
Log.Error("Failed loading channels/mappings : channel {0} erro {1} ", name, ex.Message);
Log.Error(ex.StackTrace);
textBoxAction.Text = "Error";
}
}
public void TestCRUD()
{
if( GentleSettings.ConcurrencyControl )
{
GentleSettings.CacheObjects = false;
CacheManager.Clear();
m1 = new MemberCCSD( list.Id, "John Doe", "*****@*****.**" );
// insert
m1.Persist();
Assert.AreEqual( m1.Name, "John Doe", "The object was not properly inserted!" );
Assert.AreEqual( m1.Address, "*****@*****.**", "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( "*****@*****.**", 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 = "*****@*****.**";
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" );
}
}
protected virtual void InitList(Type viaType, params Type[] relationTypes)
{
if (listType == GentleListType.StandAlone)
{
broker.RetrieveList(containedMap.Type, this);
}
else if (listType == GentleListType.OneToMany)
{
// no relation objects for 1:n relationships
viaInstances = null;
mappings = containedMap.GetForeignKeyMappings(parentMap, true);
if (mappings.Count == 0)
{
mappings = viaMap.GetForeignKeyMappings(parentMap, false);
}
Check.Verify(mappings.Count > 0, Error.DeveloperError,
"The type {0} does not contain a foreign key reference to type {1}.",
parentMap.Type, containedMap.Type);
Check.Verify(mappings.Count == 1, Error.NotImplemented,
"GentleList for 1:n relations can not be used with composite keys.");
// populate self with any existing entries matching the current parent
Key key = new Key(parentMap.Type, true);
IDictionaryEnumerator iterator = mappings.GetEnumerator();
while (iterator.MoveNext())
{
// construct a key to read the data; first obtain the referenced value from
// the parent object (this is the constraint value used in the select)
FieldMap fm = iterator.Value as FieldMap;
object referencedValue = fm.GetValue(parent);
// if class references self make sure to pick the outgoing column
if (containedMap.Type == parentMap.Type && !fm.IsForeignKey)
{
fm = iterator.Key as FieldMap;
}
key[fm.MemberName] = referencedValue;
}
broker.RetrieveList(containedMap.Type, key, this);
}
else if (listType == GentleListType.ManyToMany)
{
// create relation for n:m management
Type[] relatedTypes = Merge(containedMap.Type, relationTypes);
viaInstances = new GentleRelation(broker, viaType, parent, relatedTypes);
// populate the list with any existing entries matching the current relation entries
ObjectMap viaMap = ObjectFactory.GetMap(broker, viaType);
SqlBuilder sb = new SqlBuilder(broker, StatementType.Select, containedMap.Type);
// assume the relation object is the child, i.e. refers to the contained type
mappings = viaMap.GetForeignKeyMappings(containedMap, true);
if (mappings.Count == 0)
{
mappings = viaMap.GetForeignKeyMappings(containedMap, false);
}
Check.Verify(mappings.Count > 0, Error.DeveloperError,
"The type {0} does not contain a foreign key reference to type {1}.",
viaMap.Type, containedMap.Type);
Check.Verify(mappings.Count == 1, Error.NotImplemented,
"GentleList for n:m relations can not be used with composite keys.");
// verify that references point to unique instance
//Check.Verify( mappings.Count == parentMap.PrimaryKeyCount, Error.DeveloperError,
// "The number of fields ({0}) referencing {1} from {2} must equal the primary key count ({3}).",
// mappings.Count, parentMap.Type, containedMap.Type, parentMap.PrimaryKeyCount );
if (viaInstances.Count > 0)
{
foreach (FieldMap remote in mappings.Keys)
{
FieldMap local = (FieldMap)mappings[remote];
// viaMap.GetForeignKeyFieldMap( containedMap.Type, local.PropertyName );
sb.AddConstraint(Operator.In, local.MemberName, viaInstances, remote.MemberName);
}
ObjectFactory.GetCollection(containedMap.Type, sb.GetStatement(true).Execute(), this);
}
}
}
public void CleanUp()
{
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(MailingList) );
sb.AddConstraint( Operator.GreaterThan, "Id", 3 );
Broker.Execute( sb.GetStatement( true ) );
}
