/// <summary>
/// Creates a list of phrases and associates them with a specific operation
/// </summary>
/// <param name="phrases">A list of phrases</param>
/// <param name="operation">An Operation</param>
public async Task <int> CreateAsync(List <Phrase> phrases, Operation operation, CancellationToken cts)
{
int result = 0;
using (SQLiteConnection con = new SQLiteConnection(Database.ConnectionString))
{
await con.OpenAsync();
using (SQLiteTransaction trans = con.BeginTransaction())
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
if (await new OperationDataAccess().CreateAsync(operation, con) > 0)
{
cmd.Parameters.Clear();
cmd.CommandText = "SELECT last_insert_rowid()";
int latestId = Convert.ToInt32(await cmd.ExecuteScalarAsync());
if (latestId > 0)
{
cmd.Parameters.Clear();
foreach (Phrase phrase in phrases)
{
if (cts.IsCancellationRequested)
{
con.Cancel();
return(-1);
}
if (this.IsValidPhrase(phrase))
{
cmd.CommandText = $"INSERT OR IGNORE INTO Phrase (Text, GematriaValue, OrdinalValue, ReducedValue, SumerianValue, PrimesValue, SquaredValue, MisparGadolValue, MisparShemiValue, Alphabet, OperationId) VALUES (@Text, @GematriaValue, @OrdinalValue, @ReducedValue, @SumerianValue, @PrimesValue, @SquaredValue, @MisparGadolValue, @MisparShemiValue, @Alphabet, @OperationId)";
cmd.Parameters.AddWithValue("@Text", phrase.NormalizedText);
cmd.Parameters.AddWithValue("@GematriaValue", phrase.Values[CalculationMethod.Gematria]);
cmd.Parameters.AddWithValue("@OrdinalValue", phrase.Values[CalculationMethod.Ordinal]);
cmd.Parameters.AddWithValue("@ReducedValue", phrase.Values[CalculationMethod.Reduced]);
cmd.Parameters.AddWithValue("@SumerianValue", phrase.Values[CalculationMethod.Sumerian]);
cmd.Parameters.AddWithValue("@PrimesValue", phrase.Values[CalculationMethod.Primes]);
cmd.Parameters.AddWithValue("@SquaredValue", phrase.Values[CalculationMethod.Squared]);
cmd.Parameters.AddWithValue("@MisparGadolValue", phrase.Values[CalculationMethod.MisparGadol]);
cmd.Parameters.AddWithValue("@MisparShemiValue", phrase.Values[CalculationMethod.MisparShemi]);
cmd.Parameters.AddWithValue("@Alphabet", (int)phrase.Alphabet);
cmd.Parameters.AddWithValue("@OperationId", latestId);
result += await cmd.ExecuteNonQueryAsync();
}
}
}
}
}
trans.Commit();
}
}
return(result);
}
public ActionResult AddUser(string dbName, string tabName, string name, string pass)
{
try {
string path = Server.MapPath($"~/bin/{dbName}");
SQLiteConnection dbConnection = OpenConnection(path);
ExecuteQueryNoResult($"insert into {tabName} (name, pass, someval) values('{name}', '{pass}', '0')", dbConnection);
dbConnection.Cancel();
return(Content("OMG, new person in your fantastic app!"));
}
catch {
return(Content("Oh no! Something went wrong."));
}
}
public ActionResult CreateTable(string dbName, string tabName)
{
try {
string path = Server.MapPath($"~/bin/{dbName}");
SQLiteConnection dbConnection = OpenConnection(path);
ExecuteQueryNoResult($"CREATE TABLE {tabName} (name VARCHAR(50), pass VARCHAR(50), someval VARCHAR(500))", dbConnection);
dbConnection.Cancel();
return(Content("Table created. Look at you, you're so freaking amazing."));
}
catch {
return(Content("Oh no! Something went wrong."));
}
}
public ActionResult UpdatePoints(string dbName, string tabName, string name, string someval)
{
try {
string path = Server.MapPath($"~/bin/{dbName}");
SQLiteConnection dbConnection = OpenConnection(path);
ExecuteQueryNoResult($"UPDATE {tabName} SET someval = '{someval}' WHERE name = '{name}' ; ", dbConnection);
dbConnection.Cancel();
return(Content("Tasty, new value arrived!"));
}
catch {
return(Content("Oh no! Something went wrong."));
}
}
protected static void CloseConnection(SQLiteConnection conn)
{
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Cancel();
conn.Close();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public ActionResult GetTableJson(string dbName, string tabName)
{
try {
string path = Server.MapPath($"~/bin/{dbName}");
SQLiteConnection dbConnection = OpenConnection(path);
List <Dictionary <string, object> > data = new List <Dictionary <string, object> >();
data = ExecuteQueryAndDisplay($"select * from {tabName}", dbConnection);
dbConnection.Cancel();
return(Json(data, JsonRequestBehavior.AllowGet));
}
catch {
return(Content("Oh no! Something went wrong."));
}
}
/// <summary>
/// Creates multiple phrases
/// </summary>
/// <param name="phrases">A list of phrases</param>
public async Task <int> CreateAsync(List <Phrase> phrases, CancellationToken cts)
{
int result = 0;
using (SQLiteConnection con = new SQLiteConnection(Database.ConnectionString))
{
await con.OpenAsync();
using (SQLiteTransaction trans = con.BeginTransaction())
{
string query = $"INSERT OR IGNORE INTO Phrase (Text, GematriaValue, OrdinalValue, ReducedValue, SumerianValue, PrimesValue, SquaredValue, MisparGadolValue, MisparShemiValue, Alphabet, OperationId) VALUES (@Text, @GematriaValue, @OrdinalValue, @ReducedValue, @SumerianValue, @PrimesValue, @SquaredValue, @MisparGadolValue, @MisparShemiValue, @Alphabet, @OperationId)";
using (SQLiteCommand cmd = new SQLiteCommand(query, con))
{
foreach (Phrase phrase in phrases)
{
if (cts.IsCancellationRequested)
{
con.Cancel();
return(-1);
}
if (this.IsValidPhrase(phrase))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Text", phrase.NormalizedText);
cmd.Parameters.AddWithValue("@GematriaValue", phrase.Values[CalculationMethod.Gematria]);
cmd.Parameters.AddWithValue("@OrdinalValue", phrase.Values[CalculationMethod.Ordinal]);
cmd.Parameters.AddWithValue("@ReducedValue", phrase.Values[CalculationMethod.Reduced]);
cmd.Parameters.AddWithValue("@SumerianValue", phrase.Values[CalculationMethod.Sumerian]);
cmd.Parameters.AddWithValue("@PrimesValue", phrase.Values[CalculationMethod.Primes]);
cmd.Parameters.AddWithValue("@SquaredValue", phrase.Values[CalculationMethod.Squared]);
cmd.Parameters.AddWithValue("@MisparGadolValue", phrase.Values[CalculationMethod.MisparGadol]);
cmd.Parameters.AddWithValue("@MisparShemiValue", phrase.Values[CalculationMethod.MisparShemi]);
cmd.Parameters.AddWithValue("@Alphabet", (int)phrase.Alphabet);
cmd.Parameters.AddWithValue("@OperationId", phrase.OperationId == 0 ? (object)DBNull.Value : phrase.OperationId);
result += await cmd.ExecuteNonQueryAsync();
}
}
}
trans.Commit();
}
}
return(result);
}
/// <summary>
/// Closes connection with the option to cancel the latest operation
/// </summary>
/// <param name="cancelLastOp">Abort the last database operation if true</param>
public static void CloseConn(bool cancelLastOp = false)
{
try //Catch all for System errors
{
//Check to see if we want to cancel and also connection is valid
if (cancelLastOp && (dbConnection.State != System.Data.ConnectionState.Closed))
{
//Abort pending database changes
dbConnection.Cancel();
}
//Close the connection
dbConnection.Close();
dbConnection.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "SQLite DB Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
static void Main(string[] args)
{
//### 1. Get SqLite from Nuget
//### 2. For the first time create database
//CreateDb("Authors");
//### 3. Open Connection
SQLiteConnection dbConnection = OpenConnection();
//### 4. Create table with a simple query execution
//ExecuteQueryNoResult("CREATE TABLE books (id INT, name VARCHAR(20))", dbConnection);
//### 5. Execute and get rows number
//ExecuteQueryNoResult("insert into books (id, name) values(1, 'Snow White')", dbConnection);
//### 6. Execute and get reader to select data
ExecuteQueryAndDisplay("select * from books", dbConnection);
dbConnection.Cancel();
Console.ReadKey();
}
