/Source/HydroDesktop.Data/Database/MetadataCacheManagerSQL.cs

# · C# · 1174 lines · 836 code · 147 blank · 191 comment · 76 complexity · 724921c975c448f5e08497c48fd47195 MD5 · raw file

Large files are truncated click here to view the full file

    

  1. using System;

    2. 

  2. using System.Collections.Generic;

    3. 

  3. using System.Linq;

    4. 

  4. using System.Text;

    5. 

  5. using HydroDesktop.Interfaces;

    6. 

  6. using HydroDesktop.Interfaces.ObjectModel;

    7. 

  7. using System.Data;

    8. 

  8. using System.Data.Common;

    9. 

  9. using System.Globalization;

    10. 

  10. 

    11. 

  11. namespace HydroDesktop.Database

    12. 

  12. {

    13. 

  13. 	/// <summary>

    14. 

  14. 	/// This class is responsible for communication with the 'Metadata Cache' database

    15. 

  15.     /// This is an alternative implementation internally using SQL queries instead of NHibernate.

    16. 

  16.     /// This is to compare saving speed.

    17. 

  17. 	/// </summary>

    18. 

  18. 	public class MetadataCacheManagerSQL

    19. 

  19. 	{

    20. 

  20. 		#region Variables

    21. 

  21. 

    22. 

  22.         //helper class which communicates with the database

    23. 

  23.         private DbOperations _db;

    24. 

  24. 

    25. 

  25. 		// lookup caches used by the SaveSeries method

    26. 

  26. 		private Dictionary<string, Site> _siteCache = new Dictionary<string, Site> ();

    27. 

  27. 		private Dictionary<string, Variable> _variableCache = new Dictionary<string, Variable> ();

    28. 

  28. 		private Dictionary<string, Method> _methodCache = new Dictionary<string, Method> ();

    29. 

  29. 		private Dictionary<string, QualityControlLevel> _qualControlCache = new Dictionary<string, QualityControlLevel> ();

    30. 

  30. 		private Dictionary<string, Source> _sourcesCache = new Dictionary<string, Source> ();

    31. 

  31. 

    32. 

  32. 		#endregion

    33. 

  33. 

    34. 

  34. 		#region Constructor

    35. 

  35. 		/// <summary>

    36. 

  36. 		/// Creates a new instance of the manager given a connection string

    37. 

  37. 		/// </summary>

    38. 

  38. 		/// <param name="dbType">The type of the database (SQLite, SQLServer, ...)</param>

    39. 

  39. 		/// <param name="connectionString">The connection string</param>

    40. 

  40. 		public MetadataCacheManagerSQL ( DatabaseTypes dbType, string connectionString )

    41. 

  41. 		{

    42. 

  42.             _db = new DbOperations(connectionString, dbType);

    43. 

  43. 		}

    44. 

  44.         /// <summary>

    45. 

  45.         /// Creates a new instance of the manager using a DbOperations object

    46. 

  46.         /// </summary>

    47. 

  47.         /// <param name="db">The dbOperations object</param>

    48. 

  48.         public MetadataCacheManagerSQL(DbOperations db)

    49. 

  49.         {

    50. 

  50.             _db = db;

    51. 

  51.         }

    52. 

  52. 		#endregion

    53. 

  53. 

    54. 

  54. 		private bool NumberIsBetween ( double numberToCheck, double bounds1, double bounds2, bool inclusiveAtBounds )

    55. 

  55. 		{

    56. 

  56. 			double lowerBound, upperBound;

    57. 

  57. 

    58. 

  58. 			if ( bounds1 > bounds2 )

    59. 

  59. 			{

    60. 

  60. 				lowerBound = bounds2;

    61. 

  61. 				upperBound = bounds1;

    62. 

  62. 			}

    63. 

  63. 			else

    64. 

  64. 			{

    65. 

  65. 				lowerBound = bounds1;

    66. 

  66. 				upperBound = bounds2;

    67. 

  67. 			}

    68. 

  68. 

    69. 

  69. 			if ( inclusiveAtBounds == true )

    70. 

  70. 			{

    71. 

  71. 				return (numberToCheck >= lowerBound && numberToCheck <= upperBound);

    72. 

  72. 			}

    73. 

  73. 			else

    74. 

  74. 			{

    75. 

  75. 				return (numberToCheck > lowerBound && numberToCheck < upperBound);

    76. 

  76. 			}

    77. 

  77. 		}

    78. 

  78. 

    79. 

  79. 		private bool EnvelopesIntersect ( double env1xMin, double env1xMax, double env1yMin, double env1yMax, double env2xMin, double env2xMax, double env2yMin, double env2yMax )

    80. 

  80. 		{

    81. 

  81. 			return (((NumberIsBetween ( env1xMin, env2xMin, env2xMax, true ) || NumberIsBetween ( env1xMax, env2xMin, env2xMax, true )) && (NumberIsBetween ( env1yMin, env2yMin, env2yMax, true ) || NumberIsBetween ( env1yMax, env2yMin, env2yMax, true ))) ||

    82. 

  82. 					((NumberIsBetween ( env2xMin, env1xMin, env1xMax, true ) || NumberIsBetween ( env2xMax, env1xMin, env1xMax, true )) && (NumberIsBetween ( env2yMin, env1yMin, env1yMax, true ) || NumberIsBetween ( env2yMax, env1yMin, env1yMax, true ))));

    83. 

  83. 		}

    84. 

  84. 

    85. 

  85. 		private bool PointIntersectsEnvelope ( double pointX, double pointY, double envXMin, double envXMax, double envYMin, double envYMax )

    86. 

  86. 		{

    87. 

  87. 			return (NumberIsBetween ( pointX, envXMin, envXMax, true ) && NumberIsBetween ( pointY, envYMin, envYMax, true ));

    88. 

  88. 		}

    89. 

  89. 

    90. 

  90. 		private bool DateRangesOverlap ( DateTime startDate1, DateTime endDate1, DateTime startDate2, DateTime endDate2 )

    91. 

  91. 		{

    92. 

  92. 			return (((startDate1 >= startDate2) && (startDate1 <= endDate2)) ||

    93. 

  93. 					((endDate1 >= startDate2) && (endDate1 <= endDate2)) ||

    94. 

  94. 					((startDate2 >= startDate1) && (startDate2 <= endDate1)) ||

    95. 

  95. 					((endDate2 >= startDate1) && (endDate2 <= endDate1)));

    96. 

  96. 		}

    97. 

  97. 

    98. 

  98.         

    99. 

  99. 

    100. 

  100. 		#region Public Methods

    101. 

  101. 

    102. 

  102. 

    103. 

  103. 

    104. 

  104. 		/// <summary>

    105. 

  105. 		/// Get all data services saved in the metadata cache database

    106. 

  106. 		/// </summary>

    107. 

  107. 		public IList<DataServiceInfo> GetAllServices ()

    108. 

  108. 		{

    109. 

  109. 			string sql = "SELECT * FROM DataServices";

    110. 

  110. 

    111. 

  111.             System.Data.DataTable tbl = _db.LoadTable("services", sql);

    112. 

  112. 

    113. 

  113.             IList<DataServiceInfo> services = null;

    114. 

  114. 

    115. 

  115. 			services = new List<DataServiceInfo> ();

    116. 

  116. 			if ( tbl.Rows.Count > 0 )

    117. 

  117.             {

    118. 

  118.                 foreach(System.Data.DataRow row in tbl.Rows)

    119. 

  119.                 {

    120. 

  120.                     services.Add(ServiceFromDataRow(row));

    121. 

  121.                 }

    122. 

  122.             }

    123. 

  123.             return services;

    124. 

  124. 		}

    125. 

  125. 

    126. 

  126.         /// <summary>

    127. 

  127.         /// Get data service by serviceUrl

    128. 

  128.         /// </summary>

    129. 

  129.         /// <param name="serviceURL">ServiceUrl</param>

    130. 

  130.         /// <returns>Data service or null (if not found)</returns>

    131. 

  131.         public DataServiceInfo GetServiceByServiceUrl(string serviceURL)

    132. 

  132.         {

    133. 

  133.             var sql = string.Format("SELECT * FROM DataServices where ServiceID = '{0}'", serviceURL);

    134. 

  134.             var tbl = _db.LoadTable("services", sql);

    135. 

  135.             return tbl.Rows.Count == 1 ? ServiceFromDataRow(tbl.Rows[0]) : null;

    136. 

  136.         }

    137. 

  137. 

    138. 

  138. 	    #endregion

    139. 

  139.         /// <summary>

    140. 

  140.         /// Gets all sites in box (not implemented)

    141. 

  141.         /// </summary>

    142. 

  142.         /// <param name="xMin">minimum x (longitude)</param>

    143. 

  143.         /// <param name="xMax">maximum x (lognitude)</param>

    144. 

  144.         /// <param name="yMin">minimum y (latitude)</param>

    145. 

  145.         /// <param name="yMax">maximum y (latitude)</param>

    146. 

  146. 		public void GetSitesInBox ( double xMin, double xMax, double yMin, double yMax )

    147. 

  147. 		{

    148. 

  148. 			throw new System.NotImplementedException ();

    149. 

  149. 		}

    150. 

  150.         /// <summary>

    151. 

  151.         /// Gets a list of all services within the bounding box

    152. 

  152.         /// </summary>

    153. 

  153.         /// <param name="xMin">minimum x (longitude)</param>

    154. 

  154.         /// <param name="xMax">maximum x (lognitude)</param>

    155. 

  155.         /// <param name="yMin">minimum y (latitude)</param>

    156. 

  156.         /// <param name="yMax">maximum y (latitude)</param>

    157. 

  157.         /// <returns>the list of serviceInfo objects matching the criteria</returns>

    158. 

  158. 		public IList<DataServiceInfo> GetServicesInBox ( double xMin, double xMax, double yMin, double yMax )

    159. 

  159. 		{

    160. 

  160. 			//IList<DataServiceInfo> services = null;

    161. 

  161. 

    162. 

  162.             string sql = "SELECT * FROM DataServicesCache WHERE " +

    163. 

  163.                 String.Format("EastLongitude BETWEEN {0} AND {1}", xMin, xMax) +

    164. 

  164.                 String.Format("OR WestLongitude BETWEEN {0} AND {1}", xMin, xMax) +

    165. 

  165.                 String.Format("OR NorthLatitude BETWEEN {0} AND {1}", yMin, yMax) +

    166. 

  166.                 String.Format("OR SouthLatitude BETWEEN {0} AND {1}", yMin, yMax);

    167. 

  167. 

    168. 

  168.             DataTable tbl = _db.LoadTable(sql);

    169. 

  169. 

    170. 

  170.             IList<DataServiceInfo> services = null;

    171. 

  171. 

    172. 

  172.             if (tbl.Rows.Count > 0)

    173. 

  173.             {

    174. 

  174.                 services = new List<DataServiceInfo>();

    175. 

  175.                 foreach (System.Data.DataRow row in tbl.Rows)

    176. 

  176.                 {

    177. 

  177.                     services.Add(ServiceFromDataRow(row));

    178. 

  178.                 }

    179. 

  179.             }

    180. 

  180.             return services;

    181. 

  181. 		}

    182. 

  182. 

    183. 

  183.         private string DetailedSeriesSQLQuery()

    184. 

  184.         {

    185. 

  185.             string sql = "SELECT SeriesID, " +

    186. 

  186.                 "SiteName, SiteCode, Latitude, Longitude, " +

    187. 

  187.                 "VariableName, VariableCode, DataType, ValueType, Speciation, SampleMedium, " +

    188. 

  188.                 "TimeSupport, GeneralCategory, " +

    189. 

  189.                 "TimeUnitsName, " +

    190. 

  190.                 "BeginDateTime, EndDateTime, DataSeriesCache.ValueCount, ServiceTitle, ServiceEndpointURL " +

    191. 

  191.                 "FROM DataSeriesCache " +

    192. 

  192.                 "LEFT JOIN SitesCache ON DataSeriesCache.SiteID = SitesCache.SiteID " +

    193. 

  193.                 "LEFT JOIN VariablesCache ON DataSeriesCache.VariableID = VariablesCache.VariableID " + 

    194. 

  194.                 "LEFT JOIN DataServices ON DataSeriesCache.ServiceID = DataServices.ServiceID";

    195. 

  195.             return sql;

    196. 

  196.         }

    197. 

  197. 

    198. 

  198.         private DataServiceInfo ServiceFromDataRow(System.Data.DataRow row)

    199. 

  199.         {

    200. 

  200.             DataServiceInfo dsi = new DataServiceInfo();

    201. 

  201.             dsi.Id = DataReader.ReadInteger(row["ServiceID"]);

    202. 

  202.             dsi.ServiceCode = DataReader.ReadString(row["ServiceCode"]);

    203. 

  203.             dsi.ServiceName = DataReader.ReadString(row["ServiceName"]);

    204. 

  204.             dsi.ServiceType = DataReader.ReadString(row["ServiceType"]);

    205. 

  205.             dsi.Version = DataReader.ReadDouble(row["ServiceVersion"]);

    206. 

  206.             dsi.Protocol = DataReader.ReadString(row["ServiceProtocol"]);

    207. 

  207.             dsi.EndpointURL = DataReader.ReadString(row["ServiceEndpointURL"]);

    208. 

  208.             dsi.DescriptionURL = DataReader.ReadString(row["ServiceDescriptionURL"]);

    209. 

  209.             dsi.NorthLatitude = DataReader.ReadDouble(row["NorthLatitude"]);

    210. 

  210.             dsi.SouthLatitude = DataReader.ReadDouble(row["SouthLatitude"]);

    211. 

  211.             dsi.EastLongitude = DataReader.ReadDouble(row["EastLongitude"]);

    212. 

  212.             dsi.WestLongitude = DataReader.ReadDouble(row["WestLongitude"]);

    213. 

  213.             dsi.Abstract = DataReader.ReadString(row["Abstract"]);

    214. 

  214.             dsi.ContactEmail = DataReader.ReadString(row["ContactEmail"]);

    215. 

  215.             dsi.ContactName = DataReader.ReadString(row["ContactName"]);

    216. 

  216.             dsi.Citation = DataReader.ReadString(row["Citation"]);

    217. 

  217.             dsi.IsHarvested = DataReader.ReadBoolean(row["IsHarvested"]);

    218. 

  218.             dsi.HarveDateTime = DataReader.ReadDateTime(row["HarveDateTime"]);

    219. 

  219.             dsi.ServiceTitle = DataReader.ReadString(row["ServiceTitle"]);

    220. 

  220.             return dsi;

    221. 

  221.         }

    222. 

  222. 

    223. 

  223.         private Variable VariableFromDataRow(DataRow row)

    224. 

  224.         {

    225. 

  225.             Variable v = new Variable();

    226. 

  226.             v.Name = Convert.ToString(row["VariableName"]);

    227. 

  227.             v.Code = Convert.ToString(row["VariableCode"]);

    228. 

  228.             v.DataType = Convert.ToString(row["DataType"]);

    229. 

  229.             v.ValueType = Convert.ToString(row["ValueType"]);

    230. 

  230.             v.Speciation = Convert.ToString(row["Speciation"]);

    231. 

  231.             v.SampleMedium = Convert.ToString(row["SampleMedium"]);

    232. 

  232.             v.TimeSupport = Convert.ToDouble(row["TimeSupport"]);

    233. 

  233.             v.GeneralCategory = Convert.ToString(row["GeneralCategory"]);

    234. 

  234.             v.VariableUnit = Unit.Unknown;

    235. 

  235.             v.VariableUnit.Name = Convert.ToString(row["VariableUnitsName"]);

    236. 

  236.             v.VariableUnit.Abbreviation = Convert.ToString(row["VariableUnitsAbbreviation"]);

    237. 

  237.             v.TimeUnit = Unit.UnknownTimeUnit;

    238. 

  238.             v.TimeUnit.Name = Convert.ToString(row["TimeUnitsName"]);

    239. 

  239.             v.IsRegular = Convert.ToBoolean(row["IsRegular"]);

    240. 

  240.             v.NoDataValue = Convert.ToDouble(row["NoDataValue"]);

    241. 

  241.             v.Id = Convert.ToInt32(row["VariableID"]);

    242. 

  242.             return v;

    243. 

  243.         }

    244. 

  244. 

    245. 

  245.         private SeriesMetadata SeriesFromDataRow(DataRow row)

    246. 

  246.         {

    247. 

  247.             Site site = new Site();

    248. 

  248.             site.Name = Convert.ToString(row["SiteName"]);

    249. 

  249.             site.Code = Convert.ToString(row["SiteCode"]);

    250. 

  250.             site.Latitude = Convert.ToDouble(row["Latitude"]);

    251. 

  251.             site.Longitude = Convert.ToDouble(row["Longitude"]);

    252. 

  252. 

    253. 

  253.             Variable v = new Variable();

    254. 

  254.             v.Name = Convert.ToString(row["VariableName"]);

    255. 

  255.             v.Code = Convert.ToString(row["VariableCode"]);

    256. 

  256.             v.DataType = Convert.ToString(row["DataType"]);

    257. 

  257.             v.ValueType = Convert.ToString(row["ValueType"]);

    258. 

  258.             v.Speciation = Convert.ToString(row["Speciation"]);

    259. 

  259.             v.SampleMedium = Convert.ToString(row["SampleMedium"]);

    260. 

  260.             v.TimeSupport = Convert.ToDouble(row["TimeSupport"]);

    261. 

  261.             v.GeneralCategory = Convert.ToString(row["GeneralCategory"]);

    262. 

  262.             v.VariableUnit = Unit.Unknown;

    263. 

  263.             v.VariableUnit.Name = Convert.ToString(row["VariableUnitsName"]);

    264. 

  264.             v.TimeUnit = Unit.UnknownTimeUnit;

    265. 

  265.             v.TimeUnit.Name = Convert.ToString(row["TimeUnitsName"]);

    266. 

  266. 

    267. 

  267.             Method m = Method.Unknown;

    268. 

  268.             //m.Description = Convert.ToString(row["MethodDescription"]);

    269. 

  269. 

    270. 

  270.             Source src = Source.Unknown;

    271. 

  271.             //src.Description = Convert.ToString(row["SourceDescription"]);

    272. 

  272.             src.Organization = Convert.ToString(row["Organization"]);

    273. 

  273.             src.Citation = Convert.ToString(row["Citation"]);

    274. 

  274. 

    275. 

  275.             QualityControlLevel qc = QualityControlLevel.Unknown;

    276. 

  276.             //qc.Code = Convert.ToString(row["QualityControlLevelCode"]);

    277. 

  277.             //qc.Definition = Convert.ToString(row["QualityControlLevelDefinition"]);

    278. 

  278. 

    279. 

  279.             SeriesMetadata newSeries = new SeriesMetadata(site, v, m, qc, src);

    280. 

  280.             newSeries.BeginDateTime = Convert.ToDateTime(row["BeginDateTime"]);

    281. 

  281.             newSeries.EndDateTime = Convert.ToDateTime(row["EndDateTime"]);

    282. 

  282.             newSeries.BeginDateTimeUTC = Convert.ToDateTime(row["BeginDateTimeUTC"]);

    283. 

  283.             newSeries.EndDateTimeUTC = Convert.ToDateTime(row["EndDateTimeUTC"]);

    284. 

  284.             newSeries.ValueCount = Convert.ToInt32(row["ValueCount"]);

    285. 

  285. 

    286. 

  286.             DataServiceInfo servInfo = new DataServiceInfo();

    287. 

  287.             servInfo.EndpointURL = Convert.ToString(row["ServiceEndpointURL"]);

    288. 

  288.             //servInfo.ServiceCode = Convert.ToString(row["ServiceCode"]);

    289. 

  289.             newSeries.DataService = servInfo;

    290. 

  290. 

    291. 

  291.             return newSeries;

    292. 

  292.         }

    293. 

  293. 

    294. 

  294.         /// <summary>

    295. 

  295.         /// Converts DataRow into SeriesDataCart

    296. 

  296.         /// </summary>

    297. 

  297.         /// <param name="row">DataRow to convert</param>

    298. 

  298.         /// <returns>SeriesDataCart</returns>

    299. 

  299.         public SeriesDataCart SeriesDataCartFromRow(DataRow row)

    300. 

  300.         {

    301. 

  301.             var result = new SeriesDataCart();

    302. 

  302.             result.SiteName = Convert.ToString(row["SiteName"]);

    303. 

  303.             result.SiteCode = Convert.ToString(row["SiteCode"]);

    304. 

  304.             result.Latitude = Convert.ToDouble(row["Latitude"], CultureInfo.InvariantCulture);

    305. 

  305.             result.Longitude = Convert.ToDouble(row["Longitude"], CultureInfo.InvariantCulture);

    306. 

  306. 

    307. 

  307.             //Variable v = new Variable();

    308. 

  308.             result.VariableName = Convert.ToString(row["VariableName"]);

    309. 

  309.             result.VariableCode = Convert.ToString(row["VariableCode"]);

    310. 

  310.             result.DataType = Convert.ToString(row["DataType"]);

    311. 

  311.             result.ValueType = Convert.ToString(row["ValueType"]);

    312. 

  312.             

    313. 

  313.             result.SampleMedium = Convert.ToString(row["SampleMedium"]);

    314. 

  314.             result.TimeSupport = Convert.ToDouble(row["TimeSupport"], CultureInfo.InvariantCulture);

    315. 

  315.             result.GeneralCategory = Convert.ToString(row["GeneralCategory"]);

    316. 

  316.             result.TimeUnit = Convert.ToString(row["TimeUnitsName"]);

    317. 

  317. 

    318. 

  318.             result.BeginDate = Convert.ToDateTime(row["BeginDateTime"], CultureInfo.InvariantCulture);

    319. 

  319.             result.EndDate = Convert.ToDateTime(row["EndDateTime"], CultureInfo.InvariantCulture);

    320. 

  320.             result.ValueCount = Convert.ToInt32(row["ValueCount"]);

    321. 

  321. 

    322. 

  322.             result.ServURL = Convert.ToString(row["ServiceEndpointURL"]);

    323. 

  323.             result.ServCode = Convert.ToString(row["ServiceTitle"]);

    324. 

  324. 

    325. 

  325.             return result;

    326. 

  326.         }

    327. 

  327.         /// <summary>

    328. 

  328.         /// Gets a list of all data series within the bounding box

    329. 

  329.         /// </summary>

    330. 

  330.         /// <param name="xMin">minimum X (longitude)</param>

    331. 

  331.         /// <param name="xMax">maximum X (longitude)</param>

    332. 

  332.         /// <param name="yMin">minimum Y (latitude)</param>

    333. 

  333.         /// <param name="yMax">maximum Y (latitude)</param>

    334. 

  334.         /// <returns>the list of data series metadata matching the search criteria</returns>

    335. 

  335. 		public IList<SeriesDataCart> GetSeriesListInBox ( double xMin, double xMax, double yMin, double yMax )

    336. 

  336. 		{

    337. 

  337.             string sql1 = DetailedSeriesSQLQuery();

    338. 

  338.             string sqlWhere = " WHERE Latitude > @minlat AND Latitude <= @maxlat AND Longitude > @minlon AND Longitude <= @maxlon";

    339. 

  339.             string sql = sql1 + sqlWhere;

    340. 

  340. 

    341. 

  341.             DbCommand cmd = _db.CreateCommand(sql);

    342. 

  342.             //lat, lon parameters

    343. 

  343.             _db.AddParameter(cmd, "@minlat", DbType.Double);

    344. 

  344.             _db.AddParameter(cmd, "@maxlat", DbType.Double);

    345. 

  345.             _db.AddParameter(cmd, "@minlon", DbType.Double);

    346. 

  346.             _db.AddParameter(cmd, "@maxlon", DbType.Double);

    347. 

  347. 

    348. 

  348.             cmd.Parameters[0].Value = yMin;

    349. 

  349.             cmd.Parameters[1].Value = yMax;

    350. 

  350.             cmd.Parameters[2].Value = xMin;

    351. 

  351.             cmd.Parameters[3].Value = xMax;

    352. 

  352. 

    353. 

  353.             DataTable seriesTable = _db.LoadTable("seriesTable", cmd);

    354. 

  354.             

    355. 

  355.             //DataTable seriesTable = _db.LoadTable("seriesTable", sql);

    356. 

  356. 

    357. 

  357.             IList<SeriesDataCart> lst = new List<SeriesDataCart>();

    358. 

  358.             foreach (DataRow row in seriesTable.Rows)

    359. 

  359.             {

    360. 

  360.                 SeriesDataCart newSeries = SeriesDataCartFromRow(row);

    361. 

  361.                 lst.Add(newSeries);

    362. 

  362.             }

    363. 

  363. 

    364. 

  364.             return lst;

    365. 

  365. 		}

    366. 

  366. 

    367. 

  367.         /// <summary>

    368. 

  368.         /// Gets a data table of all data series within the bounding box

    369. 

  369.         /// </summary>

    370. 

  370.         /// <param name="xMin">minimum X (longitude)</param>

    371. 

  371.         /// <param name="xMax">maximum X (longitude)</param>

    372. 

  372.         /// <param name="yMin">minimum Y (latitude)</param>

    373. 

  373.         /// <param name="yMax">maximum Y (latitude)</param>

    374. 

  374.         /// <param name="conceptCodes">array of Concept keywords</param>

    375. 

  375.         /// <param name="startDate">start date</param>

    376. 

  376.         /// <param name="endDate">end date</param>

    377. 

  377.         /// <param name="networkIDs">larray of service codes</param>

    378. 

  378.         /// <returns>the list of data series metadata matching the search criteria</returns>

    379. 

  379. 		public DataTable GetSeriesDataTableInBox ( double xMin, double xMax, double yMin, double yMax, string[] conceptCodes, DateTime startDate, DateTime endDate, int[] networkIDs )

    380. 

  380. 		{

    381. 

  381.             string sql1 = DetailedSeriesSQLQuery();

    382. 

  382.             string sqlWhere1 = " WHERE Latitude > @minlat AND Latitude < @maxlat AND Longitude > @minlon AND Longitude < @maxlon";

    383. 

  383.             string sqlWhere2 = "";

    384. 

  384. 

    385. 

  385.             //concept keywords | variable names

    386. 

  386.             if (conceptCodes == null)

    387. 

  387.             {

    388. 

  388.                 sqlWhere2 = "";

    389. 

  389.             }

    390. 

  390.             else if (conceptCodes.Length == 0)

    391. 

  391.             {

    392. 

  392.                 sqlWhere2 = "";

    393. 

  393.             }

    394. 

  394.             else if (string.IsNullOrEmpty(conceptCodes[0]))

    395. 

  395.             {

    396. 

  396.                 sqlWhere2 = "";

    397. 

  397.             }

    398. 

  398.             else if (conceptCodes.Length == 1)

    399. 

  399.             {

    400. 

  400.                 sqlWhere2 = " AND VariableName = '" + conceptCodes[0] + "'";

    401. 

  401.             }

    402. 

  402.             else if (conceptCodes.Length > 1)

    403. 

  403.             {

    404. 

  404.                 sqlWhere2 = " AND VariableName IN (";

    405. 

  405.                 foreach (string keyword in conceptCodes)

    406. 

  406.                 {

    407. 

  407.                     sqlWhere2 += "'" + keyword + "',";

    408. 

  408.                 }

    409. 

  409.                 if (sqlWhere2.EndsWith(","))

    410. 

  410.                 {

    411. 

  411.                     sqlWhere2 = sqlWhere2.Substring(0, sqlWhere2.Length - 1);

    412. 

  412.                 }

    413. 

  413.                 sqlWhere2 += ")";

    414. 

  414.             }

    415. 

  415. 

    416. 

  416.             //date and time

    417. 

  417.             string sqlWhere3 = " AND ( (BeginDateTime < @p1 AND EndDateTime > @p2) OR (BeginDateTime > @p1 AND BeginDateTime <= @p2) OR (EndDateTime > @p1 AND EndDateTime <= @p2) )";

    418. 

  418.             

    419. 

  419.             //network IDs

    420. 

  420.             string sqlWhere4 = "";

    421. 

  421. 

    422. 

  422.             if (networkIDs != null)

    423. 

  423.             {

    424. 

  424.                 if (networkIDs.Length == 1)

    425. 

  425.                 {

    426. 

  426.                     sqlWhere4 = " AND DataSeriesCache.ServiceID = " + networkIDs[0];

    427. 

  427.                 }

    428. 

  428.                 else if (networkIDs.Length > 1)

    429. 

  429.                 {

    430. 

  430.                     sqlWhere4 = " AND DataSeriesCache.ServiceID IN (";

    431. 

  431.                     foreach (int servID in networkIDs)

    432. 

  432.                     {

    433. 

  433.                         sqlWhere4 += servID.ToString() + ",";

    434. 

  434.                     }

    435. 

  435.                     if (sqlWhere4.EndsWith(","))

    436. 

  436.                     {

    437. 

  437.                         sqlWhere4 = sqlWhere4.Substring(0, sqlWhere4.Length - 1);

    438. 

  438.                     }

    439. 

  439.                     sqlWhere4 += ")";

    440. 

  440.                 }

    441. 

  441.             }

    442. 

  442. 

    443. 

  443.             string sql = sql1 + sqlWhere1 + sqlWhere2 + sqlWhere3 + sqlWhere4;

    444. 

  444.             DbCommand cmd = _db.CreateCommand(sql);

    445. 

  445.             //lat, lon parameters

    446. 

  446.             _db.AddParameter(cmd, "@minlat", DbType.Double);

    447. 

  447.             _db.AddParameter(cmd, "@maxlat", DbType.Double);

    448. 

  448.             _db.AddParameter(cmd, "@minlon", DbType.Double);

    449. 

  449.             _db.AddParameter(cmd, "@maxlon", DbType.Double);

    450. 

  450.             

    451. 

  451.             _db.AddParameter(cmd, "@p1", DbType.DateTime);

    452. 

  452.             _db.AddParameter(cmd, "@p2", DbType.DateTime);

    453. 

  453.             cmd.Parameters[0].Value = yMin;

    454. 

  454.             cmd.Parameters[1].Value = yMax;

    455. 

  455.             cmd.Parameters[2].Value = xMin;

    456. 

  456.             cmd.Parameters[3].Value = xMax;

    457. 

  457.             cmd.Parameters[4].Value = startDate;

    458. 

  458.             cmd.Parameters[5].Value = endDate;

    459. 

  459.             

    460. 

  460.             var seriesTable = _db.LoadTable("seriesTable", cmd);

    461. 

  461.             return seriesTable;

    462. 

  462. 		}

    463. 

  463. 

    464. 

  464.         /// <summary>

    465. 

  465.         /// Gets a list of all data series within the bounding box

    466. 

  466.         /// </summary>

    467. 

  467.         /// <param name="xMin">minimum X (longitude)</param>

    468. 

  468.         /// <param name="xMax">maximum X (longitude)</param>

    469. 

  469.         /// <param name="yMin">minimum Y (latitude)</param>

    470. 

  470.         /// <param name="yMax">maximum Y (latitude)</param>

    471. 

  471.         /// <param name="conceptCodes">array of Concept keywords</param>

    472. 

  472.         /// <param name="startDate">start date</param>

    473. 

  473.         /// <param name="endDate">end date</param>

    474. 

  474.         /// <param name="networkIDs">larray of service codes</param>

    475. 

  475.         /// <returns>the list of data series metadata matching the search criteria</returns>

    476. 

  476.         public IList<SeriesDataCart> GetSeriesListInBox(double xMin, double xMax, double yMin, double yMax, string[] conceptCodes, DateTime startDate, DateTime endDate, int[] networkIDs)

    477. 

  477.         {

    478. 

  478.             var dt = GetSeriesDataTableInBox(xMin, xMax, yMin, yMax, conceptCodes, startDate, endDate, networkIDs);

    479. 

  479.             return (from DataRow row in dt.Rows select SeriesDataCartFromRow(row)).ToList();

    480. 

  480.         }

    481. 

  481. 

    482. 

  482. 	    /// <summary>

    483. 

  483. 		/// Gets all variables that are currently stored in the metadata cache database

    484. 

  484. 		/// </summary>

    485. 

  485. 		public IList<Variable> GetVariables()

    486. 

  486. 		{

    487. 

  487.             string sql = "SELECT * FROM VariablesCache";

    488. 

  488. 

    489. 

  489.             DataTable tbl = _db.LoadTable(sql);

    490. 

  490.             List<Variable> variables = new List<Variable>();

    491. 

  491.             foreach (DataRow row in tbl.Rows)

    492. 

  492.             {

    493. 

  493.                 Variable v = VariableFromDataRow(row);

    494. 

  494.                 variables.Add(v);

    495. 

  495.             }

    496. 

  496.             return variables;

    497. 

  497. 		}

    498. 

  498. 

    499. 

  499.         /// <summary>

    500. 

  500.         /// Gets all variables that are currently stored in the metadata cache database

    501. 

  501.         /// </summary>

    502. 

  502.         public IList<Variable> GetVariablesByService(int serviceID)

    503. 

  503.         {

    504. 

  504.             string sql = "SELECT * FROM VariablesCache WHERE serviceID=" + serviceID;

    505. 

  505. 

    506. 

  506.             DataTable tbl = _db.LoadTable(sql);

    507. 

  507.             List<Variable> variables = new List<Variable>();

    508. 

  508.             foreach (DataRow row in tbl.Rows)

    509. 

  509.             {

    510. 

  510.                 Variable v = VariableFromDataRow(row);

    511. 

  511.                 variables.Add(v);

    512. 

  512.             }

    513. 

  513.             return variables;

    514. 

  514.         }

    515. 

  515. 

    516. 

  516.         /// <summary>

    517. 

  517.         /// Gets the names of all variables accessible by the specific web service

    518. 

  518.         /// </summary>

    519. 

  519.         /// <returns></returns>

    520. 

  520.         public IList<string> GetVariableNamesByService(int serviceID)

    521. 

  521.         {

    522. 

  522.             string sql = "SELECT DISTINCT VariableName FROM VariablesCache WHERE ServiceID = " + serviceID;

    523. 

  523.             DataTable tbl = _db.LoadTable(sql);

    524. 

  524.             List<string> variableNames = new List<string>();

    525. 

  525.             foreach (DataRow row in tbl.Rows)

    526. 

  526.             {

    527. 

  527.                 variableNames.Add(row["VariableName"].ToString());

    528. 

  528.             }

    529. 

  529.             return variableNames;

    530. 

  530.         }

    531. 

  531. 

    532. 

  532.         /// <summary>

    533. 

  533.         /// Gets the names of all variables that are currently stored in the metadata cache DB

    534. 

  534.         /// </summary>

    535. 

  535.         /// <returns></returns>

    536. 

  536.         public IList<string> GetVariableNames()

    537. 

  537.         {

    538. 

  538.             string sql = "SELECT DISTINCT VariableName FROM VariablesCache";

    539. 

  539.             DataTable tbl = _db.LoadTable(sql);

    540. 

  540.             List<string> variableNames = new List<string>();

    541. 

  541.             foreach (DataRow row in tbl.Rows)

    542. 

  542.             {

    543. 

  543.                 variableNames.Add(row["VariableName"].ToString());

    544. 

  544.             }

    545. 

  545.             return variableNames;

    546. 

  546.         }

    547. 

  547. 

    548. 

  548. 		/// <summary>

    549. 

  549. 		/// Saves a new data service object to the database. If an entry with the same

    550. 

  550. 		/// web service URL already exists in the database, update it.

    551. 

  551. 		/// </summary>

    552. 

  552. 		/// <param name="service">the ServiceInfo object to be saved to the DB</param>

    553. 

  553. 		public void SaveDataService ( DataServiceInfo service )

    554. 

  554. 		{

    555. 

  555.             string sqlInsert =

    556. 

  556.                 "INSERT INTO DataServices(" +

    557. 

  557.                 "ServiceCode, ServiceName, ServiceType, ServiceVersion, ServiceProtocol, " +

    558. 

  558.                 "ServiceEndpointURL, ServiceDescriptionURL, NorthLatitude, SouthLatitude, EastLongitude, WestLongitude, " +

    559. 

  559.                 "Abstract, ContactName, ContactEmail, Citation, IsHarvested, HarveDateTime, ServiceTitle) " +

    560. 

  560.                 "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    561. 

  561.             

    562. 

  562.             using (DbConnection conn = _db.CreateConnection())

    563. 

  563.             {

    564. 

  564.                 conn.Open();

    565. 

  565. 

    566. 

  566.                 using (DbTransaction tran = conn.BeginTransaction())

    567. 

  567.                 {

    568. 

  568.                     using (DbCommand cmd = conn.CreateCommand())

    569. 

  569.                     {

    570. 

  570.                         cmd.CommandText = sqlInsert;

    571. 

  571.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ServiceCode));

    572. 

  572.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ServiceName));

    573. 

  573.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ServiceType));

    574. 

  574.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Double, service.Version));

    575. 

  575.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.Protocol));

    576. 

  576.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.EndpointURL));

    577. 

  577.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.DescriptionURL));

    578. 

  578.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Double, service.NorthLatitude));

    579. 

  579.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Double, service.SouthLatitude));

    580. 

  580.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Double, service.EastLongitude));

    581. 

  581.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Double, service.WestLongitude));

    582. 

  582.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.Abstract));

    583. 

  583.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ContactName));

    584. 

  584.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ContactEmail));

    585. 

  585.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.Citation));

    586. 

  586.                         cmd.Parameters.Add(_db.CreateParameter(DbType.Boolean, service.IsHarvested));

    587. 

  587.                         cmd.Parameters.Add(_db.CreateParameter(DbType.DateTime, service.HarveDateTime));

    588. 

  588.                         cmd.Parameters.Add(_db.CreateParameter(DbType.String, service.ServiceTitle));

    589. 

  589. 

    590. 

  590.                         cmd.ExecuteNonQuery();

    591. 

  591.                     }

    592. 

  592.                     tran.Commit();

    593. 

  593.                 }

    594. 

  594.             }

    595. 

  595. 		}

    596. 

  596. 

    597. 

  597. 		/// <summary>

    598. 

  598. 		/// Deletes all entries in the metadata cache database that were

    599. 

  599. 		/// added by the data service

    600. 

  600. 		/// </summary>

    601. 

  601.         /// <param name="service">The serviceInfo object to be deleted</param>

    602. 

  602. 		/// <param name="deleteService">Set to true if the record in the DataServices

    603. 

  603. 		/// table should also be deleted. Set to false if the record in the DataServices

    604. 

  604. 		/// table should be kept</param>

    605. 

  605. 		/// <returns>The total number of records deleted</returns>

    606. 

  606. 		public int DeleteRecordsForService ( DataServiceInfo service, bool deleteService )

    607. 

  607. 		{

    608. 

  608.             string serviceID = service.Id.ToString();

    609. 

  609. 

    610. 

  610.             string sqlDelete = "DELETE FROM DataSeriesCache WHERE ServiceID = " + serviceID + "; " +

    611. 

  611.                 "DELETE FROM SitesCache WHERE ServiceID = " + serviceID + "; " +

    612. 

  612.                 "DELETE FROM VariablesCache WHERE ServiceID = " + serviceID + "; " +

    613. 

  613.                 "DELETE FROM SourcesCache WHERE ServiceID = " + serviceID + "; " +

    614. 

  614.                 "DELETE FROM MethodsCache WHERE ServiceID = " + serviceID + "; " +

    615. 

  615.                 "DELETE FROM QualityControlLevelsCache WHERE ServiceID = " + serviceID + ";" + 

    616. 

  616. 				"DELETE FROM ISOMetadataCache WHERE ServiceID = " + serviceID + ";";

    617. 

  617. 

    618. 

  618. 			if ( deleteService == true )

    619. 

  619. 			{

    620. 

  620. 				sqlDelete += "DELETE FROM DataServices WHERE ServiceID = " + serviceID + ";";

    621. 

  621. 			}

    622. 

  622. 

    623. 

  623.             using (DbConnection conn = _db.CreateConnection())

    624. 

  624.             {

    625. 

  625.                 conn.Open();

    626. 

  626. 

    627. 

  627.                 using (DbTransaction tran = conn.BeginTransaction())

    628. 

  628.                 {

    629. 

  629.                     using (DbCommand cmd01 = conn.CreateCommand())

    630. 

  630.                     {

    631. 

  631.                         cmd01.CommandText = sqlDelete;

    632. 

  632.                         cmd01.ExecuteNonQuery();

    633. 

  633.                     }

    634. 

  634.                     tran.Commit();

    635. 

  635.                 }

    636. 

  636. 

    637. 

  637.             }

    638. 

  638.             return 0;

    639. 

  639. 		}

    640. 

  640. 

    641. 

  641. 

    642. 

  642.         /// <summary>

    643. 

  643.         /// Deletes a series given it's ID. The series is only deleted when it belongs to one theme.

    644. 

  644.         /// </summary>

    645. 

  645.         /// <param name="seriesID">The database ID of the series</param>

    646. 

  646.         /// <returns>true if series was deleted, false otherwise</returns>

    647. 

  647.         public bool DeleteSeries(int seriesID)

    648. 

  648.         {

    649. 

  649.             int siteID = 0;

    650. 

  650.             int variableID = 0;

    651. 

  651.             int sourceID = 0;

    652. 

  652.             int qualityID = 0;

    653. 

  653.             int methodID = 0;

    654. 

  654. 

    655. 

  655. 

    656. 

  656.             string sqlSeries = "SELECT SiteID, VariableID, MethodID, SourceID, QualityControlLevelID " +

    657. 

  657.                 "FROM DataSeriesCache WHERE SeriesID = " + seriesID;

    658. 

  658. 

    659. 

  659.             DataTable seriesTable = _db.LoadTable("seriesTable", sqlSeries);

    660. 

  660.             

    661. 

  661.             if (seriesTable.Rows.Count == 0) return false;

    662. 

  662. 

    663. 

  663.             DataRow seriesRow = seriesTable.Rows[0];

    664. 

  664.             siteID = Convert.ToInt32(seriesRow["SiteID"]);

    665. 

  665.             variableID = Convert.ToInt32(seriesRow["VariableID"]);

    666. 

  666.             methodID = Convert.ToInt32(seriesRow["MethodID"]);

    667. 

  667.             sourceID = Convert.ToInt32(seriesRow["SourceID"]);

    668. 

  668.             qualityID = Convert.ToInt32(seriesRow["QualityControlLevelID"]);

    669. 

  669. 

    670. 

  670.             //SQL Queries

    671. 

  671.             string sqlSite = "SELECT SiteID from DataSeriesCache where SiteID = " + siteID;

    672. 

  672.             string sqlVariable = "SELECT VariableID from DataSeriesCache where VariableID = " + variableID;

    673. 

  673.             string sqlSource = "SELECT SourceID from DataSeriesCache where SourceID = " + sourceID;

    674. 

  674.             string sqlMethod = "SELECT MethodID from DataSeriesCache where MethodID = " + methodID;

    675. 

  675.             string sqlQuality = "SELECT QualityControlLevelID from DataSeriesCache where QualityControlLevelID = " + qualityID;

    676. 

  676. 

    677. 

  677. 

    678. 

  678.             //SQL Delete Commands

    679. 

  679.             string sqlDeleteSeries = "DELETE FROM DataSeriesCache WHERE SeriesID = " + seriesID;

    680. 

  680. 

    681. 

  681.             string sqlDeleteSite = "DELETE FROM SitesCache WHERE SiteID = " + siteID;

    682. 

  682.             string sqlDeleteVariable = "DELETE FROM VariablesCache WHERE VariableID = " + variableID;

    683. 

  683.             string sqlDeleteMethod = "DELETE FROM MethodsCache WHERE MethodID = " + methodID;

    684. 

  684.             string sqlDeleteSource = "DELETE FROM SourcesCache WHERE SourceID = " + sourceID;

    685. 

  685.             string sqlDeleteQuality = "DELETE FROM QualityControlLevelsCache WHERE QualityControlLevelID = " + qualityID;

    686. 

  686. 

    687. 

  687.             DataTable tblSite = new DataTable();

    688. 

  688.             DataTable tblVariable = new DataTable();

    689. 

  689.             DataTable tblSource = new DataTable();

    690. 

  690.             DataTable tblMethod = new DataTable();

    691. 

  691.             DataTable tblQuality = new DataTable();

    692. 

  692. 

    693. 

  693.             //Begin Transaction

    694. 

  694.             using (DbConnection conn = _db.CreateConnection())

    695. 

  695.             {

    696. 

  696.                 conn.Open();

    697. 

  697. 

    698. 

  698.                 using (DbTransaction tran = conn.BeginTransaction())

    699. 

  699.                 {

    700. 

  700.                     // get site IDs

    701. 

  701.                     using (DbCommand cmd01 = conn.CreateCommand())

    702. 

  702.                     {

    703. 

  703.                         cmd01.CommandText = sqlSite;

    704. 

  704.                         tblSite = _db.LoadTable("t1", cmd01);

    705. 

  705.                     }

    706. 

  706. 

    707. 

  707.                     // get variable IDs

    708. 

  708.                     using (DbCommand cmd02 = conn.CreateCommand())

    709. 

  709.                     {

    710. 

  710.                         cmd02.CommandText = sqlVariable;

    711. 

  711.                         tblVariable = _db.LoadTable("t2", cmd02);

    712. 

  712.                     }

    713. 

  713. 

    714. 

  714.                     // get source IDs

    715. 

  715.                     using (DbCommand cmd03 = conn.CreateCommand())

    716. 

  716.                     {

    717. 

  717.                         cmd03.CommandText = sqlSource;

    718. 

  718.                         tblSource = _db.LoadTable("t3", cmd03);

    719. 

  719.                     }

    720. 

  720. 

    721. 

  721.                     // get method IDs

    722. 

  722.                     using (DbCommand cmd04 = conn.CreateCommand())

    723. 

  723.                     {

    724. 

  724.                         cmd04.CommandText = sqlMethod;

    725. 

  725.                         tblMethod = _db.LoadTable("t4", cmd04);

    726. 

  726.                     }

    727. 

  727. 

    728. 

  728.                     // get qualityControl IDs

    729. 

  729.                     using (DbCommand cmd05 = conn.CreateCommand())

    730. 

  730.                     {

    731. 

  731.                         cmd05.CommandText = sqlQuality;

    732. 

  732.                         tblQuality = _db.LoadTable("t5", cmd05);

    733. 

  733.                     }

    734. 

  734. 

    735. 

  735.                     //delete the site

    736. 

  736.                     if (tblSite.Rows.Count == 1)

    737. 

  737.                     {

    738. 

  738.                         using (DbCommand cmdDeleteSite = conn.CreateCommand())

    739. 

  739.                         {

    740. 

  740.                             cmdDeleteSite.CommandText = sqlDeleteSite;

    741. 

  741.                             cmdDeleteSite.ExecuteNonQuery();

    742. 

  742.                         }

    743. 

  743.                     }

    744. 

  744. 

    745. 

  745.                     //delete the variable

    746. 

  746.                     if (tblVariable.Rows.Count == 1)

    747. 

  747.                     {

    748. 

  748.                         using (DbCommand cmdDeleteVariable = conn.CreateCommand())

    749. 

  749.                         {

    750. 

  750.                             cmdDeleteVariable.CommandText = sqlDeleteVariable;

    751. 

  751.                             cmdDeleteVariable.ExecuteNonQuery();

    752. 

  752.                         }

    753. 

  753.                     }

    754. 

  754. 

    755. 

  755.                     //delete the method

    756. 

  756.                     if (tblMethod.Rows.Count == 1)

    757. 

  757.                     {

    758. 

  758.                         using (DbCommand cmdDeleteMethod = conn.CreateCommand())

    759. 

  759.                         {

    760. 

  760.                             cmdDeleteMethod.CommandText = sqlDeleteMethod;

    761. 

  761.                             cmdDeleteMethod.ExecuteNonQuery();

    762. 

  762.                         }

    763. 

  763.                     }

    764. 

  764. 

    765. 

  765.                     //delete the source

    766. 

  766.                     if (tblSource.Rows.Count == 1)

    767. 

  767.                     {

    768. 

  768.                         using (DbCommand cmdDeleteSource = conn.CreateCommand())

    769. 

  769.                         {

    770. 

  770.                             cmdDeleteSource.CommandText = sqlDeleteSource;

    771. 

  771.                             cmdDeleteSource.ExecuteNonQuery();

    772. 

  772.                         }

    773. 

  773.                     }

    774. 

  774. 

    775. 

  775.                     //delete the quality control level

    776. 

  776.                     if (tblQuality.Rows.Count == 1)

    777. 

  777.                     {

    778. 

  778.                         using (DbCommand cmdDeleteQuality = conn.CreateCommand())

    779. 

  779.                         {

    780. 

  780.                             cmdDeleteQuality.CommandText = sqlDeleteQuality;

    781. 

  781.                             cmdDeleteQuality.ExecuteNonQuery();

    782. 

  782.                         }

    783. 

  783.                     }  

    784. 

  784. 

    785. 

  785.                     //finally delete the series

    786. 

  786.                     using (DbCommand cmdDeleteSeries = conn.CreateCommand())

    787. 

  787.                     {

    788. 

  788.                         cmdDeleteSeries.CommandText = sqlDeleteSeries;

    789. 

  789.                         cmdDeleteSeries.ExecuteNonQuery();

    790. 

  790.                     }

    791. 

  791. 

    792. 

  792.                     //commit transaction

    793. 

  793.                     tran.Commit();

    794. 

  794.                 }

    795. 

  795.             }

    796. 

  796.             return true;

    797. 

  797.         }

    798. 

  798. 

    799. 

  799. 

    800. 

  800. 

    801. 

  801. 		/// <summary>

    802. 

  802. 		/// Check if the series with the same site, variable, method,

    803. 

  803. 		/// source, quality control level and data service already

    804. 

  804. 		/// exists in the database.

    805. 

  805. 		/// </summary>

    806. 

  806. 		/// <param name="seriesToCheck">the series to be checked</param>

    807. 

  807. 		/// <returns>The series from the db, or NULL if it doesn't exist</returns>

    808. 

  808.         /// <remarks>Not implemented</remarks>

    809. 

  809. 		private SeriesMetadata CheckIfSeriesExists ( SeriesMetadata seriesToCheck )

    810. 

  810. 		{

    811. 

  811.             throw new NotImplementedException();

    812. 

  812. 		}

    813. 

  813. 

    814. 

  814. 		/// <summary>

    815. 

  815. 		/// Saves the series metadata to the metadata cache database.

    816. 

  816. 		/// This method also automatically saves the site, variable,

    817. 

  817. 		/// method, source and quality control level of the series.

    818. 

  818. 		/// </summary>

    819. 

  819. 		/// <param name="series">The series to be saved</param>

    820. 

  820. 		/// <param name="dataService">The web service containing the series</param>

    821. 

  821. 		public void SaveSeries ( SeriesMetadata series, DataServiceInfo dataService )

    822. 

  822. 		{

    823. 

  823.             string sqlSite = "SELECT SiteID FROM SitesCache WHERE SiteCode = ?";

    824. 

  824.             string sqlVariable = "SELECT VariableID FROM VariablesCache WHERE VariableCode = ?";            

    825. 

  825.             string sqlMethod = "SELECT MethodID FROM MethodsCache WHERE MethodDescription = ?";

    826. 

  826.             string sqlSource = "SELECT SourceID FROM SourcesCache WHERE Organization = ?";          

    827. 

  827.             string sqlQuality = "SELECT QualityControlLevelID FROM QualityControlLevelsCache WHERE Definition = ?";           

    828. 

  828.             string sqlRowID = "; SELECT LAST_INSERT_ROWID();";

    829. 

  829.             

    830. 

  830.             string sqlSaveSite = "INSERT INTO SitesCache(SiteCode, SiteName, Latitude, Longitude, LatLongDatumSRSID, LatLongDatumName, " +

    831. 

  831.                 "Elevation_m, VerticalDatum, LocalX, LocalY, LocalProjectionSRSID, LocalProjectionName, " +

    832. 

  832.                 "PosAccuracy_m, State, County, Comments, ServiceID) " +

    833. 

  833.                 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

    834. 

  834. 

    835. 

  835.             string sqlSaveVariable = "INSERT INTO variablesCache(VariableCode, VariableName, Speciation, " +

    836. 

  836.                 "SampleMedium, ValueType, DataType, GeneralCategory, NoDataValue, VariableUnitsName, VariableUnitsType, VariableUnitsAbbreviation, " +

    837. 

  837.                 "IsRegular, TimeSupport, TimeUnitsName, TimeUnitsType, TimeUnitsAbbreviation, ServiceID) " +

    838. 

  838.                 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

    839. 

  839. 

    840. 

  840.             string sqlSaveMethod = "INSERT INTO MethodsCache(OriginMethodID, MethodDescription, MethodLink, ServiceID) VALUES(?, ?, ?, ?)" + sqlRowID;

    841. 

  841. 

    842. 

  842.             string sqlSaveQualityControl = "INSERT INTO QualityControlLevelsCache(OriginQualityControlLevelID, QualityControlLevelCode, Definition, Explanation) " +

    843. 

  843.                 "VALUES(?,?,?,?)" + sqlRowID;

    844. 

  844. 

    845. 

  845.             string sqlSaveSource = "INSERT INTO SourcesCache(OriginSourceID, Organization, SourceDescription, SourceLink, ContactName, Phone, " +

    846. 

  846.                                    "Email, Address, City, State, ZipCode, Citation, MetadataID) " +

    847. 

  847.                                    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

    848. 

  848. 

    849. 

  849.             string sqlSaveISOMetadata = "INSERT INTO ISOMetadataCache(TopicCategory, Title, Abstract, ProfileVersion, MetadataLink) " +

    850. 

  850.                                     "VALUES(?, ?, ?, ?, ?)" + sqlRowID;

    851. 

  851. 

    852. 

  852.             string sqlSaveSeries = "INSERT INTO DataSeriesCache(SiteID, VariableID, MethodID, SourceID, QualityControlLevelID, " +

    853. 

  853.                 "BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ValueCount, ServiceID) " +

    854. 

  854.                 "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

    855. 

  855. 

    856. 

  856.             int siteID = 0;

    857. 

  857.             int variableID = 0;

    858. 

  858.             

    859. 

  859.             int methodID = 0;

    860. 

  860.             int qualityControlLevelID = 0;

    861. 

  861.             int sourceID = 0;

    862. 

  862.             int isoMetadataID = 0;

    863. 

  863.             int seriesID = 0;

    864. 

  864.             

    865. 

  865. 

    866. 

  866.             object siteIDResult = null;

    867. 

  867.            

    868. 

  868.             object variableIDResult = null;

    869. 

  869.             

    870. 

  870.             object methodIDResult = null;

    871. 

  871.             object qualityControlLevelIDResult = null;

    872. 

  872.             object sourceIDResult = null;

    873. 

  873.             

    874. 

  874.             object seriesIDResult = null;

    875. 

  875.             

    876. 

  876.             //check the ServiceID (must be already set)

    877. 

  877.             if (dataService.Id <= 0)

    878. 

  878.             {

    879. 

  879.                 throw new ArgumentException("The DataServiceID must be set.");

    880. 

  880.             }

    881. 

  881. 

    882. 

  882. 

    883. 

  883.             //Step 1 Begin Transaction

    884. 

  884.             using (DbConnection conn = _db.CreateConnection())

    885. 

  885.             {

    886. 

  886.                 conn.Open();

    887. 

  887. 

    888. 

  888.                 using (DbTransaction tran = conn.BeginTransaction())

    889. 

  889.                 {

    890. 

  890.                     //****************************************************************

    891. 

  891.                     //*** Step 2 Site

    892. 

  892.                     //****************************************************************

    893. 

  893.                     using (DbCommand cmd01 = conn.CreateCommand())

    894. 

  894.                     {

    895. 

  895.                         cmd01.CommandText = sqlSite;

    896. 

  896.                         cmd01.Parameters.Add(_db.CreateParameter(DbType.String, series.Site.Code));

    897. 

  897.                         siteIDResult = cmd01.ExecuteScalar();

    898. 

  898.                         if (siteIDResult != null)

    899. 

  899.                         {

    900. 

  900.                             siteID = Convert.ToInt32(siteIDResult);

    901. 

  901.                         }

    902. 

  902.                     }

    903. 

  903. 

    904. 

  904.                     if (siteID == 0) //New Site needs to be created

    905. 

  905.                     {

    906. 

  906.                         //Insert the site to the database

    907. 

  907.                         using (DbCommand cmd04 = conn.CreateCommand())

    908. 

  908.                         {

    909. 

  909.                             Site site = series.Site;

    910. 

  910. 

    911. 

  911.                             cmd04.CommandText = sqlSaveSite;

    912. 

  912.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Code));

    913. 

  913.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Name));

    914. 

  914.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Latitude));

    915. 

  915.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Longitude));

    916. 

  916.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, site.SpatialReference.SRSID));

    917. 

  917.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.SpatialReference.SRSName));

    918. 

  918.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Elevation_m));

    919. 

  919.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.VerticalDatum));

    920. 

  920.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.LocalX));

    921. 

  921.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.LocalY));

    922. 

  922.                             if (site.LocalProjection != null)

    923. 

  923.                             {

    924. 

  924.                                 cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, site.LocalProjection.SRSID));

    925. 

  925.                                 cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.LocalProjection.SRSName));

    926. 

  926.                             }

    927. 

  927.                             else

    928. 

  928.                             {

    929. 

  929.                                 cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, 0));

    930. 

  930.                                 cmd04.Parameters.Add(_db.CreateParameter(DbType.String, "unknown"));

    931. 

  931.                             }

    932. 

  932.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.PosAccuracy_m));

    933. 

  933.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.State));

    934. 

  934.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.County));

    935. 

  935.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Comments));

    936. 

  936.                             cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));

    937. 

  937. 

    938. 

  938.                             siteIDResult = cmd04.ExecuteScalar();

    939. 

  939.                             siteID = Convert.ToInt32(siteIDResult);

    940. 

  940.                         }

    941. 

  941.                     }

    942. 

  942. 

    943. 

  943.                     //****************************************************************

    944. 

  944.                     //*** Step 3 Variable

    945. 

  945.                     //****************************************************************

    946. 

  946.                     Variable variable = series.Variable;

    947. 

  947. 

    948. 

  948.                     using (DbCommand cmd05 = conn.CreateCommand())

    949. 

  949.                     {

    950. 

  950.                         cmd05.CommandText = sqlVariable;

    951. 

  951.                         cmd05.Parameters.Add(_db.CreateParameter(DbType.String, variable.Code));

    952. 

  952.                         cmd05.Parameters[0].Value = variable.Code;

    953. 

  953.                         variableIDResult = cmd05.ExecuteScalar();

    954. 

  954.                         if (variableIDResult != null)

    955. 

  955.                         {

    956. 

  956.                             variableID = Convert.ToInt32(variableIDResult);

    957. 

  957.                         }

    958. 

  958.                     }

    959. 

  959. 

    960. 

  960.                     if (variableID == 0) //New variable needs to be created

    961. 

  961.                     {

    962. 

  962.                         //Insert the variable to the database

    963. 

  963.                         using (DbCommand cmd09 = conn.CreateCommand())

    964. 

  964.                         {

    965. 

  965.                             cmd09.CommandText = sqlSaveVariable;

    966. 

  966.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Code));

    967. 

  967.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Name));

    968. 

  968.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Speciation));

    969. 

  969.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.SampleMedium));

    970. 

  970.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.ValueType));

    971. 

  971.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.DataType));

    972. 

  972.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.GeneralCategory));

    973. 

  973.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.Double, variable.NoDataValue));

    974. 

  974.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.Name));

    975. 

  975.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.UnitsType));

    976. 

  976.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.Abbreviation));

    977. 

  977.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.Boolean, variable.IsRegular));

    978. 

  978.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.Double, variable.TimeSupport));

    979. 

  979.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.Name));

    980. 

  980.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.UnitsType));

    981. 

  981.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.Abbreviation));

    982. 

  982. 

    983. 

  983.                             cmd09.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));

    984. 

  984. 

    985. 

  985.                             variableIDResult = cmd09.ExecuteScalar();

    986. 

  986.                             variableID = Convert.ToInt32(variableIDResult);

    987. 

  987.                         }

    988. 

  988.                     }

    989. 

  989. 

    990. 

  990.                     //****************************************************************

    991. 

  991.                     //*** Step 4 Method

    992. 

  992.                     //****************************************************************

    993. 

  993.                     Method method = series.Method;

    994. 

  994. 

    995. 

  995.                     using (DbCommand cmd10 = conn.CreateCommand())

    996. 

  996.                     {

    997. 

  997.                         cmd10.CommandText = sqlMethod;

    998. 

  998.                         cmd10.Parameters.Add(_db.CreateParameter(DbType.String, method.Description));

    999. 

  999.                         methodIDResult = cmd10.ExecuteScalar();

    1000. 

  1000.                         if (methodIDResult != null)

    1001. 

  1001.                         {

    1002. 

  1002.                             methodID = Convert.ToInt32(methodIDResult);

    1003. 

  1003.                         }

    1004. 

  1004.                     }

    1005. 

  1005. 

    1006. 

  1006.                     if (methodID == 0)

    1007. 

  1007.                     {

    1008. 

  1008.                         using (DbCommand cmd11 = conn.CreateCommand())

    1009. 

  1009.                         {

    1010. 

  1010.                             cmd11.CommandText = sqlSaveMethod;

    1011. 

  1011.                             cmd11.Parameters.Add(_db.CreateParameter(DbType.Int32, method.Code));

    1012. 

  1012.                             cmd11.Parameters.Add(_db.CreateParameter(DbType.String, method.Description));

    1013. 

  1013.                             cmd11.Parameters.Add(_db.CreateParameter(DbType.String, method.Link));

    1014. 

  1014.                             cmd11.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));

    1015. 

  1015.                             methodIDResult = cmd11.ExecuteScalar();

    1016. 

  1016.                             methodID = Convert.ToInt32(methodIDResult);

    1017. 

  1017.                         }

    1018. 

  1018.                     }

    1019. 

  1019. 

    1020. 

  1020.                     //****************************************************************

    1021. 

  1021.                     //*** Step 5 Quality Control Level

    1022. 

  1022.                     //****************************************************************

    1023. 

  1023.                     QualityControlLevel qc = series.QualityControlLevel;

    1024. 

  1024. 

    1025. 

  1025.                     using (DbCommand cmd12 =…
    1026.