Ejemplo n.º 1
1
        private Theme FillTheme(SqliteDataReader reader)
        {
            Theme value = new Theme();

            int index = reader.GetOrdinal("Id");
            if (!reader.IsDBNull(index))
            {
                value.Id = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("Title");
            if (!reader.IsDBNull(index))
            {
                value.Title = reader.GetString(index);
            }

            index = reader.GetOrdinal("Description");
            if (!reader.IsDBNull(index))
            {
                value.Description = reader.GetString(index);
            }

            index = reader.GetOrdinal("ParentId");
            if (!reader.IsDBNull(index))
            {
                value.ParentId = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("ParentTitle");
            if (!reader.IsDBNull(index))
            {
                value.ParentTitle = reader.GetString(index);
            }

            index = reader.GetOrdinal("WithText");
            if(!reader.IsDBNull(index))
            {
                value.WithText = reader.GetBoolean(index);
            }

            return value;
        }
Ejemplo n.º 2
1
        private Painting FillPainting(SqliteDataReader reader)
        {
            Painting value = new Painting();

            int index = reader.GetOrdinal("Id");
            if (!reader.IsDBNull(index))
            {
                value.Id = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("Title");
            if (!reader.IsDBNull(index))
            {
                value.Title = reader.GetString(index);
            }

            index = reader.GetOrdinal("ThemeId");
            if (!reader.IsDBNull(index))
            {
                value.ThemeId = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("ThemeTitle");
            if (!reader.IsDBNull(index))
            {
                value.ThemeTitle = reader.GetString(index);
            }

            index = reader.GetOrdinal("Description");
            if (!reader.IsDBNull(index))
            {
                value.Description = reader.GetString(index);
            }

            index = reader.GetOrdinal("Filename");
            if (!reader.IsDBNull(index))
            {
                value.Filename = reader.GetString(index);
            }

            index = reader.GetOrdinal("OnSlider");
            if (!reader.IsDBNull(index))
            {
                value.OnSlider = reader.GetBoolean(index);
            }

            index = reader.GetOrdinal("Price");
            if (!reader.IsDBNull(index))
            {
                value.Price = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("Available");
            if (!reader.IsDBNull(index))
            {
                value.Available = reader.GetBoolean(index);
            }

            return value;
        }
Ejemplo n.º 3
1
        private Event FillEvent(SqliteDataReader reader)
        {
            Event value = new Event();

            int index = reader.GetOrdinal("Id");
            if (!reader.IsDBNull(index))
            {
                value.Id = reader.GetInt32(index);
            }

            index = reader.GetOrdinal("Title");
            if (!reader.IsDBNull(index))
            {
                value.Title = reader.GetString(index);
            }

            index = reader.GetOrdinal("Description");
            if (!reader.IsDBNull(index))
            {
                value.Description = reader.GetString(index);
            }

            index = reader.GetOrdinal("Modification");
            if (!reader.IsDBNull(index))
            {
                string modification = reader.GetString(index);

                value.ModificationDate = DateTime.ParseExact(modification, DateTimeFormat, CultureInfo.InvariantCulture);
            }

            index = reader.GetOrdinal("Expiration");
            if (!reader.IsDBNull(index))
            {
                string expiration = reader.GetString(index);
                value.ExpirationDate = DateTime.ParseExact(expiration, DateTimeFormat, CultureInfo.InvariantCulture);
            }

            return value;
        }
Ejemplo n.º 4
1
        protected override void OnCreate(Bundle bundle) {
            base.OnCreate(bundle);

            // Set our view from the "main" layout resource
            SetContentView(Resource.Layout.Main);

            // Get our button from the layout resource,
            // and attach an event to it
            Button button = FindViewById<Button>(Resource.Id.MyButton);

            button.Click += delegate { button.Text = string.Format("{0} clicks!", count++); };

            #region ADDED TO SAMPLE TO DEMONSTRATE Portable.Data.Sqlite

            string myTableName = "TestTable1";
            string sql;

            //Instantiate my "crypt engine"
            this.AppCryptEngine = this.AppCryptEngine ?? new FakeCryptEngine(this.AppPassword);

            #region Part 1 - ADO - Create a table, add a record, add a column, add encrypted data, read back data

            using (var dbConn = new SqliteAdoConnection(this.SqliteConnection, this.AppCryptEngine)) {

                Console.WriteLine("PART 1 - Doing ADO stuff");

                //Create the table if it doesn't exist
                sql = "CREATE TABLE IF NOT EXISTS " + myTableName + " (IdColumn INTEGER PRIMARY KEY AUTOINCREMENT, DateTimeColumn DATETIME, TextColumn TEXT);";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    dbConn.SafeOpen();
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("Table [" + myTableName + "] created (if it didn't exist).");
                }

                //Add a record
                sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn) VALUES (@date, @text);";
                int newRowId;
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
                    cmd.Parameters.Add(new SqliteParameter("@text", "Hello SQLite."));
                    dbConn.SafeOpen();
                    newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId());  //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
                    Console.WriteLine("A record with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
                }

                //Read the datetime column on the oldest record
                sql = "SELECT [DateTimeColumn] FROM " + myTableName + " ORDER BY [DateTimeColumn] LIMIT 1;";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    dbConn.SafeOpen();
                    DateTime oldest = Convert.ToDateTime(cmd.ExecuteScalar());
                    Console.WriteLine("The oldest record in table [" + myTableName + "] has timestamp: " + oldest.ToString());
                }

                //Add an encrypted column to the table
                //NOTE: There is no benefit to creating the column as SQLite datatype ENCRYPTED vs. TEXT
                //  It is actually a TEXT column - but I think it is nice to set it to type ENCRYPTED for future purposes.
                //  Hopefully a future version of SQLitePCL will make it easy to figure out if a column is defined as ENCRYPTED or TEXT
                //  (right now, it identifies both as TEXT)
                sql = "ALTER TABLE " + myTableName + " ADD COLUMN EncryptedColumn ENCRYPTED;";
                //Note: This column shouldn't exist until the above sql is run, since I just created the table above.  But if this application has been run multiple times, 
                //  the column may already exist in the table - so I need to check for it.
                bool columnAlreadyExists = false;
                #region Check for column
                using (var checkCmd = new SqliteCommand(dbConn)) {
                    checkCmd.CommandText = "PRAGMA table_info (" + myTableName + ");";
                    dbConn.SafeOpen();
                    using (var checkDr = new SqliteDataReader(checkCmd)) {
                        while (checkDr.Read()) {
                            if (checkDr.GetString("NAME") == "EncryptedColumn") {
                                Console.WriteLine("The [EncryptedColumn] column already exists.");
                                columnAlreadyExists = true;
                                break;
                            }
                        }
                    }
                }
                #endregion
                if (!columnAlreadyExists) {
                    using (var cmd = new SqliteCommand(sql, dbConn)) {
                        dbConn.SafeOpen();
                        cmd.ExecuteNonQuery();
                        Console.WriteLine("The [EncryptedColumn] column was created in table [" + myTableName + "].");
                    }
                }

                //Add a record with an encrypted column value
                sql = "INSERT INTO " + myTableName + " (DateTimeColumn, TextColumn, EncryptedColumn) VALUES (@date, @text, @encrypted);";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    cmd.Parameters.Add(new SqliteParameter("@date", DateTime.Now));
                    cmd.Parameters.Add(new SqliteParameter("@text", "Hello data."));
                    cmd.AddEncryptedParameter(new SqliteParameter("@encrypted",
                        Tuple.Create<string, string, string>("Hello", "encrypted", "data")));
                    dbConn.SafeOpen();
                    newRowId = Convert.ToInt32(cmd.ExecuteReturnRowId());  //Note: INTEGER columns in SQLite are always long/Int64 - including ID columns, so converting to int
                    Console.WriteLine("A record featuring encrypted data with ID " + newRowId.ToString() + " was created in table [" + myTableName + "].");
                }

                //Get the value of the encrypted column
                sql = "SELECT [EncryptedColumn] FROM " + myTableName + " WHERE [IdColumn] = @id;";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    cmd.Parameters.Add(new SqliteParameter("@id", newRowId));
                    dbConn.SafeOpen();
                    string encryptedColumnValue = cmd.ExecuteScalar().ToString();
                    var decryptedValue = this.AppCryptEngine.DecryptObject<Tuple<string, string, string>>(encryptedColumnValue);
                    Console.WriteLine("The actual (encrypted) value of the [EncryptedColumn] column of record ID " + newRowId.ToString() + " is: " + encryptedColumnValue);
                    Console.WriteLine("The decrypted value of the [EncryptedColumn] column of record ID " + newRowId.ToString() + " is: " +
                        decryptedValue.Item1 + " " + decryptedValue.Item2 + " " + decryptedValue.Item3);
                }

                //Using a SqliteDataReader and GetDecrypted<T> to get all of the encrypted values
                sql = "SELECT [IdColumn], [DateTimeColumn], [EncryptedColumn] FROM " + myTableName + ";";
                using (var cmd = new SqliteCommand(sql, dbConn)) {
                    dbConn.SafeOpen();
                    using (var dr = new SqliteDataReader(cmd)) {
                        while (dr.Read()) {
                            var sb = new StringBuilder();
                            sb.Append("ID: " + dr.GetInt32("IdColumn").ToString());
                            sb.Append(" - Timestamp: " + dr.GetDateTime("DateTimeColumn").ToString());
                            //IMPORTANT: By default, GetDecrypted<T> will throw an exception on a NULL column value.  You can specify DbNullHandling.ReturnTypeDefaultValue
                            // to return the default value of the specified type - as in default(T) - when a NULL column value is encountered, if you choose.
                            var decryptedValue = dr.GetDecrypted<Tuple<string, string, string>>("EncryptedColumn", DbNullHandling.ReturnTypeDefaultValue);
                            sb.Append(" - Value: " + ((decryptedValue == null) ? "NULL" :
                                decryptedValue.Item1 + " " + decryptedValue.Item2 + " " + decryptedValue.Item3));
                            Console.WriteLine(sb.ToString());
                        }
                    }
                }
            }

            #endregion

            #region Part 2 - EncryptedTable - Create an encrypted table to hold SampleDataItem objects, and read and write data

            long numRecords;

            using (var dbConn = new SqliteAdoConnection(this.SqliteConnection, this.AppCryptEngine)) {

                Console.WriteLine(" ");
                Console.WriteLine("PART 2 - Doing EncryptedTable stuff");

                //Creating the encrypted table, adding some items/records
                using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {

                    //Shouldn't need to call CheckDbTable manually, but I am going to check to see if there are
                    //  records in the table, so I need to make sure the table exists
                    //This will check the table and create the table and/or any missing columns if needed.
                    encTable.CheckDbTable();

                    //Check to see how many records are in the table now
                    numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
                    Console.WriteLine("(1) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);

                    foreach (var item in ExampleData.GetData().Where(i => i.LastName != "Johnson")) {
                        encTable.AddItem(item);
                    }

                    Console.WriteLine("(2) There are currently {0} items to be written to the encrypted table: {1}",
                        encTable.TempItems.Where(i => i.IsDirty).Count(), encTable.TableName);

                    //Note that at this point in the code, nothing new has been written to the table yet.  
                    //  The table will be updated on encTable.Dispose (in this case, that happens automatically at the end of this using() code 
                    //  block) or we could force it now with encTable.WriteItemChanges()

                    //encTable.WriteItemChanges();
                }

                //Adding a couple more records...
                using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {

                    //Because encTable was disposed above, we should now see records in the table
                    numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
                    Console.WriteLine("(3) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);

                    //Here is one way to add an item to the table - immediately
                    //  (no need to type out 'immediateWriteToTable:' - but just wanted to show what the 'true' was for)
                    encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Bob").Single(), immediateWriteToTable: true);

                    //Another way to add items to the table - wait until WriteItemChanges() or WriteChangesAndFlush() or encTable.Dispose()
                    //  is called
                    encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Joan").Single(), immediateWriteToTable: false);
                    encTable.AddItem(ExampleData.GetData().Where(i => i.FirstName == "Ned").Single());

                    //Should only see one more record - Joan and Ned haven't been written yet
                    numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
                    Console.WriteLine("(4) There are currently " + numRecords.ToString() + " records in the table: " + encTable.TableName);

                    //Let's see which items we have in memory right now
                    foreach (var item in encTable.TempItems) {
                        Console.WriteLine("In memory: ID#{0} {1} {2} - Status: {3}", item.Id, item.FirstName, item.LastName, item.SyncStatus);
                    }

                    //We can use WriteItemChanges() - writes any in-memory item changes to the table
                    //encTable.WriteItemChanges();

                    //OR WriteChangesAndFlush() writes any in-memory items to the table, and then drops any in-memory items and/or in-memory index of the table
                    //Normally, only items that are out-of-sync with the table are written, forceWriteAll causes all items (whether they have changed or not)
                    //  to be written
                    encTable.WriteChangesAndFlush(forceWriteAll: true);

                    //How many items in memory now?
                    Console.WriteLine("After WriteChangesAndFlush() there are now {0} items in memory.", encTable.TempItems.Count());

                    //How many records in the table?
                    numRecords = SampleDataItem.GetNumRecords(dbConn, encTable.TableName);
                    Console.WriteLine("After WriteChangesAndFlush() there are now {0} records in the table.", numRecords.ToString());
                }

                //Reading and searching for items/records
                using (var encTable = new EncryptedTable<SampleDataItem>(this.AppCryptEngine, dbConn)) {

                    //Doing a GetItems() with an empty TableSearch (like the line below) will get all items
                    List<SampleDataItem> allItems = encTable.GetItems(new TableSearch());

                    foreach (var item in allItems) {
                        Console.WriteLine("In table: ID#{0} {1} {2}", item.Id, item.FirstName, item.LastName);
                    }

                    //Let's just get one item - exceptionOnMissingItem: true will throw an exception if the item wasn't found
                    // in the table; with exceptionOnMissingItem: false, we will just get a null
                    SampleDataItem singleItem = encTable.GetItem(allItems.First().Id, exceptionOnMissingItem: true);
                    Console.WriteLine("Found via ID: ID#{0} {1} {2}", singleItem.Id, singleItem.FirstName, singleItem.LastName);

                    //Because we did a full table GetItems() above, we should have a nice, searchable index of all of the 
                    // items in the table.  But let's check it and re-build if necessary
                    encTable.CheckFullTableIndex(rebuildIfExpired: true);

                    //Otherwise, we could just force a rebuild of the searchable index
                    //  encTable.BuildFullTableIndex();

                    //So, the easy way to find matching items, based on the full table index is to pass in a TableSearch
                    List<SampleDataItem> matchingItems = encTable.GetItems(new TableSearch {
                        SearchType = TableSearchType.MatchAll,  //Items must match all search criteria
                        MatchItems = {
                            new TableSearchItem("LastName", "Johnson", SearchItemMatchType.IsEqualTo),
                            new TableSearchItem("FirstName", "Ned", SearchItemMatchType.DoesNotContain)
                        }
                    });
                    foreach (var item in matchingItems) {
                        Console.WriteLine("Found via search: ID#{0} {1} {2}", item.Id, item.FirstName, item.LastName);
                    }

                    //Let's see what is in this "full table index" anyway
                    foreach (var item in encTable.FullTableIndex.Index) {
                        Console.WriteLine("Indexed item ID: " + item.Key.ToString());
                        foreach (var value in item.Value) {
                            Console.WriteLine("  - Searchable value: {0} = {1}", value.Key ?? "", value.Value ?? "");
                        }
                    }

                    //Let's remove/delete a record from the table (with immediate removal)
                    Console.WriteLine("Deleting record: ID#{0} {1} {2}", singleItem.Id, singleItem.FirstName, singleItem.LastName);
                    encTable.RemoveItem(singleItem.Id, immediateWriteToTable: true);

                    //Let's see what is actually in the table
                    Console.WriteLine("Records in the table " + encTable.TableName + " - ");
                    sql = "select * from " + encTable.TableName;
                    using (var cmd = new SqliteCommand(sql, dbConn)) {
                        using (SqliteDataReader reader = cmd.ExecuteReader()) {
                            while (reader.Read()) {
                                var record = new StringBuilder();
                                foreach (Portable.Data.Sqlite.TableColumn column in encTable.TableColumns.Values.OrderBy(tc => tc.ColumnOrder)) {
                                    if (column.ColumnName == "Id") { record.Append(column.ColumnName + ": " + reader[column.ColumnName].ToString()); }
                                    else {
                                        record.Append(" - " + column.ColumnName + ": " + (reader[column.ColumnName].ToString() ?? ""));
                                    }
                                }
                                Console.WriteLine(record.ToString());
                            }
                        }
                    }
                }

            }

            #endregion

            Console.WriteLine("Done.");

            //Pop up a message saying we are done
            var dialog = new AlertDialog.Builder(this);
            dialog.SetMessage("If you can see this message, then the sample Portable.Data.Sqlite code ran correctly. " +
                "Take a look at the code in the 'MainActivity.cs' file, and compare it to what you are seeing in the IDE Output window. " +
                "And have a nice day...");
            dialog.SetCancelable(false); 
            dialog.SetPositiveButton("OK", delegate { });
            dialog.Create().Show();

            #endregion


        }
Ejemplo n.º 5
0
        public static List <String> GetData()
        {
            List <String> entries = new List <string>();

            using (SqliteConnection db =
                       new SqliteConnection("Filename=sqliteSample.db"))
            {
                db.Open();

                SqliteCommand selectCommand = new SqliteCommand
                                                  ("SELECT Text_Entry from MyTable", db);

                SqliteDataReader query = selectCommand.ExecuteReader();

                while (query.Read())
                {
                    entries.Add(query.GetString(0));
                }

                db.Close();
            }

            return(entries);
        }
Ejemplo n.º 6
0
        /// <summary>
        /// Get a list of courses with the student's enrollment status.
        /// </summary>
        /// <returns>A list of courses with the student's enrollment status.</returns>
        public List <StudentCourse> GetEnrollment()
        {
            List <StudentCourse> courses = new List <StudentCourse>();

            SqliteConnection conn = DatabaseConnection.GetConnection();

            conn.Open();
            SqliteCommand command = conn.CreateCommand();

            const string enrollmentSqlFormat =
                "SELECT c.ID, c.Name, c.StartDate, c.EndDate, " +
                "CASE WHEN sc.StudentID IS NOT NULL THEN 1 ELSE 0 END " +
                "FROM Courses c " +
                "LEFT JOIN (SELECT * FROM StudentCourses WHERE StudentID = {0}) sc " +
                "ON c.ID = sc.CourseID";

            command.CommandText = String.Format(enrollmentSqlFormat, Id);
            SqliteDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                int      id         = reader.GetInt32(0);
                string   name       = reader.GetString(1);
                DateTime startDate  = reader.GetDateTime(2);
                DateTime endDate    = reader.GetDateTime(3);
                bool     isEnrolled = reader.GetBoolean(4);

                courses.Add(new StudentCourse(id, name, startDate, endDate, Id, isEnrolled));
            }

            // clean up
            reader.Close();
            conn.Close();

            return(courses);
        }
