C#/.NET sample by for parameterizing SQL IN queries

// c#/.NET sample by fredrik@johanssonrobotics.se for parameterizing SQL "IN" queries
// NOTE: execute "CREATE TYPE [dbo].[inttabletype] AS TABLE([value] [int] NULL)" in SQL-server first

// define class compatible with what SqlParameter expect
private class SqlHashSet : HashSet<int>, IEnumerable<SqlDataRecord> {
    IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() {
        SqlDataRecord ret = new SqlDataRecord(new SqlMetaData("value", SqlDbType.Int));
        foreach (var data in this) {
            ret.SetValue(0, data);
            yield return ret;
        }
    }
}

// create the id-array (may be HUGE)
var myIds = new SqlHashSet();
myIds.Add(1);
myIds.Add(2);
myIds.Add(3);
...

// execute the query
using (var con = new SqlConnection(connectionString)) {
    con.Open();
    using (var cmd = con.CreateCommand()) {
        cmd.CommandText = "SELECT * FROM mytable WHERE id IN (@i)"
        cmd.Parameters.Add(new SqlParameter("@i", myIds) {
            SqlDbType = System.Data.SqlDbType.Structured,
            TypeName = "dbo.inttabletype"
        });
        using (var reader = cmd.ExecuteReader()) {
            ...