protected void Page_Load(object sender, EventArgs e)
{
HttpCookie userCookie = Request.Cookies["QTCookie"];
if (userCookie == null)
{
string str = Request.QueryString["value"];
string songStr = "SELECT * FROM song, artist, genre WHERE song_name LIKE '%" + str + "%' AND song.artist_id = artist.artist_id AND song.genre_id = genre.genre_id AND song.song_permission = 2";
string albumStr = "SELECT * FROM album WHERE album_name LIKE '%" + str + "%'";
string artistStr = "SELECT * FROM song, artist WHERE artist_name LIKE '%" + str + "%' AND song.artist_id=artist.artist_id";
string playlistStr = "SELECT * FROM playlist WHERE playlist_name LIKE '%" + str + "%'";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand songCmd = new SqlCommand(songStr, con);
SqlCommand albumCmd = new SqlCommand(albumStr, con);
SqlCommand artistCmd = new SqlCommand(artistStr, con);
SqlCommand playlistCmd = new SqlCommand(playlistStr, con);
SqlDataReader reader;
ArrayList songList = new ArrayList();
ArrayList albumList = new ArrayList();
ArrayList playlList = new ArrayList();
ArrayList artistList = new ArrayList();
try
{
con.Open();
reader = songCmd.ExecuteReader();
while (reader.Read())
{
song songLst = new song();
songLst._id = (int)reader["song_id"];
songLst._artist = (string)reader["artist_name"];
songLst._genre = (string)reader["genre_name"];
songLst._name = (string)reader["song_name"];
songLst._length = reader["song_length"].ToString();
songLst._downloadCount = (int)reader["song_download_count"];
songLst._playCount = (int)reader["song_play_count"];
songLst._likeCount = (int)reader["song_like_count"];
songList.Add(songLst);
}
songGrid.DataSource = songList;
songGrid.DataBind();
reader.Close();
reader = albumCmd.ExecuteReader();
while (reader.Read())
{
album albumLst = new album();
albumLst._id = (int)reader["album_id"];
albumLst._name = (string)reader["album_name"];
albumLst._playCount = (int)reader["album_play_count"];
albumLst._releaseDate = (DateTime)reader["album_release_date"];
albumList.Add(albumLst);
}
albumGrid.DataSource = albumList;
albumGrid.DataBind();
reader.Close();
reader = playlistCmd.ExecuteReader();
while (reader.Read())
{
album playlLst = new album();
playlLst._id = (int)reader["playlist_id"];
playlLst._name = (string)reader["playlist_name"];
playlLst._playCount = (int)reader["playlist_play_count"];
playlLst._releaseDate = (DateTime)reader["playlist_create_date"];
playlList.Add(playlLst);
}
playlGrid.DataSource = playlList;
playlGrid.DataBind();
reader.Close();
reader = artistCmd.ExecuteReader();
while (reader.Read())
{
artist artistLst = new artist();
artistLst._id = (int)reader["artist_id"];
artistLst._name = (string)reader["artist_name"];
artistLst._debutYear = (DateTime)reader["artist_debut_year"];
artistList.Add(artistLst);
}
artistGrid.DataSource = artistList;
artistGrid.DataBind();
reader.Close();
}
catch (Exception err)
{
lblResult.Text = "Error reading data. <br />";
lblResult.Text += err.Message;
}
finally
{
con.Close();
}
}
else
{
song[] songLst = new song[200];
string str = Request.QueryString["value"];
string songStr = "SELECT * FROM song, artist, genre WHERE song_name LIKE '%"+str+"%' AND song.artist_id = artist.artist_id AND song.genre_id = genre.genre_id" +
" AND song.song_permission = 2";
string sharedSong = "SELECT * FROM song, artist, genre,songup WHERE song_name LIKE '%"+str+"%' AND song.artist_id = artist.artist_id AND song.genre_id = genre.genre_id AND song.song_id = songup.song_id" +
" AND song.song_permission = 1";
string albumStr = "SELECT * FROM album WHERE album_name LIKE '%" + str + "%'";
string artistStr = "SELECT * FROM song, artist WHERE artist_name LIKE '%" + str + "%' AND song.artist_id=artist.artist_id";
string playlistStr = "SELECT * FROM playlist WHERE playlist_name LIKE '%" + str + "%'";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand songCmd = new SqlCommand(songStr, con);
SqlCommand sharedSongCmd = new SqlCommand(sharedSong, con);
SqlCommand albumCmd = new SqlCommand(albumStr, con);
SqlCommand artistCmd = new SqlCommand(artistStr, con);
SqlCommand playlistCmd = new SqlCommand(playlistStr, con);
SqlDataReader reader, userReader; ;
ArrayList songList = new ArrayList();
ArrayList albumList = new ArrayList();
ArrayList playlList = new ArrayList();
ArrayList artistList = new ArrayList();
int j=0;
try
{
con.Open();
reader = songCmd.ExecuteReader();
while (reader.Read())
{
songLst[j] = new song();
songLst[j]._id = (int)reader["song_id"];
songLst[j]._artist = (string)reader["artist_name"];
songLst[j]._genre = (string)reader["genre_name"];
songLst[j]._name = (string)reader["song_name"];
songLst[j]._length = reader["song_length"].ToString();
songLst[j]._downloadCount = (int)reader["song_download_count"];
songLst[j]._playCount = (int)reader["song_play_count"];
songLst[j]._likeCount = (int)reader["song_like_count"];
songList.Add(songLst[j]);
j++;
}
reader.Close();
int[] shareSongLst = new int[100];
int[] sharedSongUsr = new int[100];
int i, count = 0;
reader = sharedSongCmd.ExecuteReader();
while (reader.Read())
{
shareSongLst[count]= (int)reader["song_id"];
sharedSongUsr[count] = (int)reader["user_id"];
count++;
}
reader.Close();
for (i = 0; i <= count; i++)
{
string userStr = "SELECT DISTINCT song.song_id, friend.user_id, friend_id, artist_name, genre_name, song_name, song_length, song_download_count, song_like_count, song_play_count "
+"FROM artist,genre,song, songup, friend WHERE songup.user_id=friend.user_id AND songup.song_id=song.song_id AND artist.artist_id=song.artist_id AND genre.genre_id=song.genre_id"
+ " AND song.song_id=" + shareSongLst[i] + " AND friend.user_id=" + sharedSongUsr[i] + " AND friend_id=" + userCookie["ID"];
SqlCommand userCmd = new SqlCommand(userStr, con);
userReader = userCmd.ExecuteReader();
while (userReader.Read())
{
songLst[j] = new song();
songLst[j]._id = (int)userReader["song_id"];
songLst[j]._artist = (string)userReader["artist_name"];
songLst[j]._genre = userReader["genre_name"].ToString();
songLst[j]._name = (string)userReader["song_name"];
songLst[j]._length = userReader["song_length"].ToString();
songLst[j]._downloadCount = (int)userReader["song_download_count"];
songLst[j]._playCount = (int)userReader["song_play_count"];
songLst[j]._likeCount = (int)userReader["song_like_count"];
songList.Add(songLst[j]);
j++;
}
userReader.Close();
}
songGrid.DataSource = songList;
songGrid.DataBind();
reader = albumCmd.ExecuteReader();
while (reader.Read())
{
album albumLst = new album();
albumLst._id = (int)reader["album_id"];
albumLst._name = (string)reader["album_name"];
albumLst._playCount = (int)reader["album_play_count"];
albumLst._releaseDate = (DateTime)reader["album_release_date"];
albumList.Add(albumLst);
}
albumGrid.DataSource = albumList;
albumGrid.DataBind();
reader.Close();
reader = playlistCmd.ExecuteReader();
while (reader.Read())
{
album playlLst = new album();
playlLst._id = (int)reader["playlist_id"];
playlLst._name = (string)reader["playlist_name"];
playlLst._playCount = (int)reader["playlist_play_count"];
playlLst._releaseDate = (DateTime)reader["playlist_create_date"];
playlList.Add(playlLst);
}
playlGrid.DataSource = playlList;
playlGrid.DataBind();
reader.Close();
reader = artistCmd.ExecuteReader();
while (reader.Read())
{
artist artistLst = new artist();
artistLst._id = (int)reader["artist_id"];
artistLst._name = (string)reader["artist_name"];
artistLst._debutYear = (DateTime)reader["artist_debut_year"];
artistList.Add(artistLst);
}
artistGrid.DataSource = artistList;
artistGrid.DataBind();
reader.Close();
}
catch (Exception err)
{
lblResult.Text = "Error reading data. <br />";
lblResult.Text += err.Message;
}
finally
{
con.Close();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string song, art, gen;
if (Request.QueryString["song"] == "")
{
song = "";
}
else
{
song = " AND song_name LIKE '%" + Request.QueryString["song"] + "%'";
}
if (Request.QueryString["art"] == "")
{
art = "";
}
else
{
art = " AND artist_name LIKE '%" + Request.QueryString["art"] + "%'";
}
if (Request.QueryString["gen"] == "")
{
gen = "";
}
else
{
gen = " AND genre_name LIKE '%" + Request.QueryString["gen"] + "%'";
}
string sqlStr = "SELECT * " +
"FROM song, artist, genre " +
"WHERE song.artist_id = artist.artist_id " +
"AND song.genre_id = genre.genre_id" +
song + art + gen;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sqlStr, con);
SqlDataReader reader;
ArrayList list = new ArrayList();
try
{
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
song songLst = new song();
songLst._name = (string)reader["song_name"];
songLst._artist = (string)reader["artist_name"];
songLst._genre = (string)reader["genre_name"];
songLst._length = reader["song_length"].ToString();
songLst._downloadCount = (int)reader["song_download_count"];
songLst._playCount = (int)reader["song_play_count"];
songLst._likeCount = (int)reader["song_like_count"];
list.Add(songLst);
}
advanceGrid.DataSource = list;
advanceGrid.DataBind();
reader.Close();
//if (reader.NextResult()==false)
//{
// lblResult.Text = "No record found!!";
//}
//else
//{
//lblResult.Text = "<h3> This is the f*****g list you need to search: </h3><br />";
//lblResult.Text += "<table border = '0'><tr>"
// + "<td style='width:200px'><b> Ful Name </b></td>"
// + "<td style='width:200px'><b> Phone Number </b></td>"
// + "<td style='width:200px'><b> Address </b></td></b></tr>";
//while (reader.Read())
//{
// lblResult.Text += "<tr><td>" + reader["fname"] + " " + reader["lname"]
// + "</td> <td>" + reader["pr_info"]
// + "</td> <td>" + reader["job_desc"]
// + "</td></tr>";
//}
//lblResult.Text += "</table>";
//}
}
catch (Exception err)
{
lblResult.Text = "Error reading data. <br />";
lblResult.Text += err.Message;
}
finally
{
con.Close();
}
}