Ejemplo n.º 7
0
    //关闭数据库
    public void Disconnect()
    {
        if (command != null)
        {
            command.Dispose();
        }

        command = null;

        if (dataReader != null)
        {
            dataReader.Dispose();
        }

        dataReader = null;

        if (connection != null)
        {
            connection.Close();
            Debug.Log("Disconnected db.");
        }

        connection = null;
    }
Ejemplo n.º 8
0
        public static List <Vozilo> DohvatiSvaVozila()
        {
            var lista = new List <Vozilo>();

            SqliteCommand c = BazaPodataka.con.CreateCommand();

            c.CommandText = string.Format(@"SELECT id, marka, model, godiste, registracija, tip 
											FROM Vozila"                                            );
            SqliteDataReader reader = c.ExecuteReader();

            while (reader.Read())
            {
                Vozilo auto = new Vozilo();
                auto.id           = (long)reader["id"];
                auto.Marka        = (string)reader["marka"];
                auto.Model        = (string)reader["model"];
                auto.godiste      = (long)reader["godiste"];
                auto.Registracija = (string)reader["registracija"];
                auto.Tip          = Vozilo.GetTipVrsta((long)reader["tip"]);
                lista.Add(auto);
            }
            c.Dispose();
            return(lista);
        }
Ejemplo n.º 9
0
        public DateTime?GetLastUpdatedAt(int companyId)
        {
            using (SqliteConnection db = new SqliteConnection("Filename=SirmiumERPGFC.db"))
            {
                db.Open();
                try
                {
                    SqliteCommand selectCommand = new SqliteCommand("SELECT COUNT(*) from BusinessPartnerTypes WHERE CompanyId = @CompanyId AND IsSynced = 1", db);
                    selectCommand.Parameters.AddWithValue("@CompanyId", companyId);
                    SqliteDataReader query = selectCommand.ExecuteReader();
                    int count = query.Read() ? query.GetInt32(0) : 0;

                    if (count == 0)
                    {
                        return(null);
                    }
                    else
                    {
                        selectCommand = new SqliteCommand("SELECT MAX(UpdatedAt) from BusinessPartnerTypes WHERE CompanyId = @CompanyId AND IsSynced = 1", db);
                        selectCommand.Parameters.AddWithValue("@CompanyId", companyId);
                        query = selectCommand.ExecuteReader();
                        if (query.Read())
                        {
                            int counter = 0;
                            return(SQLiteHelper.GetDateTimeNullable(query, ref counter));
                        }
                    }
                }
                catch (Exception ex)
                {
                    MainWindow.ErrorMessage = ex.Message;
                }
                db.Close();
            }
            return(null);
        }
        private Equipo ParseEquipo(SqliteDataReader reader)
        {
            var Equipo = new Equipo();
            var i      = 0;

            unchecked
            {
                Equipo.ID     = (int)reader.GetInt64(i++);
                Equipo.NOMBRE = reader.GetString(i++);
                Equipo.AULA   = reader.GetString(i++);
                Equipo.AREA   = reader.GetString(i++);
                Equipo.Fecha_adquisicion_hw = reader.GetString(i++);
                Equipo.garantia             = reader.GetString(i++);
                Equipo.ficha_tecnica_hd     = reader.GetString(i++);
                Equipo.num_provee           = (int)reader.GetInt64(i++);
                Equipo.conectividad_red     = reader.GetString(i++);
                Equipo.licencia_hw          = reader.GetString(i++);
                Equipo.TIPO = reader.GetString(i++);



                return(Equipo);
            }
        }
        public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            //No era necesario pero bueno.. por si acaso
            int titulodevolver        = 0;
            SqliteConnection conexion = new SqliteConnection("Data Source=peliculasbase.db");

            conexion.Open();
            SqliteCommand comando = conexion.CreateCommand();

            comando.CommandText = "SELECT idPelicula FROM peliculas  where titulo=@titulo";
            comando.Parameters.Add("@titulo", SqliteType.Text);
            comando.Parameters["@titulo"].Value = value.ToString();
            SqliteDataReader lector = comando.ExecuteReader();

            if (lector.HasRows)
            {
                while (lector.Read())
                {
                    titulodevolver = int.Parse(lector["idPelicula"].ToString());
                }
            }
            conexion.Close();
            return(titulodevolver);
        }
        public String GetAlias(Int32 threadId, String bolded)
        {
            String rc = String.Empty;
            // Check our thread.
            String sql =
                @"
SELECT poster.postername FROM Poster 
JOIN Alias ON (Poster.posterid = Alias.posterid) 
WHERE (threadid = @p1) AND (bolded = @p2)
LIMIT 1;
";
            Stopwatch watch = new Stopwatch();

            watch.Start();
            using (SqliteConnection dbRead = new SqliteConnection(_connect))
            {
                dbRead.Open();
                using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
                {
                    cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
                    cmd.Parameters.Add(new SqliteParameter("@p2", bolded));
                    using (SqliteDataReader r = cmd.ExecuteReader())
                    {
                        if (r.Read())
                        {
                            rc = r.GetString(0);
                        }
                    }
                }
            }
            watch.Stop();
            //Trace.TraceInformation("after GetAliasDB {0}", watch.Elapsed.ToString());

            // Check other threads.
            return(rc);
        }
        public IEnumerable <String> GetLivePlayers(Int32 threadId, Int32 postNumber)
        {
            List <String> players = new List <string>();
            String        sql     =
                @"SELECT Poster.postername
FROM Poster
INNER JOIN Player ON (Poster.posterid = Player.posterid)
INNER JOIN GameRole ON (Player.roleid = GameRole.roleid)
WHERE (GameRole.threadid = @p1)
AND ((Player.endtime IS NULL)
OR (Player.endtime > @p2))
ORDER BY Poster.postername ASC;";
            Stopwatch watch = new Stopwatch();

            watch.Start();
            using (SqliteConnection dbRead = new SqliteConnection(_connect))
            {
                dbRead.Open();
                using (SqliteCommand cmd = new SqliteCommand(sql, dbRead))
                {
                    cmd.Parameters.Add(new SqliteParameter("@p1", threadId));
                    cmd.Parameters.Add(new SqliteParameter("@p2", postNumber));
                    using (SqliteDataReader r = cmd.ExecuteReader())
                    {
                        while (r.Read())
                        {
                            String player = r.GetString(0);
                            players.Add(player);
                        }
                    }
                }
            }
            watch.Stop();
            //Trace.TraceInformation("after GetPlayerList {0}", watch.Elapsed.ToString());
            return(players);
        }
