/// <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(); }