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();
}
}
public void Dispose()
{
_cleanupTimer?.Dispose();
Commands?.Dispose();
_db?.Close();
_db?.Dispose();
}
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);
}
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);
}
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();
}
}
}
public void _01_CanGetScheme()
{
var connStr = "Data Source=./test_db.db3;Foreign Keys=True;";
var conn = new Microsoft.Data.Sqlite.SqliteConnection(connStr);
conn.Open();
IsNotNull(conn);
var schema = conn.GetSchema();
IsNotNull(schema);
conn.Close();
}
public void Dispose()
{
_logger.LogTrace("Disposing SQLite cache database at {SqliteCacheDbPath}", _config.CachePath);
_cleanupTimer?.Dispose();
Commands?.Dispose();
if (_db is not null)
{
_logger.LogTrace("Closing connection to SQLite database at {SqliteCacheDbPath}", _config.CachePath);
_db.Close();
_db.Dispose();
}
}
/// <summary>
/// 关闭数据库
/// </summary>
public void Close()
{
if (this.IsOpen)
{
dbc.Close();
}
if (dbc != null)
{
dbc.Dispose();
dbc = null;
}
//throw new NotImplementedException();
}
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
{
db?.Dispose();
db?.Close();
_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);
}
}
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();
}
}
public void Dispose()
{
lock (_storageDirectory)
{
_storageDirectory.Remove(_file);
}
_conn.Close();
_conn.Dispose();
try
{
System.IO.File.Delete(_file);
}
catch
{
// file probably in use, try to delete in 5 seconds
var thread = new Thread(() =>
{
Thread.Sleep(5000);
try
{
System.IO.File.Delete(_file);
}
catch
{
}
});
thread.Start();
}
if (Disposing != null)
{
Disposing();
Disposing = null;
}
}
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);
}
public void Close()
{
connection.Close();
}
