dynamic sql result into array

Please support our C# advertiser: Intel Parallel Studio Home
Thread Solved

Join Date: Apr 2007
Posts: 30
Reputation: DeOiD is an unknown quantity at this point 
Solved Threads: 0
DeOiD DeOiD is offline Offline
Light Poster

dynamic sql result into array

 
0
  #1
Sep 25th, 2009
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:

  1. aResult = functionsMysql.getArray(query);
  2.  
  3.  
  4. class functionsMysql
  5. {
  6.  
  7. function getArray (string query)
  8. {
  9.  
  10. ...connect and execute query
  11. while(Reader.Read())
  12. {
  13. ...fill...
  14. }
  15.  
  16. }
  17.  
  18. }

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.
Reply With Quote Quick reply to this message  
Join Date: Jul 2009
Posts: 908
Reputation: DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough 
Solved Threads: 145
DdoubleD DdoubleD is offline Offline
Posting Shark

Re: dynamic sql result into array

 
1
  #2
Sep 25th, 2009
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.:

  1. public void BuildDynamicTableArrayExample()
  2. {
  3. DataTable dt = new DataTable(); // table to hold each record
  4. dt.Columns.Add(new DataColumn("FieldName1", typeof(string))); // Create a string column dynamically
  5. dt.Columns.Add(new DataColumn("FieldName2", typeof(int))); // create an int column dyn
  6. dt.Columns.Add(new DataColumn("FieldName3", typeof(double))); // create a double column dyn
  7. for (int r = 0; r < 10; r++) // generate 10 rows of data
  8. {
  9. DataRow dr = dt.NewRow(); // create a row object of table's type defined above
  10.  
  11. dr["FieldName1"] = "data " + r; // add some data...
  12. dr["FieldName2"] = r;
  13. dr["FieldName3"] = (double)r;
  14.  
  15. dt.Rows.Add(dr); // add the row to the table
  16. }
  17. }
Reply With Quote Quick reply to this message  
Join Date: Nov 2008
Posts: 249
Reputation: Antenka has a spectacular aura about Antenka has a spectacular aura about Antenka has a spectacular aura about 
Solved Threads: 65
Antenka's Avatar
Antenka Antenka is offline Offline
Posting Whiz in Training

Re: dynamic sql result into array

 
2
  #3
Sep 25th, 2009
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:
  1. public class Result
  2. {
  3. int rowNumber;
  4. string fieldName;
  5. string value;
  6.  
  7. public Result(int rorNumber, string fieldNumber, string value)
  8. {
  9. // assign variables
  10. }
  11. }

if you have a few types of results, you can create more classes/structures ...
  1. public class Result2
  2. {
  3. int rowNumber;
  4. string fieldName;
  5. string value;
  6. string whatever;
  7.  
  8. public Result2(int rorNumber, string fieldNumber, string value, string whatever)
  9. {
  10. // assign variables
  11. }
  12. }

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:
  1. List<object> resultSet = new List<object>();
  2. .. or
  3. List<SomeBaseClass> resultSet = new List<SomeBaseClass>();

and add elements to it:
  1. resultSet.Add(new Result(1, "someName", "someValue"));
  2. 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.
So what if you can see the darkest side of me?
No one would ever change this animal I have become
Help me believe it's not the real me
Somebody help me tame this animal
Reply With Quote Quick reply to this message  
Join Date: Apr 2007
Posts: 30
Reputation: DeOiD is an unknown quantity at this point 
Solved Threads: 0
DeOiD DeOiD is offline Offline
Light Poster

Re: dynamic sql result into array

 
0
  #4
Sep 25th, 2009
thank's for the replies, i will test these solutions tonight and post my results...
Reply With Quote Quick reply to this message  
Join Date: Feb 2009
Posts: 3,215
Reputation: sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of sknake has much to be proud of 
Solved Threads: 573
Sponsor
sknake's Avatar
sknake sknake is offline Offline
.NET Enthusiast

Re: dynamic sql result into array

 
0
  #5
Sep 25th, 2009
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.
Scott Knake
Custom Software Development
Apex Software, Inc.
Reply With Quote Quick reply to this message  
Join Date: Jul 2009
Posts: 908
Reputation: DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough DdoubleD is a jewel in the rough 
Solved Threads: 145
DdoubleD DdoubleD is offline Offline
Posting Shark

Re: dynamic sql result into array

 
0
  #6
Sep 25th, 2009
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:

  1. public class FieldValues
  2. {
  3. public int Row { get; set; }
  4. public string FldName { get; set; }
  5. public object FldRowValue { get; set; } // don't know its type yet
  6.  
  7. public FieldValues(int row, string fldName, object value)
  8. {
  9. Row = row;
  10. FldName = fldName;
  11. FldRowValue = value;
  12. }
  13. }
  14. List<FieldValues> fieldValues = new List<FieldValues>();
  15.  
  16. public void AddAnItem(int row, string fldName, object value)
  17. {
  18. fieldValues.Add(new FieldValues(row, fldName, value));
  19. }

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

Cheers!
Reply With Quote Quick reply to this message  
Join Date: Apr 2007
Posts: 30
Reputation: DeOiD is an unknown quantity at this point 
Solved Threads: 0
DeOiD DeOiD is offline Offline
Light Poster

Re: dynamic sql result into array

 
0
  #7
Sep 26th, 2009
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
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC