0

As we are good in developing Stored procedures having joins and using multiple tables and good hand at ADO.Net, we have not chosen EF. Now stuck in a case where we are looking a need of ORM but still trying to find simple solution:

We have one Class Message and its child class with list of object as Class URL. Now, we are looking to generate JSON like this:

{
messegid:1,
messege:’Hello’,
messegeurl:
    [
        {url:’http://example.com/1.jpg’},
        {url:’http://example.com/2.jpg’}
    ]
}

We understand it would be easy to get it done using EF but what is we don't use it. We are using below method to generate objects:

public List<T> CreateObject<T>(List<MySqlParameter> parameters, string SPName)
        {
            List<T> t = new List<T>();
            try
            {
                using (MySqlConnection con = new MySqlConnection(connString))
                {
                    using (MySqlCommand cmd = new MySqlCommand(SPName, con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        foreach (MySqlParameter param in parameters)
                        {
                            cmd.Parameters.Add(param);
                        }

                        con.Open();
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            T obj = default(T);
                            while (reader.Read())
                            {
                                obj = Activator.CreateInstance<T>();
                                foreach (PropertyInfo prop in obj.GetType().GetProperties())
                                {
                                    if (prop.PropertyType.Equals(System.Type.GetType("System.String")) || prop.PropertyType.Equals(System.Type.GetType("System.Int32")) || prop.PropertyType.Equals(System.Type.GetType("System.Int64")) || prop.PropertyType.Equals(System.Type.GetType("System.Byte")) || prop.PropertyType.Equals(System.Type.GetType("System.DateTime")) || prop.PropertyType.Equals(System.Type.GetType("System.Boolean")))
                                    {
                                        if (!object.Equals(reader[prop.Name], DBNull.Value))
                                        {
                                            prop.SetValue(obj, reader[prop.Name], null);
                                        }
                                    }
                                }
                                t.Add(obj);
                            }
                            con.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            return t;
        }

So, I will get two list of objects one for master and another for parent and then loop through to add objects of child class to relevant parent class.

From these objects I will get json.

Now, so far I understand there is a performance issue in this approach as if I have 1000s of message and 10s of URL I will end up having bulk of loops.

Can you please suggest any way to generate direct from Mysql Stored Procedures and sort of this.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Jaidev Khatri
  • 159
  • 4
  • 15
  • You might want to check out [Dapper](https://github.com/StackExchange/Dapper). It's a micro-ORM built for performance. I'm pretty sure comparing your current `CreateObject` to Dapper's `Query` will show that Dapper out-performs it. Unfortunately, I don't know enough about MySql to help build a stored procedure to do what you are asking, but if you where working with SQL Server 2016 or higher it could all be done on the database level with a simple query and sub queries using `for json`. – Zohar Peled May 03 '18 at 09:05
  • You might also want to check out [MySQL: Return JSON from a standard SQL Query](https://dba.stackexchange.com/questions/192208/mysql-return-json-from-a-standard-sql-query) over at dbo.stackExchange or [How to convert result table to JSON array in MySQL](https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql) on Stackoverflow. – Zohar Peled May 03 '18 at 09:08
  • I made the solution for the time being using Createobject only. But like to know whether I leave the way I do Complex query using Stored Procedures. – Jaidev Khatri May 10 '18 at 06:54

0 Answers0