C# select data using join to temp table

This is useful if you want to select a lot of data by key and writing IN statement is not efficient (too much data etc..). You load the keys to temp table and then select data using a join.

CREATE TABLE [dbo].[Table_1](
  [Id] [int] NOT NULL,
  [Name] [varchar](50) NULL
)

[TestFixture]
public class TestSelectWithTempTableJoin
{
  [Test]
  public void Test()
  {
 
    const string nbTempCreate = @"
      CREATE TABLE #Ids(
        Id INT
      )
    ";
 
    const string nbTempDrop = @"
      DROP TABLE #Ids
    ";
 
    const string query = @"
      SELECT * FROM Table_1 t
      JOIN #Ids temp ON t.Id = temp.Id
	";
 
    var ids = new List<int>(new int[]{1,3,4});
    var rows = new List<KeyValuePair<int, string>>();
 
    var sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
    sqlConnectionStringBuilder.DataSource = "(local)";
    sqlConnectionStringBuilder.InitialCatalog = "Tests";
    sqlConnectionStringBuilder.ApplicationName = "TestTempTableJoin";
    sqlConnectionStringBuilder.IntegratedSecurity = true;
 
    using (var conn = new SqlConnection(sqlConnectionStringBuilder.ToString()))
    {
        conn.Open();
        var tran = conn.BeginTransaction();        
 
        try
        {
        SqlCommand sqlCommand = null;
 
        //create temp table
        using (sqlCommand = new SqlCommand(nbTempCreate, conn, tran))
        {
            sqlCommand.ExecuteNonQuery();
        }
 
        //load data
        var dt = new DataTable();
        dt.Columns.Add("Id", typeof(int));
 
        foreach (int id in ids)
        {
            var row = dt.NewRow();
            row["Id"] = id;
            dt.Rows.Add(row);
        }
 
        using (SqlBulkCopy bcp = new SqlBulkCopy(conn,new SqlBulkCopyOptions(), tran))
        {
            bcp.DestinationTableName = "#Ids";
            bcp.BulkCopyTimeout = 30;
            bcp.BatchSize = 10;
            bcp.WriteToServer(dt);
        }
 
        //execute query
        using (sqlCommand = new SqlCommand(query, conn, tran))
        {
            using (SqlDataReader reader = sqlCommand.ExecuteReader())
            {
            while (reader.Read())
            {
                rows.Add(new KeyValuePair<int, string>(reader.GetInt32(0), reader.GetString(1)));
            }
            }
        }
 
        //remove temp table
        using (sqlCommand = new SqlCommand(nbTempDrop, conn, tran))
        {
            sqlCommand.ExecuteNonQuery();
        }
 
        tran.Commit();
      }
      catch (Exception)
      {
        tran.Rollback();
      }
      finally
      {            
        conn.Close();
      }
    }
 
    Console.WriteLine("Found {0} rows", rows.Count);
 
    foreach (var row in rows)
    {
        Console.WriteLine("{0},{1}",row.Key,row.Value);
    }
  }
}