/// <summary>
        /// Get Source details from the ODM
        /// For GHCN we have only one source
        /// </summary>
        /// <returns>SiteCode - GhcnSite object lookup dictionary</returns>
        private GhcnSource GetSource()
        {
            string     connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString;
            GhcnSource source     = new GhcnSource();

            using (SqlConnection connection = new SqlConnection(connString))
            {
                string sql = "SELECT TOP 1 SourceID, SourceCode, Organization, SourceDescription, Citation FROM dbo.Sources";
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    cmd.Connection.Open();

                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        string code = reader.GetString(1);
                        source = new GhcnSource
                        {
                            SourceID          = reader.GetInt32(0),
                            SourceCode        = reader.GetString(1),
                            Organization      = reader.GetString(2),
                            SourceDescription = reader.GetString(3),
                            Citation          = reader.GetString(4)
                        };
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
            }
            return(source);
        }
        public void UpdateSources()
        {
            try
            {
                int        metadataID = SaveOrUpdateMetadata();
                GhcnSource source     = new GhcnSource
                {
                    Organization      = "NOAA National Centers for Environmental Information",
                    SourceDescription = "Global Historical Climate Network - Daily (GHCN-Daily) Version 3",
                    SourceLink        = "ncdc.noaa.gov",
                    ContactName       = "John Leslie",
                    Phone             = "1-828-271-4876",
                    Email             = "*****@*****.**",
                    Address           = "Federal Building, 151 Patton Avenue",
                    City       = "Asheville",
                    State      = "NC",
                    ZipCode    = "28801-5001",
                    Citation   = @"Cite this dataset when used as a source: 
Menne, Matthew J., Imke Durre, Bryant Korzeniewski, Shelley McNeal, Kristy Thomas, Xungang Yin, Steven Anthony, Ron Ray, Russell S. Vose, Byron E.Gleason, and Tamara G. Houston (2012): 
Global Historical Climatology Network - Daily (GHCN-Daily), Version 3. [indicate subset used]. 
NOAA National Climatic Data Center. doi:10.7289/V5D21VHZ [access date].",
                    MetadataID = metadataID,
                    SourceCode = "NOAH-GHCN"
                };


                string connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(connString))
                {
                    int sourceID = SaveOrUpdateSource(source, connection);
                }
                _log.LogWrite("UpdateSources OK");
            }
            catch (Exception ex)
            {
                _log.LogWrite("UpdateSources ERROR: " + ex.Message);
            }
        }
        private int SaveOrUpdateSource(GhcnSource source, SqlConnection connection)
        {
            object sourceIDResult = null;

            using (SqlCommand cmd = new SqlCommand("SELECT SourceID FROM Sources WHERE Organization = @organization", connection))
            {
                cmd.Parameters.Add(new SqlParameter("@organization", source.Organization));
                connection.Open();
                sourceIDResult = cmd.ExecuteScalar();
                connection.Close();
            }

            if (sourceIDResult != null)
            {
                //update the source
                source.SourceID = Convert.ToInt32(sourceIDResult);
                string sql = @"UPDATE dbo.Sources SET 
                                SourceDescription = @desc, 
                                SourceLink = @link, 
                                ContactName = @name, 
                                Phone = @phone,
                                Email = @email,
                                Address = @address,
                                City = @city,
                                State = @state,
                                ZipCode = @zipcode,
                                Citation = @citation,
                                MetadataID = @metadataid,
                                SourceCode =@sourcecode
                               WHERE Organization = @org";
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    cmd.Parameters.Add(new SqlParameter("@org", source.Organization));
                    cmd.Parameters.Add(new SqlParameter("@desc", source.SourceDescription));
                    cmd.Parameters.Add(new SqlParameter("@link", source.SourceLink));
                    cmd.Parameters.Add(new SqlParameter("@name", source.ContactName));
                    cmd.Parameters.Add(new SqlParameter("@phone", source.Phone));
                    cmd.Parameters.Add(new SqlParameter("@email", source.Email));
                    cmd.Parameters.Add(new SqlParameter("@address", source.Address));
                    cmd.Parameters.Add(new SqlParameter("@city", source.City));
                    cmd.Parameters.Add(new SqlParameter("@state", source.State));
                    cmd.Parameters.Add(new SqlParameter("@zipcode", source.ZipCode));
                    cmd.Parameters.Add(new SqlParameter("@citation", source.Citation));
                    cmd.Parameters.Add(new SqlParameter("@metadataid", source.MetadataID));
                    cmd.Parameters.Add(new SqlParameter("@sourcecode", source.SourceCode));
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            else
            {
                //save the source
                string sql = @"INSERT INTO Sources (
                                Organization,
                                SourceDescription, 
                                SourceLink, 
                                ContactName, 
                                Phone,
                                Email,
                                Address,
                                City,
                                State,
                                ZipCode,
                                Citation,
                                MetadataID,
                                SourceCode)
                              VALUES (
                                @org,
                                @desc, 
                                @link, 
                                @name, 
                                @phone,
                                @email,
                                @address,
                                @city,
                                @state,
                                @zipcode,
                                @citation,
                                @metadataid,
                                @sourcecode)";

                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    cmd.Parameters.Add(new SqlParameter("@org", source.Organization));
                    cmd.Parameters.Add(new SqlParameter("@desc", source.SourceDescription));
                    cmd.Parameters.Add(new SqlParameter("@link", source.SourceLink));
                    cmd.Parameters.Add(new SqlParameter("@name", source.ContactName));
                    cmd.Parameters.Add(new SqlParameter("@phone", source.Phone));
                    cmd.Parameters.Add(new SqlParameter("@email", source.Email));
                    cmd.Parameters.Add(new SqlParameter("@address", source.Address));
                    cmd.Parameters.Add(new SqlParameter("@city", source.City));
                    cmd.Parameters.Add(new SqlParameter("@state", source.State));
                    cmd.Parameters.Add(new SqlParameter("@zipcode", source.ZipCode));
                    cmd.Parameters.Add(new SqlParameter("@citation", source.Citation));
                    cmd.Parameters.Add(new SqlParameter("@metadataid", source.MetadataID));
                    cmd.Parameters.Add(new SqlParameter("@sourcecode", source.SourceCode));
                    // to get the inserted variable id
                    SqlParameter param = new SqlParameter("@SourceID", SqlDbType.Int);
                    param.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                    sourceIDResult = cmd.Parameters["@SourceID"].Value;
                    connection.Close();
                }

                // to get the inserted source id..
                using (SqlCommand cmd = new SqlCommand("SELECT SourceID FROM Sources WHERE Organization = @org", connection))
                {
                    connection.Open();
                    cmd.Parameters.Add(new SqlParameter("@org", source.Organization));
                    sourceIDResult = cmd.ExecuteScalar();
                    connection.Close();
                }
            }
            return(Convert.ToInt32(sourceIDResult));
        }