public IEnumerable<CdmsPoints> GetLocations(string strProject, int datasetId)
{
logger.Debug("Inside DatastoreController.cs, GetLocations...");
//logger.Debug("Getting locations for project: " + strProject + ", projectId = " + projectId);
logger.Debug("Getting locations for dataset: " + strProject + ", datasetId = " + datasetId);
//Boolean blnShowDebug = true;
Boolean blnShowDebug = false;
strProject = strProject.ToLower();
string strSearchItem = "";
if (strProject == "primary project location")
strSearchItem = strProject;
/*else if (strProject == "creelsurvey")
strSearchItem = "harvest";
else if (strProject == "adultweir")
strSearchItem = "adultweir";
else if (strProject == "electrofishing")
strSearchItem = "electrofishing";
else if (strProject == "electrofishing")
strSearchItem = "fishscales";
else if (strProject == "fishscales")
strSearchItem = "electrofishing";
else if (strProject == "fishtransport")
strSearchItem = "fishtransport";
else if (strProject == "spawninggroundsurvey")
strSearchItem = "spawninggroundsurvey";
else if (strProject == "waterquality")
strSearchItem = "waterquality";
else if (strProject == "watertemp")
strSearchItem = "watertemp";
*/
else
strSearchItem = strProject;
SqlConnection objDC;
SqlDataReader reader;
string strConnectionString = WebConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString;
string strSqlLocations = "";
//if (blnOnlyPrimaryProjects)
if (strSearchItem == "primary project location")
strSqlLocations = "select * from dbo.CDMS_POINT where LocationType = 'Primary Project Location' order by OBJECTID";
else if (strSearchItem == "All")
strSqlLocations = "select * from dbo.CDMS_POINT order by OBJECTID";
else
{
//strSqlLocations = "select * from dbo.CDMS_POINT where lower(replace(LocationType, ' ', '')) like '%" + strSearchItem + "%' order by OBJECTID";
//strSqlLocations = "select * from dbo.CDMS_POINT where ProjectId = " + projectId + " order by OBJECTID";
strSqlLocations = "select * from dbo.CDMS_POINT where DatasetId = " + datasetId + " order by OBJECTID";
}
//SELECT lower(replace(LocationType, ' ', ''))
//FROM [CDMS_DEV_NEWGEO].[dbo].[DNR_CDMS_POINT]
logger.Debug("strSqlLocations = " + strSqlLocations);
int readerLength = 0;
int recordCount = 0;
List<CdmsPoints> LocationsList = new List<CdmsPoints>();
using (objDC = new SqlConnection(strConnectionString))
{
objDC.Open();
SqlCommand sqlProjectLocations = new SqlCommand(strSqlLocations, objDC);
reader = sqlProjectLocations.ExecuteReader();
if (reader.HasRows)
{
readerLength = reader.FieldCount;
try
{
while (reader.Read())
{
CdmsPoints l = new CdmsPoints();
// Note: When we work with the CDMS_POINT table with ArcCatalog, sometimes the
// field order gets slightly askew, due to the order of the GIS columns.
// When that happens, we must reset the index numbers below.
// So we compare the order of the columns in the database for the table, to that below.
// There must to be a better way to do this!!!
// The challenge is this: we must read in a text and convert to the applicable field.
// We don't need to retrieve some items right now, but we will keep their placemarkers.
if (blnShowDebug) logger.Debug("0 " + reader.GetValue(0).ToString());
l.ObjectID = Convert.ToInt32(reader.GetValue(0).ToString());
if (blnShowDebug) logger.Debug("1: " + reader.GetValue(1).ToString());
l.GlobalID = reader.GetValue(1).ToString();
if (blnShowDebug) logger.Debug("2: " + reader.GetValue(2).ToString());
l.SDEObjectID = Convert.ToInt32(reader.GetValue(2).ToString());
if (blnShowDebug) logger.Debug("3: " + reader.GetValue(3).ToString());
l.LocationType = reader.GetValue(3).ToString();
if (blnShowDebug) logger.Debug("4: " + reader.GetValue(4).ToString());
l.SdeFeatureClass = reader.GetValue(4).ToString();
if (blnShowDebug) logger.Debug("5: " + reader.GetValue(5).ToString());
l.SdeGlobalId = reader.GetValue(5).ToString();
if (blnShowDebug) logger.Debug("6: " + reader.GetValue(6).ToString());
l.Label = reader.GetValue(6).ToString();
if (blnShowDebug) logger.Debug("7: " + reader.GetValue(7).ToString());
l.Description = reader.GetValue(7).ToString();
if (blnShowDebug) logger.Debug("8: " + reader.GetValue(8).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(8).ToString()))
l.CreateDateTime = Convert.ToDateTime(reader.GetValue(8).ToString());
else
if (blnShowDebug) logger.Debug("CreateDataTime is blank. Skipping...");
if (blnShowDebug) logger.Debug("9: " + reader.GetValue(9).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(9).ToString()))
l.UserId = Convert.ToInt32(reader.GetValue(9).ToString());
else
if (blnShowDebug) logger.Debug("UserId is blank. Skipping...");
if (blnShowDebug) logger.Debug("10: " + reader.GetValue(10).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(10).ToString()))
l.GPSEasting = Convert.ToDecimal(reader.GetValue(10).ToString());
else
if (blnShowDebug) logger.Debug("GPSEasting is blank. Skipping...");
if (blnShowDebug) logger.Debug("11: " + reader.GetValue(11).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(11).ToString()))
l.GPSNorthing = Convert.ToDecimal(reader.GetValue(11).ToString());
else
if (blnShowDebug) logger.Debug("GPSNorthing is blank. Skipping...");
if (blnShowDebug) logger.Debug("12: " + reader.GetValue(12).ToString());
l.OtherAgencyId = reader.GetValue(12).ToString();
if (blnShowDebug) logger.Debug("13: " + reader.GetValue(13).ToString());
l.ImageLink = reader.GetValue(13).ToString();
if (blnShowDebug) logger.Debug("14: " + reader.GetValue(14).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(14).ToString()))
l.RiverMile = Convert.ToDecimal(reader.GetValue(14).ToString());
else
if (blnShowDebug) logger.Debug("RiverMile is blank. Skipping...");
if (blnShowDebug) logger.Debug("15: " + reader.GetValue(15).ToString());
l.WaterBody = reader.GetValue(15).ToString();
//if (blnShowDebug) logger.Debug("16: " + reader.GetValue(16).ToString());
//l.Shape = reader.GetValue(16).ToString();
if (blnShowDebug) logger.Debug("17 " + reader.GetValue(17).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(17).ToString()))
l.ProjectId = Convert.ToInt32(reader.GetValue(17).ToString());
else
if (blnShowDebug) logger.Debug("ProjectId is blank. Skipping...");
if (blnShowDebug) logger.Debug("18 " + reader.GetValue(18).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(18).ToString()))
l.DatasetId = Convert.ToInt32(reader.GetValue(18).ToString());
else
if (blnShowDebug) logger.Debug("DatasetId is blank. Skipping...");
// This was from before we removed some fields from CDMS_POINT table.
/*
if (blnShowDebug) logger.Debug("0 " + reader.GetValue(0).ToString());
l.ObjectID = Convert.ToInt32(reader.GetValue(0).ToString());
if (blnShowDebug) logger.Debug("1: " + reader.GetValue(1).ToString());
l.GlobalID = reader.GetValue(1).ToString();
if (blnShowDebug) logger.Debug("2: " + reader.GetValue(2).ToString());
l.SDEObjectID = Convert.ToInt32(reader.GetValue(2).ToString());
if (blnShowDebug) logger.Debug("3: " + reader.GetValue(3).ToString());
l.LocationType = reader.GetValue(3).ToString();
if (blnShowDebug) logger.Debug("4: " + reader.GetValue(4).ToString());
l.SdeFeatureClass = reader.GetValue(4).ToString();
if (blnShowDebug) logger.Debug("5: " + reader.GetValue(5).ToString());
l.SdeGlobalId = reader.GetValue(5).ToString();
if (blnShowDebug) logger.Debug("6: " + reader.GetValue(6).ToString());
l.Label = reader.GetValue(6).ToString();
if (blnShowDebug) logger.Debug("7: " + reader.GetValue(7).ToString());
l.Name = reader.GetValue(7).ToString();
if (blnShowDebug) logger.Debug("8: " + reader.GetValue(8).ToString());
l.Description = reader.GetValue(8).ToString();
if (blnShowDebug) logger.Debug("9: " + reader.GetValue(9).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(9).ToString() ) )
l.CreateDateTime = Convert.ToDateTime(reader.GetValue(9).ToString());
else
if (blnShowDebug) logger.Debug("CreateDataTime is blank. Skipping...");
if (blnShowDebug) logger.Debug("10: " + reader.GetValue(10).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(10).ToString()))
l.UserId = Convert.ToInt32(reader.GetValue(10).ToString());
else
if (blnShowDebug) logger.Debug("UserId is blank. Skipping...");
if (blnShowDebug) logger.Debug("11: " + reader.GetValue(11).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(11).ToString()))
l.Elevation = Convert.ToInt32(reader.GetValue(11).ToString());
else
if (blnShowDebug) logger.Debug("Elevation is blank. Skipping...");
*/
// Skip the Status. I don't think we really need this.
//*****
//if (blnShowDebug) logger.Debug("12: " + reader.GetValue(12).ToString());
//if (!String.IsNullOrEmpty(reader.GetValue(12).ToString()))
// l.Status = Convert.ToInt32(reader.GetValue(12).ToString());
//else
// if (blnShowDebug) logger.Debug("Status is blank. Skipping...");
//*****\/
/*
if (blnShowDebug) logger.Debug("13: " + reader.GetValue(13).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(13).ToString()))
l.GPSEasting = Convert.ToDecimal(reader.GetValue(13).ToString());
else
if (blnShowDebug) logger.Debug("GPSEasting is blank. Skipping...");
if (blnShowDebug) logger.Debug("14: " + reader.GetValue(14).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(14).ToString()))
l.GPSNorthing = Convert.ToDecimal(reader.GetValue(14).ToString());
else
if (blnShowDebug) logger.Debug("GPSNorthing is blank. Skipping...");
if (blnShowDebug) logger.Debug("15: " + reader.GetValue(15).ToString());
l.Projection = reader.GetValue(15).ToString();
if (blnShowDebug) logger.Debug("16: " + reader.GetValue(16).ToString());
l.UTMZone = reader.GetValue(16).ToString();
if (blnShowDebug) logger.Debug("17: " + reader.GetValue(17).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(17).ToString()))
l.Latitude = Convert.ToDecimal(reader.GetValue(17).ToString());
else
if (blnShowDebug) logger.Debug("Latitude is blank. Skipping...");
if (blnShowDebug) logger.Debug("18: " + reader.GetValue(18).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(18).ToString()))
l.Longitude = Convert.ToDecimal(reader.GetValue(18).ToString());
else
if (blnShowDebug) logger.Debug("Longitude is blank. Skipping...");
if (blnShowDebug) logger.Debug("19: " + reader.GetValue(19).ToString());
l.OtherAgencyId = reader.GetValue(19).ToString();
if (blnShowDebug) logger.Debug("20: " + reader.GetValue(20).ToString());
l.ImageLink = reader.GetValue(20).ToString();
if (blnShowDebug) logger.Debug("21: " + reader.GetValue(21).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(21).ToString()))
l.WettedWidth = Convert.ToSingle(reader.GetValue(21).ToString());
else
if (blnShowDebug) logger.Debug("WettedWidth is blank. Skipping...");
if (blnShowDebug) logger.Debug("22: " + reader.GetValue(22).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(22).ToString()))
l.WettedDepth = Convert.ToSingle(reader.GetValue(22).ToString());
else
if (blnShowDebug) logger.Debug("WettedDepth is blank. Skipping...");
if (blnShowDebug) logger.Debug("23: " + reader.GetValue(23).ToString());
if (!String.IsNullOrEmpty(reader.GetValue(23).ToString()))
l.RiverMile = Convert.ToDecimal(reader.GetValue(23).ToString());
else
if (blnShowDebug) logger.Debug("RiverMile is blank. Skipping...");
if (blnShowDebug) logger.Debug("24: " + reader.GetValue(24).ToString());
l.WaterBody = reader.GetValue(24).ToString();
//if (blnShowDebug) logger.Debug("25: " + reader.GetValue(25).ToString());
//l.Shape = reader.GetValue(25).ToString();
if (blnShowDebug) logger.Debug("26 " + reader.GetValue(26).ToString());
l.ProjectId = Convert.ToInt32(reader.GetValue(26).ToString());
if (blnShowDebug) logger.Debug("27 " + reader.GetValue(27).ToString());
l.DatasetId = Convert.ToInt32(reader.GetValue(27).ToString());
//if (blnShowDebug) logger.Debug("25 " + reader.GetValue(25).ToString());
//l.ObjectID = Convert.ToInt32(reader.GetValue(25).ToString());
*/
LocationsList.Add(l);
recordCount++;
}
}
catch (Exception)
{
logger.Debug("Had problem retrieving Locations...");
}
}
logger.Debug("Records found: " + recordCount);
reader.Close();
sqlProjectLocations.Dispose();
objDC.Close();
objDC.Dispose();
}
return LocationsList.AsEnumerable();
}
public IEnumerable<CdmsPoints> GetSdeGlobalIdForPoint(int Id)
{
// Note: Although it seems like we are returning a list, we are only returning one record.
// I (gc) did it this way for simplicity...
logger.Debug("Inside DatastoreController.cs, GetSdeGlobalIdForPoint...");
logger.Debug("Id = " + Id);
Boolean blnShowDebug = false;
SqlConnection objDC;
//SqlDataReader reader;
int readerLength = 0;
int recordCount = 0;
string strConnectionString = WebConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString;
//string strSqlGetSdePointInfo = "select SdeGlobalID from dbo.CDMS_POINT where OBJECTID = " + Id;
//logger.Debug("strSqlGetSdePointInfo = " + strSqlGetSdePointInfo);
/*string strGlobalId = "";
using (objDC = new SqlConnection(strConnectionString))
{
objDC.Open();
SqlCommand sqlSdeGlobalId = new SqlCommand(strSqlGetSdePointInfo, objDC);
reader = sqlSdeGlobalId.ExecuteReader();
if (reader.HasRows)
{
readerLength = reader.FieldCount;
try
{
while (reader.Read())
{
CdmsPoints c = new CdmsPoints();
if (blnShowDebug) logger.Debug("0: " + reader.GetValue(0).ToString());
c.SdeGlobalId = reader.GetValue(0).ToString();
strGlobalId = reader.GetValue(0).ToString();
recordCount++;
}
logger.Debug("Records found: " + recordCount);
}
catch (Exception)
{
logger.Debug("Had a problem retrieving the GlobalId for a newly created point.");
}
}
}
*/
/* OK. Notes are in order here...
* We were first working with a table that was copied/pasted from sdevector, which retained all the numbering, indexes, etc.
* However, in that process, ArcGIS caused the table/service to require Z values, which we do not send over (only x, y).
* Therefore, we created the SDEObjectID column and copied the existing OBJECTID into it, to retain the values.
* Next we imported the table via ArcGIS, and it renumbered the OBJECTID as expected from prior experience.
* When we save a point, we DO NOT set the SDEObjectID; instead we set the SDEGlobalID.
* OBJECTID will never be null, but it may match a corresponding SDEObjectID.
* Consequently, when we want to pull an SDEGlobalID and pass in the OBJECTID,
* it will match either the OBJECTID or an SDEObjectID, but we do not want to pull both records.
* More or lesss, we want to check the SDEObjectID for the passed in number first.
* If we do not find the number there, then we check the OBJECTID column.
* Scenario
* OBJECTID SDEObjectID SDEGlobalID
* 1 4009 333...
* 4009 null something
* 21 13623 6C0...
* 13623 null something
*
* We DO NOT want to pull both records that have the matching OBJECTID and SDEObjectID.
*/
//string strSqlSdeGlobalId = "select SdeGlobalId from dbo.DNR_CDMS_POINT where SdeObjectID = " + Id;
//logger.Debug("strSqlSdeGlobalId = " + strSqlSdeGlobalId);
//string strSdeGlobalId = "";
List<CdmsPoints> locationPointList = new List<CdmsPoints>();
using (objDC = new SqlConnection(strConnectionString))
{
objDC.Open();
string strSqlSdeGlobalIdFromOBJECTID = "select SdeGlobalID from dbo.CDMS_POINT where OBJECTID = " + Id;
SqlCommand sqlSdeGlobalIdFromObjectId = new SqlCommand(strSqlSdeGlobalIdFromOBJECTID, objDC);
logger.Debug("strSqlSdeGlobalIdFromOBJECTID = " + strSqlSdeGlobalIdFromOBJECTID);
SqlDataReader reader = sqlSdeGlobalIdFromObjectId.ExecuteReader();
if (reader.HasRows)
{
logger.Debug("Found " + Id + " in OBJECTID...");
readerLength = reader.FieldCount;
try
{
while (reader.Read())
{
CdmsPoints c = new CdmsPoints();
if (blnShowDebug) logger.Debug("0: " + reader.GetValue(0).ToString());
c.SdeGlobalId = reader.GetValue(0).ToString();
//strSdeGlobalId = reader.GetValue(0).ToString();
locationPointList.Add(c);
recordCount++;
}
logger.Debug("Records found: " + recordCount);
}
catch (Exception)
{
logger.Debug("Had a problem retrieving the SdeGlobalId (checking OBJECTID) for an existing point.");
}
sqlSdeGlobalIdFromObjectId.Dispose();
}
else
{
reader.Close(); // Need to close the reader from the read in the if branch.
string strSqlSdeGlobalIdFromSDEObjectID = "select SdeGlobalID from dbo.CDMS_POINT where SDEObjectID = " + Id;
SqlCommand sqlSdeGlobalIdFromSdeObjectId = new SqlCommand(strSqlSdeGlobalIdFromSDEObjectID, objDC);
logger.Debug("strSqlSdeGlobalIdFromSDEObjectID = " + strSqlSdeGlobalIdFromSDEObjectID);
reader = sqlSdeGlobalIdFromSdeObjectId.ExecuteReader();
if (reader.HasRows)
{
logger.Debug("Found " + Id + " in SDEObjectID...");
readerLength = reader.FieldCount;
try
{
while (reader.Read())
{
CdmsPoints c = new CdmsPoints();
if (blnShowDebug) logger.Debug("0: " + reader.GetValue(0).ToString());
c.SdeGlobalId = reader.GetValue(0).ToString();
//strSdeGlobalId = reader.GetValue(0).ToString();
locationPointList.Add(c);
recordCount++;
}
logger.Debug("Records found: " + recordCount);
}
catch (Exception)
{
logger.Debug("Had a problem retrieving the SdeGlobalId (checking SDEObjectID) for an existing point.");
}
}
reader.Close();
sqlSdeGlobalIdFromSdeObjectId.Dispose();
}
}
objDC.Close();
objDC.Dispose();
return locationPointList.AsEnumerable();
//return strSdeGlobalId;
}
public IEnumerable<CdmsPoints> GetGlobalIdForPoint(int Id)
{
// Note: Although it seems like we are returning a list, we are only returning one record.
// I (gc) did it this way for implicity...
logger.Debug("Inside DatastoreController.cs, GetSdeGlobalIdForPoint...");
logger.Debug("Id = " + Id);
Boolean blnShowDebug = false;
SqlConnection objDC;
SqlDataReader reader;
int readerLength = 0;
int recordCount = 0;
string strConnectionString = WebConfigurationManager.ConnectionStrings["ServicesContext"].ConnectionString;
string strSqlGlobalId = "select GlobalID from dbo.CDMS_POINT where OBJECTID = " + Id;
logger.Debug("strSqlGlobalId = " + strSqlGlobalId);
List<CdmsPoints> locationPointList = new List<CdmsPoints>();
using (objDC = new SqlConnection(strConnectionString))
{
objDC.Open();
SqlCommand sqlSdeGlobalId = new SqlCommand(strSqlGlobalId, objDC);
reader = sqlSdeGlobalId.ExecuteReader();
if (reader.HasRows)
{
readerLength = reader.FieldCount;
try
{
while (reader.Read())
{
CdmsPoints c = new CdmsPoints();
if (blnShowDebug) logger.Debug("0: " + reader.GetValue(0).ToString());
c.GlobalID = reader.GetValue(0).ToString();
locationPointList.Add(c);
recordCount++;
}
logger.Debug("Records found: " + recordCount);
}
catch (Exception)
{
logger.Debug("Had a problem retrieving the GlobalId for a newly created point.");
}
}
}
return locationPointList.AsEnumerable();
}