Ejemplo n.º 14
0
    GetSQliteData getSQliteData;//连接数据库类的引用



    /// <summary>
    /// 给小怪模型类赋值
    /// </summary>
    /// <param name="conditon">小怪等级</param>
    /// <param name="value">值</param>
    /// <returns></returns>
    public LowMonsterModel LowMonster(string conditon, string value)
    {
        getSQliteData.OpenDB("Data Source=" + Path.Combine(Application.persistentDataPath, ConstData.dataBase)); //根据路径打开数据库
        LowMonsterModel  lowMonster = new LowMonsterModel();                                                     //创建小怪模型类对象
        SqliteDataReader reader     = getSQliteData.GetDataReader(ConstData.T_lowMonster, conditon, value);

        //读取数据
        while (reader.Read())
        {
            lowMonster.Level         = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.level)));
            lowMonster.EmpiricValue  = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.empiricValue)));
            lowMonster.Name          = reader.GetString(reader.GetOrdinal(ConstMonsterData.name));
            lowMonster.Hp            = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.hp)));
            lowMonster.PhysicsAttack = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.physicsAttack)));
            lowMonster.MagicAttack   = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.magicAttack)));
            lowMonster.Armor         = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.armor)));
            lowMonster.MagicResist   = int.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.magicResist)));
            lowMonster.AttackRate    = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.attackRate)));
            lowMonster.RecoverRate   = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.recoverRate)));
            lowMonster.Speed         = float.Parse(reader.GetString(reader.GetOrdinal(ConstMonsterData.speed)));
        }
        getSQliteData.CloseDB();//关闭数据库
        return(lowMonster);
    }
