How to save date, datetime and time to SQLite Database in Xamarin Forms?

Question

I want to insert a Date, DateTime(without seconds) and Time to my SQLite database. I can insert the data but the information is wrong. The variable that holds the data is correct but when the data is inserted to the database the information is wrong.

I have this table:

[Table("tblCaf")]
public class CAFTable
{
[PrimaryKey, MaxLength(100)]
public string CAFNo { get; set; }
public int EmployeeID { get; set; }
public DateTime CAFDate { get; set; }
[MaxLength(100)]
public string CustomerID { get; set; }
public DateTime StartTime { get; set; }
public DateTime EndTime { get; set; }
public string Photo1 { get; set; }
public string Photo2 { get; set; }
public string Photo3 { get; set; }
public string MobilePhoto1 { get; set; }
public string MobilePhoto2 { get; set; }
public string MobilePhoto3 { get; set; }
[MaxLength(1000)]
public string Remarks { get; set; }
[MaxLength(1000)]
public string OtherConcern { get; set; }
public DateTime LastSync { get; set; }
public DateTime ServerUpdate { get; set; }
public DateTime MobileUpdate { get; set; }
}

This is my code:

var caf = entCafNo.Text;
var retailerCode = entRetailerCode.Text;
var employeeNumber = entEmployeeNumber.Text;
var date = dpDate.Date; //I get the date inside the datepicker
var startTime = tpTime.Time; //I get the time inside the time picker
var endTime = DateTime.Now.TimeOfDay; //I get the current time
var photo1url = entPhoto1Url.Text;
var photo2url = entPhoto2Url.Text;
var photo3url = entPhoto3Url.Text;
var otherconcern = entOthers.Text;
var remarks = entRemarks.Text;
var current_datetime = DateTime.Now.ToString("yyyy-MM-dd hh:mm"); //I get the current datetime

string caf_sql = "INSERT INTO tblCaf(CAFNo, EmployeeID, CafDate, CustomerID, StartTime, EndTime, Photo1, Photo2, Photo3, Remarks, OtherConcern, LastSync, MobileUpdate) 
VALUES('" + caf + "','" + employeeNumber + "', '" + date + "', '" + retailerCode + "', '" + startTime + "', '" + endTime + "', '" + photo1url + "', '" + photo2url + "', '" + photo3url + "', '" + remarks + "', '" + otherconcern + "', '" + current_datetime + "', '" + current_datetime + "')";

await conn.ExecuteAsync(caf_sql);

I can get the right date, time and datetime. The problem is when I save this the date became 01/01/0001, the time became 00:00:00 and the datetime became 01/01/0001 00:00:00 in other words the data is not adding correctly. What can I improve to my code?

is there a reason you're doing an insert query instead of using the Insert method? — Jason, Sep 23 '18 at 01:12
await conn.InsertAsync(obj); where obj is a CAFTable - see the docs: https://github.com/praeclarum/sqlite-net — Jason, Sep 23 '18 at 04:27
You are also using TimeSpan objects for times and inserting them into DateTime fields, which will not work — Jason, Sep 23 '18 at 04:27
@Jason I don't understand can you show me a code to improve my code? — loot verge, Sep 23 '18 at 04:52

score 2 · Accepted Answer · answered Sep 23 '18 at 05:04

2

instead of manually building an insert statement, you'll probably get better results by using the Insert function

var item = new CAFTable {
  CAFDate = dpDate.Date,
  StartTime = tpTime.Date, // note Time is a TimeSpan, not a DateTime
  EndTime = DateTime.Now, // note TimeOFDay is a TimeSpan, not a DateTime
  LastSync = DateTime.Now,
  MobileUpdate = DateTime.Now
  ... set any other properties as needed
};

await conn.InsertAsync(item);

answered Sep 23 '18 at 05:04

Jason

86,222
15
131
146

so i need change the data type of StartTime and EndTime to Timespan inorder for tpTime.Time to work? – loot verge Sep 23 '18 at 14:02
How about the datetime? The LastSync's format is for example **2018-08-21 12:30:00** is **var current_datetime = DateTime.Now.ToString("yyyy-MM-dd hh:mm");** ok? – loot verge Sep 23 '18 at 14:05
1

it is already defined as a DateTime in your DB, why would you convert it to string first? Just store it as a DateTime. – Jason Sep 23 '18 at 14:06
So I declare it like this **"var current_datetime = DateTime.Now("yyyy-MM-dd hh:mm");"** – loot verge Sep 23 '18 at 14:07
2

just DateTime.Now; – Jason Sep 23 '18 at 14:12
ok thank you, last question i need change the data type of StartTime and EndTime to Timespan inorder for tpTime.Time to work? – loot verge Sep 23 '18 at 14:13
SQLite doesn't support TimeSpan. I would either store it as a DateTime and ignore the Date portion in your code, or store it as an int representing the minutes since midnight (or something like that) – Jason Sep 23 '18 at 14:18
how can i ignore the date portion of the date time? – loot verge Sep 23 '18 at 14:41
1

https://stackoverflow.com/questions/1026841/how-to-get-only-time-from-date-time-c-sharp – Jason Sep 23 '18 at 14:57

How to save date, datetime and time to SQLite Database in Xamarin Forms?

1 Answers1