public void FromItemList(string pDBPath, Sven_Bobrowski.DE.Integration.Items pItems)
{
if (null == pItems)
return;
try
{
// connect and create tables
using (SQLiteConnection tConnection = new SQLiteConnection(String.Format("Data Source={0};Version=3;", pDBPath)))
{
tConnection.Open();
// loop items
foreach (int tId in pItems.DataItems.Keys)
{
Sven_Bobrowski.DE.Integration.DataItem tItem;
if (pItems.TryGetItem(tId, out tItem))
{
// check if dataset allready exists, so update it, else add it
if (DataItemExists(tConnection, tId))
{
UpdateDataItem(tConnection, tItem);
}
else
{
InsertDataItem(tConnection, tItem);
}
}
}
tConnection.Close();
}
}
catch (Exception e)
{
string tMsg =
String.Format(Resources.DB_Update_Failed_1 + Environment.NewLine + Resources.DB_Update_Failed_2, pDBPath, e.Message);
MessageBox.Show(tMsg, Resources.DB_Update_Failed_Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private int InsertDataItem(SQLiteConnection pConnection, Sven_Bobrowski.DE.Integration.DataItem pItem)
{
try
{
using (var tCommand = new SQLiteCommand(pConnection))
{
// we need to insert the externals now
int tDeviceId = InsertExternalItem(pConnection, pItem.Device);
int tSegmentId = InsertExternalItem(pConnection, pItem.Segment);
int tLabelId = InsertExternalItem(pConnection, pItem.Label);
int tImgId = InsertExternalItem(pConnection, pItem.Image);
string tSQLCmdText = "INSERT INTO main (" +
"Decoder_ID, " +
"Point, " +
"DCC_Adress_1, " +
"DCC_Adress_2, " +
"DCC_Adress_3, " +
"DCC_Adress_4, " +
"State, " +
"External_ID_1, " +
"External_ID_2, " +
"External_ID_3, " +
"External_ID_4, " +
"Notes) VALUES (" +
Convert.ToString(pItem.DecoderId) + ", " +
Convert.ToString(pItem.Point) + ", " +
Convert.ToString(pItem.DCCAdress1) + ", " +
Convert.ToString(pItem.DCCAdress2) + ", " +
Convert.ToString(pItem.DCCAdress3) + ", " +
Convert.ToString(pItem.DCCAdress4) + ", " +
Convert.ToString((int)pItem.State) + ", " +
Convert.ToString(tDeviceId) + ", " +
Convert.ToString(tSegmentId) + ", " +
Convert.ToString(tLabelId) + ", " +
Convert.ToString(tImgId) + ", " +
"'" + "" + "'" +
");";
tCommand.CommandText = tSQLCmdText;
tCommand.ExecuteNonQuery();
tCommand.CommandText = "SELECT last_insert_rowid();";
using (SQLiteDataReader tReader = tCommand.ExecuteReader())
{
tReader.Read();
int tId = Convert.ToInt32(tReader[0]);
pItem.ID = tId;
}
return pItem.ID;
}
}
catch (Exception e)
{
MessageBox.Show("Internal error (something goes wrong with an sql statement, source: InsertDataItem): \"" + e.Message + "\".");
return -1;
}
}
private void UpdateDataItem(SQLiteConnection pConnection, Sven_Bobrowski.DE.Integration.DataItem pItem)
{
}
public void ToItemList(string pDBPath, ref Sven_Bobrowski.DE.Integration.Items pItems)
{
if (pItems == null)
pItems = new Items();
// FF
}