How To Do Native SQL Queries in TVE35 plugins (1 Viewer)

huha

Extension Developer
January 3, 2008
890
556
Home Country
Germany Germany
I do ask for help on how to do native sql queries from a TVE35 plugin.

With the old TV server in MP1 I could do a native SQL query like

StringBuilder SqlSelectCommand = new StringBuilder();
SqlSelectCommand.Append("select * from Recording ");
SqlSelectCommand.AppendFormat(string.Format("where {0}", Expression));
SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof(Recording)).GetStatement(true);
SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(), typeof(Recording));
myRecordingList = ObjectFactory.GetCollection<Recording>(ManualJoinSQL.Execute());
Log.Debug("SQL Result:myRecordingList.Count" + myRecordingList.Count.ToString());

For the old server there was a definition of SqlStatement, Objectfactory and SqlBuilder in Gentle.Framework, which i could not find for the new TvE35.

As this is my biggest roadblock on the path to write my first plugin for TVE35 I would appreciate any help here.

Another issue is the deletion of a setting, but I guess if I could do a general SQL query i could do the deletion from there.
 

huha

Extension Developer
January 3, 2008
890
556
Home Country
Germany Germany
  • Thread starter
  • Moderator
  • #4
morpeus_xx,
thanks a lot! This was exactly what i was looking for.
Now i found the GenericRepository class.
 

gibman

Retired Team Member
  • Premium Supporter
  • October 4, 2006
    2,998
    1,372
    Aarhus
    Home Country
    Denmark Denmark
    hi..

    yeah, GenericRepository holds all the "generic stuff" for accessing entities.
    you are adviced to create your own repository class if you find yourself in a situation where you need to extend it with more linq queries etc.

    there are lots of repository classes that you can look at.

    regards
    gibman
     

    huha

    Extension Developer
    January 3, 2008
    890
    556
    Home Country
    Germany Germany
    • Thread starter
    • Moderator
    • #6
    gibman,
    i made some progress with TvWishlist, but I am now running into another roadblock:
    I have defined my own class Setting which includes its own repository.
    The function Remove should delete a setting by creating a new repository in DeleteSettings.
    Tracing the log there is an entry "f", but no more entry "a" and the setting is not getting deleted.
    Do i need to define a new ServiceAgent for that first? Can I do that from within a plugin, as I saw that all service agents are registered within the main code from the class.
    Needless to say that i am a complete newby on this part.



    public void Remove()
    {
    Log.Debug("f");
    DeleteSettings(this.Tag);
    Log.Debug("e");
    }



    private static void DeleteSettings(string tagName)
    {
    using (ISettingsRepository settingRepository = new SettingsRepository(true))
    {
    Log.Debug("a");
    settingRepository.Delete<Mediaportal.TV.Server.TVDatabase.Entities.Setting>(s => s.Tag == tagName);
    Log.Debug("b");
    settingRepository.UnitOfWork.SaveChanges();
    Log.Debug("c");

    }
    }

    public static IList<Setting> QuerySettings()
    {
    using (ISettingsRepository settingRepository = new SettingsRepository(true))
    {
    IQueryable<Setting> query = settingRepository.GetQuery<Setting>(s => s.Tag == "*"); //should return all settings from the data base in a list
    settingRepository.UnitOfWork.SaveChanges();
    return query.ToList();
    }
    }
     

    gibman

    Retired Team Member
  • Premium Supporter
  • October 4, 2006
    2,998
    1,372
    Aarhus
    Home Country
    Denmark Denmark
    Hi.

    In order to reach the db from a plugins standpoint you have to first establish where the code is running. In what context.

    The part of the plugin that runs in setup.. the configurable part needs to use wcf in order to reach the db. The service agents.

    If we are talking about the part that runs inside the tvservice context then you can bypass wcf and just call the repository class.

    There are lots of examples onthese 2 different approaches in some of the plugins.
     

    huha

    Extension Developer
    January 3, 2008
    890
    556
    Home Country
    Germany Germany
    • Thread starter
    • Moderator
    • #8
    gibman,
    a big thanks to your response, which was the key for me. I am starting to understand the method now and just completed my first general SQL query on a program search with the ObjectContext.ExecuteStoreQuery. I am also able to delete settings and list all settings, so this was really a breakthrough. Still a lot of work ahead, but this part was my biggest worry.
     

    Users who are viewing this thread

    Top Bottom