daily pastebin goal
40%
SHARE
TWEET

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

fredrikdev May 2nd, 2013 556 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // c#/.NET sample by fredrik@johanssonrobotics.se for parameterizing SQL "IN" queries
  2. // NOTE: execute "CREATE TYPE [dbo].[inttabletype] AS TABLE([value] [int] NULL)" in SQL-server first
  3.  
  4. // define class compatible with what SqlParameter expect
  5. private class SqlHashSet : HashSet<int>, IEnumerable<SqlDataRecord> {
  6.         IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() {
  7.                 SqlDataRecord ret = new SqlDataRecord(new SqlMetaData("value", SqlDbType.Int));
  8.                 foreach (var data in this) {
  9.                         ret.SetValue(0, data);
  10.                         yield return ret;
  11.                 }
  12.         }
  13. }
  14.  
  15. // create the id-array (may be HUGE)
  16. var myIds = new SqlHashSet();
  17. myIds.Add(1);
  18. myIds.Add(2);
  19. myIds.Add(3);
  20. ...
  21.  
  22. // execute the query
  23. using (var con = new SqlConnection(connectionString)) {
  24.         con.Open();
  25.         using (var cmd = con.CreateCommand()) {
  26.                 cmd.CommandText = "SELECT * FROM mytable WHERE id IN (@i)"
  27.                 cmd.Parameters.Add(new SqlParameter("@i", myIds) {
  28.                         SqlDbType = System.Data.SqlDbType.Structured,
  29.                         TypeName = "dbo.inttabletype"
  30.                 });
  31.                 using (var reader = cmd.ExecuteReader()) {
  32.                         ...
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top