using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Maser.Feanor.Model; using System.Data.SqlClient; using System.Globalization; namespace Maser.Feanor.Biz { public class Calibrations { public Calibration GetByPK(Int32 PK) { string query = String.Format("Select * from Calibrations where PK = {0}", PK); try { return GetByQuery(query); } catch (Exception e) { throw e; } } public List GetBySensorPK(Int32 SensorPK) { // Return all calibrations of sensor string query = String.Format("Select * from Calibrations where SensorPK = {0} order by Creation desc", SensorPK); try { return GetListByQuery(query); } catch (Exception e) { throw e; } } public Calibration GetLastCreatedBySensorPK(Int32 SensorPK) { string query = String.Format("Select top 1 * from Calibrations where SensorPK = {0} order by Creation desc", SensorPK); try { return GetByQuery(query); } catch (Exception e) { throw e; } } public bool Equal(Calibration A, Calibration B) { // Not equal when values, release or expiry differs // Values can differ 0.01% and still be equal if (A.Release != B.Release) return false; if (A.Expiry != B.Expiry) return false; if (A.Coefficients.Length != B.Coefficients.Length) return false; for (int i = 0; i < A.Coefficients.Length; i++) { if (B.Coefficients[i] != 0) { double d = Math.Abs(A.Coefficients[i] / B.Coefficients[i] - 1); if (Math.Abs(A.Coefficients[i] / B.Coefficients[i] - 1) > 0.0000001) return false; } else if (A.Coefficients[i] != 0) return false; } return true; } public Int32 Add(Calibration calibration) { double c0 = calibration.Coefficients[0]; double c1 = calibration.Coefficients[1]; double c2 = calibration.Coefficients[2]; double c3 = calibration.Coefficients[3]; double X1 = calibration.Xvalues[0]; double X2 = calibration.Xvalues[1]; double X3 = calibration.Xvalues[2]; double X4 = calibration.Xvalues[3]; //string query = String.Format("INSERT INTO Calibrations (SensorPK, Release, Expiry, Creation, c0, c1, c2, c3, U) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}');", calibration.SensorPK, calibration.Release.FormatSQL(),calibration.Expiry.FormatSQL(), calibration.Creation.FormatSQL(), c0,c1,c2,c3, calibration.Uncertainty); string query = String.Format("INSERT INTO Calibrations (SensorPK, Release, Expiry, Creation, c0, c1, c2, c3, U, X1, X2, X3, X4) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');", calibration.SensorPK, calibration.Release.FormatSQL(), calibration.Expiry.FormatSQL(), calibration.Creation.FormatSQL(), c0, c1, c2, c3, calibration.Uncertainty,X1,X2,X3,X4); query += "SELECT SCOPE_IDENTITY();"; SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr); myConnection.Open(); try { SqlCommand myCommand = new SqlCommand(query, myConnection); return Int32.Parse(myCommand.ExecuteScalar().ToString()); } catch (Exception e) { throw e; } finally { myConnection.Close(); } } private Calibration GetByQuery(string query) { Calibration result = null; SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr); SqlDataReader myReader = null; try { myConnection.Open(); SqlCommand myCommand = new SqlCommand(query, myConnection); myReader = myCommand.ExecuteReader(); if (myReader.HasRows) { myReader.Read(); result = FromSqlDataReader(myReader); //Console.WriteLine(result); } // Console.WriteLine(myReader.FieldCount); myReader.Close(); } catch (Exception e) { // Console.WriteLine(e); throw e; } finally { myConnection.Close(); // Martijn /* https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection. */ } return result; } private List GetListByQuery(string query) { List result = new List(); SqlDataReader myReader = null; SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.FeanorConnStr); try { myConnection.Open(); SqlCommand myCommand = new SqlCommand(query, myConnection); myReader = myCommand.ExecuteReader(); if (myReader.HasRows) { while (myReader.Read()) { result.Add(FromSqlDataReader(myReader)); } } } catch (Exception e) { throw e; } finally { myReader.Close(); myConnection.Close(); // Martijn /* https://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.110).aspx Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection. */ } return result; } private Calibration FromSqlDataReader(SqlDataReader myReader) { double c0 = (double)myReader.GetFloat(5); double c1 = (double)myReader.GetFloat(6); double c2 = (double)myReader.GetFloat(7); double c3 = (double)myReader.GetFloat(8); double U = (double)myReader.GetFloat(9); double X1 = (double)myReader.GetFloat(10); double X2 = (double)myReader.GetFloat(11); double X3 = (double)myReader.GetFloat(12); double X4 = (double)myReader.GetFloat(13); double[] c = new double[4] { c0, c1, c2, c3 }; double[] Xvalues = new double[4] { X1, X2, X3, X4 }; Calibration result = new Calibration( myReader.GetInt32(0), myReader.GetInt32(1), myReader.GetDateTime(2), myReader.GetDateTime(3), myReader.GetDateTime(4), c, U, Xvalues ); return result; } } public static class StringExtenions { public static bool TryParseToDouble(this String s, out double value) { value = 0; try { NumberStyles styles = NumberStyles.AllowExponent | NumberStyles.Number; value = double.Parse(s, styles); } catch { return false; } return true; } } }