CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Dismissile
Dismissile

Reputation: 33071

SQL Query - Select from a List of Items/Tuples

I have a list of objects that I retrieved from a web service call and I need to match them up against some rows in one of my database tables. My object has a few properties:

Name - string
Type - string

Together Name and Type would bring back a unique item in my database:

SELECT * FROM dbo.MyTable WHERE SomeName = @Name AND SomeType = @Type

This works fine; however, I have a list of Name/Type pairs and need to match against rows in my database:

List<Tuple> values = [{ "Name1", "Type1" }, { "Name2", "Type2" }, { "Name3", "Type3" }]

How could i write a query in SQL that would return a list of items based on a list of tuples. The format above is not actually the format of my values, so don't worry about writing some parsing logic to get the values.

Upvotes: 3

Views: 6782

Answers (3)

Tom Hunter
Tom Hunter

Reputation: 5918

You could use Table-Valued Parameters in SQL Server 2008 (ADO.NET).

SQL

CREATE TYPE [dbo].[MyTableType] AS TABLE
(
    [SomeName] NVARCHAR(50),
    [SomeType] NVARCHAR(50)
)

CREATE TABLE [dbo].[MyTable]
(
    [SomeName] NVARCHAR(50),
    [SomeType] NVARCHAR(50)
)

INSERT  [dbo].[MyTable]
VALUES  ('Name1', 'Type1'),
        ('Name2', 'Type2'),
        ('Name3', 'Type3'),
        ('Name4', 'Type4')

C#

var values = new List<Tuple<string, string>> { Tuple.Create("Name1", "Type1"), Tuple.Create("Name2", "Type2"), Tuple.Create("Name3", "Type3") };

var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("SomeName", typeof(string)));
dataTable.Columns.Add(new DataColumn("SomeType", typeof(string)));

values.ForEach(v => dataTable.Rows.Add(v.Item1, v.Item2));

using (var connection = new SqlConnection(@"Data Source=.;Initial Catalog=Tom;Integrated Security=True"))
{
    using (var command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandText =
            "SELECT * " +
            "FROM [dbo].[MyTable] mt " +
            "INNER JOIN @MyTableType mtt " +
            "   ON mt.[SomeName] = mtt.[SomeName] " +
            "   AND mt.[SomeType] = mtt.[SomeType]";

        SqlParameter parameter = command.Parameters.AddWithValue("@MyTableType", dataTable);
        parameter.SqlDbType = SqlDbType.Structured;
        parameter.TypeName = "[dbo].[MyTableType]";

        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("mt.[SomeName]: {0}, mt.[SomeType]: {1}, mtt.[SomeName]: {2}, mtt.[SomeType]: {3}", 
                reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3));
        }
    }
}

Console.ReadKey();

Upvotes: 2

chezy525
chezy525

Reputation: 4174

A couple of options I can think of off the top of my head:

1) Iterate over the list of values and dynamically build a query like the following:

SELECT *
FROM dbo.MyTable
WHERE (SomeName = values[0][0] and SomeType = values[0][1])
   OR (SomeName = values[1][0] and SomeType = values[1][1])
   OR (SomeName = values[2][0] and SomeType = values[2][1])

2) Insert the list into a temp table and run the query as a simple join:

CREATE TABLE #tuples
(Name varchar(max), Type varchar(max))

--Insert the list into the #tuples table (several ways to do this)

SELECT *
FROM dbo.MyTable m
  join #tuples t on m.SomeName = t.Name
                and m.SomeType = t.Type

DROP TABLE #tuples

Upvotes: 1

Altair
Altair

Reputation: 294

Could you iterate over the values list, executing your query that returns one item from the database each time and add each result to a list in your application code?

Upvotes: 0

Related Questions