public static ToolModel LoadToolDataByID(int toolID)
{
using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
ToolModel output = cnn.Query <ToolModel>("select * from Tools where toolID =" + toolID, new DynamicParameters()).ToList()[0];
return(output); //Returns output as a list instead of a IEnumerable
}
}
public static bool UpdateToolRecord(ToolModel updateToolModel)
{
// Check and Adjust toolmodel data
if (updateToolModel.Storage == "")
{
updateToolModel.Storage = "-";
}
if (updateToolModel.Location == "")
{
updateToolModel.Location = "-";
}
if (updateToolModel.HolderVendor == "Select" | updateToolModel.HolderVendor == "")
{
updateToolModel.HolderVendor = "-";
}
if (updateToolModel.HolderArt == "")
{
updateToolModel.HolderArt = "-";
}
if (updateToolModel.ExtensionVendor == "Select" | updateToolModel.ExtensionVendor == "")
{
updateToolModel.ExtensionVendor = "-";
}
if (updateToolModel.ExtensionArt == "")
{
updateToolModel.ExtensionArt = "-";
}
if (updateToolModel.ToolVendor == "Select" | updateToolModel.ToolVendor == "")
{
updateToolModel.ToolVendor = "-";
}
if (updateToolModel.ToolArt == "")
{
updateToolModel.ToolArt = "-";
}
// Create SQL connection
using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
SQLiteCommand updateSQL = new SQLiteCommand("UPDATE Tools SET Description= @Description, Storage= @Storage, Location= @Location, HolderVendor= @HolderVendor," +
"HolderArt= @HolderArt, ExtensionVendor= @ExtensionVendor, ExtensionArt= @ExtensionArt, ToolVendor= @ToolVendor, ToolArt= @ToolArt WHERE ToolID= @ToolID", cnn);
// Assign parameters
updateSQL.Parameters.Add("@Description", DbType.String).Value = updateToolModel.Description;
updateSQL.Parameters.Add("@Storage", DbType.String).Value = updateToolModel.Storage;
updateSQL.Parameters.Add("@Location", DbType.String).Value = updateToolModel.Location;
updateSQL.Parameters.Add("@HolderVendor", DbType.String).Value = updateToolModel.HolderVendor;
updateSQL.Parameters.Add("@HolderArt", DbType.String).Value = updateToolModel.HolderArt;
updateSQL.Parameters.Add("@ExtensionVendor", DbType.String).Value = updateToolModel.ExtensionVendor;
updateSQL.Parameters.Add("@ExtensionArt", DbType.String).Value = updateToolModel.ExtensionArt;
updateSQL.Parameters.Add("@ToolVendor", DbType.String).Value = updateToolModel.ToolVendor;
updateSQL.Parameters.Add("@ToolArt", DbType.String).Value = updateToolModel.ToolArt;
updateSQL.Parameters.Add("@ToolID", DbType.Int32).Value = updateToolModel.ToolID;
// Open Database
cnn.Open();
// Writing succesfull or not
bool writingSuccesFull;
// Try to execute command
try
{
updateSQL.ExecuteNonQuery();
writingSuccesFull = true;
}
catch (Exception ex)
{
if (ex != null)
{
writingSuccesFull = false;
}
throw;
}
//Close Database
cnn.Close();
return(writingSuccesFull);
}
}
public static bool NewToolRecord(ToolModel toolModel)
{
// Check and Adjust toolmodel data
if (toolModel.Storage == "")
{
toolModel.Storage = "-";
}
if (toolModel.HolderVendor == "Select" | toolModel.HolderVendor == "")
{
toolModel.HolderVendor = "-";
}
if (toolModel.HolderArt == "")
{
toolModel.HolderArt = "-";
}
if (toolModel.ExtensionVendor == "Select" | toolModel.ExtensionVendor == "")
{
toolModel.ExtensionVendor = "-";
}
if (toolModel.ExtensionArt == "-")
{
toolModel.ExtensionArt = "-";
}
if (toolModel.ToolVendor == "Select" | toolModel.ToolVendor == "")
{
toolModel.ToolVendor = "-";
}
if (toolModel.ToolArt == "")
{
toolModel.ToolArt = "-";
}
// Create SQL connection
using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString()))
{
SQLiteCommand insertSQL = new SQLiteCommand("INSERT INTO Tools (ToolID, Description, Type, Function, Diameter, Length, Holder, Machine, Storage, HolderVendor, HolderArt, ExtensionVendor, ExtensionArt, ToolVendor, ToolArt) " +
"VALUES (@ToolID, @Description, @Type, @Function, @Diameter, @Length, @Holder, @Machine, @Storage, @HolderVendor, @HolderArt, @ExtensionVendor, @ExtensionArt, @ToolVendor, @ToolArt)", cnn);
// Assign parameters
insertSQL.Parameters.Add("@ToolID", DbType.Int32).Value = toolModel.ToolID;
insertSQL.Parameters.Add("@Description", DbType.String).Value = toolModel.Description;
insertSQL.Parameters.Add("@Type", DbType.String).Value = toolModel.Type;
insertSQL.Parameters.Add("@Function", DbType.String).Value = toolModel.Function;
insertSQL.Parameters.Add("@Diameter", DbType.String).Value = toolModel.Diameter;
insertSQL.Parameters.Add("@Length", DbType.String).Value = toolModel.Length;
insertSQL.Parameters.Add("@Holder", DbType.String).Value = toolModel.Holder;
insertSQL.Parameters.Add("@Machine", DbType.String).Value = toolModel.Machine;
insertSQL.Parameters.Add("@Storage", DbType.String).Value = toolModel.Storage;
insertSQL.Parameters.Add("@HolderVendor", DbType.String).Value = toolModel.HolderVendor;
insertSQL.Parameters.Add("@HolderArt", DbType.String).Value = toolModel.HolderArt;
insertSQL.Parameters.Add("@ExtensionVendor", DbType.String).Value = toolModel.ExtensionVendor;
insertSQL.Parameters.Add("@ExtensionArt", DbType.String).Value = toolModel.ExtensionArt;
insertSQL.Parameters.Add("@ToolVendor", DbType.String).Value = toolModel.ToolVendor;
insertSQL.Parameters.Add("@ToolArt", DbType.String).Value = toolModel.ToolArt;
// Open Database
cnn.Open();
// Writing succesfull or not
bool writingSuccesFull;
// Try to execute command
try
{
insertSQL.ExecuteNonQuery();
writingSuccesFull = true;
}
catch (Exception ex)
{
if (ex != null)
{
writingSuccesFull = false;
}
throw;
}
//Close Database
cnn.Close();
return(writingSuccesFull);
}
}