Have you ever faced the need to send a list of parameters to a stored procedure? A nice and simple solution is the user-defined table type, which is available in SQL Server 2008.

 

First, we need to create the user defined type:

 

CREATE TYPE [dbo].[SongsType] AS TABLE(

       [Id] [int] NOT NULL,

       [Name] [varchar](100) NOT NULL,

       [Artist] [varchar](100) NULL,

)

 

In our code, we will create and populate a DataTable:

 

var dtSongs = new DataTable("Songs");

           

dtSongs.Columns.Add(new DataColumn("Id", typeof(int)));

dtSongs.Columns.Add(new DataColumn("Name", typeof(string)));

dtSongs.Columns.Add(new DataColumn("Artist", typeof(string)));

 

foreach (var song in songsList)//songsList contains the list of songs to save

{

var songRow = dtSongs.NewRow();

        songRow ["Id"] = song. Id;

        songRow ["Name"] = song. Name;

        songRow ["Artist"] = song.Artist;

dtSongs.Rows.Add(songRow);

}

 

Once we have our DataTable, we are going to call the stored procedure and send it as a parameter:

 

using (var connection = new SqlConnection(connectionInfo))

{

connection.Open();

        var command = new SqlCommand("usp_SaveSongs", connection)

{

CommandType = CommandType.StoredProcedure

};

var parameter = command.Parameters.AddWithValue("@Songs", dtSongs);

parameter.SqlDbType = SqlDbType.Structured;

 

        command.ExecuteNonQuery();

}

 

The stored procedure will look something like this:

 

CREATE PROCEDURE InsertFileDetails

(

    @Songs SongsType READONLY

)

AS

       INSERT INTO Songs (Id, Name, Artist)

              SELECT Id, Name, Artist

              FROM  @Songs

 

I hope this post was useful! Till next time.