public static bool SetData(SQLite_DataStruct data)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + database_name + ".sqlite");
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = "INSERT INTO device VALUES('" + data.DeviceID + "','" + data.UserID + "','" + data.NickName + "')";
try
{
cmd.ExecuteNonQuery();
}
catch (Exception es)
{
try
{
cmd.CommandText = "UPDATE device SET UserID=@userid,NickName=@nickname WHERE ID='" + data.DeviceID + "'";
cmd.Parameters.Add("userid", DbType.String).Value = data.UserID == null ? "": data.UserID;
cmd.Parameters.Add("nickname", DbType.String).Value = data.NickName == null ? "" : data.NickName;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
cn.Close();
MessageBox.Show(ex.Message);
return(false);
}
}
}
cn.Close();
return(true);
}
public static List <SQLite_DataStruct> GetData()
{
SqlData.Clear();
SQLiteConnection cn = new SQLiteConnection("data source=" + database_name + ".sqlite");
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM device";
SQLiteDataReader sr = cmd.ExecuteReader();
//sql_data.Items.Clear();
int i = 0;
while (sr.Read())
{
//Console.WriteLine($"{sr.GetString(0)} {sr.GetString(1)}");
SQLite_DataStruct data2 = new SQLite_DataStruct
{
Index = i,
DeviceID = sr.GetString(0),
UserID = sr.GetString(1),
NickName = sr.GetString(2)
};
SqlData.Add(data2);
i++;
}
}
cn.Close();
return(SqlData);
}
public static bool DeleteData(SQLite_DataStruct data)
{
SQLiteConnection cn = new SQLiteConnection("data source=" + database_name + ".sqlite");
if (cn.State != System.Data.ConnectionState.Open)
{
try
{
cn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = cn;
cmd.CommandText = "DELETE FROM device WHERE ID='" + data.DeviceID + "'";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
cn.Close();
MessageBox.Show(e.Message);
return(false);
}
}
cn.Close();
return(true);
}
