C# and long SQL Server queries, what's the correct way to do this?

Question

My problem is simple in nature- query a database, and write output to a set of files (~1000).

I have a SQL query I need to execute from C# on SQL Server. The query is fairly simple except for one of the values in an IN clause.

As an example, the query may look like:

SELECT 
    ID, Name
FROM
    MyTable
WHERE
    SomeValue IN (/* list of values */)

The list of values is around 300 strings that are 6 characters each. The values will ultimately look something like ("A00001", "A00002", etc), and are specified as input by the user of the application.

My initial thought was to build the query out as a long string, with each of the values aggregated together. This doesn't seem like the proper approach however, and seems to be error prone. My next thought was to use a stored procedure, but I'm unsure of how the data should be passed into it. How should this query be built in an efficient way where the data can be reliably passed in?

My other option is to read in ALL data from SQL Server and then filter the results in C# as I receive them. If I do this, however, I'll get around 9 million records which seems to be too many to read into memory as a data set. Because I'm writing files after parsing ~5000 records, I don't want to keep a data reader connected to the database while I chew through the data I'm receiving.

What is the best approach?

Answer 1

The usual way is to make the IN clause a subquery:

SELECT ProductID from Product where ManufacturerID in 
    (SELECT ManufacturerID from Manufacturer where HomeOfficeLocation = 'France')

If you can't do that: as long as the user is picking from a list you've provided, a simple concatenated list isn't particularly error-prone.

You definitely don't want to loop through a large dataset looking for a small number of items.

Edited to add

To be a bit more complete: what I often do is create a comma-delimited list in the front end (ex. "234, 345, 456") and pass that to a stored procedure as a string; then inside that proc use a user-defined function such as the one discussed here.

I have nothing against temp tables, but usually I only use them if I'm going to do more than one operation on a list.

The reason I said "as long as the user is picking from a list" is to save you having to validate (and sanitize) user input.

Answer 2

The topic is discussed at length in this series of articles:

• Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters
• Arrays and Lists in SQL Server 2005 and Beyond When TVPs Do Not Cut it
• Arrays and Lists in SQL Server 2000 and Earlier

You should start with the approach recommended in the first article, using a Table Value Parameter. See also the MSDN usage article: Table-Valued Parameters in SQL Server 2008 (ADO.NET):

void SelectValuesOfInterest(IEnumerable<SqlDataRecord> valuesOfInterest)
SqlCommand cmd = new SqlCommand(@"    SELECT 
        ID, Name
    FROM
        MyTable
    WHERE
        SomeValue IN (SELECT value FROM @tableValueParameter);"
, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tableValueParameter", valuesOfInterest);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.MyTVPType";
using (SqlDataReader rdr = cmd.ExecuteReader ())
{
 while rdr.Read() {...}
}
}

Answer 3

If the number of items in your IN clause is large enough, you probably want to use a temporary table - insert the values there first then join to the temp table, or use it as a subquery source for your SELECT.

This CodeProject article discusses the use of temp tables; but to summarize for your case:

CREATE TABLE #TempQueryList(Item varchar(300))

// in your app code:
insert into #TempQueryList values ( @item ); -- use BIND variable
insert into #TempQueryList values ( @item ); -- use BIND variable
...
insert into #TempQueryList values ( @item ); -- use BIND variable

// then your query becomes:
SELECT 
  ID, Name
FROM
  MyTable
WHERE
  SomeValue IN (SELECT Item FROM #TempQueryList)

Answer 4

If you can do it with a subquery (as egrunin showed), that's easiest and fastest. If you can't do that (ie, all you've got is a list of values), then you might want to look at a table value parameter.

This question has some reading that might be of interest.

Answer 5

Here is one way to do it using xml:

C#:

StringBuilder sb = new StringBuilder();
sb.AppendLine("<SelectedValues>");

foreach (string s in userSelectedValues)
  sb.AppendFormat("<row val=\"{0}\" />{1}", s, Environment.NewLine);
sb.AppendLine("</SelectedValues>");

Pass this sb.ToString() AS XML TO your stored procedure

SQL:

CREATE PROCEDURE dbo.MyStoredProcedure
(
  @UserSelectedValuesXml XML
)     

SELECT * 
FROM dbo.MyTable
WHERE Id IN ( SELECT ref.value('@val', 'varchar')
              FROM @UserSelectedValuesXml.nodes('/SelectedValues/row') AS T(ref))

Answer 6

I'd use a table value parameter. Depending on the version of SQL server you have access to you can pass an entire datatable into a stored procedure then query off of it just like you would a normal table.

Basically, you'd create a table in C# that contains your list. You'd pass that as a parameter to a stored procedure. In the proc you could just do an inner join between MyTable and the parameter you passed to get the results you want.