Ejemplo n.º 15
0
        public static Dictionary <string, Dictionary <string, string> > GetData()
        {
            Dictionary <string, Dictionary <string, string> > allData;
            Dictionary <string, string> hashDict = new Dictionary <string, string>()
            {
                { "DestinyInventoryItemDefinition", "itemHash" }
            };

            using (SqliteConnection db =
                       new SqliteConnection("Filename=Manifest.content"))
            {
                db.Open();

                allData = new Dictionary <string, Dictionary <string, string> >();
                foreach (string tableName in hashDict.Keys)
                {
                    SqliteCommand    selectCommand = new SqliteCommand("SELECT json from " + tableName, db);
                    SqliteDataReader query         = selectCommand.ExecuteReader();
                    while (query.Read())
                    {
                        if (tableName.Equals("DestinyInventoryItemDefinition"))
                        {
                            if (!allData.Keys.Contains("DestinyInventoryItemDefinition"))
                            {
                                allData.Add("DestinyInventoryItemDefinition", new Dictionary <string, string>());
                            }
                            string json = query.GetString(0);
                            allData["DestinyInventoryItemDefinition"].Add(JsonConvert.DeserializeObject <DestinyInventoryItemRootObject>(json).hash + "", json);
                        }
                    }
                }

                db.Close();
            }
            return(allData);
        }
