public string Insert(FieldValuesStuff data)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO [dbo].[aggregatevalues] ([definition],[field],[value]) VALUES (" + EntityDefaults.IDkey + ", " + AggregateFields.IDkey + ", @value);");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue(EntityDefaults.IDkey, data.Definition);
command.Parameters.AddWithValue(AggregateFields.IDkey, data.FieldId);
command.Parameters.AddWithValue("@value", data.FieldValue);
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = Utilities.parseCommandString(command, new List <string>(new string[] { EntityDefaults.IDkey, AggregateFields.IDkey }));
}
return(query);
}
public ObservableCollection <FieldValuesStuff> GetValuesForEntity(int EntityId)
{
ObservableCollection <FieldValuesStuff> list = new ObservableCollection <FieldValuesStuff>();
using (SqlConnection conn = new SqlConnection(this.ConnString))
{
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append(@"SELECT aggregatefields.id as fieldid, aggregatevalues.id as valueid, aggregatefields.name as fieldname, aggregatevalues.value,
aggregatefields.formula, aggregatefields.measurementunit, aggregatefields.measurementoffset, aggregatefields.measurementmultiplier
from aggregatevalues join aggregatefields on (aggregatevalues.field = aggregatefields.id) Where definition=@id;");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue("@id", EntityId);
command.Connection = conn;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
FieldValuesStuff tmp = new FieldValuesStuff();
tmp.dBAction = DBAction.UPDATE;
tmp.Definition = EntityId;
tmp.FieldId = Convert.ToInt32(reader["fieldid"]);
tmp.ValueId = Convert.ToInt32(reader["valueid"]);
tmp.FieldName = Convert.ToString(reader["fieldname"]);
tmp.FieldValue = Convert.ToDecimal(reader["value"]);
tmp.FieldFormula = Convert.ToInt32(reader["formula"]);
tmp.FieldUnits = Convert.ToString(reader["measurementunit"]);
tmp.FieldOffset = Convert.ToDecimal(reader["measurementoffset"]);
tmp.FieldMultiplier = Convert.ToDecimal(reader["measurementmultiplier"]);
list.Add(tmp);
}
}
}
}
return(list);
}
/// <summary>
/// saves existing record
/// And returns query as string for recording SQL updates to file
/// </summary>
public string Save(FieldValuesStuff data)
{
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE aggregatevalues SET definition=" + EntityDefaults.IDkey + ", field=" + AggregateFields.IDkey + ", value=@value WHERE id = " + FieldValuesStuff.IDkey + ";");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue(FieldValuesStuff.IDkey, data.ValueId);
command.Parameters.AddWithValue(EntityDefaults.IDkey, data.Definition);
command.Parameters.AddWithValue(AggregateFields.IDkey, data.FieldId);
command.Parameters.AddWithValue("@value", data.FieldValue);
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = Utilities.parseCommandString(command, new List <string>(new string[] { FieldValuesStuff.IDkey, AggregateFields.IDkey, EntityDefaults.IDkey }));
}
return(query);
}
public string Save(FieldValuesStuff data)
{
this.GetById(data.FieldId);
string query = "";
using (SqlCommand command = new SqlCommand())
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("UPDATE aggregatefields SET [name]=@SQLname, [formula]=@formula, [measurementunit]=@measurementunit, [measurementmultiplier]=@measurementmultiplier, [measurementoffset]=@measurementoffset, [category]=@category, [digits]=@digits, [moreisbetter]=@moreisbetter, [usedinconfig]=@usedinconfig, [note]=@note WHERE id =" + AggregateFields.IDkey + ";");
command.CommandText = sqlCommand.ToString();
command.Parameters.AddWithValue(AggregateFields.IDkey, data.FieldId);
command.Parameters.AddWithValue("@SQLname", data.FieldName);
command.Parameters.AddWithValue("@formula", data.FieldFormula);
command.Parameters.AddWithValue("@measurementunit", data.FieldUnits);
command.Parameters.AddWithValue("@measurementmultiplier", data.FieldMultiplier);
command.Parameters.AddWithValue("@measurementoffset", data.FieldOffset);
command.Parameters.AddWithValue("@category", this.category);
command.Parameters.AddWithValue("@digits", this.digits);
command.Parameters.AddWithValue("@moreisbetter", Utilities.getNullableInt(this.moreisbetter));
command.Parameters.AddWithValue("@usedinconfig", Utilities.getNullableInt(this.usedinconfig));
if (this.note == null)
{
command.Parameters.AddWithValue("@note", string.Empty);
}
else
{
command.Parameters.AddWithValue("@note", this.note);
}
SqlConnection conn = new SqlConnection(this.ConnString);
conn.Open();
command.Connection = conn;
command.ExecuteNonQuery();
conn.Close();
query = Utilities.parseCommandString(command, new List <string>(new string[] { AggregateFields.IDkey }));
}
return(query);
}