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