Readme_Table-Valued Parameters

This sample works only with SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2008.

This sample does the following:

• Creates table-valued parameters by using dynamic discovery though IOpenRowset::OpenRowset.
• Sends table-valued parameter rows by using the pull model in the EmployeesRowset class. In the pull model, the consumer provides data on demand to the provider.
• Sends BLOBs as part of a table-valued parameter in the CPhotograph class.
• Uses custom parameter properties using ISSCommandWithParameters.
• Shows error handling for SQLNCLI10 errors.

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Scenario

For more information about table-valued parameters, see Table-Valued Parameters (SQL Server Native Client) in SQL Server Books Online.

Languages

This sample uses Visual C++.

Prerequisites

Before running this sample, make sure the following software is installed:

• SQL Server or SQL Server Express, including Database Engine.
  You can download SQL Server Express from the Microsoft Download Center.
• The SQL Server Database Engine samples that are available at the Microsoft SQL Server Developer Center.
• .NET Framework SDK 2.0 (or later) or Microsoft Visual Studio 2005 (or later). You can obtain .NET Framework SDK free of charge. For more information, see Installing the .NET Framework Documentation.

Building the Sample

• Before you build OleDb_TVP, you must execute the following Transact-SQL:

  create database testdb
  go
  use testdb
  go
  create table tblEmployees (
  id int identity primary key,
  name nvarchar(50) not null,
  birthday date null,
  salary int null,
  photograph varbinary(max) null
  )
  go
  
  create type tvpEmployees as table(
  name nvarchar(50) not null,
  birthday date null,
  salary int null,
  photograph varbinary(max) null
  )
  go
  
  create procedure insertEmployees @tvpEmployees tvpEmployees readonly, 
  @id int output as
  insert tblEmployees(name, birthday, salary, photograph)
  select name, birthday, salary, photograph from @tvpEmployees
  select @id = coalesce(scope_identity(), -1)
  go
  

• This sample connects to your computer's default SQL Server instance, which must be an instance of SQL Server 2008 (or later). Note that on some Windows operating systems, you will have to change localhost to the name of your SQL Server instance. To connect to a named instance, change the connection string from L"localhost" to L"localhost\\name", where name is the named instance. By default, SQL Server Express installs to a named instance.

• Make sure your INCLUDE environment variable includes the directory that contains sqlncli.h.

• If you are using Visual Studio, load the OleDb_TVP.sln file and build it.

• If you are using MSBuild.exe, invoke MSBuild.exe at a command prompt. Pass in the OleDb_TVP.sln file, as follows:

  MSBuild OleDb_TVP.sln
  

Running the Sample

• From Visual Studio, invoke Start Without Debugging (CTRL+F5).
• If you built with MSBuild.exe, invoke OleDb_TVP.exe.

Each time the sample is run, it displays the ID for the last row it added.

See Also

Concepts

Data Access Samples

Help and Information

Getting SQL Server 2008 Assistance