public async Task Load(string rootFolder) { var createTables = false; if (!_databaseFile.Exists) { SQLiteConnection.CreateFile(_databaseFile.FullName); createTables = true; } var dataProvider = new IDataProvider[] {Images, Artists, Albums, Tracks, Playlists}; _connection = new SQLiteConnection($"Data Source={_databaseFile.FullName};Version=3;"); await _connection.OpenAsync(); if(createTables) foreach (var provider in dataProvider) await provider.CreateTables(_connection); foreach (var data in dataProvider) await data.Load(_connection); var userDataFileInfo = new FileInfo(Path.Combine(rootFolder, UserDataFilename)); if (userDataFileInfo.Exists) await UserData.LoadFromFile(userDataFileInfo.FullName); LoadSettings(); }
public async Task Clear() { using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); await conn.ExecuteAsync("delete from [User]").ConfigureAwait(false); } }
public async Task<ObservableCollection<Todo>> GetTodos() { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { using (var cmd = new SQLiteCommand("SELECT * FROM TODOs", conn)) { await conn.OpenAsync(); using (var reader = await cmd.ExecuteReaderAsync()) { if (!reader.HasRows) { return new ObservableCollection<Todo>(); } var todos = new ObservableCollection<Todo>(); Todo todo = null; while (reader.Read()) { todo = new Todo(); todo.Id = Convert.ToInt32(reader["Id"].ToString()); todo.CreateDate = Convert.ToDateTime(reader["CreateDate"].ToString()); todo.Task = reader["Task"].ToString(); todo.Done = Convert.ToBoolean(reader["Done"].ToString()); todo.DueDate = reader["DueDate"] == DBNull.Value ? null as DateTime? : Convert.ToDateTime(reader["DueDate"].ToString()); todos.Add(todo); } return todos; } } } }
/// <summary> /// Connects to db. /// </summary> public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } SQLiteConnection.SetMemoryStatus(false); var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, CacheSize = cacheSize ?? 2000, SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal, // This is causing crashing under linux Pooling = enablePooling && Environment.OSVersion.Platform == PlatformID.Win32NT, ReadOnly = isReadOnly }; var connectionString = connectionstr.ConnectionString; if (!enablePooling) { logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString); } var connection = new SQLiteConnection(connectionString); await connection.OpenAsync().ConfigureAwait(false); return connection; }
public async void SetComplete(Todo todo) { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { using (var cmd = new SQLiteCommand("UPDATE TODOs SET Done = 1 WHERE Id = " + todo.Id, conn)) { await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
public async Task<UserInfo> AddUser(string userLogin, string displayName) { var res = new UserInfo() {UserLogin = userLogin, DisplayName = displayName, Created = DateTime.UtcNow}; using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteAsync("insert or ignore into [User] (Login, Display, Created) values(@userLogin, @displayName, @created)", new {userLogin, displayName, created = res.Created }).ConfigureAwait(false); if (count == 0) throw new Exception($"The user '{userLogin}' already exists"); } return res; }
/// <summary> /// Connects to DB. /// </summary> /// <param name="dbPath">The db path.</param> /// <returns>Task{System.Boolean}.</returns> /// <exception cref="System.ArgumentNullException">dbPath</exception> protected Task ConnectToDb(string dbPath) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } DbFileName = dbPath; var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, CacheSize = 40960, SyncMode = SynchronizationModes.Off, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; Connection = new SQLiteConnection(connectionstr.ConnectionString); return Connection.OpenAsync(); }
public async void ConvertToSqlite(string pathToExcelFile) { SetPathToParentDirectoryOfDatabaseFile(); if (File.Exists(PathToDatabaseArchiveFile) && !File.Exists(_pathToDatabaseFile)) ZipFile.ExtractToDirectory(PathToDatabaseArchiveFile, _pathToDatabase); using ( var dbSqLiteConnection = new SQLiteConnection((WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString))) { //load data from xlsx(excel) file var ds = await SetDataSet(pathToExcelFile); await dbSqLiteConnection.OpenAsync(); //Set data from rows for (var i = 0; i < ds.Tables[0].Rows.Count; i++) { var rowsStringBuilder = new StringBuilder(); //load data from row to string for (var j = 0; j < ds.Tables[0].Rows[i].ItemArray.Length; j++) { var row = string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString()) ? "NULL" : ds.Tables[0].Rows[i][j].ToString(); if (j < ds.Tables[0].Rows[i].ItemArray.Length - 1) rowsStringBuilder.Append(row + ","); else rowsStringBuilder.Append(row); } //Insert data into table var sqlQuery = "Insert into " + TableName + "(" + ColumnNames + ") Values(" + rowsStringBuilder + ");"; using (var cmd = new SQLiteCommand(sqlQuery, dbSqLiteConnection)) await cmd.ExecuteNonQueryAsync(); } dbSqLiteConnection.Shutdown(); dbSqLiteConnection.Close(); } if (File.Exists(PathToDatabaseArchiveFile)) File.Delete(PathToDatabaseArchiveFile); ZipFile.CreateFromDirectory(_pathToDatabase, PathToDatabaseArchiveFile); }
async Task LoadAsync() { Db = new SQLiteConnection($"Data Source={dbPath}"); await Db.OpenAsync(); var canContinue = await TryCreateTablesAsync(); if (!canContinue) { // If the expected schema can't be created, delete // the cache file and start over. Db.Dispose(); File.Delete(dbPath); SQLiteConnection.CreateFile(dbPath); Db = new SQLiteConnection($"Data Source={dbPath}"); await Db.OpenAsync(); // If table creation still fails, throw canContinue = await TryCreateTablesAsync(); if (!canContinue) { throw new Exception("Unable to create cache database"); } } }
public static async Task<bool> InsertReadedFile(string path) { return await Task.Factory.StartNew(async () => { using (var connection = new SQLiteConnection(ConnectionString)) { await connection.OpenAsync(); try { var command = connection.CreateCommand(); command.CommandText = $"INSERT INTO readed_files(path) VALUES ('{path}');"; await command.ExecuteNonQueryAsync().ConfigureAwait(false); return true; } catch (SQLiteException) { return false; } } }).Result; }
public async Task<UserMessage> AddMessage(string userLogin, string text) { var res = new UserMessage() {Text = text, UserLogin = userLogin, Created = DateTime.UtcNow}; using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteAsync( "insert or ignore into [Message] (Text, Created, UserId) select @text as Text, @created as Created, UserId from [User] where [Login] = @userLogin", new {text, created = res.Created, userLogin}).ConfigureAwait(false); if (count == 0) { int userId = await conn.ExecuteScalarAsync<int>( "insert into [User] (Login, Created) values(@userLogin, @created); SELECT last_insert_rowid() FROM [User]", new {userLogin, created = res.Created}).ConfigureAwait(false); await conn.ExecuteAsync("insert into [Message] (Text, Created, UserId) values(@text, @created, @userId)", new { text, created = res.Created, userId}).ConfigureAwait(false); } } return res; }
public async void AddTodo(Todo todo) { using (var conn = new SQLiteConnection("Data Source=TodoList.s3db")) { var sb = new StringBuilder(); sb.Append("INSERT INTO TODOs (Task, DueDate, CreateDate, Done) VALUES ('"); sb.Append(todo.Task); sb.Append("', '"); sb.Append(todo.DueDate.Value.ToString("yyyy-MM-dd HH:mm")); sb.Append("', '"); sb.Append(todo.CreateDate.ToString("yyyy-MM-dd HH:mm")); sb.Append("', "); sb.Append(todo.Done ? 1 : 0); sb.Append(");"); Debug.WriteLine("Executing: " + sb.ToString()); using (var cmd = new SQLiteCommand(sb.ToString(), conn)) { await conn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } } }
/// <summary> /// Connects to db. /// </summary> /// <param name="dbPath">The db path.</param> /// <param name="logger">The logger.</param> /// <returns>Task{IDbConnection}.</returns> /// <exception cref="System.ArgumentNullException">dbPath</exception> public static async Task<IDbConnection> ConnectToDb(string dbPath, ILogger logger) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath); var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, CacheSize = 2000, SyncMode = SynchronizationModes.Full, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; var connection = new SQLiteConnection(connectionstr.ConnectionString); await connection.OpenAsync().ConfigureAwait(false); return connection; }
/// <summary> /// Connects to db. /// </summary> /// <param name="dbPath">The db path.</param> /// <param name="logger">The logger.</param> /// <returns>Task{IDbConnection}.</returns> /// <exception cref="System.ArgumentNullException">dbPath</exception> public static async Task<IDbConnection> ConnectToDb(string dbPath, ILogger logger) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } logger.Info("Opening {0}", dbPath); #if __MonoCS__ var connectionstr = new SqliteConnectionStringBuilder { PageSize = 4096, CacheSize = 4096, SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Off }; var connection = new SqliteConnection(connectionstr.ConnectionString); #else var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, CacheSize = 4096, SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; var connection = new SQLiteConnection(connectionstr.ConnectionString); #endif await connection.OpenAsync().ConfigureAwait(false); return connection; }
/// <summary> /// Runs the "ANALYZE;" SQL command on the database to update the statistics tables for better query performance /// </summary> /// <returns>Task that completes when the "ANALYZE;" command has finished</returns> private async Task PerformDatabaseMaintenanceAsync() { ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Performing database maintenance..."); try { using (var connection = new SQLiteConnection(_connectionString)) { await connection.OpenAsync(); using (var command = connection.CreateCommand()) { command.CommandText = "ANALYZE;"; await command.ExecuteNonQueryAsync(); } connection.Close(); } } catch (Exception e) { ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Error while performing database maintenance:", e); } ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Database maintenance finished"); LogStatistics(); }
private async Task InitializeAsync() { if (_connection == null) { var builder = new SQLiteConnectionStringBuilder() { FailIfMissing = true, ReadOnly = !this.IsWriteable, DataSource = this.StorageLocation, Version = 3, UseUTF16Encoding = true, }; _connection = new SQLiteConnection(builder.ToString()); await _connection.OpenAsync(); } }
public async Task<DataPage<UserMessage>> GetUserMessages(string userLogin, int pageNumber, int pageSize) { ValidatePagination(pageNumber, pageSize); using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); if (await conn.ExecuteScalarAsync<int>("select count(*) from [User] where Login = @userLogin", new { userLogin }).ConfigureAwait(false) == 0) return new DataPage<UserMessage>(pageNumber, pageSize, 0, null); //not found int count = await conn.ExecuteScalarAsync<int>("select count(*) from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin", new {userLogin}).ConfigureAwait(false); string q = "select u.Login as UserLogin, m.Text, m.Created from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin order by m.Created"; object prms; if (pageSize == -1) prms = new { userLogin }; else { q += " limit @size offset @skip"; prms = new { userLogin, size = pageSize, skip = (pageNumber - 1) * pageSize }; } return new DataPage<UserMessage>(pageNumber, pageSize, count, (await conn.QueryAsync<UserMessage>(q, prms)).ToList()); } }
private async void InitializeFromDatabase() { var sqlConStr = new SQLiteConnectionStringBuilder { DataSource = Settings.Default.Database, //Password = Settings.Default.DatabasePwd, DateTimeKind = DateTimeKind.Utc, ForeignKeys = true }; var sqlCon = new SQLiteConnection(sqlConStr.ToString()); await sqlCon.OpenAsync().ContinueWith(t => { if (!t.IsCompleted) { return; } var sqlCmd = new SQLiteCommand("SELECT * FROM [AppSeriesTable] ORDER BY AppSeriesID ASC", sqlCon); using (var sqlReader = sqlCmd.ExecuteReader()) { AppSeries.BeginBulkOperation(); while (sqlReader.Read()) { AppSeries.Add(new AppSeries { AppSeriesID = sqlReader.GetInt32(0), AppSeriesName = sqlReader.GetString(1), AppSeriesFriendlyDescription = sqlReader.GetString(2) }); } AppSeries.EndBulkOperation(); } sqlCmd = new SQLiteCommand("SELECT * FROM [AppBranchesTable] ORDER BY AppBranchID ASC", sqlCon); using (var sqlReader = sqlCmd.ExecuteReader()) { IAppSeries series = null; AppBranches.BeginBulkOperation(); while (sqlReader.Read()) { var seriesId = sqlReader.GetInt32(0); if (series == null || series.AppSeriesID != seriesId) { series = AppSeries.FirstOrDefault(s => s.AppSeriesID == seriesId); } var branch = new AppBranch { AppSeries = series, AppBranchID = sqlReader.GetInt32(1), AppBranchName = sqlReader.GetString(2), AppBranchFriendlyDescription = sqlReader.GetString(3) }; AppBranches.Add(branch); series?.ChildBranches.Add(branch); } AppBranches.EndBulkOperation(); } sqlCmd = new SQLiteCommand("SELECT * FROM [AppUpdateClientsTable] ORDER BY AppBrancheID ASC", sqlCon); using (var sqlReader = sqlCmd.ExecuteReader()) { IAppBranch branch = null; ClientInfos.BeginBulkOperation(); while (sqlReader.Read()) { var branchId = sqlReader.GetInt32(3); if (branch == null || branch.AppBranchID != branchId) { branch = AppBranches.FirstOrDefault(b => b.AppBranchID == branchId); } IPAddress ipAddress; if (sqlReader.IsDBNull(4) || !IPAddress.TryParse(sqlReader.GetString(4), out ipAddress)) { ipAddress = IPAddress.None; } var client = new ClientInfo { MachineID = sqlReader.GetString(0), ClientName = sqlReader.GetString(1), Company = sqlReader.GetString(2), AppBranch = branch, IPAddress = ipAddress, RsaPrivateKey = sqlReader.GetString(5), Expiration = sqlReader.GetDateTime(6), Serial = sqlReader.GetString(7), SetupLocation = sqlReader.GetString(8) }; ClientInfos.Add(client); branch?.ChildClients.Add(client); } ClientInfos.BeginBulkOperation(); } }).ContinueWith(t => sqlCon.Close()); }
/// <exception cref="ArgumentNullException">The exception that is thrown when the <paramref name="function" /> argument is null.</exception> public static async Task<bool> RemoveReadedFile(string path) { return await Task.Factory.StartNew(async () => { using (var connection = new SQLiteConnection(ConnectionString)) { await connection.OpenAsync(); try { var command = connection.CreateCommand(); command.CommandText = $"DELETE FROM readed_files WHERE path = '{path}';"; await command.ExecuteNonQueryAsync().ConfigureAwait(false); return true; } catch (SQLiteException) { return false; } } }).Result; }
public async Task<int> RemoveUser(string userLogin) { using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); return await conn.ExecuteAsync("delete from [User] where [Login] = @userLogin", new {userLogin}).ConfigureAwait(false); } }
/// <summary> /// Connects to db. /// </summary> /// <param name="dbPath">The db path.</param> /// <returns>Task{IDbConnection}.</returns> /// <exception cref="System.ArgumentNullException">dbPath</exception> public static async Task<SQLiteConnection> ConnectToDb(string dbPath) { if (string.IsNullOrEmpty(dbPath)) { throw new ArgumentNullException("dbPath"); } var connectionstr = new SQLiteConnectionStringBuilder { PageSize = 4096, CacheSize = 4096, SyncMode = SynchronizationModes.Normal, DataSource = dbPath, JournalMode = SQLiteJournalModeEnum.Wal }; var connection = new SQLiteConnection(connectionstr.ConnectionString); await connection.OpenAsync().ConfigureAwait(false); return connection; }
public static async Task<bool> ClearDb() { return await Task.Factory.StartNew(async () => { using (var connection = new SQLiteConnection(ConnectionString)) { await connection.OpenAsync(); try { var command = connection.CreateCommand(); command.CommandText = "DELETE FROM readed_files WHERE id >= 0;"; command.ExecuteNonQuery(); return true; } catch (SQLiteException) { return false; } } }).Result; }
public async Task<DataPage<UserInfo>> GetUsers(int pageNumber, int pageSize) { ValidatePagination(pageNumber, pageSize); using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteScalarAsync<int>("select count(*) from [User]").ConfigureAwait(false); string q = "select [Login] as UserLogin, Display as DisplayName, Created from [User] order by [Login]"; object prms; if (pageSize == -1) prms = null; else { q += " limit @size offset @skip"; prms = new { size = pageSize, skip = (pageNumber - 1) * pageSize }; } return new DataPage<UserInfo>(pageNumber, pageSize, count, (await conn.QueryAsync<UserInfo>(q, prms).ConfigureAwait(false)).ToList()); } }