在mysql更新查询中使用c＃datetime [英] using c# datetime in mysql update query
问题描述
我正在尝试使用mysql连接器从MySQL网站运行查询从C＃到MySQL（版本5.5.27）。我开发的.dll的目标是跟踪我读过的行。
我用来读取数据库的查询是：
string strSQL =SELECT date，ask，bid，volume FROM gbpjpy where`read` = 0;
要阅读日期，我有：
DateTime dateTime = mysqlReader.GetDateTime（0）;
工作正常。
有5列，最后一列称为读。在选择查询和解析完成之后，我执行以下查询：
string sqlFormattedDate = dateTime.ToString（yyyy / MM / dd HH：mm：ss）;
string query =UPDATE gbpjpy SET`read` = 1 WHERE`date` =+ sqlFormattedDate;
但是，当我查看我的日志时，我有以下错误：
您的SQL语法有错误;请检查与您的MySQL服务器版本相对应的手册，以便在第1行的01：20：08附近使用正确的语法。
读入的第一个日期是2012 / 08/30 01:20:08（这是怎么出现在MySQL表中，所以这是01:20:08）。
我已经尝试过var sqlFormattedDate，将ToString重载更改为yyyy-MM-dd（使用破折号而不是斜杠）和dateTime.ToString（）全部无效
为什么MySQL没有收到整个字符串？
基本上你应该避免直接在你的查询中包含值。
毫无疑问，你可以把引号放在价值...但你应该'吨。相反，您应该使用paramterized SQL，并将值放在参数中。这样，您不会出现容易出错的字符串转换，您可以避免SQL注入攻击（用于字符串参数），并将数据与数据分开。
（作为示例你现在的代码会使用当前文化的日期和时间分隔符 - 这可能不是 / 和：。您可以通过指定 CultureInfo.InvariantCulture ...来解决此问题，但最好不要进行转换。）
查找参数属性在任何命令中键入文档的文档使用（例如 MySqlCommand.Parameters ），这将有希望给你的例子。在参数化SQL的文档中甚至可能有一个教程部分。例如，这个页面 可能是你以后的。
I'm trying to run a query from C# to MySQL (version 5.5.27) using the mysql connector for .net from the MySQL website.
The ultimate goal of the .dll I'm developing is to keep track of the rows that I've read.
The query I use to read the database is:
string strSQL = "SELECT date,ask,bid,volume FROM gbpjpy where `read` = 0";
To read in the date, I have:
DateTime dateTime = mysqlReader.GetDateTime(0);
That works fine.
The table has 5 columns, the last column is called "read". Right after the select query and parsing is done, I execute the following query:
string sqlFormattedDate = dateTime.ToString("yyyy/MM/dd HH:mm:ss");
string query = "UPDATE gbpjpy SET `read` = 1 WHERE `date` = " + sqlFormattedDate;
However, when I check my log, I have the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01:20:08' at line 1.
The first date read in is 2012/08/30 01:20:08 (that's how it appears in the MySQL table, so that's where it gets the 01:20:08).
I've tried var sqlFormattedDate, changing the ToString overload to yyyy-MM-dd (using dashes and not forward slashes) and dateTime.ToString() all to no avail.
Why is MySQL not picking up the entire string?
Basically you should avoid including values in your query directly.
No doubt you could put quotes around the value... but you shouldn't. Instead, you should use paramterized SQL, and put the value in the parameter. That way you don't an error-prone string conversion, you avoid SQL injection attacks (for string parameters), and you separate code from data.
(As an example of how subtly-broken this can be, your current code will use the "current culture"'s date and time separators - which may not be / and :. You could fix this by specifying CultureInfo.InvariantCulture... but it's best not to do the conversion at all.)
Look for documentation of a Parameters property on whatever Command type you're using (e.g. MySqlCommand.Parameters) which will hopefully give you examples. There may even be a tutorial section in the documentation for parameterized SQL. For example, this page may be what you're after.
这篇关于在mysql更新查询中使用c＃datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
