dynamic sql result into array

[DeOiD]

Hi

I want to fetch results from a given table (in this case using MySql) and put them in a array or arrayList or List<> (whatever works better). The thing is that this must be made dynamically. The idea is something like this:

aResult = functionsMysql.getArray(query);


class functionsMysql
{

function getArray (string query)
{

...connect and execute query
while(Reader.Read())
{
...fill...
}

}

}

I already have the connection working and the query is executed but i don´t have any idea how to put the results into the array, because the result doesn't a fixed number of rows or fields.

The array(or whatever...) has to be something like:
aResult[RowNumber, FieldName, Row.Field.Value]

is this possible?

Needless to say I'm a C# beginner and English isn't my language

[DdoubleD]

Here is an example of how you could build a dynamic table to hold your data. The field names and data types are up to you to decide, etc.:

public void BuildDynamicTableArrayExample()
        {
            DataTable dt = new DataTable(); // table to hold each record
            dt.Columns.Add(new DataColumn("FieldName1", typeof(string))); // Create a string column dynamically
            dt.Columns.Add(new DataColumn("FieldName2", typeof(int))); // create an int column dyn
            dt.Columns.Add(new DataColumn("FieldName3", typeof(double))); // create a double column dyn
            for (int r = 0; r < 10; r++) // generate 10 rows of data
            {
                DataRow dr = dt.NewRow();   // create a row object of table's type defined above

                dr["FieldName1"] = "data " + r; // add some data...
                dr["FieldName2"] = r;
                dr["FieldName3"] = (double)r;

                dt.Rows.Add(dr); // add the row to the table
            }
        }

[Antenka]

Hello, DeOiD.
Here's one more way to go ..
If choose between Array, ArrayList or List - I would suggest you use List.
For keeping such values you can define a class or structure. For example:

public class Result
    {
        int rowNumber;
        string fieldName;
        string value;

        public Result(int rorNumber, string fieldNumber, string value)
        {
            // assign variables
        }
    }

if you have a few types of results, you can create more classes/structures ...

public class Result2
    {
        int rowNumber;
        string fieldName;
        string value;
        string whatever;

        public Result2(int rorNumber, string fieldNumber, string value, string whatever)
        {
            // assign variables
        }
    }

We know, that List can contain the elements of same type. So we should find the connection between 2 created classes:
1. Both classes (also as all reference types) are inherit from System.Object.
2. You can define the parent class for them by yourself.

What it will give to you? After that you can create a List:

List<object> resultSet = new List<object>();
.. or
List<SomeBaseClass> resultSet = new List<SomeBaseClass>();

and add elements to it:

resultSet.Add(new Result(1, "someName", "someValue"));
resultSet.Add(new Result2(1, "someName", "someValue", "something else"));

Depending on what way you'll choose - the new element of resultSet would be automatically converted to either Object or SomeBaseClass .

And then to determine, which type of result you currently working with - use is or as keywords.

[DeOiD]

thank's for the replies, i will test these solutions tonight and post my results...

[sknake]

You can also use a HashTable to access the columns by names. I think that is along the lines of what you may want if the other (better!) solutions don't pan out :)

DdoubleD/Antenka showed great ways to go about it.

[DdoubleD]

That's how I originally started thinking it as an example, then I scrapped it figuring it wasn't practical for using the list, but don't really know exactly how it will be used anyway:

public class FieldValues
        {
            public int Row { get; set; }
            public string FldName { get; set; }
            public object FldRowValue { get; set; } // don't know its type yet

            public FieldValues(int row, string fldName, object value)
            {
                Row = row;
                FldName = fldName;
                FldRowValue = value;
            }
        }
        List<FieldValues> fieldValues = new List<FieldValues>();

        public void AddAnItem(int row, string fldName, object value)
        {
            fieldValues.Add(new FieldValues(row, fldName, value));
        }

DeOiD, be sure to check back periodically for some other's opinions too.

Cheers!

[DeOiD]

Hi

I've tested Antenka's solution and it works like a charm. Thank you very much.

DdoubleD solution apparently works too, but i haven't had the time to test it yet. It seems to me that, for what i want, it only needs another loop to dynamically set the fieldName and its value...

Thank so much for your replies, now i have at least 2 working solutions for my problem ;)