/// <summary> /// Execute a custom SQL statement. /// </summary> /// <param name="stmt">The statement to execute, wrapped in a SqlStatement object</param> /// <param name="dbConnection">An existing database connection to reuse. This is useful /// when you need to execute statements in the same session as a previous /// statement.</param> /// <param name="dbTransaction">The database transaction for when participating in transactions</param> /// <returns>An SqlResult instance</returns> public SqlResult Execute(SqlStatement stmt, IDbConnection dbConnection, IDbTransaction dbTransaction) { if (dbTransaction != null) { return(stmt.Execute(dbTransaction.Connection, dbTransaction)); } else { return(stmt.Execute(dbConnection != null ? dbConnection : stmt.SessionBroker.Provider.GetConnection(), null)); } }
/// <summary> /// Retrieve multiple instances of the given type. The retrieved rows are limited by the fields /// and values specified in the given <see cref="Key"/> instance. /// </summary> /// <typeparam name="T">The type of objects to retrieve</typeparam> /// <param name="key">The key of the objects to retrieve</param> /// <param name="result">An optional existing container in which to store the created objects. If /// this parameter is null a new IList instance will be created.</param> /// <param name="conn">An existing database connection to reuse. This is useful /// when you need to execute statements in the same session as a previous statement.</param> /// <param name="tr">The database transaction for when participating in transactions.</param> /// <returns>An array containing the created object instances</returns> public IList <T> RetrieveList <T>(Key key, IList <T> result, IDbConnection conn, IDbTransaction tr) { SqlResult sr; SqlStatement stmt = GetRetrieveListStatement(typeof(T), key); // check cache before execution IList <T> cache = GetCacheResult <T>(stmt.CacheKey); if (cache != null) { if (result != null) { return(ListCopy <T>(cache, result)); } return(cache); } // no cache result, proceed as normal conn = tr != null ? tr.Connection : conn ?? stmt.SessionBroker.Provider.GetConnection(); sr = stmt.Execute(conn, tr); if (sr.ErrorCode == 0) { return(ObjectFactory.GetCollection <T>(sr, result)); } Check.Fail(Error.StatementError, "Unable to retrieve list of objects", sr.Statement.Sql); return(null); }
/// <summary> /// Retrieve data for the specified type. Throws an exception for unsupported types. /// </summary> /// <param name="type">The type of object</param> /// <param name="key">The key indentifying the object</param> /// <param name="conn">An existing database connection to reuse. This is useful /// when you need to execute statements in the same session as a previous statement.</param> /// <param name="tr">The database transaction for when participating in transactions.</param> /// <returns>An SqlResult containing the returned rows and helper methods</returns> public SqlResult Retrieve(Type type, Key key, IDbConnection conn, IDbTransaction tr) { SqlStatement stmt = GetRetrieveStatement(type, key); // connections are supplied from outside when in a transaction or executing batch queries conn = tr != null ? tr.Connection : conn ?? stmt.SessionBroker.Provider.GetConnection(); SqlResult sr = stmt.Execute(conn, tr); // require that operation succeeded and a valid result if (IsPrimaryKeyForType(type, key)) { Check.Verify(sr.ErrorCode == 0, Error.NoSuchRecord, type, key, sr.Error); Check.Verify(sr.RowsContained == 1, Error.UnexpectedRowCount, sr.RowsContained, 1); } return(sr); }
/// <summary> /// Retrieve an instance of the given type and identified by the given key using the /// supplied connection and transaction. /// </summary> /// <param name="type">The type of object to create</param> /// <param name="key">The key of the object to retrieve</param> /// <param name="conn">An existing database connection to reuse. This is useful /// when you need to execute statements in the same session as a previous statement.</param> /// <param name="tr">The database transaction for when participating in transactions.</param> /// <returns>The created object instance</returns> public object RetrieveInstance(Type type, Key key, IDbConnection conn, IDbTransaction tr) { SqlStatement stmt = GetRetrieveStatement(type, key); // check cache before execution if needed if (GentleSettings.CacheObjects && ObjectFactory.GetMap(this, type).CacheStrategy != CacheStrategy.Never) { IList cache = GetCacheResult(stmt.CacheKey); if (cache != null && cache.Count == 1) { return(cache[0]); } } // no cache result, proceed as normal conn = tr != null ? tr.Connection : conn ?? stmt.SessionBroker.Provider.GetConnection(); SqlResult sr = stmt.Execute(conn, tr); if (sr.ErrorCode == 0) { return(ObjectFactory.GetInstance(type, sr, key)); } Check.Fail(Error.NoSuchRecord, type, key, sr.Error); return(null); }
public IList<Program> GetProgramsByTitle(DateTime startTime, DateTime endTime, string title) { IFormatProvider mmddFormat = new CultureInfo(String.Empty, false); string StartTimeString = startTime.ToString(GetDateTimeString(), mmddFormat); string EndTimeString = endTime.ToString(GetDateTimeString(), mmddFormat); StringBuilder SqlSelectCommand = new StringBuilder(); SqlSelectCommand.Append("select p.* from Program p inner join Channel c on c.idChannel = p.idChannel "); SqlSelectCommand.AppendFormat( "where ((EndTime > '{0}' and EndTime < '{1}') or (StartTime >= '{0}' and StartTime <= '{1}') or (StartTime <= '{0}' and EndTime >= '{1}')) and title = '{2}' and c.visibleInGuide = 1 order by startTime", StartTimeString, EndTimeString, EscapeSQLString(title) ); SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof (Program)).GetStatement(true); SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(), typeof (Program)); return ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute()); }
private void getTvServerChannels() { CBChannelGroup chGroup = (CBChannelGroup)GroupComboBox.SelectedItem; IList<Channel> Channels; 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 {0}GroupMap g on c.idChannel=g.idChannel where c.{1} = 1 and g.idGroup = '{2}' order by g.sortOrder", IsTvMapping ? "" : "Radio", IsTvMapping ? "isTv" : "isRadio", chGroup.idGroup), typeof (Channel)); Channels = ObjectFactory.GetCollection<Channel>(ManualJoinSQL.Execute()); } else { SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel)); sb.AddOrderByField(true, "sortOrder"); if (IsTvMapping) { sb.AddConstraint("isTv = 1"); } else { sb.AddConstraint("isRadio = 1"); } SqlStatement stmt = sb.GetStatement(true); Channels = ObjectFactory.GetCollection<Channel>(stmt.Execute()); } foreach (Channel chan in Channels) { if (!_channelMapping.ContainsKey(chan.DisplayName)) { ChannelMap channel = new ChannelMap(); channel.displayName = chan.DisplayName; _channelMapping.Add(chan.DisplayName, channel); } } }
public IList<Channel> GetChannelsInGroup(ChannelGroup group) { SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel)); SqlStatement origStmt = sb.GetStatement(true); string sql = "SELECT c.* FROM Channel c INNER JOIN GroupMap gm ON (c.idChannel = gm.idChannel AND gm.idGroup =" + group.IdGroup + ") ORDER BY gm.SortOrder ASC"; SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql, typeof (Channel)); return ObjectFactory.GetCollection<Channel>(statement.Execute()); }
/// <summary> /// Internal helper method used for standard CRUD operations on known types. /// </summary> /// <param name="obj">The object instance being operated on</param> /// <param name="st">The statement type</param> /// <param name="conn">An existing database connection to reuse. This is useful /// when you need to execute statements in the same session as a previous statement.</param> /// <param name="tr">The database transaction for when participating in transactions.</param> /// <returns>The result of the operation</returns> internal SqlResult Execute(object obj, StatementType st, IDbConnection conn, IDbTransaction tr) { ObjectMap map = ObjectFactory.GetMap(this, obj); // perform validations first if (StatementType.Insert == st || StatementType.Update == st) { ValidationBroker.Validate(obj); } if (map.IsSoftDelete && st == StatementType.Delete) { st = StatementType.SoftDelete; } SqlStatement stmt = GetStatement(obj, st); stmt.SetParameters(obj, true); // connections are supplied from outside when in a transaction or executing batch queries conn = tr != null ? tr.Connection : conn ?? stmt.SessionBroker.Provider.GetConnection(); SqlResult sr = stmt.Execute(conn, tr); // throw an error if execution failed Check.Verify(sr.ErrorCode == 0, Error.StatementError, sr.Error, stmt.Sql); // check that statement affected a row if (st == StatementType.Insert || st == StatementType.Update || st == StatementType.Delete || st == StatementType.SoftDelete) { Check.Verify(sr.RowsAffected >= 1, Error.UnexpectedRowCount, sr.RowsAffected, "1+"); } // update identity values for inserts if (st == StatementType.Insert) { if (sr.LastRowId != 0 && map.IdentityMap != null) { map.SetIdentity(obj, sr.LastRowId); } if (obj is IEntity) { (obj as IEntity).IsPersisted = true; } // this is not really necessary but nice for error checking (also used in test cases) if (map.InheritanceMap != null) { map.InheritanceMap.SetValue(obj, obj.GetType().AssemblyQualifiedName); } } // update/invalidate the cache if (GentleSettings.CacheObjects && map.CacheStrategy != CacheStrategy.Never) { if (st == StatementType.Insert) { // insert new object into cache CacheManager.Insert(map.GetInstanceHashKey(obj), obj, map.CacheStrategy); // invalidate query results for select statements for this type, reducing the // effectiveness of the cache (but ensuring correct results) if (GentleSettings.CacheStatements) { CacheManager.ClearQueryResultsByType(map.Type); } } else if (st == StatementType.Delete || st == StatementType.SoftDelete) { // remove invalidated object from the cache CacheManager.Remove(obj); // invalidate query results for select statements for this type, reducing the // effectiveness of the cache (but ensuring correct results) if (GentleSettings.CacheStatements) { CacheManager.ClearQueryResultsByType(map.Type); } } } // update the in-memory version/revision counter for objects under concurrency control if (st == StatementType.Update && GentleSettings.ConcurrencyControl) { if (map.ConcurrencyMap != null) { FieldMap fm = map.ConcurrencyMap; long version = Convert.ToInt64(fm.GetValue(obj)); // handle wrap-around of the version counter if ((fm.Type.Equals(typeof(int)) && version == int.MaxValue) || (fm.Type.Equals(typeof(long)) && version == long.MaxValue)) { version = 1; } else { version += 1; } map.ConcurrencyMap.SetValue(obj, version); } } // update object with database-created values if UpdateAfterWrite is set to true if (map.IsUpdateAfterWrite && (st == StatementType.Insert || st == StatementType.Update)) { if (tr != null) { Refresh(obj, tr); } else { Refresh(obj); } } return(sr); }
//------------------------------------------------------------------------------------------------------------- // Search epg data and find all matching programs //------------------------------------------------------------------------------------------------------------- // matching programs are searched based on available information for program name, start time, endtime, displayname or groupname // if dispalyname is defined, groupnames will be ignored // partial name = true will handle partial name matches from program name // the routine will return an IList<Program> matchingprograms which contains all EPG programs of Type Program // the routine will not do any checking for conflicts of the list //------------------------------------------------------------------------------------------------------------- public bool SqlQueryRecordings(TvWish mywish, int counter) { LogDebug("TvWishList: Autocomplete search for matching recordings", (int)LogSetting.DEBUG); if (mywish.b_active == false) { Log.Debug("Tvwish is inactive - returning"); return true; } string SearchIn = mywish.s_SearchIn.ToLower(); String Expression = String.Empty; IFormatProvider mmddFormat = new CultureInfo(String.Empty, false); IList<Recording> myRecordingList = null; /* search names are case invariant (title SOKO equal to title soko) * * LIKE * NOT LIKE % A substitute for zero or more characters _ A substitute for exactly one character [charlist] Any single character in charlist [^charlist] or [!charlist] Any single character not in charlist * * * * SELECT column_name(s) FROM table_name WHERE column_name operator value Operators Allowed in the WHERE Clause With the WHERE clause, the following operators can be used: Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns * * * Combine with brackets () * and with AND or OR Note: In some versions of SQL the <> operator may be written as != * * * Recording columns(use lower case in sql quesry): public string Description { get; set; } public DateTime EndTime { get; set; } public string EpisodeName { get; set; } public string EpisodeNum { get; set; } public string EpisodeNumber { get; } public string EpisodePart { get; set; } public string FileName { get; set; } public string Genre { get; set; } public int IdChannel { get; set; } public int IdRecording { get; } public int Idschedule { get; set; } public int IdServer { get; set; } public bool IsChanged { get; } public bool IsManual { get; } public bool IsRecording { get; set; } public int KeepUntil { get; set; } public DateTime KeepUntilDate { get; set; } public string SeriesNum { get; set; } public bool ShouldBeDeleted { get; } public DateTime StartTime { get; set; } public int StopTime { get; set; } public int TimesWatched { get; set; } public string Title { get; set; } */ string EscapeName = EscapeSQLString(mywish.searchfor); if (SearchIn == "title") //no translation needed due to separation of original string { //Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now if (mywish.b_partialname == true) //partial title { Expression += String.Format("( title like '%{0}%' ) ", EscapeName); } else //exact title { Expression += String.Format("( title = '{0}' ) ", EscapeName); } }//end title else if (SearchIn == "text") //search in description //no translation needed due to separation of original string { //Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now Expression += String.Format("( description like '%{0}%' ) ", EscapeName); }//end description else if (SearchIn == "both") //no translation needed due to separation of original string { //Expression += String.Format("(EndTime >= '{0}') AND (", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now if (mywish.b_partialname == true) //partial title { Expression += String.Format("( title like '%{0}%' ) OR ", EscapeName); } else //exact title { Expression += String.Format("( title = '{0}' ) OR ", EscapeName); } Expression += String.Format("( description like '%{0}%' )", EscapeName); }//end both if (SearchIn == "expression") //no translation needed due to separation of original string { Expression = mywish.searchfor; //split in schedule and recording expression Expression = Expression.Replace("<BR>", "\n"); Expression = Expression.Replace("<br>", "\n"); Expression = Expression.Replace(@"\n", "\n"); Expression = Expression.Replace(@"\'", "'"); //needed for expressions string[] expArray = Expression.Split('\n'); if (expArray.Length != 2) { Log.Debug("No expression defined for searching recordings on tvwish "+mywish.name); return true; } else { Expression = expArray[1]; } Log.Debug("Expression=" + Expression); } //SQL Query LogDebug("SQL query for: " + Expression, (int)LogSetting.DEBUG); try { #if (MPTV2) string command = "select * from recordings where " + Expression; Log.Debug("command=" + command); myRecordingList = Recording.GeneralSqlQuery(command); #else 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()); #endif Log.Debug("SQL Result:myRecordingList.Count" + myRecordingList.Count.ToString()); } catch (Exception exc) { LogDebug("Error in SQL query for searching " + Expression, (int)LogSetting.ERROR); LogDebug("Exception message was " + exc.Message, (int)LogSetting.ERROR); string languagetext = lng.TranslateString("Error in SQL query - check the expression {0}", 25, Expression); labelmessage(languagetext, PipeCommands.StartEpg); //do not mark as an error as other commands are still coming - just delay message Thread.Sleep(ErrorWaitTime); return false; } // End of SQL Query Log.Debug(myRecordingList.Count.ToString() + " recordings found after SQL query"); foreach (Recording myrecording in myRecordingList) { LogDebug("****Next program in autocompletion:", (int)LogSetting.DEBUG); //outputrecordingresponse(myrecording, (int)LogSetting.DEBUG); //Debug only /* // process groupname BUG: not needed in recordings if (mywish.group != lng.TranslateString("All Channels", 4104)) { if ((IsChannelInGroup(myrecording.IdChannel, mywish.group) == false) && (IsRadioChannelInGroup(myrecording.IdChannel, mywish.group) == false)) { LogDebug("!!!!!!!Groupname " + mywish.group + " not matching for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } }*/ //process exclude if (mywish.exclude != string.Empty) { string Exclude = mywish.exclude.ToUpper(); String Exclude1 = ""; String Exclude2 = ""; Exclude = Exclude.Replace("||", "\n");//bug fix: was && before String[] Tokens = Exclude.Split('\n'); if (Tokens.Length >= 2) { Exclude2 = Tokens[1]; } if (Tokens.Length >= 1) { Exclude1 = Tokens[0]; } LogDebug("TvWishList: Exclude1=" + Exclude1, (int)LogSetting.DEBUG); LogDebug("TvWishList: Exclude2=" + Exclude2, (int)LogSetting.DEBUG); Boolean foundflag = false; if ((Exclude1.StartsWith("*") == true) && (Exclude1.StartsWith("**") == false)) //exclude in description { string Exclude_mod = Exclude1.Substring(1, Exclude.Length - 1); //LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG); if ((myrecording.Description.ToUpper().Contains(Exclude_mod) == false) || (Exclude_mod == "")) { if ((myrecording.Description.ToUpper().Contains(Exclude2) == false) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); } } } else if (Exclude1.StartsWith("**") == true) //exclude in title or description { string Exclude_mod = Exclude1.Substring(2, Exclude.Length - 2); //LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG); if (((myrecording.Title.ToUpper().Contains(Exclude_mod) == false) && (myrecording.Description.ToUpper().Contains(Exclude_mod) == false)) || (Exclude_mod == "")) { if (((myrecording.Title.ToUpper().Contains(Exclude2) == false) && (myrecording.Description.ToUpper().Contains(Exclude2) == false)) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); } } } else if ((myrecording.Title.ToUpper().Contains(Exclude1) == false) || (Exclude1 == "")) //exclude only in title { if ((myrecording.Title.ToUpper().Contains(Exclude2) == false) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); } } if (foundflag == false) { LogDebug("!!!!!!!exclude condition " + Exclude + " found for for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } }//end exclude // process valid word check if (mywish.b_wordmatch == true) { if (SearchIn == "title") //no translation needed due to separation of original string { if (mywish.b_partialname == true) //partial title { if (validwordcheck(myrecording.Title, mywish.searchfor) == false) { LogDebug("!!!!!!!wordcheck on title failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } } } else if (SearchIn == "text") //search in description //no translation needed due to separation of original string { if (validwordcheck(myrecording.Description, mywish.searchfor) == false) { LogDebug("!!!!!!!wordcheck on description failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } } else if (SearchIn == "both") //search in either title or description //no translation needed due to separation of original string { if ((validwordcheck(myrecording.Title, mywish.searchfor) == false) && (validwordcheck(myrecording.Description, mywish.searchfor) == false)) { LogDebug("!!!!!!!wordcheck on title and description failed for title " + myrecording.Title + " at " + myrecording.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } } } //end processing valid wordcheck //process episode name, part, number and series number #if(TV100 || TV101) if (1==1) //always true for 1.0.1 #elif(TV11 || TV12) if (((myrecording.EpisodeName.ToLower() == mywish.episodename.ToLower() == true) || (mywish.episodename == "")) && ((myrecording.EpisodePart.ToLower() == mywish.episodepart.ToLower() == true) || (mywish.episodepart == "")) && ((myrecording.SeriesNum.ToLower() == mywish.seriesnumber.ToLower()) || (mywish.seriesnumber == "")) && ((myrecording.EpisodeNum.ToLower() == mywish.episodenumber.ToLower()) || (mywish.episodenumber == ""))) //bug to fix with > number #endif { if ((myrecording.StartTime < myrecording.EndTime)&&(File.Exists(myrecording.FileName))) {//during recording start time will be equal to end time and text all in capitol letters - in that case ignore and wait till recording is finished - schedule does exist in parallel during recording //create new recording message Log.Debug("myrecording.Title=" + myrecording.Title); Log.Debug("myrecording.Description=" + myrecording.Description); FileInfo myfileinfo = new FileInfo(myrecording.FileName); double filesize = Convert.ToDouble(myfileinfo.Length)/(1024*1024*1024); string message = lng.TranslateString("{0} with {1} GB",59,myrecording.FileName,filesize.ToString("F2")); Log.Debug("recording message with filesize="+message); //add new recorded message mymessage.addmessage(myrecording, message, MessageType.Recorded, mywish.searchfor, (int)XmlMessages.MessageEvents.RECORDING_FOUND, mywish.tvwishid, myrecording.FileName); //remove deleted messages with same title/description/episode name/number try { for (int i = mymessage.ListAllTvMessages().Count-1; i >=0 ; i--) { xmlmessage onemessage = mymessage.GetTvMessageAtIndex(i); if ((onemessage.title == myrecording.Title) && (onemessage.description == myrecording.Description) && (onemessage.type == MessageType.Deleted.ToString())) { #if(TV100 || TV101) if (1==1) //always true for 1.0.1 #elif(TV11 || TV12) if (((myrecording.EpisodeName.ToLower() == onemessage.EpisodeName.ToLower() == true) || (onemessage.EpisodeName == "")) && ((myrecording.EpisodePart.ToLower() == onemessage.EpisodePart.ToLower() == true) || (onemessage.EpisodePart == "")) && ((myrecording.SeriesNum.ToLower() == onemessage.SeriesNum.ToLower()) || (onemessage.SeriesNum == "")) && ((myrecording.EpisodeNum.ToLower() == onemessage.EpisodeNum.ToLower()) || (onemessage.EpisodeNum == ""))) #endif { //delete message Log.Debug("Deleting message " + onemessage.title+" at "+onemessage.start+" due to new recording"); mymessage.DeleteTvMessageAt(i); } } } } catch (Exception exc) { LogDebug("Error: searching deleted messages failed with exception " + exc.Message, (int)LogSetting.ERROR); string languagetext2 = lng.TranslateString("Fatal error - check the log file", 24); labelmessage(languagetext2, PipeCommands.StartEpg); //do not stop - do not flag as error Thread.Sleep(ErrorWaitTime); return false; } } }//end episode filter string languagetext = lng.TranslateString("Processing Recording {0} at {1}", 22, myrecording.Title, myrecording.StartTime.ToString()); labelmessage(languagetext, PipeCommands.StartEpg); }//end all recordings LogDebug("TvWishList: Autocomplete finished", (int)LogSetting.DEBUG); return true; }
//------------------------------------------------------------------------------------------------------------- // Search epg data and find all matching programs //------------------------------------------------------------------------------------------------------------- // matching programs are searched based on available information for program name, start time, endtime, displayname or groupname // if dispalyname is defined, groupnames will be ignored // partial name = true will handle partial name matches from program name // the routine will return an IList<Program> matchingprograms which contains all EPG programs of Type Program // the routine will not do any checking for conflicts of the list //------------------------------------------------------------------------------------------------------------- public bool SqlQueryPrograms(ref TvWish mywish, int counter) { LogDebug("TvWishList: Autocomplete search for matching programs", (int)LogSetting.DEBUG); if (mywish.b_active == false) { Log.Debug("Tvwish is inactive - returning"); return true; } string SearchIn = mywish.s_SearchIn.ToLower(); String Expression = String.Empty; IFormatProvider mmddFormat = new CultureInfo(String.Empty, false); IList<Program> myprogramlist = null; /* search names are case invariant (title SOKO equal to title soko) * * LIKE * NOT LIKE % A substitute for zero or more characters _ A substitute for exactly one character [charlist] Any single character in charlist [^charlist] or [!charlist] Any single character not in charlist * * * * SELECT column_name(s) FROM table_name WHERE column_name operator value Operators Allowed in the WHERE Clause With the WHERE clause, the following operators can be used: Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns * * * Combine with brackets () * and with AND or OR Note: In some versions of SQL the <> operator may be written as != * * * Program columns: * title * description * classification * string Title string Classification string Description DateTime StartTime DateTime EndTime DateTime OriginalAirDate string EpisodeName string EpisodeNum string EpisodeNumber string EpisodePart string Genre int ParentalRating string SeriesNum int StarRating int IdChannel int IdProgram */ string EscapeName = EscapeSQLString(mywish.searchfor); if (SearchIn == "title") //no translation needed due to separation of original string { Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now if (mywish.b_partialname == true) //partial title { Expression += String.Format("( title like '%{0}%' ) ", EscapeName); } else //exact title { Expression += String.Format("( title = '{0}' ) ", EscapeName); } }//end title else if (SearchIn == "text") //search in description //no translation needed due to separation of original string { Expression += String.Format("(EndTime >= '{0}') AND ", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now Expression += String.Format("( description like '%{0}%' ) ", EscapeName); }//end description else if (SearchIn == "both") //no translation needed due to separation of original string { Expression += String.Format("(EndTime >= '{0}') AND (", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); //only programs after now if (mywish.b_partialname == true) //partial title { Expression += String.Format("( title like '%{0}%' ) OR ", EscapeName); } else //exact title { Expression += String.Format("( title = '{0}' ) OR ", EscapeName); } Expression += String.Format("( description like '%{0}%' ) )", EscapeName); }//end both if (SearchIn == "expression") //no translation needed due to separation of original string { Expression = mywish.searchfor; //split in schedule and recording expression Expression = Expression.Replace("<BR>", "\n"); Expression = Expression.Replace("<br>", "\n"); Expression = Expression.Replace(@"\n", "\n"); Expression = Expression.Replace(@"\'", "'"); //needed for expressions string[] expArray = Expression.Split('\n'); if (expArray.Length < 1) { Log.Debug("No expression defined for searching schedules on tvwish " + mywish.name); return true; } else { Expression = expArray[0]; Log.Debug("Expression=" + Expression); } } //SQL Query LogDebug("SQL query for: " + Expression, (int)LogSetting.DEBUG); try { #if (MPTV2) string command = "select * from programs where "+Expression; Log.Debug("command=" + command); myprogramlist = Program.GeneralSqlQuery(command); #else StringBuilder SqlSelectCommand = new StringBuilder(); SqlSelectCommand.Append("select * from Program "); SqlSelectCommand.AppendFormat("where {0}", Expression); //!!!!!!!!!!!!!!!!!!!!!!EscapeSQLString cannot be checked for expression SqlSelectCommand.Append(" order by StartTime"); SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof(Program)).GetStatement(true); SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(),typeof(Program)); myprogramlist = ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute()); #endif } catch (Exception exc) { LogDebug("Error in SQL query for searching " + Expression, (int)LogSetting.ERROR); LogDebug("Exception message was " + exc.Message, (int)LogSetting.ERROR); string languagetext = lng.TranslateString("Error in SQL query - check the expression {0}", 25, Expression); labelmessage(languagetext, PipeCommands.StartEpg); //do not mark as an error as other commands are still coming - just delay message Thread.Sleep(ErrorWaitTime); return false; } // End of SQL Query Log.Debug(myprogramlist.Count.ToString()+" programs found"); foreach (Program myprogram in myprogramlist) { Program oneprogram = myprogram; LogDebug("\n************************************************************", (int)LogSetting.DEBUG); LogDebug("****Next program in autocompletion:", (int)LogSetting.DEBUG); outputprogramresponse(myprogram, (int)LogSetting.DEBUG); //Debug only // process groupname if (mywish.group != lng.TranslateString("All Channels",4104)) { if ((IsChannelInGroup(oneprogram.IdChannel, mywish.group) == false) && (IsRadioChannelInGroup(oneprogram.IdChannel, mywish.group) == false)) { LogDebug("!!!!!!!Groupname " + mywish.group + " not matching for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG); continue; } } //process exclude if (mywish.exclude != string.Empty) { string Exclude = mywish.exclude.ToUpper(); String Exclude1 = ""; String Exclude2 = ""; Exclude = Exclude.Replace("||", "\n");//bug fix: was && before String[] Tokens = Exclude.Split('\n'); if (Tokens.Length >= 2) { Exclude2 = Tokens[1]; } if (Tokens.Length >= 1) { Exclude1 = Tokens[0]; } LogDebug("TvWishList: Exclude1=" + Exclude1, (int)LogSetting.DEBUG); LogDebug("TvWishList: Exclude2=" + Exclude2, (int)LogSetting.DEBUG); Boolean foundflag = false; if ((Exclude1.StartsWith("*") == true) && (Exclude1.StartsWith("**") == false)) //exclude in description { string Exclude_mod = Exclude1.Substring(1, Exclude.Length - 1); //LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG); if ((oneprogram.Description.ToUpper().Contains(Exclude_mod) == false) || (Exclude_mod == "")) { if ((oneprogram.Description.ToUpper().Contains(Exclude2) == false) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG); } } } else if (Exclude1.StartsWith("**") == true) //exclude in title or description { string Exclude_mod = Exclude1.Substring(2, Exclude.Length - 2); //LogDebug("Exclude_mod= " + Exclude_mod, (int)LogSetting.DEBUG); if (((oneprogram.Title.ToUpper().Contains(Exclude_mod) == false) && (oneprogram.Description.ToUpper().Contains(Exclude_mod) == false)) || (Exclude_mod == "")) { if (((oneprogram.Title.ToUpper().Contains(Exclude2) == false) && (oneprogram.Description.ToUpper().Contains(Exclude2) == false)) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG); } } } else if ((oneprogram.Title.ToUpper().Contains(Exclude1) == false) || (Exclude1 == "")) //exclude only in title { if ((oneprogram.Title.ToUpper().Contains(Exclude2) == false) || (Exclude2 == "")) { foundflag = true; LogDebug("No Exclude of program for title " + oneprogram.Title + " at " + oneprogram.StartTime.ToString(), (int)LogSetting.DEBUG); } } if (foundflag == false) { LogDebug("", (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); message = lng.TranslateString("Excluding program {0} due to exclude filter condition {1}",55,oneprogram.Title,mywish.exclude); LogDebug(message, (int)LogSetting.INFO); LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty); continue; } }//end exclude // process valid word check if (mywish.b_wordmatch == true) { if (SearchIn == "title") //no translation needed due to separation of original string { if (mywish.b_partialname == true) //partial title { if (validwordcheck(oneprogram.Title,mywish.searchfor) == false) { LogDebug("", (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition",56,oneprogram.Title); LogDebug(message, (int)LogSetting.INFO); LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty); continue; } } } else if (SearchIn == "text") //search in description //no translation needed due to separation of original string { if (validwordcheck(oneprogram.Description, mywish.searchfor) == false) { LogDebug("", (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition ", 57, oneprogram.Title); LogDebug(message, (int)LogSetting.INFO); LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty); continue; } } else if (SearchIn == "both") //search in either title or description //no translation needed due to separation of original string { if ((validwordcheck(oneprogram.Title, mywish.searchfor) == false) && (validwordcheck(oneprogram.Description, mywish.searchfor) == false)) { LogDebug("", (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); message = lng.TranslateString("Excluding program {0} due to wordcheck filter condition ", 57, oneprogram.Title); LogDebug(message, (int)LogSetting.INFO); LogDebug("Start=" + oneprogram.StartTime.ToString(), (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.FILTER_MISMATCH, mywish.tvwishid, string.Empty); continue; } } } //end processing valid wordcheck string channelname = "Error_with_ID_" + oneprogram.IdChannel.ToString(); try { channelname = Channel.Retrieve(oneprogram.IdChannel).DisplayName; } catch//do nothing { } string languagetext = lng.TranslateString("Processing EPG data {0} on {1} at {2}", 21, oneprogram.Title, channelname, oneprogram.StartTime.ToString()); labelmessage(languagetext, PipeCommands.StartEpg); if (mywish.i_scheduled >= MAXFOUND) { LogDebug("", (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); message = lng.TranslateString("Maximum number ({0}) of programs found - will ignore additional matches",58, MAXFOUND.ToString()); LogDebug(message, (int)LogSetting.INFO); LogDebug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!", (int)LogSetting.INFO); mymessage.addmessage(oneprogram, message, MessageType.Conflict, mywish.name, (int)XmlMessages.MessageEvents.MAXFOUND_EXCEEDED, mywish.tvwishid, string.Empty); return true; } DateTime start = DateTime.Now; //DEBUG PERFORMANCE bool ok = addsingleschedule(oneprogram, ref mywish, counter); DateTime end = DateTime.Now; //DEBUG PERFORMANCE Log.Debug("addsingleschedule time=" + end.Subtract(start).TotalSeconds.ToString()); //DEBUG PERFORMANCE //post processing and updating of Tv wish data by locking to existing found schedule if (ok == true) { try //update lock filters into tv server { if (mywish.recordtype == lng.TranslateString("Only Once",2650)) // stop after first successful program { LogDebug("Program " + mywish.name + " deactivated", (int)LogSetting.DEBUG); mywish.b_active = false; mywish.active = "False"; break; // stop after first successful program } //include all language strings with recordtype Channel if (((mywish.recordtype == lng.Get(2652)) || (mywish.recordtype== lng.Get(2653)) || (mywish.recordtype== lng.Get(2654))|| (mywish.recordtype== lng.Get(2655)))&& (mywish.channel == lng.TranslateString("Any",4100))) { mywish.channel = channelname; Log.Debug("Locked to channelfilter=" + channelname); } //include all language strings with recordtype Time if (((mywish.recordtype == lng.Get(2654)) || (mywish.recordtype == lng.Get(2655)))&& (mywish.i_aftertime == -1) && (mywish.i_beforetime == -1)) { mywish.i_aftertime = oneprogram.StartTime.Hour * 60 + oneprogram.StartTime.Minute; mywish.aftertime = oneprogram.StartTime.Hour.ToString("D2") + ":" + oneprogram.StartTime.Minute.ToString("D2"); Log.Debug("Locked to i_aftertime=" + mywish.i_aftertime.ToString()); mywish.i_beforetime = oneprogram.EndTime.Hour * 60 + oneprogram.EndTime.Minute; mywish.beforetime = oneprogram.EndTime.Hour.ToString("D2") + ":" + oneprogram.EndTime.Minute.ToString("D2"); Log.Debug("Locked to i_beforetime=" + mywish.i_beforetime.ToString()); } //include all language strings with recordtype Day if (((mywish.recordtype == lng.Get(2653)) || (mywish.recordtype == lng.Get(2655))) && (mywish.i_afterdays == -1) && (mywish.i_beforedays == -1)) { mywish.i_afterdays = (int)oneprogram.StartTime.DayOfWeek; if (mywish.i_afterdays == (int)DayOfWeek.Sunday) mywish.afterdays = "Sunday"; else if (mywish.i_afterdays == (int)DayOfWeek.Monday) mywish.afterdays = "Monday"; else if (mywish.i_afterdays == (int)DayOfWeek.Tuesday) mywish.afterdays = "Tuesday"; else if (mywish.i_afterdays == (int)DayOfWeek.Wednesday) mywish.afterdays = "Wednesday"; else if (mywish.i_afterdays == (int)DayOfWeek.Thursday) mywish.afterdays = "Thursday"; else if (mywish.i_afterdays == (int)DayOfWeek.Friday) mywish.afterdays = "Friday"; else if (mywish.i_afterdays == (int)DayOfWeek.Saturday) mywish.afterdays = "Saturday"; mywish.i_beforedays = (int)oneprogram.StartTime.DayOfWeek; if (mywish.i_beforedays == (int)DayOfWeek.Sunday) mywish.beforedays = "Sunday"; else if (mywish.i_beforedays == (int)DayOfWeek.Monday) mywish.beforedays = "Monday"; else if (mywish.i_beforedays == (int)DayOfWeek.Tuesday) mywish.beforedays = "Tuesday"; else if (mywish.i_beforedays == (int)DayOfWeek.Wednesday) mywish.beforedays = "Wednesday"; else if (mywish.i_beforedays == (int)DayOfWeek.Thursday) mywish.beforedays = "Thursday"; else if (mywish.i_beforedays == (int)DayOfWeek.Friday) mywish.beforedays = "Friday"; else if (mywish.i_beforedays == (int)DayOfWeek.Saturday) mywish.beforedays = "Saturday"; Log.Debug("Locked to day=" + mywish.i_beforedays.ToString()); } } catch (Exception exc) { Log.Error("Error in updating lock filter - ex ception:" + exc.Message); languagetext = lng.TranslateString("Fatal error - check the log file", 24); labelmessage(languagetext, PipeCommands.StartEpg); //do not stop - do not flag as error Thread.Sleep(ErrorWaitTime); } } //end locking condition }//end search episode n //} LogDebug("TvWishList: Autocomplete finished", (int)LogSetting.DEBUG); return true; }
public IList<Program> SearchProgramsByDescription(string searchCriteria, ChannelType channelType) { IFormatProvider mmddFormat = new CultureInfo(String.Empty, false); StringBuilder SqlSelectCommand = new StringBuilder(); SqlSelectCommand.Append("select p.* from Program p inner join Channel c on c.idChannel = p.idChannel "); SqlSelectCommand.AppendFormat("where endTime > '{0}'", DateTime.Now.ToString(GetDateTimeString(), mmddFormat)); if (searchCriteria.Length > 0) { SqlSelectCommand.AppendFormat("and description like '{0}%' ", EscapeSQLString(searchCriteria)); } switch (channelType) { case ChannelType.Radio: SqlSelectCommand.Append("and c.isTv=0 "); break; case ChannelType.Tv: SqlSelectCommand.Append("and c.isTv=1 "); break; } SqlSelectCommand.Append("and c.visibleInGuide = 1 order by description, startTime"); SqlStatement stmt = new SqlBuilder(StatementType.Select, typeof (Program)).GetStatement(true); SqlStatement ManualJoinSQL = new SqlStatement(StatementType.Select, stmt.Command, SqlSelectCommand.ToString(), typeof (Program)); return ObjectFactory.GetCollection<Program>(ManualJoinSQL.Execute()); }
public IList<Channel> GetChannelsInGroup(ChannelGroup group) { SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (Channel)); SqlStatement origStmt = sb.GetStatement(true); string sql = "select c.* from channel c inner join groupmap gm on (c.idChannel = gm.idChannel and gm.idGroup =" + group.IdGroup + ") order by gm.SortOrder asc"; SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql, typeof (Channel)); return ObjectFactory.GetCollection<Channel>(statement.Execute()); }
/// <summary> /// Execute a custom SQL statement. /// </summary> /// <param name="stmt">The statement to execute, wrapped in a SqlStatement object</param> /// <returns>An SqlResult instance</returns> public SqlResult Execute(SqlStatement stmt) { return(stmt.Execute()); }
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"]; 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"; } }
/// <summary> /// Execute the <see cref="SqlStatement"/> by providing it with a connection /// to the SQL engine. /// </summary> /// <param name="stmt">The SqlStatement instance</param> /// <returns>The result of the statement</returns> public virtual SqlResult ExecuteStatement(SqlStatement stmt) { Check.VerifyNotNull(stmt, Error.NullParameter, "stmt"); return(stmt.Execute(GetConnection(), null)); }
/// <summary> /// Execute the <see cref="SqlStatement"/> by providing it with a connection /// to the SQL engine. /// </summary> /// <param name="stmt">The SqlStatement instance</param> /// <returns>The result of the statement</returns> public virtual SqlResult ExecuteStatement( SqlStatement stmt ) { Check.VerifyNotNull( stmt, Error.NullParameter, "stmt" ); return stmt.Execute( GetConnection(), null ); }
/// <summary> /// Execute the current instance using the supplied database connection and return /// the result of the operation as an instance of the <see cref="SqlResult"/> class. /// </summary> /// <param name="conn">The database connection to use for executing this statement.</param> /// <param name="tr">The transaction instance to run this statement in. If there is no /// transaction context then this parameter should be null. This will cause the /// connection to be closed after execution.</param> /// <returns>The result of the operation</returns> internal SqlResult Execute(IDbConnection conn, IDbTransaction tr) { Check.VerifyNotNull(conn, Error.NoNewConnection); cmd.Connection = conn; cmd.Transaction = tr; SqlResult sr = null; StringBuilder log = IsLoggingEnabled ? new StringBuilder(500) : null; try { if (log != null) { log.AppendFormat("Executing query: {0}{1}", cmd.CommandText, Environment.NewLine); foreach (IDataParameter param in cmd.Parameters) { object paramValue = param.Value == null ? "null" : param.Value; log.AppendFormat("Parameter: {0} = {1}{2}", param.ParameterName, paramValue, Environment.NewLine); } } switch (statementType) { case StatementType.Count: case StatementType.Identity: sr = new SqlResult(broker, cmd.ExecuteScalar(), this); break; case StatementType.Select: sr = new SqlResult(broker, cmd.ExecuteReader(), this); break; case StatementType.Update: case StatementType.Delete: case StatementType.SoftDelete: // returns the number of rows affected sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this); break; case StatementType.Insert: if (map != null && map.IdentityMap != null) { GentleSqlFactory sf = broker.GetSqlFactory(); // check if we need to execute insert statement separately if (!sf.HasCapability(Capability.BatchQuery)) { // execute the insert command cmd.ExecuteNonQuery(); // get the sql string for retrieving the generated id string sql = sf.GetIdentitySelect(null, map); // get an sql statement that will execute using ExecuteScalar SqlStatement stmt = broker.GetStatement(StatementType.Identity, sql); // execute the query and fetch the id sr = stmt.Execute(cmd.Connection, tr); } else { // returns the 1st column of the 1st row in the result set (i.e. new identity) sr = new SqlResult(broker, cmd.ExecuteScalar(), this); } } else { sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this); } break; default: // returns the number of rows affected sr = new SqlResult(broker, cmd.ExecuteReader(), this); break; } if (log != null) { PerformExecutionLog(log, sr); } } catch (Exception e) { if (!Check.IsCalledFrom("Analyzer.Analyze", e) || !GentleSettings.AnalyzerSilent) { Check.LogError(LogCategories.StatementExecution, e); } if (e is GentleException) { throw; } else { throw new GentleException(Error.StatementError, cmd.CommandText, e); } } finally { // close the database connection (only if not in transaction) if (conn != null && conn.State == ConnectionState.Open && tr == null) { conn.Close(); } // only if not in transaction; would otherwise break with PostgreSQL (not permitted) if (tr == null) { // clear the connection reference try { cmd.Connection = null; } catch { // ignore errors here.. like for the SQLite-provider which complains for no good reason } // clear the transaction reference cmd.Transaction = null; } } return(sr); }
public bool IsChannelMappedToCard(Channel dbChannel, Card card, bool forEpg) { SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof (ChannelMap)); SqlStatement origStmt = sb.GetStatement(true); string sql = "select cm.* from ChannelMap cm where cm.idChannel =" + dbChannel.IdChannel + " and cm.idCard=" + card.IdCard + (forEpg ? "" : " and cm.epgOnly=0"); SqlStatement statement = new SqlStatement(StatementType.Select, origStmt.Command, sql, typeof (Channel)); IList<ChannelMap> maps = ObjectFactory.GetCollection<ChannelMap>(statement.Execute()); return maps != null && maps.Count > 0; }
/// <summary> /// Execute a custom SQL statement. /// </summary> /// <param name="stmt">The statement to execute, wrapped in a SqlStatement object</param> /// <param name="dbTransaction">The database transaction for when participating in transactions</param> /// <returns>An SqlResult instance</returns> public SqlResult Execute(SqlStatement stmt, IDbTransaction dbTransaction) { return(stmt.Execute(dbTransaction.Connection, dbTransaction)); }
/// <summary> /// Gets a list of tv channels sorted by their group /// </summary> /// <returns>a list of TVDatabase Channels</returns> public List<Channel> GetTVGuideChannelsForGroup(int groupID) { SqlBuilder sb1 = new SqlBuilder(StatementType.Select, typeof (Channel)); SqlStatement stmt1 = sb1.GetStatement(true); SqlStatement ManualJoinSQL = new SqlStatement(stmt1.StatementType, stmt1.Command, String.Format( "select c.* from Channel c inner join GroupMap g on (c.idChannel=g.idChannel and g.idGroup = '{0}') where visibleInGuide = 1 and isTv = 1 order by g.sortOrder", groupID), typeof (Channel)); return ObjectFactory.GetCollection<Channel>(ManualJoinSQL.Execute()) as List<Channel>; }
public static void ResetAllStates() { string sql = "Update Program set state=0 where state<>0;"; SqlStatement stmt = new SqlStatement(StatementType.Update, Broker.Provider.GetCommand(), sql); stmt.Execute(); Gentle.Common.CacheManager.ClearQueryResultsByType(typeof(Program)); }