public SqliteGisModelReader(string fileName) { mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"); mDataConnection.Open(); SQLiteCommand getModelCommand = mDataConnection.CreateCommand(); getModelCommand.CommandText = "SELECT CorrectionConstant, CorrectionParameter FROM Model"; IDataReader reader = getModelCommand.ExecuteReader(); while (reader.Read()) { mCorrectionConstant = reader.GetInt32(0); mCorrectionParameter = reader.GetDouble(1); } reader.Close(); List<string> outcomeLabels = new List<string>(); getModelCommand.CommandText = "SELECT OutcomeLabel FROM Outcome ORDER BY OutcomeID"; reader = getModelCommand.ExecuteReader(); while (reader.Read()) { outcomeLabels.Add(reader.GetString(0)); } reader.Close(); mOutcomeLabels =outcomeLabels.ToArray(); mGetParameterCommand = mDataConnection.CreateCommand(); mGetParameterCommand.CommandText = "SELECT PredicateParameter.OutcomeID, PredicateParameter.Parameter FROM PredicateParameter INNER JOIN Predicate ON PredicateParameter.PredicateID = Predicate.PredicateID WHERE Predicate.PredicateLabel = ?"; mPredicateParameter = new SQLiteParameter(); mPredicateParameter.DbType = DbType.String; mPredicateParameter.Size = 255; mGetParameterCommand.Parameters.Add(mPredicateParameter); }
public GamesRepository() { bool buildSchema = false; if (!File.Exists(DatabaseFile)) { Directory.CreateDirectory(DatabasePath); SQLiteConnection.CreateFile(DatabaseFile); buildSchema = true; } DatabaseConnection = new SQLiteConnection(ConString); DatabaseConnection.Open(); using (SQLiteCommand com = DatabaseConnection.CreateCommand()) { com.CommandText = "PRAGMA automatic_index=FALSE; PRAGMA synchronous=OFF; PRAGMA auto_vacuum=INCREMENTAL; PRAGMA foreign_keys=ON; PRAGMA encoding='UTF-8';"; com.ExecuteScalar(); } if (buildSchema) { using (SQLiteCommand com = DatabaseConnection.CreateCommand()) { string md = Resource1.MakeDatabase; com.CommandText = md; com.ExecuteNonQuery(); } } using (SQLiteCommand com = DatabaseConnection.CreateCommand()) { UpdateDatabase.Update(DatabaseConnection); } }
public void TestQuery() { var builder = new SQLiteConnectionStringBuilder(); builder.DataSource = "test.db"; using (DbConnection connection = new SQLiteConnection(builder.ToString())) { connection.Open(); using (var cmd1 = connection.CreateCommand()) { cmd1.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='table_test';"; var reader = cmd1.ExecuteReader(); if (reader.Read()) { var tableName = reader.GetString(0); System.Diagnostics.Trace.WriteLine(String.Format("table name={0}", tableName)); } else { using (var cmd2 = connection.CreateCommand()) { cmd2.CommandText = @"Create Table 'table_test' (num Integer, str)"; cmd2.ExecuteNonQuery(); } } } } }
public void Cleaup() { //drop all the tables from the db using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", DB_FILE_PATH))) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "DROP TABLE records"; cmd.ExecuteNonQuery(); } using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "DROP TABLE datasets"; cmd.ExecuteNonQuery(); } using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = "DROP TABLE kvstore"; cmd.ExecuteNonQuery(); } } }
/// <summary>Write data with a given key to cache</summary> /// <param name="key">Key of data to be stored</param> /// <param name="data">Data to be stored</param> public void Store(string key, string data) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { DateTime now = DateTime.Now; connection.Open(); SQLiteCommand command = connection.CreateCommand(); command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?"; command.Parameters.Add("kw", DbType.String).Value = key; command.ExecuteNonQuery(); // out with the old command = connection.CreateCommand(); command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)"; command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day; command.Parameters.Add("kw", DbType.String).Value = key; command.Parameters.Add("dt", DbType.String).Value = data; command.ExecuteNonQuery(); // in with the new } finally { connection.Close(); } }
public int updateItemTable(string key, string value) { SQLiteCommand sqliteCmd = sqlite.CreateCommand(); sqliteCmd.CommandText = "UPDATE ItemTable SET VALUE =:value WHERE KEY=:key"; SQLiteParameter parameter = new SQLiteParameter(); try { sqlite.Open(); byte[] bytes = System.Text.Encoding.Unicode.GetBytes(value); sqliteCmd.Parameters.Add("value", DbType.Binary).Value = bytes; sqliteCmd.Parameters.Add("key", DbType.String).Value = key; return(sqliteCmd.ExecuteNonQuery()); } catch (Exception ex) { throw new Exception("数据读取报错:" + ex.Message); } finally { if (sqlite != null) { sqliteCmd.Dispose(); sqlite.Close(); } } }
/// <summary> /// コマンドを実行. /// </summary> /// <param name="inputCmd">inputed command</param> /// <param name="conn">SQLite connection</param> public static void executeCommand(String inputCmd, SQLiteConnection conn) { if (inputCmd.ToLower().StartsWith("select")) { try { SQLiteCommand command = conn.CreateCommand(); command = conn.CreateCommand(); command.CommandText = inputCmd; SQLiteDataReader reader = command.ExecuteReader(); if (!reader.HasRows) { Console.WriteLine("no row selected"); } int fc = reader.FieldCount; while (reader.Read()) { String[] row = new String[fc]; for (int i = 0; i < fc; i++) { row[i] = reader.GetValue(i).ToString(); } Console.WriteLine(String.Join("\t", row)); } Console.WriteLine(); Console.WriteLine(reader.StepCount + " row(s) selected"); } catch (SQLiteException e) { Console.WriteLine(e.Message); } } }
public MainWindow() { InitializeComponent(); if (!File.Exists("pix_database.sqlite")) { SQLiteConnection.CreateFile("pix_database.sqlite"); new_db = true; } db = new SQLiteConnection("Data Source=pix_database.sqlite;Version=3;"); db.Open(); SQLiteCommand createTables = db.CreateCommand(); String bookTable = "CREATE TABLE IF NOT EXISTS Book" + "(ISBN TEXT PRIMARY KEY NOT NULL," + "Title TEXT NOT NULL," + "Published DATETIME NOT NULL," + "Author TEXT NOT NULL," + "Pages INT NOT NULL," + "Publisher TEXT NOT NULL);"; String courseTable = "CREATE TABLE IF NOT EXISTS CourseBook" + "(Course TEXT NOT NULL," + "ISBN TEXT NOT NULL," + "PRIMARY KEY (Course, ISBN)," + "FOREIGN KEY (ISBN) REFERENCES Book(ISBN));"; createTables.CommandText = bookTable; createTables.ExecuteNonQuery(); createTables.Parameters.Clear(); createTables.CommandText = courseTable; createTables.ExecuteNonQuery(); createTables.Dispose(); if (new_db) { SQLiteCommand insertInitial = db.CreateCommand(); String book351 = "INSERT INTO Book VALUES ('0-672-33690-1', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');" + "INSERT INTO Book VALUES ('978-0-672-33690-4', 'C# Unleashed', '2013-03-23', 'Bart de Smet', 1700, 'Sams Publishing');"; String course351 = "INSERT INTO CourseBook VALUES ('CSCI351', '978-0-672-33690-4');" + "INSERT INTO CourseBook VALUES ('CSCI351', '0-672-33690-1');"; insertInitial.CommandText = book351; insertInitial.ExecuteNonQuery(); insertInitial.Parameters.Clear(); insertInitial.CommandText = course351; insertInitial.ExecuteNonQuery(); insertInitial.Dispose(); } }
public static void CreateKaraokeDatabase( FileInfo theDatabaseFileInfo) { SQLiteConnection.CreateFile(theDatabaseFileInfo.FullName); using (SQLiteConnection theConnection = new SQLiteConnection( DatabaseLayer.ToConnectionString(theDatabaseFileInfo))) { try { theConnection.Open(); using (SQLiteCommand theCommand = theConnection.CreateCommand()) { StringBuilder theCommandText = new StringBuilder(); theCommandText.Append("CREATE TABLE [Settings] ("); theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,"); theCommandText.Append("[Key] [text],"); theCommandText.Append("[Value] [text])"); theCommand.CommandText = theCommandText.ToString(); theCommand.ExecuteNonQuery(); } using (SQLiteCommand theCommand = theConnection.CreateCommand()) { StringBuilder theCommandText = new StringBuilder(); theCommandText.Append("CREATE TABLE [Tracks] ("); theCommandText.Append("[ID] [integer] PRIMARY KEY AUTOINCREMENT,"); theCommandText.Append("[Path] [text],"); theCommandText.Append("[Details] [text],"); theCommandText.Append("[Extension] [text],"); theCommandText.Append("[Rating] [integer] DEFAULT 0,"); theCommandText.Append("[Checksum] [text],"); theCommandText.Append("[ToBeDeleted] [boolean] DEFAULT 0)"); theCommand.CommandText = theCommandText.ToString(); theCommand.ExecuteNonQuery(); } } finally { theConnection.Close(); } } }
internal static void DeleteHopsIngredient(int hopsIngredientId, SQLiteConnection connection) { using (SQLiteCommand deleteIngredientCommand = connection.CreateCommand()) { deleteIngredientCommand.CommandText = "DELETE FROM HopsIngredients WHERE id = @id"; deleteIngredientCommand.Parameters.AddWithValue("id", hopsIngredientId); deleteIngredientCommand.ExecuteNonQuery(); } using (SQLiteCommand deleteJunctionCommand = connection.CreateCommand()) { deleteJunctionCommand.CommandText = "DELETE FROM HopsInRecipe WHERE hopsIngredient = @id"; deleteJunctionCommand.Parameters.AddWithValue("id", hopsIngredientId); deleteJunctionCommand.ExecuteNonQuery(); } }
public DataManager(string dbFilePath) { if (string.IsNullOrEmpty(dbFilePath) == true) { throw new ArgumentNullException("dbFilePath"); } _fileExists = File.Exists(CommonConst.DBFileName); _dbFilePath = dbFilePath; // DBとの接続を開始する。 _conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName); // DBに接続する。 _conn.Open(); _command = _conn.CreateCommand(); // DBファイルが新規作成された場合 if (_fileExists == false) { // 空のテーブルを作成する。 this.CreateNewTables(); _fileExists = true; } // アプリケーション設定 _applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter(); _applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter(); }
/// <summary> /// Constructs the transaction object, binding it to the supplied connection /// </summary> /// <param name="connection">The connection to open a transaction on</param> /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param> internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock) { _cnn = connection; _version = _cnn._version; _level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable; if (_cnn._transactionLevel++ == 0) { try { using (SQLiteCommand cmd = _cnn.CreateCommand()) { if (!deferredLock) cmd.CommandText = "BEGIN IMMEDIATE"; else cmd.CommandText = "BEGIN"; cmd.ExecuteNonQuery(); } } catch (SQLiteException) { _cnn._transactionLevel--; _cnn = null; throw; } } }
public void EnsureDatabaseFileExistsKeepsData() { string connectionString = "Data Source={0};New=False;UTF8Encoding=True;Version=3"; string path = Path.GetTempFileName(); File.Delete(path); string fullConnectionString = string.Format(connectionString, path); IDataBaseSetupImpl dataBaseSetup = new ProjectsRepository.DataBaseSetup(cfg); dataBaseSetup.EnsureDatabaseFileExists(path); using (SQLiteConnection sc = new SQLiteConnection(fullConnectionString)) { sc.Open(); IDbCommand command = sc.CreateCommand(); command.CommandText = "INSERT INTO Queries (project_id, name,text) VALUES (100,'Test Queries','from Tests As Success')"; command.ExecuteNonQuery(); } //Call it again to see it destroy data. dataBaseSetup.EnsureDatabaseFileExists(path); using (SQLiteConnection sc = new SQLiteConnection(fullConnectionString)) { sc.Open(); IDbCommand command = sc.CreateCommand(); command.CommandText = "SELECT name,text FROM Queries WHERE project_id = 100"; IDataReader reader = command.ExecuteReader(); Assert.IsTrue(reader.Read(), "Data was no preserved when calling EnsureDatabaseFileExists twice"); Assert.AreEqual("Test Queries", reader.GetValue(0), "Bad data when calling ensure db twice"); Assert.AreEqual("from Tests As Success", reader.GetValue(1), "Bad data when calling ensure db twice"); } File.Delete(path); }
public Database() { string _dbPath = "MyDatabase.db3"; // Instanciation de notre connexion SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;"); // Utilisation de l'API en mode synchrone this.conn = sqlite_conn; sqlite_conn.Open(); SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand(); // Let the SQLiteCommand object know our SQL-Query: sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS pass (id INT PRIMARY KEY, login varchar(100), privk varchar(100), pubk varchar(100)) ;"; // Now lets execute the SQL ;-) sqlite_cmd.ExecuteNonQuery(); /* // Utilisation de l'API asynchrone * SQLiteAsyncConnection connAsync = new SQLiteAsyncConnection("myDb.db3"); * connection.CreateTableAsync<Personne>();*/ }
private static void MakeTableForListOfFields([JetBrains.Annotations.NotNull][ItemNotNull] List <FieldDefinition> fields, [JetBrains.Annotations.NotNull] System.Data.SQLite.SQLiteConnection conn, [JetBrains.Annotations.NotNull] string tableName) { if (fields.Count == 0) { throw new LPGException("No fields defined for database"); } string sql = "CREATE TABLE " + tableName + "("; foreach (var field in fields) { sql += field.Name + " " + field.Type + ","; } sql = sql.Substring(0, sql.Length - 1) + ");"; int result; using (var command = conn.CreateCommand()) { command.CommandText = sql; result = command.ExecuteNonQuery(); } if (result != 0) { throw new LPGException("Creating the table " + tableName + " failed."); } }
private bool CreateDatabase() { SQLiteConnection tempConnection = null; try { SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]); tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;"); tempConnection.Open(); SQLiteCommand command = tempConnection.CreateCommand(); command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)"; command.ExecuteNonQuery(); tempConnection.Close(); } catch(Exception e) { errors.Add("Tietokannan luominen epäonnistui"); return false; } finally { if(tempConnection != null) tempConnection.Close(); } return true; }
public void Clear(string name) { int procCount = Process.GetProcessesByName("firefox").Length; if (procCount > 0) throw new ApplicationException(string.Format("There are {0} instances of Firefox still running", procCount)); try { using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName())) { conn.Open(); SQLiteCommand command = conn.CreateCommand(); command.CommandText = "delete from moz_cookies where name='" + name + "'"; int count = command.ExecuteNonQuery(); } } catch (SQLiteException ex) { if ( !(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) || ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked))) throw new ApplicationException("The Firefox cookies.sqlite file is locked"); } }
//[JetBrains.Annotations.NotNull] //private string MainFilename { get; set; } public void MakeTableForListOfFields([JetBrains.Annotations.NotNull][ItemNotNull] List <FieldDefinition> fields, [JetBrains.Annotations.NotNull] HouseholdKey householdKey, [JetBrains.Annotations.NotNull] string tableName) { if (fields.Count == 0) { throw new LPGException("No fields defined for database"); } string dstFileName = GetFilenameForHouseholdKey(householdKey); string sql = "CREATE TABLE " + tableName + "("; foreach (var field in fields) { sql += field.Name + " " + field.Type + ","; } sql = sql.Substring(0, sql.Length - 1) + ");"; using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dstFileName + ";Version=3;") ) { conn.Open(); var command = conn.CreateCommand(); command.CommandText = sql; var result = command.ExecuteNonQuery(); if (result != 0) { throw new LPGException("Creating the table " + tableName + " failed."); } conn.Close(); } }
/// <summary> /// Takes a GIS model and a file and writes the model to that file. /// </summary> /// <param name="model"> /// The GisModel which is to be persisted. /// </param> /// <param name="fileName"> /// The name of the file in which the model is to be persisted. /// </param> public void Persist(GisModel model, string fileName) { Initialize(model); PatternedPredicate[] predicates = GetPredicates(); if ( File.Exists(fileName)) { File.Delete(fileName); } using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3")) { mDataConnection.Open(); mDataCommand = mDataConnection.CreateCommand(); CreateDataStructures(); using (mDataTransaction = mDataConnection.BeginTransaction()) { mDataCommand.Transaction = mDataTransaction; CreateModel(model.CorrectionConstant, model.CorrectionParameter); InsertOutcomes(model.GetOutcomeNames()); InsertPredicates(predicates); InsertPredicateParameters(model.GetOutcomePatterns(), predicates); mDataTransaction.Commit(); } mDataConnection.Close(); } }
private void loadHeroDiary() { // Load the latest Hero Diary entries using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;")) { conn.Open(); using (SQLiteCommand cmd = conn.CreateCommand()) { string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where HeroName=@HeroName order by Diary_ID desc limit 1000"; cmd.CommandText = commandText; cmd.Parameters.AddWithValue("@HeroName", this.HeroName); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); da = new SQLiteDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); BindingSource bindingSource = new BindingSource(); bindingSource.DataSource = ds.Tables[0]; grdDiary.DataSource = bindingSource; grdDiary.AutoGenerateColumns = true; grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders; grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } } }
public static bool Add(dtoNhanVien nvsx) { var cnn = new SQLiteConnection(_connString); cnn.Open(); using (var cmd = cnn.CreateCommand()) { cmd.CommandText = "insert into NhanVien (ID,hoTen,ngaySinh,diaChi,tienLuong,ngheNghiep,thuocTinhKhac) values(@ID,@hoTen,@ngaySinh,@diaChi,@tienLuong,@ngheNghiep,@thuocTinhKhac)"; //Parameters cmd.Parameters.Add(new SQLiteParameter("@ID")); cmd.Parameters.Add(new SQLiteParameter("@hoTen")); cmd.Parameters.Add(new SQLiteParameter("@ngaySinh")); cmd.Parameters.Add(new SQLiteParameter("@diaChi")); cmd.Parameters.Add(new SQLiteParameter("@tienLuong")); cmd.Parameters.Add(new SQLiteParameter("@ngheNghiep")); cmd.Parameters.Add(new SQLiteParameter("@imagePath")); cmd.Parameters.Add(new SQLiteParameter("@thuocTinhKhac")); cmd.Parameters["@ID"].Value = nvsx.Msnv; cmd.Parameters["@hoTen"].Value = nvsx.HoTen; cmd.Parameters["@ngaySinh"].Value = nvsx.NgaySinh; cmd.Parameters["@diaChi"].Value = nvsx.DiaChi; cmd.Parameters["@tienLuong"].Value = nvsx.TienLuong; cmd.Parameters["@ngheNghiep"].Value = nvsx.NgheNghiep; cmd.Parameters["@imagePath"].Value = nvsx.NgheNghiep; cmd.Parameters["@thuocTinhKhac"].Value = nvsx.XmlThuocTinhRieng; //Excute cmd.ExecuteNonQuery(); } return true; }
public static Toimipiste Hae(int toimipisteId) { Toimipiste toimipiste = null; DataSet ds = new DataSet(); SQLiteConnection conn = new SQLiteConnection(Properties.Settings.Default.Database); conn.Open(); SQLiteCommand comm = conn.CreateCommand(); comm.CommandText = "SELECT id, nimi, lahiosoite, postitoimipaikka, postinro, email, puhelinnro FROM toimipiste WHERE id = $id"; comm.Parameters.AddWithValue("$id", toimipisteId); using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(comm.CommandText, conn)) { adapter.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { toimipiste = new Toimipiste(); toimipiste.Id = int.Parse(row["id"].ToString()); toimipiste.Nimi = row["nimi"].ToString(); toimipiste.ParsiOsoite(row); } } conn.Close(); return toimipiste; }
public static dtoNhanVien Search(int msnv) { var cnn = new SQLiteConnection(_connString); cnn.Open(); var cmd = cnn.CreateCommand(); cmd.CommandText = "select * from NhanVien where ID = @ID"; // Parameters cmd.Parameters.Add(new SQLiteParameter("@ID")); cmd.Parameters["@ID"].Value = msnv; var reader = cmd.ExecuteReader(); dtoNhanVien dtoNhanVien = null; if (reader.Read()) { dtoNhanVien = new dtoNhanVien(); dtoNhanVien.Msnv = int.Parse(reader[0].ToString()); dtoNhanVien.HoTen = reader[1].ToString(); dtoNhanVien.NgaySinh = (DateTime)reader[2]; dtoNhanVien.DiaChi = reader[3].ToString(); dtoNhanVien.TienLuong = float.Parse(reader[4].ToString()); dtoNhanVien.NgheNghiep = reader[5].ToString(); //dtoNhanVien.ImagePath = reader[6].ToString(); dtoNhanVien.XmlThuocTinhRieng = reader[6].ToString(); } cnn.Close(); return dtoNhanVien; }
/// <summary> /// delete specific cookie /// </summary> /// <param name="id"></param> public static void DeleteCookie(object id) { if (!string.IsNullOrEmpty(GetFFCookiePath())) { using (var conn = new SQLiteConnection("Data Source=" + GetFFCookiePath())) { using (SQLiteCommand cmd = conn.CreateCommand()) { try { cmd.CommandText = "delete from moz_cookies where id=" + id; conn.Open(); int res = cmd.ExecuteNonQuery(); if (res != 1) { } } finally { cmd.Dispose(); conn.Close(); } } } } }
public int InstallDatabase() { int res = -1; if (!File.Exists(DBParameter.data_source)) { SQLiteConnection.CreateFile(DBParameter.data_source); string connectionString = "Data Source=" + DBParameter.data_source; connection = new SQLiteConnection(connectionString); //Open connection if (this.OpenConnection() == true) { try { using (connection) { //Create Command using (SQLiteCommand comm = connection.CreateCommand()) { comm.CommandText = DellContactsScript.Script; res = comm.ExecuteNonQuery(); } } } catch (Exception ex) { // } //close Connection this.CloseConnection(); } } return res; }
public static void ClearHistory() { if (!string.IsNullOrEmpty(GetFFHistoryPath())) { try { using (var conn = new SQLiteConnection("Data Source=" + GetFFHistoryPath())) { using (SQLiteCommand cmd = conn.CreateCommand()) { try { cmd.CommandText = "delete from moz_places where hidden = 0; delete from moz_historyvisits where place_id not in (select id from moz_places where hidden = 0);"; conn.Open(); int res = cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); conn.Close(); } } } } catch { } } }
public void Execute(Action <SqliteConnection, SqliteCommand, SqliteTransaction> ExcuteQuery) { Lock = true; using (var conn = new SqliteConnection() { ConnectionString = this.ConnectString }) { conn.Open(); using (var cmd = conn.CreateCommand()) { using (var trans = conn.BeginTransaction()) { try { ExcuteQuery(conn, cmd, trans); trans.Commit(); } catch (Exception ex) { trans.Rollback(); } } } conn.Close(); } Lock = false; }
/// <summary> /// Initializes a new instance of the <b>DBCommand</b> class. /// </summary> /// <param name="con"></param> /// <param name="commandType"></param> /// <param name="commandText"></param> private DBCommand( SQLiteConnection con, CommandType commandType, string commandText ) { _con = con; cmd = _con.CreateCommand(); cmd.CommandType = commandType; cmd.CommandText = commandText; }
public static SQLiteConnection CreateConnection() { // check and create database // Tao db neu chua ton tai var dataPath = AppDomain.CurrentDomain.BaseDirectory ; var connString = @"Data Source=" + AppDomain.CurrentDomain.BaseDirectory + DatabaseName; SQLiteConnection cnn; if (!File.Exists(dataPath + DatabaseName)) { // Create database SQLiteConnection.CreateFile(dataPath + DatabaseName); cnn = new SQLiteConnection(connString); cnn.Open(); var cmd = cnn.CreateCommand(); cmd.CommandText = DataCreator; cmd.ExecuteNonQuery(); cnn.Close(); } else { cnn = new SQLiteConnection(connString); } return cnn; }
/// <summary>Initialization ctor</summary> /// <param name="file">Name of database file, will be created if not existing</param> public WebCache(string file = "Cache.dat") { _File = file; if (!System.IO.File.Exists(_File)) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { connection.Open(); string[] inits = new string[]{ "CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)", "CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])" }; SQLiteCommand command = connection.CreateCommand(); foreach (string cmd in inits) { command.CommandText = cmd; command.ExecuteNonQuery(); } } finally { connection.Close(); } } }
public override void update() { //Create a Dictionary to hold the receivers saved in the database Dictionary<string, Receiver> receivers = new Dictionary<string, Receiver>(); //Create a new connection to the database using (SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=database.sqlite;Version=3;")) { //Open database connection m_dbConnection.Open(); using (SQLiteCommand command = m_dbConnection.CreateCommand()) { //Select everything from the 'receivers' table SQLiteCommand getReceivers = new SQLiteCommand("SELECT * FROM receivers", m_dbConnection); SQLiteDataReader reader = getReceivers.ExecuteReader(); //Read every entry in the receivers table while (reader.Read()) { string name = (string)reader["name"]; Receiver battery = new Receiver((string)reader["name"], (double)reader["weight"], (int)reader["channelCount"], (int)reader["minVoltage"], (int)reader["maxVoltage"]); //Add the battery into the dictionary using the name as the key and a new Receiver object as the value receivers.Add(name, battery); } } //Close the database connection m_dbConnection.Close(); } //Save the updated savedReceiver list savedReceivers = receivers; }
public static DataTable SearchMusicDatabase( FileInfo theDatabaseFileInfo, string theCriteria) { using (DataSet theDataSet = new DataSet()) { using (SQLiteConnection theConnection = new SQLiteConnection( DatabaseLayer.ToConnectionString(theDatabaseFileInfo))) { try { theConnection.Open(); using (SQLiteCommand theCommand = theConnection.CreateCommand()) { StringBuilder theCommandBuilder = new StringBuilder( "SELECT [ID], [Path], [Details], [Extension], 0, " + "[Path] || '\\' || [Details] || [Extension] AS [FullPath] "); theCommandBuilder.Append("FROM [Tracks] "); theCommandBuilder.Append("WHERE "); bool IsFirstParameter = true; foreach (string thisCriteria in theCriteria.Split(' ')) { if (!IsFirstParameter) { theCommandBuilder.Append(" AND "); } theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)"); SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter(); theCommand.Parameters.Add(thisCriteriaParameter); theCommand.Parameters.Add(thisCriteriaParameter); thisCriteriaParameter.Value = String.Format( CultureInfo.CurrentCulture, "%{0}%", thisCriteria.ToString()); IsFirstParameter = false; } theCommandBuilder.Append(" ORDER BY [Path], [Details]"); theCommand.CommandText = theCommandBuilder.ToString(); using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand)) { theAdapter.Fill(theDataSet); } } } finally { theConnection.Close(); } } return theDataSet.Tables[0]; } }
public Notification GetNotification(ref string strError) { Notification notification = new Notification(); try { using (var conn = new SQLiteConnection(ConnectionString)) using (var cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = "SELECT * FROM Notification WHERE NotificationPK = 1"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Notification n = new Notification() { EmailAddress = reader.GetString(reader.GetOrdinal("EmailAddress")), PhoneNumber = reader.GetString(reader.GetOrdinal("PhoneNumber")), NotificationType = reader.GetString(reader.GetOrdinal("NotificationType")), CarrierGateway = reader.GetString(reader.GetOrdinal("CarrierGateway")), Carrier = reader.GetString(reader.GetOrdinal("Carrier")) }; notification = n; } } } } catch (Exception ex) { strError = ex.Message; } return notification; }
public void create(string file) { db_file = file; conn = new SQLiteConnection("Data Source=" + db_file); if (!File.Exists(db_file)) { conn.Open(); using (SQLiteCommand command = conn.CreateCommand()) { command.CommandText = @"CREATE TABLE `erogamescape` ( `id` INTEGER NOT NULL, `title` TEXT, `saleday` TEXT, `brand` TEXT, PRIMARY KEY(id)); CREATE TABLE `tableinfo` ( `tablename` TEXT NOT NULL, `version` INTEGER, PRIMARY KEY(tablename))"; command.ExecuteNonQuery(); command.CommandText = @"INSERT INTO tableinfo VALUES('erogamescape',0)"; command.ExecuteNonQuery(); } conn.Close(); } }
public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None) { _databaseFullPath = databaseFullPath; _destructorType = destructorType; _connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath); if (!File.Exists(_databaseFullPath)) { SQLiteConnection.CreateFile(_databaseFullPath); Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath); } else { Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath); } using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { Log.Verbose("About to create or check for the Entities table in the EntityStore database."); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database."); } } }
public void TrimSQLiteDatabase(string filename, string tablename, int MaxRows, string TestColumnName) { if (!Program.ENABLE_SQLITE) { return; } string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", filename); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); int ret = 0; string olddate; using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { cmd.CommandText = @"SELECT COUNT(*) AS NumberOfRows FROM " + tablename; cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "NumberOfRows" }); //int ret = cmd.ExecuteNonQuery(); SQLiteDataReader rdr = cmd.ExecuteReader(); rdr.Read(); ret = rdr.GetInt32(0); //get the number from return parameter position 0 rdr.Close(); if (ret > MaxRows) { //obtain the minimum value for the test column (usually a date field) cmd.CommandText = @"SELECT MIN(" + TestColumnName + ") AS OldestDate FROM " + tablename; cmd.Parameters.Clear(); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "OldestDate" }); SQLiteDataReader rdr2 = cmd.ExecuteReader(); rdr2.Read(); olddate = rdr2.GetString(0); rdr2.Close(); //delete all rows containing that minimum test value cmd.CommandText = @"DELETE FROM " + tablename + " WHERE " + TestColumnName + " = '" + olddate + "'"; cmd.Parameters.Clear(); cmd.ExecuteNonQuery(); } } Program.logEvent("Local SQLite Database Table " + tablename + " Trimmed Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }
/// <summary> /// SQLを直接発行してDBにアクセス。 /// DB以外のパッケージからは使用しないこと /// </summary> /// <param name="sql"></param> /// <returns></returns> public SQLiteDataReader ExecuteReader(string sql) { SQLiteCommand cmd = conn_.CreateCommand(); cmd.CommandText = sql; var reader = cmd.ExecuteReader(); return(reader); }
//CREATE TABLE cookies(creation_utc INTEGER NOT NULL UNIQUE PRIMARY KEY, host_key TEXT NOT NULL, name TEXT NOT NULL, value TEXT NOT NULL, path TEXT NOT NULL, //expires_utc INTEGER NOT NULL, secure INTEGER NOT NULL, httponly INTEGER NOT NULL, last_access_utc INTEGER NOT NULL, has_expires INTEGER NOT NULL DEFAULT 1, //persistent INTEGER NOT NULL DEFAULT 1, priority INTEGER NOT NULL DEFAULT 1, encrypted_value BLOB DEFAULT '', firstpartyonly INTEGER NOT NULL DEFAULT 0) static public IEnumerable <Cookie> ReadCookies() { var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Can't find cookie store", dbPath); } var connectionString = "Data Source=" + dbPath; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); cmd.Parameters.Add(prm); cmd.CommandText = "SELECT * FROM cookies;Read Only=True"; //cmd.CommandText = "PRAGMA table_info(cookies)"; //cmd.CommandText = "SELECT sql FROM sqlite_master WHERE tbl_name = 'cookies' AND type = 'table'"; conn.Open(); try { using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var decoded_value = System.Security.Cryptography.ProtectedData.Unprotect((byte[])reader["encrypted_value"], null, System.Security.Cryptography.DataProtectionScope.CurrentUser); var ascii_value = Encoding.ASCII.GetString(decoded_value); yield return(new Cookie() { creation_utc = (System.Int64)reader["creation_utc"], host_key = (string)reader["host_key"], name = (string)reader["name"], value = (string)reader["value"], path = (string)reader["path"], expires_utc = (System.Int64)reader["expires_utc"], secure = (System.Int64)reader["secure"], httponly = (System.Int64)reader["httponly"], last_access_utc = (System.Int64)reader["last_access_utc"], has_expires = (System.Int64)reader["has_expires"], persistent = (System.Int64)reader["persistent"], priority = (System.Int64)reader["priority"], AsciiDecryptedValue = ascii_value, firstpartyonly = (System.Int64)reader["firstpartyonly"] }); } } } finally { conn.Close(); } } }
public List <CookieItem> ReadChromeCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } List <CookieItem> ret = new List <CookieItem>(); var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var name = reader[0].ToString(); var encryptedData = (byte[])reader[1]; string encKey = File.ReadAllText(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Local State"); encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString(); var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine); var _cookie = _decryptWithKey(encryptedData, decodedKey, 3); if (ret.Find(x => x.Name == name) == null) { ret.Add(new CookieItem { Name = name, Value = _cookie }); } } } conn.Close(); } return(ret); }
private static void GetItemsFromDatabase() { List <int> numberOfPeakMatchesList = new List <int>(); List <string> toDatabaseList = new List <string>(); var Binner = new Binner(); using ( System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=" + LibraryDBPath + ".db3")) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "ATTACH '" + QueryDBPath + ".db3' AS db2;"; cmd.ExecuteNonQuery(); cmd.CommandText = "ATTACH '" + QueryLibraryPairsDB + ".db3' AS dbPair;"; cmd.ExecuteNonQuery(); //This select statement is equivalent to if I had used inner joins between the tables. This version is just in an older style. cmd.CommandText = "SELECT main.Spectrum.mzList, main.Spectrum.charge, main.Spectrum.precursor, main.Spectrum.peptide, main.Files.path, " + "db2.QuerySpectrum.mzList, db2.QuerySpectrum.charge, db2.QuerySpectrum.precursor, db2.Files.path " + "FROM main.Spectrum, main.Files, db2.Files, db2.QuerySpectrum, dbPair.QueryLibraryPair " + "WHERE main.Spectrum.ID = dbPair.QueryLibraryPair.id_Library AND db2.QuerySpectrum.ID = dbPair.QueryLibraryPair.id_Query " + "AND db2.QuerySpectrum.codex = db2.Files.codex AND main.Spectrum.codex = main.Files.codex " + "AND dbPair.QueryLibraryPair.PeakMatchNumber<11;"; using (var reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult)) { while (reader.Read()) { Library oneLibrary = new Library(Convert.ToInt32(reader.GetString(1)), reader.GetString(3), reader.GetString(0).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(4), Convert.ToDouble(reader.GetString(2))); Query oneQuery = new Query(Convert.ToInt32(reader.GetString(6)), reader.GetString(5).Split(',').Select(n => Convert.ToDouble(n)).ToArray(), reader.GetString(8), Convert.ToDouble(reader.GetString(7))); listOfAllLibraries.Add(oneLibrary); listOfAllQueries.Add(oneQuery); if (listOfAllLibraries.Count % 50 == 0) { Console.WriteLine("processed: " + listOfAllLibraries.Count); } if (listOfAllLibraries.Count >= 500) //if(listOfAllLibraries.Count %1000 ==0) { Binner.processor(listOfAllQueries, listOfAllLibraries); listOfAllLibraries = new List <Library>(); listOfAllQueries = new List <Query>(); // break; } } } Binner.sendToFile(outputFilename); } conn.Close(); } }
private void executeCommand(Action <SQLiteCommand> commandAction) { using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db")) { database.Open(); using (SQLiteCommand command = database.CreateCommand()) { commandAction.Invoke(command); } } }
public void InsertValues(string table, IEnumerable <string> values, bool replace = false) { var sb = new StringBuilder(); sb.Append("INSERT OR "); sb.Append(replace ? "REPLACE" : "IGNORE"); sb.Append(" INTO "); sb.Append(table); sb.Append(" VALUES("); foreach (string value in values) { if (value == null) { sb.Append("NULL"); } else { bool isNumber = value.Length > 0; foreach (char c in value) { if (!Char.IsNumber(c)) { isNumber = false; break; } } if (!isNumber) { sb.Append("'"); } sb.Append(value.Replace("'", "''")); if (!isNumber) { sb.Append("'"); } } sb.Append(","); } sb.Remove(sb.Length - 1, 1); sb.Append(");"); string sql = sb.ToString(); sb.Clear(); sb = null; using (var command = m_Connection.CreateCommand()) { command.CommandText = sql; command.ExecuteNonQuery(); } }
public List <CookieItem> ReadCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } List <CookieItem> ret = new List <CookieItem>(); var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var name = reader[0].ToString(); var encryptedData = (byte[])reader[1]; var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser); var plainText = Encoding.ASCII.GetString(decodedData); // Looks like ASCII if (ret.Find(x => x.Name == name) == null) { ret.Add(new CookieItem { Name = name, Value = plainText }); } } } conn.Close(); } return(ret); }
public void SaveDictionaryToDatabaseNewConnection([ItemNotNull][JetBrains.Annotations.NotNull] List <Dictionary <string, object> > values, [JetBrains.Annotations.NotNull] string tableName, [JetBrains.Annotations.NotNull] HouseholdKey householdKey) { string sql = "Insert into " + tableName + "("; string fields = ""; string parameters = ""; foreach (KeyValuePair <string, object> pair in values[0]) { fields += pair.Key + ","; parameters += "@" + pair.Key + ","; } fields = fields.Substring(0, fields.Length - 1); parameters = parameters.Substring(0, parameters.Length - 1); sql += fields + ") VALUES (" + parameters + ")"; string dstFileName = GetFilenameForHouseholdKey(householdKey); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dstFileName + ";Version=3;Synchronous=OFF;Journal Mode=WAL;")) { conn.Open(); using (var transaction = conn.BeginTransaction()) { var command = conn.CreateCommand(); command.CommandText = sql; foreach (Dictionary <string, object> row in values) { if (row.Count != values[0].Count) { throw new LPGException("Incorrect number of columns"); } command.Parameters.Clear(); foreach (KeyValuePair <string, object> pair in row) { string parameter = "@" + pair.Key; command.Parameters.AddWithValue(parameter, pair.Value); } command.ExecuteNonQuery(); } transaction.Commit(); } conn.Close(); } }
//[JetBrains.Annotations.NotNull] //public string ReturnMainSqlPath() => _filenameByHouseholdKey[Constants.GeneralHouseholdKey].Filename; private static void SaveDictionaryToDatabase([NotNull][ItemNotNull] List <Dictionary <string, object> > values, [NotNull] string tableName, [NotNull] System.Data.SQLite.SQLiteConnection conn) { if (values.Count == 0) { return; } //figure out sql var firstrow = values[0]; string sql = "Insert into " + tableName + "("; string fields = ""; string parameters = ""; foreach (KeyValuePair <string, object> pair in firstrow) { fields += pair.Key + ","; parameters += "@" + pair.Key + ","; } fields = fields.Substring(0, fields.Length - 1); parameters = parameters.Substring(0, parameters.Length - 1); sql += fields + ") VALUES (" + parameters + ")"; //execute the sql using (var transaction = conn.BeginTransaction()) { using (var command = conn.CreateCommand()) { command.CommandText = sql; foreach (Dictionary <string, object> row in values) { if (row.Count != firstrow.Count) { throw new LPGException("Incorrect number of columns"); } command.Parameters.Clear(); foreach (KeyValuePair <string, object> pair in row) { string parameter = "@" + pair.Key; command.Parameters.AddWithValue(parameter, pair.Value); } command.ExecuteNonQuery(); } } transaction.Commit(); } }
private static bool GetCookie_Chrome(string strHost, string strField, ref string Value) { Value = string.Empty; bool fRtn = false; string strPath, strDb; // Check to see if Chrome Installed strPath = GetChromeCookiePath(); if (string.Empty == strPath) // Nope, perhaps another browser { return(false); } try { strDb = "Data Source=" + strPath + ";pooling=false"; using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strDb)) { using (SQLiteCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT value FROM cookies WHERE host_key LIKE '%" + strHost + "%' AND name LIKE '%" + strField + "%';"; conn.Open(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Value = reader.GetString(0); if (!Value.Equals(string.Empty)) { fRtn = true; break; } } } conn.Close(); } } } catch (Exception) { Value = string.Empty; fRtn = false; } return(fRtn); }
/// <summary> /// Opret forbindelse til databasen /// </summary> public static bool Connect(string connectionString, string prefix = "") { if (IsConnected) { return(false); } //Start connection sql_conn = new SQLite_LIB.SQLiteConnection(connectionString); sql_conn.Open(); sql_cmd = sql_conn.CreateCommand(); _prefix = prefix; _isConnected = true; return(true); }
static public IEnumerable <Tuple <string, string> > ReadCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); //cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key = @hostName"; cmd.CommandText = "SELECT name,encrypted_value,host_key FROM cookies"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var encryptedData = (byte[])reader[1]; var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser); var plainText = Encoding.ASCII.GetString(decodedData); // Looks like ASCII if (Regex.IsMatch(reader.GetString(2), hostName)) { yield return(Tuple.Create(reader.GetString(2) + ":" + reader.GetString(0), plainText)); } } } conn.Close(); } }
private List <string> RetrieveLateIDNumbers() { /// ///Retrieve unfinished transactions from LOCAL SQLite DATABASE /// List <string> CardNumbers = new List <string>(); string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"SELECT * FROM RES_KEY WHERE (ID_CARD_NO_IN is null or ID_CARD_NO_IN = '')"; SQLiteDataReader r = cmd.ExecuteReader(); while (r.Read()) { DateTime Datetime = Convert.ToDateTime(r["OUT_DATETIME"]); //Select Card records which have a Date of today, yesterday or two days ago. This way records which have been accidentally created will not continue to send reminders forever if ((Datetime.Date == DateTime.Today) || (Datetime.Date == DateTime.Today - new TimeSpan(1, 0, 0, 0)) || (Datetime.Date == DateTime.Today - new TimeSpan(2, 0, 0, 0))) { CardNumbers.Add(Convert.ToString(r["ID_CARD_NO_OUT"])); } } dbConn.Close(); } Program.logEvent("Local SQLite Database Transaction Inserted Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } return(CardNumbers); }
/// <summary> /// Ctor /// </summary> public Bot() { SDB = new SQLiteConnection(); SDB.ConnectionString = BuildConString(@"D:\AIML\ConsoleBot\bin\Debug\AIML.aidb", 8192, 3, 8192, 8192, true, false); SDB.Open(); using (SQLiteTransaction tr = SDB.BeginTransaction()) { using (SQLiteCommand cmd = SDB.CreateCommand()) { cmd.CommandText = "PRAGMA temp_store = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "PRAGMA count_changes = OFF"; cmd.ExecuteNonQuery(); cmd.CommandText = "PRAGMA page_size=8192"; cmd.ExecuteNonQuery(); } } this.setup(); }
public override List <WebVisit> RecentStackoverflow(string dbPath) { var list = new List <WebVisit>(); using (var connection = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath + ";Version=3;Read Only=True")) { connection.Open(); var command = @"SELECT urls.url, visits.visit_time, urls.title FROM visits, urls WHERE visits.url = urls.id AND urls.url LIKE '%stackoverflow%' "; using (var c = connection.CreateCommand()) { c.CommandText = command; var reader = c.ExecuteReader(); var urls = new HashSet <string>(); while (reader.Read()) { var url = reader.GetString(0); var timeEpoch = reader.GetInt64(1) / 1000; var visitTime = FromGoogleTime(timeEpoch); var title = reader.GetString(2); if (!urls.Contains(url)) { list.Add(new WebVisit() { Url = url, Timestamp = visitTime, Title = title }); } urls.Add(url); } } connection.Close(); } return(list); }
static void Main(string[] args) { string filter = ""; string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Login Data"; string db = "Data Source=" + path + ";pooling=false"; if (args.Length >= 1) { filter = args[0]; Console.WriteLine("Filter is {0}", filter); } Console.WriteLine("DS is located at {0}", path); SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(db); SQLiteCommand cmd = conn.CreateCommand(); if (filter.Equals("")) { cmd.CommandText = "SELECT action_url, username_value, password_value FROM logins"; } else { cmd.CommandText = "SELECT action_url, username_value, password_value FROM logins WHERE action_url LIKE '%" + filter + "%'"; } conn.Open(); SQLiteDataReader result = cmd.ExecuteReader(); while (result.Read()) { byte[] data = (byte[])result[2]; byte[] plaintext = System.Security.Cryptography.ProtectedData.Unprotect(data, null, System.Security.Cryptography.DataProtectionScope.CurrentUser); Console.WriteLine("{0}:{1}={2}", result.GetString(0), result.GetString(1), Encoding.ASCII.GetString(plaintext)); Console.Read(); } conn.Close(); }
public void GetAllClassesTest() { QuizardDatabase db = null; Assert.DoesNotThrow(delegate { db = new QuizardDatabase(); db.Open(); if (!File.Exists("quizard.db")) { int x = db.buildDB(); Assert.AreEqual(x, 0); } }); Console.WriteLine("Database created"); List <Class> allClasses = db.GetAllClasses(); Console.WriteLine("Query successful"); Console.WriteLine("Class count = " + allClasses.Count); Assert.DoesNotThrow(delegate { using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db")) { database.Open(); using (SQLiteCommand command = database.CreateCommand()) { Console.WriteLine("SELECT COUNT(*) FROM classes;"); command.CommandText = "SELECT COUNT(*) FROM classes;"; Assert.AreEqual(command.ExecuteScalar(), allClasses.Count); Console.WriteLine("Count was equal"); } } }); }
public bool ErrorDatabaseEntry(string Reservation, string Operator, string SQLFailTimeStamp, string KioskNum, int FailErrorFlag, string Generic1, string Generic2, int Generic3) { if (!Program.ENABLE_SQLITE) { return(true); } string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { cmd.CommandText = @"INSERT INTO RES_KEY_FAIL ( RESV_RESERV_NO, OPERATOR_NO, FAIL_DATETIME, SQL_FAIL_TIME_STAMP, KIOSK_FAIL_NO, FAIL_ERROR_FLAG, COL_GENERIC_1, COL_GENERIC_2, COL_GENERIC_3 ) VALUES ( @Reservation, @Operator, @FailDateTime, @SQLFailDateTime, @KioskID, @FailErrFlag, @Generic1, @Generic2, @Generic3 )"; //parameterized update - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@Reservation", Value = Reservation }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@Operator", Value = Operator }); // SQLite date format is: yyyy-MM-dd HH:mm:ss cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@FailDateTime", Value = String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now) }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@SQLFailDateTime", Value = SQLFailTimeStamp }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KioskID", Value = Program.KIOSK_ID }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@FailErrFlag", Value = FailErrorFlag }); string gen1; if (Generic1.Length > 254) { gen1 = Generic1.Substring(0, 254); } else { gen1 = Generic1; } cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@Generic1", Value = gen1 }); string gen2; if (Generic2.Length > 254) { gen2 = Generic2.Substring(0, 254); } else { gen2 = Generic2; } cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@Generic2", Value = gen2 }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@Generic3", Value = Generic3 }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Error Inserted Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } return(true); }
public List <CookieItem> ReadCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } List <CookieItem> ret = new List <CookieItem>(); var oriCookieFile = $@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Default\Cookies"; var copyCookieFile = @"c:\setting\Cookies"; if (File.Exists(oriCookieFile)) { File.Copy(oriCookieFile, copyCookieFile, true); } else { throw new System.IO.FileNotFoundException("Cant find cookie store", oriCookieFile); } var dbPath = copyCookieFile; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key = '" + hostName + "'"; //cmd.CommandText = "SELECT * FROM cookies"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var name = reader[0].ToString(); var encryptedData = (byte[])reader[1]; string encKey = File.ReadAllText($@"C:\Users\{localUser}\AppData\Local\115Chrome\User Data\Local State"); encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString(); var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine); var _cookie = _decryptWithKey(encryptedData, decodedKey, 3); if (string.IsNullOrEmpty(_cookie)) { _cookie = Encoding.ASCII.GetString(System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser)); } if (ret.Find(x => x.Name == name) == null) { ret.Add(new CookieItem { Name = name, Value = _cookie }); } } } conn.Close(); } return(ret); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection, and returns /// the results. If the connection string is null, a temporary in-memory /// database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { if (connectionString == null) { connectionString = DefaultConnectionString; } using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { if (arg is SQLiteParameter) { command.Parameters.Add((SQLiteParameter)arg); } else { command.Parameters.Add(new SQLiteParameter(DbType.Object, arg)); } } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { return(command.ExecuteReader(commandBehavior)); } } } } return(null); }
private void UpdateDBTransaction() { /// ///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE /// string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"UPDATE RES_KEY SET IN_DATETIME = @ReturnDate, KEY_BOX_IN_NO = @KeyBoxNumIn, TRANSACTION_IN_NO = @TransactionNum, CAR_DAMAGED = @CarDamaged, CAR_CLEANED = @CarCleaned, CAR_REFUELED = @CarRefueled WHERE RESV_RESERV_NO = @ResvNum "; //parameterized update - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter {// SQLite date format is: yyyy-MM-dd HH:mm:ss ParameterName = "@ReturnDate", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KeyBoxNumIn", Value = this.BoxNumber, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@TransactionNum", Value = this.IdentificationType, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarDamaged", Value = this.CarDamaged, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarCleaned", Value = this.CarCleaned, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarRefueled", Value = this.CarRefueled, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ResvNum", Value = this.AccessCode }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Updated Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }
private void InsertDBTransaction() { /// ///INSERT TRANSACTION INTO LOCAL SQLite DATABASE /// string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"INSERT INTO RES_KEY ( OUT_DATETIME, KEY_BOX_OUT_NO, RESV_RESERV_NO, TRANSACTION_OUT_NO, KIOSK_ID ) VALUES ( @KioskDateOut, @BoxNum, @ReservNum, @IdentificationType, @KioskId )"; //parameterized insert - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter {// SQLite date format is: yyyy-MM-dd HH:mm:ss ParameterName = "@KioskDateOut", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@BoxNum", Value = this.BoxNumber, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ReservNum", Value = this.AccessCode, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@IdentificationType", Value = this.IdentificationType, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KioskId", Value = Program.KIOSK_ID, }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Inserted Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }
private void UpdateDBTransaction() { /// ///UPDATE TRANSACTION INTO REMOTE SQL SERVER DATABASE /// #region SQL SERVER int intReturnValue = 0; int intOutput1 = 0; SqlCommand comm = new SqlCommand(); //This is needed because there is no public contructor for SqlParameterCollection SqlParameterCollection Parameters = comm.Parameters; SqlParameterCollection ReturnParameters; try { //Input Parameters Parameters.Add(Program.SqlManager.CreateParameter("ResvNum", ParameterDirection.Input, this.AccessCode, SqlDbType.VarChar)); Parameters.Add(Program.SqlManager.CreateParameter("OperNum", ParameterDirection.Input, this.OperatorNumber, SqlDbType.VarChar)); Parameters.Add(Program.SqlManager.CreateParameter("KioskDateIn", ParameterDirection.Input, this.DateAndTime, SqlDbType.VarChar)); Parameters.Add(Program.SqlManager.CreateParameter("KioskNumIn", ParameterDirection.Input, Program.KIOSK_ID.PadLeft(4, '0'), SqlDbType.VarChar)); Parameters.Add(Program.SqlManager.CreateParameter("MileageEnd", ParameterDirection.Input, this.Odometer, SqlDbType.Int)); Parameters.Add(Program.SqlManager.CreateParameter("KeyNumMax", ParameterDirection.Input, Program.NUMBER_RELAYS, SqlDbType.Int)); //Return Values Parameters.Add(Program.SqlManager.CreateParameter("KeyNumIn", ParameterDirection.Output, 0, SqlDbType.Int)); Parameters.Add(Program.SqlManager.CreateParameter("Return_Val", ParameterDirection.ReturnValue, 0, SqlDbType.Int)); ReturnParameters = Program.SqlManager.SqlStoredProcedure("SPA_ReturnKey", Parameters); intOutput1 = (int)ReturnParameters["KeyNumIn"].Value; //set the return code (of the stored procedure) to the results sent back. intReturnValue = (int)ReturnParameters["Return_Val"].Value; switch (intReturnValue) { case 1: //Success Program.logEvent("Transaction Data Successfully Updated into Database"); break; case 12: //Failure Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000); Program.logEvent("Failure - can't save data - perhaps server is down."); Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - can't save data - perhaps server is down.", "", Odometer); break; case 13: //Failure Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000); Program.logEvent("Failure - RES_KEY table INSERT failed."); Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - RES_KEY table INSERT failed.", "", Odometer); break; case 14: //Failure Program.ShowErrorMessage("Database Error\r\nTry Again Or\r\nPlease Call " + Program.SERVICE_MANAGER_NUMBER, 5000); Program.logEvent("Failure - Key# not in range (CAST(em.PARKING_STALL as int))."); Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "Failure - Key# not in range (CAST(em.PARKING_STALL as int)).", "", Odometer); break; } } catch (Exception ex) { //An application exception was thrown - process/display exception. Program.logEvent("GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message); Program.SqlManager.ErrorDatabaseEntry(AccessCode, OperatorNumber, DateTime.Now.ToString(), Program.KIOSK_ID, 0, "GENERAL EXCEPTION:" + "Transaction Update: " + ex.Message, "", Odometer); Program.ShowErrorMessage("Database Error\r\nTransaction Data Saved\r\nPlease Call\r\n" + Program.SERVICE_MANAGER_NUMBER, 5000); } #endregion /// ///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE /// #region SQLite if (!Program.ENABLE_SQLITE) { return; } string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- // '56650' , '501889889' , 38318 , '2012-12-20' , 'RETURNED' 3 '2012-12-20' , 'COTTAGE GROVE','OR' '011563' //RESV_RESERV_NO OPER_OPER_NO METER_1_READING_OUT REQUIRED_DATETIME STATUS KeyBoxNum DUE_DATETIME_ORIG DESTINATION_CITY EQ_EQUIP_NO cmd.CommandText = @"UPDATE RES_KEY SET KIOSK_IN_NO = @KioskNumIn, KEY_BOX_IN_NO = @KeyNumIn, RETURN_DATETIME = @ReturnDate, METER_1_READING_OUT = @MileageBegin, METER_1_READING_IN = @MileageEnd WHERE RESV_RESERV_NO = @ResvNum "; //parameterized update - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KioskNumIn", Value = Program.KIOSK_ID.PadLeft(4, '0'), }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KeyNumIn", Value = this.BoxNumber, }); // SQLite date format is: yyyy-MM-dd HH:mm:ss cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ReturnDate", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@MileageBegin", Value = " ", }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@MileageEnd", Value = this.Odometer, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ResvNum", Value = this.AccessCode }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Updated Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } #endregion }
private void UpdateDBTransaction() { /// ///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE /// string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"UPDATE RES_KEY SET IN_DATETIME = @ReturnDate, ID_CARD_NO_IN = @CardNoIn, RFID_TAG_IN_DETECTED = @TagDetectIn WHERE ID_CARD_NO_OUT = @CardNoIn AND OUT_DATETIME = (SELECT max(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_OUT = @CardNoIn) "; //This query should update the newest transaction for CardNoIn //////Alternatively perhaps we should select the oldest transaction for CardNoIn which hasn't been finished // ID_CARD_NO_OUT = @CardNoIn // AND // OUT_DATETIME = (SELECT min(OUT_DATETIME) FROM RES_KEY WHERE ID_CARD_NO_IN = '') //parameterized update - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter {// SQLite date format is: yyyy-MM-dd HH:mm:ss ParameterName = "@ReturnDate", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CardNoIn", Value = this.CardNumber, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@TagDetectIn", Value = this.RFIDTagDetected, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KeyBoxNo", Value = this.BoxNumber }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Updated Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }