/// <summary>
/// Execute an INSERT query with multiple values within a transaction.
/// </summary>
/// <param name="tableName">The table in which you wish to INSERT.</param>
/// <param name="keyValuePairList">List of dictionaries containing key-value pairs for the rows you wish to INSERT.</param>
public void InsertMultiple(string tableName, List <Dictionary <string, object> > keyValuePairList)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairList == null || keyValuePairList.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairList));
}
#region Validate-Inputs
Dictionary <string, object> reference = keyValuePairList[0];
if (keyValuePairList.Count > 1)
{
foreach (Dictionary <string, object> dict in keyValuePairList)
{
if (!(reference.Count == dict.Count) || !(reference.Keys.SequenceEqual(dict.Keys)))
{
throw new ArgumentException("All supplied dictionaries must contain exactly the same keys.");
}
}
}
#endregion
#region Build-Keys
string keys = "";
int keysAdded = 0;
foreach (KeyValuePair <string, object> curr in reference)
{
if (keysAdded > 0)
{
keys += ",";
}
keys += MysqlHelper.PreparedFieldName(curr.Key);
keysAdded++;
}
#endregion
#region Build-Values
List <string> values = new List <string>();
foreach (Dictionary <string, object> currDict in keyValuePairList)
{
string vals = "";
int valsAdded = 0;
foreach (KeyValuePair <string, object> currKvp in currDict)
{
if (valsAdded > 0)
{
vals += ",";
}
if (currKvp.Value != null)
{
if (currKvp.Value is DateTime || currKvp.Value is DateTime?)
{
vals += "'" + DbTimestamp((DateTime)currKvp.Value) + "'";
}
else if (currKvp.Value is int || currKvp.Value is long || currKvp.Value is decimal)
{
vals += currKvp.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(currKvp.Value.ToString()))
{
vals += MysqlHelper.PreparedUnicodeValue(currKvp.Value.ToString());
}
else
{
vals += MysqlHelper.PreparedStringValue(currKvp.Value.ToString());
}
}
}
else
{
vals += "null";
}
valsAdded++;
}
values.Add(vals);
}
#endregion
#region Build-INSERT-Query-and-Submit
Query(MysqlHelper.InsertMultipleQuery(tableName, keys, values));
#endregion
}