/// <returns>Returns null if no matching item is in the cache.</returns>
public async Task<string> Get(string markdown, string context, string mode)
{
if (markdown == null) throw new ArgumentNullException(nameof(markdown));
if (context == null) throw new ArgumentNullException(nameof(context));
if (mode == null) throw new ArgumentNullException(nameof(mode));
await Initialize();
var key = ComputeHash(markdown, context, mode);
using (var conn = await GetOpenConnectionAsync())
{
// After https://github.com/aspnet/Microsoft.Data.Sqlite/pull/172 is published to the
// Microsoft.Data.SQLite package we can update the package and use this code instead:
// var result = await conn.ExecuteScalarAsync<string>(
// $"select Contents from {CacheTableName} where Key = @Key",
// new { Key = key });
// Using a cast since this code should be removable in the future:
var cmd = new SqliteCommand($"select Contents from {CacheTableName} where Key = @Key", (SqliteConnection)conn);
cmd.Parameters.AddWithValue("@Key", key);
var result = (string)(await cmd.ExecuteScalarAsync());
if (!string.IsNullOrEmpty(result))
{
return result;
}
}
return null;
}
public void Parameters_works()
{
var command = new SqliteCommand();
var result = command.Parameters;
Assert.NotNull(result);
Assert.Same(result, command.Parameters);
}
protected override bool CanShowOverlay(string path, FILE_ATTRIBUTE attributes)
{
// Return true if the file is read only, meaning we'll show the overlay.
try
{
string app = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
string dbPath = Path.Combine(app, @"yliyun\db\file_status.db");
using (SqliteConnection conn = new SqliteConnection("Data Source =" + dbPath))
{
string rootDir = @"D:\一粒云盘";
conn.Open();
SqliteCommand cmd1 = new SqliteCommand("SELECT syncRoot FROM yliyun_conf", conn);
SqliteDataReader reader1 = cmd1.ExecuteReader();
if (reader1.Read())
{
rootDir = reader1.GetString(0);
}
if (path.StartsWith(Path.Combine(rootDir, @"个人空间\"))
|| path.StartsWith(Path.Combine(rootDir, @"群组空间\"))
|| path.StartsWith(Path.Combine(rootDir, @"部门空间\"))
|| path.StartsWith(Path.Combine(rootDir, @"共享空间\")))
{
string sql = "SELECT lastModified FROM file_status WHERE filePath = @fp";
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.Parameters.AddRange(new[]
{
new SqliteParameter("@fp", path)
});
SqliteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
return false;
}
else
{
return true;
}
}
else
{
return false;
}
}
}
catch (Exception)
{
return false;
}
}
public async Task Post([FromBody]string value)
{
using (var transaction = ApplicationContext.Connection.BeginTransaction())
{
var command = new SqliteCommand("INSERT INTO word (text) values (@text)", ApplicationContext.Connection, transaction);
command.Parameters.AddWithValue("@text", value);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
}
public async Task<IEnumerable<string>> Get()
{
var command = new SqliteCommand("SELECT text from word", ApplicationContext.Connection);
var reader = command.ExecuteReader();
var data = new List<string>();
while (await reader.ReadAsync())
{
data.Add(reader.GetString(0));
}
return data;
}
public void Ctor_sets_values()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = new SqliteCommand("SELECT 1;", connection, transaction);
Assert.Equal("SELECT 1;", command.CommandText);
Assert.Same(connection, command.Connection);
Assert.Same(transaction, command.Transaction);
}
}
}
protected void Execute(Action<SqliteCommand> action)
{
try
{
using (SqliteConnection connection = new SqliteConnection(this.connectionString))
{
connection.Open();
using (SqliteCommand command = new SqliteCommand())
{
command.Connection = connection;
action.Invoke(command);
}
}
}
catch (Exception)
{
throw;
}
}
private static void AttachParameters(SqliteCommand command, DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (DbParameter p in commandParameters)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
private static void PrepareCommand(
SqliteCommand command,
SqliteConnection connection,
SqliteTransaction transaction,
CommandType commandType,
string commandText,
DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
command.CommandType = commandType;
command.CommandText = commandText;
command.Connection = connection;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
if (commandParameters != null) { AttachParameters(command, commandParameters); }
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Filename=TestDatabase.db", options =>
{
options.MigrationsAssembly(Assembly.GetExecutingAssembly().FullName);
});
using (SqliteConnection con = new SqliteConnection("Data Source=TestDatabase.db;"))
using (Microsoft.Data.Sqlite.SqliteCommand command = con.CreateCommand())
{
con.Open();
command.CommandText = "SELECT name FROM sqlite_master WHERE name='Personas'";
var name = command.ExecuteScalar();
if (name != null && name.ToString() == "Personas")
{
return;
}
// acount table not exist, create table and insert
command.CommandText = "CREATE TABLE Personas (Nombre VARCHAR(50),Apellidos VARCHAR(50),DNI VARCHAR(10),Sexo VARCHAR(10), FechaNacimiento TEXT, Direccion VARCHAR(50),Pais VARCHAR(50), CodigoPostal int)";
command.ExecuteNonQuery();
}
base.OnConfiguring(optionsBuilder);
}
public async Task Add(string markdown, string context, string mode, string contents)
{
if (markdown == null) throw new ArgumentNullException(nameof(markdown));
if (context == null) throw new ArgumentNullException(nameof(context));
if (mode == null) throw new ArgumentNullException(nameof(mode));
await Initialize();
var key = ComputeHash(markdown, context, mode);
using (var conn = await GetOpenConnectionAsync())
{
// After https://github.com/aspnet/Microsoft.Data.Sqlite/pull/172 is published to the
// Microsoft.Data.SQLite package we can update the package and use this code instead:
// await conn.ExecuteAsync(
// $"insert or replace into {CacheTableName} (Key, Contents) values (@Key, @Contents)",
// new { Key = key, Contents = contents });
// Using a cast since this code should be removable in the future:
var cmd = new SqliteCommand($"insert or replace into {CacheTableName} (Key, Contents) values (@Key, @Contents)", (SqliteConnection)conn);
cmd.Parameters.AddWithValue("@Key", key);
cmd.Parameters.AddWithValue("@Contents", contents);
await cmd.ExecuteNonQueryAsync();
}
}
public static int ExecuteNonQuery(SqliteConnection connection, string commandText, params DbParameter[] parameters)
{
using (SqliteCommand cmd = new SqliteCommand())
{
cmd.Connection = connection;
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
if (parameters != null)
{
foreach (DbParameter p in parameters)
{
cmd.Parameters.Add(p);
}
}
return cmd.ExecuteNonQuery();
}
}
public void CommandText_defaults_to_empty()
{
var command = new SqliteCommand();
Assert.Empty(command.CommandText);
}
public async Task Delete(int id)
{
using (var transaction = ApplicationContext.Connection.BeginTransaction())
{
var command = new SqliteCommand("DELETE FROM word where id = @id", ApplicationContext.Connection, transaction);
command.Parameters.AddWithValue("@id", id);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
}
public async Task Put(int id, [FromBody]string value)
{
using (var transaction = ApplicationContext.Connection.BeginTransaction())
{
var command = new SqliteCommand("UPDATE word set text= @text where id = @id", ApplicationContext.Connection, transaction);
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@text", value);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
}
public async Task<string> Get(int id)
{
string text = null;
var command = new SqliteCommand("SELECT text from word where id=@id", ApplicationContext.Connection);
command.Parameters.AddWithValue("@id", id);
var reader = command.ExecuteReader();
while (await reader.ReadAsync())
{
text = reader.GetString(0);
}
return text;
}