public static string InsertData(POCO.UID uid)
{
string status;
String connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;
string insterData = "Insert into UID_quantity_counter5 (UID, quantity, source, event_date_UMS) VALUES (@UID,@Quantity,@Source,@Event_date) ON DUBLICATE KEY UPDATE quantity=@quantity, event_date_UMS=@Event_date";
using (MySqlConnection mysqlconnection = new MySqlConnection(connection))
{
using (MySqlCommand insert = new MySqlCommand(insterData, mysqlconnection))
{
mysqlconnection.Open();
insert.Parameters.AddWithValue("@UID", uid.Uid);
insert.Parameters.AddWithValue("@Quantity", uid.Quantity);
insert.Parameters.AddWithValue("@Source", uid.Source);
insert.Parameters.AddWithValue("@Event_date", uid.Event_date_UMS);
try
{
int insertStatus = insert.ExecuteNonQuery();
if (insertStatus == 1)
{
status = "Row added";
}
else
{
status = "";
}
} catch (Exception ex)
{
status = ex.Message;
}
}
}
return(status);
}
public void TestInsertData()
{
POCO.UID uid = new POCO.UID();
uid.Uid = "79879";
uid.Quantity = "10";
uid.Source = "source";
uid.Event_date_UMS = "576540000";
string status = MySqlDBConnection.InsertData(uid);
Assert.AreEqual("Row added", status);
}
public static string UpSert(POCO.UID uid)
{
string status;
int insertStatus;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["BarCodeScanner"].ConnectionString;
using (SQLiteConnection sqliteconnection = new SQLiteConnection(connection))
{
sqliteconnection.Open();
string insertData = "INSERT INTO UID_quantity_counter (UID, quantity, source, event_date_UMS) VALUES (@UID,@Quantity,@Source,@Event_date) on conflict(UID) do update set quantity=excluded.quantity, event_date_UMS=excluded.event_date_UMS";
using (SQLiteCommand insert = new SQLiteCommand(sqliteconnection))
{
insert.CommandText = insertData;
insert.Parameters.AddWithValue("@UID", uid.Uid);
insert.Parameters.AddWithValue("@Quantity", uid.Quantity);
insert.Parameters.AddWithValue("@Source", uid.Source);
insert.Parameters.AddWithValue("@Event_date", uid.Event_date_UMS);
//insert.Parameters.AddWithValue("@Entry_date", uid.Entry_date);
try
{
insertStatus = insert.ExecuteNonQuery();
if (insertStatus == 1)
{
status = "Row added, UID: " + uid.Uid;
logger.Info("Row added, UID: " + uid.Uid);
}
else
{
status = "";
}
}
catch (Exception ex)
{
logger.Error(ex, "Upsert");
status = ex.Message;
throw new Exception(ex.Message);
}
}
}
return(status);
}
//string tableName = "UID_quantity_counter";
//enne inserti tuleb kontrollida, kas selline uid on juba olemas, kui on, siis tuleb teha update!
public static void Insert(POCO.UID uid)
{
string status;
int insertStatus;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["BarCodeScanner"].ConnectionString;
using (SQLiteConnection sqliteconnection = new SQLiteConnection(connection))
{
sqliteconnection.Open();
string insertData = "INSERT INTO UID_quantity_counter (UID, quantity, source, event_date_UMS) VALUES (@UID,@Quantity,@Source,@Event_date)";
using (SQLiteCommand insert = new SQLiteCommand(sqliteconnection))
{
insert.CommandText = insertData;
insert.Parameters.AddWithValue("@UID", uid.Uid);
insert.Parameters.AddWithValue("@Quantity", uid.Quantity);
insert.Parameters.AddWithValue("@Source", uid.Source);
insert.Parameters.AddWithValue("@Event_date", uid.Event_date_UMS);
//insert.Parameters.AddWithValue("@Entry_date", uid.Entry_date);
try
{
insertStatus = insert.ExecuteNonQuery();
if (insertStatus == 1)
{
status = "Row added";
}
else
{
status = "";
}
}
catch (Exception ex)
{
//status = ex.Message;
//throw new Exception(ex.Message);
}
}
}
//return status;
}