Ejemplo n.º 16
0
            /// <summary>Selects all projects.</summary>
            /// <returns>All projects.</returns>
            public List <Project> SelectProjects()
            {
                List <Project> Projects = new List <Project>();

                string sql = "SELECT * FROM Projects ORDER BY ProjectId ASC; ";

                using (SqliteCommand cmd = new SqliteCommand(sql, Connection))
                {
                    using (SqliteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Project p = new Project();
                            p.ProjectId   = (long)rdr["ProjectId"];
                            p.Name        = rdr["Name"].ToString();
                            p.Description = rdr["Description"].ToString();
                            p.Created     = DateTime.Parse((string)rdr["Created"]);
                            Projects.Add(p);
                        }
                    }
                }

                return(Projects);
            }
Ejemplo n.º 17
0
            /// <summary>Selects project by the name.</summary>
            /// <returns>Project with given name.</returns>
            /// <param name="name">Name of the project.</param>
            public Project[] SelectProjectByName(string name)
            {
                Project[] Ret = new Project[1];

                string sql = "SELECT * FROM Projects WHERE Name = '" + name + "' LIMIT 1; ";

                using (SqliteCommand cmd = new SqliteCommand(sql, Connection))
                {
                    using (SqliteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Project p = new Project();
                            p.ProjectId   = (long)rdr["ProjectId"];
                            p.Name        = rdr["Name"].ToString();
                            p.Description = rdr["Description"].ToString();
                            p.Created     = DateTime.Parse((string)rdr["Created"]);
                            Ret[0]        = p;
                        }
                    }
                }

                return(Ret);
            }
        public static List <LeaderBoard> GetUsersNotOnLeaderBoard(string dataSource)
        {
            List <LeaderBoard> leaderBoard = new List <LeaderBoard>();

            using (SqliteConnection conn = new SqliteConnection(dataSource))
            {
                conn.Open();

                SqliteCommand cmd = new SqliteCommand("SELECT * FROM Checkin WHERE UserName NOT IN (SELECT DISTINCT Username FROM LeaderBoard)", conn);

                SqliteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var leaderBoardRow = new LeaderBoard
                    {
                        Username = rdr["Username"] == DBNull.Value ? "" : (string)rdr["Username"]
                    };

                    leaderBoard.Add(leaderBoardRow);
                }
            }

            return(leaderBoard);
        }
