locked
using SQLite to find information in windows 8 store app

    Question

  • Hi

    Is there a sample on how to search a SQLite database and obtain a xaml page with the results of the query?

    The xaml page should present as results, images and texts.

    Also, I would like to know how to store inside an SQLite database the paths for images inside my windows 8 store app.

    is this possible?

    Thursday, March 19, 2015 5:29 PM

Answers

  • You can query a SQLite database and return the results using the SQLitePCL library by Microsoft Open Technologies team. You can't return a XAML page but rather a list containing the results from the query. Take some time and review the SQLitePCL documentation.

    With the SQLitePCL library, you can easily insert, select, delete and update the data in the database. Storing the path to your images can be done by using an insert statement. Review the SQLite documentation on how to write your SQL queries and take note of the supported data types. 

    Below is a few examples on working with SQLitePCL to manipulate your SQLite database:

    //add the SQLitePCL namespace
    using SQLitePCL;
    
    
    public static SQLitePCL.SQLiteConnection HCSConn;
    
    //Create the database
    HCSConn = new SQLitePCL.SQLiteConnection("MySQLiteDatabase.db", SQLitePCL.SQLiteOpen.READWRITE);     
    
    //Create the MyUser table       
    string sql = @"CREATE TABLE IF NOT EXISTS MyUser
          (   
               Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    	   UserName VARCHAR(255),
    	   FirstName VARCHAR(255),
    	   LastName VARCHAR(255)
          );";
                
    using (var statement = HCSConn.Prepare(sql))
    {
         statement.Step();
    }
    
    
    
    
    
    //Method to fetch user by userName
    public static List<MyUser> GetUserByName(string userName)
            {
                HCSConn = new SQLitePCL.SQLiteConnection("MySQLiteDatabase.db", SQLitePCL.SQLiteOpen.READWRITE);
    
                List<MyUser> myUsers = null;
                string sql = @"Select FirstName, LastName, UserName
                                FROM MyUser
                                Where UserName = ? ";
    
                using (var stmt = HCSConn.Prepare(sql))
                {
                    stmt.Bind(1, userName);
    
                    SQLiteResult result = stmt.Step();
    
                    while(result == SQLiteResult.ROW && stmt[0] != null)
                    {
                        MyUser myUser = new MyUser()
                        {
                            FirstName = (string)stmt[0],
                            LastName = (string)stmt[1],
                            UserName = (string)stmt[2]
                        };
    
                        //Store result in a list
                        myUsers.Add(myUser);
    
                        stmt.Step();
                    }
                }
    
                return
    }
    
    
    //create a new user
    private static void InsertUSer(MyUser user)
            {
                HCSConn = new SQLitePCL.SQLiteConnection("MySQLiteDatabase.db", SQLitePCL.SQLiteOpen.READWRITE);
                
                string sql = @"INSERT INTO MyUser (FirstName, LastName, UserName)
                                    VALUES (?, ?, ?)";
    
                using (var statement = HCSConn.Prepare(sql))
                {
                    statement.Bind(1, user.FirstName);
                    statement.Bind(2, user.LastName);
                    statement.Bind(3, user.UserName);
    
                    statement.Step();
                }
            }

    Hope this give you a good start.


    Abdulwahab Suleiman


    Thursday, March 19, 2015 9:29 PM
    Moderator