/* this method load records into TimeSeriesResourcesList object from HydroSecurity Database given startDateTime and endDateTime*/
public void GetBetweenDates(DateTime startDateTime, DateTime endDateTime)
{
string connectionString = ConfigurationManager.ConnectionStrings["SecurityDb"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
string queryString = "SELECT timeseriesresourceid as timeseriesresourceid, timeseriesmetadataid as timeseriesmetadataid, SeriesID as seriesid,SiteID as siteid,SiteCode as sitecode, SiteName as sitename, VariableID as variableid, VariableCode as variablecode, VariableName as variablename, Speciation as speciation, VariableUnitsID as variableunitsid, VariableUnitsName as variableunitsname, SampleMedium as samplemedium, ValueType as valuetype, TimeSupport as timesupport, TimeUnitsID as timeunitsid, TimeUnitsName as timeunitsname, DataType as datatype, GeneralCategory as generalcategory, MethodID as methodid, MethodDescription as methoddescription, SourceID as sourceid, Organization as organization, SourceDescription as sourcedescription,citation as citation, QualityControlLevelID as qualitycontrollevelid, QualityControlLevelCode as qualitycontrollevelcode, ValueCount as valuecount, DateCreated as datecreated, DatabaseName as databasename, WaterOneFlowWSDL as wateroneflowwsdl FROM TimeSeriesResource where datecreated >= ' " + startDateTime + "' and datecreated <= '" + endDateTime + "';";
cmd.CommandText = queryString;
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
foreach (DataRow row in dt.Rows)
{
TimeSeriesResource timeSeriesResources = new TimeSeriesResource();
Guid d = new Guid(row["timeseriesresourceid"].ToString());
Guid m = new Guid(row["timeseriesmetadataid"].ToString());
timeSeriesResources.dataGuid = d;
timeSeriesResources.metadataGuid = m;
timeSeriesResources.seriesId = Convert.ToInt16(row["seriesid"].ToString());
timeSeriesResources.siteId = Convert.ToInt16(row["siteid"].ToString());
timeSeriesResources.siteCode = row["sitecode"].ToString();
timeSeriesResources.siteName = row["sitename"].ToString();
timeSeriesResources.variableId = Convert.ToInt16(row["variableid"].ToString());
timeSeriesResources.variableCode = row["variablecode"].ToString();
timeSeriesResources.variableName = row["variablename"].ToString();
timeSeriesResources.speciation = row["speciation"].ToString();
timeSeriesResources.variableUnitsId = Convert.ToInt16(row["variableunitsid"].ToString());
timeSeriesResources.variableUnitsName = row["variableunitsname"].ToString();
timeSeriesResources.sampleMedium = row["samplemedium"].ToString();
timeSeriesResources.valueType = row["valuetype"].ToString();
timeSeriesResources.timeSupport = Convert.ToDouble(row["timesupport"].ToString());
timeSeriesResources.timeUnitsId = Convert.ToInt16(row["timeunitsid"].ToString());
timeSeriesResources.timeUnitsName = row["timeunitsname"].ToString();
timeSeriesResources.dataType = row["datatype"].ToString();
timeSeriesResources.generalCategory = row["generalcategory"].ToString();
timeSeriesResources.methodId = Convert.ToInt16(row["methodid"].ToString());
timeSeriesResources.methodDescription = row["methoddescription"].ToString();
timeSeriesResources.sourceId = Convert.ToInt16(row["sourceid"].ToString());
timeSeriesResources.organization = row["organization"].ToString();
timeSeriesResources.sourceDescription = row["sourcedescription"].ToString();
timeSeriesResources.citation = row["citation"].ToString();
timeSeriesResources.qualityControLlevelId = Convert.ToInt16(row["qualitycontrollevelid"].ToString());
timeSeriesResources.qualityControlLevelCode = row["qualitycontrollevelcode"].ToString();
timeSeriesResources.valueCount = Convert.ToInt32(row["valuecount"].ToString());
timeSeriesResources.dateCreated = Convert.ToDateTime(row["datecreated"].ToString());
timeSeriesResources.databaseName = row["databasename"].ToString();
timeSeriesResources.waterOneflowWSDL = row["wateroneflowwsdl"].ToString();
this.timeResourcesLocal.Add(timeSeriesResources);
}
myConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("this is not successful :" + e.Message.ToString());
}
}
public void Load(List <Guid> resourceGuids)
{
string connectionString = ConfigurationManager.ConnectionStrings["SecurityDb"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
List <Guid> returnResourceList = new List <Guid>();
foreach (Guid g in resourceGuids)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
string queryString = "select ResourceId as resourceid,RequesterId as requesterid,DateRequested as daterequested,Priviledge as priviledgetype,IsGranted as status from DataRequest where resourceid='" + g + "' and isgranted =0;";
cmd.CommandText = queryString;
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
foreach (DataRow row in dt.Rows)
{
RequestManagement rm = new RequestManagement();
Guid gd = new Guid(row["resourceid"].ToString());
TimeSeriesResource timeRes = new TimeSeriesResource();
rm.timeResource = timeRes.GetTimeSeriesObjectbyDataGuid(gd);
int resConId = Convert.ToInt16(row["requesterid"].ToString());
ResourceConsumer resCon = new ResourceConsumer();
rm.resConsumer = resCon.Load(resConId);
rm.dateRequested = Convert.ToDateTime(row["daterequested"].ToString());
rm.status = row["status"].ToString();
Priviledge pv = new Priviledge();
pv.Load(Convert.ToInt16(row["priviledgetype"].ToString()));
rm.privilegeType = pv.priviledgeType;
this.resourceManagementLocal.Add(rm);
}
myConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("this is not successful :" + e.Message.ToString());
}
}
}
public void Load(List <Guid> resourceGuids)
{
string connectionString = ConfigurationManager.ConnectionStrings["SecurityDb"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
List <Guid> returnResourceList = new List <Guid>();
foreach (Guid g in resourceGuids)
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
string queryString = "select PersonResourcesId as personresourceid,ResourcesId as resourceid,PersonId as personid,PrivilegesGivenBy as authorizerid,PrivilegeId as privilegeid, DateCreated as datecreated, DateValidTill as datevalidtill from PersonResources where resourcesid='" + g + "';";
cmd.CommandText = queryString;
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
foreach (DataRow row in dt.Rows)
{
Authorization auth = new Authorization();
Guid gd = new Guid(row["resourceid"].ToString());
auth.personResourceId = Convert.ToInt16(row["personresourceid"].ToString());
TimeSeriesResource tm = new TimeSeriesResource();
auth.timeResources = tm.GetTimeSeriesObjectbyDataGuid(gd);
ResourceConsumer resCon = new ResourceConsumer();
auth.person = resCon.Load(Convert.ToInt16(row["personid"].ToString()));
auth.authorizer = resCon.Load(Convert.ToInt16(row["authorizerid"].ToString()));
auth.priviledgeId = Convert.ToInt16(row["privilegeid"].ToString());
auth.dateCreated = Convert.ToDateTime(row["datecreated"].ToString());
auth.dateValidTill = Convert.ToDateTime(row["datevalidtill"].ToString());
this.authList.Add(auth);
}
myConnection.Close();
}
catch (Exception e)
{
Console.WriteLine("this is not successful :" + e.Message.ToString());
}
}
}