Associative Array/Hash/Hashtable using Connector/NET

Question

Hey Guys, working with asp.NET and c#, I'm using MySQL's connector/NET plug-in thingy to connect to a MySQL db (no surprises there!).

And that works fine, can connect, run queries etc etc all fine and dandy, but is it possible to return a Hashtable or similar of the results? Save running a describe on the same table to get the column names and use those values to create the Hash each time.

Thanks,

Psy

Answer 1

The MySQL C/C++ connector which I assume to be wrapped around C# (versus re-implemented in C#) returns a two-demential array containing the results. This is only the column and row data, not the column name. The API also returns a field (column name) value through mysql_fetch_field_direct() -- a separate function call after obtaining the query results. This too is a two-demential array. The connector itself doesn't contain API for merging the two separate results (column names + column/row data) into a hash table.

Instead of making a second query to obtain the column names, all you need to do is call mysql_fetch_field_direct() for each column as you progress through assigning values. This gives you the field name along with the data contained in that column/row. At this point it's up to the developer as to how to arrange that data such as storing it in a hash table, etc.

I use a helper function as a wrapper around query execution that stores each row in a binary tree with the column name being the key and returns a linked list of trees for me to do with what I need.

Answer 2

Hi,

in .net you get only datatables and datasets, a datatable is made out of datarows, those are very very similar to hashtables and in most cases you can use those to achieve the tasks, but if you need hashtable you can use this code

public static Hashtable convertDataRowToHashTable(DataRow dr)
{
    if (dr == null)
    {
        return null;
    }

    Hashtable ret = new Hashtable(dr.Table.Columns.Count);

    for (int iColNr = 0; iColNr < dr.Table.Columns.Count; iColNr++)
    {
        ret[dr.Table.Columns[iColNr].ColumnName] = dr[iColNr];
    }
    return ret;
}

other direction (hast table to datrow) is not that easy, as datarow does not have a public constructor (by design) and you have to call newRow = myDataTable.NewRow(); to get a new instance of a row, and than you can work with row almost as with hashtable

newRow["column1"]="some value";

but if you need a new column in hashtable you will have to add column to datatable and not to data row myTable.Columns.Add("name", "type");

hope this helps