我创建了一个函数,如果字符串的字段长度小于 2 个字符,它应该将一个空值发送回我的数据库,但它在数据库上将其更改为 00-00-00。有人可以指出我正确的方向。
public string dateChanger(string txtBox)
if (txtBox.Length < 2)
{
return DBNull.Value.ToString();
}
else
{
var date = txtBox;
var name = date.Substring(6, 4) + "-" + date.Substring(3, 2) + "-" + date.Substring(0, 2);
return name;
}
}
下面是我用来调用函数“dateChanger”并插入数据库的代码
[HttpPost]
public JsonResult AjaxMethodSaveStudent(string cboStudentName = "", string cboStudentSurname = "", string txtStudentMiddleName = "", string txtStudentNumber = "", string txtStudentDOB = "", string txtStudentPreferredName = "", string txtStudentPropertyName = "", string txtStudentRegImmiCardNumber = "")
{
long studentId = 0;
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
string queryStudent = "INSERT INTO `STUDENT REGISTER` " +
"(`first name`, " +
"`surname`, " +
"`Middle Names`," +
"`DATE OF BIRTH`," +
"`ImmiCardNumber`) " +
"VALUES" +
"('" + cboStudentName + "'," +
"'" + cboStudentSurname + "'," +
"'" + txtStudentMiddleName + "'," +
"'" + dateChanger(txtStudentDOB) + "'," +
"'" + txtStudentRegImmiCardNumber + "') ";
using (MySqlCommand cmd = new MySqlCommand(queryStudent))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
studentId = cmd.LastInsertedId;
con.Close();
}
}
return Json(new { success = true, studentId = studentId }, JsonRequestBehavior.AllowGet);
}
该问题的天真解决方案是像这样更改函数:
public string dateChanger(string txtBox)
{
if (txtBox.Length < 2) return "NULL";
return txtBox.Substring(6, 4) + "-" + txtBox.Substring(3, 2) + "-" + txtBox.Substring(0, 2);
}
该函数的一个稍微改进的版本如下所示:
public string dateChanger(string txtBox)
{
if (txtBox.Length < 2) return "NULL";
return "'" + DateTime.Parse(txtBox).ToString("yyyy-MM-dd") + "'";
}
请注意,这两个都需要将 SQL 单引号的责任转移到函数中。否则,您可能无法从该函数返回任何将NULL
值放入数据库的内容,因为数据库会将其视为字符串文字。而不是+ "'" + dateChanger(...) + "'" +
你只是想要+ dateChanger(...) +
。您还需要考虑如何处理对 的需求,IS
而不是=
与NULL
.
但是这些都不要做!
这两种可能性都迎合了将参数数据作为字符串提供的不良做法。这会对性能、可维护性和安全性产生负面影响。只是不要这样做。
最好的选择是dateChanger()
完全从项目中删除该功能,以支持以下内容:
[HttpPost]
public JsonResult AjaxMethodSaveStudent(string cboStudentName = "", string cboStudentSurname = "", string txtStudentMiddleName = "", string txtStudentNumber = "", string txtStudentDOB = "", string txtStudentPreferredName = "", string txtStudentPropertyName = "", string txtStudentRegImmiCardNumber = "")
{
long studentId = 0;
string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
string queryStudent =
"INSERT INTO `STUDENT REGISTER` " +
"(`first name`, `surname`, `Middle Names`,`DATE OF BIRTH`, `ImmiCardNumber`) " +
"VALUES (@StudentName, @Surname, @MiddleName, @DOB, @ImmiCardNumber);";
using (MySqlConnection con = new MySqlConnection(constr))
using (MySqlCommand cmd = new MySqlCommand(queryStudent, con))
{
//Use actual types and lengths from the database here.
// Do NOT use AddWithValue() to skip setting parameter types.
cmd.Parameters.Add("@StudentName", MySqlDbType.VarChar, 30).Value = cboStudentName;
cmd.Parameters.Add("@Surname", MySqlDbType.VarChar, 30).Value = cboStudentSurname;
cmd.Parameters.Add("@MiddleName", MySqlDbType.VarChar, 30).Value = txtStudentMiddleName;
cmd.Parameters.Add("@DOB", MySqlDbType.DateTime).Value = (txtStudentDOB.Length > 2) ? DateTime.Parse(txtStudentDOB.Substring(6, 4) + "-" + txtStudentDOB.Substring(3, 2) + "-" + txtStudentDOB.Substring(0, 2)) : (object)DBNull.Value;
cmd.Parameters.Add("@ImmiCardNumber", MySqlDbType.VarChar, 30).Value = txtStudentRegImmiCardNumber;
con.Open();
cmd.ExecuteNonQuery();
studentId = cmd.LastInsertedId;
}
return Json(new { success = true, studentId = studentId }, JsonRequestBehavior.AllowGet);
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句