Ad:

C# Discussion Thread
Marked Solved
Views: 3620

Sep 25th, 2009

dynamic sql result into array

Expand Post »

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:

C# Syntax (Toggle Plain Text)
aResult = functionsMysql.getArray(query);
 
 
class functionsMysql
{
 
function getArray (string query)
{
 
...connect and execute query
while(Reader.Read())
{
...fill...
}
 
}
 
}
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

Last edited by DeOiD; Sep 25th, 2009 at 10:42 am.

Similar Threads

Populating a datagrid with dynamic SQL in ASP.NET
Dynamic SQL - Need help in Oracle
vb.net dynamic multi dimention array in VB.NET
How to find the UserID on logging time in PHP
From SQL string to array in PHP

DeOiD

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

30 posts
since Apr 2007

Permalink

Sep 25th, 2009

Re: dynamic sql result into array

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.:

C# Syntax (Toggle Plain Text)
        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
            }
        }
        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
            }
        }

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Sep 25th, 2009

Re: dynamic sql result into array

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

c# Syntax (Toggle Plain Text)
public class Result
    {
        int rowNumber;
        string fieldName;
        string value;
 
        public Result(int rorNumber, string fieldNumber, string value)
        {
            // assign variables
        }
    }
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 ...

c# Syntax (Toggle Plain Text)
public class Result2
    {
        int rowNumber;
        string fieldName;
        string value;
        string whatever;
 
        public Result2(int rorNumber, string fieldNumber, string value, string whatever)
        {
            // assign variables
        }
    }
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:

c# Syntax (Toggle Plain Text)
List<object> resultSet = new List<object>();
.. or
List<SomeBaseClass> resultSet = new List<SomeBaseClass>();
List<object> resultSet = new List<object>();
.. or
List<SomeBaseClass> resultSet = new List<SomeBaseClass>();

and add elements to it:

c# Syntax (Toggle Plain Text)
resultSet.Add(new Result(1, "someName", "someValue"));
resultSet.Add(new Result2(1, "someName", "someValue", "something else"));
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.

Antenka

Reputation Points: 293

Solved Threads: 82

Posting Whiz

Offline

361 posts
since Nov 2008

Permalink

Sep 25th, 2009

Re: dynamic sql result into array

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

DeOiD

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

30 posts
since Apr 2007

Permalink

Sep 25th, 2009

Re: dynamic sql result into array

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Sep 25th, 2009

Re: dynamic sql result into array

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:

C# Syntax (Toggle Plain Text)
        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));
        }
        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!

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Sep 26th, 2009

Re: dynamic sql result into array

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

DeOiD

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

30 posts
since Apr 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: How To Close All Child Forms In MDI Form: C#.net

Next Thread in C# Forum Timeline: Adding Row Into Datagrid View At Runtime

DaniWeb Message

Cancel Changes

User Name
Password