How To Do Native SQL Queries in TVE35 plugins

Discussion in 'Plugin Development' started by huha, September 30, 2013.

  1. huha
    • Premium Supporter

    huha Extension Developer

    Joined:
    January 3, 2008
    Messages:
    890
    Likes Received:
    550
    Gender:
    Male
    Ratings:
    +622 / 0
    Home Country:
    Germany Germany
    Show System Specs
    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.
     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. morpheus_xx
    • Team MediaPortal

    morpheus_xx Lead Dev MP2

    Joined:
    March 24, 2007
    Messages:
    11,017
    Likes Received:
    4,749
    Ratings:
    +6,795 / 11
    Home Country:
    Germany Germany
    Show System Specs
  4. morpheus_xx
    • Team MediaPortal

    morpheus_xx Lead Dev MP2

    Joined:
    March 24, 2007
    Messages:
    11,017
    Likes Received:
    4,749
    Ratings:
    +6,795 / 11
    Home Country:
    Germany Germany
    Show System Specs
  5. huha
    • Premium Supporter

    huha Extension Developer

    Joined:
    January 3, 2008
    Messages:
    890
    Likes Received:
    550
    Gender:
    Male
    Ratings:
    +622 / 0
    Home Country:
    Germany Germany
    Show System Specs
    morpeus_xx,
    thanks a lot! This was exactly what i was looking for.
    Now i found the GenericRepository class.
     
  6. gibman
    • Premium Supporter

    gibman Retired Team Member

    Joined:
    October 4, 2006
    Messages:
    2,998
    Likes Received:
    1,321
    Occupation:
    Developer
    Location:
    Aarhus
    Ratings:
    +1,372 / 0
    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
     
    • Thank You! Thank You! x 1
  7. huha
    • Premium Supporter

    huha Extension Developer

    Joined:
    January 3, 2008
    Messages:
    890
    Likes Received:
    550
    Gender:
    Male
    Ratings:
    +622 / 0
    Home Country:
    Germany Germany
    Show System Specs
    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();
    }
    }
     
  8. gibman
    • Premium Supporter

    gibman Retired Team Member

    Joined:
    October 4, 2006
    Messages:
    2,998
    Likes Received:
    1,321
    Occupation:
    Developer
    Location:
    Aarhus
    Ratings:
    +1,372 / 0
    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.
     
    • Thank You! Thank You! x 1
  9. huha
    • Premium Supporter

    huha Extension Developer

    Joined:
    January 3, 2008
    Messages:
    890
    Likes Received:
    550
    Gender:
    Male
    Ratings:
    +622 / 0
    Home Country:
    Germany Germany
    Show System Specs
    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.
     
    • Like Like x 2
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!