Ejemplo n.º 19
0
        private List <Reservation> GetAllRows(SqliteDataReader reader)
        {
            List <Reservation> result = new List <Reservation>();

            while (reader.HasRows)
            {
                while (reader.Read())
                {
                    result.Add(new Reservation
                    {
                        reservation_id   = reader.GetInt32(0),
                        reservation_date = DateTime.Parse(reader.GetString(1)),
                        reservation_time = TimeSpan.Parse(reader.GetString(2)),
                        client_id        = reader.GetInt32(3),
                        services_id      = reader.GetInt32(4),
                        employee_id      = reader.GetInt32(5)
                    });
                }
                reader.NextResult();
            }


            return(result);
        }
Ejemplo n.º 20
0
        protected override void ParseData(SqliteDataReader reader)
        {
            mInfos.Clear();

            DBFightEfffectLayoutItem info;

            if (reader != null)
            {
                if (reader.HasRows == true)
                {
                    while (reader.Read())
                    {
                        info = new DBFightEfffectLayoutItem();
                        info.FightTypeStr = GetReaderString(reader, "type");
                        info.Level        = DBTextResource.ParseI_s(GetReaderString(reader, "level"), 0);

                        if (info.FightTypeStr != null && mInfos.ContainsKey(info.FightTypeStr) == false)
                        {
                            mInfos[info.FightTypeStr] = info;
                        }
                    }
                }
            }
        }
Ejemplo n.º 21
0
        // Funkcja dodaje zeskanowany produkt do listy produktów
        public bool add_product_to_list(string RFID, bool storno)
        {
            // Wyszukanie w bazie danych produktu oznaczonego zeskanowanym tagiem RFID
            // Zapytanie SQL:
            string sql = "select * from Produkty where RFID like '%" + RFID + "%'";

            // Umieszczenie zapytania SQL w komedzie SQLite
            SqliteCommand command = new SqliteCommand(sql, db);

            // Wykonanie zapytania na bazie danych
            SqliteDataReader reader = command.ExecuteReader();

            // Odczyt rezultatu zapytania z bazy danych
            if (reader.Read())
            {
                string prefix = "";
                if (storno)
                {
                    prefix = "-";
                }

                // Utworzenie obiektu pojedynczego produktu na podstawie dancy z bazy danych
                Produkt p = new Produkt(reader["Name"].ToString(), prefix + reader["Price"].ToString(), reader["RFID"].ToString(), reader["VAT"].ToString());

                // Dodanie produktu do listy zakupów
                zakupy.Add(p);

                // Zwraca true jeżeli się powiodło
                return(true);
            }
            else
            {
                // Zwraca false, jeśli się nie powiodło
                return(false);
            }
        }
