/// <summary>
/// MySQL Select
/// </summary>
/// <param name="tableName">The name of table</param>
/// <param name="dbqCollection">The array of DBQuery element (columnname, value)</param>
public static DataTable DBSelect(string tableName, params DBQueryElement[] dbqCollection)
{
DataTable dt = new DataTable();
DBOpenConnection();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "SELECT * FROM " + tableName + (dbqCollection.Length > 0 ? " WHERE " : "");
for (int x = 0; x <= dbqCollection.Length - 1; x++)
{
DBQueryElement i = dbqCollection[x];
command.CommandText += "" + i.queryName + " = @param_" + i.queryName + (x == dbqCollection.Length - 1 ? "" : " AND ");
command.Parameters.AddWithValue("@param_" + i.queryName, i.queryValue);
}
MySqlDataAdapter da = new MySqlDataAdapter
{
SelectCommand = command
};
da.Fill(dt);
}
DBCloseConnection();
return(dt);
}
/// <summary>
/// MySQL Select using LIKE, commonly used in search functions
/// </summary>
/// <param name="tableName">The name of table</param>
/// <param name="dBQuery">The query using DBQuery element (columnname, value)</param>
public static DataTable DBSelectLike(string tableName, DBQueryElement dBQuery)
{
DataTable dt = new DataTable();
DBOpenConnection();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "SELECT * FROM " + tableName + " WHERE ";
DBQueryElement i = dBQuery;
command.CommandText += "" + i.queryName + " LIKE @param_" + i.queryName;
command.Parameters.AddWithValue("@param_" + i.queryName, i.queryValue);
MySqlDataAdapter da = new MySqlDataAdapter
{
SelectCommand = command
};
da.Fill(dt);
}
DBCloseConnection();
return(dt);
}
/// <summary>
/// MySQL Update
/// </summary>
/// <param name="tableName">The name of table</param>
/// <param name="idName">The column name of the Primary Key</param>
/// <param name="id">The ID of the record to be updated</param>
/// <param name="dbqCollection">The array of DBQuery element (columnname, value to update)</param>
public static void DBUpdate(string tableName, string idName, int id, params DBQueryElement[] dbqCollection)
{
DBOpenConnection();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "UPDATE " + tableName + " SET ";
for (int x = 0; x <= dbqCollection.Length - 1; x++)
{
DBQueryElement i = dbqCollection[x];
command.CommandText += "" + i.queryName + " = @param_" + i.queryName + "" + (x == dbqCollection.Length - 1 ? "" : ", ");
MySqlParameter p = new MySqlParameter("@param_" + i.queryName, i.queryValue);
command.Parameters.Add(p);
}
command.CommandText += " WHERE " + idName + " = " + id.ToString();
command.ExecuteNonQuery();
}
DBCloseConnection();
}
/// <summary>
/// MySQL Replace
/// </summary>
/// <param name="tableName">The name of table</param>
/// <param name="dbqCollection">The array of DBQuery element (columnname, value to be replaced)</param>
public static int DBReplace(string tableName, params DBQueryElement[] dbqCollection)
{
DBOpenConnection();
long id = -1;
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandText = "REPLACE INTO " + tableName + " (";
for (int x = 0; x <= dbqCollection.Length - 1; x++)
{
DBQueryElement i = dbqCollection[x];
command.CommandText += "" + i.queryName + "" + (x == dbqCollection.Length - 1 ? "" : ", ");
}
command.CommandText += ") VALUES (";
for (int x = 0; x <= dbqCollection.Length - 1; x++)
{
DBQueryElement i = dbqCollection[x];
command.CommandText += "@param_" + i.queryName + "" + (x == dbqCollection.Length - 1 ? "" : ", ");
MySqlParameter p = new MySqlParameter("@param_" + i.queryName, i.queryValue);
command.Parameters.Add(p);
}
command.CommandText += ")";
command.ExecuteNonQuery();
id = command.LastInsertedId;
}
DBCloseConnection();
return((int)id);
}
