/*-------------------------------------------------------------*/
private void tbxMPN_Leave(object sender, EventArgs e)
{
Footprint loadedFootprint = DatabaseOperations.GetFootprint(tbxMPN.Text);
if (loadedFootprint != null)
{
this.SetFootprint(loadedFootprint);
}
}
/// <summary>
/// Sets the footprint to be edited by displaying its properties
/// </summary>
/// <param name="footprint">Footprint to be edited</param>
public void SetFootprint(Footprint footprint)
{
tbxMPN.Text = footprint.ManufacturerPartNumber;
nudHeight.Value = (decimal)footprint.Height;
nudLength.Value = (decimal)footprint.Length;
nudWidth.Value = (decimal)footprint.Width;
nudRotation.Value = footprint.Rotation;
bscOffset.ValueX = (decimal)footprint.OffsetStackX;
bscOffset.ValueY = (decimal)footprint.OffsetStackY;
cbxNozzle.SelectedItem = footprint.Nozzle;
cbxStackType.SelectedItem = PNPconverterTools.StackTypeToString(footprint.StackType);
nudFeedRate.Value = (decimal)footprint.FeedRate;
}
/// <summary>
/// Set all the sql command parameters with the values of the footprint, sqlCommand is passed by reference
/// </summary>
/// <param name="sqlCommand">Sqlite command</param>
/// <param name="footprint">Footprint with parameters</param>
private static void SetParametersSqlString(SqliteCommand sqlCommand, Footprint footprint)
{
//http://stackoverflow.com/questions/6001016/why-c-sharp-dont-let-to-pass-a-using-variable-to-a-function-as-ref-or-out
//no ref -> should be fine
sqlCommand.Parameters.AddWithValue("manufacturerPartNumber", footprint.ManufacturerPartNumber);
sqlCommand.Parameters.AddWithValue("width", Math.Round(footprint.Width, 2));
sqlCommand.Parameters.AddWithValue("length", Math.Round(footprint.Length, 2));
sqlCommand.Parameters.AddWithValue("height", Math.Round(footprint.Height, 2));
sqlCommand.Parameters.AddWithValue("rotation", footprint.Rotation);
sqlCommand.Parameters.AddWithValue("offsetStackX", Math.Round(footprint.OffsetStackX, 2));
sqlCommand.Parameters.AddWithValue("offsetStackY", Math.Round(footprint.OffsetStackY, 2));
sqlCommand.Parameters.AddWithValue("feedRate", Math.Round(footprint.FeedRate, 2));
sqlCommand.Parameters.AddWithValue("nozzle", footprint.Nozzle.ToString());
sqlCommand.Parameters.AddWithValue("stackType", footprint.StackType.ToString());
}
/// <summary>
/// Adds a new footprint to the database
/// </summary>
/// <param name="footprint">Footprint to be added</param>
public static void AddNewFootprint(Footprint footprint)
{
string sqlString = @"INSERT INTO footprints(manufacturerPartNumber,width,length,height,rotation,offsetStackX,offsetStackY,feedRate,nozzle_id,stackType_id)
VALUES(@manufacturerPartNumber,@width,@length,@height,@rotation,@offsetStackX,@offsetStackY,@feedRate,
(SELECT n.id FROM nozzles n WHERE n.name = @nozzle),
(SELECT s.id FROM stackTypes s WHERE s.name = @stackType));";
using (SqliteConnection dbConnection = new SqliteConnection(connectionString))
using (SqliteCommand dbCommand = new SqliteCommand(sqlString, dbConnection))
{
dbConnection.Open();
SetParametersSqlString(dbCommand, footprint);
dbCommand.ExecuteNonQuery();
dbConnection.Clone();
}
}
/// <summary>
/// Updates all the values of the footprint with the new values, based on the manufacturerPartNumber
/// </summary>
/// <param name="footprint">Footprint with new values</param>
public static void UpdateFootprint(Footprint footprint)
{
string sqlString = @"UPDATE footprints
SET width=@width, length=@length, height=@height, rotation=@rotation,
offsetStackX=@offsetStackX, offsetStackY=@offsetStackY, feedRate=@feedRate,
nozzle_id=(SELECT n.id FROM nozzles n WHERE n.name = @nozzle),
stackType_id=(SELECT s.id FROM stackTypes s WHERE s.name = @stackType)
WHERE manufacturerPartNumber = @manufacturerPartNumber;";
using (SqliteConnection dbConnection = new SqliteConnection(connectionString))
using (SqliteCommand dbCommand = new SqliteCommand(sqlString, dbConnection))
{
dbConnection.Open();
SetParametersSqlString(dbCommand, footprint);
dbCommand.ExecuteNonQuery();
dbConnection.Close();
}
}
/*-------------------------------------------------------------*/
private void btnSave_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(tbxMPN.Text))
{
MessageBox.Show("Please enter a valid manufacturer part number", "Error: invalid manufacturer part number", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else if (tbxMPN.Text.Contains(','))
{
MessageBox.Show("manufacturer part number contains a ','" + Environment.NewLine + "this is not allowed.", "Error: Comma not allowed", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
try
{
//Generate new footprint
string manufacturerPartNumber = tbxMPN.Text;
float width = (float)nudWidth.Value;
float length = (float)nudLength.Value;
float height = (float)nudHeight.Value;
int rotation = Convert.ToInt32(nudRotation.Value);
float offsetX = (float)bscOffset.ValueX;
float offsetY = (float)bscOffset.ValueY;
float feedRate = (float)nudFeedRate.Value;
StackType stackType = PNPconverterTools.StringToStackType(cbxStackType.SelectedItem.ToString());
footPrint_ = new Footprint(manufacturerPartNumber, width, length, height, rotation, offsetX, offsetY, feedRate, (Nozzle)cbxNozzle.SelectedItem, stackType);
newFootprintMade_ = !DatabaseOperations.FootprintExists(footPrint_.ManufacturerPartNumber);
if (newFootprintMade_)
{
//new footprint
DatabaseOperations.AddNewFootprint(footPrint_);
}
else
{
//change of values
DatabaseOperations.UpdateFootprint(footPrint_);
}
this.DialogResult = DialogResult.Yes;
this.Close();
}
catch (Exception exc)
{
MessageBox.Show(exc.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Import external database into this database
/// </summary>
/// <param name="path">Path to the database to be imported</param>
/// <param name="progress">Progress class to report to</param>
public static void ImportDatabase(string path, IProgress <Tuple <int, int> > progress)
{
//http://stackoverflow.com/questions/19768718/updating-progressbar-external-class
string externalConnectionString = "Data Source=" + path + "; Version=3; FailIfMissing=True; Foreign Keys=True;";
List <string> externalFootprints = GetFootprintList(externalConnectionString); //list of all MPN in the external database
List <string> internalFootprints = GetFootprintList(connectionString); //list of all the MPN in the internal database
List <string> difference = externalFootprints.Except(internalFootprints).ToList(); //list of all the MPN that are in the external database and not in the internal database
int numberOfDifferences = difference.Count;
int counter = 0;
progress.Report(new Tuple <int, int>(counter, numberOfDifferences));
foreach (string manufacturerPartNumber in difference)
{
counter++;
Footprint newFootprint = GetFootprint(manufacturerPartNumber, externalConnectionString); //Get the footprint from the other database
AddNewFootprint(newFootprint); //And add it to our database
progress.Report(new Tuple <int, int>(counter, numberOfDifferences));
}
}
/// <summary>
/// Get the footprint from the database specified by the connectionstring
/// </summary>
/// <param name="manufacturerPartNumber">Manufacturer part number to be loaded</param>
/// <param name="connectionString_">Connectionstring for the sqlite database</param>
/// <returns>Footprint of the manufacturere part number</returns>
private static Footprint GetFootprint(string manufacturerPartNumber, string connectionString_)
{
Footprint result = null;
string sqlString = @"SELECT f.manufacturerPartNumber,f.width,f.length,f.height,f.rotation,f.offsetStackX,f.offsetStackY,f.feedRate,n.name as nozzle,s.name as stackType
FROM footprints f, nozzles n,stackTypes s
WHERE n.id = f.nozzle_id
AND s.id = f.StackType_id
AND f.manufacturerPartNumber = @manufacturerPartNumber";
using (SqliteConnection dbConnection = new SqliteConnection(connectionString_))
using (SqliteCommand dbCommand = new SqliteCommand(sqlString, dbConnection))
{
dbConnection.Open();
dbCommand.Parameters.AddWithValue("manufacturerPartNumber", manufacturerPartNumber);
using (SqliteDataReader reader = dbCommand.ExecuteReader())
{
reader.Read();
if (reader.HasRows)
{
string partNumber_ = reader["manufacturerPartNumber"].ToString();
float width_ = float.Parse(reader["width"].ToString());
float length_ = float.Parse(reader["length"].ToString());
float height_ = float.Parse(reader["height"].ToString());
int rotation_ = Int32.Parse(reader["rotation"].ToString());
float offsetX_ = float.Parse(reader["offsetStackX"].ToString());
float offsetY_ = float.Parse(reader["offsetStackY"].ToString());
float feedRate = float.Parse(reader["feedRate"].ToString());
Nozzle nozzle = PNPconverterTools.StringToNozzle(reader["nozzle"].ToString());
StackType stackType = PNPconverterTools.StringToStackType(reader["stackType"].ToString());
result = new Footprint(partNumber_, width_, length_, height_, rotation_, offsetX_, offsetY_, feedRate, nozzle, stackType);
}
}
dbConnection.Close();
}
return(result);
}
