public List <T> Execute() { List <T> result = new List <T>(); SqlConnection conn = SongChartsDatabase.Connection(); SqlCommand command; command = new SqlCommand(this.ProcedureName, conn); command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = 200; foreach (SqlParameter p in _parameters) { command.Parameters.Add(p); } try { conn.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { result.Add(this.IDataRecordFunc.Invoke(reader)); } } catch (Exception ex) { throw ex; } finally { command.Dispose(); command = null; conn.Close(); conn.Dispose(); conn = null; } return(result); }
public List <object> Get() { List <object> data = new List <object>(); SqlCommand command; SqlDataReader reader; // Get the data. using (SqlConnection conn = SongChartsDatabase.Connection()) { conn.Open(); command = new SqlCommand("SELECT [Year],[MonthOrdinal],[WeekOrdinal],[Rank],[Title],[Fullname] FROM [RankConflicts] ORDER BY 4,1,2,3", conn); command.CommandType = CommandType.Text; command.CommandTimeout = 200; reader = command.ExecuteReader(); while (reader.Read()) { data.Add(new { Year = reader.GetInt16(0), MonthOrdinal = reader.GetByte(1), WeekOrdinal = reader.GetByte(2), Rank = reader.GetDecimal(3), Title = reader.GetString(4), Fullname = reader.GetString(5) }); } reader.Close(); conn.Close(); } // using // All done. return(data); }
public HomePageData Get() { HomePageData data = new HomePageData(); SqlCommand command; SqlDataReader reader; // Get the data. using (SqlConnection conn = SongChartsDatabase.Connection()) { conn.Open(); command = new SqlCommand("SELECT [DecadeNumber],[SongCount] FROM [dbo].[DecadeSummaries] ORDER BY [DecadeNumber]", conn); command.CommandType = CommandType.Text; command.CommandTimeout = 200; reader = command.ExecuteReader(); while (reader.Read()) { Decade decade = new Decade(); decade.GetSummaryData(reader); data.Decades.Add(decade); } reader.Close(); command = new SqlCommand("SELECT [YearNumber],[SongCount] FROM [dbo].[YearSummaries] ORDER BY [YearNumber]", conn); command.CommandType = CommandType.Text; command.CommandTimeout = 200; reader = command.ExecuteReader(); while (reader.Read()) { Year year = new Year(); year.GetSummaryData(reader); data.Years.Add(year.Number, year); } reader.Close(); Get_Artists(conn, data); Get_Genres(conn, data); Get_CommonWords(conn, data); Get_UniqueWords(conn, data); conn.Close(); // Massage data and form output object. //TEMP? data.DecadeNumbers = data.Decades.Keys.ToList(); //TEMP? data.YearNumbers = data.Years.Keys.ToList(); // Put the year data into the respective decade objects. foreach (Decade decade in data.Decades) { short decadeNumber = decade.Number; // decade.Years == {} short[] yearOrdinals = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; foreach (short y in yearOrdinals) { short yearNumber = (short)(decadeNumber + y); Year year; if (data.Years.ContainsKey(yearNumber)) { year = data.Years[yearNumber]; } else { year = new Year(yearNumber); } decade.Years.Add(year); } } // Get artist summary. } // using // All done. return(data); }