public static void Post(string channel, string title, string content)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO @channel VALUES (@title, @content);";
insertCommand.Parameters.AddWithValue("@channel", channel);
insertCommand.Parameters.AddWithValue("@title", title);
insertCommand.Parameters.AddWithValue("@content", content);
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.CommandText.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[0].Value.ToString());
// System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[1].Value.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[2].Value.ToString());
title = title.Trim();
content = content.Trim();
String insertCommand2 = "INSERT INTO " + channel + " VALUES ('" + title + "', '" + content + "');";
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(insertCommand2, db);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand2);
selection.ExecuteReader();
//insertCommand.ExecuteReader();
db.Close();
}
}
private static List <Domain.Objects.Scrobble> GetScrobblesFromOldTrack(string md5, int trackId)
{
List <Domain.Objects.Scrobble> scrobbles = new List <Domain.Objects.Scrobble>();
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
using (var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM Scrobble WHERE MD5=@MD5", conn))
{
cmd.Parameters.AddWithValue("MD5", md5);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DateTime playedOn = Convert.ToDateTime(reader["PlayedOn"]);
scrobbles.Add(new Domain.Objects.Scrobble()
{
On = playedOn,
TrackId = trackId,
PlayedToEnd = true // old scrobbles were only made when played to end
});
}
}
}
}
return(scrobbles);
}
public async static void ResetChannels()
{
await Windows.Storage.ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", Windows.Storage.CreationCollisionOption.OpenIfExists);
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
String tableCommand = "DROP TABLE IF EXISTS channels";
Microsoft.Data.Sqlite.SqliteCommand dropTable = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand, db);
dropTable.ExecuteReader();
String tableCommand2 = "CREATE TABLE IF NOT " +
"EXISTS channels (name TEXT UNIQUE)";
//System.Diagnostics.Debug.WriteLine(tableCommand);
Microsoft.Data.Sqlite.SqliteCommand createTable2 = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand2, db);
createTable2.ExecuteReader();
}
AddChannel("General");
}
private void CreateForSet(DbCommand cmd, string key, byte[] value, DistributedCacheEntryOptions options)
{
cmd.Parameters.AddWithValue("@key", key);
cmd.Parameters.AddWithValue("@value", value);
AddExpirationParameters(cmd, options);
}
private void CreateForSet(DbCommand cmd, string key, byte[] value, DistributedCacheEntryOptions options)
{
cmd.Parameters.AddWithValue("@key", key);
cmd.Parameters.AddWithValue("@value", value);
DateTimeOffset?expiry = null;
TimeSpan? renewal = null;
if (options.AbsoluteExpiration.HasValue)
{
expiry = options.AbsoluteExpiration.Value;
}
else if (options.AbsoluteExpirationRelativeToNow.HasValue)
{
expiry = DateTimeOffset.UtcNow
.Add(options.AbsoluteExpirationRelativeToNow.Value);
}
if (options.SlidingExpiration.HasValue)
{
renewal = options.SlidingExpiration.Value;
expiry = (expiry ?? DateTimeOffset.UtcNow) + renewal;
}
cmd.Parameters.AddWithValue("@expiry", (object)expiry?.Ticks ?? DBNull.Value);
cmd.Parameters.AddWithValue("@renewal", (object)renewal?.Ticks ?? DBNull.Value);
}
public async Task WriteMetadataAsync(IDictionary <string, string> metadata)
{
try
{
using (var command = new SQLiteCommand("create table if not exists metadata (name string, value string)", connection))
{
await command.ExecuteNonQueryAsync();
}
using (var command = new SQLiteCommand("insert or replace into metadata (name, value) values (@n, @v)", connection))
{
foreach (var keyValue in metadata)
{
command.Parameters.AddWithValue("@n", keyValue.Key);
command.Parameters.AddWithValue("@v", keyValue.Value);
await command.ExecuteNonQueryAsync();
}
}
}
catch (Exception ex)
{
Debug.WriteLine("MBTileData: " + ex.Message);
}
}
public override void DisposeCommand(IDbCommand command)
{
SQLiteCommand cmd = (SQLiteCommand)command;
cmd.Parameters.Clear();
base.DisposeCommand(command);
}
public static List <String> GetChannels()
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT name from channels", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
/*foreach(var entry in entries)
* {
* System.Diagnostics.Debug.WriteLine("------------------------------------"+entry);
* }
* //System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + entries[2]);*/
db.Close();
}
return(entries);
}
/// <summary>
/// execute as an asynchronous operation.
/// </summary>
/// <param name="executionToken">The execution token.</param>
/// <param name="implementation">The implementation.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <param name="state">The state.</param>
/// <returns>Task.</returns>
/// <exception cref="ArgumentNullException">
/// executionToken;executionToken is null.
/// or
/// implementation;implementation is null.
/// </exception>
protected override async Task <int?> ExecuteAsync(CommandExecutionToken <SQLiteCommand, SQLiteParameter> executionToken, CommandImplementationAsync <SQLiteCommand> implementation, CancellationToken cancellationToken, object state)
{
if (executionToken == null)
{
throw new ArgumentNullException("executionToken", "executionToken is null.");
}
if (implementation == null)
{
throw new ArgumentNullException("implementation", "implementation is null.");
}
var mode = DisableLocks ? LockType.None : (executionToken as SQLiteCommandExecutionToken)?.LockType ?? LockType.Write;
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
try
{
using (var cmd = new SQLiteCommand())
{
cmd.Connection = m_Connection;
cmd.Transaction = m_Transaction;
if (DefaultCommandTimeout.HasValue)
{
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
}
cmd.CommandText = executionToken.CommandText;
cmd.CommandType = executionToken.CommandType;
foreach (var param in executionToken.Parameters)
{
cmd.Parameters.Add(param);
}
executionToken.ApplyCommandOverrides(cmd);
var rows = await implementation(cmd).ConfigureAwait(false);
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(executionToken, startTime, DateTimeOffset.Now, rows, state);
return(rows);
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert SQLiteException into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
}
public static List <post> GetPosts(string channel)
{
List <post> posts = new List <post>();
List <string> contents = new List <string>();
List <string> titles = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT title from General", db);
//selectCommand.Parameters.AddWithValue("@channel", channel);
String selCommand = "SELECT title from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(selCommand, db);
System.Diagnostics.Debug.WriteLine("---------------------" + channel);
Microsoft.Data.Sqlite.SqliteDataReader query = selection.ExecuteReader();
while (query.Read())
{
titles.Add(query.GetString(0));
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
System.Diagnostics.Debug.WriteLine("========================" + titles[0]);
Microsoft.Data.Sqlite.SqliteCommand selectCommand2 = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT content from General", db);
//selectCommand2.Parameters.AddWithValue("@channel", channel);
String selCommand2 = "SELECT content from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection2 = new Microsoft.Data.Sqlite.SqliteCommand(selCommand2, db);
Microsoft.Data.Sqlite.SqliteDataReader query2 = selection2.ExecuteReader();
while (query2.Read())
{
contents.Add(query2.GetString(0));
}
db.Close();
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
int k = 0;
for (int i = 0; i < titles.Count(); i++)
{
post p = new post();
p.content = contents[i];
p.title = titles[i];
posts.Add(p);
}
return(posts);
}
/// <summary>
/// 执行标准的SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int Exec(string sql)
{
//throw new NotImplementedException();
int res = -1;
using (Microsoft.Data.Sqlite.SqliteCommand sqlCommand = new Microsoft.Data.Sqlite.SqliteCommand(sql, dbc)) {
res = sqlCommand.ExecuteNonQuery();
}
return(res);
}
///////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Constructs a data adapter with the specified select command text,
/// and using the specified database connection string.
/// </summary>
/// <param name="commandText">
/// The select command text to use to construct a select command.
/// </param>
/// <param name="connectionString">
/// A connection string suitable for passing to a new SQLiteConnection,
/// which is associated with the select command.
/// </param>
/// <param name="parseViaFramework">
/// Non-zero to parse the connection string using the built-in (i.e.
/// framework provided) parser when opening the connection.
/// </param>
public SqliteDataAdapter(
string commandText,
string connectionString,
bool parseViaFramework
)
{
SQLiteConnection cnn = new SQLiteConnection(connectionString);
SelectCommand = new SQLiteCommand(commandText, cnn);
}
private void Connect()
{
if (_db == null)
{
var connectionString = _config.ConnectionString;
_logger.LogTrace("Opening connection to SQLite database: " +
"{ConnectionString}", connectionString);
// First try to open an existing database
if (!_config.MemoryOnly && System.IO.File.Exists(_config.CachePath))
{
_logger.LogTrace("Found existing database at {CachePath}", _config.CachePath);
var db = new DbConnection(_config.ConnectionString);
db.Open();
if (CheckExistingDb(db))
{
// Everything checks out, we can use this as our cache db
_db = db;
}
else
{
if (db is not null)
{
_logger.LogTrace("Closing connection to SQLite database at {SqliteCacheDbPath}", _config.CachePath);
db.Close();
db.Dispose();
}
_logger.LogInformation("Deleting existing incompatible cache db file {CachePath}", _config.CachePath);
System.IO.File.Delete(_config.CachePath);
}
}
if (_db == null)
{
_db = new DbConnection(_config.ConnectionString);
_db.Open();
Initialize();
}
Commands = new DbCommandPool(_db, _logger);
// Explicitly set default journal mode and fsync behavior
using (var cmd = new DbCommand("PRAGMA journal_mode = WAL;", _db))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new DbCommand("PRAGMA synchronous = NORMAL;", _db))
{
cmd.ExecuteNonQuery();
}
}
}
private static List <int> ReadResearcherIDs(Microsoft.Data.Sqlite.SqliteCommand command)
{
List <int> Ids = new List <int>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Ids.Add(reader.GetInt32(0));
}
}
return(Ids);
}
private static void MigrateOldMusicCache()
{
// configure to use sqlite
Dapper.SimpleCRUD.SetDialect(Dapper.SimpleCRUD.Dialect.SQLite);
using (DAL.DALManager mgr = new DAL.DALManager(newDatabasePath))
{
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM MusicCache", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string filename = reader["Filename"] + "";
string md5 = reader["MD5"] + "";
int played = Convert.ToInt32(reader["Played"]);
int playedToEnd = Convert.ToInt32(reader["PlayedToEnd"]);
DateTime lastPlayed = reader["LastPlayed"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["LastPlayed"]);
LikeStatus likeStatus = (LikeStatus)Convert.ToInt32(reader["LikeStatus"]);
if (filename.StartsWith("/media/hdd/Dwight/Music/"))
{
filename = filename.Substring("/media/hdd/Dwight/Music/".Length);
}
var track = mgr.GetTrackByFilename(filename);
if (track != null)
{
track.NrPlayed = played;
track.NrPlayedToEnd = playedToEnd;
track.LastPlayed = lastPlayed;
track.LikeStatus = likeStatus;
mgr.Set(track);
var oldScrobbles = GetScrobblesFromOldTrack(md5, track.Id);
mgr.SetAll(oldScrobbles);
}
else
{
Console.Error.WriteLine("Track with relative path " + filename + " not found");
}
}
}
}
}
protected override int?Execute(CommandExecutionToken <SQLiteCommand, SQLiteParameter> executionToken, CommandImplementation <SQLiteCommand> implementation, object state)
{
if (executionToken == null)
{
throw new ArgumentNullException("executionToken", "executionToken is null.");
}
if (implementation == null)
{
throw new ArgumentNullException("implementation", "implementation is null.");
}
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
try
{
using (var cmd = new SQLiteCommand())
{
cmd.Connection = m_Connection;
cmd.Transaction = m_Transaction;
if (DefaultCommandTimeout.HasValue)
{
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
}
cmd.CommandText = executionToken.CommandText;
cmd.CommandType = executionToken.CommandType;
foreach (var param in executionToken.Parameters)
{
cmd.Parameters.Add(param);
}
executionToken.ApplyCommandOverrides(cmd);
var rows = implementation(cmd);
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(executionToken, startTime, DateTimeOffset.Now, rows, state);
return(rows);
}
}
catch (Exception ex)
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
private void CreateBulkInsert(DbCommand cmd, IEnumerable <KeyValuePair <string, byte[]> > keyValues, DistributedCacheEntryOptions options)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(DbCommands.Commands[(int)Operation.BulkInsert]);
int i = 0;
foreach (var pair in keyValues)
{
sb.Append($"(@key{i}, @value{i}, @expiry, @renewal),");
cmd.Parameters.AddWithValue($"@key{i}", pair.Key);
cmd.Parameters.AddWithValue($"@value{i}", pair.Value);
i++;
}
sb.Remove(sb.Length - 1, 1);
sb.Append(";");
AddExpirationParameters(cmd, options);
cmd.CommandText = sb.ToString();
}
private List <ColumnMetadata <DbType> > GetColumns(SQLiteObjectName tableName, bool isTable)
{
/* NOTE: Should be safe since GetTableOrViewInternal returns null after querying the table name with a
** prepared statement, thus proving that the table name exists.
*/
var hasPrimarykey = false;
var columnSql = $"PRAGMA table_info('{tableName.Name}')";
var columns = new List <ColumnMetadata <DbType> >();
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(columnSql, con))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetString(reader.GetOrdinal("name"));
var typeName = reader.GetString(reader.GetOrdinal("type"));
var isPrimaryKey = reader.GetInt32(reader.GetOrdinal("pk")) != 0 ? true : false;
var isnNullable = !reader.GetBoolean(reader.GetOrdinal("notnull"));
hasPrimarykey = hasPrimarykey || isPrimaryKey;
columns.Add(new ColumnMetadata <DbType>(name, false, isPrimaryKey, false, typeName, null, "[" + name + "]", isnNullable, null, null, null, null));
}
}
}
}
//Tables wihtout a primary key always have a ROWID.
//We can't tell if other tables have one or not.
if (isTable && !hasPrimarykey)
{
columns.Add(new ColumnMetadata <DbType>("ROWID", true, false, true, "INTEGER", null, "[ROWID]", false, null, null, null, null));
}
return(columns);
}
public async Task <byte[]> ReadImageBufferAsync(int x, int y, int zoomLevel)
{
byte[] imageBuffer = null;
try
{
using (var command = new SQLiteCommand("select tile_data from tiles where zoom_level=@z and tile_column=@x and tile_row=@y", connection))
{
command.Parameters.AddWithValue("@z", zoomLevel);
command.Parameters.AddWithValue("@x", x);
command.Parameters.AddWithValue("@y", (1 << zoomLevel) - y - 1);
imageBuffer = await command.ExecuteScalarAsync() as byte[];
}
}
catch (Exception ex)
{
Debug.WriteLine("MBTileData: {0}/{1}/{2}: {3}", zoomLevel, x, y, ex.Message);
}
return(imageBuffer);
}
public static void AddChannel(string name)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO channels VALUES (@name);";
insertCommand.Parameters.AddWithValue("@name", name);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.CommandText.ToString());
try
{
insertCommand.ExecuteReader();
}
catch (Exception)
{
ContentDialog dialog = new ContentDialog();
dialog.Title = "That Channel Already Exists";
dialog.IsSecondaryButtonEnabled = false;
dialog.PrimaryButtonText = "Continue";
//dialog.SecondaryButtonText = "Cancel";
}
string trimmed = String.Concat(name.Where(c => !Char.IsWhiteSpace(c)));
String tableCommand = "CREATE TABLE IF NOT " +
"EXISTS " + trimmed + " (title TEXT, content TEXT)";
//System.Diagnostics.Debug.WriteLine(tableCommand);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + tableCommand);
Microsoft.Data.Sqlite.SqliteCommand createTable = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand, db);
createTable.ExecuteReader();
}
}
public static void AddUser(string name, string pass)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO users VALUES (@user, @pass);";
insertCommand.Parameters.AddWithValue("@user", name);
insertCommand.Parameters.AddWithValue("@pass", pass);
insertCommand.ExecuteReader();
db.Close();
}
}
/// <summary>
/// 执行标准的SQL语句并返回单行数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public Row GetRow(string sql)
{
//throw new NotImplementedException();
Row row = new Row();
using (Microsoft.Data.Sqlite.SqliteCommand sqlCommand = new Microsoft.Data.Sqlite.SqliteCommand(sql, dbc)) {
using (Microsoft.Data.Sqlite.SqliteDataReader reader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//item.Set(SqlReader.GetName(i), SqlReader[i].ToString());
string szName = reader.GetName(i);
row[szName] = reader[i].ToString();
}
}
reader.Close();
}
}
return(row);
}
// Some day, Microsoft will deign it useful to add async service initializers and we can
// bring this code back to the light of day.
#if false
private async Task <bool> CheckExistingDbAsync(DbConnection db, CancellationToken cancel)
{
try
{
// Check for correct structure
using (var cmd = new DbCommand(@"SELECT COUNT(*) from sqlite_master", db))
{
var result = (long)await cmd.ExecuteScalarAsync(cancel);
// We are expecting two tables and one additional index
if (result != 3)
{
_logger.LogWarning("Incorrect/incompatible existing cache db structure found!");
return(false);
}
}
// Check for correct version
using (var cmd = new DbCommand(@"SELECT value FROM meta WHERE key = ""version""", db))
{
var result = (long)await cmd.ExecuteScalarAsync(cancel);
if (result != SchemaVersion)
{
_logger.LogWarning("Existing cache db has unsupported schema version {SchemaVersion}",
result);
return(false);
}
}
}
catch (Exception ex)
{
_logger.LogError(ex, "Error while checking compatibilty of existing cache db!");
return(false);
}
return(true);
}
public async Task <IDictionary <string, string> > ReadMetadataAsync()
{
var metadata = new Dictionary <string, string>();
try
{
using (var command = new SQLiteCommand("select * from metadata", connection))
{
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
metadata[(string)reader["name"]] = (string)reader["value"];
}
}
}
catch (Exception ex)
{
Debug.WriteLine("MBTileData: " + ex.Message);
}
return(metadata);
}
private TableOrViewMetadata <SQLiteObjectName, DbType> GetTableOrViewInternal(SQLiteObjectName tableName)
{
const string tableSql =
@"SELECT
type AS ObjectType,
tbl_name AS ObjectName
FROM sqlite_master
WHERE UPPER(tbl_name) = UPPER(@Name) AND
(type='table' OR type='view')";
string actualName;
bool isTable;
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(tableSql, con))
{
cmd.Parameters.AddWithValue("@Name", tableName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find table or view {tableName}");
}
actualName = reader.GetString(reader.GetOrdinal("ObjectName"));
var objectType = reader.GetString(reader.GetOrdinal("ObjectType"));
isTable = objectType.Equals("table");
}
}
}
var columns = GetColumns(tableName, isTable);
return(new TableOrViewMetadata <SQLiteObjectName, DbType>(actualName, isTable, columns));
}
public async Task WriteImageBufferAsync(int x, int y, int zoomLevel, byte[] imageBuffer)
{
try
{
using (var command = new SQLiteCommand("create table if not exists tiles (zoom_level integer, tile_column integer, tile_row integer, tile_data blob)", connection))
{
await command.ExecuteNonQueryAsync();
}
using (var command = new SQLiteCommand("insert or replace into tiles (zoom_level, tile_column, tile_row, tile_data) values (@z, @x, @y, @b)", connection))
{
command.Parameters.AddWithValue("@z", zoomLevel);
command.Parameters.AddWithValue("@x", x);
command.Parameters.AddWithValue("@y", (1 << zoomLevel) - y - 1);
command.Parameters.AddWithValue("@b", imageBuffer);
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Debug.WriteLine("MBTileData: {0}/{1}/{2}: {3}", zoomLevel, x, y, ex.Message);
}
}
/// <summary>
/// Preloads metadata for all database tables.
/// </summary>
/// <remarks>This is normally used only for testing. By default, metadata is loaded as needed.</remarks>
public void PreloadTables()
{
const string tableSql =
@"SELECT
tbl_name as TableName
FROM sqlite_master
WHERE type = 'table'";
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(tableSql, con))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var tableName = reader.GetString(reader.GetOrdinal("TableName"));
GetTableOrView(tableName);
}
}
}
}
}
public static List <String> TestGet(string name)
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT password from users WHERE username = '******'", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
db.Close();
}
return(entries);
}
private void AddExpirationParameters(DbCommand cmd, DistributedCacheEntryOptions options)
{
DateTimeOffset?expiry = null;
TimeSpan? renewal = null;
if (options.AbsoluteExpiration.HasValue)
{
expiry = options.AbsoluteExpiration.Value.ToUniversalTime();
}
else if (options.AbsoluteExpirationRelativeToNow.HasValue)
{
expiry = DateTimeOffset.UtcNow
.Add(options.AbsoluteExpirationRelativeToNow.Value);
}
if (options.SlidingExpiration.HasValue)
{
renewal = options.SlidingExpiration.Value;
expiry = (expiry ?? DateTimeOffset.UtcNow) + renewal;
}
cmd.Parameters.AddWithValue("@expiry", expiry?.Ticks ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("@renewal", renewal?.Ticks ?? (object)DBNull.Value);
}
private async Task InitializeAsync(CancellationToken cancel)
{
_logger.LogInformation("Initializing db cache: {ConnectionString}",
_config.ConnectionString);
using (var transaction = _db.BeginTransaction())
{
using (var cmd = new DbCommand(Resources.TableInitCommand, _db))
{
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync(cancel);
}
using (var cmd = new DbCommand(
$"INSERT INTO meta (key, value) " +
$"VALUES " +
$@"(""version"", {SchemaVersion}), " +
$@"(""created"", {DateTimeOffset.UtcNow.Ticks})", _db))
{
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync(cancel);
}
transaction.Commit();
}
}
public static void AssemblyInit(TestContext context)
{
DataSource.GlobalExecutionCanceled += DefaultDispatcher_ExecutionCanceled;
DataSource.GlobalExecutionError += DefaultDispatcher_ExecutionError;
DataSource.GlobalExecutionFinished += DefaultDispatcher_ExecutionFinished;
DataSource.GlobalExecutionStarted += DefaultDispatcher_ExecutionStarted;
#if !Roslyn_Missing
CompiledMaterializers.MaterializerCompiled += CompiledMaterializers_MaterializerCompiled;
CompiledMaterializers.MaterializerCompilerFailed += CompiledMaterializers_MaterializerCompiled;
#endif
File.Delete(databaseFileName);
#if SDS
SQLiteConnection.CreateFile(databaseFileName);
#endif
var dbConnection = new SQLiteConnection("Data Source=SQLiteTestDatabase.sqlite;");
using (dbConnection)
{
dbConnection.Open();
string sql = @"
CREATE TABLE Employee
(
EmployeeKey INTEGER PRIMARY KEY,
FirstName nvarChar(25) NOT NULL,
MiddleName nvarChar(25) NULL,
LastName nVarChar(25) NOT NULL,
Title nVarChar(100) null,
ManagerKey INT NULL REferences Employee(EmployeeKey),
CreatedDate DateTime NOT NULL DEFAULT CURRENT_TIME,
UpdatedDate DateTime NULL
)";
string sql2 = @"CREATE TABLE Customer
(
CustomerKey INTEGER PRIMARY KEY,
FullName NVARCHAR(100) NULL,
State Char(2) NOT NULL,
CreatedByKey INTEGER NULL,
UpdatedByKey INTEGER NULL,
CreatedDate DATETIME2 NULL,
UpdatedDate DATETIME2 NULL,
DeletedFlag BIT NOT NULL Default 0,
DeletedDate DateTimeOffset NULL,
DeletedByKey INTEGER NULL
)";
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
command.ExecuteNonQuery();
using (SQLiteCommand command = new SQLiteCommand(sql2, dbConnection))
command.ExecuteNonQuery();
sql = @"INSERT INTO Employee ([EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey]) VALUES (@EmployeeKey, @FirstName, @MiddleName, @LastName, @Title, @ManagerKey); SELECT [EmployeeKey], [FirstName], [MiddleName], [LastName], [Title], [ManagerKey] FROM Employee WHERE ROWID = last_insert_rowid();";
for (var i = 0; i < 10; i++)
using (SQLiteCommand command = new SQLiteCommand(sql, dbConnection))
{
command.Parameters.AddWithValue("@EmployeeKey", DBNull.Value);
command.Parameters.AddWithValue("@FirstName", "Tom");
command.Parameters.AddWithValue("@MiddleName", DBNull.Value);
command.Parameters.AddWithValue("@LastName", "Jones");
command.Parameters.AddWithValue("@Title", "CEO");
command.Parameters.AddWithValue("@ManagerKey", DBNull.Value);
var key = command.ExecuteScalar();
}
}
}
protected override int? Execute(CommandExecutionToken<SQLiteCommand, SQLiteParameter> executionToken, CommandImplementation<SQLiteCommand> implementation, object state)
{
if (executionToken == null)
throw new ArgumentNullException("executionToken", "executionToken is null.");
if (implementation == null)
throw new ArgumentNullException("implementation", "implementation is null.");
var mode = DisableLocks ? LockType.None : (executionToken as SQLiteCommandExecutionToken)?.LockType ?? LockType.Write;
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
IDisposable lockToken = null;
try
{
switch (mode)
{
case LockType.Read: lockToken = SyncLock.ReaderLock(); break;
case LockType.Write: lockToken = SyncLock.WriterLock(); break;
}
using (var con = CreateConnection())
{
using (var cmd = new SQLiteCommand())
{
cmd.Connection = con;
if (DefaultCommandTimeout.HasValue)
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
cmd.CommandText = executionToken.CommandText;
//TODO: add potential check for this type.
cmd.CommandType = executionToken.CommandType;
foreach (var param in executionToken.Parameters)
cmd.Parameters.Add(param);
executionToken.ApplyCommandOverrides(cmd);
var rows = implementation(cmd);
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(executionToken, startTime, DateTimeOffset.Now, rows, state);
return rows;
}
}
}
catch (Exception ex)
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
finally
{
if (lockToken != null)
lockToken.Dispose();
}
}
/// <summary>
/// Executes the specified operation asynchronously.
/// </summary>
/// <param name="executionToken"></param>
/// <param name="implementation"></param>
/// <param name="cancellationToken"></param>
/// <param name="state"></param>
/// <returns></returns>
protected override async Task<int?> ExecuteAsync(CommandExecutionToken<SQLiteCommand, SQLiteParameter> executionToken, CommandImplementationAsync<SQLiteCommand> implementation, CancellationToken cancellationToken, object state)
{
if (executionToken == null)
throw new ArgumentNullException("executionToken", "executionToken is null.");
if (implementation == null)
throw new ArgumentNullException("implementation", "implementation is null.");
var mode = DisableLocks ? LockType.None : (executionToken as SQLiteCommandExecutionToken)?.LockType ?? LockType.Write;
var startTime = DateTimeOffset.Now;
OnExecutionStarted(executionToken, startTime, state);
IDisposable lockToken = null;
try
{
switch (mode)
{
case LockType.Read: lockToken = await SyncLock.ReaderLockAsync().ConfigureAwait(false); break;
case LockType.Write: lockToken = await SyncLock.WriterLockAsync().ConfigureAwait(false); break;
}
using (var con = await CreateConnectionAsync(cancellationToken).ConfigureAwait(false))
{
using (var cmd = new SQLiteCommand())
{
cmd.Connection = con;
if (DefaultCommandTimeout.HasValue)
cmd.CommandTimeout = (int)DefaultCommandTimeout.Value.TotalSeconds;
cmd.CommandText = executionToken.CommandText;
cmd.CommandType = executionToken.CommandType;
foreach (var param in executionToken.Parameters)
cmd.Parameters.Add(param);
executionToken.ApplyCommandOverrides(cmd);
var rows = await implementation(cmd).ConfigureAwait(false);
executionToken.RaiseCommandExecuted(cmd, rows);
OnExecutionFinished(executionToken, startTime, DateTimeOffset.Now, rows, state);
return rows;
}
}
}
catch (Exception ex)
{
if (cancellationToken.IsCancellationRequested) //convert SQLiteException into a OperationCanceledException
{
var ex2 = new OperationCanceledException("Operation was canceled.", ex, cancellationToken);
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex2, state);
throw ex2;
}
else
{
OnExecutionError(executionToken, startTime, DateTimeOffset.Now, ex, state);
throw;
}
}
finally
{
if (lockToken != null)
lockToken.Dispose();
}
}
/// <summary>
/// Tests the connection asynchronously.
/// </summary>
/// <returns></returns>
public override async Task TestConnectionAsync()
{
using (var con = await CreateConnectionAsync())
using (var cmd = new SQLiteCommand("SELECT 1", con))
await cmd.ExecuteScalarAsync();
}
/// <summary>
/// Tests the connection.
/// </summary>
public override void TestConnection()
{
using (var con = CreateConnection())
using (var cmd = new SQLiteCommand("SELECT 1", con))
cmd.ExecuteScalar();
}
