|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Announcements
Chapters
Services
Feature Zones
|
IntroductionThis article describes how to use table-valued parameters when calling Stored Procedures. Table-valued parameters were introduced in SQL Server 2008. With this parameter type, a table with several rows can be passed to a Stored Procedure or a function. In some cases, this technique eliminates the need for several roundtrips between the client and the database, if the same procedure is called for several times but with different parameter values. This article is not to be taken as an example of how to use SQL Server specific classes in C#, and certainly not as a coding style reference. Type definitionsThe example uses two types: -- Create the type for artist
CREATE TYPE ArtistType AS TABLE (
[Artist#] int,
[Name] nvarchar(100)
);
-- Create the type for record
CREATE TYPE RecordType AS TABLE (
[Record#] int,
[Artist#] int,
[Name] nvarchar(100),
[Year] int
);
Table definitionsThere are two target tables that are filled by a procedure. In this example, the data from the table isn't modified, but there's one trick: The client defines a primary key for each artist and record, and also a foreign key from the record to the artist. This information is used in the Stored Procedure, but the real primary keys in the database are auto-generated by SQL Server. The tables are: -- Create artist table
CREATE TABLE Artist (
[Artist#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL
);
-- Create record table
CREATE TABLE Record (
[Record#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Artist#] int NOT NULL FOREIGN KEY REFERENCES Artist([Artist#]),
[Name] nvarchar(100) NOT NULL,
[Year] int NULL
);
The procedureThe procedure consists of two loops. The outer loop fetches each artist and inserts it into the database. After that, it takes the identity given to the new row. After this, all the records from this single artist are fetched, and the foreign key is set to the corresponding primary key in the artist table. CREATE PROCEDURE [dbo].[AddShoppings](
@Artists dbo.ArtistType READONLY,
@Records dbo.RecordType READONLY) AS
BEGIN
-- variables to use
DECLARE @artist int;
DECLARE @artistIdentity int;
DECLARE @name varchar(100);
DECLARE @year int;
-- cursor for artists parameter
DECLARE artistCursor CURSOR FOR
SELECT [Artist#], [Name]
FROM @Artists;
-- loop through artists
OPEN artistCursor;
FETCH NEXT FROM artistCursor INTO @artist, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert the artist
INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity= @@IDENTITY;
-- cursor for records parameter
DECLARE recordsCursor CURSOR FOR
SELECT [Name], [Year]
FROM @Records
WHERE [Artist#] = @artist;
-- fetch records and insert them
OPEN recordsCursor;
FETCH NEXT FROM recordsCursor INTO @name, @year;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Record ([Artist#], [Name], [Year])
VALUES (@artistIdentity, @name, @year);
FETCH NEXT FROM recordsCursor INTO @name, @year;
END;
CLOSE recordsCursor;
DEALLOCATE recordsCursor;
FETCH NEXT FROM artistCursor INTO @artist, @name;
END;
CLOSE artistCursor;
-- clean-up
DEALLOCATE artistCursor;
END;
The C# codeThe program is a simple console application. It:
In order to use the code, you need to install a SQL Server 2008 instance and create a database in it. After that, the SQL Server instance name and database name are configured via app.config. It would look something like: ...
<applicationSettings>
<TableValuedParameters.Properties.Settings>
<setting name="DataSource" serializeAs="String">
<value>MyMachine\SqlServerInstanceName</value>
</setting>
<setting name="DatabaseName" serializeAs="String">
<value>DatabaseNameToUse</value>
</setting>
</TableValuedParameters.Properties.Settings>
</applicationSettings>
...
The actual call to the database is simple. The keyword for the parameters is command.CommandText = "AddShoppings";
command.CommandType = System.Data.CommandType.StoredProcedure;
parameter = command.Parameters.AddWithValue("@Artists", artist);
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter = command.Parameters.AddWithValue("@Records", record);
parameter.SqlDbType = System.Data.SqlDbType.Structured;
command.Transaction = transaction;
command.ExecuteNonQuery();
That's about it. The rest of the logic is in the code sample. Enjoy! History
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
News 
Answer 
Joke 
Rant 
Admin