Windows store apps - Storing DateTime.Now into Sqlite database

Question

I read the DateTimeNow section, on how to insert DateTime.Now objects into my Sqlite Database from the sqlite website.

I still do not understand how i can store DateTime into the sqlite database and convert it back to DateTime.Now to use in my app. 

Could someone explain how to do this?

PAGE:  http://www.sqlite.org/datatype3.html

 

Answer 1

I cant help specifically with SQLite, but you can convert a DateTime object to and from the ISO8601 format with the Round-trip ("O", "o") Format Specifier as discussed in the Standard Date and Time Format Strings documentation.

There are code snippets in that topic demonstrating using DateTime.ToString("o") to convert to a standard string and DateTime.Parse(dateString, null, DateTimeStyles.RoundtripKind) to convert back from a string.

Note that the snippets target a desktop console app (i.e. they use the Console class), but the DateTime handling in them is valid for Windows Store apps.

--Rob

Answer 2

Hi ,

I'm not sure that I have completely understood your question but I will give it a try.

In  my app I have this table

  public class tbTodoList
        {
            [PrimaryKey, AutoIncrement]
            public Int32 Auto_Sequence { get; set; }
                [MaxLength(50)]
            public string Title { get; set; }
            [MaxLength(4000)]
            public string Comments { get; set; }
            public DateTime DateCreated { get; set; }
            [MaxLength(50)]
            public string Status { get; set; }
        }

I can use it as a class in my XAML page.

I can insert in my db

               

  var db = new SQLite.SQLiteConnection(this.dbpath);

                string display = "";
           FromDate=Datetime.Now;

                db.RunInTransaction(() =>
                {
                    db.Insert(new MyApp.App.tbTodoList()
                  {
                        
                       DateCreated = FromDate,
                       Comments = this.txtComments.Text,
                     Title = this.txtTitle.Text,
                       Status = "Pending"
                    });

                });

db.dispose();

I can delete

object[] args1 = { MyAuto_Sequence.ToString() };

 db.Execute("delete from tbTodoList where auto_sequence = ? ", args1);

I can get the values

using this sql statement

string sql_statement="";

sql_statement = "Select * from tbtodolist  where   Datetime(DateCreated) >= '1953-09-16 12:00:00' "

  db.RunInTransaction(() =>
                {
                    MySearchResults = null;
                    MySearchResults = db.Query<Myapp.App.tbtodolist>(sql_statement);


                });
                db.Dispose();

Where the mysearchresults is the App.tbTodolist class.

I was having difficulties to the the dates but then I figured it out.

That I must put the Datetime() in the sql_statement and the date I want to use into single quottes.

Don't ask me why but it worked.

I hope I helped you or gave you something to start.

thank you