How do I insert multiple rows into MySql using C# where columns have dates?

Question

The following class captures SendQueue objects from API json responses:

public class SendQueue
{
    public DateTime SendDate { get; set; }
    public int StatusCode { get; set; }
    public string StatusMessage { get; set; }

}

When I receive the object, I convert the SendDate value to string to insert into a MySQL database, but the formatting apparently is not accepted by the DB and so the insert fails.

When I convert the value to string, the formatting of the SendDate is "dd/MM/yyyy hh:mm:ss" whereas the database accepts "yyyy-MM-dd hh:mm:ss".

Of course, a better way of handling this is via parameterized queries. But since I am using batch inserts, I am unable to use DateTime objects.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

I will have the same problem if I use Bulk Insert as well since the dates will eventually be converted to strings stored in a file.

Answer 1

You should use an appropriate DateTime type in your schema. Do not use strings to represent Dates or DateTimes or Times, numbers, or anything else that is not a native string.

MySql allows for the following schema types:

DATE
TIME
DATETIME
TIMESTAMP
YEAR

In this case the most appropriate one would be DATETIME. This aligns with the .net type System.DateTime. If you are using ADO.NET then you should be using parameters in your queries. Example:

using(MySqlCommand m = new MySqlCommand("INSERT INTO table (sendDate) VALUES (@sendDate)"))
{
     m.Parameters.Add("@sendDate", MySqlDbType.DateTime).Value = myObj.SendDate;
     // rest of code
}

Batch Insert

You do not have to run one insert statement at a time. You can easily create one statement with multiple tuples that you want to insert. Here is sample c# code that does that with 1 column. You can easily extend this with multiple values per tuple.

var sql = "INSERT INTO table (sendDate) VALUES (";
var parameters = input.Select((queue, i) => new MySqlParameter("@sendDate" + i.ToString(), MySqlDbType.DateTime)).ToArray();
sql += string.Join("), (", parameters.Select(_ => _.ParameterName)) + ")";
for (int i = 0; i < parameters.Length; i++)
{
    parameters[i].Value = input[i].SendDate;
}

using(var connection = new MySqlConnection(connectionString))
using(var command = new MySqlCommand(sql, connection))
{
  command.Parameters.AddRange(parameters);
  connection.Open();
  command.ExecuteScalar();
}

---

When to convert?

The code stack should always convert a DateTime to a string as late as possible up the call stack, generally in the Presentation Layer (at the point a Human has to read it). The reverse is also true, a string input should be converted to a DateTime as early as possible going down the call stack (so again, in the presentation layer).

---

Why not varchar for Dates?

If you are wondering why you should not store DateTime as a string see this previous answer: When to use VARCHAR and DATE/DATETIME. I'll quote the important stuff:

Some of the disadvantages of the VARCHAR version:

• You can't easily add / subtract days to the VARCHAR version.
• It is harder to extract just month / year.
• There is nothing stopping you putting non-date data in the VARCHAR column in the database.
• The VARCHAR version is culture specific.
• You can't easily sort the dates.
• It is difficult to change the format if you want to later.
• It is unconventional, which will make it harder for other developers to understand.
• In many environments, using VARCHAR will use more storage space. This may not matter for small amounts of data, but in commercial environments with millions of rows of data this might well make a big difference.

---

Time Zones and Values

Do not forget about time zones if this is applicable to your application. It is recommended to always store a DateTime UTC value in the database instead of a local timezone value. The UTC value can then be sent to the presentation layer where the presentation layer is responsible for applying the local time zone to the value (optional). Some UI frameworks have built in mechanisms for doing this in the DateTime controls. Again, the reverse is also true, have the presentation layer convert any input to a UTC DateTime value and send that back down the call stack to the server.

Answer 2

Per your updated question, it seems the root issue is that you need a way to insert multiple rows via a single insert statement.

Ideally you'd provide a stored procedure which could accept a table type as input, as could be done in the SqlServer world: Insert entire DataTable into database at once instead of row by row?

Given that's not an option for MySQL at time of writing however, there are a few alternatives...

If you need to do this because you need all rows to be inserted in the same transaction, simply wrap your inserts in a transaction. Code would be something like this (untested as I don't have MySQL).

public void InsertMySql(string connectionString, string command, DataTable data)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                using (MySqlCommand cmd = new MySqlCommand(command, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    foreach (var row in data.Rows)
                    {
                        cmd.Parameters.Clear();
                        foreach (var cell in data.Columns)
                        {
                            cmd.Parameters.AddWithValue($"@{cell.Name}", row[cell]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            catch 
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

If you need to do this for performance reasons, another option may be to look into a third party library such as: https://github.com/zzzprojects/Bulk-Operations. I've not tried this personally, but the library looks promising / the publisher (zzzprojects) are quite well known and respected / maintain the popular site SQL Fiddle amongst other projects.

public static void InsertMySql(string connectionString, DataTable data)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();
        var bulk = new BulkOperation(connection);
        bulk.BulkInsert(data); //assumes your data table's table name, column names/definitions match your table's; I think.  See https://bulk-operations.net/bulk-insert for what documentation's available
    }
}