How to insert all C# object values to MySQL table in one query?

Question

I have an object with many parameters, each represents a column in the MySQL table.

I have a code which generates a new object with all the above parameters and their values:

foreach (MetaData metaData in elnetMcMapping.DataItems) {
    var value = metaData.Value[0] * metaData.Multiplier;
    Type type = mcReadings.GetType();
    PropertyInfo prop = type.GetProperty(metaData.Label);
    prop.SetValue(mcReadings, value, null);
}

Now I want to insert all the values to the MySql table as a new row but not sure how to dynamically implement the below logic:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

I am looking for something like this:

await dataAccess.SaveData("insert into", mcReadings, DeviceDb);

Does anyone have a code example to help me with this ?

Answer 1

I do something like the following:

// Build the SQL statement, this can be cached and used again
var propList = mcReadings.GetType().GetProperties().ToList();
StringBuilder str = new StringBuilder();
str.Append("INSERT INTO elnet21630388 (");
// Build column list
foreach(PropertyInfo prop in propList) 
{
    str.Append(prop.Name + ",");
}
str.Remove(str.Length - 1, 1);
str.Append(") VALUES (");
// Build values list
foreach (PropertyInfo prop in propList) 
{ 
    str.Append("@" + prop.Name + ",");
}
str.Remove(str.Length - 1, 1);
str.Append(");");

// Generate the dynamic parameters, this needs to be done on each call 
DynamicParameters parameters = new DynamicParameters();
foreach (PropertyInfo prop in propList)
{
    parameters.Add("@" + prop.Name, prop.GetValue(mcReadings, null));
}

connection.Execute(str.ToString(), parameters);