topten.cs

一个在线的音乐榜单的系统

using System;
using System.Data;
using System.Data.OracleClient;

namespace MusicClass
{
	/// <summary>
	/// TopTen 的摘要说明。
	/// </summary>
	public class TopTen
	{
		public TopTen()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}
		public static DataSet GetTopByYear(string year,string categoryid)
		{
			string top10Year =  "SELECT songs.*,t.num FROM songs,(	"+
				"SELECT songid,count(to_char(submitdate,'rrrr')) as num from sort "+
				"group by songid order by count(trunc(submitdate) )  desc ) t	   "+
				" where songs.id = t.songid and to_char(songs.pubdate,'rrrr')='"+year+
				"' AND CategoryId='"+categoryid+"' AND rownum <= 10 ORDER BY t.num desc ";
			OracleConnection conn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["OraConnString"]);			
			OracleDataAdapter orada = new OracleDataAdapter(top10Year,conn);
			try
			{
				conn.Open();
				DataSet dset = new DataSet();
				orada.Fill(dset);
				return dset;
			}
			finally
			{
				conn.Close();
			}
		}

		public static DataSet GetTopByMonth(string year,string month,string categoryid)
		{
			if(month.Length < 2)
				month = "0"+month;
			string top10Month = "SELECT songs.*,t.num FROM songs,(	"+
				"SELECT songid,count(to_char(submitdate,'rrrr-mm')) as num from sort "+
				"group by songid order by count(trunc(submitdate) )  desc ) t	   "+
				" where songs.id = t.songid and to_char(songs.pubdate,'rrrr-mm')='"+year+"-"+month+
				"' AND CategoryId='"+categoryid+"' AND rownum <= 10 ORDER BY t.num desc ";
			OracleConnection conn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["OraConnString"]);			
			OracleDataAdapter orada = new OracleDataAdapter(top10Month,conn);
			try
			{
				conn.Open();
				DataSet dset = new DataSet();
				orada.Fill(dset);
				return dset;
			}
			finally
			{
				conn.Close();
			}
		}
	}
}