private void Load(string table_name, string sql_query)
{
ColumnNames = new List <string>();
Rows = new List <DatabaseRow>();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); // Grab the conection string for accessing the database.
SqlCommand command = new SqlCommand(sql_query, connection); // Associate the given sql query with the connection.
connection.Open();
SqlDataReader reader = command.ExecuteReader(); // Send the sql query to the database for processing.
for (int n = 0; n < reader.FieldCount; ++n)
{
ColumnNames.Add(reader.GetName(n));
}
while (reader.Read()) // Access each student in turn (continue until there are no students left to read).
{
DatabaseRow dr = new DatabaseRow();
for (int column = 0; column < ColumnCount; ++column)
{
dr.Add(ColumnNames[column], reader[column].ToString());
}
Rows.Add(dr);
}
connection.Close();
}
//Function to query DB and return an array of rows
public override DatabaseRow[] ExecuteQuery(string query, params QueryParameter[] parameters)
{
try {
using (MySqlConnection connection = new MySqlConnection(connectionString)) {
using (MySqlCommand command = new MySqlCommand(query, connection)) {
foreach (QueryParameter parameter in parameters)
{
command.Parameters.AddWithValue(parameter.name, parameter.obj);
}
connection.Open();
using (MySqlDataReader reader = command.ExecuteReader()) {
int fieldCount = reader.FieldCount;
List <DatabaseRow> rows = new List <DatabaseRow> ();
while (reader.Read())
{
//For each row create a DatabaseRow
DatabaseRow row = new DatabaseRow();
//And add each field to it
for (int i = 0; i < fieldCount; i++)
{
row.Add(reader.GetName(i), reader.GetValue(i));
}
//Add it to the rows
rows.Add(row);
}
return(rows.ToArray());
}
}
}
}
catch (MySqlException e) {
throw new DatabaseException(e.Message, e);
}
}
/// <summary>
/// Executes a query on the database returning an array of rows.
/// </summary>
/// <returns>The rows of the database selected.</returns>
/// <param name="query">The query.</param>
/// <param name="paramaters">The paramaters to be added to this query.</param>
public override DatabaseRow[] ExecuteQuery(string query, params QueryParameter[] parameters)
{
try
{
using (MySqlConnection connection = new MySqlConnection (connectionString))
{
using (MySqlCommand command = new MySqlCommand (query, connection))
{
foreach (QueryParameter parameter in parameters)
command.Parameters.AddWithValue(parameter.name, parameter.obj);
connection.Open();
using (MySqlDataReader reader = command.ExecuteReader())
{
int fieldCount = reader.FieldCount;
List<DatabaseRow> rows = new List<DatabaseRow> ();
while (reader.Read())
{
//For each row create a DatabaseRow
DatabaseRow row = new DatabaseRow();
//And add each field to it
for (int i = 0; i < fieldCount; i++)
{
row.Add(
reader.GetName(i),
reader.GetValue(i)
);
}
//Add it to the rows
rows.Add(row);
}
return rows.ToArray();
}
}
}
}
catch(MySqlException e)
{
throw new DatabaseException(e.Message, e);
}
}