/// <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)); }