C# MYSQL SELECT Method dynamic

Question

Below is a method for selecting from a mysql table. However I do not find it very dynamic. Coming from PHP all I do is send a query to a function and recieve its data in a variable. This is no longer the case it seems.

My queries vary a lot. Sometimes they only get one row and one column. Next time it may collect 1000 rows and all columns. Using the list the way below is not very dynamic and I do not wish to build a select method for each possible scenario.

I wish to run my query, return the data and let me do what I want with it.

public List<string>[] Select(string query)
        {

            //Create a list to store the result
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {

                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
            while (dataReader.Read())
            {
                list[0].Add(dataReader["id"] + "");
                list[1].Add(dataReader["name"] + "");
                list[2].Add(dataReader["age"] + "");
            }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;

            }
            else
            {

                return list;

            }
        }

Thanks!

Answer 1

Take a look at ExpandoObject. Or for older .NET, use a Dictionary or HashMap.

You can return a List from your Select() method. Inside the method you can iterate through the DataReader's fields to map them to one of the object types I mentioned.

How to loop through columns of a data reader -

how can i loop through all of the columns of the OracleDataReader

How to add properties to ExpandoObject by name at runtime

Adding unknown (at design time) properties to an ExpandoObject

 public List<dynamic> Select(string sql) {

        var list = new List<dynamic>();

        // ... your code to connect to database, execute sql as datareader ...
        // I recommend you Google the using statement, and use that to dispose
        // your connection and reader

        while (dataReader.Read())
        {
           var obj = new ExpandoObject();
           var d = obj as IDictionary<String, object>;
           for( int index = 0; index < reader.FieldCount; index ++ )
              d[ reader.GetName( index ) ] = reader.GetString( index );

           list.Add(obj);
        }

        return list;
  }

While not really the scope of this question, you should employ using statements instead of explicitly closing connections or disposing readers in finally blocks as people often do.

Should I be using SqlDataReader inside a "using" statement?

Answer 2

pulled this from this my sql tutorial, think what you want to do is return a data set or data table.

this is just an example and will need to be altered to work in your method, should give you what you need though

    string cs = @"server=localhost;userid=user12;
        password=34klq*;database=mydb";

    MySqlConnection conn = null;

    try 
    {

        conn = new MySqlConnection(cs);
        conn.Open();

        string stm = "SELECT * FROM Authors";
        MySqlDataAdapter da = new MySqlDataAdapter(stm, conn);

        DataSet ds = new DataSet();

        da.Fill(ds, "Authors");
        DataTable dt = ds.Tables["Authors"];

        dt.WriteXml("authors.xml");

        foreach (DataRow row in dt.Rows) 
        {            
            foreach (DataColumn col in dt.Columns) 
            {
              Console.WriteLine(row[col]);
            }

            Console.WriteLine("".PadLeft(20, '='));
        }

    } catch (MySqlException ex) 
    {
        Console.WriteLine("Error: {0}",  ex.ToString());

    } finally 
    {          
        if (conn != null) 
        {
            conn.Close();
        }

    }

Answer 3

Have you thought about using a micro ORM like NPoco?

I work with many different types of database including MySQL and I like to use this in conjunction with Entity Framework to build classes that represent my tables and views. I don't use Entity Framework at runtime with MySQL - historically it was really slow. I then have a data access layer that holds all the methods I use to access the data.

Typical methods with inline SQL using NPoco might look like this:

    public user GetUser(int userId)
    {
        using (var db = new Database("LocalMySqlServer"))
        {
            var sql = NPoco.Sql.Builder.Append("SELECT * FROM users where ID=@0", userId);
            var myList = db.FirstOrDefault<user>(sql);
            return myList;
        }
    }        

    public List<user> GetUsers()
    {
        using (var db = new Database("LocalMySqlServer"))
        {
            return db.Fetch<user>("SELECT * FROM users");
        }
    }

In these examples (not tested), you would have a connection string called LocalMySqlServer saved in web.config or app.config and in MySQL you would have a table called users. You would have a class called user that matches the users table in the database - either imported using Entity Framework or created manually.

NPoco is capable of much more and is worth checking out. If you don't want to use NPoco in production, being open source you run through the source code to see how it works.

Lastly, in your example you were passing the SQL statement you wanted to run. NPoco uses parameterization queries to reduce the risk of injection attacks.

Answer 4

i will avoid using data reader, instead use DataTable and you can get the columns like this

foreach (DataColumn column in oTable1.Columns) {
 object value1 = oRow[column.ToString()].ToString();
}