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