public List<Album> GetAlbumNames()
{
List<Album> res = new List<Album>();
dal.OpenConn();
SqlDataAdapter adp = new SqlDataAdapter("select * from [Album]", dal.OpenConn());
DataSet ds = new DataSet();
int affected = adp.Fill(ds);
dal.CloseConn();
for (int i = 0; i < affected; i++)
{
Album obj = new Album();
obj.AlbumId =Convert.ToInt32( ds.Tables[0].Rows[i].ItemArray[0]);
obj.AlbumName = ds.Tables[0].Rows[i].ItemArray[1].ToString();
obj.year = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[2]);
obj.path = ds.Tables[0].Rows[i].ItemArray[3].ToString();
res.Add(obj);
}
return res;
}
public string GetJson()
{
FinalJson fjson = new FinalJson();
List<SubJson> res = new List<SubJson>();
dal.OpenConn();
SqlDataAdapter adp = new SqlDataAdapter("select * from [Track] inner join [Album] on Track.AlbumId=Album.AlbumId where [Featured]=1", dal.OpenConn());
DataSet ds = new DataSet();
int affected = adp.Fill(ds);
if (affected > 10)
affected = 10;
dal.CloseConn();
for (int i = 0; i < affected; i++)
{
SubJson obj = new SubJson();
Track objTrack = new Track();
Album objAlbum = new Album();
objTrack.TrackID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
objTrack.TrackName = ds.Tables[0].Rows[i].ItemArray[1].ToString();
objTrack.AlbumID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[2]);
objTrack.Price = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[3]);
objTrack.Downloads = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[4]);
objTrack.Length = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[5]);
objTrack.Genre = ds.Tables[0].Rows[i].ItemArray[6].ToString();
objTrack.Path = ds.Tables[0].Rows[i].ItemArray[7].ToString();
objTrack.Artist = ds.Tables[0].Rows[i].ItemArray[8].ToString();
objTrack.Featured = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[9]);
objAlbum.AlbumId = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[10]);
objAlbum.AlbumName = ds.Tables[0].Rows[i].ItemArray[11].ToString();
objAlbum.year = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[12]);
objAlbum.path = ds.Tables[0].Rows[i].ItemArray[13].ToString();
obj.objTrack = objTrack;
obj.objAlbum = objAlbum;
res.Add(obj);
}
fjson.Featured = res;
List<SubJson> res1 = new List<SubJson>();
dal.OpenConn();
SqlDataAdapter adp1 = new SqlDataAdapter("select * from [Track] inner join [Album] on Track.AlbumId=Album.AlbumId ORDER BY Album.Year DESC ", dal.OpenConn());
DataSet ds1 = new DataSet();
int affected1 = adp1.Fill(ds1);
if (affected1 > 10)
affected1 = 10;
dal.CloseConn();
for (int i = 0; i < affected1; i++)
{
SubJson obj = new SubJson();
Track objTrack = new Track();
Album objAlbum = new Album();
objTrack.TrackID = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[0]);
objTrack.TrackName = ds1.Tables[0].Rows[i].ItemArray[1].ToString();
objTrack.AlbumID = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[2]);
objTrack.Price = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[3]);
objTrack.Downloads = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[4]);
objTrack.Length = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[5]);
objTrack.Genre = ds1.Tables[0].Rows[i].ItemArray[6].ToString();
objTrack.Path = ds1.Tables[0].Rows[i].ItemArray[7].ToString();
objTrack.Artist = ds1.Tables[0].Rows[i].ItemArray[8].ToString();
objTrack.Featured = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[9]);
objAlbum.AlbumId = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[10]);
objAlbum.AlbumName = ds1.Tables[0].Rows[i].ItemArray[11].ToString();
objAlbum.year = Convert.ToInt32(ds1.Tables[0].Rows[i].ItemArray[12]);
objAlbum.path = ds1.Tables[0].Rows[i].ItemArray[13].ToString();
obj.objTrack = objTrack;
obj.objAlbum = objAlbum;
res1.Add(obj);
}
fjson.Recent = res1;
List<SubJson> res2 = new List<SubJson>();
dal.OpenConn();
SqlDataAdapter adp2 = new SqlDataAdapter("select * from [Track] inner join [Album] on Track.AlbumId=Album.AlbumId order by Track.Downloads DESC", dal.OpenConn());
DataSet ds2 = new DataSet();
int affected2 = adp2.Fill(ds2);
if (affected2 > 10)
affected2 = 10;
dal.CloseConn();
for (int i = 0; i < affected2; i++)
{
SubJson obj = new SubJson();
Track objTrack = new Track();
Album objAlbum = new Album();
objTrack.TrackID = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[0]);
objTrack.TrackName = ds2.Tables[0].Rows[i].ItemArray[1].ToString();
objTrack.AlbumID = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[2]);
objTrack.Price = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[3]);
objTrack.Downloads = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[4]);
objTrack.Length = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[5]);
objTrack.Genre = ds2.Tables[0].Rows[i].ItemArray[6].ToString();
objTrack.Path = ds2.Tables[0].Rows[i].ItemArray[7].ToString();
objTrack.Artist = ds2.Tables[0].Rows[i].ItemArray[8].ToString();
objTrack.Featured = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[9]);
objAlbum.AlbumId = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[10]);
objAlbum.AlbumName = ds2.Tables[0].Rows[i].ItemArray[11].ToString();
objAlbum.year = Convert.ToInt32(ds2.Tables[0].Rows[i].ItemArray[12]);
objAlbum.path = ds2.Tables[0].Rows[i].ItemArray[13].ToString();
obj.objTrack = objTrack;
obj.objAlbum = objAlbum;
res2.Add(obj);
}
fjson.Popular = res2;
var jstring = JsonConvert.SerializeObject(fjson);
return jstring;
}