ADO.NET 3.5 Cookbook, 2nd Edition

3.26. Executing a SQL Server Table-Valued Function

Problem

Your SQL Server database includes a table-valued function that returns a table. You want to retrieve the table from this function using ADO.NET.

Solution

Execute a SQL SELECT command to retrieve records from the table returned by the table-valued function.

The solution fills a DataTable using a DataAdapter with a SQL SELECT command that retrieves records using the table-valued function dbo.ufnGetContactInformation in AdventureWorks. The values in the DataTable are output to the console.

The C# code in Program.cs in the project ExecuteTableValuedFunction is shown in Example 3-38.

Example 3-38. File: Program.cs for ExecuteTableValuedFunction solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteTableValuedFunction
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            // Select all fields from all records returned by the
            // table-valued function for ContactID = 10.
            string sqlSelect = "SELECT * FROM dbo.ufnGetContactInformation(10)";

            // Fill a Data Table with the result set from the table-valued function SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); DataTable dt = new DataTable( ); da.Fill(dt); // Output the row count and result set to the console Console.WriteLine("{0} rows retrieved.\n", dt.Rows.Count); foreach (DataColumn col in dt.Columns) { Console.WriteLine("{0} ...