I am since recently struggling with a new problem. I've got a database table that contains multiple fields (columns) with a few rows containing (money - decimal) values that are related to the fieldnames.
For example:
What I want to do is getting these values from the database, add them together and displaying the total amount in a label.
Up till now I used the OleDbDataReader for all my outputting/storing value needs. Though I have absolutely no clue how I can add the read values since the reader usually expects an pre defined field name to read.
In my project however, a user can add a custom new field name (so a pre defined field name in the reader isn't possible because you don't know which custom field names will be added by the user in the DB. These custom added fields names and their values need to be added in the total amount as well though..
Does anyone have a clue how I can fix this?
I've tried multiple things like storing it in an array, defining decimal variables and using something like x = x + (decimal)reader[0] but this all didn't work so I think I am way off.
The code (and query) I am using for the reading part is as follows:
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select * from money where [Month]='January'";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//Something I tried
//x[0] = (decimal)reader[0];
//x[1] = (decimal)reader[1];
//And so on...
//Another thing I tried
//list.Add(reader.GetInt32(0));
}
//list.ToArray();
//int sum = list.Sum();
// y = x[0] + x[1] + ...;
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}
You should just be able to declare a variable, then add up all the columns. If you don't know the number of columns in the reader, you can get that using reader.FieldCount.
You do not need to know the column name to get data from the reader. You can access it by column index as you started to do, e.g. reader[0], or using the helper methods such as GetDecimal(0).
try
{
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "select * from money where [Month]='January'";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
// start the total at 0
int total = 0.0m;
while (reader.Read())
{
// loop through all the fields in the reader
for(int f = 0; f < reader.FieldCount; f++) {
// read as a decimal and add to the total
total += reader.GetDecimal(f);
}
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}