using System; using System.Data; using MySql.Data.MySqlClient; using Woodpecker.Core; namespace Woodpecker.Storage { /// /// A reuseable instance of a database connection, for accessing/writing data into the database. /// public class Database { #region Fields /// /// The MySqlConnection object of this connection. This object is private. /// private MySqlConnection Connection; /// /// The MySqlDataAdapter object of this connection, required for inserting data etc. This object is private. /// private MySqlDataAdapter dataAdapter = new MySqlDataAdapter(); /// /// The MySqlCommand object of this connection, used for executing commands at the database. This object is private. /// private MySqlCommand Command = new MySqlCommand(); /// /// A boolean indicating if the Database object should be closed after the next query. /// public bool closeAfterNextQuery; /// /// The connection string for connections. This string is static. /// public static string connectionString; private bool _Ready = false; /// /// Gets the current readystate. (connected yes/no) /// public bool Ready { get { return this._Ready; } } #endregion #region Constructors /// /// Initializes the Database object, with the options to open the database upon constructing, and/or to make the Database object tidy up (close connection and dispose resources) after the first query. /// /// Indicates if the database connection should be opened already. /// Indicates if the Database object should close the connection and dispose resources after the first query. public Database(bool openNow, bool closeAfterFirstQuery) { if (openNow) this.Open(); this.closeAfterNextQuery = closeAfterFirstQuery; } #endregion #region Methods #region Opening and closing database /// /// Attempts to open a connection to the database and prepares for use. /// public void Open() { // Attempt to connect to the database and handle exceptions try { this.Connection = new MySqlConnection(connectionString); this.Connection.Open(); this.Command.Connection = this.Connection; this.dataAdapter.SelectCommand = this.Command; this._Ready = true; } catch (MySqlException ex) // Error while connecting { Logging.Log(ex.Message, Logging.logType.commonError); } } /// /// Closes the connection to the database, if connected. All resources are disposed. /// public void Close() { if (this._Ready) { this.Connection.Close(); this.Connection = null; this.dataAdapter = null; this.Command = null; this.closeAfterNextQuery = false; this._Ready = false; } } #endregion #region Data access /// /// Returns a DataSet object containing requested data of various tables. /// /// The query to run at the database. public DataSet getDataSet(string Query) { DataSet dReturn = new DataSet(); try { this.Command.CommandText = Query; this.dataAdapter.Fill(dReturn); } catch (Exception ex) { Logging.Log(ex.Message, Logging.logType.commonError); } if (this.closeAfterNextQuery) this.Close(); return dReturn; } /// /// Returns a DataTable object containing requested data of a single table. /// /// The query to run at the database. public DataTable getTable(string Query) { //Logging.Log("Retrieving datatable; " + Query); DataTable dReturn = new DataTable(); try { this.Command.CommandText = Query; this.dataAdapter.Fill(dReturn); } catch (Exception ex) { Logging.Log(ex.Message, Logging.logType.commonError); } if (this.closeAfterNextQuery) this.Close(); return dReturn; } /// /// Returns a DataRow object containing requested data of a single row of a single table. /// /// The query to run at the database. public DataRow getRow(string Query) { DataRow dReturn = null; try { DataSet tmpSet = new DataSet(); this.Command.CommandText = Query; this.dataAdapter.Fill(tmpSet); dReturn = tmpSet.Tables[0].Rows[0]; } catch { } if (this.closeAfterNextQuery) this.Close(); return dReturn; } /// /// Retrieves a single field value from the database and returns it as a string. /// /// The query to run at the database. public string getString(string Query) { string s = ""; try { this.Command.CommandText = Query; s = this.Command.ExecuteScalar().ToString(); } catch { } if (this.closeAfterNextQuery) this.Close(); return s; } /// /// Retrieves a single field value from the database and returns it as an integer. /// /// The query to run at the database. public int getInteger(string Query) { int i = 0; try { this.Command.CommandText = Query; i = int.Parse(this.Command.ExecuteScalar().ToString()); } catch { } if (this.closeAfterNextQuery) this.Close(); return i; } /// /// Returns a boolean indicating if there were results for a certain query. /// /// The query to run at the database. public bool findsResult(string Query) { bool Found = false; try { this.Command.CommandText = Query; MySqlDataReader dReader = this.Command.ExecuteReader(); Found = dReader.HasRows; dReader.Close(); } catch (Exception ex) { Logging.Log(ex.Message, Logging.logType.commonError); } if (this.closeAfterNextQuery) this.Close(); return Found; } #endregion #region Other /// /// Adds a parameter with a value to the current parameter collection, for use in queries. A '@' symbol is placed infront of the parameter key automatically. /// /// The parameter key to add. '@' symbol is added infront. /// The value of the parameter, can be any type. public void addParameterWithValue(string Parameter, object Value) { this.Command.Parameters.AddWithValue("@" + Parameter, Value); } public void addRawParameter(MySqlParameter Parameter) { this.Command.Parameters.Add(Parameter); } /// /// Clears all parameters from the parameter collection. /// public void clearParameters() { this.Command.Parameters.Clear(); } /// /// Attempts to open a connection to the database to execute a query. /// /// The query string to execute. public void runQuery(string Query) { try { this.Command.CommandText = Query; this.Command.ExecuteNonQuery(); } catch (Exception ex) { Logging.Log(ex.Message, Logging.logType.commonError); } if (this.closeAfterNextQuery) this.Close(); } #endregion #endregion } }