public IEnumerable <Title> getTitlesForTheDay(int publishDate) { var con = new DBCon(); var l = new List <Title>(); string sql = @" select t.publishDate, t.title, t.titleId, p.cnt from ( SELECT * FROM Titles WHERE publishDate = @publishDate ) as t inner join ( SELECT count(*) as cnt, titleId from Pages GROUP BY titleId ) as p on t.titleId = p.titleId; "; var result = con.ExecuteSelect(sql, new Dictionary <string, object[]> { { "@publishDate", new object[2] { SqlDbType.Int, publishDate } } }); result.ForEach((e) => { var title = new Title(); title.publishDate = DateTime.ParseExact(((int)e["publishDate"]).ToString(), "yyyyMMdd", null); title.titleId = (int)e["titleId"]; title.title = (string)e["title"]; title.cnt = (int)e["cnt"]; l.Add(title); }); return(l); }
public IEnumerable <Page> getPagesForTitle(int titleId) { var con = new DBCon(); var l = new List <Page>(); var result = con.ExecuteSelect($"SELECT p.titleId, p.pageName, p.link, p.explanation, t.publishDate, t.title FROM Pages as p inner join Titles as t on p.titleId = t.titleId and p.titleId = @titleId;", new Dictionary <string, object[]> { { "@titleId", new object[2] { SqlDbType.Int, titleId } } }); result.ForEach((e) => { var page = new Page(); page.titleId = (int)e["titleId"]; page.title = (string)e["title"]; page.publishDate = DateTime.ParseExact(((int)e["publishDate"]).ToString(), "yyyyMMdd", null); page.link = (string)e["link"]; page.pageName = (string)e["pageName"]; page.explanation = (string)e["explanation"]; l.Add(page); }); return(l); }
public List <string> GetAllTableNames() { //SQL文作成 string sql = "SELECT *"; sql += " FROM sysobjects"; sql += " WHERE xtype = 'U'"; sql += " ORDER BY name;"; //List<Dictionary<string, Object>>型で取得 var tables = con.ExecuteSelect(sql, null); var tableNames = new List <string>(); foreach (var table in tables) { tableNames.Add((string)table["name"]); } return(tableNames); }
public IEnumerable <dynamic> getAllDates() { var con = new DBCon(); var l = new List <object>(); var result = con.ExecuteSelect($"SELECT publishDate, count(titleId) as cnt FROM Titles GROUP BY publishDate ORDER BY publishDate desc;"); result.ForEach((e) => { var publishDate = DateTime.ParseExact(((int)e["publishDate"]).ToString(), "yyyyMMdd", null); var cnt = (int)e["cnt"]; var dates = new { publishDate, cnt }; l.Add(dates); }); return(l); }