public AgeSet(DatabaseUtility databaseUtility)
{
_ageSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
databaseUtility.Command.CommandText = "SELECT * FROM EMPLOYEES";
var reader = databaseUtility.Command.ExecuteReader();
while (reader.Read())
{
_ageSet.Add(reader["Age"].ToString());
}
reader.Close();
}
public new void SetUp()
{
BeforeSetup();
log4net.Config.XmlConfigurator.Configure();
var connection = ConfigurationManager.ConnectionStrings["MySql"].ToString();
_databaseUtility = new DatabaseUtility(connection);
_databaseUtility.Create();
_databaseUtility.Populate();
//_persistance = ContextRegistry.GetContext()["PersistenceRepository"] as IPersistanceRepository;
//_orderRepository = ContextRegistry.GetContext()["OrderRepository"] as IOrderRepository;
//initialize journaler
_eventStore = new RavenNEventStore(Constants.OUTPUT_EVENT_STORE);
Journaler journaler = new Journaler(_eventStore);
//assign journaler to disruptor as its consumer
OutputDisruptor.InitializeDisruptor(new IEventHandler <byte[]>[] { journaler });
_manualResetEvent = new ManualResetEvent(false);
// _listener = new TradeEventListener(_persistance);
AfterSetup();
}
public void SetRotation(string strPicture, int iRotation)
{
string strSQL = "";
try
{
string strPic = strPicture;
DatabaseUtility.RemoveInvalidChars(ref strPic);
long lPicId = AddPicture(strPicture, iRotation);
if (lPicId >= 0)
{
strSQL = String.Format("update tblPicture set iRotation={0} where strFile like '{1}'", iRotation, strPic);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
}
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
/// <summary>
/// Plays all songs from the given artist, starting with item defined by startPos
/// </summary>
/// <param name="albumArtist">Artist that is played</param>
/// <param name="startPos">Position from where playback is started (playlist index)</param>
internal static void PlayArtist(string albumArtist, int startPos)
{
List <Song> songs = new List <Song>();
string sql = String.Format("select * from tracks where strAlbumArtist like '%{0}%'",
DatabaseUtility.RemoveInvalidChars(albumArtist));
MusicDatabase.Instance.GetSongsByFilter(sql, out songs, "tracks");
if (songs.Count > 0)
{
PlaylistHelper.ClearPlaylist("music", false);
List <PlayListItem> items = new List <PlayListItem>();
foreach (Song s in songs)
{
items.Add(PlaylistHelper.ToPlayListItem(s));
}
PlaylistHelper.AddPlaylistItems(PlayListType.PLAYLIST_MUSIC, items, 0);
PlaylistHelper.StartPlayingPlaylist("music", startPos, true);
}
}
public string GetArtistsImgUrl(string letter)
{
try
{
if (string.IsNullOrEmpty(letter))
{
return(string.Empty);
}
string lsSQL = string.Format("select * from ARTISTS WHERE ARTIST_NAME like '{0}' order by ARTIST_NAME", DatabaseUtility.RemoveInvalidChars(letter));
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
return(DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG"));
}
}
catch (Exception exception)
{
Log.Error(exception);
}
return(string.Empty);
}
private void addButton_Click(object sender, EventArgs e)
{
string newContent = newBookContent.Text.ToString();
int newChapNo = book.Chapter_no + 1;
int temp = newChapNo + 1;
if (newChapNo > book.Chapter_no)
{
DatabaseUtility.updateChapterNoByBookId(newChapNo, bookId);
DatabaseUtility.modifyBookContent(book, newChapNo, newContent);
DatabaseUtility.updateModifyDateByBookId(bookId);
theView[8, row].Value = System.DateTime.Now;
}
else
{
MessageBox.Show("Please enter the book content!");
}
refresh();
newBookContent.Text = "";
newChapterNoLabel.Text = string.Format("New Chapter : {0}", temp.ToString());
}
/// <summary>
/// Scalar Value: illustrates how to retrieve scalar (single value) data from database.
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
///Utils.GenerationCommandType.ScalarValue
public static string GetFirstNameByEmployeeId(int employeeId)
{
if (employeeId < GetIdMinValue)
{
throw (new ArgumentOutOfRangeException("employeeId"));
}
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@employeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReturnVal", SqlDbType.NVarChar, 10, ParameterDirection.Output, null);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETEMPLOYEEFIRSTNAME_BY_EMPLOYEEID);
//executing the command
DatabaseUtility.ExecuteScalarCmd(sqlCmd);
string returnValue = (string)sqlCmd.Parameters["@ReturnVal"].Value;
return(returnValue);
}
public void SetUp()
{
var connection = ConfigurationManager.ConnectionStrings["MySql"].ToString();
_databaseUtility = new DatabaseUtility(connection);
_databaseUtility.Create();
_databaseUtility.Populate();
inputEventStore = new RavenNEventStore(Constants.INPUT_EVENT_STORE);
outputEventStore = new RavenNEventStore(Constants.OUTPUT_EVENT_STORE);
inputJournaler = new Journaler(inputEventStore);
outputJournaler = new Journaler(outputEventStore);
IList <CurrencyPair> currencyPairs = new List <CurrencyPair>();
currencyPairs.Add(new CurrencyPair("BTCUSD", "USD", "BTC"));
currencyPairs.Add(new CurrencyPair("BTCLTC", "LTC", "BTC"));
currencyPairs.Add(new CurrencyPair("BTCDOGE", "DOGE", "BTC"));
_exchange = new Exchange(currencyPairs);
_exchange.EnableSnaphots(5000);
InputDisruptorPublisher.InitializeDisruptor(new IEventHandler <InputPayload>[] { _exchange, inputJournaler });
OutputDisruptor.InitializeDisruptor(new IEventHandler <byte[]>[] { outputJournaler });
}
private Boolean CheckIfExists()
{
int count = 0;
using (SQLiteConnection conn = DatabaseUtility.GetConnection())
{
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "SELECT COUNT(*) FROM travel_section WHERE travel_section_id = @travel_section_id";
command.Parameters.AddWithValue("@travel_section_id", travel_section_id);
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
count = reader.GetInt32(0);
}
}
}
}
return(count > 0);
}
public void AddUser(string lastName, string firstName, string username, string password, Role role)
{
if (string.IsNullOrEmpty(lastName))
{
throw new ArgumentNullException("LastName");
}
if (string.IsNullOrEmpty(firstName))
{
throw new ArgumentNullException("FirstName");
}
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("Username");
}
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("Password");
}
if (UserExists(username))
{
throw new UserExistsException("Username " + username + " already exists");
}
string salt = Hashing.CreateSalt(16);
string hashedPassword = Hashing.GenerateSaltedHash(password, salt);
int newUserID = TotalUsers() + 1;
string commandText = "INSERT INTO " + _usersTableName + " (UserID, LastName, FirstName, Username, Password, Salt, Role, Active) VALUES ('" + newUserID + "','" + lastName + "','" + firstName + "','" + username + "','" + hashedPassword + "','" + salt + "','" + (int)role + "'," + "1" + ")";
try
{
DatabaseUtility.ModifyDatabase(_connectionString, commandText);
}
catch (ServerUnavailableException)
{
throw new ServerUnavailableException();
}
}
public int GetRotation(string strPicture)
{
string strSQL = "";
try
{
string strPic = strPicture;
int iRotation;
DatabaseUtility.RemoveInvalidChars(ref strPic);
strSQL = String.Format("select * from tblPicture where strFile like '{0}'", strPic);
using (SqlCommand cmd = _connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
iRotation = (int)reader["iRotation"];
reader.Close();
return(iRotation);
}
reader.Close();
}
}
ExifMetadata extractor = new ExifMetadata();
ExifMetadata.Metadata metaData = extractor.GetExifMetadata(strPicture);
iRotation = EXIFOrientationToRotation(Convert.ToInt32(metaData.Orientation.Hex));
AddPicture(strPicture, iRotation);
return(0);
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
return(0);
}
public override void ViewDidLoad()
{
base.ViewDidLoad();
// Perform any additional setup after loading the view, typically from a nib.
String weekText = NSBundle.MainBundle.LocalizedString("Vernacular_P0_stats_week_label", null).PrepareForLabel();
lblWeekText.Text = weekText;
lblWeekText.TextColor = StyleSettings.TextOnDarkColor();
String overallText = NSBundle.MainBundle.LocalizedString("Vernacular_P0_stats_overall_label", null).PrepareForLabel();
lblOverallText.Text = overallText;
lblOverallText.TextColor = StyleSettings.TextOnDarkColor();
String lastTrackText = NSBundle.MainBundle.LocalizedString("Vernacular_P0_stats_last_track_label", null).PrepareForLabel();
lblLastTrackText.Text = lastTrackText;
lblLastTrackText.TextColor = StyleSettings.TextOnDarkColor();
try
{
using (var conn = DatabaseUtility.OpenConnection())
{
var week = StatisticHelper.GetPeriodSummary(conn, StatisticPeriod.Week);
var overall = StatisticHelper.GetPeriodSummary(conn, StatisticPeriod.Overall);
var last = StatisticHelper.GetLastTrack(conn);
UpdateKmCounter(lblLastTrack, last?.DistanceTraveled);
UpdateKmCounter(lblWeek, week.Distance);
UpdateKmCounter(lblOverall, overall.Distance);
}
}
catch (Exception ex)
{
Log.Error(ex, "Failed to load statistics");
}
// TODO: add share button
}
public bool AddFavoriteCategory(Category cat, string siteName)
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
string categoryHierarchyName = EscapeString(cat.RecursiveName("|"));
//check if the category is already in the favorite list
if (m_db.Execute(string.Format("select CAT_ID from FAVORITE_Categories where CAT_Hierarchy='{0}' AND CAT_SITE_ID='{1}'", categoryHierarchyName, siteName)).Rows.Count > 0)
{
Log.Instance.Info("Favorite Category {0} already in database", cat.Name);
return(true);
}
Log.Instance.Info("inserting favorite category on site {0} with name: {1}, desc: {2}, image: {3}",
siteName, cat.Name, cat.Description, cat.Thumb, siteName);
string lsSQL =
string.Format(
"insert into FAVORITE_Categories(CAT_Name,CAT_Desc,CAT_ThumbUrl,CAT_Hierarchy,CAT_SITE_ID,CAT_IS_SEARCH,SEARCH_CAT_HASSUBS) " +
"VALUES('{0}','{1}','{2}','{3}','{4}',{5},{6})",
DatabaseUtility.RemoveInvalidChars(cat.Name),
cat.Description == null ? "" : DatabaseUtility.RemoveInvalidChars(cat.Description),
cat.Thumb,
categoryHierarchyName,
siteName,
cat.ParentCategory is SearchCategory,
cat.HasSubCategories
);
m_db.Execute(lsSQL);
if (m_db.ChangedRows() > 0)
{
Log.Instance.Info("Favorite Category {0} inserted successfully into database", cat.Name);
return(true);
}
else
{
Log.Instance.Warn("Favorite Category {0} failed to insert into database", cat.Name);
return(false);
}
}
public GenericListItemCollections GetTopPlayed(int numPlay)
{
GenericListItemCollections res = new GenericListItemCollections();
try
{
string lsSQL =
string.Format(
"select * from (SELECT VIDEOS.VIDEO_ID AS VIDEO_ID, ARTIST_ID, TITLE, IMG_URL, count(PLAY_HISTORY.VIDEO_ID) as num_play FROM VIDEOS, PLAY_HISTORY WHERE VIDEOS.VIDEO_ID=PLAY_HISTORY.VIDEO_ID group by VIDEOS.VIDEO_ID, ARTIST_ID, TITLE, IMG_URL order by count(PLAY_HISTORY.VIDEO_ID) desc)where num_play>" +
numPlay.ToString());
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
YouTubeEntry youTubeEntry = new YouTubeEntry();
youTubeEntry.AlternateUri = new AtomUri("http://www.youtube.com/watch?v=" + DatabaseUtility.Get(loResultSet, iRow, "VIDEO_ID"));
youTubeEntry.Title = new AtomTextConstruct();
youTubeEntry.Title.Text = DatabaseUtility.Get(loResultSet, iRow, "TITLE");
youTubeEntry.Media = new MediaGroup();
youTubeEntry.Media.Description = new MediaDescription("");
youTubeEntry.Id = new AtomId(youTubeEntry.AlternateUri.Content);
GenericListItem listItem = new GenericListItem()
{
Title = youTubeEntry.Title.Text,
IsFolder = false,
LogoUrl = DatabaseUtility.Get(loResultSet, iRow, "IMG_URL"),
Tag = youTubeEntry,
Title2 = DatabaseUtility.Get(loResultSet, iRow, "num_play"),
//ParentTag = artistItem
};
res.Items.Add(listItem);
}
;
}
catch (Exception exception)
{
Log.Error(exception);
}
return(res);
}
public static Emulator getEmulator(int uid)
{
if (uid == -1)
{
return(getPCDetails());
}
SQLiteResultSet result = dbExecute("SELECT * FROM Emulators WHERE uid=" + uid);
if (result.Rows.Count > 0)
{
Emulator item = new Emulator();
item.UID = DatabaseUtility.GetAsInt(result, 0, 0);
item.PathToEmulator = decode(DatabaseUtility.Get(result, 0, 1));
item.Position = DatabaseUtility.GetAsInt(result, 0, 2);
item.PathToRoms = decode(DatabaseUtility.Get(result, 0, 3));
item.Title = decode(DatabaseUtility.Get(result, 0, 4));
item.Filter = DatabaseUtility.Get(result, 0, 5);
item.WorkingFolder = decode(DatabaseUtility.Get(result, 0, 6));
item.UseQuotes = Boolean.Parse(DatabaseUtility.Get(result, 0, 7));
item.View = DatabaseUtility.GetAsInt(result, 0, 8);
item.Arguments = decode(DatabaseUtility.Get(result, 0, 9));
item.SuspendRendering = Boolean.Parse(DatabaseUtility.Get(result, 0, 10));
item.EnableGoodmerge = Boolean.Parse(DatabaseUtility.Get(result, 0, 11));
item.GoodmergePref1 = decode(DatabaseUtility.Get(result, 0, 12));
item.GoodmergePref2 = decode(DatabaseUtility.Get(result, 0, 13));
item.GoodmergePref3 = decode(DatabaseUtility.Get(result, 0, 14));
item.GoodmergeTempPath = decode(DatabaseUtility.Get(result, 0, 15));
item.Grade = DatabaseUtility.GetAsInt(result, 0, "Grade");
item.Company = decode(DatabaseUtility.Get(result, 0, "Company"));
item.Yearmade = DatabaseUtility.GetAsInt(result, 0, "Yearmade");
item.Description = decode(DatabaseUtility.Get(result, 0, "Description"));
item.MountImages = Boolean.Parse(DatabaseUtility.Get(result, 0, "mountimages"));
return(item);
}
else
{
return(null);
}
}
public bool Load()
{
_watchItemList.Clear();
if (DatabaseUtility.TableExists(_sqlClient, "WatchList"))
{
SQLiteResultSet resultSet = _sqlClient.Execute("SELECT * FROM WatchList;");
foreach (SQLiteResultSet.Row row in resultSet.Rows)
{
WatchItem watch = new WatchItem();
watch.SearchTerm = row.fields[(int)resultSet.ColumnIndices["Search"]];
watch.Added = new DateTime(Convert.ToInt64(row.fields[(int)resultSet.ColumnIndices["Added"]]));
watch.Label = row.fields[(int)resultSet.ColumnIndices["Label"]];
_watchItemList.Add(watch);
}
}
if (DatabaseUtility.TableExists(_sqlClient, "SeriesWatchList"))
{
SQLiteResultSet resultSet = _sqlClient.Execute("SELECT * FROM SeriesWatchList;");
foreach (SQLiteResultSet.Row row in resultSet.Rows)
{
SeriesItem watch = new SeriesItem();
watch.show = new Series();
watch.show.Name = row.fields[(int)resultSet.ColumnIndices["SeriesName"]];
watch.show.OriginalName = row.fields[(int)resultSet.ColumnIndices["OriginalSeriesName"]];
watch.show.ID = row.fields[(int)resultSet.ColumnIndices["SeriesID"]];
watch.Added = new DateTime(Convert.ToInt64(row.fields[(int)resultSet.ColumnIndices["Added"]]));
watch.quality = row.fields[(int)resultSet.ColumnIndices["Quality"]];
watch.tracker = row.fields[(int)resultSet.ColumnIndices["Tracker"]];
watch.type = row.fields[(int)resultSet.ColumnIndices["Type"]];
watch.source = row.fields[(int)resultSet.ColumnIndices["Source"]];
watch.folder = row.fields[(int)resultSet.ColumnIndices["Folder"]];
watch.includespecials = Convert.ToBoolean(row.fields[(int)resultSet.ColumnIndices["IncludeSpecials"]]);
_watchItemList.Add(watch);
}
}
return(true);
}
/// <summary>
/// sample custom method for get
/// returns a collection for records of DataTable type
/// Generic Data Table: illustrates how to retrieve a collection of data from database and populate it to a .NET generic DataTable instance.
/// </summary>
/// <param name="reportsTo"></param>
/// <returns></returns>
///Utils.GenerationCommandType.GenericDataTable
public static DataTable GetEmployeesByReportsTo(int reportsTo)
{
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.CurrentCulture;
using (SqlConnection cn = new SqlConnection(ConnectionStringManager.DefaultDBConnectionString))
{
//sql command
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = cn;
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO);
//adapter
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = sqlCmd;
adapter.Fill(ds);
}
return(ds.Tables[0]);
}
public List <FavoriteDbCategory> GetFavoriteCategories(string siteName)
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
var results = new List <FavoriteDbCategory>();
SQLiteResultSet resultSet = m_db.Execute(string.Format("select * from Favorite_Categories where CAT_SITE_ID = '{0}'", siteName));
for (int iRow = 0; iRow < resultSet.Rows.Count; iRow++)
{
results.Add(
new FavoriteDbCategory()
{
Name = DatabaseUtility.Get(resultSet, iRow, "CAT_Name"),
Description = DatabaseUtility.Get(resultSet, iRow, "CAT_Desc"),
Thumb = DatabaseUtility.Get(resultSet, iRow, "CAT_ThumbUrl"),
Id = DatabaseUtility.GetAsInt(resultSet, iRow, "CAT_ID"),
RecursiveName = DatabaseUtility.Get(resultSet, iRow, "CAT_Hierarchy"),
IsSearchCat = DatabaseUtility.GetAsInt(resultSet, iRow, "CAT_IS_SEARCH") == 1,
SearchCatHasSubcategories = DatabaseUtility.GetAsInt(resultSet, iRow, "SEARCH_CAT_HASSUBS") == 1
});
}
return(results);
}
public static List <Product> GetProducts()
{
var list = new List <Product>();
String commandText = @"SELECT * FROM test_Products ORDER BY ProductId DESC";
var parameterList = new List <SqlParameter>();
string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
var commandType = CommandType.Text;
DataSet dataSet = DatabaseUtility.ExecuteDataSet(new SqlConnection(connectionString), commandText, commandType, parameterList.ToArray());
if (dataSet.Tables.Count > 0)
{
using (DataTable dt = dataSet.Tables[0])
{
foreach (DataRow dr in dt.Rows)
{
var e = GetProductFromDataRow(dr);
list.Add(e);
}
}
}
return(list);
}
public void DeleteFolderSetting(string path, string Key)
{
if (path == null)
{
return;
}
if (path == string.Empty)
{
return;
}
if (Key == null)
{
return;
}
if (Key == string.Empty)
{
return;
}
try
{
string pathFiltered = Utils.RemoveTrailingSlash(path);
string keyFiltered = Key;
DatabaseUtility.RemoveInvalidChars(ref pathFiltered);
DatabaseUtility.RemoveInvalidChars(ref keyFiltered);
int PathId = AddPath(pathFiltered);
if (PathId < 0)
{
return;
}
string strSQL = String.Format("delete from tblFolderSetting where idPath={0} and tagName ='{1}'", PathId,
keyFiltered);
SqlServerUtility.ExecuteNonQuery(_connection, strSQL);
}
catch (Exception ex)
{
Log.Error(ex);
}
}
public Modify_Chapter(int theBookId, int theRow, Form theForm)
{
InitializeComponent();
bookId = theBookId;
if (DatabaseUtility.getBookByIDInAdmin(ref book, bookId) == -1)
{
MessageBox.Show("There is no Internet!");
}
else
{
for (int i = 1; i <= book.Chapter_no; i++)
{
chapterBox.Items.Add(i);
}
}
if (DatabaseUtility.getAuthors(ref authorList) == -1)
{
MessageBox.Show("There is no Internet!");
}
else
{
for (int i = 0; i < authorList.Count; i++)
{
user = (User)authorList[i];
authorBox.Items.Add(user.User_name);
}
}
User temp = (User)authorList[0];
initCon = chapterContent.Text.ToString();
initNewCon = newBookContent.Text.ToString();
manageForm = theForm;
row = theRow;
theView = Manage.getView();
int tempNo = (book.Chapter_no + 1);
newChapterNoLabel.Text = string.Format("New Chapter : {0}", tempNo.ToString());
}
public bool IsActive(string username)
{
if (string.IsNullOrEmpty(username))
{
throw new ArgumentNullException("Username");
}
string commandText = "SELECT * from " + _usersTableName + " WHERE Username = '******';";
try
{
using (DataTable contents = DatabaseUtility.QueryDatabase(_connectionString, commandText))
{
if (contents != null && contents.Rows.Count > 0)
{
if (contents.Rows[0]["Active"].ToString().Equals("True") || contents.Rows[0]["Active"].ToString().Equals("1"))
{
return(true);
}
else if (contents.Rows[0]["Active"].ToString().Equals("False") || contents.Rows[0]["Active"].ToString().Equals("0"))
{
return(false);
}
else
{
throw new Exception("Status could not be parsed.");
}
}
else
{
throw new UserDoesNotExistException("User " + username + " does not exist");
}
}
}
catch (ServerUnavailableException)
{
throw new ServerUnavailableException();
}
}
private void Open()
{
try
{
// Maybe called by an exception
if (m_db != null)
{
try
{
m_db.Close();
m_db.Dispose();
Log.Warn("PictureDatabaseSqlLite: Disposing current instance..");
}
catch (Exception) {}
}
// Open database
try
{
Directory.CreateDirectory(Config.GetFolder(Config.Dir.Database));
}
catch (Exception) {}
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "PictureDatabase.db3"));
// Retry 10 times on busy (DB in use or system resources exhausted)
m_db.BusyRetries = 10;
// Wait 100 ms between each try (default 10)
m_db.BusyRetryDelay = 100;
DatabaseUtility.SetPragmas(m_db);
CreateTables();
InitSettings();
}
catch (Exception ex)
{
Log.Error("picture database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
Log.Info("picture database opened");
}
public List <ArtistItem> GetArtistsByTag(string tag)
{
List <ArtistItem> res = new List <ArtistItem>();
string lsSQL =
string.Format(
"select distinct * from ARTISTS,TAGS where ARTISTS.ARTIST_ID=TAGS.ARTIST_ID and TAGS.ARTIST_TAG like '{0}' order by ARTIST_NAME",
tag);
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Add(new ArtistItem()
{
Id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID"),
Name = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_NAME"),
Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG"),
User = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_USER"),
Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG")
});
}
return(res);
}
public static DBValue GetOptions(string property)
{
try
{
lock (thisLock)
{
DBValue retValue;
if (optionsCache.TryGetValue(property, out retValue))
{
return(retValue);
}
// ensure our sql query will be using a valid string
string convertedProperty = property;
DatabaseUtility.RemoveInvalidChars(ref convertedProperty);
string sqlQuery = "SELECT value FROM options WHERE property = '" + convertedProperty + "'";
SQLiteResultSet sqlResults = DBTVSeries.Execute(sqlQuery);
if (sqlResults.Rows.Count > 0)
{
string dbValue = DatabaseUtility.Get(sqlResults, 0, "value");
if (!optionsCache.ContainsKey(property))
{
optionsCache.Add(property, dbValue);
}
return(dbValue);
}
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An error occurred (" + ex.Message + ").");
}
return(null);
}
public bool AddFavoriteVideo(VideoInfo foVideo, string titleFromUtil, string siteName)
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
string title = string.IsNullOrEmpty(titleFromUtil) ? "" : DatabaseUtility.RemoveInvalidChars(titleFromUtil);
string desc = string.IsNullOrEmpty(foVideo.Description) ? "" : DatabaseUtility.RemoveInvalidChars(foVideo.Description);
string thumb = string.IsNullOrEmpty(foVideo.Thumb) ? "" : DatabaseUtility.RemoveInvalidChars(foVideo.Thumb);
string url = string.IsNullOrEmpty(foVideo.VideoUrl) ? "" : DatabaseUtility.RemoveInvalidChars(foVideo.VideoUrl);
string length = string.IsNullOrEmpty(foVideo.Length) ? "" : DatabaseUtility.RemoveInvalidChars(foVideo.Length);
string airdate = string.IsNullOrEmpty(foVideo.Airdate) ? "" : DatabaseUtility.RemoveInvalidChars(foVideo.Airdate);
string other = DatabaseUtility.RemoveInvalidChars(foVideo.GetOtherAsString());
Log.Instance.Info("inserting favorite on site '{4}' with title: '{0}', desc: '{1}', thumb: '{2}', url: '{3}'", title, desc, thumb, url, siteName);
//check if the video is already in the favorite list
string lsSQL = string.Format("select VDO_ID from FAVORITE_VIDEOS where VDO_SITE_ID='{0}' AND VDO_URL='{1}' and VDO_OTHER_NFO='{2}'", siteName, url, other);
if (m_db.Execute(lsSQL).Rows.Count > 0)
{
Log.Instance.Info("Favorite Video '{0}' already in database", title);
return(false);
}
lsSQL =
string.Format(
"insert into FAVORITE_VIDEOS(VDO_NM,VDO_URL,VDO_DESC,VDO_TAGS,VDO_LENGTH,VDO_OTHER_NFO,VDO_IMG_URL,VDO_SITE_ID)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
title, url, desc, airdate, length, other, thumb, siteName);
m_db.Execute(lsSQL);
if (m_db.ChangedRows() > 0)
{
Log.Instance.Info("Favorite '{0}' inserted successfully into database", foVideo.Title);
return(true);
}
else
{
Log.Instance.Warn("Favorite '{0}' failed to insert into database", foVideo.Title);
return(false);
}
}
public bool CorrectPassword(int userID, string password)
{
if (string.IsNullOrEmpty(password))
{
throw new ArgumentNullException("Password");
}
string commandText = "SELECT * FROM " + _usersTableName + " WHERE UserID = " + userID;
try
{
using (DataTable contents = DatabaseUtility.QueryDatabase(_connectionString, commandText))
{
if (contents != null && contents.Rows.Count > 0)
{
string hashedPassword = contents.Rows[0]["Password"].ToString();
string salt = contents.Rows[0]["Salt"].ToString();
if (hashedPassword.Equals(Hashing.GenerateSaltedHash(password, salt)))
{
return(true);
}
else
{
return(false);
}
}
else
{
throw new UserDoesNotExistException("User " + userID + " does not exist");
}
}
}
catch (ServerUnavailableException)
{
throw new ServerUnavailableException();
}
}
private static void bwAsync_Worker(object sender, DoWorkEventArgs e)
{
while (true)
{
System.Threading.Thread.Sleep(300);
try
{
if (QueueLogRecord.Count > 0)
{
SanitaDataLogModel result = QueueLogRecord.Dequeue();
StringBuilder sql = new StringBuilder();
sql.Append(" INSERT INTO tblLogData (");
sql.Append(" LogApp,");
sql.Append(" LogUser,");
sql.Append(" SoftVersion,");
sql.Append(" LogTime,");
sql.Append(" IPAddress,");
sql.Append(" ComputerName,");
sql.Append(" LogValue) ");
sql.Append(" VALUES( " + DatabaseUtility.Escape(result.App) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.User) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.SoftVersion) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.LogTime) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.IPAddress) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.ComputerName) + ", ");
sql.Append(" " + DatabaseUtility.Escape(result.LogValue) + ") ");
// Assign new customer Id back to business object
baseDAO.DoInsert(sql.ToString());
}
}
catch (Exception ex)
{
}
}
}
private async void BtnUpdateInfo_ClickAsync(object sender, EventArgs e)
{
FragmentTransaction transaction = FragmentManager.BeginTransaction();
DialogInfo dialog = new DialogInfo();
dialog.Show(transaction: transaction, "dialog info");
ISharedPreferences prefs = PreferenceManager.GetDefaultSharedPreferences(this.Activity);
string username = prefs.GetString("username", "");
string password = prefs.GetString("password", "");
if (String.IsNullOrEmpty(username) || String.IsNullOrEmpty(password))
{
Toast.MakeText(this.Activity, "Hãy thử đăng nhập lại !!!", ToastLength.Short).Show();
//Show message failture
return;
}
if (Connectivity.NetworkAccess == NetworkAccess.None)
{
dialog.Dismiss();
Toast.MakeText(this.Activity, "Không thể kết nối với Wifi/3G/4G", ToastLength.Short).Show();
return;
}
bool isLoginSuccess = await LoginUtility.CrawlData(username, password).ConfigureAwait(true);
if (isLoginSuccess)
{
DatabaseUtility.GetDataScheduler();
DatabaseUtility.GetDataExam();
DatabaseUtility.SaveInfoDatabase();
dialog.Dismiss();
Toast.MakeText(this.Activity, "Cập nhật thành công !", ToastLength.Short).Show();
}
else
{
dialog.Dismiss();
Toast.MakeText(this.Activity, "Cập nhật thất bại !", ToastLength.Short).Show();
}
}
/// <summary>
/// The Database command handler, which handles console commands for database file and schema management
/// </summary>
/// <param name="input">The full console command input</param>
/// <returns>A value indicating whether the nntpServer should terminate</returns>
private static bool DatabaseCommand(string input)
{
var parts = input.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (parts.Length < 2)
{
Console.WriteLine("One parameter is required.");
return(false);
}
switch (parts[1].ToLowerInvariant())
{
case "annihilate":
{
DatabaseUtility.RebuildSchema();
Console.WriteLine("Database recreated");
break;
}
case "update":
{
DatabaseUtility.UpdateSchema();
break;
}
case "verify":
{
DatabaseUtility.VerifyDatabase();
break;
}
default:
Console.WriteLine("Unknown parameter {0} specified for Debug command", parts[1]);
break;
}
return(false);
}
public static List <Station> GetNoNearbyStations(int station_id)
{
List <Station> nearby_stations = new List <Station>();
using (SQLiteConnection conn = DatabaseUtility.GetConnection())
{
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "SELECT * FROM station WHERE station_id NOT IN(SELECT station_one_id FROM border_station WHERE station_one_id = @station_id OR station_two_id = @station_id) AND station_id NOT IN(SELECT station_two_id FROM border_station WHERE station_one_id = @station_id OR station_two_id = @station_id); ";
command.Parameters.AddWithValue("@station_id", station_id);
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id_station = reader.GetInt32(0);
nearby_stations.Add(Station.Find(id_station));
}
}
}
}
return(nearby_stations ?? null);
}
