public Int32 GetCountOfAnimals() { string sql = "select count(*) from animal Where SID is not Null and GID is not Null and Sex!=''"; return(Int32.Parse(Dal.ExecScalar(sql).ToString())); }
public List <SearchExp> GetAllExpList() { List <SearchExp> lstExp = new List <SearchExp>(); using (DataTable dt = Dal.GetDataTable($@"select Experiment.ExpName, task.name as CognitiveTask, Case Experiment.Status When 1 Then 'Public' When 0 Then 'Private' End as Status, STUFF((SELECT distinct ' <br/>' + Age.AgeInMonth FROM SubExperiment inner join Age on Age.ID = SubExperiment.AgeID Where SubExperiment.ExpID = Experiment.ExpID --order by Age.AgeInMonth FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,6,'') As Age, STUFF((SELECT distinct ' <br/>' + Strain.Strain FROM Animal inner join Strain on Strain.ID = Animal.SID Where Animal.ExpID = Experiment.ExpID FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,6,'') As Strain, STUFF((SELECT distinct ', ' + Genotype.Genotype FROM Animal inner join Genotype on Genotype.ID = Animal.GID Where Animal.ExpID = Experiment.ExpID FOR XML PATH(''), type ).value('.', 'nvarchar(max)'),1,2,'') As Genotype, Experiment.TaskDescription, Concat(Experiment.StartExpDate, '-', Experiment.EndExpDate) as Period ,tt2.PISiteName, tt2.UserName as Username, tt2.Email, Experiment.DOI From Experiment inner join task on task.ID = Experiment.TaskID inner join (Select PUSID, PIUserSite.PSID, tt.PISiteName, CONCAT(AspNetUsers.GivenName, ' ', AspNetUsers.FamilyName) as UserName, AspNetUsers.Email as Email From PIUserSite inner join (Select PSID, PI.PName, Site.Institution, CONCAT(PName, ' - ', Institution) as PISiteName From PISite inner join PI on PI.PID = PISite.PID inner join Site on Site.SiteID = PISite.SiteID ) as tt on tt.PSID = PIUserSite.PSID inner join AspNetUsers on AspNetUsers.Id = PIUserSite.UserID) as tt2 on tt2.PUSID = Experiment.PUSID" )) { foreach (DataRow dr in dt.Rows) { lstExp.Add(new SearchExp { ExpName = Convert.ToString(dr["ExpName"].ToString()), CognitiveTask = Convert.ToString(dr["CognitiveTask"].ToString()), Status = Convert.ToString(dr["Status"].ToString()), Age = Convert.ToString(dr["Age"].ToString()), Strain = Convert.ToString(dr["Strain"].ToString()), Genotype = Convert.ToString(dr["Genotype"].ToString()), TaskDescription = Convert.ToString(dr["TaskDescription"].ToString()), Period = Convert.ToString(dr["Period"].ToString()), PISiteName = Convert.ToString(dr["PISiteName"].ToString()), Username = Convert.ToString(dr["Username"].ToString()), Email = Convert.ToString(dr["Email"].ToString()), DOI = Convert.ToString(dr["DOI"].ToString()), }); } } return(lstExp); }
public string GetTaskAnalysisByID(int expid) { string sql = ""; return(Dal.ExecScalar(sql).ToString()); }