How do I translate a List<string> into a SqlParameter for a Sql In statement? [duplicate]

Question

I seem to be confused on how to perform an In statement with a SqlParameter. So far I have the following code:

cmd.CommandText = "Select dscr from system_settings where setting in @settings";
cmd.Connection = conn;
cmd.Parameters.Add(new SqlParameter("@settings", settingList));

reader = cmd.ExecuteReader();

settingsList is a List<string>. When cmd.ExecuteReader() is called, I get an ArgumentException due to not being able to map a List<string> to "a known provider type".

How do I (safely) perform an In query with SqlCommands?

Dreda

To what SQL type are you expecting your list converted? There are no arrays... – Cordova

I need the list to be a list of sql strings, for example where setting in ('setting1', 'setting2') – Dreda

@KallDrexx, that makes no sense. There's no such data type in SQL. The best you can do is modify your "CommandText" to construct the desired SQL yourself (embedding the parameter values within the SQL). It cannot be encapsulated in a SqlParameter. – Cordova

Except that allows unsafe sql to be executed. I find it hard to believe there's no construct to allow safe sql in statements – Dreda

@KirkWoll, He is not trying to insert an array, the Query is a select with an intended multi valued parameter. He is trying to do something like this: set @pCode='''A1'',''A2'',''A3''' Exec ('Select * From Receiving Where Code In (' + @pCode + ')') – Wortham

@rizzle, yes I know that. He is trying to pass an array for use as the operand in his in expression. That is not going to fly. – Cordova

@kirk woll - as of 2008, you can pass in multiple values via table valued parameters which remove the need for the "workarounds" that were required for arrays of data in previous versions – Directive

Answer 1

You could try something like this:

string sql = "SELECT dscr FROM system_settings WHERE setting IN ({0})";
string[] paramArray = settingList.Select((x, i) => "@settings" + i).ToArray();
cmd.CommandText = string.Format(sql, string.Join(",", paramArray));

for (int i = 0; i < settingList.Count; ++i)
{
    cmd.Parameters.Add(new SqlParameter("@settings" + i, settingList[i]));
}

Dynah

Since it's not possible to do what I wanted, I used this method. I love linq! – Dreda

This works until settingList.Count = 0, then you are going to get an error in SQL because WHERE setting IN () is invalid syntax. Table value parameter is more correct, see rizzle's answer. – Nika

Answer 2

You appear to be trying to pass a multi valued parameter, that SQL syntax isn't going to do what you expect. You may want to pass a table value parameter.

Read this: http://www.sommarskog.se/arrays-in-sql.html#iter-list-of-strings

specifically: http://www.sommarskog.se/arrays-in-sql-2008.html#ListSqlDataRecord

private static void datatable_example() {

   string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"};

   DataTable custid_list = new DataTable();
   custid_list.Columns.Add("custid", typeof(String));

   foreach (string custid in custids) {
      DataRow dr = custid_list.NewRow();
      dr["custid"] = custid;
      custid_list.Rows.Add(dr);
   }

   using(SqlConnection cn = setup_connection()) {
      using(SqlCommand cmd = cn.CreateCommand()) {

         cmd.CommandText =
           @"SELECT C.CustomerID, C.CompanyName
             FROM   Northwind.dbo.Customers C
             WHERE  C.CustomerID IN (SELECT id.custid FROM @custids id)";
         cmd.CommandType = CommandType.Text;

         cmd.Parameters.Add("@custids", SqlDbType.Structured);
         cmd.Parameters["@custids"].Direction = ParameterDirection.Input;
         cmd.Parameters["@custids"].TypeName = "custid_list_tbltype";
         cmd.Parameters["@custids"].Value = custid_list;

         using (SqlDataAdapter da = new SqlDataAdapter(cmd))
         using (DataSet        ds = new DataSet()) {
            da.Fill(ds);
            PrintDataSet(ds);
         }
      }
   }
}

Wortham

surely the vitally important point you are skipping over here is the need to create the custid_list_tbltype on the database before this will work – Wallin

Answer 3

If you're using Sql Server 2008 or later, you can make use of table valued parameters - this allows you to pass in a table of values as a parameter. From .net you define a "structured" type SqlParameter and set the value to something that implements IEnumerable.

See the full MSDN reference with examples here: http://msdn.microsoft.com/en-us/library/bb675163.aspx

Directive

Answer 4

I ever use my own function to create Parameters like this:

public void SomeDataFunction() {    
    ArrayList params = GetParameters(someEntity);
    CommandObject.Parameters.AddRange(parameters.ToArray());
}

public static ArrayList GetParameters(ISomeEntity entity) {
    ArrayList result = new ArrayList {                  
            OleDbUtility.NewDbParam("@Param1", OleDbType.Integer, , entity.Parameter1),
            OleDbUtility.NewDbParam("@Param2", OleDbType.VarChar, 9, entity.Parameter2),
        }
}

public static OleDbParameter NewDbParam(string parameterName, OleDbType dataType,
                    int size, object value) {
    OleDbParameter result = new OleDbParameter(parameterName, dataType, size, string.Empty);
    result.Value = value;
    return result;
}

Higa

Answer 5

Use XML, it's plenty fast for this scenario. You would turn your list into XML and simply pass a string:

CREATE TABLE #myTempTable
(   Letter VARCHAR(20) )

INSERT INTO  #myTempTable (Letter) VALUES ('A'), ('B')

Declare @xml XML = '<a>A</a><a>B</a><a>C</a>'

Select * from #myTempTable 
Where Letter in 
(Select p.value('.', 'VARCHAR(40)') AS [Letter] from @xml.nodes('//a') as t(p)) 

DROP TABLE #myTempTable

Wortham

you have not specified how to convert list to xml. – Jejune

Answer 6

I usually pass in the list as a comma separated string, and then use a table valued function to 'split' the string into a table that I can then use to join with in another query.

DECLARE @Settings TABLE (Sid INT)   
INSERT INTO @Settings(Sid)
SELECT CAST(Items AS INT) FROM dbo.Split(@SettingsParameter, ',')

Unless of course you are using SQL Server 2008, then I would use the table valued parameters.

Chitter