2

Is there a way to add a parameter to my SqlCommand in a way that the engine will not complain if it's not used in my query?

I have about 50 parameters to include in my query but which parameters need to be included depends highly on the situation. I could easily delete 200 lines of code if i could just put them all on top and build my query after adding my params.

A very simple / dumb / wrong.. example (yes, the solution here is to add id to the else clause)

cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@name", SqlDbType.nVarChar, 250).Value = name;

if(id == null) cmd.CommandText = "INSERT INTO tab (name) VALUES (@name)";
else cmd.CommandText = "UPDATE tab SET name = @name WHERE id = @id";

This returns the error:

System.Data.SqlClient.SqlException: The parameterized query '(@id,@name) ' expects the parameter '@id', which was not supplied

If it's not possible, a simple 'No' will suffice to be accepted as an answer..

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tiele Declercq
  • 2,070
  • 2
  • 28
  • 39

1 Answers1

6

Is there a way to add a parameter to my SqlCommand in a way that the engine will not complain if it's not used in my query?

ADO.NET does not complain if you add a parameter and do not use it. The error message you report is because you are trying to use a parameter that you didn't add - the opposite scenario. Most likely, id is null. Parameters with a value of null are not added - you need to use DBNull.Value:

cmd.Parameters.Add("@id", SqlDbType.Int).Value = ((object)id) ?? DBNull.Value;
cmd.Parameters.Add("@name", SqlDbType.nVarChar, 250)
         .Value = ((object)name) ?? DBNull.Value;

Alternatively, tools like "dapper" will make this easy:

conn.Execute(sql, new { id, name }); // job done
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I was not aware of this horrible design choice of ADO.NET. – usr Apr 28 '14 at 14:39
  • 1
    @usr don't get me started: http://stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull/9632050#9632050 – Marc Gravell Apr 28 '14 at 14:40
  • Marc, thank you for your input. It helped me to find the problem.. however it's not exactly like you said. Every parameter that you add REQUIRES a VALUE. No matter if you use the parameter in the query or not. – Tiele Declercq Apr 29 '14 at 07:39
  • 1
    @TieleDeclercq indeed; a parameter without a value implicitly has a `null` value, and will not be sent. In the example you show, you assign *something* to each `.Value`. The code I show illustrates how to turn that "something" into a `DBNull` if necessary - which is enough for it to exist etc. – Marc Gravell Apr 29 '14 at 08:03