DbParameterCollection does not contain a definition for AddWithValue

Question

Getting above error on the last line of the following code. I'm using EF Core 1.1. Trying to follow this suggestion.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient; //Visual Studio had greyed out this line suggesting this as unnecessary.

var conn = _context.Database.GetDbConnection();

var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "MySproc";
cmd.Parameters.AddWithValue("@MyParameter", 42);

@H.Herzl I tried your suggestion - still the same error. Also when I added `System.Data.SqlClient` Visual Studio has greyed it out suggesting this using statement as unnecessary. — nam, May 12 '17 at 21:43
I think you need to cast your db connection to sql connection — H. Herzl, May 12 '17 at 21:51
You should also be aware of the performance implications of using `AddWithValue`. Rather than repeat them all, there is a good explanation of these in the accepted answer [here](https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications) — bornfromanegg, Jul 24 '19 at 12:29

score 18 · Answer 1 · answered May 15 '17 at 15:34

18

Here's the equivalent code using just the System.Data.Common APIs. (It can be used with any ADO.NET provider.)

var parameter = cmd.CreateParameter();
parameter.ParameterName = "@MyParameter";
parameter.Value = 42;

cmd.Parameters.Add(parameter);

answered May 15 '17 at 15:34

bricelam

28,825
9
92
117

1

This answer doesn't have any relation with question, the question is about to use a method that allows add a parameter with name and value in same code line – H. Herzl Aug 06 '17 at 18:33
3

@H.Herzl There is no such method available without casting to a more specific type. This code accomplishes the task without casting. – bricelam Aug 07 '17 at 15:48
2

Great answer - exactly what I needed! This should be the accepted answer. – Tom Dec 05 '17 at 01:15
@bricelam is this injection safe? – Sep 02 '18 at 00:38
1

Yes. ADO.NET parameters are designed to prevent SQL injection. – bricelam Sep 04 '18 at 15:43

score 4 · Accepted Answer · answered May 12 '17 at 21:57

4

Add reference for System.Data.SqlClient and cast your DbConnection instance to SqlConnection.

answered May 12 '17 at 21:57

H. Herzl

3,030
1
14
19

1

NOTE: this only applies for SQL Server. If you're using MySQL and therefore have a MySqlConnection object instead, you can't perform that cast. – David Dec 21 '17 at 20:21

score 1 · Answer 3 · answered Feb 27 '20 at 15:13

1

In dotnet core 3+ running command is broken. To fix it, change System.Data.SqlClient to Microsoft.Data.SqlClient

answered Feb 27 '20 at 15:13

Sukhminder Sandhu

696
1
5
6

DbParameterCollection does not contain a definition for AddWithValue

3 Answers3