public static DateTime GetAsDateTime(SQLiteResultSet results, int iRecord, string aTimestampColum) { DateTime finalResult = DateTime.MinValue; if (results == null || string.IsNullOrEmpty(aTimestampColum) || results.Rows.Count < 1 || results.Rows.Count < iRecord) { return(finalResult); } try { SQLiteResultSet.Row arr = results.Rows[iRecord]; int iCol = 0; if (results.ColumnIndices.ContainsKey(aTimestampColum)) { iCol = (int)results.ColumnIndices[aTimestampColum]; if (arr.fields[iCol] != null) { finalResult = Convert.ToDateTime((arr.fields[iCol])); } } } catch (Exception) {} return(finalResult); }
public static bool IntegrityCheck(SQLiteClient m_db) { SQLiteResultSet results; if (m_db == null) { return(false); } results = m_db.Execute("PRAGMA integrity_check;"); if (results != null) { if (results.Rows.Count == 1) { SQLiteResultSet.Row arr = results.Rows[0]; if (arr.fields.Count == 1) { if (arr.fields[0] == "ok") { Log.Debug("IntegrityCheck: the {0} is OK", m_db.DatabaseName); return(true); } } } } Log.Error("IntegrityCheck: the {0} is corrupt.", m_db.DatabaseName); return(false); }
public static string Get(SQLiteResultSet results, int iRecord, int column) { if (null == results) { return(string.Empty); } if (results.Rows.Count < iRecord) { return(string.Empty); } if (column < 0 || column >= results.ColumnNames.Count) { return(string.Empty); } SQLiteResultSet.Row arr = results.Rows[iRecord]; if (arr.fields[column] == null) { return(string.Empty); } string strLine = (arr.fields[column]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); ; }
private string Get(SQLiteResultSet results, int iRecord, string strColum) { if (null == results) { return(string.Empty); } if (results.Rows.Count < iRecord) { return(string.Empty); } SQLiteResultSet.Row arr = results.Rows[iRecord]; int iCol = 0; foreach (string columnName in results.ColumnNames) { if (strColum == columnName) { string strLine = arr.fields[iCol].Trim(); strLine = strLine.Replace("''", "'"); return(strLine); } iCol++; } return(string.Empty); }
/// <summary> /// Check if a view exists /// </summary> /// <param name="table">name of view</param> /// <returns>true: view exists /// false: view does not exist</returns> public static bool ViewExists(SQLiteClient m_db, string view) { SQLiteResultSet results; if (m_db == null) { return(false); } if (view == null) { return(false); } if (view.Length == 0) { return(false); } results = m_db.Execute("SELECT name FROM sqlite_master WHERE name like '" + view + "' and type like 'view'"); // UNION ALL SELECT name FROM sqlite_temp_master WHERE type='view' ORDER BY name"); if (results != null) { if (results.Rows.Count == 1) { SQLiteResultSet.Row arr = results.Rows[0]; if (arr.fields.Count == 1) { if (arr.fields[0] == view) { return(true); } } } } return(false); }
public static bool CheckIntegrity() { string query = "PRAGMA integrity_check;"; MPTVSeriesLog.Write("Executing SQL integrity check"); SQLiteResultSet results = Execute(query); if (results != null) { if (results.Rows.Count == 1) { SQLiteResultSet.Row arr = results.Rows[0]; if (arr.fields.Count == 1) { if (arr.fields[0] == "ok") { MPTVSeriesLog.Write("Database integrity check succeeded"); return(true); } } } } MPTVSeriesLog.Write("Integrity check failed, database is corrupt. Filename = '{0}'", m_db.DatabaseName); return(false); }
// Loads data into this object based on the given database record. public void LoadByRow(SQLiteResultSet.Row row) { RetrievalInProcess = true; ReadOnlyCollection <DBField> fieldList = DBField.GetFieldList(this.GetType()); // load each field one at a time. they should have been retrieved in the // ordering in FieldList int i; for (i = 0; i < fieldList.Count; i++) { if (row.fields[i] == "") { fieldList[i].SetValue(this, null); } else { fieldList[i].SetValue(this, row.fields[i]); } } // id is always at the end, assign that too id = int.Parse(row.fields[i]); // all values are in sync with DB so no commit needed. commitNeeded = false; RetrievalInProcess = false; }
public bool Read(SQLiteResultSet.Row row, System.Collections.Hashtable ColumnIndices) { if (row == null || row.fields.Count == 0) { return(false); } string res = null; int iCol = 0; foreach (KeyValuePair <string, DBField> field in m_fields) { object o = null; if (((o = ColumnIndices[field.Key]) != null) || ((o = ColumnIndices[m_tableName + "." + field.Key]) != null) || ((o = ColumnIndices[m_tableName + field.Key]) != null)) // because of order bug in sqlite { iCol = (int)o; res = row.fields[iCol]; field.Value.Value = res == null ? string.Empty : res; } else { // we have a column in mfields that is not in the database result (or null), as such it is to be empty field.Value.Value = string.Empty; } } m_CommitNeeded = false; return(true); }
private bool verifyIntegrity(SQLiteClient client) { string query = "PRAGMA integrity_check;"; logger.Info("Executing SQL integrity check"); try { SQLiteResultSet results = client.Execute(query); if (results != null) { if (results.Rows.Count == 1) { SQLiteResultSet.Row arr = results.Rows[0]; if (arr.fields.Count == 1) { if (arr.fields[0] == "ok") { logger.Info("Database integrity check succeeded"); return(true); } } } } } catch (Exception e) { logger.Info("Integrity check failed, database is corrupt. Reason = '{0}', Filename = '{1}'", e.Message, client.DatabaseName); return(false); } logger.Info("Integrity check failed, database is corrupt. Filename = '{0}'", client.DatabaseName); return(false); }
public static string Get(SQLiteResultSet results, int iRecord, int column) { if (null == results) { Log.Error("DatabaseUtility.Get: Results=null"); return(string.Empty); } if (results.Rows.Count < iRecord) { Log.Error("DatabaseUtility.Get: Recordnr {0} not found", iRecord); return(string.Empty); } if (column < 0 || column >= results.ColumnNames.Count) { Log.Error("DatabaseUtility.Get: Columnr {0} out of range", column); return(string.Empty); } SQLiteResultSet.Row arr = results.Rows[iRecord]; if (arr.fields[column] == null) { Log.Error("DatabaseUtility.Get: Field {0} = null", column); return(string.Empty); } string strLine = (arr.fields[column]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); ; }
public static DateTime GetAsDateTime(SQLiteResultSet results, int iRecord, string aTimestampColum) { DateTime finalResult = DateTime.MinValue; if (results == null || string.IsNullOrEmpty(aTimestampColum) || results.Rows.Count < 1 || results.Rows.Count < iRecord) { return(finalResult); } try { SQLiteResultSet.Row arr = results.Rows[iRecord]; int?iCol = (int?)results.ColumnIndices[aTimestampColum]; if (iCol.HasValue) { if (arr.fields[iCol.Value] != null) { finalResult = Convert.ToDateTime((arr.fields[iCol.Value])); } } } catch (Exception ex) { Log.Error("DatabaseUtility:GetAsDateTime: {0}", ex.Message); } return(finalResult); }
public bool Read(ref SQLiteResultSet records, int index) { if (records.Rows.Count > 0 || records.Rows.Count < index) { SQLiteResultSet.Row row = records.Rows[index]; return(Read(row, records.ColumnIndices)); } return(false); }
public static string Get(SQLiteResultSet results, int iRecord, string strColum) { if (null == results) { Log.Error("DatabaseUtility.Get: Results=null"); return(string.Empty); } if (results.Rows.Count == 0) { Log.Error("DatabaseUtility.Get: Rowcount=0"); return(string.Empty); } if (results.Rows.Count < iRecord) { Log.Error("DatabaseUtility.Get: Recordnr {0} not found", iRecord); return(string.Empty); } SQLiteResultSet.Row arr = results.Rows[iRecord]; int?iCol = (int?)results.ColumnIndices[strColum]; if (iCol.HasValue) { if (arr.fields[iCol.Value] == null) { Log.Error("DatabaseUtility.Get: Field {0} not found", strColum); return(string.Empty); } string strLine = (arr.fields[iCol.Value]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); } int pos = strColum.IndexOf(".", StringComparison.Ordinal); if (pos < 0) { Log.Error("DatabaseUtility.Get: Field {0} not found", strColum); return(string.Empty); } strColum = strColum.Substring(pos + 1); iCol = (int?)results.ColumnIndices[strColum]; if (iCol.HasValue) { ; if (arr.fields[iCol.Value] == null) { Log.Error("DatabaseUtility.Get: Field {0} not found", strColum); return(string.Empty); } string strLine = (arr.fields[iCol.Value]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); } return(string.Empty); }
public static string Get(SQLiteResultSet results, int iRecord, string strColum) { if (null == results) { return(string.Empty); } if (results.Rows.Count == 0) { return(string.Empty); } if (results.Rows.Count < iRecord) { return(string.Empty); } SQLiteResultSet.Row arr = results.Rows[iRecord]; int iCol = 0; if (results.ColumnIndices.ContainsKey(strColum)) { iCol = (int)results.ColumnIndices[strColum]; if (arr.fields[iCol] == null) { return(string.Empty); } string strLine = (arr.fields[iCol]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); } int pos = strColum.IndexOf(".", StringComparison.Ordinal); if (pos < 0) { return(string.Empty); } strColum = strColum.Substring(pos + 1); if (results.ColumnIndices.ContainsKey(strColum)) { iCol = (int)results.ColumnIndices[strColum]; if (arr.fields[iCol] == null) { return(string.Empty); } string strLine = (arr.fields[iCol]).Trim(); //strLine = strLine.Replace("''","'"); return(strLine); } return(string.Empty); }
private DBMovieInfo getMovieInfo(string idMovie) { DBMovieInfo movieRes = new DBMovieInfo(); try { SQLiteClient mp_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3")); SQLiteResultSet sqlResults = mp_db.Execute("SELECT * FROM movieinfo WHERE idMovie LIKE '" + idMovie + "'"); SQLiteResultSet.Row sqlRow = sqlResults.GetRow(0); System.Collections.Hashtable columns = sqlResults.ColumnIndices; movieRes.Popularity = int.Parse(sqlResults.GetField(0, int.Parse(columns["strVotes"].ToString()))); movieRes.Runtime = int.Parse(sqlResults.GetField(0, int.Parse(columns["runtime"].ToString()))); movieRes.Score = float.Parse(sqlResults.GetField(0, int.Parse(columns["fRating"].ToString()))); movieRes.Year = int.Parse(sqlResults.GetField(0, int.Parse(columns["iYear"].ToString()))); string Title = sqlResults.GetField(0, int.Parse(columns["strTitle"].ToString())); if (!Title.Contains("unknown")) { movieRes.Title = Title; } string Certification = sqlResults.GetField(0, int.Parse(columns["mpaa"].ToString())); if (!Certification.Contains("unknown")) { movieRes.Certification = Certification; } string Tagline = sqlResults.GetField(0, int.Parse(columns["strTagLine"].ToString())); if (!Tagline.Contains("unknown")) { movieRes.Tagline = Tagline; } string Summary = sqlResults.GetField(0, int.Parse(columns["strPlotOutline"].ToString())); if (!Summary.Contains("unknown")) { movieRes.Summary = Summary; } string imdb_id = sqlResults.GetField(0, int.Parse(columns["IMDBID"].ToString())); if (!imdb_id.Contains("unknown")) { movieRes.ImdbID = imdb_id; } string genreMain = sqlResults.GetField(0, int.Parse(columns["strGenre"].ToString())); if (!genreMain.Contains("unknown")) { string[] genreSplit = genreMain.Split('/'); foreach (string genre in genreSplit) { movieRes.Genres.Add(genre.Trim()); } } string castMain = sqlResults.GetField(0, int.Parse(columns["strCast"].ToString())); if (!castMain.Contains("unknown")) { string[] castSplit = castMain.Split('\n'); foreach (string cast in castSplit) { string castFinal = cast; if (cast.Contains(" as ")) { castFinal = cast.Remove(cast.IndexOf(" as ")); } movieRes.Actors.Add(castFinal.Trim()); } } string idDirector = sqlResults.GetField(0, int.Parse(columns["idDirector"].ToString())); if (!castMain.Contains("unknown")) { SQLiteResultSet sqlDirector = mp_db.Execute("SELECT strActor FROM actors WHERE idActor LIKE '" + idDirector + "'"); movieRes.Directors.Add(sqlDirector.GetField(0, 0)); } string writers = sqlResults.GetField(0, int.Parse(columns["strCredits"].ToString())); if (!writers.Contains("unknown")) { string[] writerArray = writers.Split(new string[] { "\n", " " }, StringSplitOptions.None); foreach (string writer in writerArray) { if (!movieRes.Writers.Contains(writer.Trim())) { movieRes.Writers.Add(writer.Trim()); } } } movieRes.GetSourceMovieInfo(SourceInfo).Identifier = sqlResults.GetField(0, int.Parse(columns["idMovie"].ToString())).ToString(); mp_db.Close(); } catch { return(null); } return(movieRes); }