public InstaUser GetInstaUser(InstaUser IU)
{
C_voice_core.speak("database GetInstaUser");
try
{
using (SQLiteConnection _conn = new SQLiteConnection(SQLiteConnString, true))
{
// IU passed in is just a username, populate all other fields then return
using (SQLiteCommand SQLcommand = new SQLiteCommand("select * from insta_users WHERE username = @username);", _conn))
{
SQLcommand.Parameters.AddWithValue("username", IU.username);
if (_conn.State != System.Data.ConnectionState.Open)
{
_conn.Open();
}
using (SQLiteDataReader rdr = SQLcommand.ExecuteReader())
{
if (rdr.Read()) // there can only ever be 1 row
{
if (DateTime.TryParse(rdr["date_created"].ToString(), out DateTime _date_created))
{
IU.date_created = _date_created;
}
if (DateTime.TryParse(rdr["date_followed_them"].ToString(), out DateTime _date_followed_them))
{
IU.date_followed_them = _date_followed_them;
}
if (DateTime.TryParse(rdr["date_followed_back_detected"].ToString(), out DateTime _date_followed_back_detected))
{
IU.date_followed_back_detected = _date_followed_back_detected;
}
if (DateTime.TryParse(rdr["date_last_commented"].ToString(), out DateTime _date_last_commented))
{
IU.date_last_commented = _date_last_commented;
}
if (DateTime.TryParse(rdr["date_last_liked"].ToString(), out DateTime _date_last_liked))
{
IU.date_last_liked = _date_last_liked;
}
}
else
{
IU.error = "no record for user";
C_voice_core.speak(IU.error);
}
_conn.Close();
}
}
}
}
catch (InvalidOperationException se)
{
System.Windows.Forms.MessageBox.Show($"SQL Error: {se.Message}");
}
return(IU);
}
// REMEBER spaces at end of each line in SQL to avoid SQL error
public bool SaveInstaUser(InstaUser IU)
{
// start by calling AddInstaUser,which will create it if not exists
AddInstaUser(IU);
try
{
using (SQLiteConnection _conn = new SQLiteConnection(SQLiteConnString, true))
{
// C_voice_core.speak("db SaveInstaUser");
// now we know they exist, we can update all other fields
using (SQLiteCommand SQLcommand = new SQLiteCommand("update insta_users set " +
"date_followed_them = (case when @date_followed_them = @SQLiteNullDateString then date_followed_them else @date_followed_them end), " +
"date_unfollowed_them = (case when @date_unfollowed_them = @SQLiteNullDateString then date_unfollowed_them else @date_unfollowed_them end), " +
"date_followed_back_detected = (case when @date_followed_back_detected = @SQLiteNullDateString then date_followed_back_detected else @date_followed_back_detected end), " +
"date_last_commented = (case when @date_last_commented = @SQLiteNullDateString then date_last_commented else @date_last_commented end), " +
"date_last_liked = (case when @date_last_liked = @SQLiteNullDateString then date_last_liked else @date_last_liked end), " +
"date_last_updated = @date_last_updated " +
"where username=@username " +
"", _conn))
{
SQLcommand.Parameters.AddWithValue("username", IU.username);
SQLcommand.Parameters.AddWithValue("date_followed_them", IU.date_followed_them != null ? IU.date_followed_them.ToString(SQLiteDateTimeFormat) : "");
SQLcommand.Parameters.AddWithValue("date_unfollowed_them", IU.date_unfollowed != null ? IU.date_unfollowed.ToString(SQLiteDateTimeFormat) : "");
SQLcommand.Parameters.AddWithValue("date_followed_back_detected", IU.date_followed_back_detected != null ? IU.date_followed_back_detected.ToString(SQLiteDateTimeFormat) : "");
SQLcommand.Parameters.AddWithValue("date_last_commented", IU.date_last_commented != null ? IU.date_last_commented.ToString(SQLiteDateTimeFormat) : "");
SQLcommand.Parameters.AddWithValue("date_last_liked", IU.date_last_liked != null ? IU.date_last_liked.ToString(SQLiteDateTimeFormat) : "");
SQLcommand.Parameters.AddWithValue("date_last_updated", DateTime.Now.ToString(SQLiteDateTimeFormat));
SQLcommand.Parameters.AddWithValue("SQLiteNullDateString", SQLiteNullDateString);
if (_conn.State != System.Data.ConnectionState.Open)
{
_conn.Open();
}
SQLcommand.ExecuteNonQuery();
_conn.Close();
}
}
}
catch (InvalidOperationException se)
{
C_voice_core.speak($"SQL Error: {se.Message}");
IU.error = se.Message;
System.Windows.Forms.MessageBox.Show($"SQL Error: {se.Message}");
}
catch (Exception se)
{
C_voice_core.speak($"SQL Error: {se.Message}");
IU.error = se.Message;
System.Windows.Forms.MessageBox.Show($"SQL Error: {se.Message}");
}
return(true);
}
// add new instagram user just by username
public bool AddInstaUser(InstaUser IU)
{
try
{
using (SQLiteConnection _conn = new SQLiteConnection(SQLiteConnString, true))
{
// C_voice_core.speak("db AddInstaUser");
// only add if not already there
using (SQLiteCommand SQLcommand = new SQLiteCommand("insert into insta_users (username, date_created,date_last_updated) select @username, @datetime, @date_last_updated " +
"WHERE NOT EXISTS(SELECT 1 FROM insta_users WHERE username = @username);", _conn))
{
SQLcommand.Parameters.AddWithValue("username", IU.username);
SQLcommand.Parameters.AddWithValue("datetime", DateTime.Now.ToString(SQLiteDateTimeFormat));
SQLcommand.Parameters.AddWithValue("date_last_updated", DateTime.Now.ToString(SQLiteDateTimeFormat));
if (_conn.State != System.Data.ConnectionState.Open)
{
_conn.Open();
}
SQLcommand.ExecuteNonQuery();
_conn.Close();
}
}
}
catch (InvalidOperationException se)
{
C_voice_core.speak($"SQL Error: {se.Message}");
IU.error = se.Message;
System.Windows.Forms.MessageBox.Show($"SQL Error: {se.Message}");
}
catch (Exception se)
{
C_voice_core.speak($"SQL Error: {se.Message}");
IU.error = se.Message;
System.Windows.Forms.MessageBox.Show($"SQL Error: {se.Message}");
}
return(true);
}