-1

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.

16
  • 10
    Why are you storing dates as strings in the database? Commented Nov 7, 2018 at 10:47
  • 7
    3. Neither, the correct place to format the DateTime value into a string is when you need to display it. The database should be able to accept DateTime values, not formatted as strings. Commented Nov 7, 2018 at 10:48
  • 1
    You can parse the Datetime when the request reaches the WEB API and before storing into the DB. This will give you more control since you can format it depending on which DB engine you will store it in. You can format it before sending it in the mobile or web application if you know what is the storage format accepted by the DB and you will never change it. Commented Nov 7, 2018 at 10:55
  • 1
    @JohnLBevan this does not work when you are using batch inserts. Parameterized queries are best when used for one statement. But I prepare my data so that I use them in a batch statement (e.g. INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);) I doubt if this works there. Commented Nov 7, 2018 at 12:19
  • 1
    @JohnLBevan exactly. SQL Server is much simpler. And yes that is my question. Commented Nov 7, 2018 at 12:31

2 Answers 2

2

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.

Sign up to request clarification or add additional context in comments.

3 Comments

The reason I did not use parameterized queries was that I INSERT many records with a single script and I did not find a good library for bulk insert in MySQL.
Using single insert queries the type you so brilliantly described slows down the word when we have a couple hundred thousand rows. I either need to use bulk inserts which demand that the entire dataset is written to a .csv file first, but then I realized having one insert with many rows included is significantly faster than single inserts and that is why I adopted this method.
@Disasterkid - see the addition, heading Batch Insert. You can create batches that insert data in the same command. The sample code can be extended with additional values depending on the width of the destination.
0

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
    }
}

3 Comments

I can consider transactions. But is using multiple single parameterized INSERTs inside of one transaction faster than running the same number of single parameterized INSERTs without that transaction? / Also is using transactions this way any faster than batch inserts?
I've not suggested the transaction approach for performance, but for the functional impact (i.e. if you insert 3 rows then others looking at the database will see no rows until the transaction's committed, then they'll see 3;whereas without a transaction if the rows are inserted one by one they may see 1 or two rows; which in some scenarios may be invalid (e.g. finance system where you're creating different rows for credits and debits, and require that they always have to balance).
Transactions can improve performance since some checks can be performed for all rows at once rather than for each individual insert; but they can impact performance by tying up resources / causing blocking. If performance is your aim try experimenting with both approaches with realistic data and load to get an idea of the real life output.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.