How to save values in database using Sqlite?

Question

I am trying to do a tracker application in wp8. I want to save the values in database using Sqlite. All the values r working (time, distance and pace). After pressing Stop button, I want the values to be posted in a table view using Sqlite as u can see in the second screen. Any good suggestions or links are appreciated. Thank u.

Answer 1

I'm assuming your table is.

    public class HistoryTable
    {
        public string date { get; set; }
        public string time { get; set; }
        public double pace { get; set; }
        public double distance { get; set; }
    }

Insert values using this statement.

    string date = DateTime.Now.ToShortDateString();
    string time = DateTime.Now.ToShortTimeString();
    double pace = 16;
    double distance = 4;
    SQLiteConnection conn = new SQLiteConnection(System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Database.sqlite"));
    conn.Execute("Insert into HistoryTable values(?,?,?,?)", date, time, pace, distance);

Fetch your data as below statement, I'm assuming that you know how to bind the data in listbox if there is need. I'm taking the values in textbox.

    SQLiteConnection conn = new SQLiteConnection(System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Database.sqlite"));
    var result = conn.Table<HistoryTable>();
    foreach (var val in result)
    {
        TimeSpan pace = TimeSpan.FromMinutes(val.pace);
        TextBoxDate.Text = val.date;
        TextBoxTime.Text = val.time;
        TextBoxPace.Text = pace.Minutes + ":" + pace.Seconds;
        TextBoxDistance.Text = val.distance + " Km";
    }

The reason why I used Pace as double because I can use this double value as total minutes and can be changed as timespan(in minutes and seconds). For any other queries you can ask any time. To get exactly the same format as you ask in your question you should use like this also.

    string date = string.Format("{0:00}.{1:00}.{2:00}", DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year);
    string time = DateTime.Now.ToString("HH:mm:ss");
    double pace = 16.5;
    double distance = 4;
    SQLiteConnection conn = new SQLiteConnection(System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Database.sqlite"));
    conn.Execute("Insert into HistoryTable values(?,?,?,?)", date, time, pace, distance);

At the time of fetching info, please change the above steps by this.

    TextBoxDistance.Text = string.Format("{0:0.00}Km", val.distance);

Answer 2

Try this nokia developer site here.

Gives you a small tutorial how to use sqlite on windows phones.

This piece of code gives you the answer?

 private void Insert_Click_1(object sender, RoutedEventArgs e)
        {
            // Create a new task.
            Task task = new Task()
            {
                Title = TitleField.Text,
                Text = TextField.Text,
                CreationDate = DateTime.Now
            };
            /// Insert the new task in the Task table.
            dbConn.Insert(task);
            /// Retrieve the task list from the database.
            List<Task> retrievedTasks = dbConn.Table<Task>().ToList<Task>();
            /// Clear the list box that will show all the tasks.
            TaskListBox.Items.Clear();
            foreach (var t in retrievedTasks)
            {
                TaskListBox.Items.Add(t);
            }
        }

hm, i see this is a retrieval piece of code. Maybee this site helps you further.

The following example is an insert:

public void Initialize()
{
   using ( var db = new SQLite.SQLiteConnection( _dbPath ) )
   {
      db.CreateTable<Customer>();

      //Note: This is a simplistic initialization scenario
      if ( db.ExecuteScalar<int>( 
             "select count(1) from Customer" ) == 0 )
      {

         db.RunInTransaction( () =>
         {
            db.Insert( new Customer() { 
               FirstName = "Jon", LastName = "Galloway" } );
            db.Insert( new Customer() { 
               FirstName = "Jesse", LastName = "Liberty" } );
         } );
      }
      else
      {
         Load();
      }
   }
}