Ejemplo n.º 22
0
 private void OnGUI()
 {
     if (GUILayout.Button("Insert 10W"))
     {
         StrayFogSQLiteHelper helper = new StrayFogSQLiteHelper(mDbPath);
         while (mInsertNum > 0)
         {
             helper.ExecuteQuery("INSERT INTO AsmdefMap VALUES (1,'AAAAAAAA','BBBBBBBB','CCCCCCC','DDDDDD','EEEEEE',0)");
             mInsertNum--;
         }
     }
     GUILayout.Label("Insert Num =>" + mInsertNum);
     if (GUILayout.Button("Read SQLite"))
     {
         watch.Reset();
         watch.Start();
         StrayFogSQLiteHelper helper = new StrayFogSQLiteHelper(mDbPath);
         SqliteDataReader     reader = helper.ReadFullTable("AsmdefMap");
         while (reader.Read())
         {
             for (int i = 0; i < reader.FieldCount; i++)
             {
                 sbTableColumn.Append(reader.GetName(i) + ",");
                 sbTableValue.Append(reader.GetValue(i).ToString());
             }
         }
         reader.Close();
         watch.Stop();
     }
     GUILayout.Label("Time=>" + watch.ElapsedMilliseconds);
     mScrollViewPosition = GUILayout.BeginScrollView(mScrollViewPosition);
     GUILayout.Label("Connect String=>" + mDbPath);
     GUILayout.Label("Table Columns=>" + sbTableColumn.ToString());
     GUILayout.Label("Table Values=>" + sbTableValue.ToString());
     GUILayout.EndScrollView();
 }
Ejemplo n.º 23
0
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void CloseConnection()
    {
        //销毁Command
        if (dbCommand != null)
        {
            dbCommand.Cancel();
        }
        dbCommand = null;

        //销毁Reader
        if (dataReader != null)
        {
            dataReader.Close();
        }
        dataReader = null;

        //销毁Connection
        if (dbConnection != null)
        {
            dbConnection.Close();
            dbConnection.Dispose();
        }
        dbConnection = null;
    }
Ejemplo n.º 24
0
        public IActionResult Query(string name)
        {
            if (String.IsNullOrEmpty(name))
            {
                ViewData["Error"] = "Name can't be empty";
                return(View());
            }

            ViewData["Query"] = name;

            List <Employee> employees = new List <Employee>();
            // string queryString = "SELECT firstName, lastName, email FROM Employees WHERE firstName like '" + name + "%' or lastName like '" + name + "%' AND employeeNumber < 10000 LIMIT 5";
            var queryString             = "SELECT firstName, lastName, email FROM Employees WHERE firstName like '@name%' or lastName like '@name%' AND employeeNumber < 10000 LIMIT 5";
            SqliteConnection connection = new SqliteConnection("Data Source=db.sqlite3");

            using (SqliteCommand cmd = new SqliteCommand(queryString, connection))
            {
                connection.Open();
                var sqlLiteParam = new SqliteParameter("@name", SqliteType.Text)
                {
                    Value = name
                };
                cmd.Parameters.Add(sqlLiteParam);


                using (SqliteDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        employees.Add(new Employee(dr.GetString(0), dr.GetString(1), dr.GetString(2)));
                    }
                }
            }

            return(View(employees));
        }
Ejemplo n.º 25
0
 public UploadEntry GetNextUploadEntryToUpload()
 {
     using (SqliteConnection conn = new SqliteConnection(_connectionString))
     {
         conn.Open();
         using (SqliteCommand cmd = conn.CreateCommand())
         {
             cmd.CommandText = @"SELECT ROWID, * from UploadEntries 
                                 WHERE UploadedAt IS NULL 
                                   AND Cancelled = 0
                                 ORDER BY PriorityNum DESC, CreatedAt ASC
                                 LIMIT 1";
             using (SqliteDataReader reader = cmd.ExecuteReader())
             {
                 UploadEntry uploadEntry = null;
                 if (reader.Read())
                 {
                     uploadEntry = GetUploadEntryFromReader(reader);
                 }
                 return(uploadEntry);
             }
         }
     }
 }
Ejemplo n.º 26
0
    void OnClick()
    {
        Debug.Log("Button Clicked!");
        // 数据库文件路径
        //string appDBPath = Application.persistentDataPath + "/test.db";
        string appDBPath = Application.dataPath + "/test.db";

        DbAccess db = new DbAccess(@"Data Source=" + appDBPath);

        path = appDBPath;

        // 创建test表
        db.CreateTable("test", new string[] { "name", "qq", "mail", "www" }, new string[] { "text", "text", "text", "text" });
        // 插入数据,插入字符串加上'' 不然会报错
        db.InsertInto("test", new string[] { "'哈哈哈'", "'1213213213'", "'*****@*****.**'", "'www.jianshu.com'" });
        db.InsertInto("test", new string[] { "'嘿嘿嘿'", "'1241124214'", "'*****@*****.**'", "'www.csdn.com'" });
        db.InsertInto("test", new string[] { "'嘻嘻嘻'", "'1235667767'", "'*****@*****.**'", "'www.manew.com'" });

        // 删掉数据
        db.Delete("test", new string[] { "mail", "mail" }, new string[] { "'*****@*****.**'", "'*****@*****.**'" });

        // 查询数据
        using (SqliteDataReader sqReader = db.SelectWhere("test", new string[] { "name", "mail" }, new string[] { "qq" }, new string[] { "=" }, new string[] { "1235667767" })) {
            while (sqReader.Read())
            {
                //目前中文无法显示
                Debug.Log("name:" + sqReader.GetString(sqReader.GetOrdinal("name")));
                Debug.Log("mail:" + sqReader.GetString(sqReader.GetOrdinal("mail")));
                name = sqReader.GetString(sqReader.GetOrdinal("name"));
                mail = sqReader.GetString(sqReader.GetOrdinal("mail"));
            }
            sqReader.Close();
        }

        db.CloseSqlConnection();
    }
