API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 10th, 2017
133
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 37.82 KB | None | 0 0
raw download clone embed print report
  1. statistics
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Data.SqlClient;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  9.  
  10. public partial class Statistics : System.Web.UI.Page
  11. {
  12. protected void Page_Load(object sender, EventArgs e)
  13. {
  14. usernameLink.Text = (String)Session["username"];
  15.  
  16. SqlConnection konekcija = new SqlConnection();
  17. konekcija.ConnectionString = "Data Source=localhost;"
  18. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  19.  
  20. SqlCommand timeSpent = new SqlCommand();
  21. SqlCommand songsPlayed = new SqlCommand();
  22. SqlCommand diffSongsPlayed = new SqlCommand();
  23. SqlCommand numOfPlaylists = new SqlCommand();
  24. SqlCommand biggestSong = new SqlCommand();
  25. SqlCommand biggestArtist = new SqlCommand();
  26.  
  27. timeSpent.Connection = konekcija;
  28. songsPlayed.Connection = konekcija;
  29. diffSongsPlayed.Connection = konekcija;
  30. numOfPlaylists.Connection = konekcija;
  31. biggestArtist.Connection = konekcija;
  32. biggestSong.Connection = konekcija;
  33.  
  34. timeSpent.CommandText = "select sum(Song_length) total from Statistic ST, Song S where ST.Song_ID=S.Song_ID";
  35. songsPlayed.CommandText = "select count(*) total from Statistic where username ='" + Session["username"] + "'"; //add username
  36. diffSongsPlayed.CommandText = "select count(distinct Song_ID) nmb from Statistic where username ='" + Session["username"] + "'";
  37. numOfPlaylists.CommandText = "select count(Playlist_ID) nmb from Playlist";
  38. biggestArtist.CommandText = "";
  39. biggestSong.CommandText = "select Song_Name, max(ST.Play_count) maks from Song S, Statistic ST where S.Song_ID=ST.Song_ID group by Song_Name order by maks desc";
  40. try
  41. {
  42. konekcija.Open();
  43.  
  44. SqlDataReader citac = numOfPlaylists.ExecuteReader();
  45. if (citac.Read())
  46. playlistsnum.Text = citac["nmb"].ToString();
  47. citac.Close();
  48.  
  49. citac = timeSpent.ExecuteReader();
  50. if (citac.Read())
  51. minutes.Text = (Int32.Parse(citac["total"].ToString())/60).ToString();
  52. hours.Text = String.Format("{0:0.##X}", (Double.Parse(minutes.Text.ToString()) / 60).ToString());
  53. days.Text = (Double.Parse(hours.Text.ToString())/24).ToString();
  54. citac.Close();
  55.  
  56. citac = songsPlayed.ExecuteReader();
  57. if (citac.Read())
  58. songslistened.Text = citac["total"].ToString();
  59. citac.Close();
  60.  
  61. citac = diffSongsPlayed.ExecuteReader();
  62. if (citac.Read())
  63. different.Text = citac["nmb"].ToString();
  64. citac.Close();
  65.  
  66. citac = biggestSong.ExecuteReader();
  67. if (citac.Read())
  68. mpSong.Text = citac["Song_Name"].ToString();
  69. citac.Close();
  70.  
  71. }
  72. finally
  73. {
  74. konekcija.Close();
  75. }
  76. }
  77.  
  78. protected void logout (object sender, EventArgs e)
  79. {
  80. Session["username"] = null;
  81. Server.Transfer("LogIn.aspx", true);
  82. }
  83. }
  84.  
  85. sing up
  86. using System;
  87. using System.Collections.Generic;
  88. using System.Data.SqlClient;
  89. using System.Linq;
  90. using System.Web;
  91. using System.Web.UI;
  92. using System.Web.UI.WebControls;
  93.  
  94. public partial class SignUp : System.Web.UI.Page
  95. {
  96. protected void Page_Load(object sender, EventArgs e)
  97. {
  98.  
  99. }
  100.  
  101. protected void register_Click(object sender, EventArgs e)
  102. {
  103. //proveri dali vnesenoto korisnichko ime go nema vo bazata
  104. Boolean unique = true;
  105. String names;
  106.  
  107. SqlConnection konekcija = new SqlConnection();
  108. konekcija.ConnectionString = "Data Source=localhost;"
  109. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  110.  
  111. SqlCommand komanda = new SqlCommand();
  112. SqlCommand proverka = new SqlCommand();
  113.  
  114. komanda.Connection = konekcija;
  115. proverka.Connection = konekcija;
  116.  
  117. komanda.CommandText = "INSERT INTO Users values " +
  118. "('" + username.Text + "'," +
  119. "'" + password.Text + "'," +
  120. "'" + mail.Text + "')";
  121. proverka.CommandText = "SELECT * FROM Users";
  122.  
  123. try
  124. {
  125. konekcija.Open();
  126. SqlDataReader citac = proverka.ExecuteReader();
  127. while (citac.Read())
  128. {
  129. names = citac["username"].ToString();
  130. if (names.Equals(username.Text))
  131. {
  132. unique = false;
  133. break;
  134. }
  135. }
  136. }
  137. finally
  138. {
  139. konekcija.Close();
  140. }
  141.  
  142. if (unique)
  143. {
  144. try
  145. {
  146. konekcija.Open();
  147. komanda.ExecuteNonQuery();
  148. }
  149. finally
  150. {
  151. konekcija.Close();
  152. }
  153. Server.Transfer("LogIn.aspx", true);
  154. }
  155. else
  156. {
  157. messages.Text = "The username is in use. Please enter another one";
  158. }
  159.  
  160. }
  161. }
  162.  
  163. search
  164. using System;
  165. using System.Collections.Generic;
  166. using System.Data;
  167. using System.Data.SqlClient;
  168. using System.Linq;
  169. using System.Web;
  170. using System.Web.UI;
  171. using System.Web.UI.WebControls;
  172.  
  173. public partial class Search : System.Web.UI.Page
  174. {
  175. protected void Page_Load(object sender, EventArgs e)
  176. {
  177. usernameLink.Text = (String)Session["username"];
  178. if (!IsPostBack)
  179. fillSearch();
  180. }
  181.  
  182. protected void fillSearch()
  183. {
  184. SqlConnection konekcija = new SqlConnection();
  185. konekcija.ConnectionString = "Data Source=localhost;"
  186. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  187. SqlCommand komanda = new SqlCommand();
  188. komanda.Connection = konekcija;
  189.  
  190. komanda.CommandText = "select Artist_Name, Song_Name, Album_Name, Release_year, url, Song_ID from Artist A, Album AL, Song S WHERE A.Artist_ID=AL.Artist_ID AND S.Album_ID=AL.Album_ID AND S.Artist_ID=A.Artist_ID";
  191. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  192. DataSet ds = new DataSet();
  193.  
  194. try
  195. {
  196. konekcija.Open();
  197. adapter.Fill(ds, "Search");
  198. gvSearch.DataSource = ds;
  199. gvSearch.DataBind();
  200. ViewState["dataset"] = ds;
  201. }
  202. finally
  203. {
  204. konekcija.Close();
  205. }
  206. }
  207.  
  208. protected void keywordEvent(object sender, EventArgs e)
  209. {
  210. SqlConnection konekcija = new SqlConnection();
  211. konekcija.ConnectionString = "Data Source=localhost;"
  212. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  213. SqlCommand komanda = new SqlCommand();
  214. komanda.Connection = konekcija;
  215.  
  216. String tag = keyword.Text.ToString();
  217. komanda.CommandText = "select Artist_Name, Song_Name, Album_Name, Release_year, url, Song_ID from Artist A, Album AL, Song S WHERE A.Artist_ID=AL.Artist_ID AND S.Album_ID=AL.Album_ID AND S.Artist_ID=A.Artist_ID and (Artist_Name like '%" + tag + "%' or Song_Name like '%" + tag + "%' or Album_Name like '%" + tag + "%')";
  218. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  219. DataSet ds = new DataSet();
  220.  
  221. try
  222. {
  223. konekcija.Open();
  224. adapter.Fill(ds, "Search");
  225. gvSearch.DataSource = ds;
  226. gvSearch.DataBind();
  227. ViewState["dataset"] = ds;
  228. }
  229. finally
  230. {
  231. konekcija.Close();
  232. }
  233. }
  234.  
  235. protected void gvSearchSelect(object sender, EventArgs e)
  236. {
  237. player.Src = gvSearch.SelectedRow.Cells[5].Text.ToString();
  238.  
  239. //pri pritiskanje na PLAY, azhuriraj ja tabelata Statistic
  240. SqlConnection konekcija = new SqlConnection();
  241. konekcija.ConnectionString = "Data Source=localhost;"
  242. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  243.  
  244. SqlCommand maxIndex = new SqlCommand();
  245. SqlCommand playCount = new SqlCommand();
  246. SqlCommand play = new SqlCommand();
  247.  
  248. maxIndex.Connection = konekcija;
  249. play.Connection = konekcija;
  250. playCount.Connection = konekcija;
  251.  
  252. int max = 0, playcount = 0;
  253.  
  254. maxIndex.CommandText = "select ISNULL(max(Stat_ID), 0) maks from Statistic";
  255. playCount.CommandText = "select ISNULL(max(Play_count),0) maks from Statistic where Song_ID=" + Int32.Parse(gvSearch.SelectedRow.Cells[6].Text);
  256. SqlDataReader citac = null;
  257. try
  258. {
  259. konekcija.Open();
  260. citac = maxIndex.ExecuteReader();
  261. if (citac.Read())
  262. {
  263. max = Int32.Parse(citac["maks"].ToString());
  264. }
  265. citac.Close();
  266. citac = playCount.ExecuteReader();
  267. if (citac.Read())
  268. {
  269. playcount = Int32.Parse(citac["maks"].ToString());
  270. }
  271. citac.Close();
  272. max++;
  273. playcount++;
  274. play.CommandText = "insert into Statistic values (" + max + "," + playcount + "," + "'2017-08-02', '" + Session["username"] + "'," + Int32.Parse(gvSearch.SelectedRow.Cells[6].Text) + ")";
  275. play.ExecuteNonQuery();
  276. }
  277. finally
  278. {
  279. konekcija.Close();
  280. }
  281. }
  282.  
  283.  
  284. protected void logout(object sender, EventArgs e)
  285. {
  286. Session["username"] = null;
  287. Server.Transfer("LogIn.aspx", true);
  288. }
  289. }
  290. profile page
  291. using System;
  292. using System.Collections.Generic;
  293. using System.Data.SqlClient;
  294. using System.Linq;
  295. using System.Web;
  296. using System.Web.UI;
  297. using System.Web.UI.WebControls;
  298.  
  299. public partial class ProfilePage : System.Web.UI.Page
  300. {
  301. protected void home(object sender, EventArgs e)
  302. {
  303. //Server.Transfer("HomePage.aspx", true);
  304. }
  305. protected void search(object sender, EventArgs e)
  306. {
  307. Server.Transfer("Search.aspx", true);
  308. }
  309. protected void playlists(object sender, EventArgs e)
  310. {
  311. Server.Transfer("Playlists.aspx", true);
  312. }
  313. protected void logOut(object sender, EventArgs e)
  314. {
  315. Session["username"] = null;
  316. Server.Transfer("LogIn.aspx", true);
  317. }
  318.  
  319. protected void History(object sender, EventArgs e)
  320. {
  321. Server.Transfer("History.aspx", true);
  322. }
  323.  
  324.  
  325. protected void Page_Load(object sender, EventArgs e)
  326. {
  327. usernameLink.Text = (String)Session["username"];
  328.  
  329. String mypassword = "", mymail = "";
  330. SqlConnection konekcija = new SqlConnection();
  331. konekcija.ConnectionString = "Data Source=localhost;"
  332. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  333.  
  334. SqlCommand getUser = new SqlCommand();
  335.  
  336. getUser.Connection = konekcija;
  337.  
  338. getUser.CommandText = "select * from Users where Username='" + Session["username"] + "'";
  339.  
  340. username.Text = (String)Session["username"];
  341.  
  342. try
  343. {
  344. konekcija.Open();
  345.  
  346. SqlDataReader citac = getUser.ExecuteReader();
  347. if (citac.Read())
  348. {
  349. mymail = citac["mail"].ToString();
  350. for (int i = 0; i < citac["password"].ToString().Length; i++)
  351. {
  352. mypassword += "•";
  353. }
  354. password.Text = mypassword;
  355. email.Text = mymail;
  356. citac.Close();
  357. }
  358.  
  359. }
  360. catch
  361. {
  362.  
  363. }
  364. finally
  365. {
  366. konekcija.Close();
  367. }
  368. }
  369.  
  370. protected void newPassB_Click(object sender, EventArgs e)
  371. {
  372. SqlConnection konekcija = new SqlConnection();
  373. konekcija.ConnectionString = "Data Source=localhost;"
  374. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  375. SqlCommand komanda = new SqlCommand();
  376. komanda.Connection = konekcija;
  377.  
  378. komanda.CommandText = "UPDATE Users SET password='"
  379. + newPass.Text + "' WHERE Username='" + username.Text + "'";
  380.  
  381. try
  382. {
  383. konekcija.Open();
  384. komanda.ExecuteNonQuery();
  385. Response.Redirect("ProfilePage.aspx");
  386. }
  387. finally
  388. {
  389. konekcija.Close();
  390. }
  391. }
  392. }
  393.  
  394. playlist
  395. using System;
  396. using System.Collections.Generic;
  397. using System.Data;
  398. using System.Data.SqlClient;
  399. using System.Linq;
  400. using System.Web;
  401. using System.Web.UI;
  402. using System.Web.UI.WebControls;
  403.  
  404. public partial class Playlists : System.Web.UI.Page
  405. {
  406. protected void Page_Load(object sender, EventArgs e)
  407. {
  408. //thesongs.Text = (String)Session["theSongs"];
  409. usernameLink.Text = (String)Session["username"];
  410. fillPlaylists();
  411. }
  412.  
  413. protected void fillPlaylists()
  414. {
  415. SqlConnection konekcija = new SqlConnection();
  416. konekcija.ConnectionString = "Data Source=localhost;"
  417. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  418. SqlCommand komanda = new SqlCommand();
  419. komanda.Connection = konekcija;
  420.  
  421. komanda.CommandText = "select Playlist_name from Users U, Playlist P, Possession PO where U.Username=PO.Username AND PO.Playlist_ID=P.Playlist_ID"; //TO-DO: dodadi AND U.Username=" + Session["username"]
  422.  
  423. try
  424. {
  425. konekcija.Open();
  426. SqlDataReader citac = komanda.ExecuteReader();
  427. while (citac.Read())
  428. {
  429.  
  430. ImageButton pic = new ImageButton ();
  431. pic.ImageUrl = "http://goo.gl/CzIJEx";
  432. pic.CssClass = "pics";
  433.  
  434. pic.Height = 125;
  435. pic.Width = 125;
  436. pic.AlternateText = citac["Playlist_name"].ToString();
  437. pic.Click += new ImageClickEventHandler(getPlaylist);
  438.  
  439. LinkButton name = new LinkButton();
  440. name.Text = String.Format("{0, 200}", citac["Playlist_name"].ToString());
  441. Label blankSpace = new Label();
  442. blankSpace.Text = "\n";
  443.  
  444. playlistPics.Controls.Add(pic);
  445. //playlistPics.Controls.Add(blankSpace);
  446. playlistPics.Controls.Add(name);
  447. // playlistNames.Controls.Add(name);
  448.  
  449. }
  450. }
  451. catch
  452. {
  453.  
  454. }
  455. finally
  456. {
  457. konekcija.Close();
  458. }
  459. }
  460.  
  461. protected void ok(object sender, EventArgs e)
  462. {
  463. /* String getSongs = thesongs.Text.ToString();
  464. thesongs.Text = getSongs;
  465. Session["theSongs"] = getSongs;
  466. thesongs.Text = (String)Session["theSongs"];*/
  467. //String getSongs = listbox.InnerHtml;
  468. //Label1.Text += getSongs + "DONE!!!!!!!!!!!!!";
  469. /*SqlConnection konekcija = new SqlConnection();
  470. konekcija.ConnectionString = "Data Source=localhost;"
  471. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  472.  
  473. SqlCommand index = new SqlCommand();
  474. index.Connection = konekcija;
  475.  
  476. SqlCommand playlist = new SqlCommand();
  477. playlist.Connection = konekcija;
  478.  
  479. SqlCommand possession = new SqlCommand();
  480. possession.Connection = konekcija;
  481.  
  482. index.CommandText = "select max(Playlist_ID) maks from Playlist";
  483. int indeks = 0;
  484. try
  485. {
  486. konekcija.Open();
  487.  
  488. SqlDataReader citac = index.ExecuteReader();
  489. if (citac.Read())
  490. {
  491. indeks = Int32.Parse(citac["maks"].ToString()) + 1;
  492. }
  493.  
  494. playlist.CommandText = "insert into Playlist values (" + indeks + "," + newPlaylistName.ToString() + ")";
  495. possession.CommandText = "insert into Possession values (" + Session["username"] + "," + indeks + ")";
  496.  
  497. playlist.ExecuteNonQuery();
  498. possession.ExecuteNonQuery();
  499.  
  500. Server.Transfer("Playlists.aspx");
  501. }
  502. finally
  503. {
  504. konekcija.Close();
  505. }*/
  506. }
  507.  
  508. protected void logout(object sender, EventArgs e)
  509. {
  510. Session["username"] = null;
  511. Server.Transfer("LogIn.aspx", true);
  512. }
  513.  
  514. protected void getPlaylist (object sender, EventArgs e)
  515. {
  516. ImageButton clicked = (ImageButton)sender;
  517. Session["playlist"] = clicked.AlternateText.ToString();
  518. logOutButton.Text = (String)Session["playlist"];
  519. Response.Redirect("PlaylistPage.aspx");
  520. }
  521.  
  522. protected void newPlaylist(object sender, EventArgs e)
  523. {
  524. Response.Redirect("NewPlaylist.aspx");
  525. }
  526. }
  527. playlistpage
  528. using System;
  529. using System.Collections.Generic;
  530. using System.Data;
  531. using System.Data.SqlClient;
  532. using System.Linq;
  533. using System.Web;
  534. using System.Web.UI;
  535. using System.Web.UI.WebControls;
  536.  
  537. public partial class PlaylistPage : System.Web.UI.Page
  538. {
  539. protected void Page_Load(object sender, EventArgs e)
  540. {
  541. usernameLink.Text = (String)Session["username"];
  542.  
  543. if (!IsPostBack)
  544. fillPlaylist();
  545. }
  546.  
  547. protected void fillPlaylist()
  548. {
  549. SqlConnection konekcija = new SqlConnection();
  550. konekcija.ConnectionString = "Data Source=localhost;"
  551. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  552. SqlCommand komanda = new SqlCommand();
  553. komanda.Connection = konekcija;
  554.  
  555. komanda.CommandText = "select * from Playlist P, Appearance A, Song S, Artist AR, Album AL where P.Playlist_ID=A.Playlist_ID and S.Song_ID=A.Song_ID and S.Artist_ID=AR.Artist_ID and S.Album_ID=AL.Album_ID and P.Playlist_name = '" + Session["playlist"] + "'";
  556.  
  557. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  558. DataSet ds = new DataSet();
  559.  
  560. try
  561. {
  562. konekcija.Open();
  563. adapter.Fill(ds, "Playlist");
  564. gvPlaylist.DataSource = ds;
  565. gvPlaylist.DataBind();
  566. ViewState["dataset"] = ds;
  567. }
  568. finally
  569. {
  570. konekcija.Close();
  571. }
  572. }
  573.  
  574. protected void logout(object sender, EventArgs e)
  575. {
  576. Session["username"] = null;
  577. Server.Transfer("LogIn.aspx", true);
  578. }
  579. }
  580. history
  581. using System;
  582. using System.Collections.Generic;
  583. using System.Data;
  584. using System.Data.SqlClient;
  585. using System.Linq;
  586. using System.Web;
  587. using System.Web.UI;
  588. using System.Web.UI.WebControls;
  589.  
  590. public partial class History : System.Web.UI.Page
  591. {
  592. protected void Page_Load(object sender, EventArgs e)
  593. {
  594. usernameLink.Text = (String)Session["username"];
  595.  
  596. if (!IsPostBack)
  597. fillHistory();
  598. }
  599.  
  600. protected void fillHistory()
  601. {
  602. SqlConnection konekcija = new SqlConnection();
  603. konekcija.ConnectionString = "Data Source=localhost;"
  604. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  605. SqlCommand komanda = new SqlCommand();
  606. komanda.Connection = konekcija;
  607.  
  608. komanda.CommandText = "select A.Artist_Name, S.Song_Name, ST.Play_count, ST.Date, S.url, S.Song_ID from Song S, Statistic ST, Artist A where S.Song_ID=ST.Song_ID and S.Artist_ID=A.Artist_ID"; //and ST.Username ='" + Session["username"] + "'";
  609. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  610. DataSet ds = new DataSet();
  611.  
  612. try
  613. {
  614. konekcija.Open();
  615. adapter.Fill(ds, "History");
  616. gvHistory.DataSource = ds;
  617. gvHistory.DataBind();
  618. ViewState["dataset"] = ds;
  619. }
  620. catch
  621. {
  622.  
  623. }
  624. finally
  625. {
  626. konekcija.Close();
  627. }
  628. }
  629.  
  630. protected void dateEvent(object sender, EventArgs e)
  631. {
  632. SqlConnection konekcija = new SqlConnection();
  633. konekcija.ConnectionString = "Data Source=localhost;"
  634. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  635. SqlCommand komanda = new SqlCommand();
  636. komanda.Connection = konekcija;
  637.  
  638. String pickedDate = datepicker.Text.ToString().Replace('/', '-');
  639. String year = pickedDate.Substring(6, 4), month = pickedDate.Substring(3, 2), day = pickedDate.Substring(0, 2);
  640. komanda.CommandText = "select A.Artist_Name, S.Song_Name, ST.Play_count, ST.Date, S.url, S.Song_ID from Song S, Statistic ST, Artist A where S.Song_ID=ST.Song_ID and S.Artist_ID=A.Artist_ID and ST.Date LIKE '" + year + "%" + month + "%" + day + "'" + "and ST.Username ='" + Session["username"] + "'";
  641. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  642. DataSet ds = new DataSet();
  643.  
  644. try
  645. {
  646. konekcija.Open();
  647. adapter.Fill(ds, "History");
  648. gvHistory.DataSource = ds;
  649. gvHistory.DataBind();
  650. ViewState["dataset"] = ds;
  651. }
  652. finally
  653. {
  654. konekcija.Close();
  655. }
  656. }
  657.  
  658. protected void gvHistorySelect(object sender, EventArgs e)
  659. {
  660. player.Src = gvHistory.SelectedRow.Cells[5].Text;
  661.  
  662. //pri pritiskanje na PLAY, azhuriraj ja tabelata Statistic
  663. SqlConnection konekcija = new SqlConnection();
  664. konekcija.ConnectionString = "Data Source=localhost;"
  665. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  666.  
  667. SqlCommand maxIndex = new SqlCommand();
  668. SqlCommand playCount = new SqlCommand();
  669. SqlCommand play = new SqlCommand();
  670.  
  671. maxIndex.Connection = konekcija;
  672. play.Connection = konekcija;
  673. playCount.Connection = konekcija;
  674.  
  675. int max = 0, playcount = 0;
  676.  
  677. maxIndex.CommandText = "select ISNULL(max(Stat_ID), 0) maks from Statistic";
  678. playCount.CommandText = "select ISNULL(max(Play_count),0) maks from Statistic where Song_ID=" + Int32.Parse(gvHistory.SelectedRow.Cells[6].Text);
  679. SqlDataReader citac = null;
  680. try
  681. {
  682. konekcija.Open();
  683. citac = maxIndex.ExecuteReader();
  684. if (citac.Read())
  685. {
  686. max = Int32.Parse(citac["maks"].ToString());
  687. }
  688. citac.Close();
  689. citac = playCount.ExecuteReader();
  690. if (citac.Read())
  691. {
  692. playcount = Int32.Parse(citac["maks"].ToString());
  693. }
  694. citac.Close();
  695. max++;
  696. playcount++;
  697. play.CommandText = "insert into Statistic values (" + max + "," + playcount + "," + "'2017-08-02', '" + Session["username"] + "'," + Int32.Parse(gvHistory.SelectedRow.Cells[6].Text) + ")";
  698. play.ExecuteNonQuery();
  699. }
  700. finally
  701. {
  702. konekcija.Close();
  703. }
  704. }
  705.  
  706. protected void logout(object sender, EventArgs e)
  707. {
  708. Session["username"] = null;
  709. Server.Transfer("LogIn.aspx", true);
  710. }
  711. }
  712. custom
  713. using System;
  714. using System.Collections.Generic;
  715. using System.Data;
  716. using System.Data.SqlClient;
  717. using System.Linq;
  718. using System.Web;
  719. using System.Web.UI;
  720. using System.Web.UI.WebControls;
  721.  
  722. public partial class Custom : System.Web.UI.Page
  723. {
  724. protected void Page_Load(object sender, EventArgs e)
  725. {
  726. usernameLink.Text = (String)Session["username"];
  727.  
  728. if (!IsPostBack)
  729. {
  730. fillGenre();
  731. fillPlaylistLength();
  732. fillMostPlayed();
  733. }
  734. }
  735.  
  736. protected void fillGenre ()
  737. {
  738. SqlConnection konekcija = new SqlConnection();
  739. konekcija.ConnectionString = "Data Source=localhost;"
  740. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  741. SqlCommand komanda = new SqlCommand();
  742. komanda.Connection = konekcija;
  743.  
  744. komanda.CommandText = "select genre.genre_name Genre, max(play_count) Plays, date Date from statistic, song, genre, is_genre where song.Song_ID=Statistic.Song_ID and song.song_id=is_genre.song_id and genre.genre_id=is_genre.genre_id group by genre.genre_name,Date";
  745. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  746. DataSet ds = new DataSet();
  747.  
  748. try
  749. {
  750. konekcija.Open();
  751. adapter.Fill(ds, "Genre");
  752. gvGenre.DataSource = ds;
  753. gvGenre.DataBind();
  754. ViewState["dataset"] = ds;
  755. }
  756. finally
  757. {
  758. konekcija.Close();
  759. }
  760. }
  761.  
  762. protected void fillPlaylistLength()
  763. {
  764. SqlConnection konekcija = new SqlConnection();
  765. konekcija.ConnectionString = "Data Source=localhost;"
  766. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  767. SqlCommand komanda = new SqlCommand();
  768. komanda.Connection = konekcija;
  769.  
  770. komanda.CommandText = "select playlist_name,sum(song.song_length) from song,playlist,appearance where song.song_id=appearance.song_id and playlist.playlist_id=appearance.playlist_id group by playlist.playlist_name having sum(song.song_length)>500";
  771. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  772. DataSet ds = new DataSet();
  773.  
  774. try
  775. {
  776. konekcija.Open();
  777. adapter.Fill(ds, "Genre");
  778. gvPlaylistLength.DataSource = ds;
  779. gvPlaylistLength.DataBind();
  780. ViewState["dataset"] = ds;
  781. }
  782. finally
  783. {
  784. konekcija.Close();
  785. }
  786. }
  787.  
  788. protected void fillMostPlayed()
  789. {
  790. SqlConnection konekcija = new SqlConnection();
  791. konekcija.ConnectionString = "Data Source=localhost;"
  792. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  793. SqlCommand komanda = new SqlCommand();
  794. komanda.Connection = konekcija;
  795.  
  796. komanda.CommandText = "select Album.Album_Name, MaxAlbumPlays.MaxPlays from Album inner join (select Album.Album_ID as AlbumID, max(Statistic.Play_count) as MaxPlays from Album, Song, Statistic where Album.Album_ID=Song.Album_ID and Song.Song_ID=Statistic.Song_ID group by Album.Album_ID) MaxAlbumPlays on Album.Album_ID = MaxAlbumPlays.AlbumID";
  797. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  798. DataSet ds = new DataSet();
  799.  
  800. try
  801. {
  802. konekcija.Open();
  803. adapter.Fill(ds, "Genre");
  804. gvMostPlayed.DataSource = ds;
  805. gvMostPlayed.DataBind();
  806. ViewState["dataset"] = ds;
  807. }
  808. finally
  809. {
  810. konekcija.Close();
  811. }
  812. }
  813.  
  814. protected void logout(object sender, EventArgs e)
  815. {
  816. Session["username"] = null;
  817. Server.Transfer("LogIn.aspx", true);
  818. }
  819. }
  820. charts
  821. using System;
  822. using System.Collections.Generic;
  823. using System.Data;
  824. using System.Data.SqlClient;
  825. using System.Linq;
  826. using System.Web;
  827. using System.Web.UI;
  828. using System.Web.UI.WebControls;
  829.  
  830. public partial class Charts : System.Web.UI.Page
  831. {
  832. protected void Page_Load(object sender, EventArgs e)
  833. {
  834. usernameLink.Text = (String)Session["username"];
  835.  
  836. if (!IsPostBack)
  837. fillChart();
  838. }
  839. protected void fillChart ()
  840. {
  841. SqlConnection konekcija = new SqlConnection();
  842. konekcija.ConnectionString = "Data Source=localhost;"
  843. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  844. SqlCommand komanda = new SqlCommand();
  845. komanda.Connection = konekcija;
  846.  
  847. String tag = keyword.Text.ToString();
  848. komanda.CommandText = "select Artist_Name, S.Song_Name, Album_Name, C.times_listened from Artist A, Song S, Album AL, (select S.Song_ID as song_ID, count(S.Song_ID) times_listened from Artist A, Song S, Album AL, Statistic ST where A.Artist_ID=S.Artist_ID and AL.Album_ID=S.Album_ID and S.Song_ID=ST.Song_ID group by S.Song_ID) C where A.Artist_ID=S.Artist_ID and AL.Album_ID=S.Album_ID and C.song_ID=S.Song_ID order by times_listened desc";
  849. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  850. DataSet ds = new DataSet();
  851.  
  852. try
  853. {
  854. konekcija.Open();
  855. adapter.Fill(ds, "Search");
  856. gvYear.DataSource = ds;
  857. gvYear.DataBind();
  858. ViewState["dataset"] = ds;
  859. }
  860. finally
  861. {
  862. konekcija.Close();
  863. }
  864. }
  865.  
  866. protected void keywordEvent(object sender, EventArgs e)
  867. {
  868. String year = keyword.Text.ToString();
  869.  
  870. SqlConnection konekcija = new SqlConnection();
  871. konekcija.ConnectionString = "Data Source=localhost;"
  872. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  873. SqlCommand komanda = new SqlCommand();
  874. komanda.Connection = konekcija;
  875.  
  876. String tag = keyword.Text.ToString();
  877. komanda.CommandText = "select Artist_Name, S.Song_Name, Album_Name, C.times_listened from Artist A, Song S, Album AL, (select S.Song_ID as song_ID, count(S.Song_ID) times_listened from Artist A, Song S, Album AL, Statistic ST where A.Artist_ID=S.Artist_ID and AL.Album_ID=S.Album_ID and S.Song_ID=ST.Song_ID and ST.Date like '" + year + "%' group by S.Song_ID) C where A.Artist_ID=S.Artist_ID and AL.Album_ID=S.Album_ID and C.song_ID=S.Song_ID";
  878. SqlDataAdapter adapter = new SqlDataAdapter(komanda);
  879. DataSet ds = new DataSet();
  880.  
  881. try
  882. {
  883. konekcija.Open();
  884. adapter.Fill(ds, "Search");
  885. gvYear.DataSource = ds;
  886. gvYear.DataBind();
  887. ViewState["dataset"] = ds;
  888. }
  889. finally
  890. {
  891. konekcija.Close();
  892. }
  893. }
  894.  
  895. protected void logout(object sender, EventArgs e)
  896. {
  897. Session["username"] = null;
  898. Server.Transfer("LogIn.aspx", true);
  899. }
  900. }
  901. home page
  902. using System;
  903. using System.Collections.Generic;
  904. using System.Data;
  905. using System.Data.SqlClient;
  906. using System.Linq;
  907. using System.Web;
  908. using System.Web.UI;
  909. using System.Web.UI.WebControls;
  910.  
  911. public partial class HomePage : System.Web.UI.Page
  912. {
  913. protected void Page_Load(object sender, EventArgs e)
  914. {
  915. usernameLink.Text = (String)Session["username"];
  916. fillNewMusic();
  917. //fillRecentArtists();
  918. //fillRecentSongs();
  919. }
  920. protected void fillNewMusic()
  921. {
  922. SqlConnection konekcija = new SqlConnection();
  923. konekcija.ConnectionString = "Data Source=localhost;"
  924. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  925. SqlCommand newmusic = new SqlCommand();
  926. SqlCommand recentartist = new SqlCommand();
  927. SqlCommand recentsong = new SqlCommand();
  928.  
  929. newmusic.Connection = konekcija;
  930. recentartist.Connection = konekcija;
  931. recentsong.Connection = konekcija;
  932.  
  933. newmusic.CommandText = "select top 5 Song_Name from Song S, Album A where S.Album_ID=A.Album_ID order by Release_year desc";
  934. recentartist.CommandText = "select distinct top 5 Artist_Name from Artist A, Song S, Statistic ST where ST.Song_ID=S.Song_ID and S.Artist_ID=A.Artist_ID";
  935. recentsong.CommandText = "select distinct top 5 Song_Name from Song S, Statistic ST where ST.Song_ID=S.Song_ID";
  936.  
  937. try
  938. {
  939. konekcija.Open();
  940. SqlDataReader citac = newmusic.ExecuteReader();
  941. while (citac.Read())
  942. {
  943. ImageButton pic = new ImageButton();
  944. pic.ImageUrl = "http://goo.gl/CzIJEx";
  945. pic.CssClass = "pics";
  946.  
  947. pic.Height = 125;
  948. pic.Width = 125;
  949.  
  950. LinkButton name = new LinkButton();
  951. name.Text = String.Format("{0: 20}", citac["Song_Name"].ToString());
  952.  
  953. Label blankSpace = new Label();
  954. blankSpace.Text = "\n";
  955.  
  956. playlistPics.Controls.Add(pic);
  957. //playlistPics.Controls.Add(blankSpace);
  958. playlistPics.Controls.Add(name);
  959. // playlistNames.Controls.Add(name);
  960. }
  961. citac.Close();
  962.  
  963. citac = recentartist.ExecuteReader();
  964. while (citac.Read())
  965. {
  966. ImageButton pic = new ImageButton();
  967. pic.ImageUrl = "http://goo.gl/CzIJEx";
  968. pic.CssClass = "pics";
  969.  
  970. pic.Height = 125;
  971. pic.Width = 125;
  972.  
  973. LinkButton name = new LinkButton();
  974. name.Text = String.Format("{0: 20}", citac["Artist_Name"].ToString());
  975.  
  976. Label blankSpace = new Label();
  977. blankSpace.Text = "\n";
  978.  
  979. playlistPics1.Controls.Add(pic);
  980. //playlistPics.Controls.Add(blankSpace);
  981. playlistPics1.Controls.Add(name);
  982. // playlistNames.Controls.Add(name);
  983. }
  984. citac.Close();
  985.  
  986. citac = recentsong.ExecuteReader();
  987. while (citac.Read())
  988. {
  989. ImageButton pic = new ImageButton();
  990. pic.ImageUrl = "http://goo.gl/CzIJEx";
  991. pic.CssClass = "pics";
  992.  
  993. pic.Height = 125;
  994. pic.Width = 125;
  995.  
  996. LinkButton name = new LinkButton();
  997. name.Text = String.Format("{0: 20}", citac["Song_Name"].ToString());
  998.  
  999. Label blankSpace = new Label();
  1000. blankSpace.Text = "\n";
  1001.  
  1002. playlistPics2.Controls.Add(pic);
  1003. //playlistPics.Controls.Add(blankSpace);
  1004. playlistPics2.Controls.Add(name);
  1005. // playlistNames.Controls.Add(name);
  1006. }
  1007. citac.Close();
  1008. }
  1009. catch
  1010. {
  1011.  
  1012. }
  1013. finally
  1014. {
  1015. konekcija.Close();
  1016. }
  1017. }
  1018.  
  1019. protected void logout(object sender, EventArgs e)
  1020. {
  1021. Session["username"] = null;
  1022. Server.Transfer("LogIn.aspx", true);
  1023. }
  1024. }
  1025. log in
  1026. using System;
  1027. using System.Collections.Generic;
  1028. using System.Data.SqlClient;
  1029. using System.Linq;
  1030. using System.Web;
  1031. using System.Web.UI;
  1032. using System.Web.UI.WebControls;
  1033.  
  1034. public partial class LogIn : System.Web.UI.Page
  1035. {
  1036. protected void Page_Load(object sender, EventArgs e)
  1037. {
  1038.  
  1039. }
  1040.  
  1041. protected void logIn (object sender, EventArgs e)
  1042. {
  1043. String dbPassword = "";
  1044. SqlConnection konekcija = new SqlConnection();
  1045. konekcija.ConnectionString = "Data Source=localhost;"
  1046. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  1047. SqlCommand komanda = new SqlCommand();
  1048. komanda.Connection = konekcija;
  1049.  
  1050. //username i password vneseni od korisnikot
  1051. String theUsername = username.Text.ToString();
  1052. String thePassword = password.Text.ToString();
  1053.  
  1054. komanda.CommandText = "select * FROM Users WHERE username='"
  1055. + theUsername.ToString() + "'";
  1056. try
  1057. {
  1058. konekcija.Open();
  1059. SqlDataReader citac = komanda.ExecuteReader();
  1060. if (citac.Read())
  1061. {
  1062. dbPassword = citac["password"].ToString();
  1063. citac.Close();
  1064. }
  1065. }
  1066. catch
  1067. {
  1068. error.Text = "An error has occured. Please try again";
  1069. }
  1070. finally
  1071. {
  1072. konekcija.Close();
  1073. }
  1074.  
  1075. if (dbPassword.Equals(""))
  1076. {
  1077. error.Text = "Username does NOT exist";
  1078. } else if (dbPassword.Equals(thePassword))
  1079. {
  1080. Session["username"] = (String)username.Text;
  1081. Response.Redirect("HomePage.aspx");
  1082. } else if (!dbPassword.Equals(thePassword))
  1083. {
  1084. error.Text = "Wrong Password! Try again";
  1085. }
  1086.  
  1087. }
  1088. }
  1089. new playlist
  1090. using System;
  1091. using System.Collections.Generic;
  1092. using System.Web;
  1093. using System.Web.UI;
  1094. using System.Web.UI.WebControls;
  1095. using System.Data.SqlClient;
  1096. using System.Configuration;
  1097.  
  1098.  
  1099.  
  1100. public partial class NewPlaylist : System.Web.UI.Page
  1101. {
  1102. protected void Page_Load(object sender, EventArgs e)
  1103. {
  1104. usernameLink.Text = (String)Session["username"];
  1105. }
  1106.  
  1107. protected void createPlaylist (object sender, EventArgs e)
  1108. {
  1109. String playlistTitle = playlistName.Value;
  1110. int index = 0;
  1111.  
  1112. SqlConnection konekcija = new SqlConnection();
  1113. konekcija.ConnectionString = "Data Source=localhost;"
  1114. + "Initial Catalog=MusicPlayer; Integrated Security=True";
  1115.  
  1116. SqlCommand playlistID = new SqlCommand();
  1117. SqlCommand insertPlaylist = new SqlCommand();
  1118. SqlCommand insertPossession = new SqlCommand();
  1119. SqlCommand insertAppearance = new SqlCommand();
  1120.  
  1121. playlistID.Connection = konekcija;
  1122. insertPlaylist.Connection = konekcija;
  1123. insertPossession.Connection = konekcija;
  1124. insertAppearance.Connection = konekcija;
  1125.  
  1126. playlistID.CommandText = "Select max(CAST(Playlist_ID AS Int)) maks from Playlist";
  1127.  
  1128. try
  1129. {
  1130. konekcija.Open();
  1131. SqlDataReader citac = playlistID.ExecuteReader();
  1132. if (citac.Read())
  1133. {
  1134. index = Int32.Parse(citac["maks"].ToString());
  1135. index++;
  1136. }
  1137. citac.Close();
  1138.  
  1139. insertPlaylist.CommandText = "insert into Playlist values ('" + index + "','" + playlistTitle + "')";
  1140. insertPlaylist.ExecuteNonQuery();
  1141.  
  1142. insertPossession.CommandText = "insert into Possession values ('" + Session["username"] + "'," + index + ")";
  1143. insertPossession.ExecuteNonQuery();
  1144.  
  1145. for (int i=21; i<25; i++)
  1146. {
  1147. insertAppearance.CommandText = "insert into Appearance values (" + index + ", " + i + ")";
  1148. insertAppearance.ExecuteNonQuery();
  1149. }
  1150. }
  1151. finally
  1152. {
  1153. konekcija.Close();
  1154. }
  1155. }
  1156.  
  1157. protected void logout(object sender, EventArgs e)
  1158. {
  1159. Session["username"] = null;
  1160. Server.Transfer("LogIn.aspx", true);
  1161. }
  1162. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!