ADO.NET 3.5 Cookbook, 2nd Edition

4.16. Invoking a Function for Each Row in a Result Set

Problem

You need to invoke a table-valued function to return additional information for each row in a result set.

Solution

Use the APPLY operator.

The solution uses the APPLY operator to retrieve the FirstName, LastName, JobTitle, and ContactType using the table-valued function ufnGetContactInformation for records in the HumanResources.Employee table in AdventureWorks where the ContactID is between 1285 and 1290.

The C# code in Program.cs in the project InvokeFunctionForEachRowResultSet is shown in Example 4-19.

Example 4-19. File: Program.cs for InvokeFunctionForEachRowResultSet solution

using System; using System.Data; using System.Data.SqlClient; namespace InvokeFunctionForEachRowResultSet { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT e.ContactID, " + "c.FirstName, c.LastName, c.JobTitle, c.ContactType " + "FROM HumanResources.Employee e " + "CROSS APPLY ufnGetContactInformation(e.ContactID) c " + "WHERE e.ContactID BETWEEN 1285 AND 1290 " + "ORDER BY e.ContactID"; // Fill the DataTable DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.Fill(dt); Console.WriteLine("ID\tType\t\tName\t\tJobTitle"); Console.WriteLine("--\t----\t\t----\t\t--------"); foreach (DataRow row in dt.Rows) Console.WriteLine("{0}\t{1}\t{2}, {3}\t{4}", row["ContactID"], ...