Ejemplo n.º 27
0
        public static Vozilo DohvatiPoRegistraciji(string registracija)
        {
            SqliteCommand c = BazaPodataka.con.CreateCommand();

            c.CommandText = string.Format(@"SELECT id, marka, model, godiste, registracija, tip 
											FROM Vozila 
											WHERE registracija = '{0}'"                                            , registracija);

            SqliteDataReader reader = c.ExecuteReader();

            Vozilo auto = new Vozilo();

            while (reader.Read())
            {
                auto.id           = (long)reader["id"];
                auto.Marka        = (string)reader["marka"];
                auto.Model        = (string)reader["model"];
                auto.godiste      = (long)reader["godiste"];
                auto.Registracija = (string)reader["registracija"];
                auto.Tip          = Vozilo.GetTipVrsta((long)reader["tip"]);
            }
            c.Dispose();
            return(auto);
        }
Ejemplo n.º 28
0
    /// <summary>
    /// This will grab all the urdf types that is known in the database
    /// </summary>
    /// <returns>A list of type of urdf in the database.</returns>
    public List <UrdfTypeModel> GetUrdfTypes()
    {
        List <UrdfTypeModel> list = new List <UrdfTypeModel>();

        string        sql = "SELECT `uid`, `name` FROM `tblUrdfType`;";
        SqliteCommand cmd = _engine.conn.CreateCommand();

        cmd.CommandText = sql;
        SqliteDataReader reader = cmd.ExecuteReader();
        UrdfTypeModel    item;

        try
        {
            while (reader.Read())
            {
                item      = new UrdfTypeModel();
                item.uid  = reader.GetInt32(0);
                item.name = reader.GetString(1);

                list.Add(item);
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }
        finally
        {
            reader.Close();
            reader = null;
            cmd.Dispose();
            cmd = null;
        }

        return(list);
    }
Ejemplo n.º 29
0
        //public static List<String> GetData()
        public static ArrayList GetData() // Return List to ArrayList Using Collection for show MessageBox
        {
            //List<String> entries = new List<string>();
            ArrayList entries = new ArrayList();

            using (SqliteConnection db =
                       new SqliteConnection($"Filename=sqliteSample.db"))
            {
                db.Open();

                SqliteCommand selectCommand = new SqliteCommand
                                                  ("SELECT CustID, first_Name, last_Name, email from MyCustomers", db);

                SqliteDataReader query = selectCommand.ExecuteReader();

                while (query.Read())
                {
                    entries.Add(query.GetString(0));
                    entries.Add("Name " + query.GetString(1) + "Last Name " + query.GetString(2) + "Email " + query.GetString(3));
                }
                db.Close();
            }
            return(entries);
        }
Ejemplo n.º 30
0
        // for saving the schedule to user //
        public void ReturnLatestSchedule()
        {
            Schedules.Clear();
            using (SqliteConnection con = new SqliteConnection(cs))
            {
                con.Open();

                string sql = "SELECT * FROM CameraSchedule WHERE GhostID ='" + GhostID + "' ORDER BY CameraScheduleID desc LIMIT 1";
                using (SqliteCommand cmd = new SqliteCommand(sql, con))
                {
                    SqliteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Schedules.Add(Convert.ToString(reader.GetValue(i)));
                        }
                    }
                    reader.Close();
                }
                con.Close();
                UpdateSchedules();
            }
        }
Ejemplo n.º 31
0
        protected override void ParseData(SqliteDataReader reader)
        {
            if (reader == null || !reader.HasRows)
            {
                return;
            }

            m_WidgetInfos.Clear();
            while (reader.Read())
            {
                var id       = DBTextResource.ParseUI(GetReaderString(reader, "id"));
                var window   = GetReaderString(reader, "window");
                var path     = GetReaderString(reader, "path");
                var is_audit = DBTextResource.ParseUI(GetReaderString(reader, "is_audit"));

                if (m_WidgetInfos.ContainsKey(window))
                {
                    var        list = m_WidgetInfos[window];
                    WidgetInfo info = new WidgetInfo();
                    info.path     = path;
                    info.is_audit = is_audit == 1;
                    info.id       = id;
                    list.Add(info);
                }
                else
                {
                    var        list = new List <WidgetInfo>();
                    WidgetInfo info = new WidgetInfo();
                    info.path     = path;
                    info.is_audit = is_audit == 1;
                    info.id       = id;
                    list.Add(info);
                    m_WidgetInfos[window] = list;
                }
            }
        }
Ejemplo n.º 32
0
        public List <Zone> GetPartitionsZones(int PartitionId)
        {
            List <Zone> result = new List <Zone>();

            try{
                connection.Open();
                using (var c = connection.CreateCommand()) {
                    c.CommandText = "Select * from Zone where PartitionId=@PartitionId";
                    c.CommandType = CommandType.Text;
                    c.Parameters.AddWithValue("@PartitionId", PartitionId);
                    SqliteDataReader reader = c.ExecuteReader();

                    while (reader.Read())
                    {
                        result.Add(MapZoneFromReader(reader));
                    }
                }
            }
            catch (Exception) {}
            finally{
                connection.Close();
            }
            return(result);
        }
Ejemplo n.º 33
0
        public static string SQLiteGet(string sql)
        {
            string outstr = "";

            using (SqliteConnection con = new SqliteConnection("DataSource=" + JustTokenClass.SQLiteDBPatch + ";"))
            {
                con.Open();

                using (SqliteCommand cmd = new SqliteCommand(sql, con))
                {
                    using (SqliteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            outstr += rdr.GetString(0);
                        }
                    }
                }

                con.Close();
            }

            return(outstr);
        }
Ejemplo n.º 34
0
        public List <string> ReturnGhostNames()
        {
            using (SqliteConnection con = new SqliteConnection(cs))
            {
                con.Open();

                string sql = "SELECT gt.Name FROM GhostType as gt";
                using (SqliteCommand cmd = new SqliteCommand(sql, con))
                {
                    SqliteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            GhostNames.Add(Convert.ToString(reader.GetValue(i)));
                        }
                    }
                    reader.Close();
                }
                con.Close();

                return(GhostNames);
            }
        }