public IEnumerable <Article> ListArticles(string ArticleSearchKey) //Article is a class from models.article.cs
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//Open the conn or connection between the web server and database
Conn.Open();
//Establish a new command(query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL Query
string query = "select * from articles where articletitle like @searchkey or articlebody like @searchkey";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@SearchKey", "%" + ArticleSearchKey + "%"); // partial query: "%"+ C# parameter +"%"
cmd.Prepare();
//accesses info from articles table
//Gather REsult Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader(); //cmd.executereader is a result set
//Create an empty list of Articles List
List <Article> Articles = new List <Article>();
//Loop through each row the result set
while (ResultSet.Read())
{
//Access Column informaiton by the DB column name as an index
//create new article
// Object of type article, variable name = class of the article
Article NewArticle = new Article();
//new articles has properties associated to it from database
string ArticleTitle = ResultSet["articletitle"].ToString();
//Access Column info by the DB column name as an index
//DateTime ArticleDate = ResultSet["articledate"];
string ArticleBody = ResultSet["articlebody"].ToString();
int ArticleID = Convert.ToInt32(ResultSet["articleid"]);
NewArticle.ArticleTitle = ArticleTitle;
NewArticle.ArticleDate = ArticleDate;
NewArticle.ArticleBody = ArticleBody;
NewArticle.ArticleId = ArticleID;
//article objects added to NewArticle
Articles.Add(NewArticle);
}
//Close the connetion between MySQL Database and the WebServer
Conn.Close();
//Return final list of article titles
return(Articles); //variable name
}
public IEnumerable <Author> ListAuthors(string SearchKey = null)
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//Open the connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from Authors where lower(authorfname) like lower(@key) or lower(authorlname) like lower(@key) or lower(concat(authorfname, ' ', authorlname)) like lower(@key)";
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Authors
List <Author> Authors = new List <Author> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int AuthorId = Convert.ToInt32(ResultSet["authorid"]);
string AuthorFname = ResultSet["authorfname"].ToString();
string AuthorLname = ResultSet["authorlname"].ToString();
string AuthorBio = ResultSet["authorbio"].ToString();;
// This technique will work,
//DateTime AuthorJoinDate = (DateTime)ResultSet["authorjoindate"];
// This technique is safer!
DateTime AuthorJoinDate;
DateTime.TryParse(ResultSet["authorjoindate"].ToString(), out AuthorJoinDate);
Author NewAuthor = new Author();
NewAuthor.AuthorId = AuthorId;
NewAuthor.AuthorFname = AuthorFname;
NewAuthor.AuthorLname = AuthorLname;
NewAuthor.AuthorBio = AuthorBio;
//Add the Author Name to the List
Authors.Add(NewAuthor);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of author names
return(Authors);
}
public IEnumerable <Author> ListAuthors()
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//Open the connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from Authors";
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Authors
List <Author> Authors = new List <Author> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int AuthorId = (int)ResultSet["authorid"];
string AuthorFname = ResultSet["authorfname"].ToString();
string AuthorLname = ResultSet["authorlname"].ToString();
string AuthorBio = ResultSet["authorbio"].ToString();
Author NewAuthor = new Author();
NewAuthor.AuthorId = AuthorId;
NewAuthor.AuthorFname = AuthorFname;
NewAuthor.AuthorLname = AuthorLname;
NewAuthor.AuthorBio = AuthorBio;
//Add the Author Name to the List
Authors.Add(NewAuthor);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of author names
return(Authors);
}
public IEnumerable <string> ListTags()
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//Open the connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from tags";
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Tag names
List <String> Tags = new List <string> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
string tagname = ResultSet["tagname"] as string;
//Add the Author Name to the List
Tags.Add(tagname);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of tags
return(Tags);
}
public IEnumerable <Author> ListAuthors(string SearchKey = null) //2. method = ListAuthors as a string
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//OPen the conn or connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY = parameterized query
cmd.CommandText = "Select * from Authors where lower(authorfname) like lower(@key) or lower(authorlname) like lower(@key) or lower(concat(authorfname, ' ', authorlname)) like lower(@key)";
//lower: means lowercase
//commandtext is public This is s command object. represents a string
//security: anything that is included - the @key is the search key, don't have to
//worry about tampering. It is a find and replace. Any strange characters
//get stripped out with following:
cmd.Parameters.AddWithValue("@key", "%" + SearchKey + "%");
cmd.Prepare();
//preparing the SQL query with cmd.Prepare();
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader(); //cmd.executereader is a result set
//Create an empty list of Authors
//what it does: finds the author names and adds them to a new list
List <Author> Authors = new List <Author> {
};
//Loop Through Each Row the Result Set
//read method will proceed through list via rows. result set will return a dual data type
// 1 loop will result in one result set. ex. if 300 authors - will loop 300 times
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
int AuthorId = Convert.ToInt32(ResultSet["authorid"]);
string AuthorFname = ResultSet["Authorfname"].ToString();
string AuthorLname = ResultSet["authorlname"].ToString();
string AuthorBio = ResultSet["authorbio"].ToString();
//Access the Author Join Date - safer way by parsing the result into a string
DateTime AuthorJoinDate;
DateTime.TryParse(ResultSet["authorjoindate"].ToString(), out AuthorJoinDate);
//Create a new author object
Author NewAuthor = new Author();
NewAuthor.AuthorId = AuthorId; //AuthorId on left refers to author.cs class
NewAuthor.AuthorFname = AuthorFname;
NewAuthor.AuthorLname = AuthorLname;
NewAuthor.AuthorBio = AuthorBio;
//Add the Author to the List of Authors
Authors.Add(NewAuthor);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of authors
return(Authors);
}
