MySQL and C# Getting data from DB and creating an associative array/object

Question

I am new to C# and have been using PHP for Years.

The problem I am facing is that I want to create a method/function that will take an "sql query" as parameter and return whatever data is retrieved so that it can be used using the key/index/column_name.

What I have achieved

I have achieved is that I am able to access the data through index within the same function. but I wont know the no. of columns that will return so I am unable to store it in an array.

Code

        string ConnectionString = ConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString;
        MySqlConnection db;
        db = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
        try
        {
            db.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = db;
            cmd.CommandText = sql;
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(rdr['areaId'].toString());
            }
            db.Close();
            return true;
        }

The problems
1. I do not know the number of Columns.
2. I cannot access the index or Column name.

I want to create an associative array of data with the column name as index and data as its data

EDIT
I am trying to build an application on C# using WPF with MySQL Database.

The number of columns or number of rows? – Oluwafemi Jul 15 '15 at 08:36 — Oluwafemi, Jul 15 '15 at 08:36
none of the above is known – Rohit Hazra Jul 15 '15 at 19:00 — Rohit Hazra, Jul 15 '15 at 19:00

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

My first suggestion would be to use one of the micro ORMs like Dapper.net

This can be fetched via nuget. If you wish to retrieve an unknown array of data from a table, or number of tables, you could fetch it into an ExpandoObject and then enumerate over that. An example of looping over an ExpandoObject can be found here.

    public Foo FetchSomeData(string query)
    {
        using (var cn = Connection)
        {
            var result = cn.Query(query);
           //do something with result, and return it
           return new Foo(result);
        }
    }

I'd have concerns about passing raw sql into the DB without at least checking it or using parameters as it could be a major security risk, especially if you allow users to generate the query and you don't vet it first.

edited May 23 '17 at 10:24

Community

1
1

answered Jul 15 '15 at 09:07

Chris W

1,792
15
32

I will not pass the query without normalizing, checking it and all. As I said I am PHP Dev so I know about those security Issues. Anyways Thanks If I could not do it without micro ORM (looking into it) then I'll use it – Rohit Hazra Jul 15 '15 at 10:08
Hi Rohit, you can bypass the micro orm and just use Data Reader .Read() to pass in the columns and data using .GetName() and .GetString(), building up the object on the fly. Using the micro orm simply removes the need to write such code. – Chris W Jul 15 '15 at 10:19
That is useful. Thanks for your help – Rohit Hazra Jul 16 '15 at 19:52

MySQL and C# Getting data from DB and creating an associative array/object

1 Answers1