public ActionResult Details(int id) { // Opening connection NpgsqlConnection connection = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password="******";Database=project;"); connection.Open(); // Getting required article Article current = new Article(); NpgsqlCommand command = new NpgsqlCommand("SELECT articles.id, articles.title, publications.name, articles.description, articles.mdurl, articles.pdf, string_agg(DISTINCT authors.name, ';'), string_agg(DISTINCT thesaurus_terms.text, ';') " + "FROM articles " + "INNER JOIN authors_of_article ON articles.id = authors_of_article.article_id " + "INNER JOIN authors ON authors_of_article.author_id = authors.id " + "INNER JOIN publications ON articles.publication_id = publications.id " + "INNER JOIN thesaurus_terms_in_article ON articles.id = thesaurus_terms_in_article.article_id " + "INNER JOIN thesaurus_terms ON thesaurus_terms_in_article.thesaurus_term_id = thesaurus_terms.id " + "WHERE articles.id = " + id + " " + "GROUP BY articles.id, articles.title, publications.name, articles.description, articles.mdurl, articles.pdf", connection); NpgsqlDataReader reader = command.ExecuteReader(); if(reader.Read()) { current.ID = (int)reader[0]; current.Title = (string)reader[1]; current.Publication = (string)reader[2]; current.Description = (string)reader[3]; current.MDURL = (string)reader[4]; current.PDF = (string)reader[5]; current.Authors = (string)reader[6]; current.Terms = (string)reader[7]; } ViewBag.Article = current; return View(); }
public ActionResult Index(string searchString, string searchMode) { // JS protection searchString = HttpUtility.HtmlEncode(searchString); // Opening connection NpgsqlConnection connection = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password="******";Database=project;"); connection.Open(); // Find required articles List<Article> articles = new List<Article>(); if (searchString != string.Empty & searchString != null) { // Select search mode from drop down list string mode = string.Empty; switch(searchMode) { case "All": mode = "WHERE articles.title LIKE '%" + searchString + "%' " + "OR authors.name LIKE '%" + searchString + "%' " + "OR publications.name LIKE '%" + searchString + "%' " + "GROUP BY articles.id, articles.title, publications.name"; break; case "Author": mode = "GROUP BY articles.id, articles.title, publications.name " + "HAVING string_agg(authors.name,';') LIKE '%" + searchString + "%'"; break; case "Publication": mode = "WHERE publications.name LIKE '%" + searchString + "%' " + "GROUP BY articles.id, articles.title, publications.name"; break; case "Article": mode = "WHERE articles.title LIKE '%" + searchString + "%' " + "GROUP BY articles.id, articles.title, publications.name"; break; case "Term": mode = "WHERE thesaurus_terms.text LIKE '%" + searchString + "%' " + "GROUP BY articles.id, articles.title, publications.name"; break; } // Execute querry NpgsqlCommand command = new NpgsqlCommand("SELECT articles.id, articles.title, publications.name, string_agg(DISTINCT authors.name,';'), string_agg(DISTINCT thesaurus_terms.text, ';') " + "FROM articles " + "INNER JOIN authors_of_article ON articles.id = authors_of_article.article_id " + "INNER JOIN authors ON authors_of_article.author_id = authors.id " + "INNER JOIN publications ON articles.publication_id = publications.id " + "INNER JOIN thesaurus_terms_in_article ON articles.id = thesaurus_terms_in_article.article_id " + "INNER JOIN thesaurus_terms ON thesaurus_terms_in_article.thesaurus_term_id = thesaurus_terms.id " + mode, connection); NpgsqlDataReader reader = command.ExecuteReader(); // Read the data while(reader.Read()) { Article current = new Article(); current.ID = (int)reader[0]; current.Title = (string)reader[1]; current.Publication = (string)reader[2]; current.Authors = (string)reader[3]; current.Terms = (string)reader[4]; articles.Add(current); } } return View(articles); }