Question

c# 向oracle中插入 date类型数据

DateTime _logintime = System.DateTime.Now;

conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "insert into SDE.PPXIP(USERNAME,LOGINIP,LOGINTIME,LOGINSTATION) values('" + _username + "','" + _loginip + "','" + _logintime + "','" + _loginstation + "')";
cmd.ExecuteNonQuery();

insert 语句错误 。查询了下网上说要用to_date转下。
'" + TO_DATE('"+DateTime.Now.Date.toString()+", 'YYYY-MM-DD HH24:MI:SS') + "'
但我把揉合进去怎么也不成功。请帮下忙
'" + TO_DATE('"+DateTime.Now.Date.toString()+"', 'YYYY-MM-DD HH24:MI:SS') + "'

Answer 1

StringBuilder strSql = new StringBuilder();
strSql.Append(" INSERT INTO SDE.PPXIP(USERNAME,LOGINIP,LOGINTIME,LOGINSTATION) ");
strSql.Append(" VALUES(:USERNAME,:LOGINIP,:LOGINTIME,:LOGINSTATION) ");

OracleParameter[] parameters = {
new OracleParameter(":USERNAME", OracleType.VarChar,50),
new OracleParameter(":LOGINIP", OracleType.VarChar,50)
new OracleParameter(":LOGINTIME", OracleType.DateTime),
new OracleParameter(":LOGINSTATION", OracleType.VarChar,50),

};
parameters[0].Value = _username;

parameters[1].Value = _loginip;

parameters[2].Value = Convert.ToDateTime(_logintime);

parameters[3].Value = _loginstation;

return SQLHelp.ExecuteSql(strSql.ToString(), parameters);

Answer 2

不要用toString(),它和时区的设置有关，在控制面板改一下时区设置你的程序立马报错。单独把年月日时分秒取出来拼成字符串就可以了

Answer 3

cmd.CommandText = "insert into SDE.PPXIP(USERNAME,LOGINIP,LOGINTIME,LOGINSTATION) values('" + _username + "','" + _loginip + "',sysdate,'" + _loginstation + "')";
cmd.ExecuteNonQuery();