public static List <DBLogging> GetLogs() // List with custom class type - DBLogging { List <DBLogging> logs = new List <DBLogging>(); string query = "SELECT * from logs"; MySqlCommand cmd = new MySqlCommand(query, dbConn); //dbConn.Open(); MySqlDataReader reader = cmd.ExecuteReader(); // Create reader and get all recoeds from DB while (reader.Read()) { DateTime date = (DateTime)reader["date"]; // Cast it to integer. (int)reader["id"] string source = reader["source"].ToString(); string message = reader["message"].ToString(); DBLogging log = new DBLogging(date, source, message); logs.Add(log); } //dbConn.Close(); return(logs); }
public static DBLogging Insert(DateTime d, String s, String m) // Date, source, message { string query = string.Format("INSERT INTO logs(date, source, message) VALUES ('{0}', '{1}', '{2}' )", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), s, m); //string query = "INSERT INTO logs(date, source, message) VALUES ('" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + s + "', '" + m + "' )"; MySqlCommand cmd = new MySqlCommand(query, dbConn); //dbConn.Open(); //MessageBox.Show("connection state: " + dbConn.State); //if (dbConn.State == System.Data.ConnectionState.Closed) // If no connection to DB //{ // Console.WriteLine("No DB connection! Connecting"); // DBConnectionOpen(); //} // A. Panin https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html using (var conn = new MySqlConnection("server=" + SettingsJson.dbHost + ";user id=slinger;password=659111;database=tut_db")) { if (conn.State == System.Data.ConnectionState.Closed) { Console.WriteLine("Connection state: " + conn.State + "Opening connection!"); conn.Open(); // If no connection to DB } else { Console.WriteLine("Connection state: " + conn.State + " Connection open, no need to connect"); } string sql = "INSERT INTO logs(date, source, message) VALUES ('" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "', '" + s + "', '" + m + "' )"; MySqlCommand cmd2 = new MySqlCommand(sql, conn); cmd2.ExecuteNonQuery(); //conn.Close(); // No need to close the connection. It closes at the disposal } //cmd.ExecuteNonQuery(); int id = (int)cmd.LastInsertedId; // Id of the record which was inserted DateTime time = DateTime.Now; DBLogging log = new DBLogging(time, "x", "zz"); // The record will be inserted. The id of last inserted record will be assigned to currLog //dbConn.Close(); return(log); }
public static List <DBLogging> GetNewLogs() // List with custom class type - DBLogging { List <DBLogging> logs = new List <DBLogging>(); using (var conn = new MySqlConnection("server=127.0.01;user id=slinger;password=659111;database=tut_db")) { if (conn.State == System.Data.ConnectionState.Closed) { Console.WriteLine("Connection state: " + conn.State + " Opening connection!"); conn.Open(); // If no connection to DB } else { Console.WriteLine("Connection state: " + conn.State + " Connection open, no need to connect"); } string sql = "SELECT * from logs WHERE id = '5'"; MySqlCommand cmd2 = new MySqlCommand(sql, conn); cmd2.ExecuteNonQuery(); MySqlDataReader reader = cmd2.ExecuteReader(); // Create reader and get all recoeds from DB while (reader.Read()) { DateTime date = (DateTime)reader["date"]; // Cast it to integer. (int)reader["id"] string source = reader["source"].ToString(); string message = reader["message"].ToString(); DBLogging l = new DBLogging(date, source, message); logs.Add(l); // get id of the current record // update is_new flag from 1 to 0 int id = (int)reader["id"]; // Type cast } //conn.Close(); // No need to close the connection. It closes at the disposal } return(logs); }