問題描述
如何在 C# 中使用帶有動態參數的 INSERT INTO SQL 查詢？ (How to use a INSERT INTO SQL Query with Dynamic parameters in C#?)
Hello i have a method which saves some data in the SQL, but sometimes not every parameters are set, this gives me an exception becuase its NULL.
Here is my Method:
public string getBookingURL(string guid, BookingRequest request, string token, out string exitURL)
{
if (validateToken(token))
{
string command = "INSERT INTO OUTLOOKADDIN (GUID, TOKEN, BOOKINGID, STARTUTC, ENDUTC, SUBJECT, NUMPARTICIPANTS) VALUES (@GUID, @TOKEN, @BOOKINGID, @STARTUTC, @ENDUTC, @SUBJECT, @NUMPARTICIPANTS)";
SqlConnection connection = new SqlConnection(GetConnectionString());
connection.Open();
try
{
SqlCommand cmd = new SqlCommand(command, connection);
cmd.Parameters.Add("@GUID", guid);
cmd.Parameters.Add("@TOKEN", token);
cmd.Parameters.Add("@BOOKINGID", request.bookingID);
cmd.Parameters.Add("@STARTUTC", request.startUTC);
cmd.Parameters.Add("@ENDUTC", request.endUTC);
cmd.Parameters.Add("@SUBJECT", request.subject);
cmd.Parameters.Add("@NUMPARTICIPANTS", request.numParticipants);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
catch (Exception ex)
{
log.Error(ex.Message + "\n\rStackTrace:\n\r" + ex.StackTrace);
}
finally
{
connection.Close();
}
HttpContext current = HttpContext.Current;
string baseUrl = current.Request.Url.Scheme + "://"
+ current.Request.Url.Authority
+ current.Request.ApplicationPath.TrimEnd('/');
exitURL = baseUrl + "/index.html";
return baseUrl + '/'
+ "WebPage/Booking/BBooking.aspx?"
+ "OPS"
+ guid; ;
}
exitURL = null;
return null;
}
i think that this SQL Query...
string command = "INSERT INTO OUTLOOKADDIN (GUID, TOKEN, BOOKINGID, STARTUTC, ENDUTC, SUBJECT, NUMPARTICIPANTS) VALUES (@GUID, @TOKEN, @BOOKINGID, @STARTUTC, @ENDUTC, @SUBJECT, @NUMPARTICIPANTS)";
... should run dynamically or is there any other solution which prevents adding the parameter when its empty?
‑‑‑‑‑
參考解法
方法 1:
In addition to vignesh's post, use DBNull.Value for null values, if the DB fields are nullable.
EDIT: It is probably better to check the values BEFORE you even get to your getBookingURL() method. If you do proper validation before reaching this point then you can avoid the whole problem altogether.
if (/* date is null or not valid */)
cmd.Parameters.Add("@mydate", SqlDbType.DateTime).Value = DBNull.Value;
else
cmd.Parameters.Add("@mydate", SqlDbType.DateTime).Value = myDate;
方法 2:
You can add validation in the front end to prevent empty values
or
you can check for null values before adding parameters
if(value!=null)
{
cmd.Parameters.Add("@GUID", guid);
}
else
{
cmd.Parameters.Add("@GUID", "somestring or value");
}
方法 3:
If your fields in the db table are unnulable, you must validate the values on the front end and show a warningto the user.
If the columns are nullable, you can send the DBNull to the database and it wont throw exception. My solution for it:
cmd.Parameters.AddWithValue("@ParameterName",
(a == null) ? (object)DBNull.Value : a);
(by Eray Geveci、Peet Brits、Vignesh Subramanian、Doruk)