How to use a INSERT INTO SQL Query with Dynamic parameters in C#?

Question

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?

that the startutc date shuld be between 1900 - 2999... , the datetime is empty — Eray Geveci
– Eray Geveci, Commented Feb 22, 2013 at 6:50
Unrelated Tip: If you use using (SqlCommand cmd = ...) { ... } then there is no need to call cmd.Dispose(); and Parameters.Clear() — Peet Brits
– Peet Brits, Commented Feb 22, 2013 at 6:52

Community · Accepted Answer · 2017-05-23 12:18:59Z

2

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;

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Feb 22, 2013 at 6:57

Peet Brits

3,2851 gold badge33 silver badges50 bronze badges

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

1 Comment

Eray Geveci Over a year ago

i tried your code, even if i let the datetime empty it shows me this date: {01.01.0001 00:00:00} how can i use this in my if statement?

Vignesh Subramanian · Accepted Answer · 2013-02-22 06:52:53Z

1

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");
}

answered Feb 22, 2013 at 6:52

Vignesh Subramanian

7,31915 gold badges97 silver badges161 bronze badges

2 Comments

Eray Geveci Over a year ago

what can i do when a datetime is empty?

Vignesh Subramanian Over a year ago

you can use the ternary operators in all your command.Parameters.Add for example instead of >> cmd.Parameters.Add("@ENDUTC", request.endUTC) you can use >> cmd.Parameters.Add("@ENDUTC", (request.endUTC == null) ? (object)DBNull.Value : request.endUTC); similarly you can do for adding all the other parameters vote up if you find the above comment useful in someway

Doruk · Accepted Answer · 2013-02-22 07:00:23Z

0

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);

answered Feb 22, 2013 at 7:00

Doruk

9129 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to use a INSERT INTO SQL Query with Dynamic parameters in C#?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related