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); }