Пример #1
0
		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);
		}
Пример #2
0
        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);
            }
        }
Пример #3
0
 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();
     }
       }
     }
       }
 }
Пример #4
0
        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();
                }
            }
        }
Пример #5
0
		/// <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();
			}
		}
Пример #6
0
        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();
                }
            }
        }
Пример #7
0
		/// <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);
				}
			}
		}
Пример #8
0
        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();
                }
            }
        }
Пример #10
0
 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();
     }
 }
Пример #11
0
        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();
        }
Пример #12
0
    /// <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);
 }
Пример #14
0
        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.");
            }
        }
Пример #16
0
        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;
        }
Пример #17
0
        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();
            }
        }
Пример #20
0
        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;
                }
            }
        }
Пример #21
0
        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;
        }
Пример #22
0
        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;
        }
Пример #23
0
        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;
        }
Пример #24
0
 /// <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();
                 }
             }
         }
     }
 }
Пример #25
0
        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;
        }
Пример #26
0
 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
         { }
     }
 }
Пример #27
0
 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;
 }
Пример #28
0
 /// <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;
 }
Пример #29
0
        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;
        }
Пример #30
0
		/// <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;
        }
Пример #32
0
        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];
            }
        }
Пример #33
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;
        }
Пример #34
0
        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();
            }
        }
Пример #35
0
		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.");
				}
			}
		}
Пример #36
0
        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();
                }
            }
        }
Пример #37
0
        /// <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);
        }
Пример #38
0
        //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();
                    }
                }
        }
Пример #39
0
    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);
    }
Пример #40
0
        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();
            }
        }
Пример #41
0
 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);
         }
     }
 }
Пример #42
0
        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();
            }
        }
Пример #43
0
    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();
            }
        }
Пример #46
0
        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);
        }
Пример #47
0
        /// <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);
        }
Пример #48
0
        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();
                }
        }
Пример #49
0
        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);
        }
Пример #50
0
        /// <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();
        }
Пример #51
0
        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);
        }
Пример #52
0
        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();
        }
Пример #53
0
        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");
                    }
                }
            });
        }
Пример #54
0
        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);
        }
Пример #55
0
    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);
    }
Пример #56
0
        /// <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();
                    }
                }
            }
Пример #59
0
            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
            }
Пример #60
0
            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();
                    }
                }
            }