C# SqlBuilder.AddOrderByField方法代码示例

    private void InitChannels()
    {
      listViewChannels.Clear();
      listViewChannels.BeginUpdate();
      try
      {
        SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof (Channel));
        if (checkBoxGuideChannels.Checked)
        {
          sb.AddConstraint(Operator.Equals, "visibleInGuide", 1);
        }
        sb.AddConstraint(Operator.Equals, "isTv", 1);
        sb.AddOrderByField(true, "sortOrder");
        sb.AddOrderByField(true, "displayName");
        SqlStatement stmt = sb.GetStatement(true);
        IList<Channel> channels = ObjectFactory.GetCollection<Channel>(stmt.Execute());

        for (int i = 0; i < channels.Count; i++)
        {
          // TODO: add imagelist with channel logos from MP :)
          ListViewItem curItem = new ListViewItem(channels[i].DisplayName);
          curItem.Tag = channels[i];
          listViewChannels.Items.Add(curItem);
        }
      }
      finally
      {
        listViewChannels.EndUpdate();
      }
      mpButtonOk.Enabled = (listViewChannels.Items.Count > 0);
    }

    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;

        if (_channelGroup.GroupName != TvConstants.TvGroupNames.AllChannels)
        {
          labelPinCode.Visible = true;
          textBoxPinCode.Visible = true;
          textBoxPinCode.Text = _channelGroup.PinCode;
        }

      }
      catch (Exception exp)
      {
        Log.Error("OnActivated error: {0}", exp.Message);
      }
      finally
      {
        Cursor.Current = Cursors.Default;
      }
    }

 public List<WebProgram> GetEPGForChannel(string idChannel,string startTime,string endTime)
 {
     List<WebProgram> infos = new List<WebProgram>();
       if (!ConnectToDatabase())
     return infos;
       DateTime dtStart; DateTime dtEnd;
       if (!DateTime.TryParse(startTime,out dtStart))
     return infos;
       if (!DateTime.TryParse(endTime, out dtEnd))
     return infos;
       IFormatProvider mmddFormat = new System.Globalization.CultureInfo(String.Empty, false);
       try
       {
     SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Program));
     sb.AddConstraint(Operator.Equals, "idChannel", Int32.Parse(idChannel));
     sb.AddConstraint(String.Format("startTime>='{0}'", dtStart.ToString(GetDateTimeString(), mmddFormat)));
     sb.AddConstraint(String.Format("endTime<='{0}'", dtEnd.ToString(GetDateTimeString(), mmddFormat)));
     sb.AddOrderByField(true, "startTime");
     SqlStatement stmt = sb.GetStatement(true);
     IList programs = ObjectFactory.GetCollection(typeof(Program), stmt.Execute());
     if (programs != null && programs.Count > 0)
     {
       foreach (Program prog in programs)
     infos.Add(new WebProgram(prog));
     }
       }
       catch (Exception)
       {
       }
       return infos;
 }

        public List<Program> GetEPGForChannel(string idChannel, DateTime startTime, DateTime endTime)
        {
            IFormatProvider mmddFormat = new CultureInfo(String.Empty, false);
            //List<Program> infos = new List<Program>();

            try
            {
                SqlBuilder sb = new SqlBuilder(Gentle.Framework.StatementType.Select, typeof(Program));
                sb.AddConstraint(Operator.Equals, "idChannel", Int32.Parse(idChannel));
                if (startTime >= DateTime.Now)
                {
                    DateTime thisMorning = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
                    sb.AddConstraint(String.Format("startTime>='{0}'", thisMorning.ToString(GetDateTimeString(), mmddFormat)));
                }
                else
                {
                    sb.AddConstraint(String.Format("startTime>='{0}'", startTime.ToString(GetDateTimeString(), mmddFormat)));
                }
                if (endTime>DateTime.Now)
                {
                    sb.AddConstraint(String.Format("endTime<='{0}'", endTime.ToString(GetDateTimeString(), mmddFormat)));
                }
                sb.AddOrderByField(true, "startTime");
                SqlStatement stmt = sb.GetStatement(true);
                IList programs = ObjectFactory.GetCollection(typeof(Program), stmt.Execute());
                return (List<Program>) programs;
            }
            catch(Exception e)
            {
                Console.WriteLine("Error while obtaining the EPG for channel " + idChannel + ": " + e.Message);
                Log.Debug("TVServerKodi: Error while obtaining the EPG for channel " + idChannel + ": " + e.Message);
            }
            return null;
        }

    protected override void OnPageLoad()
    {
      Log.Debug("zaposd pageload");
      // following line should stay. Problems with OSD not
      // appearing are already fixed elsewhere
      SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
      sb.AddConstraint(Operator.Equals, "istv", 1);
      sb.AddOrderByField(true, "sortOrder");
      SqlStatement stmt = sb.GetStatement(true);
      ObjectFactory.GetCollection(typeof (Channel), stmt.Execute());

      AllocResources();
      // if (g_application.m_pPlayer) g_application.m_pPlayer.ShowOSD(false);
      ResetAllControls(); // make sure the controls are positioned relevant to the OSD Y offset
      m_bNeedRefresh = false;
      m_dateTime = DateTime.Now;
      channelNr = GetChannelNumber();
      channelName = GetChannelName();
      idChannel = GetIdChannel();
      SetCurrentChannelLogo();
      base.OnPageLoad();

      GUIPropertyManager.SetProperty("#currentmodule", GUILocalizeStrings.Get(100000 + GetID));
    }

 public DateTime GetNewestProgramForChannel(int idChannel)
 {
   SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Program));
   sb.AddConstraint(Operator.Equals, "idChannel", idChannel);
   sb.AddOrderByField(false, "startTime");
   sb.SetRowLimit(1);
   SqlStatement stmt = sb.GetStatement(true);
   IList<Program> progs = ObjectFactory.GetCollection<Program>(stmt.Execute());
   return progs.Count > 0 ? progs[0].StartTime : DateTime.MinValue;
 }

 public IList<Card> ListAllEnabledCardsOrderedByPriority()
 {
   SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Card));
   sb.AddConstraint("enabled=1");
   sb.AddOrderByField(false, "priority");
   SqlStatement stmt = sb.GetStatement(true);
   return ObjectFactory.GetCollection<Card>(stmt.Execute());
 }

    public override bool OnMessage(GUIMessage message)
    {
      switch (message.Message)
      {
        case GUIMessage.MessageType.GUI_MSG_WINDOW_DEINIT: // fired when OSD is hidden
          {
            //if (g_application.m_pPlayer) g_application.m_pPlayer.ShowOSD(true);
            // following line should stay. Problems with OSD not
            // appearing are already fixed elsewhere
            //for (int i = (int)Controls.Panel1; i < (int)Controls.Panel2; i++)
            //{
            //  HideControl(GetID, i);
            //}
            Dispose();
            GUIPropertyManager.SetProperty("#currentmodule", GUIWindowManager.GetWindow(message.Param1).GetModuleName());
            return true;
          }


        case GUIMessage.MessageType.GUI_MSG_WINDOW_INIT: // fired when OSD is shown
          {
            // following line should stay. Problems with OSD not
            // appearing are already fixed elsewhere
            SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel));
            sb.AddConstraint(Operator.Equals, "istv", 1);
            sb.AddOrderByField(true, "sortOrder");
            SqlStatement stmt = sb.GetStatement(true);
            listTvChannels = ObjectFactory.GetCollection(typeof (Channel), stmt.Execute());
            GUIPropertyManager.SetProperty("#currentmodule", GetModuleName());
            previousProgram = null;
            AllocResources();
            // if (g_application.m_pPlayer) g_application.m_pPlayer.ShowOSD(false);
            ResetAllControls(); // make sure the controls are positioned relevant to the OSD Y offset
            isSubMenuVisible = false;
            m_iActiveMenuButtonID = 0;
            m_iActiveMenu = 0;
            m_bNeedRefresh = false;
            m_dateTime = DateTime.Now;
            Reset();
            FocusControl(GetID, (int)Controls.OSD_PLAY, 0); // set focus to play button by default when window is shown
            ShowPrograms();
            QueueAnimation(AnimationType.WindowOpen);
            for (int i = (int)Controls.Panel1; i < (int)Controls.Panel2; i++)
            {
              ShowControl(GetID, i);
            }
            if (g_Player.Paused)
            {
              ToggleButton((int)Controls.OSD_PLAY, true);
              // make sure play button is down (so it shows the pause symbol)
            }
            else
            {
              ToggleButton((int)Controls.OSD_PLAY, false); // make sure play button is up (so it shows the play symbol)
            }
            m_delayInterval = MediaPortal.Player.Subtitles.SubEngine.GetInstance().DelayInterval;
            if (m_delayInterval > 0)
              m_subtitleDelay = MediaPortal.Player.Subtitles.SubEngine.GetInstance().Delay / m_delayInterval;
            m_delayIntervalAudio = PostProcessingEngine.GetInstance().AudioDelayInterval;
            if (m_delayIntervalAudio > 0)
              m_audioDelay = PostProcessingEngine.GetInstance().AudioDelay / m_delayIntervalAudio;

            g_Player.UpdateMediaInfoProperties();
            GUIPropertyManager.SetProperty("#TV.View.HasTeletext", TVHome.Card.HasTeletext.ToString());

            MediaPortal.Player.VideoStreamFormat videoFormat = g_Player.GetVideoFormat();

            GUIPropertyManager.SetProperty("#Play.Current.TSBitRate",
             ((float)MediaPortal.Player.g_Player.GetVideoFormat().bitrate / 1024 / 1024).ToString("0.00", CultureInfo.InvariantCulture));

            GUIPropertyManager.SetProperty("#Play.Current.VideoFormat.RawResolution",
              videoFormat.width.ToString() + "x" + videoFormat.height.ToString());

            GUIPropertyManager.SetProperty("#TV.TuningDetails.FreeToAir", string.Empty);

            Channel chan = TVHome.Navigator.Channel;
            if (chan != null)
            {
              IList<TuningDetail> details = chan.ReferringTuningDetail();
              if (details.Count > 0)
              {
                TuningDetail detail = null;
                switch (TVHome.Card.Type)
                {
                  case TvLibrary.Interfaces.CardType.Analog:
                    foreach (TuningDetail t in details)
                    {
                      if (t.ChannelType == 0)
                        detail = t;
                    }
                    break;
                  case TvLibrary.Interfaces.CardType.Atsc:
                    foreach (TuningDetail t in details)
                    {
                      if (t.ChannelType == 1)
                        detail = t;
                    }
                    break;
                  case TvLibrary.Interfaces.CardType.DvbC:
                    foreach (TuningDetail t in details)
//.........这里部分代码省略.........

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

    /// <summary>
    /// Get a list of GroupMap 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, "idGroup", idGroup);
      sb.AddOrderByField("SortOrder");

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

    private void LoadDirectory()
    {
      GUIControl.ClearControl(GetID, listPriorities.GetID);
      SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Schedule));
      sb.AddOrderByField(false, "priority");
      SqlStatement stmt = sb.GetStatement(true);
      IList itemlist = ObjectFactory.GetCollection(typeof (Schedule), stmt.Execute());

      int total = 0;
      foreach (Schedule rec in itemlist)
      {
        if (rec.IsSerieIsCanceled(rec.StartTime, rec.IdChannel))
        {
          continue;
        }
        GUIListItem item = new GUIListItem();
        item.Label = String.Format("{0}.{1}", total, rec.ProgramName);
        item.TVTag = rec;
        string strLogo = Utils.GetCoverArt(Thumbs.TVChannel, rec.ReferencedChannel().DisplayName);
        if (string.IsNullOrEmpty(strLogo))                      
        {
          strLogo = "defaultVideoBig.png";
        }
        TvServer server = new TvServer();
        VirtualCard card;
        if (server.IsRecordingSchedule(rec.IdSchedule, out card))
        {
          if (rec.ScheduleType != (int)ScheduleRecordingType.Once)
          {
            item.PinImage = Thumbs.TvRecordingSeriesIcon;
          }
          else
          {
            item.PinImage = Thumbs.TvRecordingIcon;
          }
        }
        else if (rec.ReferringConflicts().Count > 0)
        {
          item.PinImage = Thumbs.TvConflictRecordingIcon;
        }
        item.ThumbnailImage = strLogo;
        item.IconImageBig = strLogo;
        item.IconImage = strLogo;
        listPriorities.Add(item);
        total++;
      }

      //set object count label
      GUIPropertyManager.SetProperty("#itemcount", Utils.GetObjectCountLabel(total));

      GUIControl.SelectItemControl(GetID, listPriorities.GetID, m_iSelectedItem);
    }

    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}' 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");
          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"];
//.........这里部分代码省略.........

 /// <summary>
 /// Static method to retrieve all instances that are stored in the database in one call
 /// </summary>
 public static IList<RadioChannelGroup> ListAll()
 {
   SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (RadioChannelGroup));
   sb.AddOrderByField(true, "sortOrder");
   SqlStatement stmt = sb.GetStatement(true);
   return ObjectFactory.GetCollection<RadioChannelGroup>(stmt.Execute());
 }

 /// <summary>
 /// Get a list of ChannelGroup referring to the current entity.
 /// </summary>
 public IList<GroupMap> ReferringChannelGroup()
 {
   SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (KeywordMap));
   sb.AddConstraint(Operator.Equals, "idKeyword", idKeyword);
   sb.AddOrderByField("SortOrder");
   SqlStatement stmt = sb.GetStatement(true);
   List<GroupMap> returnList = new List<GroupMap>();
   IList<KeywordMap> list = ObjectFactory.GetCollection<KeywordMap>(stmt.Execute());
   foreach (KeywordMap map in list)
   {
     ChannelGroup group = map.ReferencedChannelGroup();
     returnList.InsertRange(0, group.ReferringGroupMap());
   }
   return returnList;
 }

 /// <summary>
 /// Get a list of Timespan referring to the current entity.
 /// </summary>
 public IList<Timespan> ReferringTimespan()
 {
   SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Timespan));
   sb.AddConstraint(Operator.Equals, "idKeyword", idKeyword);
   sb.AddOrderByField("SortOrder");
   SqlStatement stmt = sb.GetStatement(true);
   return ObjectFactory.GetCollection<Timespan>(stmt.Execute());
 }