how to add a date (DateTime.now) to a mySql database in C#

Question

I have been researching on how to insert a date into my mySql database using the visual studio. I understand the error that mySql only accepts a certain format and I've been browsing both the net and other similar topics about this here on stackoverflow... but almost all the examples have the date as a pre-made string that is changed using the parseexact conversion

It looks something like this:

result = DateTime.ParseExact(nowstring, format, provider)

But the thing is I'm trying this with a dateTime.now function. I want a realtime dateTime added to the database each time my program does something.. well from what I researched, it should look something like this..

string nowstring, format;
                DateTime result;
                System.Globalization.CultureInfo provider = System.Globalization.CultureInfo.InvariantCulture;
                format = "yyyy-MM-dd";
                nowstring = DateTime.Now.ToString();
                result = DateTime.ParseExact(nowstring, format, provider);

I guess I'm just doing something wrong since we havent tackled this at school yet or im just simply missing something elementary. Either way, I'd glady appreciate your help.

Soner Gönül · Accepted Answer · 2015-03-08 11:59:12Z

3

If your column that you want to insert DATE or DATETIME type, you don't need any of these formatting and parsing operations.

Just pass your DateTime.Now directly to your parameterized insert query to your table.

MySQL doesn't save your DateTime values as a character for these column types. It keeps them as a binary which humans can't read. You can see them with 'YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS' format as a representation in your database.

For example;

using(var con = new MySqlConnection(conString))
using(var cmd = con.CreateCommand())
{
   cmd.CommandText = "insert into tbl_operators (DateJoined) values (@date)";
   cmd.Parameters.AddWithValue("@date", DateTime.Now);
   con.Open();
   cmd.ExecuteNonQuery();
}

edited Mar 8, 2015 at 11:59

answered Mar 8, 2015 at 11:42

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

5 Comments

Matt Yoü Over a year ago

I'm sorry, I am not familiar with "parameterized" insert query. Should the query go something like this? cmd.CommandText = "insert into tbl_operators (DateJoined) values ('"DateTime.Now"')"; By the way, I did this and it posted an error.

Soner Gönül Over a year ago

@MattYoü Read: blog.codinghorror.com/…

Matt Yoü Over a year ago

sir, I have read multiple articles about parameterizing as well as SQL Injection.. but it is simply too much for a novice like me. I mean, I understand there is a huge problem with how I execute my database queries but I simply want to add a DateTime.Now to my database... please help me...

Matt Yoü Over a year ago

Its working now thanks a lot! just a question though, how does the cmd.Parameters.AddWithValue work? what if i have multiple values that I want to add? does that mean multiple cmd.Parameters.AddWithValue?

Soner Gönül Over a year ago

@MattYoü Yes, you can add multiple parameter values with multiple AddWithValue method. For example;

cmd.CommandText = "insert into tbl_operators (DateJoined, DateJoined1) values (@date, @date1)"; cmd.Parameters.AddWithValue("@date", DateTime.Now); cmd.Parameters.AddWithValue("@dat1", DateTime.Now);

Collectives™ on Stack Overflow

how to add a date (DateTime.now) to a mySql database in C#

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related