Table-valued parameters in SQL Server 2008 (Part 2)

One advantage of the TVP is that you can build up the data for the TVP within a client application. There several examples of this available online, such as on Guy Burstein's blog  and on Mike Taulty's blog there is a short video showing how to use a DataTable as a table-valued parameter. You can also use DbDataReader or IList<SqlDataRecord> for the input parameter in the client application

For my table-valued parameter presentation, I extended Mike's example to include another dataGridView that displays the values in the destination table. This was very easy to set up using the Visual Studio wizards and means you can see the effects immediately without switching back to SQL Server Management Studio. This dataGridView is populated using the procedure dbo.Get_All_Nodes which can be found in the TVP-Client.sql script. This script will also create the TVPDEMO database, udt_HospitalHierarchy user defined type, the destination table dbo.HospitalHierarchy and the procedure which takes the TVP as a parameter dbo.Insert_Residents_From_Table_Valued_Parameter. The type for this parameter is SqlDbType.Structured. Source and code for the project is in TVP-Project.zip. To run the project you will need .NET framework 3.5.

In the TVPForm class you will see I have created a Datatable

   private DataTable table;

Which is then used in the InitializeData method. All the columns in the table are strings, so there is an implicit convertion to the hierarchyid, so when this value is input I specify an ordpath.

    private void InitializeData()

    {

        table = new DataTable();

        table.Columns.Add("node", typeof(string));

        table.Columns.Add("name", typeof(string));

        table.Columns.Add("type", typeof(string));

        dataGridViewIn.DataSource = table.DefaultView;

        dataGridViewIn.Columns[0].Width = 100;

        dataGridViewIn.Columns[1].Width = 200;

        dataGridViewIn.Columns[2].Width = 130;

    }

Of couse it wouldn't be a demo application without the big button and that is where the procedure dbo.Insert_Residents_From_Table_Valued_Parameter is executed.

    private void button1_Click(object sender, EventArgs e)

    {

        using (SqlConnection con = new SqlConnection(

              "server=(local);database=tvpdemo;integrated security=sspi"))

        {

            con.Open();

            using (SqlCommand com = new SqlCommand("dbo.Insert_Residents_From_Table_Valued_Parameter", con))

            {

                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.AddWithValue("tvp", table );

                com.ExecuteNonQuery();

            }

            con.Close();

           //Clear down the input grid

            while (dataGridViewIn.Rows.Count > 1)

                dataGridViewIn.Rows.RemoveAt(0);

//Re-populate the Output          

this.get_All_NodesTableAdapter.Fill(this.tVPDEMODataSet1.Get_All_Nodes);

        }

    }

The setup script will enter the root node for Seattle Grace Hospital, so if you run the application the initial screen should look like:

You can enter data into the top grid (being careful to get the ordpath values right!)

And pressing the Insert Data button..

I can see that TVPs are going to be a useful feature in SQL Server 2008, they are easy to code and will help reduce the number of round trips to the server. TVPs are not the only method for passing an array or structure to a stored procedure, so if you already have a solution will it be worthwhile changing the implementation to take advantage of this new feature? In my next blog in this series I will try to look at how TVP perform compared to other methods and try and find an answer! To see the first blog post go here